MySQL二進制日志使用操作步驟

二進制日志非常的好用了，我們通常可以使用mysql二進制日志進行一些操作了，下面我們來深入理解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