新接手的四套库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
最后需要重启该库才能够生效!