MySQL 8.0 binlog 过期设置

mysql8中 使用binlog_expire_logs_seconds 设置日志过期时间，以前的参数expire_logs_days废弃不用了。

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.22    |
+-----------+
1 row in set (0.01 sec)
mysql> show variables like '%expire%';
+--------------------------------+---------+
| Variable_name                  | Value   |
+--------------------------------+---------+
| binlog_expire_logs_seconds     | 2592000 |
| disconnect_on_expired_password | ON      |
| expire_logs_days               | 0       |
+--------------------------------+---------+
 
默认情况下binglog的失效日期为30天，设置为0则表示永不失效。

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的有效期时间为6天：


查看binlog：
mysql> show binary logs;
+--------------------+------------+-----------+
| Log_name           | File_size  | Encrypted |
+--------------------+------------+-----------+
| mysql80-bin.000152 |     211878 | No        |
| mysql80-bin.000153 |        179 | No        |
| 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 |  800765144 | No        |
+--------------------+------------+-----------+
12 rows in set (0.00 sec)

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

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

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)

清理日志：
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)

mysql> purge binary logs before '2021-22-20 22:00:00';
Query OK, 0 rows affected, 1 warning (0.01 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)

说明：
在MySQL 8.0之前 使用的参数expire_logs_days 只能设置到天，在新版本可以精确到秒 。

 

https://blog.csdn.net/mshxuyi/article/details/94901212