MySQL binlog太大,如何优化及清理/删除binlog

这是继上次写的《Nginx access.log文件太大,自动释放清理》的关联调优文章继续。今天同样有同学反馈图片加载速度稍慢,于是我又再次检查,然后查到了MySQL binlog是根源,作记录。

查出binlog

检查相关pinghttps,以及加载回馈都正常,于是上服务器先看一下使用量:

其中在df -h发现异常,磁盘使用量比前天增涨了15G左右,正常使用不可能有这个量,下面查找是谁占用了如此大的空间,通过如下命令找到机器上超过50MB的文件。

  • 调整下面的{/path/to/directory/}为根路径: /
  • 调整下面的{size-in-kb}为: 50000,单位kb,即50MB。

在结果中锁定了MySQL日志目录,进去看一下:

MySQL binlog太大,如何优化及清理/删除binlog

这样就确定了问题源,是binlog日志异常,好了,下面来科普一下什么是binlog并解决它。

binlog介绍

  1. binlog 是什么
    binlog日志用于记录所有更新了数据或者已经潜在更新了数据(例如,没有匹配任何行的一个DELETE)的所有语句。语句以“事件”的形式保存,它描述数据更改。
  2. binlog 作用
    因为有了数据更新的binlog,所以可以用于实时备份,与master/slave主从复制结合。
  3. binlog有关参数
    1. log_bin:设置此参数表示启用binlog功能,并指定路径名称
    2. log_bin_index:设置此参数是指定二进制索引文件的路径与名称
    3. binlog_do_db:此参数表示只记录指定数据库的二进制日志
    4. binlog_ignore_db:此参数表示不记录指定的数据库的二进制日
    5. max_binlog_cache_size:此参数表示binlog使用的内存最大的尺寸
    6. binlog_cache_size:此参数表示binlog使用的内存大小,可以通过状态binlog_cache_use和binlog_cache_disk_use来帮助测试。
    7. binlog_cache_use:使用二进制日志缓存的事务数量
    8. binlog_cache_disk_use:使用二进制日志缓存但超
    9. binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量
    10. max_binlog_sizeBinlog最大值,最大和默认值是1GB,该设置并不能严格控制Binlog的大小,尤其是Binlog比较靠近最大值而又遇到一个比较大事务时,为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的所有SQL都记录进当前日志,直到事务结束
    11. sync_binlog:这个参数直接影响mysql的性能和完整性
    12. sync_binlog=0:当事务提交后,Mysql仅仅是将binlog_cache中的数据写入Binlog文件,但不执行fsync之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘,而让Filesystem自行决定什么时候来做同步,这个是性能最好的。
    13. sync_binlog=n:在进行n次事务提交以后,Mysql将执行一次fsync之类的磁盘同步指令,同志文件系统将Binlog文件缓存刷新到磁盘。

Mysql中默认设置sync_binlog=0,即不作任何强制性的磁盘刷新指令,这时性能是最好的,但风险也是最大的。一旦系统绷Crash,在文件系统缓存中的所有Binlog信息都会丢失

binlog删除

binlog的删除可以手工删除或自动删除。binlog删除建议根据你的环境自行选择用哪种方式,以及相关的调优设置。体验盒子建议你使用手动删除和方式进行更为安全。

删除前再次确认binlog清单:

MySQL binlog太大,如何优化及清理/删除binlog

没问题,和排查到的一样。

自动删除binlog日志

通过binlog参数expire_logs_days来实现mysql自动删除binlog,此方法并不能马上清理掉binlog日志文件。

手工删除binlog日志

手工删除binlog示例:

关闭binlog日志/禁用binlog日志

找到mysql配置文件my.cnf,在配置中找到log-bin=mysql-bin然后注释掉就可以了。

重启mysql服务即可。操作完成,以后再不会因为就几十M的数据库大小生成N个G的日志文件啦。(但并不建议直接关闭binlog日志。

优化binlog日志

binlog日志配置在mysql的my.cnf文件中设置,其中默认binlog大小为1G,binlog日志过期时间为0(即永不过期),造成binlog太大。

binlog最大为500M,binlog日志过期时间为3天。这里只是简单的进行了优化设置,并不全面,但应付日PV1万足矣。

扩展

https://dev.mysql.com/doc/refman/8.0/en/binary-log.html