mysql巡检做些什么

[TOC]

1.系统层面

如果有监控系统（比如zabbix、Prometheus等），可查看监控历史曲线，否则只能通过shell查看当前使用率
查看监控，主要留意 warning 或以上级别的告警，监控基本上可以覆盖 95% 以上的日常巡检需求

1.1.CPU

严重程度：★★★★★
问题描述：通过监控找到cpu平均使用率 > 80% 的实例，进一步分析是否慢查询导致的cpu飙升，或者cpu配置过低所致
检查方法：查看监控曲线（若有），或执行 top
影响范围：如下表示

服务器ip	环境	cpu总核数	平均使用率
x.x.x.x	uat	32	16.03%
x.x.x.x	prod	32	21.84%

1.2.内存

严重程度：★★★★★
问题描述：通过监控找到内存平均使用率 > 80% 的实例，检查是否有内存不够用的问题，该扩容的扩容，防止OOM。若内存充裕而使用到 swap，大概率是由于未正确设置 numa 或 vm.swappiness 导致的
检查方法：查看监控曲线（若有），或执行 free -m
影响范围：如下表示

服务器ip	环境	内存总大小(GB)	平均使用率
x.x.x.x	uat	16	76.03%
x.x.x.x	prod	32	21.84%

1.3.磁盘

严重程度：★★★★★
问题描述：通过监控找到磁盘当前使用率 > 80% 的实例，节前巡检则关注使用率>70%的示例，提前扩容，避免长假期间发生告警
检查方法：查看监控曲线（若有），或执行 df -Th
影响范围：如下表示

服务器ip	环境	挂载点	磁盘总大小(GB)	剩余空间(GB)	可用空间率
x.x.x.x	uat	/	40	14.02	35.05%
x.x.x.x	prod	/data	200	43.68	21.84%

1.4.网络带宽

严重程度：★★★★★
问题描述：通常造成服务器带宽过高是由多种因素引起的，比如传输的数据量过大（比如查询结果集过大）、配置不当、网络攻击等
检查方法：查看监控曲线（若有）

2.应用层面

2.1.实例状态

严重程度：★★★★
问题描述：实例状态，一般来说就是检查 mysqld 的存活，有条件的话可以分析其是否健康
检查方法：执行如下sql查看服务运行信息

select @@innodb_version, @@port, @@datadir, now()-interval variable_value second as uptime 
from performance_schema.global_status where variable_name='uptime';

影响范围：如下表示

服务器ip	环境	mysql版本	服务端口	数据目录	运行时间
x.x.x.x	uat	5.7.42-45	3306	/var/lib/mysql/data/	2023-12-06 10:50
x.x.x.x	prod	5.7.42-45	3306	/var/lib/mysql/data/	2024-01-01 23:59

2.2.复制状态

严重程度：★★★★
问题描述：检查主从同步的复制状态、容灾的DTS状态等，看是否正常
检查方法：在从库上执行 show slave status\G; 若 Slave_IO_Running 与 Slave_SQL_Running 均为 Yes 则判定同步无异常，若 Seconds_Behind_Master 为 0 则判定同步无延迟
影响范围：如下表示

主库ip	从库ip	同步类型	同步状态	备注
x.x.x.x	y.y.y.y	主主同步	正常or异常	xxx

2.3.高可用状态

严重程度：★★★★
问题描述：通过巡检证明数据库是"可切换"状态，比如 MHA 架构可检查这三个脚本执行的结果：

/usr/bin/masterha_check_ssh --conf=/etc/mha/masterha_default.cnf
/usr/bin/masterha_check_repl --conf=/etc/mha/masterha_default.cnf
/usr/bin/masterha_check_status --conf=/etc/mha/masterha_default.cnf

影响范围：若无HA则不适用

2.4.VIP 状态

严重程度：★★★★
问题描述：除了检查当前在用的VIP状态，如果有双网络冗余链路（即双VIP），还要有一个冗余vip的巡检机制，因为冗余vip故障对业务是没有感知的，可以定制一个定时的探测和告警任务
影响范围：若无vip则不适用

3.可用性巡检

3.1.检查自增id上限

严重程度：★★★★★
问题描述：整数类型的字段能够保存的数值是有限的，超出最大值的数据无法写入表中，数值类型溢出会导致程序报错
检查方法：检查每张表的自增id是否即将达到最大值

SELECT a.table_schema, a.table_name, b.column_type, a.auto_increment 
FROM information_schema.tables a join information_schema.columns b on a.table_schema=b.table_schema and a.table_name=b.table_name 
WHERE b.extra='auto_increment' 
and (    (b.column_type like 'int%)' and a.auto_increment > 1900000000) 
      or (b.column_type like 'int%unsigned' and a.auto_increment > 4000000000) 
      or (b.column_type like 'bigint%)' and a.auto_increment > 8000000000000000000) 
      or (b.column_type like 'bigint%unsigned' and a.auto_increment > 17000000000000000000) 
    );

影响范围：如下表示

服务器ip	环境	库名	表名	字段类型	当前自增值	最大可用值
x.x.x.x	uat	db1	table	int	-	2147483647
x.x.x.x	prod	db2	table	bigint	-	9223372036854775807

4.可靠性巡检

4.1.检查核心参数

严重程度：★★★★★
问题描述：若核心参数配置不当，会造成性能或安全问题
检查方法：重点关注与性能有关的参数，包含并不仅限于以下这些：

wait_timeout = 1500                 #.关闭非交互连接之前等待活动的秒数,默认28800秒(8h)
interactive_timeout = 1500          #.关闭交互式连接前等待活动的秒数,默认28800秒(8h)
connect_timeout = 60                #.连接超时时间
lower_case_table_names = x          #.是否开启大小写敏感,0区分/1不区分
log-bin = /path/mysql-bin           #.配置binlog日志路径,默认datadir
binlog_format = row                 #.设置binlog日志格式
binlog_row_image = full             #.默认full(在binlog为row格式下)会记录所有的行信息
expire_logs_days = x                #.超过x天的binlog删除
sync_binlog = 1                     #.刷binlog方式,直接影响性能和完整性,0高性能/1高安全
skip-slave-start = 1                #.禁用之后,slave进程会随着mysql启动而启动

gtid-mode = on                      #.是否开启gtid功能
enforce-gtid-consistency = on       #.强制gtid一致性

slow_query_log = 1                      #.是否启用慢查询,默认0不启用
long_query_time = 3                     #.慢查询阀值时间,默认超过10秒的查询才写日志
log_output = file                       #.默认FILE将日志存入文件,改为TABLE则写入系统表
slow_query_log_file = /path/slow.log    #.开启慢查询日志,记录查询过长的语句以便优化

max_connections = 3000                  #.所有用户同时打开的最大连接数,默认151
max_user_connections = 2000             #.每个用户同时打开的最大连接数
max_connect_errors = 10000              #.针对某个IP连接中断与mysql服务连接的次数

innodb_buffer_pool_size = 2g            #.保存索引和原始数据的缓冲池大小,建议为物理内存的50-60%,默认8M
innodb_log_file_size = 512M             #.日志文件的大小,更大的设置可以提高性能,但也会增加故障恢复所需的时间
innodb_flush_log_at_trx_commit = 1      #.log buffer刷新到磁盘的方式,默认1最安全,0性能最好,其他值则折衷
innodb_file_per_table = 1               #.设置innodb引擎独立表空间,建议启用

local-infile = 0                        #.设置为0可禁用客户端load data local infile
skip_symbolic_links = yes               #.禁用符号链接以防止各种安全风险

4.2.检查主备参数是否一致

严重程度：★★★★
问题描述：若从库的参数与主库不一致，一旦发生故障转移就会存在风险
检查方法：检查这个主要是避免主备切换后发生主从复制异常，包含并不仅限于以下这些：

server_id=2313306                       #.主库与从库不可相同
slave_parallel_workers = 8              #.
slave_parallel_type = LOGICAL_CLOCK     #.
master_info_repository = TABLE          #.
relay_log_info_repository = TABLE       #.
relay_log_recovery = 1                  #.

4.3.检查运行参数与持久化配置是否一致

严重程度：★★★★
问题描述：在 5.7 及以前，mysql不支持参数持久化，修改参数通常都是分两步，比如先动态设置 set global innodb_buffer_pool_size=128G; 然后登陆服务器修改 my.cnf，如果忘记持久化到my.cnf，则mysql一旦发生crash就会导致 innodb_buffer_pool_size 被还原为默认值的128M，从而造成性能问题或数据丢失风险
检查方法：检查数据库运行参数(show global variables;)和配置文件(my.cnf)参数是否一致

5.安全性巡检

5.1.检查具有super权限的账号

严重程度：★★★★★
检查方法：查询除root之外、具有super权限的账号

SELECT user,host,Super_priv FROM mysql.user WHERE Super_priv='Y' and user not in('root','mysql.session');

5.2.检查空口令或弱口令的账号

严重程度：★★★★★
检查方法：借助mysql弱口令检测工具，检查空口令、弱口令的账号
影响范围：如下表示

ip地址	服务	端口	账户名	密码	版本
x.x.x.x	MySQL	3306	root	root	5.7.33
x.x.x.x	MySQL	3306	root	123456	8.0.32

5.3.检查备份是否正常

严重程度：★★★★
检查方法：检查是否有备份机制，若有备份机制，且最后一个备份成功为正常

5.4.备份文件的恢复测试

严重程度：★★★★
问题描述：数据备份的重要性不言而喻，为了保证备份的有效性，建议对每个备份集执行恢复测试

5.5.检查防火墙是否开启

严重程度：★★★★
问题描述：检查是否开启防火墙并配置ip访问控制策略

iptables -nL
systemctl status firewalld.service

6.性能巡检

6.1.关注 top 20 大库

严重程度：★★★★★
问题描述：单实例的数据库控制在20个以内、大小500G以内

select table_schema,concat(round(sum(data_length/1024/1024),2),'MB') as db_size 
from information_schema.tables 
where table_schema not in('information_schema','mysql','performance_schema','sys','percona_schema') 
group by table_schema 
order by sum(data_length/1024/1024) desc;

6.2.关注 top 20 大表

严重程度：★★★★★
问题描述：大表在做全表扫描时非常耗费性能，执行DDL操作更是灾难，大于 100G 的表都应该评估一下，为什么这么大？是否可以迁到TiDB？是否可以归档或分库分表？
推荐建议：单表行数控制在1000w行以内、大小30G以内，针对单表超过1000w的大表，建议对历史数据进行清理或归档(改动小)、分表(推荐方案)

select table_schema,table_name,table_rows,
  sum(DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 as total_size,sum(DATA_LENGTH)/1024/1024/1024 as data_size,sum(INDEX_LENGTH)/1024/1024/1024 as index_size 
from information_schema.tables 
where table_schema not in ('information_schema','mysql','performance_schema','sys','percona_schema') 
group by table_schema,table_name 
order by table_rows desc limit 20;

6.3.关注 top 20 慢查询

严重程度：★★★★★
检查方法：基于慢查询日志，列出 top 10 慢查询

cat mysql_slow.log | grep Query_time | more

6.4.关注 top 30 执行全表扫描的SQL

严重程度：★★★★★
问题描述：因为有些 SQL 的执行计划本身其实就有问题的，这些 SQL可能当前跑得很快，但没有评估过数据量增长，当表变得越来越大时，达到一个阀值时，线上可能就会爆发 CPU 100% 的性能问题，成为爆发性杀手级慢查询。

6.5.关注 top 30 冗余索引最多的表

严重程度：★★★★
问题描述：查看冗余索引最多的30张表（超过4个以上冗余字段）

select distinct table_schema,table_name,redundant_index_name,redundant_index_columns,sql_drop_index 
from sys.schema_redundant_indexes 
where length(redundant_index_columns)-length(replace(redundant_index_columns,',',''))+1 >= 4 limit 30;

6.6.关注 top 30 无效索引

严重程度：★★★★
问题描述：查看索引使用次数最少的30张表（排除主键），请开发评估是否可以删除

select object_schema,object_name,index_name,count_read,count_write,count_fetch
from performance_schema.table_io_waits_summary_by_index_usage
where index_name <> 'PRIMARY' and index_name is not null 
order by count_read + count_write + count_fetch asc limit 30;

6.7.关注 top 30 索引最多的表

严重程度：★★★
问题描述：索引会加快查询速度，但是单表索引过多除了会占用更多的存储空间之外，还会降低数据写入性能
检查方法：查看索引最多的30张表（超过6个以上索引）

select database_name,table_name,count(*) as index_count 
from mysql.innodb_index_stats 
where stat_name='size' 
group by database_name,table_name 
having count(*) > 6 
order by index_count desc limit 30;

6.8.关注 top 30 字段最多的索引

严重程度：★★★
问题描述：查看占用字段最多的30个索引（超过6个以上字段）

select database_name,table_name,index_name,count(*)-1 as column_count 
from mysql.innodb_index_stats 
where stat_value=0 
group by database_name,table_name,index_name 
having count(*) > 6
order by column_count desc limit 30;

6.9.是否存在没有主键的表

严重程度：★★★★★
问题描述：MySQL 的玩法就是需要有主键，最好是业务无关的 int signed 自增主键
推荐建议：每张表必须要有主键，强制自增id为bigint(20)，且为主键，业务不要使用

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_name NOT IN (SELECT DISTINCT table_name FROM information_schema.columns WHERE column_key = "PRI")
AND table_schema NOT IN ('mysql', 'information_schema', 'sys', 'performance_schema');

6.10.是否存在 MyISAM 存储引擎表

严重程度：★★★
问题描述：MyISAM 基本没有好处，MySQL 8.0 已将所有系统表更换为InnoDB引擎，默认的MySQL实例将不包含任何MyISAM表，而 MySQL 5.7 可以配置 disabled_storage_engines=MyISAM 来禁用MyISAM引擎（注：MySQL 5.7 仍然有10张元数据表使用 MyISAM 存储引擎，可能会影响数据库的升级）
检查方法：执行下面语句，可以查看哪些表使用的是 MyISAM 存储引擎

select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE from information_schema.tables where ENGINE='MyISAM' limit 100;

6.11.关注 mysql 连接数

严重程度：★★★★★
问题描述：若mysql实例的最大连接数设置过低，会导致应用程序异常，而过高则会造成服务器资源不足
推荐建议：根据服务器的配置来设置，比如 4C16G 可配置 max_connections=3000，max_user_connections=2500

select @@max_connections,@@max_user_connections,count(*) as 'current_connections' from information_schema.processlist;

6.12.关注 7天以上僵尸链接

严重程度：★★★★
问题描述：关注7天以上的僵尸链接，僵尸连接过多会导致内存紧张

select id,user,host,db,command,time from information_schema.processlist where command='sleep' and time>3600*24*7;

6.13.关注 7天以内错误日志

严重程度：★★★★
问题描述：关注最近7天的错误日志

show variables like 'log_error';

7.总结和建议

暂时想到这么多，也就是抛砖引玉，性能巡检的目的是出具尽量多的数据给租户自行做性能分析

以上就是个人对 MySQL 数据库巡检需要做什么的总结，欢迎指正。

mysql巡检做些什么

1.系统层面

1.1.CPU

1.2.内存

1.3.磁盘

1.4.网络带宽

2.应用层面

2.1.实例状态

2.2.复制状态

2.3.高可用状态

2.4.VIP 状态

3.可用性巡检

3.1.检查自增id上限

4.可靠性巡检

4.1.检查核心参数

4.2.检查主备参数是否一致

4.3.检查运行参数与持久化配置是否一致

5.安全性巡检

5.1.检查具有super权限的账号

5.2.检查空口令或弱口令的账号

5.3.检查备份是否正常

5.4.备份文件的恢复测试

5.5.检查防火墙是否开启

6.性能巡检

6.1.关注 top 20 大库

6.2.关注 top 20 大表

6.3.关注 top 20 慢查询

6.4.关注 top 30 执行全表扫描的SQL

6.5.关注 top 30 冗余索引最多的表

6.6.关注 top 30 无效索引

6.7.关注 top 30 索引最多的表

6.8.关注 top 30 字段最多的索引

6.9.是否存在没有主键的表

6.10.是否存在 MyISAM 存储引擎表

6.11.关注 mysql 连接数

6.12.关注 7天以上 僵尸链接

6.13.关注 7天以内 错误日志

7.总结和建议

results matching ""

No results matching ""

6.12.关注 7天以上僵尸链接

6.13.关注 7天以内错误日志