1. MySQL运维实战概述MySQL作为最流行的开源关系型数据库之一在企业级应用中扮演着关键角色。我在过去8年的DBA工作中发现90%的生产环境问题都集中在20%的常见场景里。这篇文章将分享这些高频问题的诊断思路和解决方案涵盖性能瓶颈、连接异常、数据一致性等核心痛点。不同于官方文档的理论说明这里的内容全部来自真实生产环境的案例总结。每个解决方案都经过至少3次以上实际验证特别适合中小规模MySQL集群1-10个节点的运维场景。无论你是刚接触MySQL的新手还是需要快速排查问题的开发人员这些实战经验都能帮你节省大量试错时间。2. 连接类问题排查2.1 连接数耗尽Too many connections上周刚处理过一个典型案例某电商网站在大促时前端突然报Cant connect to MySQL server但数据库服务器CPU/内存都正常。这就是典型的连接数耗尽问题通过以下步骤快速定位查看当前连接数上限SHOW VARIABLES LIKE max_connections;默认值151对于高并发场景往往不够检查实际连接数SHOW STATUS LIKE Threads_connected;紧急处理无需重启SET GLOBAL max_connections 500;重要提示临时调整后务必修改my.cnf永久生效否则重启后配置会丢失深度优化建议使用连接池如HikariCP控制应用层连接为不同业务设置专用账号通过PROXY_USER限制单账号连接数监控Threads_connected与max_connections比值超过70%就要预警2.2 连接超时Wait timeout当出现ERROR 3024 (HY000): Connection timeout时需要检查以下参数SHOW VARIABLES LIKE %timeout%;重点关注三个参数wait_timeout非交互连接超时interactive_timeout交互连接超时lock_wait_timeout元数据锁超时典型误区和解决方案应用连接池中的连接因超时被服务器断开但连接池不知情继续分配解决方案在连接池配置testOnBorrow/testWhileIdle长事务导致超时优化事务粒度避免单个事务执行过久3. 性能类问题排查3.1 慢查询分析慢查询是性能问题的首要嫌疑对象按这个流程排查确认慢查询日志开启SHOW VARIABLES LIKE slow_query%;临时开启生产环境慎用SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; -- 单位秒使用mysqldumpslow工具分析mysqldumpslow -s t /var/lib/mysql/mysql-slow.log优化实战技巧对于JOIN操作检查EXPLAIN中的type列确保至少是range级别警惕隐式类型转换WHERE user_id 123user_id是int时分页优化避免LIMIT 10000,10改用WHERE id last_id LIMIT 103.2 CPU利用率飙升当CPU持续高于80%时按以下顺序排查查看当前活跃线程SHOW PROCESSLIST;识别高CPU查询SELECT * FROM sys.session WHERE cpu_time 1000 ORDER BY cpu_time DESC;检查锁竞争SHOW ENGINE INNODB STATUS\G典型案例全表扫描添加缺失索引排序操作优化ORDER BY子句锁等待调整事务隔离级别或拆分热点行4. 数据一致性问题4.1 主从复制延迟复制延迟(Seconds_Behind_Master)是MySQL复制架构的常见痛点。最近处理的一个案例中从库延迟持续在2小时以上通过以下步骤解决确认延迟原因SHOW SLAVE STATUS\G检查关键指标IO线程状态SQL线程状态Last_IO_Error/Last_SQL_Error优化方案对比表方案适用场景优缺点调整sync_binlogIO瓶颈安全性vs性能权衡启用并行复制多核服务器需5.7版本使用GTID复杂拓扑简化故障转移4.2 数据损坏修复当遇到InnoDB表损坏时报错Table is crashed按这个流程恢复尝试自动修复REPAIR TABLE damaged_table;使用备份恢复mysqlbinlog /var/log/mysql/mysql-bin.000123 recovery.sql终极方案需停机innodb_force_recovery 6 # 在my.cnf中设置警告innodb_force_recovery是最后手段可能造成数据丢失5. 存储空间问题5.1 磁盘空间告急当收到磁盘空间报警时快速定位大表查看数据库大小SELECT table_schema, SUM(data_length)/1024/1024 AS size_mb FROM information_schema.tables GROUP BY table_schema;查找碎片化严重的表SELECT ENGINE, TABLE_NAME, DATA_FREE/1024/1024 AS free_mb FROM information_schema.tables WHERE DATA_FREE 100*1024*1024;清理策略归档历史数据使用pt-archiver工具在线收缩表空间ALTER TABLE ... ENGINEInnoDB清理二进制日志PURGE BINARY LOGS BEFORE 2023-01-015.2 表空间膨胀InnoDB的物理文件增长后不会自动收缩需要特殊处理检查实际数据量SELECT COUNT(*) FROM large_table;重建表在线操作ALTER TABLE large_table ENGINEInnoDB;注意事项确保有足够磁盘空间需要额外临时空间大表操作建议在低峰期进行考虑使用pt-online-schema-change减少锁时间6. 监控与预防体系6.1 关键指标监控这些指标应该纳入你的监控系统性能指标QPS/TPS慢查询率连接数使用率资源指标InnoDB缓冲池命中率临时表创建数行锁等待时间推荐采集频率基础指标15秒慢查询统计5分钟全量状态变量1小时6.2 自动化巡检脚本分享一个我日常使用的巡检脚本框架#!/bin/bash # 检查连接数 check_connections() { local used$(mysql -e SHOW STATUS LIKE Threads_connected | awk NR2{print $2}) local max$(mysql -e SHOW VARIABLES LIKE max_connections | awk NR2{print $2}) echo 连接数使用率: $((100*used/max))% } # 检查复制状态 check_replication() { mysql -e SHOW SLAVE STATUS\G | grep -E Running|Behind }把这个脚本加入cron配合邮件报警就能建立基础防护网。根据我的经验这套简单的监控能提前发现80%的潜在问题。