1、MySQL故障排查与运维案例

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

一、连接类故障

1. 连接超时

现象：ERROR 2003 (HY000): Can't connect to MySQL server on 'host' (110 "Connection timed out")
排查流程：

# 检查网络连通性nc-zvhost3306mtrhost# 检查防火墙iptables-L-n|grep3306# 验证连接数限制SHOW VARIABLES LIKE'max_connections';SHOW STATUS LIKE'Threads_connected';

2. 认证失败

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

-- 创建传统认证用户CREATEUSER'appuser'@'%'IDENTIFIEDWITHmysql_native_passwordBY'password';-- 临时降低密码强度SETGLOBALvalidate_password_policy=LOW;

二、性能类故障

1. CPU 100%问题

诊断步骤：

-- 查找高消耗SQLSELECT*FROMsys.processlistWHERECOMMAND!='Sleep'ORDERBYTIMEDESC;-- 使用Performance SchemaSELECT*FROMperformance_schema.threadsWHEREPROCESSLIST_TIME>60\G-- 分析慢查询SHOWENGINEINNODBSTATUS;

2. 慢查询优化案例

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

-- 添加复合索引ALTERTABLEordersADDINDEXidx_customer_status(customer_id,status);-- 重写查询语句SELECT/*+ INDEX(idx_customer_status) */*FROMordersWHEREcustomer_id=123ANDstatusIN('shipped','completed');

三、复制类故障

1. 主从数据不一致

检测工具：

# 安装校验工具wgethttps://downloads.percona.com/downloads/percona-toolkit/3.5.0/binary/tarball/percona-toolkit-3.5.0_x86_64.tar.gz# 数据一致性校验pt-table-checksum-hmaster-uuser-ppass--databasesmydb

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误操作
解决方案：

-- 从备份恢复单分区ALTERTABLElogsIMPORTPARTITIONp202301FROM'/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*FROMperformance_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*FROMmysql.userWHEREauthentication_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-pdb1|split-b2G - db1_part_

十、升级问题

1. 5.7升级8.0兼容问题

解决方案：

-- 开启兼容SQL模式SETGLOBALsql_mode='NO_ENGINE_SUBSTITUTION';-- 移除废弃功能ALTERTABLEmytable ROW_FORMAT=DYNAMIC;

十一、配置错误

1. 参数误设置

恢复方法：

# 安全模式启动，高版本中不可用mysqld_safe --skip-grant-tables --skip-networking&# 重置配置SET GLOBALmax_connections=100;FLUSH PRIVILEGES;

十二、工具速查表

十三、关键监控指标

十四、灾难恢复流程

1. 立即停止服务：systemctl stop mysqld
2. 保护现场：拷贝数据目录和日志文件
3. 评估损坏：

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

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

十五、最佳实践总结

1. 备份策略：
   • 每天全备 + Binlog实时同步
   • 备份恢复演练每月一次
2. 高可用架构：
3. 参数调优原则：
   • buffer_pool_size = 系统内存的70-80%
   • max_connections = (最大连接数+冗余)
   • sync_binlog = 1 (数据安全) / 1000 (性能优先)
4. 安全基线：
   • 禁用local-infile
   • 删除test数据库
   • 启用SSL连接
   • 审计插件开启

黄金准则：

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