Get Quick Info On MySQL DB, Table, Column and Index Using mysqlshow

 

Free eBook - Linux 101 Hacks. Get your copy now.

Most of the open source application uses MySQL database. To debug an issue with the open source application, it is very important to understand how to quickly get high level information about your MySQL database. In this article, I have explained how you can view the database, table, column and index information on any MySQL database using 9 examples.

In all the following mysqlshow examples, you can provide password using one of the following two methods:

• Enter the password immediately after -p in the mysqlshow command without any space after -p. This option is helpful, if you are using mysqlshow inside a shell script.
• Just provide option -p without any password to mysqlshow, which will prompt for a password. This option is recommended when you are using mysqlshow interactively from the command line.

1. Display available databases

Please replace tmppassword with your MySQL DB root user password.

# mysqlshow  -u root -ptmppassword

+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| sugarcrm           |
+--------------------+

2. Display all tables in a database

The example below will display all the tables located under sugarcrm database

# mysqlshow  -u root -ptmppassword sugarcrm

Database: sugarcrm
+--------------------------------+
|             Tables             |
+--------------------------------+
| accounts                       |
| accounts_audit                 |
| accounts_bugs                  |

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

# mysqlshow  -v -u root -p sugarcrm

Enter password:
Database: sugarcrm
+--------------------------------+----------+
|             Tables             | Columns  |
+--------------------------------+----------+
| accounts                       |       33 |
| accounts_audit                 |       10 |
| accounts_bugs                  |        5 |

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 sugarcrm

Enter password:
Database: sugarcrm
+--------------------------------+----------+------------+
|             Tables             | Columns  | Total Rows |
+--------------------------------+----------+------------+
| accounts                       |       33 |        252 |
| accounts_audit                 |       10 |         63 |
| accounts_bugs                  |        5 |          0 |

5. Display all columns of a table

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

# mysqlshow  -u root -ptmppassword sugarcrm accounts

Database: sugarcrm  Table: accounts
+-----------------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field                       | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-----------------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id                          | char(36)     | utf8_general_ci | NO   | PRI |         |       | select,insert,update,references |         |
| name                        | varchar(150) | utf8_general_ci | YES  |     |         |       | select,insert,update,references |         |
| date_entered                | datetime     |                 | YES  |     |         |       | select,insert,update,references |         |

6. Display details about a specific column from a table

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

# mysqlshow  -u root -ptmppassword sugarcrm accounts id

Database: sugarcrm  Table: accounts  Wildcard: id
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type     | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id    | char(36) | utf8_general_ci | NO   | PRI |         |       | select,insert,update,references |         |
+-------+----------+-----------------+------+-----+---------+-------+---------------------------------+---------+

7. Show all metadata information about a table

# mysqlshow  -i  -u root -ptmppassword sugarcrm accounts

This will display the following information about the accounts table.

Name Engine Version Row_format Rows Avg_row_length

Data_length Max_data_length Index_length Data_free Auto_increment Create_time

Update_time Check_time Collation Checksum Create_options Comment

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 -ptmppassword sugarcrm accounts

Database: sugarcrm  Table: accounts
+-----------------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field                       | Type         | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-----------------------------+--------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| id                          | char(36)     | utf8_general_ci | NO   | PRI |         |       | select,insert,update,references |         |
| name                        | varchar(150) | utf8_general_ci | YES  |     |         |       | select,insert,update,references |         |
+----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name               | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| accounts | 0          | PRIMARY                | 1            | id               | A         | 252         |          |        |      | BTREE      |         |
| accounts | 1          | idx_accnt_id_del       | 1            | id               | A         |             |          |        |      | BTREE      |         |
+----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

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 accounts table.

# mysqlshow -k -u root -ptmppassword sugarcrm accounts invalid_col_name

Database: sugarcrm  Table: accounts  Wildcard: invalid_col_name
+-------+------+-----------+------+-----+---------+-------+------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-------+------+-----------+------+-----+---------+-------+------------+---------+
+-------+------+-----------+------+-----+---------+-------+------------+---------+
+----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name               | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| accounts | 0          | PRIMARY                | 1            | id               | A         | 254         |          |        |      | BTREE      |         |
| accounts | 1          | idx_accnt_id_del       | 1            | id               | A         |             |          |        |      | BTREE      |         |
+----------+------------+------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

If you like this article, please bookmark it on del.icio.us and Stumble to help me out.

Tags: backup, MySQL, restore