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

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)

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工具的,可以将慢日志定期拉走,备份到远端。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/0722ddbc1759ce10deb06b993461aae8.html