MySQL 故障排查与生产环境优化

MySQL 故障排查核心思路与流程故障排查核心原则先恢复服务再定位根因先排查外部因素再排查数据库本身避免盲目操作如重启、删数据防止故障扩大同时做好操作记录便于回溯故障排查前置准备确认故障现象明确故障表现如连接失败、查询卡顿、服务宕机、数据异常、影响范围全量/部分用户、某张表/某个库、发生时间是否有定时任务、批量操作触发收集基础信息服务器资源CPU、内存、磁盘IO、网络、MySQL版本、配置文件my.cnf/my.ini、错误日志error log、慢查询日志slow query log、进程列表show processlist工具准备终端工具Xshell、SecureCRT、MySQL客户端navicat、mysql命令行、监控工具PrometheusGrafana、Zabbix、日志分析工具tail、grep、pt-query-digest故障排查核心流程排查外部环境服务器是否正常运行ping、ssh连接、网络是否通畅telnet 数据库IP 端口、防火墙是否拦截端口iptables、firewalld、磁盘是否满df -h、内存是否溢出free -m、top排查MySQL服务状态查看MySQL进程ps -ef | grep mysql、尝试启动/重启服务systemctl start/restart mysqld、查看服务启动日志journalctl -u mysqld排查连接问题确认MySQL监听地址show variables like bind_address、端口show variables like port、最大连接数show variables like max_connections、当前连接数show global status like Threads_connected排查连接被拒绝、连接超时问题排查查询/性能问题通过慢查询日志定位低效SQL、通过show processlist查看阻塞进程state列显示Waiting for table lock等、通过explain分析SQL执行计划排查索引失效、全表扫描、锁等待等问题排查数据异常确认数据是否丢失/错乱select count(*)、对比备份数据、排查事务异常show engine innodb status查看事务日志、排查主从同步异常show slave status定位根因并修复根据上述排查结果针对性处理如调整配置、优化SQL、清理磁盘、修复主从同步修复后验证服务正常复盘故障原因避免重复发生MySQL 常见故障及解决方案服务无法启动故障故障现象systemctl start mysqld 失败日志提示“Failed to start MySQL Server”配置文件错误my.cnf中参数写错如datadir路径错误、端口冲突磁盘满导致无法写入日志或数据文件MySQL数据目录权限不足mysqld用户无读写权限之前异常关闭导致ibdata1、ib_logfile等InnoDB文件损坏解决方案检查配置文件执行 mysqld --defaults-file/etc/my.cnf --validate-config 验证配置修正错误参数清理磁盘删除无用日志、备份文件确保磁盘剩余空间≥10%调整权限chown -R mysqld:mysqld /var/lib/mysql数据目录修复InnoDB文件若文件损坏可尝试删除ib_logfile0、ib_logfile1谨慎需先备份重启MySQL自动重建若损坏严重需通过备份恢复数据连接失败/连接超时故障故障现象客户端连接MySQL时提示“Cant connect to MySQL server on xxx (111)”连接拒绝或“Timeout”连接超时MySQL服务未启动防火墙/安全组拦截3306端口bind_address配置为127.0.0.1 仅允许本地连接max_connections达到上限 无法建立新连接网络不稳定或客户端与服务器网络不通解决方案确认MySQL服务已启动 执行 systemctl status mysqld开放端口firewall-cmd --permanent --add-port3306/tcp 重启防火墙修改bind_address为0.0.0.0允许所有IP连接重启MySQL临时调整max_connectionsset global max_connections2000永久调整需修改my.cnf重启生效测试网络ping 服务器IP telnet 服务器IP 3306 排查网络问题查询卡顿/性能低下故障故障现象SQL查询耗时过长 页面加载缓慢 show processlist显示大量进程处于“Query”“Waiting for table lock”状态SQL语句优化不足无索引 全表扫描 join方式不合理索引失效字段类型不匹配 使用函数/模糊查询开头 索引碎片化锁等待行锁 表锁冲突 如批量更新未提交事务服务器资源不足CPU 内存 IO占用过高MySQL配置不合理缓存设置过小 连接数不足解决方案优化SQL通过explain分析执行计划 给查询字段添加索引 避免select * 模糊查询开头 优化join条件修复索引重建碎片化索引alter table 表名 engineInnoDB确保查询字段与索引字段类型一致 避免在索引字段上使用函数解决锁等待通过show processlist找到阻塞进程 kill 进程ID 优化事务逻辑 避免长事务优化服务器资源升级CPU 内存 优化磁盘IO使用SSD关闭无用进程调整MySQL缓存增大query_cache_size查询缓存innodb_buffer_pool_sizeInnoDB缓存提升查询效率2.4 主从同步异常故障故障现象主从服务器数据不一致 show slave status显示 Slave_IO_Running: No 或 Slave_SQL_Running: No主库binlog日志损坏或丢失从库relay log日志损坏主从库表结构不一致从库SQL线程执行错误如主键冲突、数据不存在主从库MySQL版本不一致解决方案查看从库错误日志 定位具体异常原因若SQL线程错误停止从库 跳过错误 重启从库 若错误频繁需排查主从数据一致性 重新同步若IO线程错误检查主库binlog文件和位置 确认从库change master to参数正确重新配置主从同步确保主从库表结构 MySQL版本一致 定期校验主从数据如使用pt-table-checksumMySQL 生产环境优化生产环境优化核心目标提升查询性能 保证服务稳定性 减少资源消耗 避免故障发生优化需结合业务场景 避免过度优化配置文件优化核心配置参数连接配置max_connections 2000 根据业务并发量调整 避免连接数不足wait_timeout 600 空闲连接超时时间 释放无用连接 避免资源浪费interactive_timeout 600 交互式连接超时时间 与wait_timeout保持一致缓存优化InnoDB为主innodb_buffer_pool_size 物理内存的50%-70% 核心缓存 缓存表数据和索引 越大越好innodb_log_buffer_size 64M 日志缓冲区 减少磁盘IO 根据写入量调整query_cache_size 64M 查询缓存 适合读多写少场景 写多场景可设为0query_cache_type ON 开启查询缓存 与query_cache_size配合使用IO优化innodb_flush_log_at_trx_commit 1 事务提交时 日志立即写入磁盘 保证数据安全 牺牲部分性能innodb_file_per_table ON 每张表单独生成ibd文件 便于管理和回收空间innodb_flush_method O_DIRECT 跳过操作系统缓存 直接写入磁盘 提升IO效率其他优化character-set-server utf8mb4 统一字符集 支持emoji 避免乱码slow_query_log ON 开启慢查询日志long_query_time 1 慢查询阈值 超过1秒的查询记录到日志 便于优化log_bin /var/lib/mysql/mysql-bin 开启binlog日志 用于主从同步和数据恢复SQL语句优化核心优化技巧避免select * 只查询需要的字段减少数据传输和内存消耗避免使用子查询 优先使用join子查询效率较低尤其是大数据量场景避免模糊查询开头 会导致索引失效 可使用后缀模糊查询避免在索引字段上使用函数 会导致索引失效优化join查询确保join字段有索引 避免大表join大表 优先使用inner join批量操作优化批量插入 批量更新 避免循环单条操作避免长事务事务尽量短 减少锁占用时间 避免事务中包含不必要的操作优化工具使用explain分析SQL执行计划 查看是否全表扫描 索引失效 使用pt-query-digest分析慢查询日志 定位高频低效SQL服务器与环境优化硬件优化CPU选择多核 高主频CPUMySQL是多线程应用 多核可提升并发处理能力内存增大物理内存 保证innodb_buffer_pool_size足够大 减少磁盘IO磁盘使用SSD读写速度是机械硬盘的10倍以上分区分盘数据盘 日志盘分离 避免磁盘IO瓶颈网络使用千兆以上网络 避免网络带宽不足导致连接超时 查询卡顿系统优化关闭无用服务 释放系统资源调整系统文件描述符 避免文件描述符不足导致连接失败优化磁盘调度算法 提升磁盘IO效率定期备份数据 避免数据丢失 便于故障恢复