MySQL Cookbook, 2nd Edition

Monitoring the MySQL Server

Problem

You want to find out how the server was configured or monitor its state.

Solution

SHOWVARIABLESand SHOWSTATUS are useful for this.

Discussion

The SHOW VARIABLES and SHOW STATUS statements provide server configuration and performance information:

mysql>SHOW VARIABLES;
+---------------------------------+-------------------+
| Variable_name                   | Value             |
+---------------------------------+-------------------+
| back_log                        | 50                |
| basedir                         | /usr/local/mysql/ |
| bdb_cache_size                  | 8388600           |
| bdb_log_buffer_size             | 0                 |
| bdb_home                        |                   |
...
mysql> SHOW /*!50002 GLOBAL */ STATUS;
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| Aborted_clients          | 319      |
| Aborted_connects         | 22       |
| Bytes_received           | 32085033 |
| Bytes_sent               | 26379272 |
| Connections              | 65684    |
...

Both statements allow a LIKE ' pattern ' clause that takes an SQL pattern. In that case, only rows for variable names that match the pattern are returned.

The /*!50002 GLOBAL */ comment is present in the SHOW STATUS statement due to a change made in MySQL 5.0.2; before MySQL 5.0.2, status variables were global (server-wide values). In 5.0.2, status variables have global and session (per-connection) values, and SHOW STATUS has been extended to take GLOBAL or SESSION modifiers, with the default being, if neither is given, to display the session values. The comment causes servers from MySQL 5.0.2 and up to display the global values. Servers before 5.0.2 ignore ...