★ FEATURED ARTICLE MySQL日志经常是被忽略的模块等到磁盘被塞满、主从断了、误删了数据才追悔莫及。这篇文章基于我这些年排查数据库故障的实际经验围绕MySQL的六大核心日志类型——错误日志、通用查询日志、慢查询日志、二进制日志、中继日志和InnoDB重做日志详细讲解每类日志的作用、配置方法、查看方式和常见坑点适合需要真正掌握数据库运行状态的开发者、DBA以及对MySQL有进阶需求的运维人员。1. 为什么日志是MySQL的“黑匣子”1.1 日志在数据库生命周期中的角色经常有同事问我MySQL出问题了第一步干什么我的答案永远是看日志。日志相当于数据库的“黑匣子”记录了MySQL从启动、连接到执行SQL、事务提交、崩溃恢复再到主从同步的完整轨迹。没有日志排查问题就像在黑暗里找钥匙只能靠猜。MySQL的日志体系不是单一模块而是分了多个层次。有一类日志服务于“人”比如错误日志、慢查询日志、通用查询日志它们的核心目的是让你看清数据库当前的健康状态另一类日志服务于“机制”比如二进制日志、中继日志、重做日志它们承担了数据恢复、主从复制、崩溃恢复等底层职责。理解这种分层关系能帮助你判断遇到问题时该去看哪一类日志而不是眉毛胡子一把抓。1.2 六大核心日志类型总览先给大家一张总览表方便建立整体印象。后面每个章节再逐个深入。日志类型默认状态主要作用常见使用场景错误日志Error Log开启记录启动、运行、关闭过程中的错误和警告排查启动失败、连接异常、主从报错通用查询日志General Log关闭记录所有客户端连接和SQL语句审计、定位应用发来的“神秘SQL”慢查询日志Slow Query Log关闭记录执行时间超过阈值的SQL性能优化、索引调优二进制日志Binlog建议开启记录所有数据变更事件数据恢复、主从复制中继日志Relay Log从库默认开启暂存主库binlog并回放主从复制链路InnoDB重做日志Redo LogInnoDB引擎自带保证事务持久性与崩溃恢复宕机恢复、写入性能调优很多初学者会把binlog和redo log搞混。简单说binlog是MySQL Server层生成的逻辑日志记录的是SQL语句或行变更redo log是InnoDB引擎层生成的物理日志记录的是数据页的修改。一个管逻辑复制一个管物理恢复各司其职不能互相替代。2. 错误日志与通用查询日志日常运维的“眼线”2.1 错误日志的配置与实战阅读错误日志是MySQL默认开启的日志几乎所有“数据库起不来”“连接数爆了”“主从中断”这类问题第一现场都在这里。它记录了服务启动和关闭的过程、系统运行时的严重错误、权限问题以及复制链路的异常信息。错误日志的位置可以通过参数查看SHOW VARIABLES LIKE log_error;默认情况下错误日志的文件名是主机名加.err后缀比如mysql-ubuntu.err。如果使用systemd管理MySQL日志也可能被重定向到journald中此时可以执行journalctl -u mysqld查看。我读错误日志的习惯是三步走先看最后50行定位最近的异常再搜索ERROR关键字过滤掉无关警告最后结合报错时间点和应用发布记录交叉比对。比如常见的Cant start server: Bind on TCP/IP port: Address already in use说明3306端口被占用Table ./xx/xx is marked as crashed and should be repaired说明表损坏需要尽快用CHECK TABLE和REPAIR TABLE处理。2.2 通用查询日志抓取全量SQL的正确姿势通用查询日志会把每一个客户端连接、每一条收到的SQL语句都记下来。听起来很美好但默认关闭是有道理的全量记录对于压力大的生产环境是灾难日志文件会在短时间内疯狂膨胀磁盘再大也扛不住。不过在一些特殊场景里它又是无可替代的。比如应用突然出现一坨诡异SQL业务方说不清是谁发的错误日志和慢查询日志里又没有明显线索这时就可以临时打开通用查询日志抓取全量语句来定位。临时开启的方式很灵活不需要重启SET GLOBAL general_log ON; SET GLOBAL general_log_file /tmp/mysql_general.log;我一般会把文件路径先改到独立目录避免和错误日志混在一起。排查结束后立刻关闭SET GLOBAL general_log OFF;这里有个坑要提醒通用查询日志记录的是明文SQL可能包含敏感数据的查询条件。在安全要求高的环境里开启前要评估合规风险并且限制文件读取权限用完马上清理。3. 慢查询日志性能优化的第一现场3.1 慢查询日志的关键参数与开启方法慢查询日志是DBA做性能优化的首选工具。它专门记录那些执行时间超过阈值的SQL是发现“拖垮数据库的元凶”最直接的手段。核心参数有以下几个参数名默认值说明slow_query_logOFF是否开启慢查询日志long_query_time10.000000执行时间超过该秒数的SQL会被记录单位秒slow_query_log_file主机名-slow.log慢查询日志文件路径log_queries_not_using_indexesOFF记录所有未走索引的SQL即使是秒级以下的查询min_examined_row_limit0记录前至少扫描多少行的SQL用于过滤小查询我建议生产环境至少把long_query_time设置为1秒很多系统对超过1秒的查询已经敏感了。对于刚开始优化的业务可以先放到2秒避免日志量过大等慢慢优化完再收紧阈值。如果希望排查“走了全表扫描但单条不慢”的隐性慢SQL可以把log_queries_not_using_indexes打开。这个参数非常有用很多压垮数据库的问题正是大量“单条不慢合计致命”的全表扫描查询。3.2 慢日志分析与定位慢SQL的实战技巧光有日志还不够怎么从海量慢SQL里找出真正需要优化的目标才是功夫所在。我常用的工具是Linux自带的mysqldumpslow它按SQL语句结构做聚合统计能快速排出“查询次数最多”“平均耗时最长”“总耗时最长”的Top列表。常用命令示例# 按查询总耗时排序取前5条 mysqldumpslow -s t -t 5 /var/lib/mysql/mysql-slow.log # 按查询次数排序取前10条 mysqldumpslow -s c -t 10 /var/lib/mysql/mysql-slow.logmysqldumpslow会把SQL中的具体数值替成N字符串替成S这样不同参数的同结构SQL就能聚合到一起。这一点很重要直接看裸日志很难从几千条相同结构的SQL里看出压力集中点。如果慢SQL量特别大建议换用Percona Toolkit里的pt-query-digest。它能生成一份包含总耗时、平均耗时、响应时间分布、样例SQL在内的完整分析报告比自带工具直观得多。分析慢SQL时我的核心关注点是这条SQL是否走了合适的索引、是否因为字段类型转换导致索引失效、是否在事务里执行了大量不必要的小查询。大多数性能事故归根结底都是这几个原因。4. 二进制日志备份恢复与数据同步的基石4.1 binlog的三种格式如何选择二进制日志也就是常说的binlog记录了所有导致数据库内容变化的操作比如INSERT、UPDATE、DELETE以及DDL语句。它是做时间点恢复、搭建主从复制、实现增量备份的基础。binlog有三种记录格式选择时需要在“准确性”和“资源消耗”之间做权衡格式记录内容优点缺点STATEMENT原始SQL语句日志量小便于阅读部分函数和上下文依赖会导致主从数据不一致ROW行变更前后的实际值一致性最强任何变更都能准确回放日志量大尤其批量更新时MIXED自动判断默认用STATEMENT存在不确定性时自动切ROW行为和结果需要充分理解我在生产环境默认选ROW格式。虽然日志量偏大但换来的是主从之间绝对可靠的数据复制尤其在处理NOW()、UUID()这类不确定函数时不会出幺蛾子。MySQL 8.0默认也是ROW格式这本身就是官方给出的方向性建议。4.2 binlog的日常管理查看、刷写与清理binlog的日常操作离不开几个命令-- 查看当前正在写入的binlog文件 SHOW MASTER STATUS; -- 查看所有binlog文件列表 SHOW BINARY LOGS; -- 查看指定binlog的内容 SHOW BINLOG EVENTS IN mysql-bin.000023;实际查看binlog内容时用SHOW BINLOG EVENTS并不友好尤其是ROW格式直接显示为base64编码的行数据。更推荐用官方自带的mysqlbinlog工具解析mysqlbinlog --base64-outputDECODE-ROWS -v /var/lib/mysql/mysql-bin.000023关于binlog的清理很多刚接触MySQL的人会陷入一个误区因为磁盘空间紧张直接手动删除mysql-bin.0000xx文件。这个操作非常危险一旦删除的binlog正好是某个从库还没拉取的节点主从复制会立刻中断而且无法自动恢复。正确的清理方式是设置过期时间让MySQL自己管理-- MySQL 5.7及以前 SET GLOBAL expire_logs_days 7; -- MySQL 8.0推荐 SET GLOBAL binlog_expire_logs_seconds 604800;设置完成后还可以手动执行PURGE BINARY LOGS BEFORE NOW() - INTERVAL 7 DAY;清理指定日期之前的binlog。如果确定某个binlog已经没有任何从库需要读取也可以安全地执行PURGE BINARY LOGS TO mysql-bin.000023;只保留从这个文件之后的binlog。5. InnoDB重做日志与中继日志事务与主从的幕后引擎5.1 redo log的工作原理与调优很多人不理解redo log存在的意义我习惯用一个比喻你在写一本厚书如果每改一页都要立刻把整本书抄一遍成本太高。更聪明的做法是找一张草稿纸先记下“这一页改了哪几个字”等草稿纸写满了再统一誊到正本上。这张草稿纸就是redo log正本就是磁盘上的数据文件。InnoDB采用WALWrite-Ahead Logging策略事务提交时必须先把redo log刷入磁盘才能返回提交成功。这样即使数据库瞬间宕机内存里还没来得及落盘的数据页也可以借助redo log在恢复时重放不会丢失已提交事务的数据。redo log相关的核心参数是innodb_log_file_size和innodb_log_buffer_size。前者决定每个redo日志文件的大小后者决定内存中日志缓冲区的大小。如果日志文件太小会导致频繁的刷盘检查点影响写入性能太大则可能导致崩溃恢复时间过长。对于普通的OLTP业务我建议innodb_log_file_size从1GB起步但修改这个参数需要重启实例务必在低峰期操作。5.2 中继日志与主从复制故障排查中继日志是主从复制架构里从库特有的日志。主库把binlog推送给从库后从库先把这些数据写进本地relay log再由SQL线程读取并回放。之所以中间多这一层是为了将“网络接收”和“SQL执行”解耦避免网络延迟拖慢SQL线程。实际运维中relay log最常遇到的问题有两类。第一类是relay log损坏通常表现为SQL线程报错Relay log read failure。遇到这种情况常规做法是停止从库复制、重置中继日志再重新配置STOP REPLICA; RESET REPLICA ALL; CHANGE REPLICA TO ...; START REPLICA;第二类是relay log无限膨胀。正常情况下relay log里的数据被SQL线程回放后会自动删除由relay_log_purge1参数控制。但如果SQL线程长期停止relay log就会持续堆积直到塞满磁盘。处理思路是先定位SQL线程为什么停解决复制异常后再考虑清理不要贸然删除文件。6. 日志管理的常见问题与排查实录6.1 日志膨胀与磁盘空间告急的处理日志引发的最普遍事故就是磁盘被写满。binlog、慢查询日志、通用查询日志、redo log都会持续增长如果没有合理的生命周期管理再大的磁盘也不够用。典型的排查步骤是# 查看各目录和日志文件占用 du -sh /var/lib/mysql/* | sort -rh | head -20确认是哪个日志膨胀后按类型区别处理。binlog膨胀优先检查binlog_expire_logs_seconds是否配置正确以及是否有从库消费跟不上主库的写入速度慢查询日志膨胀说明数据库本身存在大量慢SQL只删日志不解决问题最好把long_query_time先调大兜底留下时间分析根因通用查询日志膨胀最简单确认审计任务结束立即关闭并删除历史文件。这里要特别提醒redo log是不能通过删除文件来“清理”的。它是InnoDB落盘机制的一部分删掉redo log会导致数据库无法正常启动。如果想给redo log“瘦身”只能通过调整innodb_log_file_size加上正常重启来重新规划千万不要手动删文件。6.2 日志相关配置的最佳实践清单从经验看我建议按以下清单来配置数据库日志。这套清单不是绝对标准但踩过足够多的坑之后它确实能帮你避开大部分麻烦错误日志必须开启且建议单独放在独立的挂载盘避免和应用日志抢占磁盘I/O。慢查询日志生产环境建议开启阈值从1秒起步配合log_queries_not_using_indexes做索引质量巡检。binlog开启并设置保留窗口建议保留3到7天格式用ROW配合GTID便于主从切换。通用查询日志默认关闭只在审计排障时临时开启用完立即关闭。relay log保持自动清理关注从库SQL线程状态异常时优先恢复复制再考虑清理。redo log根据写入量评估innodb_log_file_size不要为了省磁盘把日志文件设得过小。配置参数修改之后务必用SHOW VARIABLES逐一确认生效。很多故障的根因其实是配置写错了位置或者没生效。比如my.cnf里把参数写进了[mysql]段而不是[mysqld]段MySQL启动时根本不会识别表面看配置了实际是默认值在跑问题自然层出不穷。6.3 快速判断“该看哪类日志”的速查表面对一个陌生故障最怕的就是毫无头绪地翻文件。我整理了一张速查表你可以把报错症状和对应日志类型配对快速缩小排查范围。故障症状优先看哪类日志重点关注什么MySQL启动失败错误日志端口占用、配置文件错误、表损坏连接数瞬间到顶错误日志 通用查询日志连接拒绝记录、异常的连接来源某个业务SQL变慢慢查询日志响应时间分布、扫描行数主从不同步错误日志 中继日志SQL线程报错、relay log读取失败误删数据需要找回二进制日志结合备份做时间点恢复数据库无故重启错误日志内存溢出、OOM记录、异常断电恢复信息磁盘空间满所有日志定位是binlog、慢日志还是redo日志膨胀这张表是我日常排障时反复使用的路径。多数故障都能通过一到两类日志快速定位根因剩下的大部分是“日志没开”导致的盲区。我个人实际操作的体会是日志配置这件事最怕的不是不会用而是“平时不管出事才看”。把日志当成数据库的一部分来做日常巡检比任何监控告警都踏实。日志不会骗人它记录的就是数据库真实发生过的每一件事。你花在日志上的时间最终都会在故障处理时加倍还给你。
阅读完成 · 觉得有帮助？
