使用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 changed
mysql> 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/leshami

b、查看指定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.000001

c、提取指定position位置的binlog日誌並輸出到壓縮檔
# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 |gzip >extra_01.sql.gz

d、提取指定position位置的binlog日誌匯入資料庫
# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 | mysql -uroot -p

e、提取指定開始時間的binlog並輸出到記錄檔
# mysqlbinlog --start-datetime="2014-12-15 20:15:23" /opt/data/APP01bin.000002 --result-file=extra02.sql

f、提取指定位置的多個binlog記錄檔
# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 /opt/data/APP01bin.000002|more

g、提取指定資料庫binlog並轉換字元集到UTF8
# mysqlbinlog --database=test --set-charset=utf8 /opt/data/APP01bin.000001 /opt/data/APP01bin.000002 >test.sql

h、遠程提取日誌，指定結束時間
# mysqlbinlog -urobin -p -h192.168.1.116 -P3306 --stop-datetime="2014-12-15 20:30:23" --read-from-remote-server mysql-bin.000033 |more

i、遠程提取使用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，以及基於資料庫層級提取日誌，不支援表層級。

使用mysqlbinlog工具進行基於位置或時間點的恢複 

本文永久更新連結地址：