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

、MybinSQL日誌

1、mysql  -u root -p   #進入從伺服器mysql控制台
show slave statusG;   #檢查從伺服器正在讀取哪個日誌，有多個從伺服器，選擇時間最早的一個做為目標日誌。
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  #儲存退出

擴充閱讀：

mysql> help purge;

Name: 'PURGE BINARY LOGS'

Description:

Syntax:DivUR 🜋 }

time_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, fileplus an index 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 ified listed

in the log index file prior to ified fileorlog 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 becomesat inhio orwhii state list 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';網友一下

MYSQL主從複製(replication)採用RBR 模式後，binlog的格式為"ROW"，能解決許多原先出現的主鍵重複問題。

在一個繁忙的master db 在server上，binlog日誌檔案成長速度很快，如果不定時清除，硬碟空間很快就會被填滿。

設定自動清理mysql binlog日誌，配置my.cnf：

expire_logs_days = 10

在運行時修改：

show binary logs;

show variables like '%log%';

​​set globald expire_logs_days = 10;

清除前可以採用對應的備份策略。

手動刪除10天前的MySQL binlog日誌：

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

show master logs;

MASTER和BINARY是同義詞。

一般情況下，建議使用MIXED binlog的複製。 http://dev.mysql.com/doc/refman/5.1/en/open-bugs-general.html中的說明:Replication uses query-level logging: The master writes the executed queries to the binary log. This is a very fast, compact, and efficient logging method that works perfectly in most cases.

附：關於MYSQL複製的幾個模式

從 MySQL 5.1.12 開始，可以用以下三種模式來實現：

– 基於SQL語句的複製(statement-based replication, SBR)，

– 基於行的複製(row-based replication, RBR)，

– 混合模式複製(mixed-based replication, MBR)。

相應地，binlog的格式也有三種：STATEMENT，ROW，MIXED。 MBR 模式中，SBR 模式是預設的。

在運行時可以動態改動 binlog的格式，除了以下幾種情況：

. 儲存流程或觸發器中間

. 啟用了NDB

. 目前會話試用 RBR 模式，並且已開啟了臨時表

如果binlog採用了 MIXED 模式，那麼在下列幾種情況下會自動將binlog的模式由 SBR 模式改為 RBR 模式。

. 當DML語句更新一個NDB表時

. 當函數中包含 UUID() 時
. 2個以上包含 AUTO_INCREMENT 字段的表被更新時
. 行任何 INSERT DELAYED 語句時
. 用 UDF 時
. 視圖中必須要求運用 RBR 時，例如建立視圖是運用了 UUID() 函數

設定主從複製模式：
log-bin=mysql-bin
#binlog_format="STATEMENT"
#binlog_format="ROW"
binlog_format="MIXED"

也可以在運行時動態修改binlog的格式。例如
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

兩種模式各自的優缺點：

SBR 的優點：
歷史悠久，技能成熟
binlog文件較小
binlog中包含了所有數據庫修改信息，可以據此來審核數據庫的安全等情況
binlog可以用於實時的還原，而不僅僅用於復制
主從版本可以不一樣，從伺服器版本可以比主伺服器版本高
SBR 的缺點：
不是所有的UPDATE語句都能被複製，尤其是包含不確定操作的時候。
調用具有不確定因素的 UDF 時複製也可能出疑問
運用下列函數的語句也不能被複製：
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非啟動時啟用了 –sysdate-is-now 選項)
INSERT … SELECT 會產生比 RBR 更多的行級鎖定
複製須要執行 全表掃描(WHERE 語句中沒有運用到索引)的 UPDATE 時，須要比 RBR 請求更多的行級鎖
對於有 AUTO_INCREMENT 欄位的 InnoDB表而言，INSERT 語句會阻塞其他 INSERT 語句
對於一些複雜的語句，在從伺服器上的耗資源情況會更嚴重，而 RBR 模式下，只會對那個發生變化的記錄產生影響
存儲函數(不是存儲流程 )在被呼叫的同時也會執行一次 NOW() 函數，這個可以說是壞事也可能是好事
確定了的 UDF 也須要在從伺服器上執行
資料表必須幾乎和主伺服器保持一致才行，否則可能會導致複製出錯
執行複雜語句如果出錯的話，會消耗更多資源

RBR 的優點：
任何情況都可以被複製，這對複製來說是最安全可靠的
和其他大多數資料庫系統的複製技能一樣
多數情況下，從伺服器上的表如果有主鍵的話，複製就會快了很多
複製以下幾個語句時的行鎖更少：
* INSERT … SELECT
* 包含 AUTO_INCREMENT 欄位的 INSERT
* 沒有附帶條件或並沒有修改很多記錄的 UPDATE 或 DELETE 語句
執行 INSERT，UPDATE，DELETE 語句時鎖更少
從伺服器上採用多執行緒來執行複製成為可能
RBR 的缺點：
binlog 大了很多
複雜的回滾時 binlog 中會包含大量的資料
主伺服器上執行 UPDATE 語句時，所有發生變化的記錄都會寫到 binlog 中，而 SBR 只會寫一次，這會導致頻繁發生 binlog 的並發寫疑問
UDF 產生的大 BLOB 值會導致複製變慢
不能從 binlog 中看到都複製了寫什麼語句(加密過的)
當在非事務表上執行一段堆積的SQL語句時，最好採用 SBR 模式，否則很容易導致主從伺服器的資料不一致情況發生
另外，針對系統庫 mysql 裡面的表格發生變化時的處理準則如下：
如果是採用 INSERT，UPDATE，DELETE 直接操作表的情況，則日誌格式根據 binlog_format 的設定而記錄
如果是採用 GRANT，REVOKE，SET PASSWORD 等管理語句來做的話，那麼無論如何 都採用 SBR 模式記錄。
註：採用 RBR 模式後，能處理許多原先出現的主鍵重複問題。實例:
對於insert into db_allot_ids select * from db_allot_ids 這個語句:
在BINLOG_FORMAT=STATEMENT 模式下:
BINLOG日誌訊息為:
—————————————–
BEGIN
/*!*/;
# at 173
#090612 16:05:42 server id 1 end_log_pos 288 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1244793942/*!*/;
insert into db_allot_ids select * from db_allot_ids
/*!*/;
—————————————–

在BINLOG_FORMAT=ROW 模式下:
BINLOG日誌訊息為:
———————— —————–
BINLOG '
hA0yShMBAAAAMwAAAOAAAAAAAA8AAAAAAAAAA1NOUwAMZGJfYWxsb3RfaWRzAAIBAwAA
hA0yShcBAAAANQAAABUBAAAQAA8AAAAAAAEAAv/8AQEAAAD8AQEAAAAQD8M8 -F88––D8F867'——YFAQEAAAAQD8M—

清理日誌步驟
1.查找日誌檔案
mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| ablelee.000001 | 150462942 |
| ablelee.000002 |       125 |
| ablelee.000003 |       106 |
+----------------+-----------+
2.刪除bin-log(刪除ablelee.000003之前的而沒有包含ablelee .000003)
mysql> purge binary logs to 'ablelee.000003';
Query OK, 0 rows affected (0.16 sec)

3.  查詢結果(現在只有一筆記錄了.)

mysql> show binlog eventsG
************************** * 1. row ***************************
   Log_name: ablelee.000003
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 106
       Info: Server ver: 5.1.26-rc-log, Binlog ver: 4
1 row in set (0.01 sec)
(ablelee.000001和ablelee.000002已刪除)
mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| ablelee.000003 |       106 |
+----------------+-----------+
1 row in set (0.00 sec)
 
(刪除的其它格式運用!)
　  PURGE {MASTER | BINARY} LOGS TO 'log_name'
　　PURGE {MASTER | BINARY} LOGS BEFORE 'date'
　　用於刪除列於在指定的日誌或日期之前的日誌索引中的所有二進位日誌。這些日誌也會從記錄在日誌索引檔案中的清單中刪除，這樣被給定的日誌成為第一個。
　　例如：
　　PURGE MASTER LOGS TO 'mysql-bin.010';
　　PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00';
    清除3天前的 binlog
    PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);
　　BEFORE變數的date自變數可以為'YYYY-MM-DD hh:mm:ss'格式。 MASTER和BINARY是同義詞。
　　如果您有一個活性的從屬伺服器，該伺服器目前正在讀取您正在試圖刪除的日誌之一，則本語句不會起作用，而是會失敗，並伴隨一個錯誤。不過，如果從屬伺服器是休止的，並且您碰巧清理了其想要讀取的日誌之一，則從屬伺服器啟動後不能複製。當從屬伺服器正在複製時，本語句可以安全地運作。您不需要停止它們。
　　要清理日誌，需依照以下步驟：
　　1. 在每個從屬伺服器上，使用SHOW SLAVE STATUS來檢查它正在讀取哪個日誌。
　　2. 使用SHOW MASTER LOGS獲得主伺服器上的一系列日誌。
　　3. 在所有的從屬伺服器中判定最早的日誌。這個是目標日誌。如果所有的從屬伺服器是更新的，這是清單上的最後一個日誌。

　　4. 製作您將要刪除的所有日誌的備份。 （這個步驟是自選的，但是建議採用。）

　　5. 清理所有的日誌，但是不包括目標日誌

以上就是MySQL 自動清理日誌日誌的方法的內容， 更多相關請關注PHP中文網（www.php.cn）！