MySQL慢日志占满磁盘空间导致业务主库宕机解决

栏目: 数据库 · 发布时间: 6年前

内容简介：在MySQL的日常维护中，我们总会遇到这样或那样的问题，对于那些经常发生且有处理经验的事故，不论是新手还是老司机都能在故障规定的容错时间内解决。而对于那些不常见、比较棘手的问题，新手上路可能就显得举足无措了，这个时候新手和老司机的差距就体现出来了。从知识储备还是工作经验，可能老司机比新手强一点，但如果一个新司机没有日志排错的意识，不具备日志排错的经验，那怎么能学会弯道超车、漂移的快感。我们知道数据库中有很多重要的日志，如错误日志error log、慢日志slow log、二进制日志binary log、查

在 MySQL 的日常维护中，我们总会遇到这样或那样的问题，对于那些经常发生且有处理经验的事故，不论是新手还是老司机都能在故障规定的容错时间内解决。而对于那些不常见、比较棘手的问题，新手上路可能就显得举足无措了，这个时候新手和老司机的差距就体现出来了。从知识储备还是工作经验，可能老司机比新手强一点，但如果一个新司机没有日志排错的意识，不具备日志排错的经验，那怎么能学会弯道超车、漂移的快感。我们知道数据库中有很多重要的日志，如错误日志error log、慢日志slow log、二进制日志binary log、查询日志general log等等其他日志，错误日志error log是我们分析问题参考的依据，它记录数据库的启动/运行/停止的过程，包含了info、warning、error三个级别，分析error log也有助于我们了解数据库的运行机制。

我们知道数据库中的binary log、relay log都是数据库本身自带的purge清理线程处理过时的没有用的日志，这种处理能有效释放磁盘空间。而对于慢日志slow log、错误日志error log这种记录数据库实例整个运行阶段的日志，不会被定期处理，那么就有可能会被记录得过多，占据过多的磁盘空间。对于错误日志error log，默认记录；对于slow log需要我们自己选择是否记录。建议开启slow log功能，这对于数据库优化之一的 SQL 优化有很大的帮助。

通常我们在业务主库是开启慢日志功能并通过参数long_query_time这个参数来控制执行时间多长的SQL被记录进慢日志中，且对于执行时间超过1s的SQL就认为是慢SQL，这样的设定值，很多场合下不会记录太多的慢SQL，因而不会占据太多的磁盘空间。然而当开发上线的程序有问题，SQL执行效率不高，且执行的频率非常高，这些慢SQL被记录就会存在磁盘空间被撑爆的安全隐患，从而导致数据库宕机并尝试重启且多次尝试失败，严重影响业务。不过值得庆幸的是，该部分业务我们有MMM高可用架构，VIP已经漂移到另一台master上了。

2018-05-29 09:09:18 28094 [ERROR] Error writing file '/opt/app/mysql/logs/slow.log' (errno: 1 - No space left on device)

180529 09:09:19 mysqld_safe mysqld restarted

InnoDB: 3 transaction(s) which must be rolled back or cleaned up

InnoDB: Starting in background the rollback of uncommitted transactions

/opt/app/mysql/bin/mysqld: Error writing file '/opt/app/mysql/logs/slow.log' (Errcode: 28 - No space left on device)

2018-05-29 09:09:33 33114 [ERROR] Could not open /opt/app/mysql/logs/slow.log for logging (error 28). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

2018-05-29 09:09:34 33114 [ERROR] /opt/app/mysql/bin/mysqld: Error writing file '/opt/app/mysql/tmp/mysqld.pid' (Errcode: 28 - No space left on device)

2018-05-29 09:09:34 33114 [ERROR] Can't start server: can't create PID file: No space left on device

如上面的报错显示（只是选择截取部分错误日志），数据库发生错误的原因是磁盘没有足够的空间，慢日志无法写入，数据库尝试restart并rollback没有提交的事务（继续查看后面的日志能看到redo log的信息），而数据库也对我们提出了建议关闭错误日志的记录功能。继续看启动过程，发现关于pid文件的No space left on device，数据库还是无法启动。排查故障并处理故障，在尽可能短的时间内恢复业务是最重要的，因而这里就没有详细的操作代码保存来说明。通过df -Th查看发现/空间不足，并ls -lh查看慢日志的大小是1.2T，已经严重消耗了磁盘空间。这个时候我们并不能直接rm -rf删除慢日志文件，因为数据库的启动时需要慢日志功能切且日志文件占据了磁盘空间，我们只能重定向清空慢日志，这样数据库得以重新启动。

虽然我们的业务主库有MMM高可用架构，事实发现VIP确实是漂移到另一台master上，但仍然给我们的其他slave造成了复制同步错误的故障，更为严重的是影响到了我们的多源复制库的使用，内部人员使用和维护也带来很大的影响。对于数据库本身来讲，error log和slow log不能自动清理，这有一定的优点，但同时也会有磁盘空间可能被撑爆的潜在危险。

如果能够通过一个crontab，或者一个告警，定期或者提醒我们对慢日志进行清理，我想也不会造成如今的这个麻烦。我们可以通过如下的一个 Shell 脚本，策略是删除几天以前的慢日志，保留最近几天的慢日志，或者使用过Inception工具的，可以将慢日志定期拉走，备份到远端。

[root@172-16-3-190 shells]# cat clean_mysql_slow_log.sh

#!/binbash

#author=lyx

#time:2018-05-29

cur_date=`date +%y%m%d`

#step_days=5 #clean slowlog 6 days ago

for step_days in $(seq 5 -1 0)

end_date=`expr $cur_date - $step_days`

clean_line_num=`cat /opt/shells/slow.log |grep -n "^# Time: $end_date"|head -n 1|cut -d : -f 1`

if ((${#clean_line_num} > 1))

then

clean_max_line_num=`expr $clean_line_num - 1`

sed -i "1,$clean_max_line_num d" /opt/shells/slow.log

break

done

echo $clean_max_line_num

echo $step_days

该脚本的策略是根据当前时间，保留最近5天的慢日志，每次运行脚本则保留的日志天数减少一天。当然如果你有更好的业务策略或者脚本逻辑也可以择优选择，比如你可以根据慢日志的文件大小，配置告警并触发清理，或者crontab都可以。