Tuesday, June 26, 2012

Useful MySQL Command to troubleshoot Database

1. Display available databases

Below command will show all database available on this instance.

> mysqlshow  -uroot –p

2. Display all tables in a database

Below command will display all the tables located under test database

> mysqlshow  -u root –p test

3. Display tables along with number of columns in a database

> mysqlshow  -v -u root -p test

4. Display total number of columns and rows of all tables in a database

Please note there are two -v in the following command.

> mysqlshow  -v -v -u root -p test

5. Display all columns of a table

In the following example, it displays the entire available column name along with additional column information for accounts table in test database.

> mysqlshow  -u root -p test test

6. Display details about a specific column from a table

In this example, it displays information about id column from test table.

> mysqlshow  -u root –p test test id

7. Show all metadata information about a table

> mysqlshow  -i  -uroot -p test test

8. Display both indexes and columns of a table

Please note that the indexes are listed at the bottom of the display after the column information.

> mysqlshow -k -u root -p test test

Database: test  Table: test

9. Display only indexes and not columns of a table

This can be done by tricking the mysqlshow, by giving an invalid column name. Since invalid_col_name doesn’t exist on accounts table, the following command will display only the indexes of test table.

> mysqlshow -k -u root -p test test invalid_col_name

  Database: test  Table: test Wildcard: invalid_col_name

No comments: