The SHOW statements are the good old workhorse in MySQL for database administrators to obtain information about the schema objects and what happens on the system. While today most of the information can be found in the Information Schema or Performance Schema, the SHOW command is still very popular for interactive use due to its short syntax.
It is recommended to query the underlying Information Schema views and Performance Schema tables. This particularly applies to noninteractive access to the data. Querying the underlying sources is also more powerful as it allows you to join to other views and tables.
This chapter starts out with an overview of how the SHOW statements match up with the Information Schema views and Performance Schema tables. The remainder of the chapter covers SHOW statements that do not have views or tables in the Information Schema and Performance Schema including obtaining engine status information with a little more in-depth view of the InnoDB monitor output provided by the SHOW ENGINE INNODB STATUS statement as well as getting replication and binary log information.
Relationship to the Information Schema
Correlation between SHOW statements and the Information Schema
SHOW Statement | I_S Views | Comments |
|---|---|---|
CHARACTER SET | CHARACTER_SETS | |
COLLATION | COLLATIONS | |
COLUMNS | COLUMNS | |
CREATE DATABASE | SCHEMATA | |
CREATE EVENT | EVENTS | |
CREATE FUNCTION | ROUTINES | ROUTINE_TYPE = 'FUNCTION' |
CREATE PROCEDURE | ROUTINES | ROUTINE_TYPE = 'PROCEDURE' |
CREATE TABLE | TABLES | |
CREATE TRIGGER | TRIGGERS | |
CREATE VIEW | VIEWS | |
DATABASES | SCHEMATA | |
ENGINES | ENGINES | |
EVENTS | EVENTS | |
FUNCTION STATUS | ROUTINES | ROUTINE_TYPE = 'FUNCTION' |
GRANTS | COLUMN_PRIVILEGES SCHEMA_PRIVILEGES TABLE_PRIVILEGES USER_PRIVILEGES | |
INDEX | STATISTICS | SHOW INDEXES and SHOW INDEXES are synonyms for SHOW INDEX. |
PLUGINS | PLUGINS | |
PROCEDURE STATUS | ROUTINES | ROUTINE_TYPE = 'PROCEDURE' |
PROCESSLIST | PROCESSLIST | It is recommended to use performance_schema.threads instead. |
PROFILE | PROFILING | Deprecated – use the Performance Schema instead. |
PROFILES | PROFILING | Deprecated – use the Performance Schema instead. |
TABLE STATUS | TABLES | |
TABLES | TABLES | |
TRIGGERS | TRIGGERS |
The information will not always be identical between the SHOW statement and the corresponding Information Schema views. In some cases, there is more information available using the views, and in general the views are more flexible.
There are also several SHOW statements where the underlying data can be found in the Performance Schema.
Relationship to the Performance Schema
Correlation between SHOW statements and the Performance Schema
SHOW Statement | Performance Schema Tables |
|---|---|
MASTER STATUS | log_status |
SLAVE STATUS | log_status replication_applier_configuration replication_applier_filters replication_applier_global_filters replication_applier_status replication_applier_status_by_coordinator replication_applier_status_by_worker replication_connection_configuration replication_connection_status |
STATUS | global_status session_status events_statements_summary_global_by_event_name events_statements_summary_by_thread_by_event_name |
VARIABLES | global_variables session_variables |
The SHOW MASTER STATUS includes information about what filtering is enabled when writing events to the binary log. This information is not available from the Performance Schema, so if you are using the binlog-do-db or binlog-ignore-db option (not recommended as they can prevent point-in-time recoveries), then you still need to use SHOW MASTER STATUS.
There are a few columns in the SHOW SLAVE STATUS output that cannot be found in the Performance Schema tables. Some of those can be found in the slave_master_info and slave_relay_log_info tables in the mysql schema (if master_info_repository and relay_log_info_repository have been set to TABLE which is the default).
For SHOW STATUS and SHOW VARIABLES, one difference is that the SHOW statements returning session scope values will include the global values if there is no session value. When querying the session_status and session_variables, only the values belonging to the requested scope are returned. Additionally, the SHOW STATUS statement includes the Com_% counters, whereas when querying the Performance Schema directly, these counters correspond to events in the events_statements_summary_global_by_event_name and events_statements_summary_by_thread_by_event_name tables (depending on whether the global or session scope is queried).
There are also some SHOW statements that do not have any corresponding tables. The first group of these that will be discussed is for the engine status.
Engine Status
The SHOW ENGINE statement can be used to get storage engine–specific information. It is currently implemented for the InnoDB, Performance_Schema, and NDBCluster engines. For all three engines, it is possible to request the status, and for the InnoDB engine, it is also possible to get mutex information.
The SHOW ENGINE PERFORMANCE_SCHEMA STATUS statement can be useful to get some status information about the Performance Schema including the size of the tables and their memory usage. (The memory usage can also be obtained from the memory instrumentation.)
By far, the most used engine status statement is SHOW ENGINE INNODB STATUS which provides a comprehensive report called the InnoDB monitor report which includes some information that cannot be obtained from other sources. The rest of this section introduces the InnoDB monitor report.
You can also make InnoDB output the monitor report to the error log at regular intervals by enabling the innodb_status_output system variable. When the innodb_status_output_locks option is set, the InnoDB monitor (whether generated because of innodb_status_output = ON or using SHOW ENGINE INNODB STATUS) includes additional lock information.
BACKGROUND THREAD: The work done by the main background thread.
SEMAPHORES: Semaphore statistics. The section is most important in cases where contention causes long semaphore waits in which case the section can be used to get information about the locks and who holds them.
LATEST FOREIGN KEY ERROR: If a foreign key error has been encountered, this section includes details of the error. Otherwise, the section is omitted.
LATEST DETECTED DEADLOCK: If a deadlock has occurred, this section includes details of the two transactions and the locks that caused the deadlock. Otherwise, the section is omitted.
TRANSACTIONS: Information about the InnoDB transactions. Only transactions that have modified InnoDB tables are included. If the innodb_status_output_locks option is enabled, the locks held for each transaction are listed; otherwise, it is just locks involved in lock waits. It is in general better to use the information_schema.INNODB_TRX view to query the transaction information and for lock information to use the performance_schema.DATA_LOCKS and performance_schema.DATA_LOCK_WAITS tables.
FILE I/O: Information about the I/O threads used by InnoDB including the insert buffer thread, log thread, read threads, and write threads.
INSERT BUFFER AND ADAPTIVE HASH INDEX: Information about the change buffer (this was formerly called the insert buffer) and the adaptive hash index.
LOG: Information about the redo log.
BUFFER POOL AND MEMORY: Information about the InnoDB buffer pool. This information is better obtained from the information_schema.INNODB_BUFFER_POOL_STATS view.
INDIVIDUAL BUFFER POOL INFO: If innodb_buffer_pool_instances is greater than 1, this section includes information about the individual buffer pool instances with the same information as for the global summary in the previous section. Otherwise, the section is omitted. This information is better obtained from the information_schema.INNODB_BUFFER_POOL_STATS view.
ROW OPERATIONS: This section shows various information about InnoDB including the current activity, what the main thread is doing, and the row activity for inserts, updates, deletes, and reads.
Several of the sections will be used in later chapters when their content is used to analyze performance or lock problems.
Replication and Binary Logs
The SHOW statements have always been important when working with replication. While the Performance Schema replication tables have now largely replaced the SHOW SLAVE STATUS and SHOW MASTER STATUS statements, if you want to see which replicas are connected and inspect events in the binary log or relay log from inside MySQL, then you still need to use SHOW statements.
Listing Binary Logs
The SHOW BINARY LOGS statement is useful to check which binary logs exist. This can be useful if you want to know how much space the binary logs occupy, whether they are encrypted, and for position-based replication whether the logs required by a replica still exist.
The Encrypted column was added in MySQL 8.0.14 together with the support for encrypted binary logs.
In general, the file size will be larger than in the example as the autorotation of the binary log files happens when the size exceeds max_binlog_size (defaults to 1 GiB) after writing a transaction. Since transactions are not split between files, if you have large transactions, the file can become somewhat larger than max_binlog_size.
Viewing Log Events
IN: The name of the binary log or relay log file to read events from.
FROM: The position in bytes to start reading from.
LIMIT: The number of events to include with an optional offset. The syntax is the same as for SELECT statements: [offset], row_count.
FOR CHANNEL: For relay logs, the replication channel to read events for.
Using SHOW BINLOG EVENTS
The example illustrates some of the limitations of using SHOW statements to inspect binary and relay logs. The result is a normal result set from a query, and since the files typically are around 1 GiB in size, it means the result can be equally large. You can do as in the example where only specific events are chosen, but it is not always trivial to know where the interesting events start, and you cannot filter by the event types or which tables they affect. Finally, the default event format (the binlog_format option) is the row format, and as it can be seen from the third and fourth rows in the result, all you can see from SHOW BINGOG EVENTS is that the transaction updated the world.city table. You cannot see which rows were updated and what the values are.
Inspecting the binary log using the mysqlbinlog utility
The -v arguments request verbose mode and can be given up to two times to increase the amount of information included. A single -v is what generates the comment with the pseudo query in the event starting at position 436. The --base64-output=decode-rows argument tells mysqlbinlog not to include a base64 encoded version of the events in row format. The --start-position and --stop-position arguments specify the start and stop offsets in bytes.
The most interesting event in the transaction is the one starting with the comment # at 436 which means the event starts at offset 436 (in bytes). It is written as a pseudo update statement with the WHERE part showing the values before the change and the SET part the values after the update. This is also known as the before and after images.
If you use encrypted binary logs, you cannot directly use mysqlbinlog to read the files. One option is to make mysqlbinlog connect to the server and read them which returns the logs unencrypted. Another option if you use the keyring_file plugin to store the encryption key is to use Python or standard Linux tools to decrypt the file. These methods are described in https://mysql.wisborg.dk/decrypt-binary-logs and https://mysqlhighavailability.com/how-to-manually-decrypt-an-encrypted-binary-log-file/.
Show Connected Replicas
Another useful command is to ask a source of replication to list all replicas connected to it. This can be used for auto-discovering a replication topology in monitoring tools.
If no replicas are connected at the time the statement is executed, the result will be empty. The Server_id and Master_id columns are the values of the server_id system variable on the replica and source, respectively. The Host is the hostname of the replica as specified with the report_host option. Similarly, the Port column is the replica’s report_port value. Finally, the Slave_UUID column is the value of @@global.server_uuid on the replica.
The only group of SHOW statements left consists of various statements to get information about privileges, users, open tables, warnings, and errors.
Miscellaneous Statements
Miscellaneous SHOW statements
SHOW Statement | Description |
|---|---|
PRIVILEGES | Lists the available privileges, which context they apply to, and for some privileges a description of what the privilege controls. |
CREATE USER | Returns the CREATE USER statement for an account. |
GRANTS | Lists the assigned privileges for the current account or another account. |
OPEN TABLES | Lists the tables in the table cache, the number of table locks or lock requests, and whether the name of the table is locked (happens during DROP TABLE or RENAME TABLE). |
WARNINGS | Lists the warnings and errors and if sql_notes is enabled (the default) notes for the last executed statement. |
ERRORS | Lists the errors for the last executed statement. |
The three most commonly used of the miscellaneous SHOW statements are SHOW CREATE USER, SHOW GRANTS, and SHOW WARNINGS.
The print_identified_with_as_hex variable (available in 8.0.17 and later) is enabled to return the password digest in hexadecimal notation. This is the preferred when returning the value to the console as the digest may include unprintable characters. The SHOW CREATE USER output is equivalent to how the user was created and can be used to create a new user with the same settings, including password.
Specifying the authentication digest in hexadecimal notation when creating a user is only supported in MySQL 8.0.17 and later.
The SHOW WARNINGS statement is one of the most underused statements in MySQL. If MySQL encounters a problem but is able to continue, it will generate a warning but otherwise complete the execution of the statement. While the statement completes without error, the warning may be a sign of a larger problem, and it is best practice to always check for warnings and aim at never having warnings in the queries executed by your application.
MySQL Shell does not support the SHOW WARNINGS statement as it will automatically fetch warnings if the W mode has been enabled (the default) and otherwise not make the warnings available. The statement is however still useful in the legacy mysql command-line client and from some connectors such as MySQL Connector/Python.
Using SHOW WARNINGS to identify problems
The example starts with the SQL mode set to the default in MySQL 8. First, the SQL mode is changed using the sys.list_drop() function to remove the STRICT_TRANS_TABLES mode which triggers a warning as disabling the strict mode should be done together with other modes as they will be merged together at a later date. Then the population of one of the cities in the world.city table is updated, but the calculation ends up dividing with 0 which triggers two warnings. One warning is for division by 0 which is not defined, so MySQL uses a NULL value which causes the second warning as the Population column is a NOT NULL column. The result is that a population of 0 is assigned to the city, which is probably not what is expected in the application. This also illustrates why it is important to enable the strict SQL mode as that would have made the division by zero an error and prevented the update.
Do not disable the STRICT_TRANS_TABLES SQL mode as it makes it more likely that you end up with invalid data in your tables.
Summary
This chapter introduced the SHOW statements which date back to before the Information Schema and Performance Schema were implemented. Nowadays, it is often better to use the underlying data sources in the Information Schema and Performance Schema. The mapping between the SHOW statements and data sources was given in the first two sections.
There are also some SHOW statements that return data that cannot be accessed through other sources. A commonly used feature is the InnoDB monitor report from InnoDB obtained with the SHOW ENGINE INNODB STATUS statement . The report is split into several sections, of which some will be used when investigating performance and lock issues.
There are also some statements for replication and the binary logs that are useful. The most commonly used statement of these is SHOW BINARY LOGS which lists the binary logs that MySQL knows of for that instance. The information includes the size and whether the log is encrypted. You can also list events in the binary logs or relay logs, but in practice the mysqlbinlog utility is usually a better option.
Finally, a group of miscellaneous SHOW statements were covered. The three most used of these are SHOW CREATE USER to display a statement that can be used to recreate the user, SHOW GRANTS that returns the privileges assigned to a user, and SHOW WARNINGS which lists the errors, warnings, and by default notes that occurred for the last executed query. Checking the warnings is an often-overlooked aspect of executing queries as warnings can be an indication that the result of a query is not what you expect. It is recommended to always check for warnings and to enable the STRICT_TRANS_TABLES SQL mode.
The final chapter about sources of information is about the slow query log.