SHOW STATUS

This MySQL statement displays status information and variables from the server.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

SHOW [GLOBAL|LOCAL|SESSION] STATUS [LIKE 'pattern'|WHERE expression]

Explanation

This MySQL statement displays status information and variables from the MySQL server. You can reduce the number of variables shown with the LIKE clause, based on a naming pattern for the variable name. Similarly, the WHERE clause may be used to refine the results set.

Examples

Here is an example of how you can use this MySQL statement with the LIKE clause:

SHOW STATUS LIKE '%log%';

+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Binlog_cache_disk_use        | 0     | 
| Binlog_cache_use             | 0     | 
| Com_show_binlog_events       | 0     | 
| Com_show_binlogs             | 0     | 
| Com_show_engine_logs         | 0     | 
| Innodb_log_waits             | 0     | 
| Innodb_log_write_requests    | 0     | 
| Innodb_log_writes            | 1     | 
| Innodb_os_log_fsyncs         | 3     | 
| Innodb_os_log_pending_fsyncs | 0     | 
| Innodb_os_log_pending_writes | 0     | 
| Innodb_os_log_written        | 512   | 
| Tc_log_max_pages_used        | 0     | 
| Tc_log_page_size             | 0     | 
| Tc_log_page_waits            | 0     | 
+------------------------------+-------+

The results show any system variable in which the variable name has the word log in it. This is a new server installation, so the results have small or zero values. If you wanted to eliminate the InnoDB logs from the results, you could use the WHERE clause like so:

SHOW STATUS 
WHERE Variable_name LIKE '%log%' 
AND Variable_name NOT LIKE '%Innodb%';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Binlog_cache_disk_use  | 0     | 
| Binlog_cache_use       | 0     | 
| Com_show_binlog_events | 0     | 
| Com_show_binlogs       | 0     | 
| Com_show_engine_logs   | 0     | 
| Tc_log_max_pages_used  | 0     | 
| Tc_log_page_size       | 0     | 
| Tc_log_page_waits      | 0     | 
+------------------------+-------+

Notice that when using the WHERE clause, the field name in the results must be given. In this case, the field name Variable_name is given. You could also give the field name Value to limit the results to entries of a certain value or range of values.

SHOW GLOBAL STATUS 
WHERE Variable_name LIKE '%log%' 
AND Variable_name LIKE '%Innodb%' 
AND Value > 100;
 
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Innodb_os_log_written | 512   | 
+-----------------------+-------+

In this example we are looking for log entries for InnoDB with a value over 100. The results consist of just one entry.

You can change many variables at server startup using options for the MySQL server daemon. You can change some of them while the daemon is running with the SET statement, without having to restart the server.