mysqlshow
Shows the structure of a MariaDB database (databases, tables, columns and indexes). You can also use SHOW DATABASES, SHOW TABLES, SHOW COLUMNS, SHOW INDEX and SHOW TABLE STATUS, as well as the Information Schema tables (TABLES, COLUMNS, STATISTICS), to get similar functionality.
Usage
mysqlshow [OPTIONS] [database [table [column]]]
The output displays only the names of those databases, tables, or columns for which you have some privileges.
If no database is given then all matching databases are shown. If no table is given, then all matching tables in database are shown. If no column is given, then all matching columns and column types in table are shown.
If the last argument contains a shell or SQL wildcard (*,?,% or _) then only what's matched by the wildcard is shown. If a database name contains any underscores, those should be escaped with a backslash (some Unix shells require two) to get a list of the proper tables or columns. “*” and “?” characters are converted into SQL “%” and “_” wildcard characters. This might cause some confusion when you try to display the columns for a table with a “_” in the name, because in this case, mysqlshow shows you only the table names that match the pattern. This is easily fixed by adding an extra “%” last on the command line as a separate argument.
Options
The following options can be specified, either on the command line, or in the [mysqlshow], [client],[client-server] or [client-mariadb] groups of an option file.
Option | Description |
---|---|
-c name , --character-sets-dir=name | Directory for character set files. |
-C , --compress | Use compression in server/client protocol if both support it. |
--count | Show number of rows per table (may be slow for non-MyISAM tables). |
-# [name] , --debug[=name] | Output debug log. Typical is d:t:o,filename , the default is d:t:o . |
--debug-check | Check memory and open file usage at exit. |
--debug-info | Print some debug info at exit. |
--default-auth=name | Default authentication client-side plugin to use. |
--default-character-set=name | Set the default character set. |
--defaults-extra-file=name | Read the file name after the global files are read. Must be given as the first option. |
--defaults-file=name | Only read default options from the given file name. Must be given as the first option. |
--defaults-group-suffix=suffix | In addition to the given groups, also read groups with this suffix. |
-? , --help | Display help and exit. |
-h name , --host=name | Connect to the MariaDB server on the given host. |
-k , --keys | Show indexes for table. |
--no-defaults | Don't read default options from any option file. Must be given as the first option. |
-p[password] , --password[=password] | Password to use when connecting to server. If password is not given, it's solicited on the command line. Specifying a password on the command line should be considered insecure. You can use an option file to avoid giving the password on the command line. |
-W , --pipe | On Windows, connect to the server via a named pipe. This option applies only if the server supports named-pipe connections. |
--plugin-dir=name | Directory for client-side plugins. |
-P num , --port=num | Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306). |
--print-defaults | Print the program argument list and exit. Must be given as the first option. |
--protocol=name | The protocol to use for connection (tcp, socket, pipe, memory). |
--shared-memory-base-name=name | On Windows, the shared-memory name to use, for connections made using shared memory to a local server. The default value is MYSQL . The shared-memory name is case sensitive. The server must be started with the --shared-memory option to enable shared-memory connections. |
-t , --show-table-type | Show table type column, as in SHOW FULL TABLES. The type is BASE TABLE or VIEW. |
-S name , --socket=name | For connections to localhost, the Unix socket file to use, or, on Windows, the name of the named pipe to use. |
--ssl | Enable SSL for connection (automatically enabled with other flags). |
--ssl-ca=name | CA file in PEM format (check OpenSSL docs, implies --ssl). |
--ssl-capath=name | CA directory (check OpenSSL docs, implies --ssl). |
--ssl-cert=name | X509 cert in PEM format (implies --ssl). |
--ssl-cipher=name | SSL cipher to use (implies --ssl). |
--ssl-key=name | X509 key in PEM format (implies --ssl). |
--ssl-crl=name | Certificate revocation list (implies --ssl). |
--ssl-crlpath=name | Certificate revocation list path (implies --ssl). |
--ssl-verify-server-cert | Verify server's "Common Name" in its cert against hostname used when connecting. This option is disabled by default. |
-i , --status | Shows a lot of extra information about each table. |
-u , --user=name | User for login if not current user. |
-v , --verbose | More verbose output; you can use this multiple times to get even more verbose output. |
-V , --version | Output version information and exit. |
Examples
Getting a list of databases:
bin/mysqlshow +--------------------+ | Databases | +--------------------+ | information_schema | | test | +--------------------+
Getting a list of tables in the test
database:
bin/mysqlshow test Database: test +---------+ | Tables | +---------+ | author | | book | | city | | country | +---------+
Getting a list of columns in the test
.book
table:
bin/mysqlshow test book Database: test Table: book +-----------+-----------------------+-------------------+------+-----+---------+----------------+--------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-----------+-----------------------+-------------------+------+-----+---------+----------------+--------------------------------+---------+ | id | mediumint(8) unsigned | | NO | PRI | | auto_increment | select,insert,update,references | | | title | varchar(200) | latin1_swedish_ci | NO | | | | select,insert,update,references | | | author_id | smallint(5) unsigned | | NO | MUL | | | select,insert,update,references | | +-----------+-----------------------+-------------------+------+-----+---------+----------------+--------------------------------+---------+