MySQL8.0二进制日志自动清除设置

1、登录MySQL

[root@MongoDB ~]# mysql -uroot -p

Enter password:xxxxxxx

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 39

Server version: 8.0.31 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> \s

--------------

mysql Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id: 39

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 8.0.31 MySQL Community Server - GPL

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: utf8mb4

Db characterset: utf8mb4

Client characterset: utf8mb4

Conn. characterset: utf8mb4

UNIX socket: /tmp/mysql.sock

Binary data as: Hexadecimal

Uptime: 1 day 37 min 33 sec

Threads: 2 Questions: 7007 Slow queries: 20 Opens: 939 Flush tables: 3 Open tables: 766 Queries per second avg: 0.079

--------------

2、查看binlog的参数

mysql> show variables like 'log%bin%';

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

| Variable_name | Value |

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

| log_bin | ON |

| log_bin_basename | /data/mysqldata/mysql-bin |

| log_bin_index | /data/mysqldata/mysql-bin.index |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| log_statements_unsafe_for_binlog | ON |

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

6 rows in set (0.00 sec)

3、查看binlog日志格式

mysql> show variables like 'binlog_format';

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

| Variable_name | Value |

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

| binlog_format | MIXED |

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

1 row in set (0.00 sec)

4、查看binlog过期设置

mysql> show variables like '%expire%';

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

| Variable_name | Value |

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

| binlog_expire_logs_auto_purge | ON |

| binlog_expire_logs_seconds | 0 |

| disconnect_on_expired_password | ON |

| expire_logs_days | 7 |

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

4 rows in set (0.00 sec)

mysql>

注释：

在MySQL8.0版本中新增参数binlog_expire_logs_seconds，可以精确到秒，而此前版本中expire_logs_days的单位为天，最小值为1；

在MySQL8.0中此expire_logs_days参数标记为deprecated 。

在MySQL8.0版本默认使用binlog_expire_logs_seconds，时间为2592000秒，30天。

若在MySQL启动时binlog_expire_logs_seconds和expire_logs_days都没设置值，则使用默认值的binlog_expire_logs_seconds值，即30天；

若在MySQL启动时binlog_expire_logs_seconds或expire_logs_days其中一个设置为非0值则非0值的参数作为binlog日志失效期；

若在MySQL启动时binlog_expire_logs_seconds和expire_logs_days参数都设置为非0值则使用binlog_expire_logs_seconds值，expire_logs_days值则失效并对其发出告警信息。

若要关闭自动清除binlog文件的功能则需要显式指定binlog_expire_logs_seconds=0，并且不设置expire_logs_days的值。

为了兼容早期版本若显式指定了expire_logs_days=0而没有指定binlog_expire_logs_seconds的值，此时自动清理binlog日志则是禁用的，并且此时binlog_expire_logs_seconds的默认值不适用。

5、该参数支持动态修改，并且支持持久化到配置文件：

mysql> set global expire_logs_days=0;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>

mysql> set global binlog_expire_logs_seconds=60*60*24*7;

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> select 604800/60/60/24;

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

| 604800/60/60/24 |

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

| 7.000000000000 |

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

1 row in set (0.00 sec)

mysql>

mysql> show variables like '%expire%';

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

| Variable_name | Value |

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

| binlog_expire_logs_auto_purge | ON |

| binlog_expire_logs_seconds | 604800 |

| disconnect_on_expired_password | ON |

| expire_logs_days | 0 |

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

4 rows in set (0.01 sec)

mysql>

--持久化：

mysql> set persist binlog_expire_logs_seconds=60*60*24;

Query OK, 0 rows affected (0.00 sec)

结论：

在MySQL8.0版本对于binlog需要自定义设置的比较好，可以指定binlog文件的存储路径和binlog文件的命名，

设置binlog文件的保留的时间，需要注意防止默认设置产生较多的binlog文件占用磁盘空间。

推荐使用参数binlog_expire_logs_seconds，设置为7天,即binlog_expire_logs_seconds=604800