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
      

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