MySQL二進位日誌使用操作步驟

簡介：

MySQL的二進位日誌可以說或是MySQL最重要的日誌了,它記錄了所有的DDL和DML(除了資料查詢語句)語句,以事件形式記錄,還包含語句所執行的消耗的時間,MySQL的二進位日誌是失誤安全型的.

MySQL的二進位日誌的作用是顯而易見的，可以方便的備份這些日誌以便做資料恢複，也可以作為主從複製的同步檔案，然而二進位日誌的大小可能會根據不同的需求而存在麻煩，所以讓日誌復原是必須的，當然MySQL已經為我們提供了二進位復原的功能，那就是max_binlog_size參數。

預設MySQL的二進位日誌達到1G後就會自動復原，如果我們想要更小的二進位日誌，可以使用max_binlog_size參數來設定。測試使用max_binlog_size=200M，具體應用就是在設定檔裡添加這個參數max_binlog_size=200M，官方文檔是這樣解釋的：

mysqld在每個二進位日誌名後面添加一個數字副檔名。每次你啟動伺服器或重新整理日誌時該數字則增加。如果當前的日誌大小達到 max_binlog_size，還會自動建立新的二進位日誌。如果你正使用大的事務，二進位日誌還會超過 max_binlog_size：事務全寫入一個二進位日誌中，絕對不要寫入不同的二進位日誌中。也就是說，在達到max_binlog_size的時候，如果正在處理一個大的事務，那麼二進位日誌會在處理完這個事務後才會復原，所以該二進位日誌可能會大於所設定的max_binlog_size。

在主從複製的應用中，可能我們不希望二進位日誌過大，因為日誌過大可能會影響日誌的執行效率，適當調整max_binlog_size的值還是非常有意義的，當然還要記得定期清理長時間不用的日誌哦，要不然你的硬碟空間很快就會被用完的。

開啟mysql二進位日誌：

編輯my.cnf，添加

log-bin=/var/log/mysql/mysql-bin.log

開啟日誌後需要myssqladmin flush logs才會生效。

需要注意的是log-bin指定副檔名是無效的，當mysql建立二進位記錄檔時，首先建立一個以“mysql_log_bin”為名稱，以“.index”為尾碼的檔案；再建立一個以“mysql_log_bin”為名稱，以 “.000001”為尾碼的檔案。當mysql服務重新啟動一次以“.000001”為尾碼的檔案會增加一個，並且尾碼名加1遞增；如果日誌長度超過了 max_binlog_size的上限（預設是1G）也會建立一個新的記錄檔；使用flush logs（mysql命令符）或者執行mysqladmin –u –p flush-logs（windows命令提示字元）也會建立一個新的記錄檔。

查看：

由於日誌是以二進位方式儲存的，不能直接讀取，需要使用mysql內建的mysqlbinlog工具來進行查看

mysqlbinlog mysql-bin.000002 -d test
mysqlbinlog有一些選項可以使用,簡單說明常用選項:
-d,--database=name :指定資料庫名稱,只列出指定資料庫的操作.
-D, --disable-log-bin :執行恢複的時候,禁止二進位日誌.可以防止同一台MySQL加上-t時進入死迴圈
-o,--offset=n :忽略掉日誌前n行命令
-r,--result-file=name :將輸出日誌到指定檔案
-R, --read-from-remote-server :從一個MySQL伺服器上讀取二進位
-s,--short-form :顯示簡單格式,省略一些資訊
-S, --socket=name  :socket檔案串連path.
-t, --to-last-log  :和-R一起使用,在二進位日誌結束的時候並不會停止,而是在MySQL伺服器最後產生的binlog結束,如果輸出和輸入都在一台MySQL上可能會導致死迴圈.
--set-charset=char-name :在輸出文字格式設定的時候,在第一行加上set names char-name.
--start-datetime=# --stop-datetime=# :指定輸出起始日期的日誌.
--start-position=# --stop-position=# :指定起始日誌的位置.
清理：

刪除全部二進位日誌：

reset master

刪除部分日誌：

PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE
PURGE MASTER LOGS TO 'mysql-bin.******'命令，是將'******'編號之前的所有日誌進行刪除
PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令，是將在'yyyy-mm-dd hh:mm:ss'時間之前的所有日誌進行刪除

設定日誌到期時間：

修改my.cnf
expire_log_day=5

這裡設定儲存5天的日誌，超過5天的日誌會被自動刪除

恢複：

完全恢複：

mysqlbinlog mysql-bin.00001|mysql -uroot -p

基於時間點的恢複：

如果誤刪了一張表，使用完全恢複是沒有用的，因為日誌裡同樣也保留著刪除的sql語句，所以我們需要恢複到誤操作前的狀態，然後跳過誤操作的語句。
假如我在20:00誤刪了一張表，可以使用以下語句恢複：

mysqlbinlog --stop-date='2012-06-05 19:59:59' /var/log/mysql-bin.000001 | mysql -uroot -p

跳過誤刪除的時間點，再執行：

mysqlbinlog --start-date='2012-06-05 20:01:00' /var/log/mysql-bin.000001 | mysql -uroot -p

基於位置點的恢複：

基於位置點的恢複可以得到更為精確的資料。
binlog
如上圖，drop table test這條語句的起始位置是889107，終止位置是889189，那麼我們可以使用於以下語句進行恢複：

mysqlbinlog --stop-position='889107' /var/lib/mysql/mysql-bin.000001|mysql -uroot -p
mysqlbinlog --start-position='889189' /var/lib/mysql/mysql-bin.000001|mysql -uroot -p

有時有可能因為系統版本的問題，以上方法行不通，可以將二進位匯出到一個sql檔案中，再直接根據sql語句進行恢複

mysqlbinlog  mysqlbinlog.000001 >log.sql