二进制日志BINARY LOG清理

原创

wx58df4443e3647 2022-03-03 13:38:29 ©著作权

©著作权归作者所有：来自51CTO博客作者wx58df4443e3647的原创作品，请联系作者获取转载授权，否则将追究法律责任

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       126 |
| mysql-bin.000002 |       150 |
| mysql-bin.000003 |       150 |
| mysql-bin.000004 |       150 |
| mysql-bin.000005 |       107 |
+------------------+-----------+
5 rows in set (0.00 sec)

删除列于指定日志之前的所有日志，但不包括指定的日志：

mysql> purge binary logs to 'mysql-bin.000002';
Query OK, 0 rows affected (0.10 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |       150 |
| mysql-bin.000003 |       150 |
| mysql-bin.000004 |       150 |
| mysql-bin.000005 |       107 |
+------------------+-----------+
4 rows in set (0.00 sec)

查看binlog是否开启：

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |      368 | test,test2   |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

删除2016－08－22 16:00:00时间点之前的日志：

mysql> purge binary logs before '2016-08-22 16:00:00';
Query OK, 0 rows affected (0.16 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000007 |       368 |
+------------------+-----------+
1 row in set (0.00 sec)

注意事项：

在删除binlog日志同时，也会清理MySQL-bin.index的文件记录，清理完后命令中指定的日志文件成为第一个。

[root@DB-S data]# cat mysql-bin.index 
./mysql-bin.000007

主从架构下，如果复制正在进行中，执行该命令是安全的，例如slave正在读取我们要删除的log，该语句将什么也不会做，并返回一个错误；如果复制是停止的，我们删除了一个slave还未读取的日志，则复制重新建立连接时将会失败。

指定过期天数（expire_logs_days）:该参数为全局动态调整参数，默认值为0，即关闭，取值范围0－99；

mysql> SHOW VARIABLES LIKE 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)

这里我设置过期天数为：一周

mysql> set global expire_logs_days = 7;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 7     |
+------------------+-------+
1 row in set (0.01 sec)

注意：在双机复制环境下，应确保过期天数不应小于从机追赶主机binlog日志的时间。

触发过期删除的条件:

1重启MYSQL；

2BINLOG文件大小达到参数max_binlog_size限制；

3手动执行命令。

重置binlog（reset master）

mysql> reset master;
Query OK, 0 rows affected (0.26 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 | test,test2   |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> system cat mysql-bin.index
./mysql-bin.000001

该方法可以删除列于索引文件中的所有二进制日志，把二进制日志索引文件重新设置为空，并创建一个以.000001为后缀新的二进制日制文件，并删除，过去生成的二进制日志，如下：

mysql> system ls
ceshi      DB-S-relay-bin.000104  DB-S-relay-bin.000108    ib_logfile0  mysql-bin.000001     sx
chenlin   DB-S-relay-bin.000105  DB-S-relay-bin.000109    ib_logfile1  mysql-bin.index     test
DB-S.err  DB-S-relay-bin.000106  DB-S-relay-bin.index    master.info  performance_schema  test2
DB-S.pid  DB-S-relay-bin.000107  ibdata1        mysql         relay-log.info
mysql> flush logs;
Query OK, 0 rows affected (0.24 sec)

mysql> system ls
ceshi               DB-S-relay-bin.000107  ib_logfile1      relay-log.info
chenlin               DB-S-relay-bin.000108  master.info      sx
DB-S.err           DB-S-relay-bin.000109  mysql          test
DB-S.pid           DB-S-relay-bin.000110  mysql-bin.000001      test2
DB-S-relay-bin.000104  DB-S-relay-bin.index   mysql-bin.000002
DB-S-relay-bin.000105  ibdata1              mysql-bin.index
DB-S-relay-bin.000106  ib_logfile0          performance_schema
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       150 |
| mysql-bin.000002 |       107 |
+------------------+-----------+
2 rows in set (0.00 sec)

mysql> reset master;
Query OK, 0 rows affected (0.19 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       107 |
+------------------+-----------+
1 row in set (0.00 sec)

mysql> system ls
ceshi               DB-S-relay-bin.000106  ibdata1        mysql-bin.index
chenlin               DB-S-relay-bin.000107  ib_logfile0    performance_schema
DB-S.err           DB-S-relay-bin.000108  ib_logfile1    relay-log.info
DB-S.pid           DB-S-relay-bin.000109  master.info    sx
DB-S-relay-bin.000104  DB-S-relay-bin.000110  mysql        test
DB-S-relay-bin.000105  DB-S-relay-bin.index   mysql-bin.000001    test2