mysql自動清理MySQL binlog日誌的例子

說明：

開啟MySQL binlog日誌的伺服器，如果不設定自動清理日誌，預設binlog日誌一直保留著，時間一長，伺服器磁碟空間被binlog日誌佔滿，導致MySQL資料庫出錯。

使用下面方法可以安全清理binlog日誌

一、沒有主從同步的情況下清理日誌

mysql -uroot -p123456 -e 'PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ),INTERVAL 5 DAY)';

#mysql 定時清理5天前的binlog

mysql -u root -p  #進入mysql 控制台

reset master;  #重設binlog

二、MySQL主從同步下安全清理binlog日誌

1、mysql  -u root -p   #進入從伺服器mysql控制台

show slave status\G;   #檢查從伺服器正在讀取哪個日誌，有多個從伺服器，選擇時間最早的一個做為目標日誌。

2、進入主伺服器mysql控制台

show master log;   #獲得主伺服器上的一系列日誌

PURGE MASTER LOGS TO 'binlog.000058';   #刪除binlog.000005之前的，不包括binlog.000058

PURGE MASTER LOGS BEFORE '2016-06-22 13:00:00';  #清除2016-06-22 13:00:00前binlog日誌

PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);  #清除3天前binlog日誌

三、設定自動清理MySQL binlog日誌

vi  /etc/my.cnf  #編輯配置

expire_logs_days = 15  #自動刪除15天前的日誌。預設值為0，表示從不刪除。

log-bin=mysql-bin   #注釋掉之後，會關閉binlog日誌

binlog_format=mixed   #注釋掉之後，會關閉binlog日誌

:wq!  #儲存退出

擴充閱讀：

mysql> help purge;

Name: 'PURGE BINARY LOGS'

Description:

Syntax:

PURGE { BINARY | MASTER } LOGS

{ TO 'log_name' | BEFORE datetime_expr }

The binary log is a set of files that contain information about data

modifications made by the MySQL server. The log consists of a set of

binary log files, plus an index file (see

http://dev.mysql.com/doc/refman/5.5/en/binary-log.html).

The PURGE BINARY LOGS statement deletes all the binary log files listed

in the log index file prior to the specified log file name or date.

BINARY and MASTER are synonyms. Deleted log files also are removed from

the list recorded in the index file, so that the given log file becomes

the first in the list.

This statement has no effect if the server was not started with the

--log-bin option to enable binary logging.

URL: http://dev.mysql.com/doc/refman/5.5/en/purge-binary-logs.html

Examples:

PURGE BINARY LOGS TO 'mysql-bin.010';

PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';