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

MySQL故障排查与运维案例全集一、连接类故障1. 连接超时现象ERROR 2003 (HY000): Cant connect to MySQL server on host (110 Connection timed out)排查流程# 检查网络连通性nc-zvhost3306mtrhost# 检查防火墙iptables-L-n|grep3306# 验证连接数限制SHOW VARIABLES LIKEmax_connections;SHOW STATUS LIKEThreads_connected;2. 认证失败案例升级后密码策略变更导致应用连接失败解决方案-- 创建传统认证用户CREATEUSERappuser%IDENTIFIEDWITHmysql_native_passwordBYpassword;-- 临时降低密码强度SETGLOBALvalidate_password_policyLOW;二、性能类故障1. CPU 100%问题诊断步骤-- 查找高消耗SQLSELECT*FROMsys.processlistWHERECOMMAND!SleepORDERBYTIMEDESC;-- 使用Performance SchemaSELECT*FROMperformance_schema.threadsWHEREPROCESSLIST_TIME60\G-- 分析慢查询SHOWENGINEINNODBSTATUS;2. 慢查询优化案例场景订单查询超时调优方案-- 添加复合索引ALTERTABLEordersADDINDEXidx_customer_status(customer_id,status);-- 重写查询语句SELECT/* INDEX(idx_customer_status) */*FROMordersWHEREcustomer_id123ANDstatusIN(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--databasesmydb2. 主从延迟优化方案# 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-position107/var/log/mysql-bin.000001|mysql-uroot-p# 使用延时从库恢复STOP SLAVE;CHANGE MASTER TO MASTER_DELAY3600;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.log2. InnoDB Cluster脑裂修复方案-- 强制重启集群dba.rebootClusterFromCompleteOutage(cluster1);-- 人工重新组集群SELECT*FROMperformance_schema.replication_group_members;六、存储引擎故障1. InnoDB损坏修复修复步骤# 强制恢复模式启动innodb_force_recovery6# 导出数据mysqldump-uroot-p--all-databasesfull_backup.sql# 重建数据库mysql_install_db--usermysql systemctl start mysqld mysql-uroot-pfull_backup.sql七、内存问题1. OOM崩溃优化方案# my.cnf内存优化 [mysqld] innodb_buffer_pool_size64G key_buffer_size0 query_cache_size0 table_open_cache20000八、安全相关1. 入侵检测处理流程-- 查找异常账号SELECT*FROMmysql.userWHEREauthentication_string\G-- 检查数据库文件权限ls-l/var/lib/mysql-- 审计可疑操作mysqlbinlog/var/log/mysql-bin.000007|grep-iALTER\|CREATE\|DROP九、备份恢复1. 大库备份优化# Xtrabackup部分备份xtrabackup--backup--databasesdb1 db2--target-dir/backup/partial# mysqldump分片备份mysqldump-uroot-pdb1|split-b2G - db1_part_十、升级问题1. 5.7升级8.0兼容问题解决方案-- 开启兼容SQL模式SETGLOBALsql_modeNO_ENGINE_SUBSTITUTION;-- 移除废弃功能ALTERTABLEmytable ROW_FORMATDYNAMIC;十一、配置错误1. 参数误设置恢复方法# 安全模式启动高版本中不可用mysqld_safe --skip-grant-tables --skip-networking# 重置配置SET GLOBALmax_connections100;FLUSH PRIVILEGES;十二、工具速查表工具名称使用场景命令示例pt-query-digest慢日志分析pt-query-digest slow.log report.txtmysqladmin进程管理mysqladmin -u root -p processlistPercona Toolkit运维工具包pt-online-schema-changeMylogger实时审计mylogger -u root -p pass -h localhostMySQL ShellInnoDB Cluster管理dba.checkInstanceConfiguration()十三、关键监控指标指标报警阈值获取方式连接使用率 85%Threads_connected/max_connections复制延迟(秒) 60SHOW SLAVE STATUSInnoDB缓冲池命中率 95%(1 - Innodb_pages_read/Innodb_buffer_pool_read_requests)*100临时表磁盘使用 1GCreated_tmp_disk_tables锁等待时间(秒) 5SHOW ENGINE INNODB STATUS十四、灾难恢复流程立即停止服务systemctl stop mysqld保护现场拷贝数据目录和日志文件评估损坏innochecksum-v/var/lib/mysql/ibdata1 mysqlcheck --all-databases选择恢复方案从主备份恢复使用Binlog增量恢复重建数据库结构验证完整性pt-table-checksum灰度恢复服务十五、最佳实践总结备份策略每天全备 Binlog实时同步备份恢复演练每月一次高可用架构半同步复制应用ProxySQLPrimaryReplica1Replica2Secondary参数调优原则buffer_pool_size 系统内存的70-80%max_connections (最大连接数冗余)sync_binlog 1 (数据安全) / 1000 (性能优先)安全基线禁用local-infile删除test数据库启用SSL连接审计插件开启黄金准则任何参数修改前进行SET GLOBAL测试维护窗口操作必须有回滚计划生产环境变更遵循变更三板斧方案评审-灰度实施-结果验证