Mysql 二進制日志管理

Mysql 二進制日志管理

MySQL二進制日志是以事務安全的方式包含更新日志中可用的所有資訊。它包含了所有更新了資料或者已經潛在更新了資料（例如，沒有比對任何行的一個DELETE）的所有語句。語句以“事件”的形式儲存，它描述資料更改。

運作伺服器時若啟用二進制日志則性能大約慢1%。但是，二進制日志的好處，即用于恢複并允許設定複制超過了這個小小的性能損失。

編輯mysql配置檔案：/etc/my.cnf（windows下是my.ini），将“bin-log”一行前面的注釋去掉，預設是：#log-bin=mysql-bin，（沒有的話，自己手動添加一行），等号後面的mysql-bin是生成日志的路徑和檔案名，這裡表示在mysql的data目錄下生成，你也可以自己改路徑，如log-bin=/data/mysql/log/mysql-bin。

還可以設定以下選項：

binlog-do-db= db_name  #将db_name更新記錄到二進制日志中

binlog-ignore-db=db_name #不将db_name更新儲存到二進制日志中

max_binlog_size= 104857600 #每個日志檔案最大100M

儲存，重新開機mysql。

重新開機後看到有生成類似mysql-bin.000001、mysql-bin.000002的檔案就說明已經成功打開了二進制日志。

1、檢視目前已有的二進制日志檔案，執行sql“SHOW BINLOG EVENTS \G”或“SHOW MASTER LOGS”：

mysql> SHOW BINLOG EVENTS \G;

mysql> SHOW MASTER LOGS；

2、清除二進制日志檔案：

mysql> PURGE { MASTER|BINARY } LOGS TO 'log_name';

mysql> PURGE { MASTER|BINARY } LOGS BEFORE 'datetime';

例如： 

mysql> PURGE MASTER LOGS TO 'mysql-bin.000001'; 

mysql> PURGE MASTER LOGS BEFORE '2010-06-20 13:00:00';

另一種清除二進制日志方法：

mysql > flush logs;

mysql > reset master;

mysqlbinlog：用于處理二進制日志檔案的實用工具；可以用它來恢複從指定的時間點開始 (例如，從你最後一次備份)直到現在或另一個指定的時間點的資料。

使用方法：mysqlbinlog [options] log-files。

mysqlbinlog有很多選項，具體可以使用mysqlbinlog –help檢視:

如：bin>mysqlbinlog --help

（如果沒有将mysqlbinlog設定成系統指令，linux下可以用find / -name "mysqlbinlog"搜尋出mysqlbinlog的位置，windows下在mysql安裝目錄的bin檔案夾下）

如果執行mysqlbinlog時出現類似unknown variable 'default-character-set=utf8'錯誤提示，是因為在my.cnf（或my.ini）中的client選項組中添加了default-character-set=utf8，可以有兩種辦法解決這個bug：

第一種，執行時加上--no-defaults選項；如：mysqlbinlog --no-defaults --help。

第二種，在使用mysqlbinlog工具時把my.cnf（或my.ini）中client選項組中的default-character-set=utf8注釋掉。注意要記得在不在使用mysqlbinlog工具時取消這個注釋。

部分常用選項說明：

--start-datetime=datetime　#從二進制日志中第1個日期時間等于或晚于datetime參量的事件開始讀取, 格式應符合DATETIME或TIMESTAMP資料類型。datetime值是相對于運作mysqlbinlog的機器上的本地時區時間。

--stop-datetime=datetime　#從二進制日志中第1個日期時間等于或晚于datetime參量的事件停止讀取，描述同start-datetime。

--start-position=N #從二進制日志中第1個位置等于N參量時的事件開始讀。

·       --stop-position=N　 #從二進制日志中第1個位置等于N參量時的事件停止讀。

示例：

１、 從mysql-bin.000001中恢複從2010-01-01 01:00:00開始之後的資料：

bin> mysqlbinlog --start-datetime="2010-01-01 01:00:00" mysql-bin.000001 | mysql -uroot -proot

２、 從mysql-bin.000001中恢複從2010-01-01 01:00:00到2010-01-02 01:00:00之間的資料：

bin> mysqlbinlog --start-datetime="2010-01-01 01:00:00" --stop-datetime="2010-01-02 01:00:00" mysql-bin.000001 | mysql -uroot -proot

３、 從多個日志檔案中恢複資料：

bin> mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -uroot -proot

或

bin> ls -t -r -1 mysql-bin.* | xargs mysqlbinlog | mysql -uroot -proot

４、 其他方式恢複：

bin> mysqlbinlog mysql-bin.000001 > /temp/binlog.sql #符号>表示建立新的檔案，如果原來已經存在，則會覆寫掉原檔案。

bin> mysqlbinlog --stop-datetime="2010-01-02 01:00:00" mysql-bin.000002 >> /temp/binlog.sql #符号>>表示追加到内容後面。

bin> mysql -uroot -proot -e “source /temp/binlog.sql”　#這步必須執行

本文轉自 houzaicunsky 51CTO部落格，原文連結:http://blog.51cto.com/hzcsky/481430