Mysql清理binlog

title: Mysql清理binlog
date: 2019-07-26 11:37:39
categories: Mysql

速查

show master logs;
PURGE BINARY LOGS TO 'mysql-bin.000001';
PURGE BINARY LOGS BEFORE '2014-04-28 23:59:59';
show variables like 'expire_logs_days';
set global expire_logs_days = 60;
flush logs;
RESET MASTER;

1 purge清理binlog

mysql> help purge
Name: 'PURGE BINARY LOGS'
Description:
Syntax:
PURGE { BINARY | MASTER } LOGS
    { TO 'log_name' | BEFORE datetime_expr }
​
The binary log is a set of files that contain information about data
modifications made by the MySQL server. The log consists of a set of
binary log files, plus an index file (see
http://dev.mysql.com/doc/refman/5.6/en/binary-log.html).
​
The PURGE BINARY LOGS statement deletes all the binary log files listed
in the log index file prior to the specified log file name or date.
BINARY and MASTER are synonyms. Deleted log files also are removed from
the list recorded in the index file, so that the given log file becomes
the first in the list.
​
This statement has no effect if the server was not started with the
--log-bin option to enable binary logging.
​
URL: http://dev.mysql.com/doc/refman/5.6/en/purge-binary-logs.html
​
Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

2.1 清理到某个binlog

指定的binlog会保留

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000075 | 524735314 |
| mysql-bin.000076 | 524735314 |
| mysql-bin.000077 | 524735314 |
| mysql-bin.000078 | 524735314 |
| mysql-bin.000079 | 524735314 |
| mysql-bin.000080 | 524735314 |
| mysql-bin.000081 | 524735314 |
| mysql-bin.000082 | 205076231 |
+------------------+-----------+
8 rows in set (0.00 sec)
​
mysql> purge binary logs to 'mysql-bin.000076';
Query OK, 0 rows affected (0.09 sec)
​
mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000076 | 524735314 |
| mysql-bin.000077 | 524735314 |
| mysql-bin.000078 | 524735314 |
| mysql-bin.000079 | 524735314 |
| mysql-bin.000080 | 524735314 |
| mysql-bin.000081 | 524735314 |
| mysql-bin.000082 | 205076231 |
+------------------+-----------+
7 rows in set (0.00 sec)

2.2 清理某个时间点前的binlog

等于指定时间点的binlog会保留

524735314 Jul 26 11:32 mysql-bin.000076
524735314 Jul 26 11:32 mysql-bin.000077
524735314 Jul 26 11:33 mysql-bin.000078
524735314 Jul 26 11:33 mysql-bin.000079
524735314 Jul 26 11:34 mysql-bin.000080
524735314 Jul 26 11:34 mysql-bin.000081
205076231 Jul 26 11:42 mysql-bin.000082

清理11:34前的binlog

mysql> PURGE BINARY LOGS BEFORE '2019-07-26 11:34:00';
Query OK, 0 rows affected (0.37 sec)

查看结果

524735314 Jul 26 11:34 mysql-bin.000080
524735314 Jul 26 11:34 mysql-bin.000081
205076231 Jul 26 11:42 mysql-bin.000082

2 手动清理binlog

第一步：删除master-log-bin.index里面的对应项

第二步：删除binlog文件

3 指定过期参数自动删除

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

设定为7天

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

刷日志立即触发

flush logs;

触发条件

1. 重启mysql；

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

3. Flush log

4 重置binlog

reset master

该方法可以删除列于索引文件中的所有二进制日志，把二进制日志索引文件重新设置为空，并创建一个新的二进制日志文件mysql-bin.000001。该语法一般只用在主从环境下初次建立复制时。在主从复制进行过程中，该语句是无效的。

主从环境下的配置步骤：

1. 启动master和slave，开启replication(即复制)

2. 在master上运行一些测试的语句，看数据是否能够复制到 slave上面

3. 当复制运行正常的话，就在从上stop slave 然后执行 reset slave,去掉不需要的数据

4. 在master上面执行reset master 清除测试产生的数据

5 清理注意事项

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

建议操作步骤：

1. 在每个从属服务器上，使用SHOW SLAVE STATUS来检查它正在读取哪个日志。

2. 使用SHOW MASTER LOGS获得主服务器上的一系列日志。

3. 在所有的从属服务器中判定最早的日志。这个是目标日志。如果所有的从属服务器是最新的，这是清单上的最后一个日志。

4. 备份将要删除的所有日志（看情况）。

5. 清理除目标日志之外的所有日志。