排查MySQL故障时我最先做的事永远是翻日志。这句话听起来简单但现实中太多人一遇到报错就重启服务、改参数却忽略了错误日志里早把原因写得明明白白。MySQL日志管理这个主题覆盖了配置、轮转、分析和故障定位一整条链路尤其是刚入门MySQL的同学往往分不清有哪几类日志、哪些参数控制落盘、日志文件越来越大怎么处理更别提靠日志反推问题根因了。这篇文章按我日常操作的顺序把MySQL日志从配置到故障排查的完整过程掰开揉碎讲一遍基于MySQL 8.0版本配置思路同样适用于MySQL 5.7。想看结论的直接跳到对应章节想系统过一遍的就顺着读。1. MySQL日志家族盘点每类日志到底在记录什么1.1 六类日志的功能边界MySQL里的日志并不只有一种很多人对着数据目录发懵不知道哪个文件是干嘛的。先理清楚分类后面配置和排查才不会乱日志类型默认文件核心配置参数典型用途错误日志hostname.errlog_error启动异常、运行错误、连接失败记录慢查询日志hostname-slow.logslow_query_log、long_query_time捕获执行时间超阈值的SQL通用查询日志hostname.loggeneral_log、general_log_file记录所有连接和SQL语句排查审计用二进制日志binlog.000001log_bin、binlog_format数据恢复、主从复制数据源中继日志relay-log.000001relay_log从库接收主库binlog后本地落地InnoDB事务日志ib_logfile0innodb_log_file_size崩溃恢复引擎层物理重做日志这里必须先做一个常见混淆的澄清redo log和binlog是两类完全不同的东西。redo log是InnoDB存储引擎层的物理日志记录的是页的修改服务崩溃后靠它恢复数据binlog是MySQL Server层的逻辑日志记录的是SQL语句或行变更主从复制和备份恢复靠它。很多新手把这两个混为一谈排查时就会找错方向。比如实例崩溃后数据丢失你去翻binlog没有意义得看redo log和错误日志的崩溃恢复段。1.2 错误日志其实是故障排查的起点错误日志是所有TCP连接失败、认证失败、启动关闭事件、复制异常、InnoDB恢复状态、主从切换等关键事件的第一落点。它的输出入口由log_error参数控制默认位置在数据目录下以主机名命名的.err文件里。我遇到过不少同事my.cnf里压根没配log_error导致错误日志被系统rsyslog接管最后散落在/var/log/messages里排查时还得去系统日志里大海捞针。所以配好log_error、固定错误日志的路径是所有日志管理的第一步。1.3 慢查询日志与通用查询日志的区别慢查询日志记录的是超过long_query_time阈值的SQL主要用于性能优化通用查询日志记录的是所有客户端发来的每一条语句包括连接建立和断开生产环境默认关闭。两者定位完全不同一个做体检一个做监控录像。录像耗硬盘所以平时别开只有在需要审计某个具体时段、某个连接的完整SQL序列时才临时开启并立刻关闭。2. 日志落盘前的关键配置my.cnf参数清单与避坑2.1 一份可以直接参考的日志参数配置下面的配置是我在生产环境常用的模板按实际场景调整路径和阈值[mysqld] # 错误日志 log_error /data/mysql/logs/error.log log_error_verbosity 3 # 慢查询日志 slow_query_log ON slow_query_log_file /data/mysql/logs/slow.log long_query_time 2 log_queries_not_using_indexes ON min_examined_row_limit 100 # 通用查询日志排查问题临时开启 # general_log ON # general_log_file /data/mysql/logs/general.log # 二进制日志 server_id 1 log_bin /data/mysql/logs/binlog binlog_format ROW binlog_row_image FULL max_binlog_size 256M binlog_expire_logs_seconds 604800 sync_binlog 1 # 日志时间统一用系统本地时间 log_timestamps SYSTEM每条说明一下为什么这么配log_error_verbosity3 表示错误日志记录的信息级别包含Error、Warning、Note。默认值在MySQL 8.0就是3线上日志量能接受的话不用动但排查问题时不要手贱降成2很多警告信息反而有助于定位。log_queries_not_using_indexesON 会把没用索引的查询全部记进慢日志这对发现隐藏的全表扫描非常有帮助。但这个选项特别危险如果你库里有大量本来就不需要索引的小表查询日志会瞬间爆炸。所以必须配合min_examined_row_limit100意思是只有扫描行数超过100行的查询才会被选进来过滤掉那些本来就该全表扫的配置表查询。binlog_expire_logs_seconds604800 是7天单位是秒不是天。MySQL 8.0已经把expire_logs_days标记为废弃参数这俩混着写会导致清理策略不生效。sync_binlog1 表示每次事务提交都强制刷盘保证binlog不丢代价是写入性能有一定下降但对数据一致性要求高的业务必须开。2.2 慢查询日志“查不到记录”的四个常见原因慢查询日志开启后执行一条明显超过阈值的SQL结果日志里啥也没有。这个问题在排障和面试里都高频出现99%的原因是下面几个long_query_time默认值是10秒线上很多SQL跑个3秒5秒的你觉得慢但没到默认阈值。想验证可以先SET GLOBAL long_query_time0再执行任何查询确认日志能写入再调回合理值。慢查询参数有时效性SET GLOBAL只是修改全局变量当前已存在的连接仍然用旧值必须新开连接才生效。改完my.cnf后不重启部分版本参数也可能不生效建议用SHOW VARIABLES LIKE long_query_time验证当前实际值。路径权限问题。slow_query_log_file指定的目录不存在或MySQL进程无写权限时MySQL默认会忽略错误继续运行但日志就是不写。务必手动测试目录可写并查看错误日志里有没有“Cant create/write to file的报错。只有执行完成的语句才进慢日志被kill掉的、仍在跑的、因为锁等待尚未结束的SQL不会记录。排查时别对着原本就执行失败的语句找慢日志得用performance_schema的events_statements_current去看实时状态。2.3 二进制日志格式选择ROW还是STATEMENTbinlog_format有三个值STATEMENT、ROW、MIXED。我个人的线上结论是用ROW不用纠结。STATEMENT格式记录的是原始SQL日志量小但它在遇到NOW()、UUID()这类不确定函数时主从执行结果可能不一致对无主键表的更新同样存在隐患。ROW格式记录的是每一行前后镜像对不确定函数天然安全从库回放结果一定和主库一致。代价是日志体积成倍增长尤其大批量UPDATE时binlog会非常大——这正好印证了为什么binlog清理策略必须单独盯着。binlog_row_imageFULL表示记录行变更的前镜像和后镜像对于刚开始学、或者日常排查问题FULL信息最全。如果追求极致性能可以改成MINIMAL只记被修改的列但排障时看binlog就费劲了。3. 日志轮转与磁盘水位日志管理最容易被忽视的战场3.1 日志撑爆磁盘的三种典型场景日志管理做得再好也怕突发情况。我处理过的生产事故里日志撑爆磁盘的基础盘现象就三类第一类通用日志忘记关闭。有次排查一个连接风暴问题开了general_log想看完整的SQL序列问题解决后忘了关两天后磁盘告警一看/var/log/mysql目录占了40多GB。这类日志写满磁盘之后MySQL会因为无法写入而直接拒绝服务非常危险。第二类慢日志和无索引查询叠加。前面说的log_queries_not_using_indexesON如果没有min_examined_row_limit兜底一次大量无索引扫描就能让慢日志单日增长数GB。第三类binlog只增不减。很多新手不知道binlog默认不会自动清理以为删除数据后binlog就小了。实际上binlog是追加日志没有备份任务、没有主从复制消费的情况下它就是这个实例最大的“磁盘吞噬者”。3.2 binlog清理策略的正确配置MySQL 8.0里官方推荐用binlog_expire_logs_seconds单位秒。下面这个配置表示binlog保留7天超过7天的自动PURGEbinlog_expire_logs_seconds 604800这里必须强调两个坑第一这个参数对slave从库的relay log不生效relay log由relay_log_purge1控制自动清理第二binlog清理依赖MySQL自动任务不是进程内实时扫描高峰期日志量大的库实际占用可能超过保留期一两个G别等告警了才反应。手动清理binlog的姿势是这样-- 查看当前正在使用的binlog SHOW MASTER STATUS; -- 清理到指定文件之前不包含指定文件 PURGE BINARY LOGS TO binlog.000023; -- 按时间清理 PURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;手动PURGE前必须先确认复制链路状态。从库还在读取某个binlog文件你自信满满地PURGE了从库IO线程立刻报1236错误主从直接断裂这个坑我踩过。判断方法SHOW SLAVE STATUS\G里的Master_Log_File或SHOW REPLICA STATUS\G里的Relay_Master_Log_FilePURGE到比这个文件更早的位置就是找死。3.3 用logrotate实现日志轮转MySQL自身不负责按天切割日志Linux下用logrotate管理更顺手。下面是我的一台线上配置文件保存在/etc/logrotate.d/mysql/data/mysql/logs/*.log /data/mysql/logs/binlog.* { daily rotate 14 missingok notifempty compress delaycompress sharedscripts postrotate /usr/local/mysql/bin/mysqladmin -uroot -p*** flush-logs endscript }几个细节值得说明postrotate里的flush-logs是让MySQL重新生成新的binlog文件配合compress压缩旧文件空间友好。但注意flush-logs对通用日志和慢查询日志同样有效对错误日志不总是生效。sharedscripts确保多个日志文件匹配时postrotate脚本只执行一次。千万不要在logrotate里加copytruncate当作万能方案日志文件正在被MySQL占用时强制truncate可能导致文件空洞、写入错乱。用flush-logs才是正规姿势。轮转完成后建议自己看一眼目录确认新日志文件已创建、旧文件已压缩别指望logrotate一定成功cron执行失败、权限不对都是常见故障。4. 从日志到故障定位三个真实排查案例这一章我按实际排查链路来写不跳步。日志的价值不在于“出错了看一眼”而在于通过日志的上下文把根因推出来。4.1 案例一客户端连不上MySQL错误日志暴露的真相现象很典型客户端执行mysql -uroot -p报错ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock (2)很多人的第一反应是“MySQL挂了”但服务可能活得好好的。排查链路如下第一步看错误日志。MySQL启动、关闭、连接失败错误日志里都有记录。执行tail -100 /data/mysql/logs/error.log如果日志末尾有[System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections.说明MySQL明明正常那问题就在客户端连接参数上。socket文件不在/tmp/mysql.sock或者客户端指定的host是localhost时走了socket连接但文件路径不对。第二步确认真实socket路径。执行SHOW VARIABLES LIKE socket;如果结果是/tmp/mysql.sock但文件不存在十有八九是系统清理临时目录把它删了。服务本身正常只是Unix socket文件丢了此时用TCP方式连接即可验证mysql -uroot -p -h127.0.0.1 -P3306第三步如果错误日志里大量出现这种记录[Warning] [MY-010744] [Server] Access denied for user rootlocalhost (using password: YES)那就是密码认证问题去排查账号权限而不是纠结socket。日志已经给了你路径和答案顺着查就行。这类问题的根治思路把socket参数显式配到my.cnf里并确保连接时明确指定socket或host。连接池、ORM框架老出这类问题的统一改成TCP方式连接再在配置里指明socket绝对路径能省掉一半莫名其妙的“连不上”。4.2 案例二主从复制中断binlog与relay log的交叉验证主从同步出问题SHOW REPLICA STATUS\G里的字段会直接告诉你原因在IO线程还是SQL线程。现象Slave_SQL_RunningNoLast_Errno1032。1032代表从库回放binlog时找不到目标行常见于从库被手动改过、主库有删除但从库对应记录已被修改。错误日志里往往有完整SQL语句先去翻grep -A 10 Coordinator thread /data/mysql/logs/error.log如果错误信息里拿着具体SQL下一步确认这条SQL到底想改哪张表、哪个主键。然后用binlog反向定位mysqlbinlog --base64-outputdecode-rows -v /data/mysql/logs/binlog.000014 | grep -B 5 -A 10 1032这样可以看到完整的行前镜像和行后镜像明确从库缺什么。最稳妥的修复方式不是直接跳过错误而是把缺失的数据从主库补齐。简单场景下可以在从库上手动INSERT缺失记录后再START REPLICA如果错误不涉及该行的后续更新跳过该事务也能接受跳过命令STOP REPLICA; SET GLOBAL sql_replica_skip_counter 1; START REPLICA;MySQL 8.0里是sql_replica_skip_counter别再用旧版本的sql_slave_skip_counter了虽然语法兼容但容易让人记忆混淆。跳过一次必须立刻确认状态绝不能在故障未判断的情况下连续跳否则数据的完整性会越来越差。中继日志损坏是另一类坑错误日志里报1594relay log文件破坏。这种情况把当前出问题的relay log删掉让IO线程从主库重新拉取即可STOP REPLICA; RESET REPLICA ALL; CHANGE MASTER TO MASTER_LOG_FILEbinlog.000014, MASTER_LOG_POS4; START REPLICA;MASTER_LOG_FILE和MASTER_LOG_POS来源于SHOW MASTER STATUS拿主库当前坐标来对。重点在于修复前必须明确是只是relay log文件损坏还是数据本身已经不一致后者需要更完整的校验不能只靠reset来解决。4.3 案例三业务突慢慢查询日志怎么帮我定位具体SQL业务反馈“接口突然从200ms变2秒”数据库CPU 80%以上。此时我不建议先动索引第一步永远是看慢查询日志。执行tail -200 /data/mysql/logs/slow.log重点关注两类SQL一是执行时间突然拉长的老查询二是新出现的、之前没见过的SQL片段。慢日志每行都有关键标注# Query_time: 2.452347 Lock_time: 0.000182 Rows_sent: 1 Rows_examined: 789321 SET timestamp...; SELECT o.id, o.order_no FROM orders o WHERE o.user_id10086...Query_time是总执行时间Lock_time是锁等待时间Rows_examined是实际扫描行数。78万多行的扫描只返回1行这基本就是索引缺失或者索引失效的明牌信号。下一步用EXPLAIN确认执行计划EXPLAIN SELECT o.id, o.order_no FROM orders o WHERE o.user_id10086\G看type字段如果是ALL全表扫描或者index全索引扫描并且key字段为NULL那问题就是缺少合适索引。针对user_id这个过滤条件建一个普通索引就够了ALTER TABLE orders ADD INDEX idx_user_id (user_id);索引加完记得再回来看慢日志确认这个SQL不再上榜同时用EXPLAIN确认type变成ref扫描行数从几十万降到几十行。整个过程就是“慢日志定位候选SQL - EXPLAIN确认执行计划 - 加索引/改写SQL - 慢日志验证效果”循环两三轮绝大多数慢SQL问题都能收敛。5. 慢查询日志的进阶玩法从打印到分析闭环5.1 手动打开慢日志文件看只能处理单条SQL慢日志文件一大直接用cat和grep会很难受。MySQL自带的mysqldumpslow工具可以把相同模式的SQL聚合语法模板不同但结构相似的语句会被归为一组比如上面的查询会被归为SELECT o.id, o.order_no FROM orders o WHERE o.user_idN。常用命令# 按平均查询时间排序取前10 mysqldumpslow -s at -t 10 /data/mysql/logs/slow.log # 按总执行时间排序 mysqldumpslow -s t /data/mysql/logs/slow.log输出里每组SQL都会显示Count出现次数、Time平均/总时间、Lock、Rows返回行数/扫描行数。这个工具的好处是快速知道“哪种SQL类型最消耗数据库资源”坏处是它不会告诉你具体某一条SQL在哪个时间点执行过这需要更细的工具。5.2 pt-query-digest分析慢日志的标准姿势Percona Toolkit里的pt-query-digest是慢日志分析的利器。安装方式各系统略有差异这里不展开装好后用法很简单pt-query-digest /data/mysql/logs/slow.log digest_report.txt生成的报告怎么看重点看三块Profile部分列出了所有SQL指纹的占比排名。Response time占比最高的那组SQL就是第一优化对象哪怕它Count不是最多的。总响应时间单次执行时间×执行次数高响应占比的SQL才是真凶。每个Query group单独的执行详情包含中位数时间、95%分位时间。中位数低但95%高的SQL说明大多数时候快偶尔慢方向要往锁等待、突然的并发上引。Query_time分布直方图有助于判断是否存在周期性卡顿。用这个工具分析完别只停留在“知道了哪些SQL慢”要紧跟一步把慢日志里的查询汇总后和information_schema、performance_schema里的表数据量变化做比对很多慢查询的根因是数据量增长导致旧的执行计划不再高效而非SQL本身写错了。5.3 把慢日志分析形成日常闭环日志管理不是等出问题才去看我建议把慢日志分析做成周期性动作每周跑一次pt-query-digest对比上周排名新上榜的SQL优先处理同时通过一个简单脚本检测慢日志文件是否在持续变大超过设定阈值就触发告警。如果不想额外装工具MySQL自带的performance_schema里events_statements_summary_by_digest表也提供了类似统计能力通过SQL查询就能拿到按语句指纹聚合的延迟和扫描行数。但缺点是需要启动参数performance_schemaON且查询语句相对SQL门槛更高。慢日志文件解析则更直观两种方案按需取舍。6. 日志安全与自查清单收尾必须做的事最后聊几个日志管理里容易被忽略的安全和实践细节。第一个通用日志、慢日志里会记录完整的SQL原文如果业务SQL中拼接了用户手机号、身份证、密码等敏感信息这些数据就会明文落在日志文件里。MySQL不会记录认证密码本身但general_log会把INSERT INTO users(name, password) VALUES (x, abc123)这样的语句原样写进文件。所以生产环境尽量别开general_log非开不可时设置文件权限为640属主设置为mysql用户避免共享账号直接读日志文件。第二个日志权限自查。错误日志、慢日志、binlog文件的权限建议全部设置为mysql:mysql 640不要用root或者777。binlog是明文文件任何人能读binlog就等于拿到全量数据变更历史。这也是安全审计最基本的自查项。chown mysql:mysql /data/mysql/logs/*.log chmod 640 /data/mysql/logs/*.log第三个日志目录建议独立分盘。把日志放在根分区和binlog放在数据盘一旦日志膨胀直接把系统盘撑满MySQL直接无法启动救援起来极其痛苦。独立挂载/data/mysql/logs让日志问题只影响日志分区不影响数据库本体。第四个建议定期执行SHOW VARIABLES LIKE %log%核对运行时参数和配置文件里是否一致。MySQL 8.0里部分参数写入my.cnf后运行了才会动态生效配置文件写的和实际内存里的值不一致是排查“我明明改了怎么没用”的高频原因。以实际运行值为准不要以文件为准。还有一个小习惯很管用每次变更日志相关配置后在测试环境重启实例并执行一条慢SQL确认慢日志、binlog确实按预期记录再推到生产。日志管理这类低风险配置最大的风险恰恰是“你以为配置了实际没生效”。MySQL日志管理没有太多玄学就是把每类日志的责任边界理清把参数调对把磁盘盯紧把“先看日志再动手”养成肌肉记忆。遇到故障别急着重启先花几十秒读一遍错误日志多数问题的答案已经在里面了。