Table of Contents for

MySQL in a Nutshell, 2nd Edition

This statement displays status information and variables from the 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. Here is an example of how you can use this 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 we wanted to eliminate the InnoDB logs from the results, we 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 values over 100. The results consist of just one entry.

You can change many variables at server startup using options for the MySQL server daemon. See Chapter 15 for more details. You can change some of them while the daemon is running with the SET statement, without having to restart the server. That statement is covered earlier in this chapter.