MySql巡检脚本

查询MySql所有状态

show global status; 或者select * from `performance_schema`.`global_status`;

检查MySql数据库连接

select * from `performance_schema`.`global_status` where variable_name = 'Aborted_clients'; --由于客户端没有正确关闭连接导致客户端终止而中断的连接数
select * from `performance_schema`.`global_status` where variable_name = 'Aborted_connects'; --试图连接到MySQL服务器而失败的连接数
select * from `performance_schema`.`global_status` where variable_name = 'Max_used_connections';-- 服务器启动后已经同时使用的连接的最大数量
select * from `performance_schema`.`global_variables` where variable_name = 'max_connections'; --最大连接数

检查MySql数据库线程明细

select * from `performance_schema`.`global_status` where variable_name = 'Threads_cached'; --线程缓存内的线程数量
select * from `performance_schema`.`global_status` where variable_name = 'Threads_connected';-- 当前打开的连接数量
select * from `performance_schema`.`global_status` where variable_name = 'Threads_created'; --创建用来处理连接的线程数
select * from `performance_schema`.`global_status` where variable_name = 'Threads_running'; --激活的（非睡眠状态）线程数

检查MySql数据库明细

select schema_name from `information_schema`.`SCHEMATA`; --查询所有数据库
select * from `information_schema`.`TABLES` where table_schema = 数据库名; --查询数据库所有表
检查MySql锁信息
select * from `performance_schema`.`global_status` where variable_name = 'Table_locks_waited'; --等待表锁的次数
select * from `performance_schema`.`global_status` where variable_name = 'Table_locks_immediate';-- 立即获得表锁的次数
select * from `performance_schema`.`global_status` where variable_name = 'Innodb_row_lock_current_waits'; --当前等待锁的行锁数量
select * from `performance_schema`.`global_status` where variable_name = 'Innodb_row_lock_time'; --请求行锁总耗时
select * from `performance_schema`.`global_status` where variable_name = 'Innodb_row_lock_time_avg'; --请求行锁平均耗时
select * from `performance_schema`.`global_status` where variable_name = 'Innodb_row_lock_time_max'; --请求行锁最久耗时
select * from `performance_schema`.`global_status` where variable_name = 'Innodb_row_lock_waits'; --行锁发生次数

检查等待事件

select * from `performance_schema`.`global_status` where variable_name = 'Innodb_buffer_pool_wait_free'; --等待InnoDB缓冲池清空
select * from `performance_schema`.`global_status` where variable_name = 'Innodb_log_waits'; --等待InnoDB日志缓冲区清空

检查MySQL数据库请求缓存命中

select * from `performance_schema`.`global_status` where variable_name = 'Qcache_hits'; --查询缓存被访问的次数
select * from `performance_schema`.`global_status` where variable_name = 'Qcache_total_blocks'; --查询缓存内的总块数
select * from `performance_schema`.`global_status` where variable_name = 'Qcache_free_blocks'; --查询缓存内自由内存块的数量
select * from `performance_schema`.`global_status` where variable_name = 'Qcache_lowmem_prunes'; --由于内存较少从缓存删除的查询数量

检查MySql临时表

select * from `performance_schema`.`global_status` where variable_name = 'Created_tmp_disk_tables';-- 服务器执行语句时在硬盘上自动创建的临时表的数量
select * from `performance_schema`.`global_status` where variable_name = 'Created_tmp_tables';-- 服务器执行语句时自动创建的内存中的临时表的数量
select * from `performance_schema`.`global_status` where variable_name = 'Created_tmp_files'; --mysqld已经创建的临时文件的数量