清理和设置MySQL的bin log日志与日志过期时间

mysql reset master 和 purge binary logs 的区别

在正常的主从复制环境中，如果在 master 上执行 reset master，结果是不可预测的。
但使用 purge binary logs 语句删除 binlog 没多大影响（但是相关的 binlog 记录已经传输到slave上）

reset master

会删除所有的二进制日志 

mysql> help reset master;
Name: 'RESET MASTER'
Description:
Syntax:
RESET MASTER [TO binary_log_file_index_number]

The following example demonstrates TO clause usage:

RESET MASTER TO 1234;

SHOW BINARY LOGS;
+-------------------+-----------+-----------+
| Log_name          | File_size | Encrypted |
+-------------------+-----------+-----------+
| master-bin.001234 |       154 | No        |
+-------------------+-----------+-----------+
--URL: https://dev.mysql.com/doc/refman/8.0/en/reset-master.html

purge binary logs

基于时间点的删除

删除在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个

PURGE {MASTER | BINARY} LOGS TO'log_name'

PURGE {MASTER | BINARY} LOGSBEFORE 'date'

mysql> help purge binary logs
Name: 'PURGE BINARY LOGS'
Description:
Syntax:
PURGE { BINARY | MASTER } LOGS {
    TO 'log_name'
  | BEFORE datetime_expr
}

Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';

设置和清理

1. 查看当前的log

show binary logs;
show master logs；

2. 删除指定日志之前的bin log

PURGE MASTER LOGS TO 'mysql-bin.010(日志名，即Log_name)';

3. 删除mysql-bin.000010 之前的日志

purge binary logs to 'mysql-bin.000010(日志名，即Log_name)';

4. 显示第一个二进制日志

show binlog events;
+------------------+-----+-------------+-----------+-------------+---------------------------------------+

| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                  |

+------------------+-----+-------------+-----------+-------------+---------------------------------------+

| mysql-bin.000010 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.18-log, Binlogver: 4 |

| mysql-bin.000010 | 107 |Stop        |         1 |         126 |                                       |

+------------------+-----+-------------+-----------+-------------+---------------------------------------+

1. 删除指定日期之前的bin log

PURGE MASTER LOGS BEFORE '2011-12-05 13:00:00';

2. 清除N天前的 binlog
   BEFORE变量的date自变量可以为’YYYY-MM-DDhh:mm:ss’格式。MASTER和BINARY是同义词。

PURGE MASTER LOGS BEFOREDATE_SUB( NOW( ), INTERVAL N DAY);

3. 清除 7 天前的binlog

PURGE MASTER LOGS BEFOREDATE_SUB( NOW( ), INTERVAL 7 DAY);

4. 在contab设置定期每天凌晨3点删除7天前的binlog

0 3 * * *  `mysql -uroot -e 'PURGE BINARY LOGS BEFOREDATE_SUB( NOW( ), INTERVAL 7 DAY);'`

5. 刷新当前日志列表

mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

10. 通过设置binlog过期的时间，使系统自动删除binlog文件

mysql> set global expire_logs_days = 2;        #设置binlog多少天过期
mysql> show variables like 'expire_logs_days';  #设置 binlog 2 天过期
+------------------+-------+ 
| Variable_name  | Value | 
+------------------+-------+ 
| expire_logs_days |   2  | 
+------------------+-------+