mysqlbinlog工具基於日誌恢複詳細解釋

mysqlbinlog工具基於日誌恢複詳細解釋

如果每天都會產生大量的二進位日誌，這些日誌長時間不清理的話，將會對磁碟空間帶來很大的浪費，所以定期清理日誌是DBA維護mysql的一個重要工作

1)RESET MASTER
在上面查看日誌存放的檔案夾中，二進位日誌命名的格式是以mysql-bin.*，*代表日誌的序號,序號是遞增的,其中還有mysql-bin.index是日誌的索引檔案,記錄了日誌的最大序號
 我們執行RESET MASTER命名刪除全部日誌

 查看刪除後的日誌

 可以看到,以前的日誌全部被清空,新的日誌從00001開始

2)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'時間之前的所有日誌進行刪除

3)-EXPIRE_LOGS_DAYS
此參數是設定日誌的到期天數，到期的日誌將會被自動刪除，這有利於減少我們管理日誌的工作量，需要修改my.cnf

這裡我們設定儲存日誌為3天，3天之後到期的日誌將被自動刪除

恢複
 bin-log是記錄著mysql所有事件的操作，當mysql發生重大錯誤時，可以通過bin-log做完整恢複，基於時間點的恢複，和基於位置的恢複

完整恢複，假定我們每天淩晨2點都會使用mysqldump備份資料庫，但在第二天早上9點由於資料庫出現了故障，資料無法訪問，需要恢複資料，先使用昨天淩晨備份的檔案進行恢複到淩晨2點的狀態，在使用mysqlbinlog恢複自mysqldump備份以來的binlog
 mysql localhost mysql-bin.000001 | mysql -uroot -p
這樣資料庫就可以完全的恢複到崩潰前的完全狀態

 基於時間點的恢複，由於誤操作，比如說刪除了一張表，這時使用上面講的完全恢複是沒有用的，因為日誌裡面還存在誤操作的語句，，我們需要的是恢複到誤操作前的狀態，然後跳過誤操作的語句，再恢複後面操作的語句，假定我們刪除了一張表的誤操作發生在10:00這個時間點，我們可以使用下面的語句用備份和binlog將資料恢複到故障前

mysqlbinlog --stop-date='2010-09-04 9:59:59' /var/log/mysql-bin.000001 | mysql -uroot -p

然後跳過誤操作的時間點，繼續執行後面的binlog

mysqlbinlog --start-date='2010-09-04 10:01:00' /var/log/mysql-bin.000001 | mysql -uroot -p

其中--stop-date='2010-09-04 9:59:59' 和 --start-date='2010-09-04 10:01:00' 其中的時間是你誤操作的時間點，當然了，這個時間點你需要你自己計算的，而且這個時間點還可以涉及到的不只是誤操作，還可以有正確的操作也被跳過去了。

基於位置恢複，由於上面提到的，使用基於時間點的恢複可能出現，在一個時間點裡面可能存在誤操作和其他正確的操作，所以我們需要一種更為精確的恢複方式
 使用mysqlbinlog查看二進位，可看到

 

 其中drop tables test1這個誤操作的end_log_pos為8879917，幾下這個id,得出它前後操作的id分別為8879916,8879918
我們將進行位置恢複操作

mysqlbinlog --stop-position='8879916' /var/log/mysql-bin.000001 | mysql -uroot -p

 mysqlbinlog --start-position='8879918' /var/log/mysql-bin.000001 | mysql -uroot -p

伺服器產生的二進位記錄檔寫成二進位格式。要想檢查這些文字格式設定的檔案，應使用mysqlbinlog工具 + 生產力。
 應這樣調用mysqlbinlog：
shell> mysqlbinlog [options] log-files…例如，要想顯示二進位日誌binlog.000003的內容，使用下面的命令：
shell> mysqlbinlog binlog.0000003輸出包括在binlog.000003中包含的所有語句，以及其它資訊例如每個語句花費的時間、客戶發出的線程ID、發出線程時的時間戳記等等。
 通常情況，可以使用mysqlbinlog直接讀取二進位記錄檔並將它們用於本地MySQL伺服器。也可以使用–read-from-remote-server選項從遠程伺服器讀取二進位日誌。
 當讀取遠程二進位日誌時，可以通過串連參數選項來指示如何串連伺服器，但它們經常被忽略掉，除非你還指定了–read-from-remote-server選項。這些選項是–host、–password、–port、–protocol、–socket和–user。
 還可以使用mysqlbinlog來讀取在複製過程中從伺服器所寫的中繼記錄檔。中繼日誌格式與二進位記錄檔相同。

mysqlbinlog支援下面的選項：
·
—help，-？
 顯示協助訊息並退出。
·
—database=db_name，-d db_name
只列出該資料庫的條目(只用本地日誌)。
·
–force-read，-f
使用該選項，如果mysqlbinlog讀它不能識別的二進位日誌事件，它會列印警告，忽略該事件並繼續。沒有該選項，如果mysqlbinlog讀到此類事件則停止。
·
–hexdump，-H
在注釋中顯示日誌的十六進位轉儲。該輸出可以協助複製過程中的調試。在MySQL 5.1.2中添加了該選項。
·
–host=host_name，-h host_name
擷取給定主機上的MySQL伺服器的二進位日誌。
·
–local-load=path，-l pat
為指定目錄中的LOAD DATA INFILE預先處理本地臨時檔案。
·
–offset=N，-o N
跳過前N個條目。
·
–password[=password]，-p[password]
當串連伺服器時使用的密碼。如果使用短選項形式(-p)，選項和 密碼之間不能有空格。如果在命令列中–password或-p選項後面沒有 密碼值，則提示輸入一個密碼。
·
–port=port_num，-P port_num
用於串連遠程伺服器的TCP/IP連接埠號碼。
·
–position=N，-j N
不贊成使用，應使用–start-position。
·
–protocol={TCP | SOCKET | PIPE | -position

使用的連線協定。
·
–read-from-remote-server，-R
從MySQL伺服器讀二進位日誌。如果未給出該選項，任何串連參數選項將被忽略。這些選項是–host、–password、–port、–protocol、–socket和–user。
·
–result-file=name, -r name
將輸出指向給定的檔案。
·
–short-form，-s
只顯示日誌中包含的語句，不顯示其它資訊。
·
–socket=path，-S path
用於串連的通訊端檔案。
·
–start-datetime=datetime
從二進位日誌中第1個日期時間等於或晚於datetime參量的事件開始讀取。datetime值相對於運行mysqlbinlog的機器上的本地時區。該值格式應符合DATETIME或TIMESTAMP資料類型。例如：
shell> mysqlbinlog –start-datetime=”2004-12-25 11:25:56″ binlog.000003該選項可以協助點對點恢複。
·
–stop-datetime=datetime
從二進位日誌中第1個日期時間等於或晚於datetime參量的事件起停止讀。關於datetime值的描述參見–start-datetime選項。該選項可以協助及時恢複。
·
–start-position=N
從二進位日誌中第1個位置等於N參量時的事件開始讀。
·
–stop-position=N
從二進位日誌中第1個位置等於和大於N參量時的事件起停止讀。
·
–to-last-logs，-t
在MySQL伺服器中請求的二進位日誌的結尾處不停止，而是繼續列印直到最後一個二進位日誌的結尾。如果將輸出發送給同一台MySQL伺服器，會導致無限迴圈。該選項要求–read-from-remote-server。
·
–disable-logs-bin，-D
禁用二進位日誌。如果使用–to-last-logs選項將輸出發送給同一台MySQL伺服器，可以避免無限迴圈。該選項在崩潰恢複時也很有用，可以避免複製已經記錄的語句。注釋：該選項要求有SUPER許可權。
·
–user=user_name，-u user_name
串連遠程伺服器時使用的MySQL使用者名稱。
·
–version，-V
顯示版本資訊並退出。

MySQL 5.6.12切換binlog二進位日誌路徑 

關於使用MySQL binlog對資料進行恢複的實戰 

MySQL 5.6.12切換binlog二進位日誌路徑 

mysqlbinlog解析binlog亂碼問題解密 

MySQL通過binlog來恢複資料 

MySQL binlog 組提交與 XA(兩階段交易認可) 

MySQL使用備份和binlog進行資料恢複

本文永久更新連結地址：