MySQL数据库巡检步骤

MySQL巡检
系统基本信息

机型号
IP
CPU
内存
磁盘
(业务)系统信息
操作系统
主机名

操作系统巡检

检查内容 说明 检查方法 结果（异常需详细说明） 正常输出结果
系统配置检查 操作系
统版本 #uname –a □正常 □异常 显示系统版本和核心补丁信息
系统I/O检查 检查系统I/O负载情况 #iostat、top □正常 □异常 Top中CPUS中的%wa与iostat中的%iowait不应同时有值
系统CPU检查 检查系统CPU负载情况 #top、
Cat/proc/cpuinfo □正常 □异常 %wa（等待）的时间无或者较小
系统内存检查 检查系统内存负载情况 #vmstat、free □正常 □异常 Swap中的so有值证明内存不足
系统网络检查 检查系统网络连通性 #ping、telnet □正常 □异常 网络可连通

MySQL监控类巡检
1、TPS/QPS
1.1、TPS
tps: Transactions Per Second，每秒事务数；
TPS = Com_commit/s + Com_rollback/s

Com_commit /s= mysqladmin extended-status --relative --sleep=1|grep -w Com_commit
Com_rollback/s = mysqladmin extended-status --relative --sleep=1|grep -w Com_rollback

理想状态：
参照日常数，当出现峰值时告警

1.2、QPS
qps: Queries Per Second每秒查询数；
QPS = mysqladmin extended-status --relative --sleep=1|grep -w Questions

理想状态：
参照日常数，当出现峰值时告警

2、innodb_buffer_pool_status

innodb_buffer_pool_reads: 平均每秒从物理磁盘读取页的次数 
innodb_buffer_pool_read_requests: 平均每秒从innodb缓冲池的读次数（逻辑读请求数）
innodb_buffer_pool_write_requests: 平均每秒向innodb缓冲池的写次数
innodb_buffer_pool_pages_dirty: 平均每秒innodb缓存池中脏页的数目
innodb_buffer_pool_pages_flushed: 平均每秒innodb缓存池中刷新页请求的数目

innodb缓冲池的读命中率
innodb_buffer_read_hit_ratio = ( 1 - Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100
Innodb缓冲池的利用率
Innodb_buffer_usage =  ( 1 - Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total) * 100

理想状态：
参照日常数，当出现峰值时告警；
当缓冲池的读命中率较低时可以适当的增大innodb_buffer_pool或检查SQL语句

3、slow_queries status
执行时间超过long_query_time秒的查询的个数

理想状态：
查询值波动不大，若出现大量慢查询，需检查近期上线的SQL语句

4、Sort status
mysql> show global status like ‘%sort%’ ;

Sort_merge_passes
Sort_range
Sort_rows
Sort_scan

理想状态：
Sort_merge_passes表示当需要排序时，在排序缓冲中无法将结果完全存放，则将会基于磁盘创建临时文件进行排序。如果该值较高，则应提高sort_buffer_size大小。最好的办法是找到是由哪些排序SQL造成的。

5、select status
5.1、select_scan
对表进行完全扫描的联接的数量
mysql> show global status like ‘select_scan’ ;

理想状态：
恒定的值，不会随时间增长而有明显的增长。

5.2、select_full_join
没有主键（key）联合（Join）的执行。该值可能是零。这是捕获开发错误的好方法，因为一些这样的查询可能降低系统的性能。
mysql> show global status like ’ select_full_join ’ ;

理想状态：
出现full join的次数，如果该值不为0，需要检查表上是否有合适的索引

6、Lock status
mysql> show global status like ’ table_locks%’ ;
table_locks_immediate表示立即释放表锁数;
table_locks_waited表示需要等待的表锁数;

理想状态：
table_locks_waited如果该值很大，而且性能很慢，建议针对业务拆分主表

7、Thread status
mysql> show global status like ‘thread%‘;
Threads_cached
Threads_connected
Threads_created
Threads_running

理想状态：
threads_created表示创建过的线程数，如果发现threads_created值过大的话，表明mysql服务器一直在创建线程，这也是比较耗资源，可以适当增加配置文件中thread_cache_size值，

MySQL参数类巡检
1、Connections settings
1.1、max_connections
查询服务器最大连接数
mysql> show variables like ‘max_connections‘;
查询服务器响应的最大连接数
mysql> show global status like ‘max_used_connections’;

理想状态：
 max_used_connections / max_connections * 100% ≈ 85%；
error_log中未出现MySQL: ERROR 1040: Too many connections该类错误。

1.2、Aborted_connects
查询服务器尝试已经失败的MySQL服务器的连接的次数；
mysql> show global status like ‘Aborted_connects’;

理想状态：
固定值，不随时间增长而增长（或增长幅度较小），若该数出现持续增长，需检查
连接状况或错误日志，定位异常连接主机（一般为应用账号密码问题）。

1.3、Aborted_clients

查询服务器异常客户端数（由于客户没有正确关闭连接已经死掉，已经放弃的连接数量）；
mysql> show global status like ‘Aborted_clients’;

理想状态：
固定值，不随时间增长而增长（或增长幅度较小），若该数出现持续增长，需检查
连接状况或错误日志，定位异常连接主机（一般为程序逻辑开启连接后未正常关闭）。

2、innodb settings
2.1、innodb_buffer_pool_size
查询innodb存储引擎数据缓存大小
mysql> show variables like ‘innodb_buffer_pool_size ‘;

理想状态：
建议设置为物理内存的60%~80%，或根据业务决定

2.2、innodb_io_capacity
配置刷新I/O操作性能，此参数可以限制I/O操作
mysql> show variables like ‘innodb_io_capacity ‘;

理想状态：
根据实际磁盘情况设置
innodb_io_capacity 磁盘配置
200 单盘SAS/SATA
2000 SAS*12 RAID 10
5000 SSD
20000 FUSION-IO

2.3、innodb_flush_neighbors
刷新相同extent脏块
mysql> show variables like ‘innodb_flush_neighbors ‘;

理想状态：
默认为1，建议开启，在SSD环境建议配置为0

2.4、innodb_print_all_deadlocks
记录死锁记录
mysql> show variables like ‘innodb_print_all_deadlocks ‘;

理想状态：
默认关闭，建议开启

2.5、innodb_lock_wait_timeout
InnoDB行锁等待时间
mysql> show variables like ‘innodb_lock_wait_timeout ‘;

理想状态：
默认为50秒，建议配置为5秒

2.6、innodb_flush_method
配置刷新方法
mysql> show variables like ‘innodb_flush_method ‘;

理想状态：
Linux系统MySQL建议配置为：O_DIRECT

3、Log Settings
3.1、binlog settings
3.1.1、binlog_format
binlog日志格式
mysql> show variables like ‘binlog_format ‘;

理想状态：
建议配置为row格式，特殊业务情况另考虑
3.1.2、binlog_rows_query_log_events
写log 事件（例如 行操作日志时间）到binglog
mysql> show variables like ‘binlog_rows_query_log_events ‘;

理想状态：
默认为：0，建议配置为：1
注：此参数需要在binlog格式为ROW下配置

3.1.3、sync_binlog
控制binlog 同步磁盘方式
当sync_binlog=1时，所有的事务都在提交前写入binlog。因此即使binlog事件遇到意外重启，一些在prepared状态的binlog会丢失。这导致服务器在恢复数据时自动回滚这些事务。这确保了从binlog不丢失事务，因此是最安全的选项。事实上，这增加了同步到磁盘的总次数。但是从MySQL5.6开始，已经支持组提交和合并同步了，这使得出现性能问题的可能性最小化了。
当sync_binlog=0时，mysql-server并不把binlog同步到磁盘，而是依赖操作系统把binlog的内容同步到磁盘。因此，当出现掉电或操作系统崩溃时，很可能出现已经提交的事务没有被同步到磁盘的情况。因此mysql在自动恢复时无法恢复这些事务，他们从binlog中丢失了。
所以说，新的默认设置sync_binlog=1更安全。

mysql> show variables like ‘binlog_rows_query_log_events ‘;

理想状态：
设置sync_binlog=1更安全。

3.1.4、expire_logs_days
自动清理二进制日志的天数
mysql> show variables like ‘expire_logs_days ‘;

理想状态：
根据备份策略与业务要求设置（如备份保留的天数是一周，则expire_logs_days可设置为>7，如：8，业务要求随时能提取1年内的数据，则设置>365）。

3.1.5、master_info_repository
slave 记录 master 状态和连接信息是否放在文件（master.info）或者是写表（mysql.slave_master_info）
mysql> show variables like ‘master_info_repository ‘;

理想状态：
默认为：file，建议配置为：table

3.1.6、relay_log_info_repository
slave 上的relay log的位置信息写在文件（relay-log.info）或者是写表（mysql.slave_relay_log_info）
mysql> show variables like ‘relay_log_info_repository ‘;

理想状态：
默认为：file，建议配置为：table

3.1.7、slave_parallel_type& slave_parallel_workers
并行复制方法（库级别，表级别）
mysql> show variables like ‘slave_parallel_type ‘;
并行复制线程数
mysql> show variables like ‘slave_parallel_workers ‘;

理想状态：
slave_parallel_type
默认为：DATABASE，配置为：LOGICALC_CLOCK
Slave_parallel_workers
默认为：0，配置为：16

3.1.8、relay_log_recovery
在服务启动时自动执行relay log 恢复操作
mysql> show variables like ‘relay_log_recovery ‘;

理想状态：
默认关闭，建议开启

3.2、slowlog settings
4.2.1、slow_query_log
是否开启慢查询日志
mysql> show variables like ‘slow_query_log ‘;

理想状态：
默认为关闭慢查询日志，建议开启

3.2.2、Others
slow_query_log_file
慢查询日志记录文件

long_query_time
设置慢查询日志的时间阀值（单位：秒）

min_examined_row_limit
设置检查行数小于多少行数的查询不记录到慢查询日志

log_queries_not_using_indexes
开启后记录没有使用索引查找说有行的查询；也会记录使用索引查询了所有行的查询

理想状态：
根据业务需求设置

3.3、generallog settings
记录所有SQL语句，包含查询甚至语法错误的语句都会被记录
mysql> show variables like ‘generallog settings ‘;

理想状态：
不建议开启。在做测试的时候想知道MySQL都执行了什么命令的时候可以开启。
4、mysqld settings
以下参数建议在配置文件中体现：

参数项 参考值 参数解释
socket /tmp/mysql.sock 在unix环境下，指定用于本地连接的套接字文件
pid_file /opt/mysql/mysql.pid MySQL的进程文件路径名称
port 3306 MySQL的监听端口
log_bin /opt/mysql/data/bin.log 开启数据库二进制
log_bin_index /opt/mysql/data/bin.index 二进制日志文件名的索引文件
server-id 2 服务器ID，在复制环境中主从服务器的唯一标识
datadir /opt/mysql/data MySQL数据目录路径
user mysql MySQL服务的运行用户
interactive_timeout 300 关闭一个交互式连接等待的时间
wait_timeout 300 关闭一个非交付时连接等待的时间
read_buffer_size 16777216 MyISAM表顺序扫描时分配的读入缓冲区大小
read_rnd_buffer_size 33554432 MyISAM表随机扫描时分配的读入缓冲区大小
sort_buffer_size 33554432 每个回话进行排序的缓存大小
lower_case_table_names 1 表名大小写敏感设置
character_set_server utf8mb4 MySQL服务器的默认字符集
skip_name_resolve 1 客服端连接数据库时，不进行主机名解析
max_connections 2000 MySQL服务器允许的客服端最大连接数
transaction_isolation READ-COMMITTED MySQL服务器的默认事务隔离级别
join_buffer_size 134217728 不使用索引的连接、普通索引、范围索引扫描的缓冲区最小值
tmp_table_size 67108864 内存临时表的最大大小
tmpdir /tmp MySQL 临时文件和临时表存放目录
max_allowed_packet 16777216 设置在网络传输中一次消息传输量的最大值

Replication巡检
查看master状态
（master操作）
mysql> show master status\G

查看slave状态
（slave操作）
mysql> show slave status\G

理想状态：
1、线程正常：Slave_IO_Running、Slave_SQL_Running（状态YES）；
2、未出现延迟状态：Seconds_Behind_Master 为0或较小、Master_Log_File与Relay_Master_Log_File相同；
3、 Last_Error无值，若报错的话根据报错信息与对应数据字典查看报错信息。
查看slave_hosts状态
（master操作）
mysql> show slave hosts\G

备份巡检
1、不管库数据量的大小，都应该有备份策略，并按时执行；
2、规范化备份路径与文件名；
3、定期进行备份有效性检测；
4、定期对备份保存数量检查；
5、定期执行灾难演练；

权限巡检
1、收集现有系统所有用户权限；
2、确定应用侧业务需求与权限是否匹配；
3、回收额外权限；
4、维护权限文档。

其他巡检
1、高可用架构巡检
2、数据库中间件巡检
其他规范
1、统一使用二进制包安装（形成软件库）
2、统一软件目录
3、统一数据文件目录
4、统一日志文件目录
5、统一其他文件位置（sock,pid）
6、统一server_id命名方式