对于运维来讲,截断mysql binlog日志释放磁盘空间是必要的,因为时间太久远的binlog已经毫无意义了。

按照mysql的文档,截断binlog并不困难,下面是一个示例:

mysql> show binary logs;
+----------------------+-----------+
| Log_name | File_size |
+----------------------+-----------+
| 143-mysql-bin.000113 | 24902452 |
| 143-mysql-bin.000114 | 171 |
| 143-mysql-bin.000115 | 171 |
| 143-mysql-bin.000116 | 17582 |
| 143-mysql-bin.000117 | 1194036 |
| 143-mysql-bin.000118 | 16241754 |
| 143-mysql-bin.000119 | 104858365 |
| 143-mysql-bin.000120 | 104857673 |
| 143-mysql-bin.000121 | 104857659 |
| 143-mysql-bin.000122 | 104859207 |
| 143-mysql-bin.000123 | 104858027 |
| 143-mysql-bin.000124 | 104857668 |
| 143-mysql-bin.000125 | 104858586 |
| 143-mysql-bin.000126 | 79092077 |
| 143-mysql-bin.000127 | 104857996 |
| 143-mysql-bin.000128 | 104858482 |
| 143-mysql-bin.000129 | 104857915 |
| 143-mysql-bin.000130 | 104859195 |
| 143-mysql-bin.000131 | 104857994 |
| 143-mysql-bin.000132 | 104858944 |
| 143-mysql-bin.000133 | 104857912 |
| 143-mysql-bin.000134 | 104857797 |
| 143-mysql-bin.000135 | 104857760 |
| 143-mysql-bin.000136 | 97230598 |
| 143-mysql-bin.000137 | 104857677 |
| 143-mysql-bin.000138 | 104857765 |
| 143-mysql-bin.000139 | 104858130 |
| 143-mysql-bin.000140 | 104857727 |
| 143-mysql-bin.000141 | 104858573 |
| 143-mysql-bin.000142 | 104858606 |
| 143-mysql-bin.000143 | 104857880 |
| 143-mysql-bin.000144 | 104857681 |
| 143-mysql-bin.000145 | 104858000 |
| 143-mysql-bin.000146 | 104858072 |
| 143-mysql-bin.000147 | 72687827 |
| 143-mysql-bin.000148 | 460065 |
| 143-mysql-bin.000149 | 460065 |
| 143-mysql-bin.000150 | 50231353 |
| 143-mysql-bin.000151 | 18547688 |
+----------------------+-----------+
39 rows in set (0.00 sec)

mysql> purge binary logs to '143-mysql--bin.000135';
ERROR 1373 (HY000): Target log not found in binlog index
mysql> purge binary logs to '143-mysql-bin.000135';
Query OK, 0 rows affected (0.11 sec)

mysql> show binary logs;
+----------------------+-----------+
| Log_name | File_size |
+----------------------+-----------+
| 143-mysql-bin.000135 | 104857760 |
| 143-mysql-bin.000136 | 97230598 |
| 143-mysql-bin.000137 | 104857677 |
| 143-mysql-bin.000138 | 104857765 |
| 143-mysql-bin.000139 | 104858130 |
| 143-mysql-bin.000140 | 104857727 |
| 143-mysql-bin.000141 | 104858573 |
| 143-mysql-bin.000142 | 104858606 |
| 143-mysql-bin.000143 | 104857880 |
| 143-mysql-bin.000144 | 104857681 |
| 143-mysql-bin.000145 | 104858000 |
| 143-mysql-bin.000146 | 104858072 |
| 143-mysql-bin.000147 | 72687827 |
| 143-mysql-bin.000148 | 460065 |
| 143-mysql-bin.000149 | 460065 |
| 143-mysql-bin.000150 | 50231353 |
| 143-mysql-bin.000151 | 24259399 |
+----------------------+-----------+
17 rows in set (0.01 sec)

mysql>

可见,只需要找到一个恰当的位置,然后发出一条截断指令就够了。

为了简化运维工作,同样的我们呢这里提供一个预配置的脚本来实现自动化操作:

#!/bin/bash

DAYS=${1:-15}
# 注意,我们在 /etc/safe.mysql.passwd 中写入了root账户密码,注意安全;或者采用其他方式来解决mysql的命令行密码问题
file=$(mysql -uroot -p$(cat /etc/safe.mysql.passwd) -e 'show binary logs;'|tail -$DAYS|head -1|grep -Poi '^[^ \t]+')
#echo $file
mysql -uroot -p$(cat /etc/safe.mysql.passwd) -e "purge binary logs to '$file';"
mysql -uroot -p$(cat /etc/safe.mysql.passwd) -e 'show binary logs;'

把这个脚本加入到crontab配置中去,每天凌晨跑一跑,生活很easy是不是?