MySQL监视——状态变量 - 成就云开发者社区

MySQL除了使用日志对系统进行监视，还可以查看状态变量来监视系统。大部分的变量值以计数器的形式记录发生时间或者事件的数量。系统变量的范围包括全局（GLOBAL）和会话范围（SESSION或LOCAL）默认为会话范围。注意，一些变量仅具有全局范围，无论使用哪个关键字都返回相同的值。

显示状态信息可以通过执行“SHOW STATUS”语句、查询Performance_Schema里面的表，或者使用“mysqladmin”查看。例如，使用“SHOW STATUS”语句，

代码语言：javascript

复制

 MySQL  localhost:3306 ssl  SQL > SHOW STATUS\G
*************************** 1. row ***************************
Variable_name: Aborted_clients
        Value: 0
*************************** 2. row ***************************
Variable_name: Aborted_connects
        Value: 1
*************************** 3. row ***************************
Variable_name: Acl_cache_items_count
        Value: 0
*************************** 4. row ***************************
Variable_name: Binlog_cache_disk_use
        Value: 0
*************************** 5. row ***************************
Variable_name: Binlog_cache_use
        Value: 0
......

使用performance_schema查看“global_status”或“session_status”，

代码语言：javascript

复制

MySQL  localhost:3306 ssl  SQL > SELECT * FROM performance_schema.session_status\G
*************************** 1. row ***************************
 VARIABLE_NAME: Aborted_clients
VARIABLE_VALUE: 0
*************************** 2. row ***************************
 VARIABLE_NAME: Aborted_connects
VARIABLE_VALUE: 1
*************************** 3. row ***************************
 VARIABLE_NAME: Acl_cache_items_count
VARIABLE_VALUE: 0
*************************** 4. row ***************************
 VARIABLE_NAME: Binlog_cache_disk_use
VARIABLE_VALUE: 0
*************************** 5. row ***************************
 VARIABLE_NAME: Binlog_cache_use
VARIABLE_VALUE: 0
*************************** 6. row ***************************
 VARIABLE_NAME: Binlog_stmt_cache_disk_use
VARIABLE_VALUE: 0
*************************** 7. row ***************************
 VARIABLE_NAME: Binlog_stmt_cache_use
VARIABLE_VALUE: 0
......

除了以上的两个表，还可以查看“status_by_account”，“status_by_host”，“status_by_thread”，“status_by_user”表。

使用“mysqladmin”并通过选项查看状态变量，例如，查看状态信息，

代码语言：javascript

复制

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqladmin --user=root -p status
Enter password: ****
Uptime: 1151  Threads: 3  Questions: 13  Slow queries: 0  Opens: 138  Flush tables: 3  Open tables: 57  Queries per second avg: 0.011

查看服务器的变量，及变量值，

代码语言：javascript

复制

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqladmin --user=root -p extended-status Enter password: **** +-------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Variable_name | Value | +-------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Aborted_clients | 0 | | Aborted_connects | 2 | | Acl_cache_items_count | 0 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Binlog_stmt_cache_disk_use | 0

......

查看服务器的活动线程，

代码语言：javascript

复制

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqladmin processlist --verbose --user=root -p

Enter password: ****

+----+-----------------+-----------------+----+---------+------+------------------------+-----------------------+

| Id | User            | Host            | db | Command | Time | State                  | Info                  |

+----+-----------------+-----------------+----+---------+------+------------------------+-----------------------+

| 5  | event_scheduler | localhost       |    | Daemon  | 1369 | Waiting on empty queue |                       |

| 9  | root            | localhost:55557 |    | Sleep   | 1025 |                        |                       |

| 14 | root            | localhost:55689 |    | Query   | 0    | init                   | show full processlist |

+----+-----------------+-----------------+----+---------+------+------------------------+-----------------------+

此外，使用“mysqladmin”和选项能够输出非常有效的监视信息，例如，

代码语言：javascript

复制

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqladmin extended -i100 --relative --user=root -p

可以每100秒显示当前变量值和上一次变量值的不同。