加入收藏 | 设为首页 | 会员中心 | 我要投稿 PHP编程网 - 黄冈站长网 (http://www.0713zz.com/)- 数据应用、建站、人体识别、智能机器人、语音技术!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

Mysql数据库二进制日志的管制

发布时间:2022-04-04 13:11:02 所属栏目:MySql教程 来源:互联网
导读:如果配置了log_bin参数,则需要配置expire_logs_days参数指定二进制日志保留时间,不然磁盘空间早晚会被日志占满的。如果之前没有配置,则配置步骤如下: 1、查看当前配置 (root@localhost)[(none)] show global variables like log_bin; +---------------
Mysql数据库二进制日志的管制
        如果配置了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编程网 - 黄冈站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读