1、登录数据库:
(base) qiang@09230:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8853
Server version: 8.0.28-0ubuntu0.20.04.3 (Ubuntu)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
mysql>
2、查看数据库二进制日志
mysql> show binary logs;
+---------------+------------+-----------+
| Log_name | File_size | Encrypted |
+---------------+------------+-----------+
| binlog.000438 | 1278477710 | No |
| binlog.000439 | 1713127885 | No |
| binlog.000440 | 1732575075 | No |
| binlog.000441 | 1681076552 | No |
| binlog.000442 | 1626798483 | No |
| binlog.000443 | 1626776947 | No |
| binlog.000444 | 1312552517 | No |
| binlog.000445 | 1278477256 | No |
| binlog.000446 | 1713127885 | No |
| binlog.000447 | 1732575075 | No |
| binlog.000448 | 1681076552 | No |
| binlog.000449 | 1626798483 | No |
| binlog.000450 | 1626776947 | No |
| binlog.000451 | 1312552517 | No |
| binlog.000452 | 209588709 | No |
| binlog.000453 | 157 | No |
+---------------+------------+-----------+
16 rows in set (0.01 sec)
3、删除二进制日志
3.1、删除指定日期前的日志文件：PURGE MASTER LOGS BEFORE '指定日期';
mysql> PURGE MASTER LOGS BEFORE '2022-06-08 00:00:00';
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+---------------+------------+-----------+
| Log_name | File_size | Encrypted |
+---------------+------------+-----------+
| binlog.000445 | 1278477256 | No |
| binlog.000446 | 1713127885 | No |
| binlog.000447 | 1732575075 | No |
| binlog.000448 | 1681076552 | No |
| binlog.000449 | 1626798483 | No |
| binlog.000450 | 1626776947 | No |
| binlog.000451 | 1312552517 | No |
| binlog.000452 | 209588709 | No |
| binlog.000453 | 157 | No |
+---------------+------------+-----------+
9 rows in set (0.00 sec)
或
mysql>PURGE MASTER LOGS TO 'mysql-bin.000003';
3.2、删除所有二进制日志文件
mysql> RESET MASTER;
Query OK, 0 rows affected (0.11 sec)
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 157 | No |
+---------------+-----------+-----------+
1 row in set (0.00 sec)
mysql>
4、设置定时自动清理
mysql> show variables like 'log_%';
+----------------------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------------------+----------------------------------------+
| log_bin | ON |
| log_bin_basename | /qiang/mysql/mysql/binlog |
| log_bin_index | /qiang/mysql/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | /var/log/mysql/error.log |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_raw | OFF |
| log_replica_updates | ON |
| log_slave_updates | ON |
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_replica_statements | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
+----------------------------------------+----------------------------------------+
21 rows in set (0.01 sec)
mysql> show variables like '%expire%';
+--------------------------------+--------+
| Variable_name | Value |
+--------------------------------+--------+
| binlog_expire_logs_seconds | 432000 |
| disconnect_on_expired_password | ON |
| expire_logs_days | 0 |
+--------------------------------+--------+
3 rows in set (0.00 sec)
mysql> set global binlog_expire_logs_seconds=60*60*24*3;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%expire%';
+--------------------------------+--------+
| Variable_name | Value |
+--------------------------------+--------+
| binlog_expire_logs_seconds | 259200 |
| disconnect_on_expired_password | ON |
| expire_logs_days | 0 |
+--------------------------------+--------+
3 rows in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.11 sec)
mysql>
