二進制日志非常的好用了,我們通常可以使用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,那么我們可以使用于以下語句進行恢復:

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

有時有可能因為系統版本的問題,以上方法行不通,可以將二進制導出到一個sql文件中,再直接根據sql語句進行恢復:

mysqlbinlog  mysqlbinlog.000001 >log.sql