MySQL 手动清除binlog

来源：互联网发布：wps word表格数据计算编辑：程序博客网时间：2024/06/05 04:19

新接手的四套库MySQL库，采用主从结构。之前配置的伙计备库的bin log 日志全部是打开状态。运维人员反映，空间告警几次。均手动上去处理。

备库开启了bin log 也影响备库的性能，虽然这四套MySQL库的压力不大，但是本着优化的态度，还是对这四套库做一次彻底的清楚。

1. 查看备库当前的bin log：

show binary logs;

直接刷出了七百多个bin - log。从上线之初到现在一年时间，只有1T的bin log

2. 查看备库的slave 运行状态

show slave status\G

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.180.213.58
Master_User: rep581
Master_Port: 3301
Connect_Retry: 60
Master_Log_File: mysql-bin.000266
Read_Master_Log_Pos: 429863077
Relay_Log_File: mysql-relay-bin.000780
Relay_Log_Pos: 429863287
Relay_Master_Log_File: mysql-bin.000266
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 429863077
Relay_Log_Space: 429863578
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 581
Master_UUID: d4d049b3-7151-11e5-972f-000af77f26d8
Master_Info_File: /mysqldata/mysql2/dat/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: d4d049b3-7151-11e5-972f-000af77f26d8:1-395318788
Executed_Gtid_Set: d41f98d3-7151-11e5-972f-000af77f26d8:1-471129318,
d4d049b3-7151-11e5-972f-000af77f26d8:1-395318788
Auto_Position: 1
1 row in set (0.00 sec)

从上面四个指标可以看出备库运行正常，且没有任何延迟。因此可以开始手动清理

3. 手动清理，清理原则：手动清理五天以前的bin log

PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 5 DAY);

持续一段时间以后，binglog手动清理完毕。

4. 关闭slave备库bin log。注销备库my.cnf一下参数：

#log-bin = /mysqldata/mysql2/bin/mysql-bin
#sync_binlog = 1
#binlog_cache_size = 4M
#max_binlog_cache_size = 8M
#max_binlog_size = 1024M

最后需要重启该库才能够生效！

0 0