mysql binlog无法自动删除处理

一.问题描述
新系统在上线前经过压测，产生大量的binlog日志，但时间过去了一周，日志文件一直没有自动删除，依然占用大量磁盘空间。
mysql版本：mariaDB 10.1.11

二.问题分析

1.查看binlog过期参数，3天过期。

show global variables like 'expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 3 |
+------------------+-------+
1 row in set (0.01 sec)

2.查看binlog最大值设置，大小为512M。
show variables like 'max_binlog_size';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| max_binlog_size | 536870912 |
+-----------------+-----------+
1 row in set (0.00 sec)

3.查看OS当前时间，时间是准确的。
[apps@mwpl058 log]$ date
Tue Nov 1 09:21:00 CST 2016

4.查看binlog文件情况
show binary logs;
+------------------+------------+
| Log_name | File_size |
+------------------+------------+
| mysql-bin.000004 | 536872787 |
| mysql-bin.000005 | 536873559 |
| mysql-bin.000006 | 536872037 |
| mysql-bin.000007 | 536871022 |
| mysql-bin.000008 | 536873565 |
| mysql-bin.000009 | 536873553 |
| mysql-bin.000010 | 536874309 |
| mysql-bin.000011 | 536874215 |
| mysql-bin.000012 | 536871036 |
| mysql-bin.000013 | 536873477 |
| mysql-bin.000014 | 536873805 |
| mysql-bin.000015 | 536873248 |
| mysql-bin.000016 | 536874398 |
| mysql-bin.000017 | 536874375 |
| mysql-bin.000018 | 536872468 |
| mysql-bin.000019 | 536872774 |
| mysql-bin.000020 | 536872737 |
| mysql-bin.000021 | 536871054 |
| mysql-bin.000022 | 536871227 |
| mysql-bin.000023 | 536873274 |
| mysql-bin.000024 | 536874328 |
| mysql-bin.000025 | 536871154 |
| mysql-bin.000026 | 536872951 |
| mysql-bin.000027 | 536871914 |
| mysql-bin.000028 | 536871218 |
| mysql-bin.000029 | 536872033 |
| mysql-bin.000030 | 536871760 |
| mysql-bin.000031 | 536871008 |
| mysql-bin.000032 | 536872036 |
| mysql-bin.000033 | 536875830 |
| mysql-bin.000034 | 536871588 |
| mysql-bin.000035 | 536875222 |
| mysql-bin.000036 | 536871210 |
| mysql-bin.000037 | 536872172 |
| mysql-bin.000038 | 536872877 |
| mysql-bin.000039 | 536871156 |
| mysql-bin.000040 | 536874087 |
| mysql-bin.000041 | 536872257 |
| mysql-bin.000042 | 536872923 |
| mysql-bin.000043 | 541863112 |
| mysql-bin.000044 | 536871494 |
| mysql-bin.000045 | 536872167 |
| mysql-bin.000046 | 536871301 |
| mysql-bin.000047 | 536871384 |
| mysql-bin.000048 | 586296851 |
| mysql-bin.000049 | 536871823 |
| mysql-bin.000050 | 536871288 |
| mysql-bin.000051 | 536872267 |
| mysql-bin.000052 | 536872212 |
| mysql-bin.000053 | 559677810 |
| mysql-bin.000054 | 571145530 |
| mysql-bin.000055 | 843530054 |
| mysql-bin.000056 | 2306758408 |
| mysql-bin.000057 | 906822967 |
| mysql-bin.000058 | 929503132 |
| mysql-bin.000059 | 553530156 |
| mysql-bin.000060 | 664342784 |
| mysql-bin.000061 | 444814324 |
+------------------+------------+
58 rows in set (0.00 sec)

上面显示与mysql-bin.index文件中的内容相同。

[apps@mwpl058 log]$ ls -ltr
total 33341468
-rw-rw---- 1 apps apps 536872787 Oct 25 15:50 mysql-bin.000004
-rw-rw---- 1 apps apps 536873559 Oct 25 16:23 mysql-bin.000005
-rw-rw---- 1 apps apps 536872037 Oct 25 17:16 mysql-bin.000006
-rw-rw---- 1 apps apps 536871022 Oct 25 17:49 mysql-bin.000007
-rw-rw---- 1 apps apps 536873565 Oct 25 18:21 mysql-bin.000008
-rw-rw---- 1 apps apps 536873553 Oct 25 18:53 mysql-bin.000009
-rw-rw---- 1 apps apps 536874309 Oct 25 19:56 mysql-bin.000010
-rw-rw---- 1 apps apps 536874215 Oct 25 21:57 mysql-bin.000011
-rw-rw---- 1 apps apps 536871036 Oct 25 23:58 mysql-bin.000012
-rw-rw---- 1 apps apps 536873477 Oct 26 01:59 mysql-bin.000013
-rw-rw---- 1 apps apps 536873805 Oct 26 04:00 mysql-bin.000014
-rw-rw---- 1 apps apps 536873248 Oct 26 06:01 mysql-bin.000015
-rw-rw---- 1 apps apps 536874398 Oct 26 10:07 mysql-bin.000016
-rw-rw---- 1 apps apps 536874375 Oct 26 10:22 mysql-bin.000017
-rw-rw---- 1 apps apps 536872468 Oct 26 10:37 mysql-bin.000018
-rw-rw---- 1 apps apps 536872774 Oct 26 11:05 mysql-bin.000019
-rw-rw---- 1 apps apps 536872737 Oct 26 11:19 mysql-bin.000020
-rw-rw---- 1 apps apps 536871054 Oct 26 11:34 mysql-bin.000021
-rw-rw---- 1 apps apps 536871227 Oct 26 11:48 mysql-bin.000022
-rw-rw---- 1 apps apps 536873274 Oct 26 13:31 mysql-bin.000023
-rw-rw---- 1 apps apps 536874328 Oct 26 13:42 mysql-bin.000024
-rw-rw---- 1 apps apps 536871154 Oct 26 13:52 mysql-bin.000025
-rw-rw---- 1 apps apps 536872951 Oct 26 14:03 mysql-bin.000026
-rw-rw---- 1 apps apps 536871914 Oct 26 14:28 mysql-bin.000027
-rw-rw---- 1 apps apps 536871218 Oct 26 14:56 mysql-bin.000028
-rw-rw---- 1 apps apps 536872033 Oct 26 15:19 mysql-bin.000029
-rw-rw---- 1 apps apps 536871760 Oct 26 15:43 mysql-bin.000030
-rw-rw---- 1 apps apps 536871008 Oct 26 16:04 mysql-bin.000031
-rw-rw---- 1 apps apps 536872036 Oct 26 16:28 mysql-bin.000032
-rw-rw---- 1 apps apps 536875830 Oct 26 16:31 mysql-bin.000033
-rw-rw---- 1 apps apps 536871588 Oct 26 16:34 mysql-bin.000034
-rw-rw---- 1 apps apps 536875222 Oct 26 16:37 mysql-bin.000035
-rw-rw---- 1 apps apps 536871210 Oct 26 17:06 mysql-bin.000036
-rw-rw---- 1 apps apps 536872172 Oct 26 17:09 mysql-bin.000037
-rw-rw---- 1 apps apps 536872877 Oct 26 17:12 mysql-bin.000038
-rw-rw---- 1 apps apps 536871156 Oct 26 17:15 mysql-bin.000039
-rw-rw---- 1 apps apps 536874087 Oct 26 17:18 mysql-bin.000040
-rw-rw---- 1 apps apps 536872257 Oct 26 17:21 mysql-bin.000041
-rw-rw---- 1 apps apps 536872923 Oct 26 17:24 mysql-bin.000042
-rw-rw---- 1 apps apps 541863112 Oct 26 18:58 mysql-bin.000043
-rw-rw---- 1 apps apps 536871494 Oct 26 19:54 mysql-bin.000044
-rw-rw---- 1 apps apps 536872167 Oct 26 21:24 mysql-bin.000045
-rw-rw---- 1 apps apps 536871301 Oct 26 21:41 mysql-bin.000046
-rw-rw---- 1 apps apps 536871384 Oct 26 21:55 mysql-bin.000047
-rw-rw---- 1 apps apps 586296851 Oct 27 17:24 mysql-bin.000048
-rw-rw---- 1 apps apps 536871823 Oct 27 17:37 mysql-bin.000049
-rw-rw---- 1 apps apps 536871288 Oct 27 17:44 mysql-bin.000050
-rw-rw---- 1 apps apps 536872267 Oct 27 19:52 mysql-bin.000051
-rw-rw---- 1 apps apps 536872212 Oct 27 19:54 mysql-bin.000052
-rw-rw---- 1 apps apps 559677810 Oct 27 21:14 mysql-bin.000053
-rw-rw---- 1 apps apps 571145530 Oct 28 09:23 mysql-bin.000054
-rw-rw---- 1 apps apps 843530054 Oct 28 09:26 mysql-bin.000055
-rw-rw---- 1 apps apps 2306758408 Oct 28 09:28 mysql-bin.000056
-rw-rw---- 1 apps apps 906822967 Oct 28 09:29 mysql-bin.000057
-rw-rw---- 1 apps apps 929503132 Oct 28 09:32 mysql-bin.000058
-rw-rw---- 1 apps apps 553530156 Oct 28 09:40 mysql-bin.000059
-rw-rw---- 1 apps apps 3132 Oct 28 09:43 mysql-bin.index
-rw-rw---- 1 apps apps 664342784 Oct 28 09:43 mysql-bin.000060
-rw-rw---- 1 apps apps 444823292 Nov 1 11:19 mysql-bin.000061

从上面显示看，最后一次日志切换发生在 Oct 28 09:43时间点，按binlog过期设置3天前的时间为Oct 25 09:43才会自动删除，而最早的binlog文件时间为Oct 25 15:50，故还没到binlog切换条件
所以没有发生自动删除。

mysql的binlog自动删除发生在：
1.手工执行flush logs;
2.binlog文件大小超过max_binlog_size的设置大小;
3.重启mysql

由于刚作完压力测试，现在系统正等待上线，系统没什么压力。

另外，手工删除binlog的方法如下：

方法一：
purge binary logs to 'binlog.000058'; ----（删除mysql bin-log日志，删除binlog.000058之前的，不包括binlog.000058）

方法二：
PURGE binary LOGS BEFORE '2008-06-22 13:00:00';   //清除2008-06-22 13:00:00前binlog日志
PURGE binary LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);  //清除3天前binlog日志BEFORE，变量的date自变量可以为'YYYY-MM-DD hh:mm:ss'格式。

三.问题解决
为了释放空间，需执行flush logs来切换log，以自动删除binlog。
1.检查主从之间同步，确认同步正常。

2.执行flush logs
3.查看binlog：
show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000061 | 444823335 |
| mysql-bin.000062 | 366 |
+------------------+-----------+
2 rows in set (0.00 sec)

[apps@mwpl058 log]$ ls -lt
total 438504
-rw-rw---- 1 apps apps 108 Nov 1 13:51 mysql-bin.index
-rw-rw---- 1 apps apps 444823335 Nov 1 13:51 mysql-bin.000061
-rw-rw---- 1 apps apps 366 Nov 1 13:51 mysql-bin.000062

确认三天前的binlog已删除。