1. MySQL server的binlog清理
1.1 使用MySQL参数控制expire_logs_days设置二进制日志的过期天数,过了指定天数的日志将被自动删除,可动态修改
如果设置了非0值,则在mysqld启动和日志刷新时,可能执行清理超过定义天数的binlog file
全局变量,动态变量,默认值为0(代表不会自动清理binlog),整型值,取值范围为0~99
自动清理的具体实现是:当binlog文件达到 max_binlog_size自动切换或者手动切换(flush)或者MySQL启动(startup)时,会遍历index文件,找到第一个“最后修改时间”在N天内的binlog文件,然后将该binlog文件之前的所有binlog文件删除掉。
1.2 手动purge清理
通常手动清理binlog是使用MySQL提供的purge命令。purge命令的定义如下:
purge {binary | master} logs to "binlog-file-name"
purge {binary | master} logs before "datetime-expr"
其中第一种形式的purge命令的作用是将binlog-file-name之前的(不包括自己本身)所有binlog文件清理掉,而第二种形式的purge命令的作用是将最后修改时间早于datetime-rxpr的binlog文件清理掉(不包括自己)。
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000006 | 1215598 |
| mysql-bin.000007 | 20380128 |
| mysql-bin.000008 | 57424 |
| mysql-bin.000009 | 1861624 |
+------------------+-----------+
4 rows in set (0.03 sec)
mysql> PURGE BINARY LOGS TO 'mysql-bin.000007';
Query OK, 0 rows affected (0.06 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000007 | 20380128 |
| mysql-bin.000008 | 57424 |
| mysql-bin.000009 | 1870354 |
+------------------+-----------+
3 rows in set (0.02 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2019-04-11 12:32:31 |
+---------------------+
1 row in set (0.00 sec)
mysql> PURGE BINARY LOGS BEFORE '2019-04-11 12:32:31';
Query OK, 0 rows affected, 1 warning (0.11 sec)
Warning (Code 1868): file /var/lib/mysql/archive/mysql-bin.000009 was not purged because it is the active log file.
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000