MySQL日志保留策略：设置binlog日志保存天数、文件大小限制
一、查看当前binlog保留时长，文件大小限制
## 查看binlog的保留时长[expire_logs_days]
## 0表示永久保留
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 |
+------------------+-------+
1 row in set (0.00 sec)
## 查看binlog的文件大小限制[max_binlog_size]
## 1073741824
mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.01 sec)
二、修改保留时长
临时生效：
这种方法调整后，立即生效，但是数据库重启后，会失效
## 设置日志保留时长为30天
mysql> set global expire_logs_days=30;
Query OK, 0 rows affected (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 30 |
+------------------+-------+
1 row in set (0.00 sec)
mysql>
永久生效：
修改my.cnf文件，永久生效，数据库启动的适合会自动加载该文件
## 打开my.cnf配置文件
[root@zxy_slave1 mysql]# vim /etc/my.cnf
## [mysqld]模块下添加：expire_logs_days
[mysqld]
expire_logs_days = 30
三、设置文件大小限制
临时生效：
这种方法调整后，立即生效，但是数据库重启后，会失效
## 将max_binlog_size设置为500M
mysql> set global max_binlog_size = 524288000;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'max_binlog_size';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| max_binlog_size | 524288000 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)
永久生效
修改my.cnf文件，永久生效，数据库启动的适合会自动加载该文件
## 打开my.cnf配置文件
[root@zxy_slave1 mysql]# vim /etc/my.cnf
## [mysqld]模块下添加：max_binlog_size
[mysqld]
expire_logs_days = 30
max_binlog_size = 500M
四、手动清理binlog
按照binlog名称删除
## 将mysql-bin.000010之前的日志清理掉
mysql> purge binary logs to 'mysql-bin.000010';
Query OK, 0 rows affected (0.01 sec)
## 清理前
[root@zxy_slave1 mysql]# ls
mysql-bin.000007 mysql-bin.000012 mysql-bin.000017 mysql-bin.000008 mysql-bin.000013 mysql-bin.index mysql-bin.000009 mysql-bin.000014 mysql-bin.000005 mysql-bin.000010 mysql-bin.000015 mysql-bin.000006 mysql-bin.000011 mysql-bin.000016
## 清理后
[root@zxy_slave1 mysql]# ls
mysql-bin.000012 mysql-bin.000017 mysql-bin.000013 mysql-bin.index mysql-bin.000014 mysql-bin.000010 mysql-bin.000015 mysql-bin.000011 mysql-bin.000016
[root@zxy_slave1 mysql]#
按照时间删除
## 删除2022-04-21 18:08:00之前的binlog日志
mysql> purge binary logs before '2022-04-21 18:08:00';
Query OK, 0 rows affected, 1 warning (0.02 sec)
## 删除前
[root@zxy_slave1 mysql]# vim mysql-bin.
mysql-bin.000010 mysql-bin.000012 mysql-bin.000014 mysql-bin.000016 mysql-bin.index
mysql-bin.000011 mysql-bin.000013 mysql-bin.000015 mysql-bin.000017
[root@zxy_slave1 mysql]# ls
## 删除后
[root@zxy_slave1 mysql]# ls
mysql-bin.000017 mysql-bin.index
