提起MySQL日志文件很多后端开发的第一反应是错误日志出了问题才看慢查询日志是DBA才关心的事查询日志更是听过没见过。但实际踩过几轮坑之后你会发现这三类日志加上二进制日志就是MySQL排障和调优最核心的信息源而它们的配置说到底不过是my.cnf里几行参数的事。这篇内容我打算把错误日志、查询日志、慢查询日志从“记录什么”到“怎么配”再到“怎么用”完整捋一遍顺便把二进制日志和中继日志的定位也带出来适合刚把MySQL装起来想梳理日志机制的新手也适合被线上慢SQL折腾得够呛的开发者以及想系统搭一套日志观测体系的运维同学。看完你至少能手写配置、看懂日志内容并且能用慢查询日志实打实优化掉一条问题SQL。1. 日志体系整体思路先搞清楚每种日志记什么1.1 日志总览一个表看全MySQL的六类日志MySQL的日志不是单一概念而是一套组合拳。我习惯把日志分成“服务层日志”和“存储引擎层日志”两类。服务层日志由mysqld进程自己负责写错误日志、查询日志、慢查询日志、二进制日志都在这一层存储引擎层的日志主要指的是InnoDB的重做日志redo log和回滚日志undo log它们负责崩溃恢复和事务一致性平时不太需要人工干预。日志类型默认状态记录内容主要用途错误日志开启启动关闭过程、严重错误、告警信息排查启动失败、连接异常、崩溃原因通用查询日志关闭每一条客户端连接、每一条收到的SQL审计、复现业务SQL、排查应用发错语句慢查询日志关闭8.0默认开超过阈值的SQL、未走索引的SQLSQL性能优化、索引设计依据二进制日志默认开取决于配置所有数据变更事件主从复制、基于时间点的恢复中继日志随复制开启从库接收到的binlog事件主从复制链路中转重做/回滚日志默认开启InnoDB页修改记录、事务回滚段崩溃恢复、事务原子性这张表建议先存下来遇到问题先对号入座MySQL起不来先翻错误日志某条具体SQL执行情况不明查查询日志系统变慢无从下手看慢查询日志数据被误删要靠二进制日志救回来。1.2 为什么大部分日志默认“关着”很多新手会有疑问日志这么重要为什么通用查询日志和慢查询日志默认不开启慢查询日志在MySQL 8.0默认开启且阈值为10秒原因很简单日志是有代价的。通用查询日志会把每一条SQL原样写进文件注意是每一条包括你在命令行里敲的select、应用连接池探活发的select 1甚至连接建立和断开事件也都记录。如果一个实例每秒处理两千条请求日志写入就相当于额外的两千次磁盘顺序写。日志文件还会占据大量磁盘空间我曾经在一个测试库开了一下午general_log日志文件直接冲到30GB小磁盘瞬间报警。慢查询日志默认关闭也是同理把每条慢SQL的详细执行信息写到文件频繁刷日志也会带来额外IO不过影响比general_log小得多。所以日志配置的底层逻辑是默认只保留最关键的错误日志和二进制日志其余按需开启用完之后记得关。1.3 配置入口my.cnf、my.ini与动态调整MySQL在Linux下的配置文件是/etc/my.cnf或/etc/mysql/my.cnfWindows下是my.ini配置项都写在[mysqld]段落下面。有一类参数支持运行时动态调整不需要重启就能生效比如slow_query_log、general_log、long_query_time但另一类则必须在配置文件中预先指定改了之后要重启实例才生效比如log_error指向的文件路径、binlog_format。一个实用的检查命令是SHOW VARIABLES可以看当前生效值。这里要注意用SET GLOBAL改的变量只是内存中的值重启后又会回到配置文件指定的值。想让配置长期生效一定要同步改my.cnf。我的习惯是临时排查问题时用SET GLOBAL动态开启排查完确认有效之后再把配置写进文件并重启验证一次避免下次故障时日志还是没开。2. 错误日志打开它90%的问题都有线索2.1 错误日志到底记了什么错误日志是MySQL服务自身运行状况的记录相当于服务器的“黑匣子”。它不记录业务SQL主要记录mysqld启动过程、初始化过程、运行中遇到的严重错误、告警信息以及InnoDB相关的报错。具体来说我实际在错误日志里见过的内容有启动时提示端口3306被占用、某个表空间文件损坏、InnoDB无法分配内存、主从复制IO线程报错、SSL证书加载失败、磁盘空间不足导致redo log刷盘失败、密码插件初始化异常等等。启动失败类问题基本第一现场都在这里很多人遇到MySQL起不来第一反应是到处查资料其实打开错误日志看一眼尾部往往一句话就把原因说清了。错误日志的命名规则是如果没有显式配置log_errorMySQL默认会在数据目录下生成一个以主机名命名的.err文件比如ubuntu-server.err。Windows下则可能输出到事件日志。2.2 错误日志配置与级别控制错误日志的核心配置项有两个一个是日志文件路径log_error另一个是日志详细程度log_error_verbosityMySQL 5.7.2引入8.0继续沿用。log_error_verbosity的取值是1、2、3三个级别含义分别是1只记录错误信息2记录错误和告警信息默认级别3记录错误、告警和提示信息8.0.31之前之后略有调整在my.cnf里可以这样写[mysqld] log_error /var/log/mysql/error.log log_error_verbosity 2这里分享一个踩过的坑曾经有段时间把log_error_verbosity调成了3本意是收集更多细节结果生产库的error.log每天增长几个GB大量诸如“InnoDB: page_cleaner: 1000ms intended loop took 4203ms”这类提示信息刷屏真正重要的错误反而被淹没在潮水般的日志里。后来调回2之后文件增长速度立刻降下来问题定位也清爽了。生产环境建议就停在2确实需要更详细信息时临时调高排查完再降回去。MySQL 8.0.24之后还引入了log_error_services可以把错误日志输出到syslog、json格式文件等不同目标目前用得不算多常规场景下配置一个错误日志文件已经足够。2.3 实战用错误日志排查三起启动故障第一起是端口被占用。一台机器上同时装了多个MySQL实例新实例启动直接失败看错误日志尾部明确写着“TCP/IP port 3306 already in use”。解决办法是把新实例的port改成3307或者停掉旧实例。第二起是数据目录权限问题。用mysql用户启动服务但数据目录owner是root导致无法创建临时文件错误日志里会有一连串“[ERROR] failed to open log file”或者“Cant create/write to file”之类的提示。这个问题的根源是权限不匹配解决方案是chown把数据目录权限给mysql用户。第三起是磁盘写满。binlog写不进去redo log刷盘失败实例直接进入只读保护状态。错误日志里出现“No space left on device”后紧接着就是“Disk is full writing ./mysql-bin.000123”。这种场景下需要尽快清理磁盘或者扩容同时要注意不要手动删正在写的binlog正确做法是执行PURGE BINARY LOGS清理过期文件。动态查看错误日志最常用的命令是tail -f持续跟踪tail -f /var/log/mysql/error.log做配置变更或重启操作时开一个终端挂着tail错误日志里的每一步过程都清清楚楚。这是DBA的标配习惯强烈建议你也养成。3. 通用查询日志一把必须小心的“双刃剑”3.1 它记录的是什么通用查询日志general query log记录的是MySQL收到的所有客户端请求包括客户端连接和断开事件、每一行SQL语句、线程ID、执行时间等元信息。它的定位就像是监控摄像头把数据库入口处的所有流量忠实录下来。这个日志在什么时候有用我遇到过两个典型场景。第一个是排查“应用到底发了什么SQL”比如线上出现一个奇怪的表锁现象业务方坚称没有执行某条语句打开general_log一看实锤是凌晨批处理任务连库后执行了一条ALTER TABLE。第二个是复现偶发问题应用一直在报“Unknown column”但业务代码里明明没引用这个列最后在general_log里发现是定时任务框架拼接了错误字段名。但要注意general_log不记录查询结果只记录SQL文本所以它不能用来分析执行计划也看不到返回的行数。它只回答“谁在什么时候发了什么”这个问题。3.2 开启方式文件输出与表输出开启通用查询日志有两种输出位置写入文件或者写入mysql库下的general_log表中。通过log_output参数控制取值为FILE、TABLE、FILE,TABLE。配置文件方式[mysqld] general_log 1 general_log_file /var/log/mysql/general.log log_output FILE动态开启方式SET GLOBAL general_log ON; SET GLOBAL log_output TABLE;设置为TABLE输出的好处是不用登录服务器去看日志文件直接SQL查询即可SELECT event_time, user_host, thread_id, argument FROM mysql.general_log WHERE event_time NOW() - INTERVAL 5 MINUTE ORDER BY event_time DESC;表输出在排查时非常顺手尤其适合需要按条件过滤的场景。磁盘里如果不想堆文件用表输出更整洁。但表本身也会不断膨胀同样需要定期清理。3.3 实测性能影响与安全开关我做过一次简单压测一个约每秒500查询的测试库开启general_log后TOP命令里mysqld的CPU使用率上升了大约15%到20%磁盘写IO明显增加因为每条SQL都要额外写一条日志。如果线上并发上千影响会更明显。所以我的原则是生产环境默认不开确需开时选择业务低峰期开10到30分钟收集足够样本就关闭。排查完一定要记得关SET GLOBAL general_log OFF;曾经有人开了general_log忘记关一周后磁盘报警日志文件占了几十GB删日志也花了半天。这种“忘记关”的事故其实比开启本身更常见。3.4 查询日志的一个实用场景有一点值得单独说很多人不知道general_log可以被用来快速确认“某条SQL到底有没有执行过”。比如怀疑某个定时任务是否连上了错误的环境或者怀疑连接池是否反复建立了新连接查询general_log会得到最直接的证据。举个例子有一次应用连接数一直涨从general_log中看到同一个应用服务器IP每隔几百毫秒就出现一次Connect和Quit事件而正常的连接池复用不会这么频繁。查下来发现是连接池配置的maxLifetime小于数据库侧wait_timeout导致连接被反复销毁重建。这类问题靠慢查询日志看不出来靠错误日志也看不出来只有general_log能暴露连接层的行为。这也是日志体系需要组合使用的典型场景。4. 慢查询日志SQL优化最值得依赖的依据4.1 慢查询日志的核心判定条件慢查询日志记录的是执行时间超过long_query_time阈值的SQL默认阈值是10秒这个值对绝大多数互联网业务来说太大了线上建议改成1秒甚至更低。MySQL 5.7及以上的long_query_time可以设置小数比如0.5表示500毫秒。有一点很重要long_query_time的计时起点是查询开始执行到查询结束的总耗时但不包括初始化阶段和等待获取锁的时间。也就是说一条SQL因为锁等待卡了很久在慢查询日志里可能并不会被记录为慢查询因为实际执行时间很短。这个设计容易造成误解需要特别说明。慢查询日志的判定条件一共有三个单条SQL执行耗时大于等于long_query_time未使用索引的SQL会被记录前提是log_queries_not_using_indexes参数开启管理类语句如ALTER TABLE默认不记录除非打开log_slow_admin_statements4.2 配置参数与持久化一套比较实用的慢查询配置长这样[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1 log_slow_admin_statements 0 log_throttle_queries_not_using_indexes 10 min_examined_row_limit 100几个参数逐个说long_query_time 1超过1秒的SQL都会被记录建议根据业务调整如果业务整体延迟很低可以设为0.5。log_queries_not_using_indexes 1把没走索引的SQL也记录下来。这个非常关键很多慢查询的根因就是全表扫描索引都没用上耗时当然上去了。min_examined_row_limit 100只记录扫描行数超过100条你的实际情况可调的SQL避免把大量扫描1行但耗时略长的SQL刷进日志。log_throttle_queries_not_using_indexes 10控制未走索引SQL的写日志频率同一类SQL每分钟最多记录10条防止慢查询日志被同样的坏SQL刷爆。log_output FILE,TABLE可以同时输出到文件和表不过项目中我一般只输出文件分析时用mysqldumpslow工具更高效。动态开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;这里有一个容易踩的坑修改long_query_time后已经建立的连接可能还在用旧值需要重新连接一次才会生效。实测中如果发现改了阈值但日志行为没变先检查新连接是否生效。4.3 分析工具实战mysqldumpslow、pt-query-digest慢查询日志是纯文本文件行数多了直接看会眼花。MySQL自带一个分析工具mysqldumpslow基本用法如下# 按平均耗时排序输出前20条最慢SQL mysqldumpslow -s at -t 20 /var/log/mysql/mysql-slow.logmysqldumpslow会把相似的SQL自动归类把具体参数值替换成N这样同一模板的SQL会被汇总成一条统计出现次数、平均耗时、最大耗时等。在MySQL 8.0中mysqldumpslow位于MySQL安装目录的bin目录下5.7同样。如果想看得更细推荐percona-toolkit里的pt-query-digestpt-query-digest /var/log/mysql/mysql-slow.log slow_report.txtpt-query-digest会生成一份分级的报告先列出最耗时的SQL模板再显示每条SQL的响应时间占比、执行次数、平均耗时、最大耗时甚至能关联到具体的表名。我处理线上慢SQL的标准流程就是先跑一遍pt-query-digest拿到TOP 10之后逐条EXPLAIN分析。另外一个天然优势是慢查询日志可以直接存到表里。如果配置了log_output TABLE那么可以查mysql.slow_log表SELECT query_time, rows_examined, rows_sent, digest_text FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;4.4 一个典型慢SQL的优化全程说一个真实案例。某业务表orders有大约86万行有一段时间线上接口频繁超时慢查询日志里出现一条SELECT order_id, user_id, total_amount FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 20;用EXPLAIN查看执行计划关键信息是typeALLrows862145Extra里还有Using filesort。这意味着MySQL对整张表做了全表扫描并且在内存或磁盘上执行了排序最后才取20条返回。扫描86万行再排一次序耗时轻松超过2秒。当时我加了一个复合索引ALTER TABLE orders ADD INDEX idx_status_create_time (status, create_time);这个索引的设计思路是WHERE条件用status等值匹配ORDER BY用create_time排序联合索引可以同时覆盖过滤和排序两个需求让MySQL免去filesort。加索引后用EXPLAIN再看type变成了refrows1Extra里不再有Using filesort查询耗时降到了20毫秒以内。整个优化核心动作就是加索引看起来简单但如果没有慢查询日志定位到具体SQL你连是哪条语句慢都不知道。4.5 慢查询日志的几个“坑”第一个坑是阈值改了但没生效前面提过原因是已存在的连接还在用旧参数。第二个坑是min_examined_row_limit和long_query_time的关系只检查行数不到阈值的最小扫描行数有些行数很少但计算复杂的SQL可能漏记录。第三个坑是慢查询日志不记录锁等待时间所以如果你发现慢日志里没有对应记录但线上又有明显卡顿需要去processlist和InnoDB锁监控里找线索。第四个坑是MySQL 8.0在某些平台默认开启慢查询日志但阈值为10秒如果不主动调低等线上真的出现大量慢SQL时日志才刚有动静等到发现时问题可能已经持续很久了。装完MySQL之后第一个动作就应该是把long_query_time改成1秒千万别等出了事再调。这和“mysql安装教程”里说的初始化配置往往只讲字符集和密码策略不讲日志阈值其实是很重要的一环。另外慢查询日志里记录的是SQL文本但不记录执行计划如果想知道为什么慢得把SQL拿出来重新EXPLAIN。日志只是线索不是结论。5. 标题里那个“等”二进制日志与中继日志的定位5.1 binlog复制与恢复的基础标题里的“等”字最该补充的就是二进制日志binlog。它记录的是所有导致数据变更的事件包括INSERT、UPDATE、DELETE、DDL不包括SELECT。binlog有两个核心用途一是主从复制从库通过读取主库的binlog来同步数据二是崩溃和误操作后的恢复可以把数据库恢复到过去某个时间点的状态。浅要配置示例[mysqld] server-id 1 log-bin mysql-bin binlog_format ROW sync_binlog 1 binlog_expire_logs_seconds 2592000binlog_format有三种STATEMENT、ROW、MIXED。生产环境通常用ROW因为行格式记录的是变更前后的实际值复制一致性最好。sync_binlog1表示每次事务提交都强制把binlog刷到磁盘是最安全的配置代价是稍微增加提交延迟但对数据安全要求高的场景值得。binlog文件的保留时间很重要。MySQL 8.0里用binlog_expire_logs_seconds控制单位是秒上面配置的2592000就是30天。5.7及更早版本常用expire_logs_days控制单位是天。如果这个参数配得太小误删数据后想恢复却找不到日志文件那才是最痛苦的配得太大又会撑爆磁盘。一般按备份策略周期顺延一周比较合适。5.2 relay log与事务日志顺带了解即可中继日志relay log出现在从库上从库的IO线程把主库binlog拉到本地先写入relay log然后由SQL线程读取并执行。它对业务应用基本透明不需要手动维护相关参数relay_log_purge1表示中继日志用完后自动清理。InnoDB的redo log和undo log属于存储引擎层。redo log是物理日志记录数据页的修改用于崩溃恢复它是环形写入的写满后需要检查点推进才能继续循环。undo log则用于事务回滚和MVCC版本链。这两类日志不需要人工配置但它们与MySQL性能密切相关尤其是在高并发写入场景下redo log刷盘策略innodb_flush_log_at_trx_commit的取值会显著影响性能和可靠性。这个点属于进阶话题这里先记住有这么回事即可。6. 排查实践日志不产生、磁盘爆掉、时间不对6.1 高频问题速查表日志相关的坑无外乎几类我直接整理成速查表按症状定位和处理办法对照着看。症状可能原因处理建议日志文件不生成参数没生效、路径目录不存在或权限不足SHOW VARIABLES检查参数确认目录存在且有mysql用户写权限日志文件存在但一直是空文件日志级别设置过高、条件未触发降低阈值手动执行一条慢SQL验证是否记录日志磁盘占用过大general_log忘记关闭、verbosity过高、binlog保留时间过长立即关闭非必要日志删除过期binlog配置logrotate日志里时间与服务器时间不一致时区未设置log_timestamps参数决定SET GLOBAL log_timestamps SYSTEM或显式指定时区慢查询日志没有预期SQL锁等待不计入、min_examined_row_limit过滤、阈值过大结合processlist和锁监控调整阈值和row limit修改参数后不持久只用了SET GLOBAL没写配置文件同时修改my.cnf重启后验证6.2 日志轮转的三种做法日志文件如果不轮转最终必然撑爆磁盘。最常见的做法是用Linux的logrotate。以MySQL错误日志和慢查询日志为例在/etc/logrotate.d/mysqlshs中配置/var/log/mysql/*.log { daily rotate 14 compress missingok create 640 mysql mysql postrotate # 让MySQL重新打开日志文件 if test -x /usr/bin/mysqladmin /usr/bin/mysqladmin ping /dev/null; then mysqladmin --login-pathlocal flush-logs fi endscript }flush-logs会触发日志文件重新打开这样轮转后的新日志会写到新的文件。另一种做法是把日志输出到mysql.slow_log表手动定期DELETE历史数据。第三种做法对binlog格外重要不要直接删文件使用PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY清理或者依靠expire配置自动清理。6.3 一次线上故障排查日志是怎么配合的最后串一个我经历过的真实场景通过这个流程能看到各种日志在实战中如何配合。某天告警群里提示主库连接数飙升到80%应用大量超时。我排查的顺序是这样的先看错误日志发现大量“Aborted connection ... Reading packet from client”和“Got an error reading communication packets”说明有客户端连接异常断开。再看processlist发现大量会话卡在“Sending data”状态同一个SQL反复出现。把这条SQL从慢查询日志里捞出来EXPLAIN一看全表扫描扫描行数上百万。进一步看binlog确认同一时段有大量UPDATE事件结合应用日志定位到一个新上线的循环任务。综合下来根因是任务批量更新时没有走索引每次更新都触发全表扫描加上连接池反复重建连接把数据库拖垮了。整个过程里错误日志提供“连接异常”的信号慢查询日志提供“SQL效率低”的证据binlog帮助确认了变更模式缺一个都不好查。这也是我为什么强调日志配置要提前做好而不是等事故来了再想办法。6.4 日志监控不是看一眼就完有一点我想多说一句日志配置好之后不能放着不管。我见过太多项目的MySQL日志配置得漂漂亮亮但上线半年没人看过一眼等磁盘报警时才打开文件发现里面已经积累了几个GB的历史日志有价值的信息全被淹没了。日常运维建议每周花十分钟做三件事看一眼error.log里有没有新的Warning级别以上内容看慢查询日志里有没有新出现的SQL模板检查binlog和日志文件的占用趋势。MySQL本身没有内置太强的日志告警能力最简单的方式是用crontab定时统计日志文件大小或者用shell脚本检测慢查询日志新增条数超过阈值就发提醒。这种方式够用且不复杂比一开始就上复杂监控体系要实际得多。我个人在实际操作中最大的体会是日志配置这件事踩坑大多不是不会写参数而是“开了没看”和“看了没分析”。像long_query_time调到1秒这种动作花十秒钟就能完成但换来的是下次性能问题出现时你有据可查、有线可循。如果这篇内容能帮你避免一次“日志没开导致问题无法回溯”的尴尬那这些字就值了。