mysql ---------binlog

標籤：設定檔   伺服器   基礎知識   資料庫   二進位 mysql binlog   

1、基礎知識

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

2、啟用mysql二進位日誌

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

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

[[email protected] ~]# vim /etc/my.cnf

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/80/31/wKiom1c6tVrwDunWAAAZ82lL7ek348.png" style="float:none;" title="2.png" alt="wKiom1c6tVrwDunWAAAZ82lL7ek348.png" />

預設沒有開啟，我們將注釋開啟：

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/80/2E/wKioL1c6tkKAfJs0AAASy4JzaQM895.png" style="float:none;" title="3.png" alt="wKioL1c6tkKAfJs0AAASy4JzaQM895.png" />

重啟服務

[[email protected] ~]# /etc/init.d/mysqld restart

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M00/80/31/wKiom1c6tc7gjnH2AAAOHnRT7Mg760.png" title="4.png" alt="wKiom1c6tc7gjnH2AAAOHnRT7Mg760.png" />

[[email protected] ~]# cd /data/mysql/

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/80/2E/wKioL1c6tvqgftDwAABld3Ou2iY968.png" title="5.png" alt="wKioL1c6tvqgftDwAABld3Ou2iY968.png" />

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

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

mysql> show variables like ‘log_%‘;

650) this.width=650;" src="http://s1.51cto.com/wyfs02/M02/80/31/wKiom1c6t6jTaW_9AAB5XyEkA_0357.png" title="1.png" alt="wKiom1c6t6jTaW_9AAB5XyEkA_0357.png" />

3、binlog記錄檔的產生

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

650) this.width=650;" src="http://s2.51cto.com/wyfs02/M02/80/2F/wKioL1c6usXCBk48AAD30t34icg717.png" title="2.png" alt="wKioL1c6usXCBk48AAD30t34icg717.png" />

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

註： /etc/my.cnf 預設情況下是沒有這個選項的，需要自訂添加：

[[email protected] mysql]# vim /etc/my.cnf 

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M00/80/2F/wKioL1c6u-3S4YOzAACygoxYH5c767.png" title="3.png" alt="wKioL1c6u-3S4YOzAACygoxYH5c767.png" />

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

mysql> flush logs;

650) this.width=650;" src="http://s2.51cto.com/wyfs02/M02/80/32/wKiom1c6u3qAVr90AADR9YMlBMA549.png" title="4.png" alt="wKiom1c6u3qAVr90AADR9YMlBMA549.png" />

4、查看系統中的binlog日誌

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

mysql> show master logs;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/80/2F/wKioL1c6vSmyb40eAABs0no0Fc8142.png" title="5.png" alt="wKioL1c6vSmyb40eAABs0no0Fc8142.png" />

5、查看binlog記錄檔

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

[[email protected] mysql]# mysqlbinlog mysql-bin.000004| more

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M00/80/2F/wKioL1c6vwuy8RIHAADOpbSUL0o633.png" title="1.png" alt="wKioL1c6vwuy8RIHAADOpbSUL0o633.png" />

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

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

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

mysqlbinlog xxx-bin.000001|mysql -uroot –p‘password‘ dataname

-u  指定資料庫使用者名稱 –p 使用者名稱對應的密碼 dataname 資料庫的名字 

7、binlog命令列參數詳解

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

[[email protected] mysql]# cat /etc/my.cnf |grep -v ^#|grep -v ^$

650) this.width=650;" src="http://s1.51cto.com/wyfs02/M01/80/32/wKiom1c6wVLyuX5lAACKJE1muz4489.png" title="2.png" alt="wKiom1c6wVLyuX5lAACKJE1muz4489.png" />

8、刪除binlog日誌

8.1刪除部分binlog日誌

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

mysql> purge master logs to ‘mysql-bin.000009‘;

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

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M00/80/32/wKiom1c6w-Oi4RjhAAFc0vV-QL8375.png" title="4.png" alt="wKiom1c6w-Oi4RjhAAFc0vV-QL8375.png" />

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

[[email protected] mysql]# ll --full-time

mysql> purge master logs before ‘2016-05-17 15:27:53‘;

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

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/80/33/wKiom1c6yLCQl9kXAAF5gkgW1ec105.png" title="3.png" alt="wKiom1c6yLCQl9kXAAF5gkgW1ec105.png" />

8.2刪除全部binlog日誌

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

mysql> reset master;

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/80/33/wKiom1c6yeWxic10AAE8_9gLyKY159.png" title="5.png" alt="wKiom1c6yeWxic10AAE8_9gLyKY159.png" />

mysql ---------binlog