Mysql数据库二进制日志的管制
发布时间：2022-04-04 13:11:02 所属栏目：MySql教程 来源：互联网
导读：如果配置了log_bin参数，则需要配置expire_logs_days参数指定二进制日志保留时间，不然磁盘空间早晚会被日志占满的。如果之前没有配置，则配置步骤如下： 1、查看当前配置 (root@localhost)[(none)] show global variables like log_bin; +---------------
 如果配置了log_bin参数，则需要配置expire_logs_days参数指定二进制日志保留时间，不然磁盘空间早晚会被日志占满的。如果之前没有配置，则配置步骤如下： 1、查看当前配置 (root@localhost)[(none)] show global variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ 1 row in set (0.02 sec) (root@localhost)[(none)] show global variables like 'expire_logs_days'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | expire_logs_days | 0 | +------------------+-------+ 1 row in set (0.00 sec) (root@localhost)[(none)] show global variables like 'max_binlog_size'; +-----------------+------------+ | Variable_name | Value | +-----------------+------------+ | max_binlog_size | 1073741824 | +-----------------+------------+ 1 row in set (0.00 sec) (root@localhost)[(none)] show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 614 | | mysql-bin.000002 | 1595 | | mysql-bin.000003 | 445747850 | +------------------+-----------+ 3 rows in set (0.02 sec) [root@dbrac16 ~]# ll /usr/local/mysql/data/mysql-bin* -rw-r----- 1 mysql mysql 614 May 11 10:06 /usr/local/mysql/data/mysql-bin.000001 -rw-r----- 1 mysql mysql 1595 May 17 09:45 /usr/local/mysql/data/mysql-bin.000002 -rw-r----- 1 mysql mysql 445747850 May 19 14:28 /usr/local/mysql/data/mysql-bin.000003 -rw-r----- 1 mysql mysql 117 May 17 11:16 /usr/local/mysql/data/mysql-bin.index 2、查看slave状态 [root@dbrac17 ~]# mysql -uroot -p123456 -e "show slave statusG" | grep Master_Log_File mysql: [Warning] Using a password on the command line interface can be insecure. Master_Log_File: mysql-bin.000003 Relay_Master_Log_File: mysql-bin.000003 3、手动清除过期的日志 如果堆积的日志太多，可以先执行这一步，清理掉一部分日志。 (root@localhost)[(none)] purge master logs before '2017-05-17 00:00:00'; Query OK, 0 rows affected (0.15 sec) (root@localhost)[(none)] show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000002 | 1595 | | mysql-bin.000003 | 445747850 | +------------------+-----------+ 2 rows in set (0.00 sec) 4、配置参数 [root@dbrac16 ~]# cat /etc/my.cnf | grep expire_logs_days expire_logs_days = 3 修改参数文件，设置日志保留3天，需要重启才能生效。 直接设置，立即生效。 (root@localhost)[(none)] set global expire_logs_days=3; Query OK, 0 rows affected (0.00 sec) 触发过期日志清除操作。 (root@localhost)[(none)] flush logs; Query OK, 0 rows affected (0.04 sec) (root@localhost)[(none)] show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000003 | 445747897 | | mysql-bin.000004 | 201 | +------------------+-----------+ 3 rows in set (0.00 sec) （编辑：PHP编程网 - 黄冈站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330482号