MySQL - SHOW STATUS Statement

---

---

MySQL SHOW STATUS Statement

The SHOW STATUS Statement displays the name and values of variables that gives you information about the server status. This statement has GLOBAL and SESSION modifier by specifying them you can retrieve server and session information using these.

Syntax

Following is the syntax of the SHOW STATUS Statement −

SHOW [GLOBAL | SESSION] VARIABLES
[LIKE 'pattern' | WHERE expr]

Example

You can get the variables that provide server information as follows −

SHOW STATUS\G;

Output

Once the query is executed, it will genrate the following output −

************ 1. row ************
Variable_name: Aborted_clients
        Value: 0
************ 2. row ************
Variable_name: Aborted_connects
        Value: 0
************ 3. row ************
Variable_name: Acl_cache_items_count
        Value: 3
************ 4. row ************
Variable_name: Binlog_cache_disk_use
        Value: 0
************ 5. row ************
Variable_name: Binlog_cache_use
        Value: 26
************ 6. row ************
Variable_name: Binlog_stmt_cache_disk_use
Value: 0
************ 7. row ************
Variable_name: Binlog_stmt_cache_use
        Value: 0
************ 8. row ************
Variable_name: Bytes_received
        Value: 5872
************ 9. row ************
Variable_name: Bytes_sent
        Value: 510165

The GLOBAL modifier

As mentioned above, if you use the GLOBAL modifier of this statement the variables that listed provides status information about the whole server i.e., all connections to MYSQL.

You can retrieve the list GLOBAL VARIABLES in MySQL using the GLOBAL clause as shown below −

SHOW GLOBAL STATUS\G;

Output

Following is the output of the above query −

************ 1. row ************
Variable_name: Aborted_clients
        Value: 0
************ 2. row ************
Variable_name: Aborted_connects
        Value: 0
************ 3. row ************
Variable_name: Acl_cache_items_count
        Value: 3
************ 4. row ************
Variable_name: Binlog_cache_disk_use
        Value: 0
************ 5. row ************
Variable_name: Binlog_cache_use
        Value: 26
************ 6. row ************
Variable_name: Binlog_stmt_cache_disk_use
        Value: 0
************ 7. row ************
Variable_name: Binlog_stmt_cache_use
        Value: 0
************ 8. row ************
Variable_name: Bytes_received
        Value: 5394
************ 9. row ************
Variable_name: Bytes_sent
        Value: 356988
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . .

The SESSION modifier

As mentioned above, if you use the SESSION modifier of this statement, the variables that listed provides status information about the current session.

You can retrieve the list SESSION VARIABLES in MySQL using the SESSION class as shown below −

SHOW SESSION STATUS\G;

Output

The above query produces the following output −

************ 1. row ************
Variable_name: Aborted_clients
        Value: 0
************ 2. row ************
Variable_name: Aborted_connects
        Value: 0
************ 3. row ************
Variable_name: Acl_cache_items_count
        Value: 3
************ 4. row ************
Variable_name: Binlog_cache_disk_use
        Value: 0
************ 5. row ************
Variable_name: Binlog_cache_use
        Value: 26
************ 6. row ************
Variable_name: Binlog_stmt_cache_disk_use
        Value: 0
************ 7. row ************
Variable_name: Binlog_stmt_cache_use
        Value: 0
************ 8. row ************
Variable_name: Bytes_received
        Value: 4833
************ 9. row ************
Variable_name: Bytes_sent
Value: 372265
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . .

The LIKE clause

Using the LIKE clause, you can specify a pattern to retrieve names and values of specific variables.

SHOW STATUS LIKE 'Table%';

Output

After executing the above query, it generates the output shown below −

Variable_name	Value
Table_locks_immediate	28
Table_locks_waited	0
Table_open_cache_hits	1313
Table_open_cache_misses	52
Table_open_cache_overflows	0

The WHERE clause

You can use the WHERE clause of the SHOW STATUS statements to retrieve names of the variables which match the specified condition.

SHOW GLOBAL VARIABLES WHERE value = 'MYSQL';

Output

Once the query is executed, it will produce the following output −

Variable_name	Value
shared_memory_base_name	MYSQL
socket	MYSQL