mysqlshow Command in Linux
mysqlshow is a command-line utility provided by MySQL that allows users to quickly view information about databases and tables. This command is pretty useful for getting a quick overview of the structure and contents of your MySQL databases without needing to log into the MySQL command line interface.
Table of Contents
Here is a comprehensive guide to the options available with the mysqlshow command −
Syntax of mysqlshow Command
The basic syntax for the mysqlshow command is as follows −
mysqlshow [options] [database [table [column]]]
Where −
- [options] − Various options to customize the output.
- [database] − The name of the database you want to show information about.
- [table] − The name of the table you want to show information about.
- [column] − The name of the column you want to show information about.
mysqlshow Command Options
Here are some common options you can use with the mysqlshow command −
| Option | Description |
|---|---|
| -c, --character-sets-dir=name | Set the directory for character set files. |
| -C, --compress | Use compression in the server/client communication. |
| --count | Show the number of rows per table (may be slow for non-MyISAM tables). |
| -#, --debug[=name] | Generate a debug log; the commonly used format is 'd:t:o,filename'. |
| --debug-check | Check memory and open file usage upon exit. |
| --debug-info | Print additional debug information upon exit. |
| --default-auth=name | Set the default authentication client-side plugin to use. |
| --default-character-set=name | Specify the default character set. |
| --defaults-extra-file=# | Read this file after reading the global configuration files. |
| --defaults-file=# | Read default options only from the specified file. |
| --defaults-group-suffix=# | Also read groups with the combined name of group and suffix. |
| --enable-cleartext-plugin | Enable or disable the clear text authentication plugin. |
| --get-server-public-key | Retrieve the server's public key. |
| -h, --host=name | Connect to the specified host. |
| -i, --status | Show detailed status information about each table. |
| -k, --keys | Display keys for the table. |
| --login-path=# | Read this path from the login configuration file. |
| --no-defaults | Do not read default options from any configuration file, except the login file. |
| -p, --password[=name] | Password to use when connecting to the server; if not specified, it will be asked interactively. |
| --password1[=name] | Password for the first factor authentication plugin. |
| --password2[=name] | Password for the second factor authentication plugin. |
| --password3[=name] | Password for the third factor authentication plugin. |
| --plugin-dir=name | Directory for client-side plugins. |
| -P, --port=# | Port number to use for the connection; defaults to configuration in my.cnfor 3306 if unspecified. |
Examples of mysqlshow Command in Linux
Check out the following examples of the mysqlshow command running on the Linux environment −
- Showing All Databases
- Viewing Tables in a Database
- Displaying Columns in a Table
- Utilizing Verbose Mode
- Connecting to a Specific Host
- Counting Rows in Tables
- Connecting with SSL
Showing All Databases
To get a quick overview of all the databases on your MySQL server, run −
sudo mysqlshow --user=root --password=your_password
This command provides a complete list of all databases available on the server, which is useful for managing multiple databases.
Viewing Tables in a Database
If you need to see the tables within a specific database, use this command −
sudo mysqlshow --user=root --password=your_password database_name
This will display all tables in the specified database, giving you an easy way to understand its structure.
Displaying Columns in a Table
To delve deeper and view the columns of a particular table, run −
sudo mysqlshow --user=root --password=your_password database_name table_name
This command reveals all columns within the selected table, providing detailed insights into its structure.
Utilizing Verbose Mode
For more detailed information about your databases, enable verbose mode with −
sudo mysqlshow --verbose --user=root --password=your_password
Using the --verbose option gives you a richer set of details about the databases, helping with more thorough management.
Connecting to a Specific Host
To connect and manage a MySQL server hosted on a specific IP address or hostname, use −
sudo mysqlshow --host=hostname --user=root --password=your_password
This command allows you to access and display databases on a remote server, which is particularly useful for distributed setups.
Counting Rows in Tables
In case you want count the number of rows in each table of a database, use the following command −
sudo mysqlshow --count --user=root --password=your_password database_name
This command provides a row count for each table.
Connecting with SSL
If you need to connect to your MySQL server using SSL, use −
mysqlshow --ssl --user=root --password=your_password --ssl-ca=/path/to/ca-cert.pem --ssl-cert=/path/to/client-cert.pem --ssl-key=/path/to/client-key.pem
This command ensures that the connection to the server is secured with SSL, enhancing security.
Conclusion
mysqlshow is a useful command for obtaining a quick overview of the structure and contents of your MySQL databases without needing to log into the MySQL command line interface. By mastering the various options and practical examples, you can efficiently manage and review your MySQL database environments, ensuring smooth and effective operations. Whether you are listing databases, viewing tables, displaying columns, or connecting securely via SSL, mysqlshow provides the flexibility and functionality needed to manage your databases with ease.