1. 先理清楚MySQL到底有哪几类日志干MySQL这行不管是开发、运维还是DBA日常工作里有一半时间其实都在跟日志打交道。很多人一遇到问题就急着看网上的“万能解决方案”或者直接搜报错关键词结果越查越乱。我的习惯是先搞清楚一个基本问题当前手上的问题该看哪一类日志MySQL的日志体系并不复杂但很容易被人忽略。其实它主要分为六个方向错误日志Error Log、慢查询日志Slow Query Log、通用查询日志General Log、二进制日志Binlog、中继日志Relay Log以及InnoDB存储引擎内部的Redo Log / Undo Log。前五种是MySQL Server层面能直接配置和查看的后两种是存储引擎自己管理的内部日志用户一般不需要直接操作。这几种日志的分工非常明确我用一个生活化的类比来帮助记忆错误日志像公司的体检报告哪里出问题一查便知慢查询日志像员工的考勤记录专门揪出干活慢的SQL通用查询日志像大门口的监控录像谁进来、说了什么、干了什么全都有记录binlog则像银行流水账本每一笔数据变更都按时间顺序记着账要核对、数据要恢复都靠它。在实际操作中我见过很多人在排查问题时走弯路最典型的场景是一条SQL跑了很久负载很高有人第一反应是去看错误日志翻半天什么都没找到其实这时候该看的是慢查询日志。反过来数据库突然连接不上有人去翻binlog那也是白费力气正确入口是错误日志。你手上是什么症状决定了你应该去看哪一类日志。考虑到不少人用的MySQL版本各不相同这里我用一张表把这五类日志的概貌先列出来后面再逐个拆。日志类型默认状态记录内容主要用途错误日志开启启动关闭过程、错误信息、告警信息排查启动失败、连接异常、主从复制故障慢查询日志默认关闭执行时间超过阈值的SQL语句SQL性能优化、索引设计通用查询日志默认关闭所有客户端连接和执行的SQL语句审计行为、排查应用端问题二进制日志Binlog默认关闭云RDS默认开启所有数据变更操作主从复制、数据恢复、CDC同步中继日志Relay Log由复制拓扑自动管理主库binlog在从库上的本地副本主从复制链路注意上面这张表的“默认状态”列这个是我们判断问题入口的重要依据。比如你什么都没配置MySQL只能帮你记错误日志慢查询和通用查询日志都得手动开启。所以排查问题的第一步永远是先确认你要看的那一类日志到底有没有开。2. 日志文件在哪定位方式与开启配置我知道不少人刚接触MySQL时最头疼的问题不是日志怎么看而是“日志到底在哪个文件夹里”。其实定位日志文件有三种非常可靠的方式我从最推荐的方法讲起。第一种方式是通过SQL直接查。登录MySQL后执行SHOW VARIABLES LIKE log_error; SHOW VARIABLES LIKE slow_query_log_file; SHOW VARIABLES LIKE general_log_file; SHOW VARIABLES LIKE log_bin_basename;这个方式在任何MySQL版本上都适用也是最准确的方式因为不同发行版、不同安装方式日志路径差异很大。比如CentOS上用rpm方式安装的MySQL错误日志可能默认在/var/log/mysqld.log而源码编译安装的则会放到数据目录下以主机名.err命名的文件里。Windows上安装的MySQL日志默认在数据目录下比如C:\ProgramData\MySQL\MySQL Server 8.0\Data\文件名为DESKTOP-XXXX.err。所以最好不要凭经验猜路径一条SQL查出来是最实在的。第二种方式是看配置文件my.cnfWindows上是my.ini。你可以在[mysqld]段下找到显式配置的路径比如[mysqld] log_error/var/log/mysql/error.log slow_query_logON slow_query_log_file/var/log/mysql/slow.log long_query_time1 log_binmysql-bin binlog_formatROW这里有一个很重要的点如果配置文件的[mysqld]段下没有写log_error或者slow_query_log那说明使用的是编译时的默认值这种情况下通过第一种SQL方式查询看到的路径才是最真实的。第三种方式是在系统层面找。Linux下可以使用find、locate或ls查找find / -name *.err 2/dev/null | grep -i mysql find / -name mysql-bin.* 2/dev/null对于rpm安装的MySQL还可以检查/var/log/mysql/这样的目录是否存在。顺便说一句很多人在“无日志文件夹怎么办”这个问题上卡住多半是MySQL的数据目录权限不对导致日志无法创建这在后面常见问题部分我会专门展开讲。把日志位置搞清楚了接下来要讲的是如何开启和配置日志。我建议的配置方式是在配置文件中统一指定路径和开关然后重启MySQL让配置生效。但这种做法有个前提就是你的MySQL允许重启。如果线上环境不能随便重启MySQL也支持动态修改部分日志参数不需要重启。举个例子SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_output FILE;这三个语句分别用于开启慢查询日志、设置慢查询阈值、指定日志输出方式。注意log_output这个参数有两个取值FILE代表写入文件TABLE代表写入mysql.slow_log和mysql.general_log两张系统表中。线上调优时我建议设置成FILE因为表格方式在高并发下会带来额外的系统表写入开销而且查询历史SQL用文件配合mysqldumpslow这样的工具更顺手。但有几个参数不能动态修改比如log_bin这个开启binlog的总开关必须在配置文件里设置后重启MySQL才能生效。这里插一句MySQL 8.0社区版默认不开启binlog但很多云厂商RDS默认会开启因为数据恢复和CDC同步都需要binlog。你如果是自己安装的MySQL做数据恢复演练、部署主从复制、接Flink CDC同步数据都得先把binlog打开。3. 重点日志逐个拆错误日志、慢查询日志、通用查询日志3.1 错误日志启动失败和连接问题的第一现场错误日志是MySQL里最基础、也最应该养成习惯去看的日志。它记录的是MySQL服务从启动到关闭整个过程里出现的错误、告警和一些重要事件包括启动参数加载、InnoDB初始化、复制线程启停、SSL连接错误、权限问题等等。我记得有一次帮朋友排查数据库无法启动的问题直接看/var/log/mysql/error.log最后几行写得清清楚楚[ERROR] InnoDB: Operating system error number 13 in a file operation这是典型的文件权限不足。把数据目录的属主改成mysql:mysql后服务马上就能起来了。所以遇到任何MySQL本身无法工作的问题第一个动作就是打开错误日志不要先去网上搜报错码。日常查看错误日志最朴素也最有效的方法就是看末尾tail -n 100 /var/log/mysql/error.log如果要持续跟踪启动过程或者实时排障可以tail -f /var/log/mysql/error.log另外关于错误日志里的关键字有几个值得特别关注[ERROR]表示致命错误必须处理[Warning]表示潜在风险比如密码过期策略、SSL未配置等[Note]是正常提示信息通常不用管。线上出现客户端连接报错时很多人只盯着应用端的报错信息其实[Note]级别里记录的Aborted connection往往藏着答案比如连接被异常中断、超过wait_timeout等这些都暗示着连接池或网络层面有问题。3.2 慢查询日志SQL性能优化的第一抓手慢查询日志是我个人最常用、也最推荐优先开启的日志。它记录的是执行时间超过阈值的SQL语句是分析数据库性能瓶颈、发现索引缺陷最重要的依据。默认情况下它是关闭的需要手动打开。开启方式有两种一种是在配置文件中永久开启slow_query_logON slow_query_log_file/var/log/mysql/slow.log long_query_time1 log_queries_not_using_indexesON另一种是运行时动态开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;关于慢查询阈值的设置我个人的经验是一般业务系统建议long_query_time设置为1秒也就是执行超过1秒的SQL都记录下来如果是高并发低延迟的OLTP系统可以设置为0.5秒甚至更低因为这类系统里1秒的需求已经算是非常慢了但要注意阈值设得太小比如0.1秒会让日志文件膨胀得非常快反而淹没真正需要关注的慢SQL。慢查询日志里每一条记录包含的信息很丰富查询时间、锁等待时间、返回行数、扫描行数、SQL语句等。举个例子日志里出现类似下面这条记录# Query_time: 3.800000 Lock_time: 0.000200 Rows_sent: 10 Rows_examined: 2500000 SELECT * FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 10;Query_time是3.8秒Rows_examined是250万行但最终只返回10行这就是典型的没有走索引导致的慢查询。处理思路非常明确给orders表的user_id建立索引。如果已经建立了索引但依然慢可能就要考虑索引失效、数据类型隐式转换、或者排序字段导致查询没有走上最优索引等问题。3.3 通用查询日志不到万不得已别开通用查询日志记录的是所有连接到MySQL的客户端执行的所有SQL语句包括连接和断开的事件记录像数据库的全量监控摄像头。这个日志默认关闭因为开启后对性能影响极大在高并发场景下会显著增加磁盘I/O开销可能让整体吞吐量下降20%以上。什么情况下需要开启通用查询日志我自己的实践是当应用端收到SQL语法错误、但应用本身没有打出完整SQL文本时可以用来快速定位问题SQL或者在审计场景下需要了解某个时间段内所有用户执行过的具体语句时。排完问题后记得马上关闭避免持续产生性能损耗和磁盘占用。运行时开启SET GLOBAL general_log ON; SET GLOBAL log_output TABLE;上面我故意设置了TABLE因为通用查询日志本身是排查问题用的在排查问题期间把日志写到系统表mysql.general_log中查询、筛选会非常方便。用完了直接关掉再把表备份一下或者清空即可。但如果你胆子大一点直接设置log_outputFILE也是可以的只是处理起来不如表格方便。3.4 Binlog最值得吃透的日志如果说前面三类日志是排查问题的工具那binlog就是数据安全和架构扩展的基础设施。它的核心价值有三个主从复制、数据恢复、变更数据捕获CDC。binlog记录的是所有数据变更操作DDL和DML格式由binlog_format控制主要有三种格式记录方式优点缺点STATEMENT记录SQL原文日志量小部分函数、触发器场景可能造成主从数据不一致ROW记录具体行的变更前后内容最精确推荐日志量大MIXED自动选择兼顾两者复杂度高不推荐自己折腾我在实际生产中通常都会选择ROW格式虽然日志量会大一些但换来的是一致性和可恢复性。比如一个UPDATE语句批量更新了100万行数据在STATEMENT格式下只记一条SQL在ROW格式下会记录100万条变更记录日志体积差很多但ROW格式能精确知道每一行到底变成了什么值。做数据恢复或者Flink CDC同步到ClickHouse这类下游系统时只有ROW格式才能拿到完整的数据变更细节。查看当前binlog的状态和文件列表SHOW VARIABLES LIKE log_bin; SHOW BINARY LOGS; SHOW MASTER STATUS;SHOW BINARY LOGS会列出所有binlog文件每个文件通常是mysql-bin.000001、mysql-bin.000002这种命名默认单个文件大小由max_binlog_size控制默认1GB。SHOW MASTER STATUS则是查看当前正在写的binlog文件和position位置这个在做主从复制配置时是核心信息。查看binlog文件内容不能直接用cat因为它是二进制格式。要用自带的mysqlbinlog工具关键用法是加--base64-outputdecode-rows -v参数把ROW格式的binlog解码成可读的SQLmysqlbinlog --base64-outputdecode-rows -v /var/lib/mysql/mysql-bin.000001如果只想看某个时间段可以加--start-datetime和--stop-datetime参数mysqlbinlog --base64-outputdecode-rows -v --start-datetime2024-06-01 00:00:00 --stop-datetime2024-06-01 23:59:59 /var/lib/mysql/mysql-bin.000001这里补充一个很多人会问到的点binlog日志可以删除吗。答案是“可以但也分怎么删”。binlog文件不会自动无限增长它受expire_logs_daysMySQL 8.0已改用binlog_expire_logs_seconds和max_binlog_size的共同管理前者控制保留时间后者控制单个文件大小。手动清理也有几种方式最安全的是用SQL删除指定文件之前的binlogPURGE BINARY LOGS TO mysql-bin.000010;或者直接删除某个时间点之前的PURGE BINARY LOGS BEFORE NOW();如果因为特殊情况需要全部清掉先执行RESET MASTER然后重新从000001开始记录。但这种操作一定要非常小心没有做过全量备份且binlog还有恢复价值时千万不要执行。最简单的做法还是在配置文件里设置合理的保留时间让MySQL自动清理。4. Binlog的实战玩法数据恢复与Flink CDC采集上节提到了binlog这节我想展开讲讲它在实际项目中最常见的两种应用。一是用binlog做数据恢复二是作为数据同步链路比如Flink的数据源把MySQL实时同步到ClickHouse或者其他数据仓库。先说数据恢复的场景。假设你在凌晨3点执行了一个错误的DELETE操作把一张核心表中的几千条数据删了而且由于各种原因没有开启sql_safe_updates之类的保护数据直接从业务库中消失了。这时候如果你有完整的全量备份加上备份时间点之后的所有binlog恢复流程是这样的先恢复最近一次全量备份到临时实例或直接恢复。用mysqlbinlog解析从备份时间点到误操作时间点之间的所有binlog把日志中的SQL记录提取出来。将解析出的binlog重新执行到恢复实例中但在执行到误操作语句时跳过或者直接重放到误操作前一刻的时间点。实际操作中找到误操作语句的position很重要。一般先用mysqlbinlog输出到文本然后搜索误操作的SQL语句特征比如DELETE FROM orders WHERE order_id ...找到对应的# at 12345位置然后用--stop-position参数精确重放mysqlbinlog --start-datetime2024-06-01 00:00:00 --stop-position12345 /var/lib/mysql/mysql-bin.000001 | mysql -uroot -p这样就能把数据恢复到误操作前一秒。做这种操作前一定要先在测试环境完整演练一遍确认理解正确后再上生产否则操作本身又可能引入新问题。再说Flink CDC同步的场景。这几年实时数仓非常流行很多团队的做法是用Flink CDC监听MySQL binlog把数据实时同步到ClickHouse或者Kafka再加工到下游。这套链路的前提条件就是数据库得开启binlog而且binlog_format必须改成ROW。典型配置是[mysqld] server-id100 log_binmysql-bin binlog_formatROW为什么必须ROW因为Flink CDC需要拿到每一行的数据的before和after状态才能正确处理INSERT、UPDATE、DELETE事件。如果用STATEMENT格式CDC工具拿到的只是SQL语句无法可靠地反推出具体变更了哪些行。使用Flink CDC连接MySQL时Flink会要求你有权限读取binlog所以需要授权一个像flink这样的专用账号并赋予SELECT、RELOAD、SHOW DATABASES、REPLICATION SLAVE、REPLICATION CLIENT等权限。这个过程中很多人会踩到一个坑本机测试时Flink连不上MySQL的binlog报各种权限或者连接错误。排错时要先确认MySQL的bind-address配置如果是127.0.0.1那Flink从外部肯定连不上需要改成0.0.0.0或者指定内网IP。在同步链路的实际运维里binlog的保留时间要特别关注。Flink作业如果暂停较长时间重启后可能会发现要消费的binlog文件已经被自动清理了此时只能重新做全量同步。所以只要你的MySQL下游接了CDC任务binlog的保留时间就要结合下游消费延迟一起评估建议至少保留24小时以上防止下游短暂故障导致链路不可恢复。5. 日志的日常查看与排查技巧日志的价值不在于“存在”而在于“会看”。我见过不少同事连上服务器打开日志文件直接从头开始翻翻了几百行也没找到问题效率很低。这里我分享一套自己常用的日志排查思路和命令组合。第一步明确问题的发生时间段。大多数问题都有明确的触发时间比如“每天凌晨2点数据库CPU飙高”“每天早上9点应用报数据库连接超时”带着这个时间段去过滤日志效率会高很多。第二步用grep和tail组合定位。先看错误日志grep -i error /var/log/mysql/error.log | tail -n 50如果是慢查询日志grep -i Query_time /var/log/mysql/slow.log | sort -t: -k2 -rn | head -n 20这条命令把慢查询日志里的Query_time从大到小排序一眼就能看出当前最慢的SQL是哪些。第三步用awk做更复杂的统计。比如统计慢查询日志里出现次数最多的前10条SQL片段先把SQL语句规范化再聚合这里一般会用pt-query-digest这类工具来做比纯手工awk高效得多pt-query-digest /var/log/mysql/slow.log | head -n 100pt-query-digest会输出一个非常完整的分析报告包括Top N慢SQL、每个SQL的执行次数、平均耗时、扫描行数等。这个工具是Percona Toolkit套件的一部分需要单独安装。关于文件查看的几个基础命令我也顺手列一下方便新人复制tail -f /var/log/mysql/error.log实时跟踪日志输出。sed -n 100,200p /var/log/mysql/slow.log查看日志的第100行到200行。grep -n 关键字 /var/log/mysql/general.log查找带行号的关键字记录。另外还要提醒一下crontab的执行日志和MySQL日志不要混淆。如果定时任务里执行了MySQL相关的备份或脚本脚本报的错会写入crontab的日志如/var/log/cron或者脚本自己重定向的输出文件而不是MySQL的错误日志。查看crontab执行日志时可以先看系统cron日志grep your_script /var/log/cron再配合脚本里的输出重定向比如30 2 * * * /opt/backup.sh /var/log/mysql_backup.log 21这样脚本执行输出和报错都在/var/log/mysql_backup.log里不会和MySQL正式日志混在一起。说到日志采集现在稍微正规一点的环境都已经不用人工上服务器看日志了而是通过filebeat这种轻量级采集器把MySQL日志统一发送到Elasticsearch或者Kafka中再通过Kibana或者日志平台统一检索。我的建议是如果你维护的MySQL实例超过3个就值得尽早建设日志采集系统否则每天SSH登录不同机器去看日志效率会非常低。配置filebeat采集MySQL日志时要充分考虑日志轮转logrotate对采集位置的影响一般通过/var/log/mysql/*.log这样的通配路径去配置同时开启multiline模式把慢查询里的多行SQL合并成一条完整记录避免解析乱掉。6. 日志文件管理轮转、清理与常见坑日志文件如果不加管理最终也会变成一种故障源。磁盘满了、日志权限不对、清理机制配置错误我都遇到过。这里把日志管理的关键点和踩坑经验一起说清楚。首先是日志轮转。MySQL自带的错误日志和慢查询日志可以通过系统自带的logrotate来做轮转。以CentOS/RHEL系为例在/etc/logrotate.d/mysql下配置/var/log/mysql/*.log { daily rotate 14 compress delaycompress notifempty missingok create 0640 mysql mysql postrotate # 触发MySQL重新打开日志文件 if test -x /usr/bin/mysqladmin /usr/bin/mysqladmin ping -uroot -pXXX /dev/null 21; then /usr/bin/mysqladmin flush-logs -uroot -pXXX fi endscript }关键点在于postrotate里执行mysqladmin flush-logs对应SQL命令FLUSH LOGS这个动作会告诉MySQL重新打开一个新的日志文件否则日志轮转后MySQL还在往已经被移走的旧文件中写内容导致空间无法释放。其次是binlog的清理机制。MySQL 8.0里expire_logs_days已经被标记为废弃建议改用binlog_expire_logs_seconds[mysqld] binlog_expire_logs_seconds604800这条配置的含义是binlog保留7天超过7天的自动清理。不过在开启CDC同步或主从复制的环境里有一个特殊情况要注意如果某个从库或下游消费者长时间断开binlog即使超过保留时间也不会立即删除要等所有从库都消费完才会清掉。这本身是个保护机制但如果从库永久坏掉了binlog会一直堆积最后磁盘打满。这种情况下要主动处理比如把坏从库的复制拓扑改掉再去清理。第三是慢查询日志和通用查询日志的表文件清理。如果你用TABLE方式记录慢查询日志mysql.slow_log表会越来越大清理方式是先关闭日志再清表SET GLOBAL slow_query_log OFF; TRUNCATE TABLE mysql.slow_log; SET GLOBAL slow_query_log ON;同样适用于mysql.general_log。接下来是几个我在实操中经常见到的坑统一整理成一个速查表。常见现象可能原因处理方式慢查询日志没有生成参数没开启或目录权限不足检查slow_query_log和slow_query_log_file变量确认目录属主错误日志不记录任何内容log_error目录不可写查看目录权限用chown mysql:mysql修复日志时间比本地时间少8小时MySQL时区设置问题在[mysqld]下设置default-time-zone8:00或指定系统时区binlog文件删不掉从库或CDC下游还在消费旧binlog先确认复制拓扑处理掉无效从库后再purge日志目录磁盘使用率100%未配置轮转或binlog保留时间过长立即先PURGE BINARY LOGS腾空间再调整保留时间mysql.slow_log表占用空间大慢查询输出到TABLE且长期未清关闭慢日志开关后TRUNCATE TABLE mysql.slow_log再单独强调一个与日志关联的经典报错MySQL SSL连接错误。如果你在MySQL错误日志里看到SSL connection error: protocol version mismatch或者Cannot create accepted SSL connection说明客户端与服务端的TLS协议或证书配置不匹配。排查时先确认MySQL端的SSL配置SHOW VARIABLES LIKE %ssl%;如果还没到必须启用SSL加密连接的程度很多内部链路边可以暂时用skip_ssl处理掉外部干扰优先保障业务连接可用如果强制启用了SSL则要统一客户端的SSL模式和证书。还有Windows环境下的MySQL服务故障这里提一句很有必要Windows系统服务和MySQL的业务日志是两套体系遇到MySQL服务启动失败不要只盯着MySQL的.err文件还要去Windows事件查看器的“Windows日志-系统”里看服务控制管理器记录的报错和MySQL的错误日志交叉比对往往能更快定位。比如常见的e0434352这种错误码其实是.NET运行时异常代码跟MySQL本身没什么关系如果看到它要先检查是不是服务包装层比如用.NET写的管理工具出了问题。7. 关于日志排查顺序最后再啰嗦两句根据我自己这些年处理MySQL故障的体验日志排查的顺序可以简化成一句话先错误日志再慢查询日志最后才是通用查询日志和binlog。启动不了、连不上、异常重启看错误日志业务慢、CPU高、锁等待看慢查询日志需要审计、定位具体某条语句的执行历史才轮到开通用查询日志而binlog更多用于数据恢复、复制和同步场景。顺序一旦搞反排查效率会非常低。还有一个容易被忽视的点是日志的时区问题。MySQL 8.0之后日志里的时间戳默认使用系统时区但如果你用mysqlbinlog解析binlog它输出的是UTC时间还是本地时间取决于连接时的时区设置。在恢复数据或者核对时间点时不注意时区差8个小时的坑极容易定位错position。最后分享一个小技巧上线新功能或者做变更前先手动FLUSH LOGS切一个新日志文件并在日志文件名称上做好标记。这样变更期间产生的日志独立成一个文件排查问题范围会缩小很多不会在一整天的日志里大海捞针。等你踩过几次日志文件和故障时间点对不上的坑之后就会明白这个小动作有多值钱了。