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.

Using mysqlshow

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.


mysqlshow supports the following options:

-c name, --character-sets-dir=nameDirectory for character set files.
-C, --compressUse compression in server/client protocol if both support it.
--countShow 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-checkCheck memory and open file usage at exit.
--debug-infoPrint some debug info at exit.
--default-auth=nameDefault authentication client-side plugin to use.
--default-character-set=nameSet the default character set.
--defaults-extra-file=nameRead the file name after the global files are read. Must be given as the first option.
--defaults-file=nameOnly read default options from the given file name. Must be given as the first option.
--defaults-group-suffix=suffixIn addition to the given groups, also read groups with this suffix.
-?, --helpDisplay help and exit.
-h name, --host=nameConnect to the MariaDB server on the given host.
-k, --keysShow indexes for table.
--no-defaultsDon'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, --pipeOn Windows, connect to the server via a named pipe. This option applies only if the server supports named-pipe connections.
--plugin-dir=nameDirectory for client-side plugins.
-P num, --port=numPort 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-defaultsPrint the program argument list and exit. Must be given as the first option.
--protocol=nameThe protocol to use for connection (tcp, socket, pipe, memory).
--shared-memory-base-name=nameOn 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-typeShow table type column, as in SHOW FULL TABLES. The type is BASE TABLE or VIEW.
-S name, --socket=nameFor connections to localhost, the Unix socket file to use, or, on Windows, the name of the named pipe to use.
--sslEnable TLS for connection (automatically enabled with other flags).
--ssl-ca=nameCA file in PEM format (check OpenSSL docs, implies --ssl).
--ssl-capath=nameCA directory (check OpenSSL docs, implies --ssl).
--ssl-cert=nameX509 cert in PEM format (implies --ssl).
--ssl-cipher=nameSSL cipher to use (implies --ssl).
--ssl-key=nameX509 key in PEM format (implies --ssl).
--ssl-crl=nameCertificate revocation list (implies --ssl).
--ssl-crlpath=nameCertificate revocation list path (implies --ssl).
--ssl-verify-server-certVerify server's "Common Name" in its cert against hostname used when connecting. This option is disabled by default.
-i, --statusShows a lot of extra information about each table. See the INFORMATION_SCHEMA.TABLES table for more details on the returned information.
-u, --user=nameUser for login if not current user.
-v, --verboseMore verbose output; you can use this multiple times to get even more verbose output.
-V, --versionOutput version information and exit.

Option Files

In addition to reading options from the command-line, mysqlshow can also read options from option files. If an unknown option is provided to mysqlshow in an option file, then it is ignored.

The following options relate to how MariaDB command-line tools handles option files. They must be given as the first argument on the command-line:

--print-defaultsPrint the program argument list and exit.
--no-defaultsDon't read default options from any option file.
--defaults-file=# Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=# In addition to the default option groups, also read option groups with this suffix.

In MariaDB 10.2 and later, mysqlshow is linked with MariaDB Connector/C. However, MariaDB Connector/C does not yet handle the parsing of option files for this client. That is still performed by the server option file parsing code. See MDEV-19035 for more information.

Option Groups

mysqlshow reads options from the following option groups from option files:

[mysqlshow] Options read by mysqlshow, which includes both MariaDB Server and MySQL Server.
[client] Options read by all MariaDB and MySQL client programs, which includes both MariaDB and MySQL clients. For example, mysqldump.
[client-server]Options read by all MariaDB client programs and the MariaDB Server. This is useful for options like socket and port, which is common between the server and the clients.
[client-mariadb]Options read by all MariaDB client programs.


Getting a list of databases:

|     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 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 |         |


Comments loading...