Mysql binlog 过期设置

连接数据库

mysql -uroot -p

查看binlog过期参数设置

mysql> show variables like '%expire%';

+--------------------------------+---------+
| Variable_name                  | Value   |
+--------------------------------+---------+
| binlog_expire_logs_seconds     | 2592000 |
| disconnect_on_expired_password | ON      |
| expire_logs_days               | 0       |
+--------------------------------+---------+

默认情况下binlog的失效日期为30天（秒表示），设置为0则表示永不失效。

修改过期时间

使用binlog_expire_logs_seconds 设置日志过期时间

例：设置三天过期

mysql> set global binlog_expire_logs_seconds=3*24*60*60;
Query OK, 0 rows affected (0.00 sec)

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

备注：以前的参数expire_logs_days废弃不用了(如果使用expire_logs_days 设置过期时间，报错如下：)

mysql> set global expire_logs_days=7;
ERROR 3683 (HY000): The option expire_logs_days and binlog_expire_logs_seconds cannot be used together. 
Please use binlog_expire_logs_seconds to set the expire time (expire_logs_days is deprecated)

清理已存在的binlog日志

查看已存在的日志

mysql> show binary logs;
+--------------------+------------+-----------+
| Log_name           | File_size  | Encrypted |
+--------------------+------------+-----------+
| mysql80-bin.000154 |        179 | No        |
| mysql80-bin.000155 |        179 | No        |
| mysql80-bin.000156 |        179 | No        |
| mysql80-bin.000157 | 1073744601 | No        |
| mysql80-bin.000158 | 1073744653 | No        |
| mysql80-bin.000159 |  725690927 | No        |
| mysql80-bin.000160 | 1073743790 | No        |
| mysql80-bin.000161 | 1073744653 | No        |
| mysql80-bin.000162 | 1073745260 | No        |
| mysql80-bin.000163 |  800765193 | No        |
| mysql80-bin.000164 |        205 | No        |
| mysql80-bin.000165 |        156 | No        |
+--------------------+------------+-----------+
12 rows in set (0.00 sec)

删除mysql80-bin.000164之前的所有日志

mysql> purge binary logs to 'mysql80-bin.000164';
Query OK, 0 rows affected (0.59 sec)
 
mysql> show binary logs;
+--------------------+-----------+-----------+
| Log_name           | File_size | Encrypted |
+--------------------+-----------+-----------+
| mysql80-bin.000164 |       205 | No        |
| mysql80-bin.000165 |       156 | No        |
+--------------------+-----------+-----------+
2 rows in set (0.00 sec)