使用mysqlbinlog提取二進位日誌，mysqlbinlog二進位

使用mysqlbinlog提取二進位日誌，mysqlbinlog二進位

    MySQL binlog日誌記錄了MySQL資料庫從啟用日誌以來所有對當前資料庫的變更。binlog日誌屬於二進位檔案，我們可以從binlog提取出來產生可閱讀的SQL語句來重建當前資料庫以及根據需要實現時間點復原或不完全恢複。本文主要描述了如果提取binlog日誌，並給出相關樣本。

    有關binlog的介紹與描述請參考：MySQL 二進位日誌(Binary Log)

 

1、提取mysqlbinlog的幾種方式
a、使用show binlog events方式可以擷取當前以及指定binlog的日誌，不適宜提取大量日誌。
b、使用mysqlbinlog命令列提取(適宜批量提取日誌)。

 

2、示範show binlog events方式

mysql> show variables like 'version';+---------------+------------+| Variable_name | Value      |+---------------+------------+| version       | 5.6.12-log |+---------------+------------+mysql> show binary logs;+-----------------+-----------+| Log_name        | File_size |+-----------------+-----------+| APP01bin.000001 |       120 |+-----------------+-----------+a、只查看第一個binlog檔案的內容(show binlog events) mysql> use replication;Database changedmysql> select * from tb;+------+-------+| id   | val   |+------+-------+|    1 | robin |+------+-------+mysql> insert into tb values(2,'jack');Query OK, 1 row affected (0.02 sec)mysql> flush logs;Query OK, 0 rows affected (0.00 sec)mysql> insert into tb values(3,'fred');Query OK, 1 row affected (0.00 sec)mysql> show binary logs;+-----------------+-----------+| Log_name        | File_size |+-----------------+-----------+| APP01bin.000001 |       409 || APP01bin.000002 |       363 |+-----------------+-----------+mysql> show binlog events;+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info                                               |+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+| APP01bin.000001 |   4 | Format_desc |        11 |         120 | Server ver: 5.6.12-log, Binlog ver: 4              || APP01bin.000001 | 120 | Query       |        11 |         213 | BEGIN                                              || APP01bin.000001 | 213 | Query       |        11 |         332 | use `replication`; insert into tb values(2,'jack') || APP01bin.000001 | 332 | Xid         |        11 |         363 | COMMIT /* xid=382 */                               || APP01bin.000001 | 363 | Rotate      |        11 |         409 | APP01bin.000002;pos=4                              |+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+-- 在上面的結果中第3行可以看到我們執行的SQL語句，第4行為自動認可-- Author : Leshami-- Blog   : http://blog.csdn.net/leshamib、查看指定binlog檔案的內容(show binlog events in 'binname.xxxxx')mysql> show binlog events in 'APP01bin.000002';+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info                                               |+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+| APP01bin.000002 |   4 | Format_desc |        11 |         120 | Server ver: 5.6.12-log, Binlog ver: 4              || APP01bin.000002 | 120 | Query       |        11 |         213 | BEGIN                                              || APP01bin.000002 | 213 | Query       |        11 |         332 | use `replication`; insert into tb values(3,'fred') || APP01bin.000002 | 332 | Xid         |        11 |         363 | COMMIT /* xid=394 */                               |+-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+c、查看當前正在寫入的binlog檔案(show master status\G) mysql> show master status\G*************************** 1. row ***************************             File: APP01bin.000002         Position: 363     Binlog_Do_DB:  Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)d、擷取指定位置binlog的內容(show binlog events from) mysql> show binlog events from 213;+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+| Log_name        | Pos | Event_type | Server_id | End_log_pos | Info                                               |+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+| APP01bin.000001 | 213 | Query      |        11 |         332 | use `replication`; insert into tb values(2,'jack') || APP01bin.000001 | 332 | Xid        |        11 |         363 | COMMIT /* xid=382 */                               || APP01bin.000001 | 363 | Rotate     |        11 |         409 | APP01bin.000002;pos=4                              |+-----------------+-----+------------+-----------+-------------+----------------------------------------------------+

3、示範mysqlbinlog方式提取binlog

a、提取指定的binlog日誌# mysqlbinlog /opt/data/APP01bin.000001# mysqlbinlog /opt/data/APP01bin.000001|grep insert/*!40019 SET @@session.max_insert_delayed_threads=0*/;insert into tb values(2,'jack')b、提取指定position位置的binlog日誌# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001c、提取指定position位置的binlog日誌並輸出到壓縮檔# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 |gzip >extra_01.sql.gzd、提取指定position位置的binlog日誌匯入資料庫# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 | mysql -uroot -pe、提取指定開始時間的binlog並輸出到記錄檔# mysqlbinlog --start-datetime="2014-12-15 20:15:23" /opt/data/APP01bin.000002 --result-file=extra02.sqlf、提取指定位置的多個binlog記錄檔# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 /opt/data/APP01bin.000002|moreg、提取指定資料庫binlog並轉換字元集到UTF8# mysqlbinlog --database=test --set-charset=utf8 /opt/data/APP01bin.000001 /opt/data/APP01bin.000002 >test.sqlh、遠程提取日誌，指定結束時間 # mysqlbinlog -urobin -p -h192.168.1.116 -P3306 --stop-datetime="2014-12-15 20:30:23" --read-from-remote-server mysql-bin.000033 |morei、遠程提取使用row格式的binlog日誌並輸出到本地檔案# mysqlbinlog -urobin -p -P3606 -h192.168.1.177 --read-from-remote-server -vv inst3606bin.000005 >row.sql

4、擷取mysqlbinlog的協助資訊(僅列出常用選項)
-?, --help
   顯示協助訊息並退出。

-d, --database=name
   只列出該資料庫的條目(只適用本地日誌)。

-f, --force-read
   使用該選項，如果mysqlbinlog讀它不能識別的二進位日誌事件，它會列印警告，忽略該事件並繼續。沒有該選項，如果mysqlbinlog讀到此類事件則停止。

-h, --host=name
   擷取給定主機上的MySQL伺服器的二進位日誌。

-l, --local-load=name
   為指定目錄中的LOAD DATA INFILE預先處理本地臨時檔案。

-o, --offset=#
   跳過前N個條目。

-p, --password[=name]
   當串連伺服器時使用的密碼。如果使用短選項形式(-p)，選項和密碼之間不能有空格。
   如果在命令列中–password或-p選項後面沒有密碼值，則提示輸入一個密碼。

-P, --port=#
   用於串連遠程伺服器的TCP/IP連接埠號碼。

--protocol=name
   使用的連線協定。

-R, --read-from-remote-server|--read-from-remote-master=name
   從MySQL伺服器讀二進位日誌。如果未給出該選項，任何串連參數選項將被忽略，即串連到本地。
   這些選項是–host、–password、–port、–protocol、–socket和–user。

-r, --result-file=name
   將輸出指向給定的檔案。

-s, --short-form
   只顯示日誌中包含的語句，不顯示其它資訊，該方式可以縮小產生sql檔案的尺寸。

-S, --socket=name
   用於串連的通訊端檔案。

--start-datetime=name
   從二進位日誌中讀取等於或晚於datetime參量的事件，datetime值相對於運行mysqlbinlog的機器上的本地時區。
   該值格式應符合DATETIME或TIMESTAMP資料類型。例如：2004-12-25 11:25:56 ，建議使用引號標識。

--stop-datetime=name
   從二進位日誌中讀取小於或等於datetime的所有日誌事件。關於datetime值的描述參見--start-datetime選項。

-j, --start-position=#
   從二進位日誌中第1個位置等於N參量時的事件開始讀。

--stop-position=#
   從二進位日誌中第1個位置等於和大於N參量時的事件起停止讀。

--server-id=#  
   僅僅提取指定server_id的binlog日誌

--set-charset=name
   添加SET NAMES character_set到輸出     
               
-t, --to-last-log
   在MySQL伺服器中請求的二進位日誌的結尾處不停止，而是繼續列印直到最後一個二進位日誌的結尾。
   如果將輸出發送給同一台MySQL伺服器，會導致無限迴圈。該選項要求–read-from-remote-server。

-D, --disable-log-bin
   禁用二進位日誌。如果使用–to-last-logs選項將輸出發送給同一台MySQL伺服器，可以避免無限迴圈。
   該選項在崩潰恢複時也很有用，可以避免複製已經記錄的語句。注釋：該選項要求有SUPER許可權。

-u, --user=name
   串連遠程伺服器時使用的MySQL使用者名稱。

-v, --verbose
   用於輸出基於row模式的binlog日誌，-vv為列資料類型添加註釋

-V, --version
   顯示版本資訊並退出。

 

5、小結
a、可以通過show binlog events以及mysqlbinlog方式來提取binlog日誌。
b、show binlog events 參數有限不適宜批量提取，mysqlbinlog可用於批量提取來建立恢複資料庫的SQL。
c、mysqlbinlog可以基於時間點，position等方式實現不完全恢複或時間點復原。
d、mysqlbinlog可以從支援本地或遠程方式提取binlog日誌。
e、mysqlbinlog可以基於server_id，以及基於資料庫層級提取日誌，不支援表層級。