mysqlshow answers a question you ask constantly: does a table called orders actually exist in this database. One line, no SQL prompt, no full session to open. Databases, tables, columns and indexes all come out the same quick way. Ten minutes if you already have a database account and its connection details.
mysqlshow is the older command name. On this machine it is a symlink to mariadb-show, and both names use the installed MariaDB client 10.11.14 from the mariadb-client package. The command reads metadata, not table contents, but the results are still limited by the privileges of the account you use.
Start with read-only checks. They do not need elevated privileges and do not contact a server:
$ command -v mysqlshow
/usr/bin/mysqlshow
$ mysqlshow --no-defaults --version
mysqlshow Ver 9.10 Distrib 10.11.14-MariaDB, for debian-linux-gnu (x86_64)
$ ls -l /usr/bin/mysqlshow /usr/bin/mariadb-show
The version line is the useful checkpoint. The client reports its own version as 9.10 while the MariaDB distribution shown in the same line is 10.11.14. Do not confuse that client protocol label with the server version.
You need a MariaDB user, the server host, and either the local socket or a TCP port. The simplest form lets the client use its normal defaults:
$ mysqlshow -u reporting -pBecause -p has no password attached, the client prompts on the terminal. That is preferable to putting a password in the command line, where it can leak through shell history or process inspection. Never write -preal-password in a shared shell session.
For a remote server, make the endpoint explicit:
$ mysqlshow -h db.example.net -P 3306 -u reporting -p-P selects the TCP port and, when used without another connection property, forces the TCP protocol. For a local socket, use its actual path instead:
$ mysqlshow --socket=/run/mysqld/mysqld.sock -u reporting -pThese commands require an ordinary database login, not root or sudo. If the server is remote, ask the database administrator for the approved hostname, port and TLS settings rather than guessing them.
With no positional argument, mysqlshow lists databases visible to the account:
$ mysqlshow -u reporting -p
The result is a table headed Databases. Its contents are host-specific. An empty or incomplete list does not prove that databases are absent: the manual says that only names for which the account has some privileges are displayed.
Add a database name to list its matching tables:
$ mysqlshow -u reporting -p inventory
At this level the output normally identifies the database and prints a Tables_in_inventory column. Use the exact database name returned by the previous command. This is an inspection operation, so it does not create, alter or delete objects.
To include whether each object is a base table or a view, add --show-table-type:
$ mysqlshow --show-table-type -u reporting -p inventory
The type column distinguishes BASE TABLE from VIEW. The shorter -t spelling is equivalent.
Supply a table name after the database to show its columns and column types:
$ mysqlshow -u reporting -p inventory orders
The output is the command-line equivalent of asking MariaDB to show column information. It contains the columns that the account is allowed to see, so use a database account with the intended audit scope.
Use --keys when the question is about indexes rather than columns:
$ mysqlshow --keys -u reporting -p inventory orders
For extra table information, use --status. To add row counts, use --count, but treat that as a potentially slow operation on non-MyISAM tables:
$ mysqlshow --status --count -u reporting -p inventory
Run the count only when you need it. It can make a quick inventory unexpectedly expensive on a large or busy database.
The final positional argument can be a shell or SQL-style pattern. The client maps * and ? to SQL % and _, while % and _ are already SQL wildcards. Quote patterns so the shell does not expand them against local filenames:
$ mysqlshow -u reporting -p inventory 'order*'
This asks for table names matching the pattern. The underscore character is the awkward case: in SQL it matches one character. If the real table name contains an underscore, escape it and keep the shell from interpreting the backslash:
$ mysqlshow -u reporting -p inventory 'orders\_%' '%'
The extra final % keeps the command at the table-and-column level when you want columns for tables whose names contain an underscore. If a pattern produces surprising results, first rerun with the literal name and then add one wildcard at a time.
MariaDB clients can read option files, including /etc/my.cnf, /etc/mysql/my.cnf and ~/.my.cnf. The installed client reads groups such as [mysqlshow], [mariadb-show] and [client]. A stored host, user, socket or TLS setting can therefore change what a short command does.
Print the arguments assembled from option files without connecting:
$ mysqlshow --print-defaults
mysqlshow would have been started with the following arguments:
The lines after that heading depend on your local configuration. For a clean diagnostic test, put --no-defaults first. It must be the first argument and prevents option files from being read:
$ mysqlshow --no-defaults --host=db.example.net --port=3306 --user=reporting -p
If this changes the destination or authentication behaviour, inspect the option files and your account configuration before continuing. Do not delete a shared option file to make a command work. If it contains a password, restrict its permissions according to your site's database-client policy and avoid printing it in diagnostics.
A connection error is separate from an empty result. First verify the endpoint and protocol you intended, then check the account and its grants with the database administrator. For a local socket, confirm that the path exists without changing service state:
$ test -S /run/mysqld/mysqld.sock && echo 'socket exists'
No output means that socket path is not present. It might indicate a different socket location, a server that is not running, or a remote-only setup. Do not restart the database or change its configuration merely because mysqlshow cannot connect.
If authentication succeeds but a table is missing, check the spelling, wildcard expansion and account privileges. The command is not a catalogue of objects hidden from the user. For a more flexible query, use the MariaDB client and the relevant SHOW statement, but keep the same endpoint and privilege checks.
mysqlshow --version identifies the installed client and distribution.