MySQL故障排查与运维案例详解_Mysql

mysql故障排查与运维案例全集

一、连接类故障

1. 连接超时

现象：error 2003 (hy000): can't connect to mysql server on 'host' (110 "connection timed out")
排查流程：

# 检查网络连通性
nc -zv host 3306
mtr host
# 检查防火墙
iptables -l -n | grep 3306
# 验证连接数限制
show variables like 'max_connections';
show status like 'threads_connected';

2. 认证失败

案例：升级后密码策略变更导致应用连接失败
解决方案：

-- 创建传统认证用户
create user 'appuser'@'%' identified with mysql_native_password by 'password';
-- 临时降低密码强度
set global validate_password_policy=low;

二、性能类故障

1. cpu 100%问题

诊断步骤：

-- 查找高消耗sql
select * from sys.processlist where command != 'sleep' order by time desc;
-- 使用performance schema
select * from performance_schema.threads where processlist_time > 60\g
-- 分析慢查询
show engine innodb status;

2. 慢查询优化案例

场景：订单查询超时
调优方案：

-- 添加复合索引
alter table orders add index idx_customer_status (customer_id, status);
-- 重写查询语句
select /*+ index(idx_customer_status) */ * from orders 
where customer_id=123 and status in ('shipped','completed');

三、复制类故障

1. 主从数据不一致

检测工具：

# 安装校验工具
wget https://downloads.percona.com/downloads/percona-toolkit/3.5.0/binary/tarball/percona-toolkit-3.5.0_x86_64.tar.gz
# 数据一致性校验
pt-table-checksum -h master -u user -p pass --databases mydb

2. 主从延迟

优化方案：

# my.cnf 优化
[mysqld]
slave_parallel_workers = 8
slave_pending_jobs_size_max = 2g
innodb_flush_log_at_trx_commit = 0
sync_binlog = 1000

四、数据恢复类

1. 误删除恢复

步骤：

# 停止mysql服务
systemctl stop mysqld
# 使用mysqlbinlog恢复
mysqlbinlog --start-position=107 /var/log/mysql-bin.000001 | mysql -uroot -p
# 使用延时从库恢复
stop slave;
change master to master_delay = 3600;
start slave;

2. 分区表数据丢失

案例：drop partition误操作
解决方案：

-- 从备份恢复单分区
alter table logs import partition p202301 
    from '/backup/202301_partition.ibd';

五、高可用故障

1. mha切换失败

诊断流程：

# 检查ssh互信
masterha_check_ssh --conf=/etc/mha/app1.cnf
# 检查复制健康
masterha_check_repl --conf=/etc/mha/app1.cnf
# 查看管理日志
tail -f /var/log/masterha/app1/manager.log

2. innodb cluster脑裂

修复方案：

-- 强制重启集群
dba.rebootclusterfromcompleteoutage('cluster1');
-- 人工重新组集群
select * from performance_schema.replication_group_members;

六、存储引擎故障

1. innodb损坏修复

修复步骤：

# 强制恢复模式启动
innodb_force_recovery = 6
# 导出数据
mysqldump -uroot -p --all-databases > full_backup.sql
# 重建数据库
mysql_install_db --user=mysql
systemctl start mysqld
mysql -uroot -p < full_backup.sql

七、内存问题

1. oom崩溃

优化方案：

# my.cnf内存优化
[mysqld]
innodb_buffer_pool_size=64g
key_buffer_size=0
query_cache_size=0
table_open_cache=20000

八、安全相关

1. 入侵检测

处理流程：

-- 查找异常账号
select * from mysql.user where authentication_string='' \g
-- 检查数据库文件权限
ls -l /var/lib/mysql
-- 审计可疑操作
mysqlbinlog /var/log/mysql-bin.000007 | grep -i 'alter\|create\|drop'

九、备份恢复

1. 大库备份优化

# xtrabackup部分备份
xtrabackup --backup --databases="db1 db2" --target-dir=/backup/partial
# mysqldump分片备份
mysqldump -uroot -p db1 | split -b 2g - db1_part_

十、升级问题

1. 5.7升级8.0兼容问题

解决方案：

-- 开启兼容sql模式
set global sql_mode = 'no_engine_substitution';
-- 移除废弃功能
alter table mytable row_format=dynamic;

十一、配置错误

1. 参数误设置

恢复方法：

# 安全模式启动，高版本中不可用
mysqld_safe --skip-grant-tables --skip-networking &
# 重置配置
set global max_connections=100;
flush privileges;

十二、工具速查表

工具名称	使用场景	命令示例
pt-query-digest	慢日志分析	`pt-query-digest slow.log > report.txt`
mysqladmin	进程管理	`mysqladmin -u root -p processlist`
percona toolkit	运维工具包	`pt-online-schema-change`
mylogger	实时审计	`mylogger -u root -p pass -h localhost`
mysql shell	innodb cluster管理	`dba.checkinstanceconfiguration()`

十三、关键监控指标

指标	报警阈值	获取方式
连接使用率	> 85%	`threads_connected/max_connections`
复制延迟(秒)	> 60	`show slave status`
innodb缓冲池命中率	< 95%	`(1 - innodb_pages_read/innodb_buffer_pool_read_requests)*100`
临时表磁盘使用	> 1g	`created_tmp_disk_tables`
锁等待时间(秒)	> 5	`show engine innodb status`

十四、灾难恢复流程

立即停止服务：systemctl stop mysqld
保护现场：拷贝数据目录和日志文件

评估损坏：

innochecksum -v /var/lib/mysql/ibdata1
mysqlcheck --all-databases

选择恢复方案：
- 从主备份恢复
- 使用binlog增量恢复
- 重建数据库结构
验证完整性：pt-table-checksum
灰度恢复服务

十五、最佳实践总结

备份策略：
- 每天全备 + binlog实时同步
- 备份恢复演练每月一次
高可用架构：

参数调优原则：
- buffer_pool_size = 系统内存的70-80%
- max_connections = (最大连接数+冗余)
- sync_binlog = 1 (数据安全) / 1000 (性能优先)
安全基线：
- 禁用local-infile
- 删除test数据库
- 启用ssl连接
- 审计插件开启

黄金准则：

任何参数修改前进行set global测试
维护窗口操作必须有回滚计划
生产环境变更遵循"变更三板斧"：方案评审->灰度实施->结果验证

到此这篇关于mysql故障排查与运维案例的文章就介绍到这了,更多相关mysql故障排查与运维内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

MySQL故障排查与运维案例详解

2026年04月13日 • Mysql •我要评论