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 只能设置到天,在新版本可以精确到秒 。