MySQL的日志体系经常被忽视但几乎所有线上问题排查、数据恢复、性能优化都离不开它。这篇文章不按官方文档的顺序讲而是把我实际工作中用到的日志知识、踩过的坑、以及一些容易被忽略的细节整理出来从一个偏实战的角度把这些“杂知识”串成一条线。1. 日志家族全景搞清楚谁记录了啥很多刚接触MySQL的人一上来就搜“mysql 日志怎么开”、“mysql日志文件在哪”结果被一堆名词搞晕。其实MySQL日志体系分得非常清楚先花几分钟把每个日志的职责和关系理清后面排查问题才能不走弯路。1.1 六大日志类型速查表MySQL里常说的日志一共有六类我把它们的功能和典型应用场景先放在一张表里后面再逐个展开。你在公司里跟同事对线“日志太多”的时候或者自己排查慢查询的时候拿这张表对照一下基本就够用了。日志类型文件位置/命名示例默认状态核心作用典型应用场景错误日志hostname.err开启记录启动、关闭、运行异常看MySQL为什么起不来、为什么崩溃通用查询日志hostname.log关闭记录所有客户端连接和SQL审计、抓异常请求、调试慢查询日志hostname-slow.log关闭记录超过阈值的SQL定位性能瓶颈、优化SQL二进制日志binlog.000001通常开启记录所有变更操作主从复制、数据恢复、审计事务日志ib_logfile0开启记录InnoDB事务变更、崩溃恢复保证ACID、崩溃恢复中继日志relay-log.000001从库开启从库接收主库binlog的中转主从复制链路这里面最容易被忽略的中继日志其实很多人第一次搭建主从复制报错时问题都出在它身上这个后面实操部分我会细说。1.2 开日志前必问自己的三个问题日志不是开得越多越好。我见过一些新手把通用查询日志一开就是一天把磁盘直接撑爆数据库直接挂掉。开日志之前先问自己三个问题。第一个问题我要解决什么问题如果是SQL性能排查只要开慢查询日志就够了如果是审计需求才需要考虑通用查询日志。第二个问题磁盘空间够不够尤其是通用查询日志和binlog在高并发下增长速度快得超乎想象。第三个问题这个日志能关吗很多生产环境为了保证性能慢查询日志都是动态开关用完就关。记住一个原则通用查询日志在生产环境默认就是关闭的如果实在要开也要指定log_outputTABLE避免直接在文件系统上疯狂写IO。2. 错误日志与启动故障MySQL“起不来”的第一现场错误日志配合初始化参数、配置文件是MySQL起不来、连接不上时的第一排查入口。我见过太多人在排查“mysql安装失败”、“mysql服务无法启动”时纯粹靠百度粘贴命令却不知道正确答案就在错误日志里。2.1 错误日志里到底写了什么错误日志通常位于数据目录下默认文件名是主机名.err。它的内容分成几类启动参数信息包括参数是否生效、警告信息、InnoDB的初始化过程、连接异常比如Access denied大面积出现往往是密码爆破或密码配置错了、主从复制的中断错误比如UUID重复、server-id冲突。我在排障时最常用的查看命令强烈建议直接抄走# 实时跟踪错误日志 tail -f /var/log/mysql/error.log # 或者动态查看MySQL当前错误日志路径 mysql SHOW VARIABLES LIKE log_error; # 查看最近几条错误 tail -n 50 /var/log/mysql/error.log这里有个很多人绕弯子的点在二进制包或源码安装时错误日志目录可能默认指向datadir如果你把datadir改了别忘了改log_error的值。2.2 两个经典故障服务起不来和权限报错先说Windows下服务启动失败。典型报错是“net start mysql 服务无法启动”。很多人一看到这个就重装其实你只要打开错误日志大概率会看到有[ERROR] [MY-010584] InnoDB: Unable to lock ./ibdata1, error: 11之类的信息这就说明之前的进程没有完全退出端口或者文件锁被占用了。赶紧tasklist | findstr mysqld把残留的进程干掉再启动。再一个是权限问题尤其是用rpm或tar包安装后用root用户跑mysqld日志里会出现[ERROR] [MY-010338] Cannot change permissions of file这类提示。原因是MySQL不允许以root身份运行这时候要么用usermysql参数指定专用用户要么把数据目录所属用户改过来。还有一个非常隐蔽的启动问题Failed to open log file ... No such file or directory。这通常是你把log-error指向了一个不存在的路径比如/var/log/mysql/目录还没创建。MySQL不会自动帮你创建目录你必须手动mkdir并保证目录的属主是mysql用户。2.3 错误日志的轮替与清理错误日志不会自己清理时间久了会变得很大。最优雅的做法是利用系统自带的logrotate来按天或者按大小切割。配置模板大致是这个样子我贴出来供直接参考# /etc/logrotate.d/mysql /var/log/mysql/error.log { daily rotate 7 compress missingok notifempty copytruncate }其中重点是这个copytruncate它先把日志文件复制一份再直接把原文件截断这样MySQL不需要重启就能继续写日志。如果不加这个参数logrotate会通过rename的方式切割MySQL的日志句柄还指向旧文件你得重启MySQL才能生效。3. 慢查询日志与性能排查三分钟定位拖垮数据库的SQL慢查询日志可以说是DBA用来保命的工具。MySQL的慢查询日志记录了执行时间超过long_query_time阈值的SQL语句以及一些没有走索引的查询。我在负责复杂业务系统时每次应用反馈“页面卡了”第一件事就是打开慢查询日志这一招十次里有八次能直接定位到根源。3.1 动态开关慢查询不需要重启MySQL下面三条SQL可以直接在会话或全局层面设置慢查询参数这在生产环境非常实用避免为了开日志重启数据库的尴尬。-- 设置超过1秒的SQL记录 SET GLOBAL long_query_time 1; -- 开启慢查询日志 SET GLOBAL slow_query_log ON; -- 设置日志路径 SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;这里的坑在于long_query_time的单位是秒而且支持小数。业界习惯设置成0.5或者1别傻乎乎地设成10秒。等你发现的时候慢SQL早把应用拖崩了。另外要注意long_query_time这个参数设置为全局后新建立的连接才生效当前session需要重新连接才会读取新值。慢查询日志也会记录“没有使用索引的查询”前提是你打开了log_queries_not_using_indexes。这个开关在开发环境一定要打开能帮你抓出一堆没有索引的SELECT *在生产环境谨慎使用因为这种SQL往往很多容易把日志撑爆。3.2 慢查询日志里的关键信息解读打开一条慢SQL它的记录格式大概是这样# Time: 2024-06-20T10:15:30.123456Z # UserHost: root[root] localhost [] Id: 42 # Query_time: 2.876543 Lock_time: 0.012345 Rows_sent: 100 Rows_examined: 50000 SET timestamp1718878530; SELECT * FROM orders WHERE customer_id 9527 ORDER BY create_time DESC;这里有几个字段非常关键Query_time是SQL执行总耗时Lock_time是锁等待时间Rows_sent是返回行数Rows_examined是扫描行数。如果Rows_examined远大于Rows_sent说明这SQL扫描了大量数据但只返回少量行大概率是索引不够好。SET timestamp那个值是这条SQL实际执行的时间点用来判断慢SQL的高发时段非常有用。真正的高手还会在慢日志开启时配合performance_schema一起看但这里先不展开掌握上面的字段足够应付大多数情况。3.3 从慢日志到SQL优化的一条最短路找到慢SQL之后不要直接改代码。先用EXPLAIN看执行计划注意看type列、key列、rows列。如果type是ALL说明是全表扫描如果key是NULL说明没走索引。最常见的优化手段是创建一个复合索引把WHERE条件里的等值列放在索引前面把ORDER BY的列放在后面。我用一个贴近实际业务的小例子演示一下订单表orders上有customer_id和create_time两个字段慢日志里高频出现上面那条SQL执行计划显示typeALL、rows50000。这时候创建一个(customer_id, create_time)的复合索引再去查rows下降到几十行查询时间直接从2.8秒降到0.01秒。这就是慢查询日志带来收益的最直观体现。4. 二进制日志与数据恢复binlog的正确打开方式binlog是MySQL里最重要的一类日志它记录的是对数据有变更操作的逻辑日志比如INSERT、UPDATE、DELETE以及CREATE TABLE等DDL语句。主从复制、误删数据恢复、以及很多数据审计全部都要靠它。但很多人对它熟悉又陌生知道有这个东西一旦真需要用它恢复数据就会卡在怎么解析、怎么定位位点这些问题上。4.1 如何查看正写着的binlogbinlog是一组编号递增的文件比如binlog.000001、binlog.000002还有一个索引文件binlog.index记录文件清单。查看正在写入的binlog文件常用两条语句SHOW MASTER STATUS; SHOW BINARY LOGS;SHOW MASTER STATUS会告诉你当前正在写哪个文件以及写入到哪个位置Position这个Position在做恢复和搭建主从时极其重要。SHOW BINARY LOGS则列出所有binlog文件方便你确认日志归档的序号。另外很多人好奇binlog内容怎么看。binlog是二进制文件直接用cat是乱码。必须用自带的mysqlbinlog工具解析# 查看binlog内容 mysqlbinlog --base64-outputDECODE-ROWS -v /var/lib/mysql/binlog.000003--base64-outputDECODE-ROWS的作用是把基于行的日志解码成可读的SQL风格-v是输出行信息的注释。这个命令在排查误操作的时候基本是标配。4.2 binlog_format行级还是语句级binlog有三种格式分别对应三个值STATEMENT语句级、ROW行级、MIXED混合。这个选择直接决定日志的可读性和恢复精度。STATEMENT记录SQL原文日志量小但有些SQL在主从库上执行结果可能不一致比如用了NOW()、UUID()等函数。ROW记录每一行数据的变更前后镜像可以精确恢复但日志量大而且用mysqlbinlog看的时候默认看不懂要加解码选项。MIXED是两者的结合MySQL会自动判断是否用行级。我在生产环境里的建议是直接设为ROW。尤其是涉及金额、状态类的核心数据表行级记录能让你在误删后精确恢复那一行。有人担心ROW格式日志量膨胀其实可以通过定期归档binlog来控制这个问题后面章节会讲。查看和设置格式的语句SHOW VARIABLES LIKE binlog_format; SET GLOBAL binlog_format ROW;注意binlog_format是个全局参数修改后需要重新连接会话才生效而且最好在修改前评估一下日志增长速度。4.3 误删数据恢复实操拆解这里分享一个我实际演练过的场景某天业务人员执行了一条DELETE FROM user WHERE status0目的是清理无效用户结果条件写错了把正常用户也删了。这时候binlog是救命稻草。恢复的基本思路是从binlog中找出这条DELETE语句执行前的位置点和执行后的位置点然后用mysqlbinlog截取这段日志把其中的SQL反向恢复也就是把它变成INSERT重新执行或者直接利用时间点到备份的基础上重放binlog。第一步定位误操作的大致时间用时间范围过滤binlogmysqlbinlog --start-datetime2024-06-20 10:00:00 --stop-datetime2024-06-20 10:30:00 /var/lib/mysql/binlog.000003 restore.sql第二步在这个restore.sql里搜索DELETE FROM user找到对应的日志位置通常记录格式是# at 123456这样的注释。再往前找一下上一个# at 123000那个位置就是误操作前的位置。第三步重新截取误操作前的日志到这个位置的日志然后通过mysqlbinlog的--stop-position参数把截止位置精确到误操作前一个事件再把它重放到临时库或者直接恢恢复到备份库。最后的恢复语句大致是mysqlbinlog --stop-position123000 /var/lib/mysql/binlog.000003 | mysql -u root -p这里要特别强调的是恢复操作前一定要先备份当前的binlog和数据库恢复时最好在独立的实例或临时库里执行确认数据无误后再导回生产。千万别直接在生产上操作恢复SQL否则二次事故没人帮你背锅。4.4 常见binlog工具跨界玩法binlog不只是用来恢复数据。现在很多异构数据同步场景比如“使用flink实现mysql同步到clickhouse”、“mysql表结构自动转tdengine超级表子表”核心原理都是先解析binlog再把变更事件发送到目标库。这类大数据的玩法本质上就是在消费binlog。比如Flink CDC就是通过伪装成MySQL的从库来拉取binlog再翻译成变更流。所以理解binlog的格式和位点概念不仅对DBA有用对数据工程师同样重要。你在做实时数仓同步时遇到“数据对不上”第一步去源头看binlog有没有解析到这一步就能筛掉一半问题。5. 事务日志与崩溃恢复InnoDB的redolog和undolog关于事务日志redo log和undo log是最容易搞混的一组概念。尤其是面试的时候很多人能说“redolog是崩溃恢复用的undolog是回滚用的”但一旦追问“为什么有了binlog还要redo log”就开始语无伦次。这里把这块知识用流程的方式讲清楚。5.1 redo log的两阶段写InnoDB在事务提交时其实做了不少手脚。当你执行一条UPDATE数据并不会立刻刷到磁盘的数据文件里而是先写进内存中的缓冲池Buffer Pool并且把这次变更记录写入redo log buffer。等到事务提交时再把redo log buffer里的内容刷入磁盘上的redo log文件。这里关键的点是MySQL为了配合binlog引入了“两阶段提交”先写redo log状态为prepare再写binlog最后把redo log标记为commit。如果在写完binlog前数据库崩溃了恢复时会发现redo log有prepare状态的记录但binlog里没有对应记录那就判定这个事务未提交回滚掉。如果binlog写完了但还没标记commit恢复时会用binlog里的记录来补齐redo log保证主从数据一致。这个机制很多人不知道但它解释了非常多主从数据不一致和恢复异常的奇怪现象。我贴个简单流程让你直观理解事务开始修改Buffer Pool中的数据页生成redo log。事务提交前把redo log标记为prepare刷入磁盘。写入binlog刷入磁盘。把redo log标记为commit事务完成。第2、3步之间就是崩溃恢复的重点判断区间。5.2 undo log与MVCCundo log不是用来崩溃恢复的它有两个主要作用一是事务回滚时把数据恢复成旧值二是实现MVCC多版本并发控制时供其他事务读取旧版本数据。举个例子事务A更新了一行数据事务B在这时执行SELECT它看不到事务A未提交的修改必须读取该行修改前的版本这个旧版本就是通过undo log构建的。所以undo log其实是一个版本链记录着每次修改前的镜像。在生产环境中如果并发特别高并且长时间不提交事务undo log会不断累积表现为undo表空间膨胀。这个在MySQL 8.0里可以通过innodb_undo_log_truncateON来自动截断但前提是不能再有长事务否则截断会卡住。5.3 redo log相关参数与常见误区redo log在InnoDB里默认是ib_logfile0、ib_logfile1配置参数是innodb_log_file_size和innodb_log_files_in_group。在MySQL 8.0.30里这两个参数已经被innodb_redo_log_capacity整合替代但旧的配置文件里仍然可以设置。说到误区最常见的是有人以为“把redo log设置得越大越好”。如果redo log太大崩溃恢复时间会变长而且磁盘占用高如果太小在高并发写入时会导致频繁的checkpoint刷盘性能断崖式下跌。我的建议是对普通业务系统redo log总容量设置成1GB到4GB之间足够写入频繁的核心交易系统可以适当加大。如何判断redo log是否太小观察SHOW ENGINE INNODB STATUS的输出查找Log sequence number和Log flushed up to之间的间隔如果两者差值经常接近redo log总容量就说明日志太小需要调大。6. 通用查询日志与数据库连接池一个容易被忽视的调试角色通用查询日志这个角色有点尴尬它记录的不是慢SQL而是所有到达MySQL服务器的SQL语句和连接建立信息。因为记录全量SQL它的IO开销和磁盘消耗都很高生产环境默认关闭。但有些场景离开它根本没法排查。6.1 通用查询日志能查到什么比如某个连接池对接出了问题应用连接数暴增你想看看到底是哪个客户端建立了连接、有没有发SQL。又比如某段时间MySQL负载很高但慢查询日志里又没抓到什么这时候可以短暂开启通用查询日志抓到所有线程的执行SQL分析出负载到底是谁贡献的。通用查询日志的开关和路径如下SET GLOBAL general_log ON; SET GLOBAL general_log_file /var/log/mysql/general.log;我见过一次具体案例一个JavaWeb项目用连接池连接MySQL一段时间后连接耗尽报Too many connections。因为应用连接池的maxActive设置得过高而MySQL的max_connections相对较小。当时不开通用查询日志根本不知道哪些连接一直占着不释放开了之后发现大量连接处在Sleep状态说明连接池里借出去的连接没有及时归还。这个场景下通用查询日志中记录的连接建立记录能帮你迅速定位是哪台应用服务器、哪个用户占据大量Sleep线程。6.2 慎开、快关的使用原则通用查询日志在低峰期开个5-10分钟就够抓到需要的样本后马上关闭。生产环境从打开到关闭这段时间磁盘IO可能会成为瓶颈所以更推荐用log_outputTABLE写进表里抓完之后用SQL查询分析。但需要注意写到mysql.general_log表里同样会产生大量写入操作本质没有变。我在实际排查时如果不是必须更多会借助performance_schema的事件表来替代通用查询日志它能按线程、按用户名、按耗时聚合SQL效率比翻日志高一个量级。但通用查询日志的“全局无差别记录”特性在某些特定时刻仍然是不可替代的。7. 日志清理与磁盘空间别让日志成为事故源头日志把磁盘撑爆导致数据库宕机这个故障类型在运维圈里非常常见。你以为是有黑客攻击结果一看磁盘100%元凶是binlog积压、慢日志积压、错误日志积压。处理日志空间问题必须有预案。7.1 binlog的自动清理策略binlog的过期清理主要由expire_logs_days或MySQL 8.0的binlog_expire_logs_seconds控制。旧参数的单位是天新参数精确到秒配置生效后会按时间自动删除旧的binlog文件。-- 查看当前binlog过期策略 SHOW VARIABLES LIKE expire_logs_days; SHOW VARIABLES LIKE binlog_expire_logs_seconds; -- 设置binlog保留3天MySQL 8.0 SET GLOBAL binlog_expire_logs_seconds 259200;如果你的主从复制延迟严重binlog清理策略设置不当会引发从库断链。因为从库还在追读一个主库已经删掉的binlog主从复制就会报Got fatal error 1236。所以清理策略不能只看磁盘还得看从库的延迟和读取进度。手动清理binlog也有专门的语法-- 删除到指定日志之前的binlog不会删除正在写的那个 PURGE BINARY LOGS TO binlog.000010;采用RESET MASTER清空所有binlog这种操作在从库环境里等同于自杀一旦执行所有从库都会因为找不到binlog而断开务必谨慎。7.2 系统日志的清理不只是MySQL的事在Linux环境里系统本身的日志也会大量消耗磁盘比如/var/log/messages、/var/log/syslog。很多人搜“linux清空日志log命令”其实最安全的是用truncate而不是直接rm后重建。# 清空日志文件但保留文件句柄 truncate -s 0 /var/log/mysql/error.log truncate -s 0 /var/log/mysql/slow.log注意不要直接echo /var/log/mysql/error.log因为这样会改变文件的inodeMySQL可能不会继续向这个文件写入需要重启才生效这又是一个隐性的坑。7.3 磁盘空间压测排查的一条命令序列如果MySQL的磁盘快满了我的排查顺序是先看谁占用了空间再决定怎么处理。# 查看各目录占用 du -sh /var/lib/mysql/* | sort -rh | head -20 # 查看日志文件大小 ls -lh /var/lib/mysql/*.log /var/log/mysql/*.log 2/dev/null # 查看binlog列表及大小 SHOW BINARY LOGS;如果发现是binlog积压先查从库状态如果Seconds_Behind_Master很大优先解决从库延迟再考虑清理binlog。如果只是归档没用可以直接PURGE BINARY LOGS。如果错误日志巨大先看一眼里面是不是在刷某种报错比如多次启动失败、反复连接拒绝这类情况解决根本问题之前清空日志只是自欺欺人。8. 从日志到MySQL常见报错几个容易被搜疯的疑难杂症这里结合一些非常高频的搜索词把日志系统和常见报错的关联做一个统一梳理帮你省掉到处搜资料的功夫。这些报错常年出现在“mysql安装教程”、“docker安装mysql失败”、“mysql ssl连接错误”等热搜词背后基本是入门者和运维人员最容易卡壳的地方。8.1 通用查询日志与“Too many connections”应用一直报Too many connections时慢日志和错误日志不一定有有效输出因为连接拒绝发生在SQL执行之前。此时打开通用查询日志你会看到大量的连接建立事件但真正执行SQL的线程不多。结合SHOW PROCESSLIST观察线程状态能确定泄漏点。我见过一个比较经典的案例连接池的testOnBorrow配置不当导致每个连接在借出前都执行一次探测SQL高并发下这个探测SQL拖慢了整个连接池连接积压越来越多最终触发Too many connections。日志里表现就是大量SELECT 1的查询。这种坑不看通用查询日志或performance_schema完全发现不了。8.2 中继日志与主从复制中断主从复制报错Cannot replicate because the master purged required binary logs或者relay log read failure时排查重点就是中继日志。中继日志是SQL线程读取从库本地relay log执行变更时产生的一旦中继日志损坏或空间不足从库也会崩溃。处理这类问题常规操作是STOP SLAVE; RESET SLAVE; CHANGE MASTER TO MASTER_LOG_FILEbinlog.000010, MASTER_LOG_POS120; START SLAVE;但RESET SLAVE会清空中继日志如果你的从库已经执行了一半事务直接reset会导致数据不一致所以更安全的做法是START SLAVE UNTIL逐步追平。8.3 mysql ssl连接错误与日志记录SSL连接报错也是一个很诡异的现象。客户端连接时报SSL connection error服务端错误日志往往不会直接说“SSL证书过期”而是显示[ERROR] [MY-013602] SSL error: ...或[Warning] SSL connection error。此时优先检查MySQL配置文件里的ssl_ca、ssl_cert、ssl_key路径是否正确、证书是否过期。还可以通过一条SQL验证SSL是否真的开启SHOW VARIABLES LIKE %ssl%;别被各种教程带着去改skip_ssl这虽然能绕开报错但会让整个连接变成明文传输不是万不得已不要这么干。8.4 Docker运行MySQL的日志排查很多人搜“docker安装mysql失败”本质都是容器启动后立即退出。这种问题最简单粗暴的排查方式是直接看容器日志docker logs mysql-container比如常见报错是[ERROR] [MY-010118] Cant start server: Bind on unix socket: Permission denied说明权限没给对。如果日志里出现[ERROR] [MY-010457] Table mysql.user doesnt exist说明数据目录是空的或者初始化没完成再配合--initialize-insecure初始化一次数据目录基本就能解决。Docker里挂载数据卷时还有个大坑宿主机目录属主跟容器内mysql用户的UID不一致导致容器启动时无法写入数据。日志里会出现[ERROR] [MY-011972] InnoDB: Operating system error number 13 in a file operation这类报错通过chown -R 999:999 /data/mysql解决甚至有些场景用docker compose里的user字段做映射。9. 日志分析的工具与日常巡检脚本整理最后分享一套我自己在实际环境中用的日志巡检思路不依赖复杂平台用Linux自带命令和MySQL的SQL命令就能覆盖日常需求。9.1 半小时级巡检脚本思路一个成熟的巡检脚本应该包含以下内容我建议把脚本写到cron里每半小时自动跑一次把输出结果追加到统一的日志文件里这样回溯问题时能有一个连续的时间线。#!/bin/bash # MySQL日志巡检脚本 MYSQL_USERroot MYSQL_PASSyourpassword LOG_DIR/var/log/mysql_check DATE$(date %Y%m%d%H%M) # 检查错误日志新增内容 tail -n 100 /var/log/mysql/error.log $LOG_DIR/error_$DATE.log # 检查慢查询最近新增 tail -n 100 /var/log/mysql/slow.log $LOG_DIR/slow_$DATE.log # 检查当前binlog大小 mysql -u$MYSQL_USER -p$MYSQL_PASS -e SHOW BINARY LOGS; $LOG_DIR/binlog_$DATE.log # 检查连接数 mysql -u$MYSQL_USER -p$MYSQL_PASS -e SHOW STATUS LIKE Threads_connected; $LOG_DIR/status_$DATE.log不过更推荐的做法是直接用pt-query-digest来汇总慢日志它在分析慢日志时能输出SQL指纹、执行频率、平均耗时等统计数据比裸翻慢日志效率高太多。9.2 日志与日志可视化方案当服务器数量多了以后单机翻日志会累死人。此时可以引入ELK方案filebeat采集MySQL日志、logstash加工、elasticsearch存储、kibana展示。现在比较轻量的是用Loki Promtail Grafana体系定位日志关键字和做时间线分析都很方便。但我的经验是先别急着堆工具。如果你只有一两个MySQL实例老老实实用命令查日志是最快的。工具链引入的维护成本有时候比直接查日志的成本还高。等到日志量和实例数确实上来了再逐步把日志接入统一采集平台。9.3 日志文件名“万能”查看法不管是哪种日志你都可能忘记具体路径。这里有个一招鲜直接通过MySQL变量查。以最常用的几个为例SHOW VARIABLES LIKE log_error; SHOW VARIABLES LIKE slow_query_log_file; SHOW VARIABLES LIKE general_log_file; SHOW VARIABLES LIKE datadir;记住以MySQL变量的输出为准。网上教程里写的路径只能参考不一定适应你的环境。我曾经碰到过一个案例配置文件里写了log-error/var/log/mysql/error.log但实际环境里MySQL用的是基于datadir的默认路径导致怎么查都查不到报错内容最后就是用上面这个变量才确认了真实路径。回到开头说的MySQL日志的知识点多且杂但它是数据库运维和数据恢复的基本功也是排查问题时的第一手材料。我在实际运维里踩过不少坑比如日志文件权限配置疏忽导致重启失败比如把通用查询日志当慢日志开着导致磁盘被写爆又比如在没确认从库进度时PURGE binlog把复制链路弄断。这些经历让我形成了一套自己的习惯日志能开就开但必须明确目标、控制体积、设置清理策略排障第一眼看error日志性能问题第二眼看slow日志数据恢复问题马上想到binlog主从问题紧盯relay log。日志本身不产生价值产生价值的是你通过日志看到了系统的真实状态并做出正确决策。在MySQL的日常维护中少走弯路的最好方式就是养成遇到问题先翻日志的习惯这比记再多命令都管用。
MySQL日志体系实战：从故障排查到数据恢复的全景指南
MySQL的日志体系经常被忽视但几乎所有线上问题排查、数据恢复、性能优化都离不开它。这篇文章不按官方文档的顺序讲而是把我实际工作中用到的日志知识、踩过的坑、以及一些容易被忽略的细节整理出来从一个偏实战的角度把这些“杂知识”串成一条线。1. 日志家族全景搞清楚谁记录了啥很多刚接触MySQL的人一上来就搜“mysql 日志怎么开”、“mysql日志文件在哪”结果被一堆名词搞晕。其实MySQL日志体系分得非常清楚先花几分钟把每个日志的职责和关系理清后面排查问题才能不走弯路。1.1 六大日志类型速查表MySQL里常说的日志一共有六类我把它们的功能和典型应用场景先放在一张表里后面再逐个展开。你在公司里跟同事对线“日志太多”的时候或者自己排查慢查询的时候拿这张表对照一下基本就够用了。日志类型文件位置/命名示例默认状态核心作用典型应用场景错误日志hostname.err开启记录启动、关闭、运行异常看MySQL为什么起不来、为什么崩溃通用查询日志hostname.log关闭记录所有客户端连接和SQL审计、抓异常请求、调试慢查询日志hostname-slow.log关闭记录超过阈值的SQL定位性能瓶颈、优化SQL二进制日志binlog.000001通常开启记录所有变更操作主从复制、数据恢复、审计事务日志ib_logfile0开启记录InnoDB事务变更、崩溃恢复保证ACID、崩溃恢复中继日志relay-log.000001从库开启从库接收主库binlog的中转主从复制链路这里面最容易被忽略的中继日志其实很多人第一次搭建主从复制报错时问题都出在它身上这个后面实操部分我会细说。1.2 开日志前必问自己的三个问题日志不是开得越多越好。我见过一些新手把通用查询日志一开就是一天把磁盘直接撑爆数据库直接挂掉。开日志之前先问自己三个问题。第一个问题我要解决什么问题如果是SQL性能排查只要开慢查询日志就够了如果是审计需求才需要考虑通用查询日志。第二个问题磁盘空间够不够尤其是通用查询日志和binlog在高并发下增长速度快得超乎想象。第三个问题这个日志能关吗很多生产环境为了保证性能慢查询日志都是动态开关用完就关。记住一个原则通用查询日志在生产环境默认就是关闭的如果实在要开也要指定log_outputTABLE避免直接在文件系统上疯狂写IO。2. 错误日志与启动故障MySQL“起不来”的第一现场错误日志配合初始化参数、配置文件是MySQL起不来、连接不上时的第一排查入口。我见过太多人在排查“mysql安装失败”、“mysql服务无法启动”时纯粹靠百度粘贴命令却不知道正确答案就在错误日志里。2.1 错误日志里到底写了什么错误日志通常位于数据目录下默认文件名是主机名.err。它的内容分成几类启动参数信息包括参数是否生效、警告信息、InnoDB的初始化过程、连接异常比如Access denied大面积出现往往是密码爆破或密码配置错了、主从复制的中断错误比如UUID重复、server-id冲突。我在排障时最常用的查看命令强烈建议直接抄走# 实时跟踪错误日志 tail -f /var/log/mysql/error.log # 或者动态查看MySQL当前错误日志路径 mysql SHOW VARIABLES LIKE log_error; # 查看最近几条错误 tail -n 50 /var/log/mysql/error.log这里有个很多人绕弯子的点在二进制包或源码安装时错误日志目录可能默认指向datadir如果你把datadir改了别忘了改log_error的值。2.2 两个经典故障服务起不来和权限报错先说Windows下服务启动失败。典型报错是“net start mysql 服务无法启动”。很多人一看到这个就重装其实你只要打开错误日志大概率会看到有[ERROR] [MY-010584] InnoDB: Unable to lock ./ibdata1, error: 11之类的信息这就说明之前的进程没有完全退出端口或者文件锁被占用了。赶紧tasklist | findstr mysqld把残留的进程干掉再启动。再一个是权限问题尤其是用rpm或tar包安装后用root用户跑mysqld日志里会出现[ERROR] [MY-010338] Cannot change permissions of file这类提示。原因是MySQL不允许以root身份运行这时候要么用usermysql参数指定专用用户要么把数据目录所属用户改过来。还有一个非常隐蔽的启动问题Failed to open log file ... No such file or directory。这通常是你把log-error指向了一个不存在的路径比如/var/log/mysql/目录还没创建。MySQL不会自动帮你创建目录你必须手动mkdir并保证目录的属主是mysql用户。2.3 错误日志的轮替与清理错误日志不会自己清理时间久了会变得很大。最优雅的做法是利用系统自带的logrotate来按天或者按大小切割。配置模板大致是这个样子我贴出来供直接参考# /etc/logrotate.d/mysql /var/log/mysql/error.log { daily rotate 7 compress missingok notifempty copytruncate }其中重点是这个copytruncate它先把日志文件复制一份再直接把原文件截断这样MySQL不需要重启就能继续写日志。如果不加这个参数logrotate会通过rename的方式切割MySQL的日志句柄还指向旧文件你得重启MySQL才能生效。3. 慢查询日志与性能排查三分钟定位拖垮数据库的SQL慢查询日志可以说是DBA用来保命的工具。MySQL的慢查询日志记录了执行时间超过long_query_time阈值的SQL语句以及一些没有走索引的查询。我在负责复杂业务系统时每次应用反馈“页面卡了”第一件事就是打开慢查询日志这一招十次里有八次能直接定位到根源。3.1 动态开关慢查询不需要重启MySQL下面三条SQL可以直接在会话或全局层面设置慢查询参数这在生产环境非常实用避免为了开日志重启数据库的尴尬。-- 设置超过1秒的SQL记录 SET GLOBAL long_query_time 1; -- 开启慢查询日志 SET GLOBAL slow_query_log ON; -- 设置日志路径 SET GLOBAL slow_query_log_file /var/log/mysql/slow.log;这里的坑在于long_query_time的单位是秒而且支持小数。业界习惯设置成0.5或者1别傻乎乎地设成10秒。等你发现的时候慢SQL早把应用拖崩了。另外要注意long_query_time这个参数设置为全局后新建立的连接才生效当前session需要重新连接才会读取新值。慢查询日志也会记录“没有使用索引的查询”前提是你打开了log_queries_not_using_indexes。这个开关在开发环境一定要打开能帮你抓出一堆没有索引的SELECT *在生产环境谨慎使用因为这种SQL往往很多容易把日志撑爆。3.2 慢查询日志里的关键信息解读打开一条慢SQL它的记录格式大概是这样# Time: 2024-06-20T10:15:30.123456Z # UserHost: root[root] localhost [] Id: 42 # Query_time: 2.876543 Lock_time: 0.012345 Rows_sent: 100 Rows_examined: 50000 SET timestamp1718878530; SELECT * FROM orders WHERE customer_id 9527 ORDER BY create_time DESC;这里有几个字段非常关键Query_time是SQL执行总耗时Lock_time是锁等待时间Rows_sent是返回行数Rows_examined是扫描行数。如果Rows_examined远大于Rows_sent说明这SQL扫描了大量数据但只返回少量行大概率是索引不够好。SET timestamp那个值是这条SQL实际执行的时间点用来判断慢SQL的高发时段非常有用。真正的高手还会在慢日志开启时配合performance_schema一起看但这里先不展开掌握上面的字段足够应付大多数情况。3.3 从慢日志到SQL优化的一条最短路找到慢SQL之后不要直接改代码。先用EXPLAIN看执行计划注意看type列、key列、rows列。如果type是ALL说明是全表扫描如果key是NULL说明没走索引。最常见的优化手段是创建一个复合索引把WHERE条件里的等值列放在索引前面把ORDER BY的列放在后面。我用一个贴近实际业务的小例子演示一下订单表orders上有customer_id和create_time两个字段慢日志里高频出现上面那条SQL执行计划显示typeALL、rows50000。这时候创建一个(customer_id, create_time)的复合索引再去查rows下降到几十行查询时间直接从2.8秒降到0.01秒。这就是慢查询日志带来收益的最直观体现。4. 二进制日志与数据恢复binlog的正确打开方式binlog是MySQL里最重要的一类日志它记录的是对数据有变更操作的逻辑日志比如INSERT、UPDATE、DELETE以及CREATE TABLE等DDL语句。主从复制、误删数据恢复、以及很多数据审计全部都要靠它。但很多人对它熟悉又陌生知道有这个东西一旦真需要用它恢复数据就会卡在怎么解析、怎么定位位点这些问题上。4.1 如何查看正写着的binlogbinlog是一组编号递增的文件比如binlog.000001、binlog.000002还有一个索引文件binlog.index记录文件清单。查看正在写入的binlog文件常用两条语句SHOW MASTER STATUS; SHOW BINARY LOGS;SHOW MASTER STATUS会告诉你当前正在写哪个文件以及写入到哪个位置Position这个Position在做恢复和搭建主从时极其重要。SHOW BINARY LOGS则列出所有binlog文件方便你确认日志归档的序号。另外很多人好奇binlog内容怎么看。binlog是二进制文件直接用cat是乱码。必须用自带的mysqlbinlog工具解析# 查看binlog内容 mysqlbinlog --base64-outputDECODE-ROWS -v /var/lib/mysql/binlog.000003--base64-outputDECODE-ROWS的作用是把基于行的日志解码成可读的SQL风格-v是输出行信息的注释。这个命令在排查误操作的时候基本是标配。4.2 binlog_format行级还是语句级binlog有三种格式分别对应三个值STATEMENT语句级、ROW行级、MIXED混合。这个选择直接决定日志的可读性和恢复精度。STATEMENT记录SQL原文日志量小但有些SQL在主从库上执行结果可能不一致比如用了NOW()、UUID()等函数。ROW记录每一行数据的变更前后镜像可以精确恢复但日志量大而且用mysqlbinlog看的时候默认看不懂要加解码选项。MIXED是两者的结合MySQL会自动判断是否用行级。我在生产环境里的建议是直接设为ROW。尤其是涉及金额、状态类的核心数据表行级记录能让你在误删后精确恢复那一行。有人担心ROW格式日志量膨胀其实可以通过定期归档binlog来控制这个问题后面章节会讲。查看和设置格式的语句SHOW VARIABLES LIKE binlog_format; SET GLOBAL binlog_format ROW;注意binlog_format是个全局参数修改后需要重新连接会话才生效而且最好在修改前评估一下日志增长速度。4.3 误删数据恢复实操拆解这里分享一个我实际演练过的场景某天业务人员执行了一条DELETE FROM user WHERE status0目的是清理无效用户结果条件写错了把正常用户也删了。这时候binlog是救命稻草。恢复的基本思路是从binlog中找出这条DELETE语句执行前的位置点和执行后的位置点然后用mysqlbinlog截取这段日志把其中的SQL反向恢复也就是把它变成INSERT重新执行或者直接利用时间点到备份的基础上重放binlog。第一步定位误操作的大致时间用时间范围过滤binlogmysqlbinlog --start-datetime2024-06-20 10:00:00 --stop-datetime2024-06-20 10:30:00 /var/lib/mysql/binlog.000003 restore.sql第二步在这个restore.sql里搜索DELETE FROM user找到对应的日志位置通常记录格式是# at 123456这样的注释。再往前找一下上一个# at 123000那个位置就是误操作前的位置。第三步重新截取误操作前的日志到这个位置的日志然后通过mysqlbinlog的--stop-position参数把截止位置精确到误操作前一个事件再把它重放到临时库或者直接恢恢复到备份库。最后的恢复语句大致是mysqlbinlog --stop-position123000 /var/lib/mysql/binlog.000003 | mysql -u root -p这里要特别强调的是恢复操作前一定要先备份当前的binlog和数据库恢复时最好在独立的实例或临时库里执行确认数据无误后再导回生产。千万别直接在生产上操作恢复SQL否则二次事故没人帮你背锅。4.4 常见binlog工具跨界玩法binlog不只是用来恢复数据。现在很多异构数据同步场景比如“使用flink实现mysql同步到clickhouse”、“mysql表结构自动转tdengine超级表子表”核心原理都是先解析binlog再把变更事件发送到目标库。这类大数据的玩法本质上就是在消费binlog。比如Flink CDC就是通过伪装成MySQL的从库来拉取binlog再翻译成变更流。所以理解binlog的格式和位点概念不仅对DBA有用对数据工程师同样重要。你在做实时数仓同步时遇到“数据对不上”第一步去源头看binlog有没有解析到这一步就能筛掉一半问题。5. 事务日志与崩溃恢复InnoDB的redolog和undolog关于事务日志redo log和undo log是最容易搞混的一组概念。尤其是面试的时候很多人能说“redolog是崩溃恢复用的undolog是回滚用的”但一旦追问“为什么有了binlog还要redo log”就开始语无伦次。这里把这块知识用流程的方式讲清楚。5.1 redo log的两阶段写InnoDB在事务提交时其实做了不少手脚。当你执行一条UPDATE数据并不会立刻刷到磁盘的数据文件里而是先写进内存中的缓冲池Buffer Pool并且把这次变更记录写入redo log buffer。等到事务提交时再把redo log buffer里的内容刷入磁盘上的redo log文件。这里关键的点是MySQL为了配合binlog引入了“两阶段提交”先写redo log状态为prepare再写binlog最后把redo log标记为commit。如果在写完binlog前数据库崩溃了恢复时会发现redo log有prepare状态的记录但binlog里没有对应记录那就判定这个事务未提交回滚掉。如果binlog写完了但还没标记commit恢复时会用binlog里的记录来补齐redo log保证主从数据一致。这个机制很多人不知道但它解释了非常多主从数据不一致和恢复异常的奇怪现象。我贴个简单流程让你直观理解事务开始修改Buffer Pool中的数据页生成redo log。事务提交前把redo log标记为prepare刷入磁盘。写入binlog刷入磁盘。把redo log标记为commit事务完成。第2、3步之间就是崩溃恢复的重点判断区间。5.2 undo log与MVCCundo log不是用来崩溃恢复的它有两个主要作用一是事务回滚时把数据恢复成旧值二是实现MVCC多版本并发控制时供其他事务读取旧版本数据。举个例子事务A更新了一行数据事务B在这时执行SELECT它看不到事务A未提交的修改必须读取该行修改前的版本这个旧版本就是通过undo log构建的。所以undo log其实是一个版本链记录着每次修改前的镜像。在生产环境中如果并发特别高并且长时间不提交事务undo log会不断累积表现为undo表空间膨胀。这个在MySQL 8.0里可以通过innodb_undo_log_truncateON来自动截断但前提是不能再有长事务否则截断会卡住。5.3 redo log相关参数与常见误区redo log在InnoDB里默认是ib_logfile0、ib_logfile1配置参数是innodb_log_file_size和innodb_log_files_in_group。在MySQL 8.0.30里这两个参数已经被innodb_redo_log_capacity整合替代但旧的配置文件里仍然可以设置。说到误区最常见的是有人以为“把redo log设置得越大越好”。如果redo log太大崩溃恢复时间会变长而且磁盘占用高如果太小在高并发写入时会导致频繁的checkpoint刷盘性能断崖式下跌。我的建议是对普通业务系统redo log总容量设置成1GB到4GB之间足够写入频繁的核心交易系统可以适当加大。如何判断redo log是否太小观察SHOW ENGINE INNODB STATUS的输出查找Log sequence number和Log flushed up to之间的间隔如果两者差值经常接近redo log总容量就说明日志太小需要调大。6. 通用查询日志与数据库连接池一个容易被忽视的调试角色通用查询日志这个角色有点尴尬它记录的不是慢SQL而是所有到达MySQL服务器的SQL语句和连接建立信息。因为记录全量SQL它的IO开销和磁盘消耗都很高生产环境默认关闭。但有些场景离开它根本没法排查。6.1 通用查询日志能查到什么比如某个连接池对接出了问题应用连接数暴增你想看看到底是哪个客户端建立了连接、有没有发SQL。又比如某段时间MySQL负载很高但慢查询日志里又没抓到什么这时候可以短暂开启通用查询日志抓到所有线程的执行SQL分析出负载到底是谁贡献的。通用查询日志的开关和路径如下SET GLOBAL general_log ON; SET GLOBAL general_log_file /var/log/mysql/general.log;我见过一次具体案例一个JavaWeb项目用连接池连接MySQL一段时间后连接耗尽报Too many connections。因为应用连接池的maxActive设置得过高而MySQL的max_connections相对较小。当时不开通用查询日志根本不知道哪些连接一直占着不释放开了之后发现大量连接处在Sleep状态说明连接池里借出去的连接没有及时归还。这个场景下通用查询日志中记录的连接建立记录能帮你迅速定位是哪台应用服务器、哪个用户占据大量Sleep线程。6.2 慎开、快关的使用原则通用查询日志在低峰期开个5-10分钟就够抓到需要的样本后马上关闭。生产环境从打开到关闭这段时间磁盘IO可能会成为瓶颈所以更推荐用log_outputTABLE写进表里抓完之后用SQL查询分析。但需要注意写到mysql.general_log表里同样会产生大量写入操作本质没有变。我在实际排查时如果不是必须更多会借助performance_schema的事件表来替代通用查询日志它能按线程、按用户名、按耗时聚合SQL效率比翻日志高一个量级。但通用查询日志的“全局无差别记录”特性在某些特定时刻仍然是不可替代的。7. 日志清理与磁盘空间别让日志成为事故源头日志把磁盘撑爆导致数据库宕机这个故障类型在运维圈里非常常见。你以为是有黑客攻击结果一看磁盘100%元凶是binlog积压、慢日志积压、错误日志积压。处理日志空间问题必须有预案。7.1 binlog的自动清理策略binlog的过期清理主要由expire_logs_days或MySQL 8.0的binlog_expire_logs_seconds控制。旧参数的单位是天新参数精确到秒配置生效后会按时间自动删除旧的binlog文件。-- 查看当前binlog过期策略 SHOW VARIABLES LIKE expire_logs_days; SHOW VARIABLES LIKE binlog_expire_logs_seconds; -- 设置binlog保留3天MySQL 8.0 SET GLOBAL binlog_expire_logs_seconds 259200;如果你的主从复制延迟严重binlog清理策略设置不当会引发从库断链。因为从库还在追读一个主库已经删掉的binlog主从复制就会报Got fatal error 1236。所以清理策略不能只看磁盘还得看从库的延迟和读取进度。手动清理binlog也有专门的语法-- 删除到指定日志之前的binlog不会删除正在写的那个 PURGE BINARY LOGS TO binlog.000010;采用RESET MASTER清空所有binlog这种操作在从库环境里等同于自杀一旦执行所有从库都会因为找不到binlog而断开务必谨慎。7.2 系统日志的清理不只是MySQL的事在Linux环境里系统本身的日志也会大量消耗磁盘比如/var/log/messages、/var/log/syslog。很多人搜“linux清空日志log命令”其实最安全的是用truncate而不是直接rm后重建。# 清空日志文件但保留文件句柄 truncate -s 0 /var/log/mysql/error.log truncate -s 0 /var/log/mysql/slow.log注意不要直接echo /var/log/mysql/error.log因为这样会改变文件的inodeMySQL可能不会继续向这个文件写入需要重启才生效这又是一个隐性的坑。7.3 磁盘空间压测排查的一条命令序列如果MySQL的磁盘快满了我的排查顺序是先看谁占用了空间再决定怎么处理。# 查看各目录占用 du -sh /var/lib/mysql/* | sort -rh | head -20 # 查看日志文件大小 ls -lh /var/lib/mysql/*.log /var/log/mysql/*.log 2/dev/null # 查看binlog列表及大小 SHOW BINARY LOGS;如果发现是binlog积压先查从库状态如果Seconds_Behind_Master很大优先解决从库延迟再考虑清理binlog。如果只是归档没用可以直接PURGE BINARY LOGS。如果错误日志巨大先看一眼里面是不是在刷某种报错比如多次启动失败、反复连接拒绝这类情况解决根本问题之前清空日志只是自欺欺人。8. 从日志到MySQL常见报错几个容易被搜疯的疑难杂症这里结合一些非常高频的搜索词把日志系统和常见报错的关联做一个统一梳理帮你省掉到处搜资料的功夫。这些报错常年出现在“mysql安装教程”、“docker安装mysql失败”、“mysql ssl连接错误”等热搜词背后基本是入门者和运维人员最容易卡壳的地方。8.1 通用查询日志与“Too many connections”应用一直报Too many connections时慢日志和错误日志不一定有有效输出因为连接拒绝发生在SQL执行之前。此时打开通用查询日志你会看到大量的连接建立事件但真正执行SQL的线程不多。结合SHOW PROCESSLIST观察线程状态能确定泄漏点。我见过一个比较经典的案例连接池的testOnBorrow配置不当导致每个连接在借出前都执行一次探测SQL高并发下这个探测SQL拖慢了整个连接池连接积压越来越多最终触发Too many connections。日志里表现就是大量SELECT 1的查询。这种坑不看通用查询日志或performance_schema完全发现不了。8.2 中继日志与主从复制中断主从复制报错Cannot replicate because the master purged required binary logs或者relay log read failure时排查重点就是中继日志。中继日志是SQL线程读取从库本地relay log执行变更时产生的一旦中继日志损坏或空间不足从库也会崩溃。处理这类问题常规操作是STOP SLAVE; RESET SLAVE; CHANGE MASTER TO MASTER_LOG_FILEbinlog.000010, MASTER_LOG_POS120; START SLAVE;但RESET SLAVE会清空中继日志如果你的从库已经执行了一半事务直接reset会导致数据不一致所以更安全的做法是START SLAVE UNTIL逐步追平。8.3 mysql ssl连接错误与日志记录SSL连接报错也是一个很诡异的现象。客户端连接时报SSL connection error服务端错误日志往往不会直接说“SSL证书过期”而是显示[ERROR] [MY-013602] SSL error: ...或[Warning] SSL connection error。此时优先检查MySQL配置文件里的ssl_ca、ssl_cert、ssl_key路径是否正确、证书是否过期。还可以通过一条SQL验证SSL是否真的开启SHOW VARIABLES LIKE %ssl%;别被各种教程带着去改skip_ssl这虽然能绕开报错但会让整个连接变成明文传输不是万不得已不要这么干。8.4 Docker运行MySQL的日志排查很多人搜“docker安装mysql失败”本质都是容器启动后立即退出。这种问题最简单粗暴的排查方式是直接看容器日志docker logs mysql-container比如常见报错是[ERROR] [MY-010118] Cant start server: Bind on unix socket: Permission denied说明权限没给对。如果日志里出现[ERROR] [MY-010457] Table mysql.user doesnt exist说明数据目录是空的或者初始化没完成再配合--initialize-insecure初始化一次数据目录基本就能解决。Docker里挂载数据卷时还有个大坑宿主机目录属主跟容器内mysql用户的UID不一致导致容器启动时无法写入数据。日志里会出现[ERROR] [MY-011972] InnoDB: Operating system error number 13 in a file operation这类报错通过chown -R 999:999 /data/mysql解决甚至有些场景用docker compose里的user字段做映射。9. 日志分析的工具与日常巡检脚本整理最后分享一套我自己在实际环境中用的日志巡检思路不依赖复杂平台用Linux自带命令和MySQL的SQL命令就能覆盖日常需求。9.1 半小时级巡检脚本思路一个成熟的巡检脚本应该包含以下内容我建议把脚本写到cron里每半小时自动跑一次把输出结果追加到统一的日志文件里这样回溯问题时能有一个连续的时间线。#!/bin/bash # MySQL日志巡检脚本 MYSQL_USERroot MYSQL_PASSyourpassword LOG_DIR/var/log/mysql_check DATE$(date %Y%m%d%H%M) # 检查错误日志新增内容 tail -n 100 /var/log/mysql/error.log $LOG_DIR/error_$DATE.log # 检查慢查询最近新增 tail -n 100 /var/log/mysql/slow.log $LOG_DIR/slow_$DATE.log # 检查当前binlog大小 mysql -u$MYSQL_USER -p$MYSQL_PASS -e SHOW BINARY LOGS; $LOG_DIR/binlog_$DATE.log # 检查连接数 mysql -u$MYSQL_USER -p$MYSQL_PASS -e SHOW STATUS LIKE Threads_connected; $LOG_DIR/status_$DATE.log不过更推荐的做法是直接用pt-query-digest来汇总慢日志它在分析慢日志时能输出SQL指纹、执行频率、平均耗时等统计数据比裸翻慢日志效率高太多。9.2 日志与日志可视化方案当服务器数量多了以后单机翻日志会累死人。此时可以引入ELK方案filebeat采集MySQL日志、logstash加工、elasticsearch存储、kibana展示。现在比较轻量的是用Loki Promtail Grafana体系定位日志关键字和做时间线分析都很方便。但我的经验是先别急着堆工具。如果你只有一两个MySQL实例老老实实用命令查日志是最快的。工具链引入的维护成本有时候比直接查日志的成本还高。等到日志量和实例数确实上来了再逐步把日志接入统一采集平台。9.3 日志文件名“万能”查看法不管是哪种日志你都可能忘记具体路径。这里有个一招鲜直接通过MySQL变量查。以最常用的几个为例SHOW VARIABLES LIKE log_error; SHOW VARIABLES LIKE slow_query_log_file; SHOW VARIABLES LIKE general_log_file; SHOW VARIABLES LIKE datadir;记住以MySQL变量的输出为准。网上教程里写的路径只能参考不一定适应你的环境。我曾经碰到过一个案例配置文件里写了log-error/var/log/mysql/error.log但实际环境里MySQL用的是基于datadir的默认路径导致怎么查都查不到报错内容最后就是用上面这个变量才确认了真实路径。回到开头说的MySQL日志的知识点多且杂但它是数据库运维和数据恢复的基本功也是排查问题时的第一手材料。我在实际运维里踩过不少坑比如日志文件权限配置疏忽导致重启失败比如把通用查询日志当慢日志开着导致磁盘被写爆又比如在没确认从库进度时PURGE binlog把复制链路弄断。这些经历让我形成了一套自己的习惯日志能开就开但必须明确目标、控制体积、设置清理策略排障第一眼看error日志性能问题第二眼看slow日志数据恢复问题马上想到binlog主从问题紧盯relay log。日志本身不产生价值产生价值的是你通过日志看到了系统的真实状态并做出正确决策。在MySQL的日常维护中少走弯路的最好方式就是养成遇到问题先翻日志的习惯这比记再多命令都管用。
