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