爛泥：學習mysql的binlog配置

標籤：爛泥   學習   binlog   mysql   配置   啟用   

本文首發於爛泥行天下。

1、基礎知識

日誌是把資料庫的每一個變化都記載到一個專用的檔案裡，這種檔案就叫做記錄檔。mysql預設只開啟錯誤記錄檔，因為過多的日誌將會影響系統的處理效能。

在mysql5.0以前版本支援文字格式設定和二進位格式的日誌，但是在mysql5.0後版本就只支援二進位格式的日誌。因為二進位日誌在效能、資訊處理等方面有更多的優點。

2、啟用mysql二進位日誌

mysql二進位日誌由設定檔my.cnf的log-bin選項負責啟用的。

mysql伺服器預設會在資料根目錄建立兩個新檔案：XXX-bin.000001和XXX-bin.index。若配置選項沒有給出檔案名稱，mysql將使用mysql-bin來命名這兩個檔案，其中.index檔案包含一份完整的記錄檔清單。如下：

cat /etc/my.cnf

ll /usr/local/mysql/data/

cat /usr/local/mysql/data/mysql-bin.index

650) this.width=650;" title="clip_image001[4]" style="border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px;" border="0" alt="clip_image001[4]" src="http://img1.51cto.com/attachment/201411/7/526870_1415325450dHBW.png" width="351" height="148" />

650) this.width=650;" title="clip_image002[4]" style="border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px;" border="0" alt="clip_image002[4]" src="http://img1.51cto.com/attachment/201411/7/526870_1415325450GXgq.png" width="469" height="369" />

mysql會把使用者對所有資料庫的內容和結構的修改情況記入XXX-bin.n檔案，但是不會記錄SELECT和沒有實際更新的UPDATE語句。

當然我們也可以同過相關命令查詢，mysql是否已經開啟binlog日誌。如下：

show variables like ‘log_%‘;

650) this.width=650;" title="clip_image003[4]" style="border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px;" border="0" alt="clip_image003[4]" src="http://img1.51cto.com/attachment/201411/7/526870_1415325451HRTY.png" width="520" height="277" />

3、binlog記錄檔的產生

當mysql停止或重啟時，伺服器會把記錄檔記入下一個記錄檔，mysql會在重啟時產生一個新的記錄檔，檔案序號依次遞增。

如果記錄檔超過max_binlog_size（在my.cnf檔案中配置）系統變數配置的上限時，也會產生新的記錄檔。

除此之外，如果在mysql命令中執行flush logs命令也會產生新的記錄檔。

650) this.width=650;" title="clip_image004[4]" style="border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px;" border="0" alt="clip_image004[4]" src="http://img1.51cto.com/attachment/201411/7/526870_1415325451c8En.png" width="377" height="154" />

4、查看系統中的binlog日誌

查看系統中的binlog日誌，使用show master logs命令。如下：

show master logs;

650) this.width=650;" title="clip_image005[4]" style="border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px;" border="0" alt="clip_image005[4]" src="http://img1.51cto.com/attachment/201411/7/526870_1415325451gc0F.png" width="271" height="238" />

5、查看binlog記錄檔

mysql提供了mysqlbinlog命令來查看記錄檔，在記錄每條變更日誌的時候，記錄檔都會把目前時間給記錄下來，以便進行資料庫恢複。如下：

mysqlbinlog mysql-bin.000001| more

650) this.width=650;" title="clip_image006[4]" style="border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px;" border="0" alt="clip_image006[4]" src="http://img1.51cto.com/attachment/201411/7/526870_141532545280UJ.png" width="606" height="446" />

6、使用binlog日誌進行資料庫恢複

如果遇到災難事件，應該用最近一次製作的完整備份恢複資料庫，然後使用備份之後的binlog日誌件把資料庫恢複到最接近現在的可用狀態。

使用binlog日誌進行恢複時需要依次進行，即最早產生的記錄檔要最先恢複。恢複資料的命令格式如下：

mysqlbinlog xxx-bin.000001|mysql -u root –p password dataname

有關使用binlog進行資料庫的恢複，我會以後的文章進行詳細介紹。

7、binlog命令列參數詳解

log-bin [=file_name] 此參數表示啟用binlog日誌功能，並可以定製路徑名稱，預設為mysql-bin。

binlog_format 此參數配置binlog的日誌格式，預設為mixed。

max_binlog_size此參數配置binlog的日誌最大值，最大和預設值是1GB。

max_binlog_cache_size此參數表示binlog使用最大記憶體的數。

binlog-do-db=db_name 此參數表示只記錄指定資料庫的二進位日誌。

binlog-ignore-db=db_name此參數表示不記錄指定的資料庫的二進位日誌。

expire_logs_days 此參數表示binlog日誌保留的時間，預設單位是天。

my.cnf設定檔有關binlog配置如下：

cat /etc/my.cnf |grep -v ^#|grep -v ^$

650) this.width=650;" title="clip_image007[4]" style="border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px;" border="0" alt="clip_image007[4]" src="http://img1.51cto.com/attachment/201411/7/526870_1415325452a5Ha.png" width="401" height="405" />

8、刪除binlog日誌

8.1刪除部分binlog日誌

刪除binlog部分日誌，我們可以日誌名稱和日誌產生的時間來進行刪除。刪除命令如下：

purge {master|binary} logs to ‘log_name‘;

根據日誌名稱刪除，只刪除log_name之前的日誌，log_name本身不會被刪除。

650) this.width=650;" title="clip_image008[4]" style="border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px;" border="0" alt="clip_image008[4]" src="http://img1.51cto.com/attachment/201411/7/526870_1415325452q7rg.png" width="1010" height="352" />

通過我們可以看到，mysql-bin.000003之前的日誌已經被全部刪除。

purge {master|binary} logs before ‘date’;

根據日誌產生的時間刪除，只刪除date之前的日誌，date本身不會被刪除。

650) this.width=650;" title="clip_image009[4]" style="border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;padding-right:0px;border-top-width:0px;" border="0" alt="clip_image009[4]" src="http://img1.51cto.com/attachment/201411/7/526870_14153254536f6V.png" width="978" height="312" />

8.2刪除全部binlog日誌

刪除之前所有的binlog日誌，並重建新的binlog，尾碼從000001開始。使用命令：

reset master;

650) this.width=650;" title="clip_image010" style="border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;padding-right:0px;border-top-width:0px;" border="0" alt="clip_image010" src="http://img1.51cto.com/attachment/201411/7/526870_1415325453evcC.png" width="877" height="331" />

本文出自 “爛泥行天下” 部落格，請務必保留此出處http://ilanni.blog.51cto.com/526870/1573923

爛泥：學習mysql的binlog配置