MySQL Binlog日志保留时长配置 & 删除方法

一. 背景

今天运维同学通知大数据测试环境一台mysql服务器, 关于mysql binlog文件存储异常的大.
通过如下命令:
show master logs;
可以看到, binlog 主服务器上的同步日志 “master.00数字” 文件达到了307个之多, 且每个文件大小约为1.1G, 占用磁盘空间达到300多G, 任由这种情况发展下去, 细思极恐啊.

MySQL master binlog 查询结果如下:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1266690
Server version: 5.7.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master logs;
+---------------+------------+
| Log_name      | File_size  |
+---------------+------------+
| master.000001 | 1194127507 |
| master.000002 | 1183878743 |
| master.000003 | 2233177654 |
| master.000004 | 1074432717 |
| master.000005 | 1074995519 |
| master.000006 |  675126155 |
| master.000007 | 1074592348 |
| master.000008 | 1076686190 |
| master.000009 | 1075531352 |
| master.000010 | 1076749773 |
| master.000011 | 1074219420 |
...
| master.000305 | 1075076094 |
| master.000306 | 1073741903 |
| master.000307 |   13675149 |
+---------------+------------+

二. 产生原因

想起早前在做maxwell消费binlog实验的时候, 使用了这台测试mysql服务器做master, 其他测试服务器上的maxwell进程则作为slave.

为支持本次测试, /etc/my.cnf的配置参数当时新增了如下几个:

###  Desc: binlog function support; Author:David; Date:20201120

#mysqld模块
[mysqld]
server_id=1
log-bin=master
binlog_format=row

但是要命的是, 当时忘记配置 binlog 日志的保留时间参数了:
expire_logs_days=7
导致从20201120 至 20210920, 在约11个月的时间里 master binlog文件增长到307个之多.

三. 配置MySQL Binlog日志保留时长

3.1 <临时有效>方式 - 命令行

登录到mysql数据库中, 使用如下命令行方式执行命令行:
临时有效,重启mysqld服务后失效；
.
show variables like "%expire_logs_days%";
set expire_logs_days=7;
.
这种设置, 在重启mysqld服务后就失效了.

3.2 <永久有效>方式 - 配置文件

方式 ①:
在/etc/my.cnf文件中的[mysqld]模块下, 新增如下参数:
expire_logs_days=7
这样设置将永久有效，但需重启mysqld服务才会生效.
说明：
0-表示永不过期, 单位为天。
.
重启后, 再手工将(/data/mysql/data)目录下的N天前的"master.000数字" 文件删除.

方式 ②:
登录到mysql数据库中,执行:
set global expire_logs_days=7;
这样设置后, 后续的文件将按该策略进行滚动删除;
但如果删除之前的文件(如删除7天前的master binlog 文件), 还需执行如下操作:
mysql> PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY);

四. MySQL Binlog日志自动清理 & 手动删除方法

说明：
当开启mysql数据库主从时，会产生大量如mysql-bin.00000* log的文件，这会大量耗费您的硬盘空间。
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005
…
有三种解决方法：

• 1.关闭mysql主从，关闭binlog；
• 2.开启mysql主从，设置expire_logs_days；
• 3.手动清除binlog文件，> PURGE MASTER LOGS TO ‘MySQL-bin.010′;

实现：
1.关闭mysql主从，关闭binlog

# vim /etc/my.cnf  //注释掉log-bin,binlog_format
# Replication Master Server (default)
# binary logging is required for replication
# log-bin=mysql-bin
# binary logging format - mixed recommended
# binlog_format=mixed

然后重启数据库

2.重启mysql,开启mysql主从，设置expire_logs_days

# vim /etc/my.cnf  //修改expire_logs_days,x是自动删除的天数，一般将x设置为短点，如10

expire_logs_days = x //二进制日志自动删除的天数。默认值为0,表示“没有自动删除”
此方法需要重启mysql，附录有关于expire_logs_days的英文说明

当然也可以不重启mysql,开启mysql主从，直接在mysql里设置expire_logs_days

show binary logs;
show variables like ‘%log%’;
set global expire_logs_days = 10;

3.手动清除binlog文件

# /usr/local/mysql/bin/mysql -u root -p

PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY); //删除10天前的MySQL binlog日志,附录2有关于PURGE MASTER LOGS手动删除用法及示例
show master logs;

也可以重置master，删除所有binlog文件：

# /usr/local/mysql/bin/mysql -u root -p

reset master; //附录3有清除binlog时，对从mysql的影响说明

附录：
1.expire_logs_days英文说明
Where X is the number of days you’d like to keep them around. I would recommend 10, but this depends on how busy your MySQL server is and how fast these log files grow. Just make sure it is longer than the slowest slave takes to replicate the data from your master.
Just a side note: You know that you should do this anyway, but make sure you back up your mysql database. The binary log can be used to recover the database in certain situations; so having a backup ensures that if your database server does crash, you will be able to recover the data.

2.PURGE MASTER LOGS手动删除用法及示例,MASTER和BINARY是同义词

PURGE {MASTER | BINARY} LOGS TO ‘log_name’
PURGE {MASTER | BINARY} LOGS BEFORE ‘date’
删除指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除MySQL BIN-LOG 日志，这样被给定的日志成为第一个。

实例：

PURGE MASTER LOGS TO ‘MySQL-bin.010’; //清除MySQL-bin.010日志
PURGE MASTER LOGS BEFORE ‘2008-06-22 13:00:00’; //清除2008-06-22 13:00:00前binlog日志
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY); //清除3天前binlog日志BEFORE，变量的date自变量可以为’YYYY-MM-DD hh:mm:ss’格式。

3.清除binlog时，对从mysql的影响
如果您有一个活性的从属服务器，该服务器当前正在读取您正在试图删除的日志之一，则本语句不会起作用，而是会失败，并伴随一个错误。不过，如果从属服务器是休止的，并且您碰巧清理了其想要读取的日志之一，则从属服务器启动后不能复制。当从属服务器正在复制时，本语句可以安全运行。您不需要停止它们。