binlog是mysql記錄操作的二進制日志檔案,有三種格式可選,但是老舊的SBR已經不适合現在大多數業務需求,是以大多數都建議用MBR和RBR,即mixed或row,而解析他的原因,幾乎都隻有一個,就是恢複資料庫,或者是反編譯來恢複資料庫,目的都是一樣.
以下是基于mysql5.6的mysqlbinlog來描述的,請各位知照.
binlog使用方法
直接來看看例子:
<code>#可以在資料庫界面操作一些指令</code>
<code>#隻檢視第一個binlog檔案的内容(不建議)</code>
<code>mysql> show binlog events;</code>
<code>#檢視指定binlog檔案的内容(不建議)</code>
<code>mysql> show binlog events </code><code>in</code> <code>'mysql-bin.000002'</code><code>;</code>
<code>#檢視目前正在寫入的binlog檔案</code>
<code>mysql> show master status\G</code>
<code>#擷取binlog檔案清單</code>
<code>mysql> show binary logs;</code>
<code>#直接用mysqlbinlog指令就更全面一些</code>
<code>#可以直接打開,可能不夠直覺,而且會不停刷屏,慎用</code>
<code>mysqlbinlog mysql-bin.000002 </code>
<code>#檢視row格式下的二進制日志</code>
<code>mysqlbinlog -vv --base64-output=decode-rows filename</code>
<code>#基于開始/結束時間的解析</code>
<code>mysqlbinlog --start-datetime=</code><code>'2013-09-10 00:00:00'</code> <code>--stop-datetime=</code><code>'2013-09-10 01:01:01'</code> <code>-d 庫名 二進制檔案</code>
<code>#基于pos值的解析</code>
<code>mysqlbinlog --start-position=107 --stop-position=1000 -d 庫名 二進制檔案</code>
<code>#恢複單一資料庫的binlog</code>
<code>mysqlbinlog -d dbname binlog.00002</code>
<code>#一個完成的事例參考:把binlog裡面特定時間,特定庫的語句導出到t.txt</code>
<code>mysqlbinlog -vv --base64-output=decode-rows --start-datetime=</code><code>'2016-06-15 20:00:00'</code> <code>--stop-datetime=</code><code>'2016-06-15 21:00:00'</code> <code>-d baiyang mysql-bin.000011 > t.txt</code>
參數說明:
--base64-output=value 使用base-64編碼格式顯示二進制日志内容,AUTO ("automatic") 或 UNSPEC ("unspecified") 參數自動顯示二進制語句的内容，如果沒有指定 --base64-output參數，則效果就相當于--base64-output=AUTO；NEVER參數會導緻二進制語句不會顯示；DECODE-ROWS解碼處理二進制日志。比較常用的是--base64-output=DECODE-ROWS： 會顯示出row模式帶來的sql變更
--bind-address=name 綁定IP位址(用于一些異常IP通路的情況)
--character-sets-dir=name 字元集檔案的目錄路徑(非正常字元集使用)
-C, --compress 啟用壓縮模式(減少帶寬使用)
-d db_name,--database=db_name 隻列出該資料庫的條目(隻用本地日志)。
--debug-check 檢查記憶體和打開的檔案使用情況并退出。
--debug-info 列印一些調試資訊并退出。
--default-auth=name 預設的用戶端身份驗證插件路徑
-D,disable-logs-bin 禁用二進制日志。如果使用--to-last-logs選項将輸出發送給同一台MySQL伺服器，可以避免無限循環。該選項在崩潰恢複時也很有用，可以避免複制已經記錄的語句。注釋：該選項要求有SUPER權限。
-F, --force-if-open 強制打開沒有正常關閉的二進制日志檔案,(預設開啟,通過--skip-force-if-open關閉)
-f, --force-read 強制打開不能識别的二進制日志事件,如果使用該選項,mysqlbinlog預到不能識别的二進制日志事件，它會列印警告，忽略該事件并繼續。沒有該選項，mysqlbinlog讀到此類事件則停止。
-H, --hexdump 在注釋中顯示日志的十六進制轉儲。該輸出可以幫助複制過程中的調試。
-h, --host=name 擷取給定主機上的MySQL伺服器的二進制日志。
-l, --local-load=name 為指定目錄中的LOAD DATA INFILE預處理本地臨時檔案。
-o, --offset=# 跳過前 N個條目。
-p, --password[=name] 連接配接伺服器時使用密碼。如果使用短選項形式(-p)，選項和密碼之間不能有空格。如果在指令行中–password或-p選項後面沒有密碼值，則提示輸入一個密碼。
--plugin-dir=name 用戶端插件目錄
-P, --port=# 用于連接配接遠端伺服器的TCP/IP端口号。
--protocol=name 使用那種連接配接協定,可以是:tcp, socket, pipe,memory
-R, --read-from-remote-server 從MySQL伺服器讀二進制日志。如果沒有加入該選項，任何連接配接伺服器的參數選項将無效。這些選項是–host、–password、–port、–protocol、–socket和–user。可代替read-from-remote-master=BINLOG-DUMP-NON-GTIDS.
--read-from-remote-master=name 從MySQL伺服器讀二進制日志.通過設定COM_BINLOG_DUMP或COM_BINLOG_DUMP_GTID指令來設定參數BINLOG-DUMP-NON-GTIDS或BINLOG-DUMP-GTIDS,如果設定--read-from-remote-master=BINLOG-DUMP-GTIDS并結合--exclude-gtids,可以省一些網絡流量
--raw 需要配合-R使用。輸出原始二進制日志資料,而不是SQL,并輸出到日志檔案。
-r, --result-file=name 将輸出指向給定的檔案。和--raw一起使用的話,這是一個檔案前序.
--secure-auth 如果用戶端低于pre-4.1.1,将拒絕連接配接,預設開啟,可以通過--skip-secure-auth來關閉
--server-id=# 隻提取給定伺服器ID的二進制日志,指my.cnf的server-id的ID值
--server-id-bits=# 把server-id設定在顯著位置
--set-charset=name 輸出時添加SET NAMES character_set提示設定字元集
-s, --short-form 隻顯示日志中包含的語句，不顯示其它資訊。這僅用于測試,不應被使用在生産系統中。如果要禁止base64-output的輸出,可以考慮使用--base64-output=never instead.
-S, --socket=name 使用套接字socket來連接配接,通常是本機
--ssl 使用ssl協定來連接配接(一般不會用)
--ssl-ca=name 使用ca證書方式的SLL來連接配接,同理,下面幾個選項是各種證書和密鑰驗證方式的SSL登陸,就不一一介紹了.
--ssl-capath=name,--ssl-cert=name,--ssl-cipher=name,--ssl-key=name,--ssl-crl=name,--ssl-crlpath=name,--ssl-verify-server-cert
--start-datetime=datetime 從二進制日志中第1個日期時間等于或晚于datetime參量的事件開始讀取。datetime值相對于運作mysqlbinlog的機器上的本地時區。該值格式應符合DATETIME或TIMESTAMP資料類型。例如：mysqlbinlog –start-datetime="2004-12-2511:25:56" binlog.000003
-j, --start-position=# 從二進制日志中第1個位置等于N參量時的事件開始讀。
--stop-datetime=datetime 從二進制日志中第1個日期時間等于或晚于datetime參量的事件起停止讀。和--start-datetime選項配合使用設定導出特定的時間段資訊,有助于減少結果輸出。
--stop-never 等待更多的日志傳輸而不是退出目前指令,直到斷開伺服器連接配接為止,隐式設定--to-last-log
--stop-never-slave-server-id=# 目前指令的從庫ID值,當用到--read-from-remote-server或--stop-never時,指令就會像從庫一樣拉取binlog資料,是以需要一個唯一的server-id值來辨別他.
--stop-position=# 從二進制日志中第1個位置等于和大于N參量時的事件起停止讀。和--start-position選項配合使用設定導出特定的position間隔的binlog資訊,有助于減少結果輸出.
-t, --to-last-log 在MySQL伺服器中請求的二進制日志的結尾處不停止，而是繼續列印直到最後一個二進制日志的結尾。如果将輸出發送給同一台MySQL伺服器，會導緻無限循環。該選項要求–read-from-remote-server。
-u, --user=name 連接配接遠端伺服器時使用的MySQL使用者名。
-v, --verbose 詳細模式,顯示statement模式帶來的sql語句,-vv表示增加列類型的注釋.
-V, --version 顯示版本資訊并退出
--open-files-limit=# 指定要保留的打開的檔案描述符的數量
-c, --verify-binlog-checksum 驗證校驗binlog日志
--binlog-row-event-max-size=# 最大binlog日志檔案容量大小,此值必須是256的倍數。
--skip-gtids 不列印全局事務辨別符資訊(GTID),例如:SET GTID_NEXT=... etc
--include-gtids=name 列印給定值的GTID的資訊
--exclude-gtids=name 列印給定值以外的GTID的資訊
--rewrite-db=name 更新資料庫時給出一個和原來不一樣的資料庫名,例如:rewrite-db='from->to'.
—help，-？ 顯示幫助消息并退出。
使用binlog恢複資料
由于binlog可以直接解析成sql語句的,一般來說直接執行是可行的.
但是有時候我們不想做的那麼複雜,加上一個參數,直接執行,通過管道符就可以了.
<code>mysqlbinlog --start-position=</code><code>'530050688'</code> <code>--stop-position=</code><code>'601156037'</code> <code>-d </code><code>test</code> <code>/opt/mysql-bin</code><code>.000019 |mysql -uroot -p123 --binary-mode</code>
binlog server的建立:
用過mysql的都知道mysql支援主從架構,有些時候我們單純隻想異地備份binlog日志,但是特地搭一個從庫貌似不科學,而且mysql機制上也并不是把binlog拉下來就算,還要寫進資料庫占用一定空間,以前會引入blackhold引擎,而現在就友善多了,因為mysql5.6後引入了mysql的binlog server,相當友善,相當簡單,binlog就被拉過來了,而且隻要程序不掉,就一直拉.
因為方法很簡單,就是一條指令過去,是以不用很緊張,至于參數的意思,請看上面
指令如下,記得加背景運作指令符噢:
<code>#先看看目前的master狀态,可以忽略GTID,因為可以不用設定GTID</code>
<code>*************************** 1. row ***************************</code>
<code> </code><code>File: mysql-bin.000007</code>
<code> </code><code>Position: 56789</code>
<code> </code><code>Binlog_Do_DB: </code>
<code> </code><code>Binlog_Ignore_DB: </code>
<code>Executed_Gtid_Set: 3edae34c-6299-11e6-8999-8038bc0c67be:1-6754,</code>
<code>4cdc2a74-6299-7555-95ce-008cfaf595bc:1-7941248</code>
<code>1 row </code><code>in</code> <code>set</code> <code>(0.00 sec)</code>
<code>#開啟binlog server</code>
<code>mysqlbinlog -R --raw --host=*.*.*.* --user=root --password=x xxxxxxxxx --stop-never --stop-never-slave-server-</code><code>id</code><code>=56789 mysql-bin.000007 &</code>
<code>#binlog就過來了,看看目錄</code>
<code>ll ./*</code>
<code>-rw-rw---- 1 root root 1073741921 5月 29 19:15 mysql-bin.000007</code>
<code>-rw-rw---- 1 root root 1073741954 6月 2 12:14 mysql-bin.000008</code>
<code>-rw-rw---- 1 root root 1073743557 6月 8 10:03 mysql-bin.000009</code>
<code>-rw-rw---- 1 root root 1073742035 6月 12 16:33 mysql-bin.000010</code>
<code>-rw-rw---- 1 root root 1073742000 6月 16 10:38 mysql-bin.000011</code>
<code>-rw-rw---- 1 root root 1073742053 6月 20 09:18 mysql-bin.000012</code>
<code>#看看程序,正在運作,這就完成了</code>
<code>ps</code> <code>aux |</code><code>grep</code> <code>mysql</code>
<code>root 19683 0.0 0.2 59836 4764 pts</code><code>/1</code> <code>S 14:39 0:00 mysqlbinlog -R --raw --host=x.x.x.x -uroot -P3306 -px xxxxxxxxxxxxxx --stop-never --stop-never-slave-server-</code><code>id</code><code>=21305198 mysql-bin.000007</code>
<code>root 19697 0.0 0.0 112648 976 pts</code><code>/1</code> <code>S+ 14:45 0:00 </code><code>grep</code> <code>--color=auto mysql</code>
原來我已經跑那麼多了,呵呵~!
再看看資料庫狀态
<code>show processlist;</code>
<code>| 66379 | tencentroot | 10.*.*.*:47303 | NULL | Binlog Dump | 6932150 | Master has sent all binlog to slave; waiting </code><code>for</code> <code>binlog to be updated | NULL |</code>
<code>| 14277762 | tencentroot | 127.0.0.1:53878 | NULL | Sleep | 25 | | NULL |</code>
<code>| 20949882 | root | 10.*.*.*:2080 | NULL | Binlog Dump | 2937110 | Master has sent all binlog to slave; waiting </code><code>for</code> <code>binlog to be updated | NULL</code>
有兩個程序在拉binlog,毫無疑問,其中一個是真正的從庫,另一個就是我的binlogserver了.
完畢.
本文轉自arthur376 51CTO部落格，原文連結：http://blog.51cto.com/arthur376/1794632，如需轉載請自行聯系原作者
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)