MySQL binlog：格式谎柄、增量恢復、閃回惯雳、Java 解析

MySQL 的 binlog 日志文件朝巫，記錄了數(shù)據(jù)庫表的全部修改操作。本文簡單整理 MySQL binlog 相關知識石景，以及如何使用 binlog 恢復或閃回數(shù)據(jù)庫數(shù)據(jù)劈猿。

STATEMENT 格式的 binlog

要想開啟 binlog，需要在啟動 MySQL 時傳入 --log-bin 參數(shù)潮孽【救伲或者也可以在 MySQL 配置文件 /etc/my.cnf，設置 log_bin 開啟 binlog往史。MySQL 5.7 開始仗颈，開啟 binlog 后，--server-id 參數(shù)也必須指定椎例，否則 MySQL 服務器會啟動失敗挨决。

binlog_format 支持 STATEMENT, ROW, MIXED 三種格式，MySQL 5.5 和 5.6 默認為 STATEMENT订歪，MySQL 5.7.7 開始默認為 ROW脖祈。若 SQL 使用 UUID(), RAND(), VERSION() 等函數(shù)，或者使用存儲過程刷晋、自定義函數(shù)盖高，基于 STATEMENT 的主從復時慎陵，是不安全的（很多人可能會認為 NOW(), CURRENT_TIMESTAMP 這些函數(shù)也是不安全的，事實上是安全的）[ doc1, doc2 ]喻奥∠Γ基于 ROW 的主從復制，是最安全的復制方式映凳。

現(xiàn)在先來看下 STATEMENT 格式的 binlog，/etc/my.cnf 文件修改的內(nèi)容如下：

server_id = 1
log_bin = mysql-bin
binlog_format = STATEMENT
binlog_row_image=FULL

重啟 MySQL 后邮破，在數(shù)據(jù)目錄 datadir 下诈豌，比如 /var/lib/mysql/，將會生成相應的 binlog 文件抒和，mysql-bin.index 和 mysql-bin.000001矫渔。.index 后綴的文件保存全部 binlog 文件名。mysql-bin.000001 文件記錄 binlog 內(nèi)容摧莽。每次 MySQL 啟動或者 flush 日志庙洼，都將按序號創(chuàng)建一個新的日志文件。另外镊辕，當日志文件大小超過 max_binlog_size 時油够，也會創(chuàng)建一個新的日志文件。

現(xiàn)在來試一試 binlog 功能征懈。假設在 testdb 庫在有 hello 表石咬，并對其中某行做修改操作：

mysql> select * from hello;
+----+-------+
| id | name  |
+----+-------+
|  1 | Andy  |
|  2 | Bill  |
|  3 | Candy |
+----+-------+
4 rows in set (0.00 sec)

mysql> update hello set name = 'Will' where id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

binlog 為二進制文件，需要使用 mysqlbinlog（doc, man）命令查看：

$ sudo mysqlbinlog /var/lib/mysql/mysql-bin.000001  # 直接在 mysql 服務器上讀取 binlog 文件
$ mysqlbinlog -R -h192.168.2.107 -uroot -p123456 mysql-bin.000001  # 或者卖哎，遠程讀取 binlog 文件

執(zhí)行 update 后相應新增的 binlog 文件內(nèi)容：

# at 154
#180617 22:47:49 server id 1  end_log_pos 219 CRC32 0x4bd9d69b     Anonymous_GTID    last_committed=0    sequence_number=1    rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180617 22:47:49 server id 1  end_log_pos 302 CRC32 0x476fafc9     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529246869/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 302
#180617 22:47:49 server id 1  end_log_pos 423 CRC32 0x7f2c2c7a     Query    thread_id=2    exec_time=0    error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1529246869/*!*/;
update hello set name = 'Will' where id = 3
/*!*/;
# at 423
#180617 22:47:49 server id 1  end_log_pos 454 CRC32 0x68da744a     Xid = 12
COMMIT/*!*/;

ROW 格式的 binlog

修改 /etc/my.cnf 的 binlog_format 為 ROW鬼悠，再重啟 MySQL。格式修改后亏娜，會生成一個新的 binlog 文件 mysql-bin.000002焕窝。

mysql> show create table hello;
+-------+-------------------------------------------------------------------------+
| Table | Create Table
+-------+-------------------------------------------------------------------------+
| hello | CREATE TABLE `hello` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from hello where id;
+----+------+
| id | name |
+----+------+
|  1 | Andy |
|  2 | Lily |
|  3 | Will |
+----+------+
1 row in set (0.00 sec)

mysql> update hello set name = 'David' where id = 3;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查看 ROW 格式的 binlog，需要使用 sudo mysqlbinlog -v --base64-output=DECODE-ROWS /var/lib/mysql/mysql-bin.000002 命令维贺。執(zhí)行 update 后相應新增的 binlog 內(nèi)容：

# at 154
#180617 22:54:13 server id 1  end_log_pos 219 CRC32 0x2ce70d4d     Anonymous_GTID    last_committed=0    sequence_number=1    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#180617 22:54:13 server id 1  end_log_pos 293 CRC32 0x8183fddf     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529247253/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 293
#180617 22:54:13 server id 1  end_log_pos 346 CRC32 0x0fc7e1a4     Table_map: `testdb`.`hello` mapped to number 110
# at 346
#180617 22:54:13 server id 1  end_log_pos 411 CRC32 0xb58e729d     Update_rows: table id 110 flags: STMT_END_F
### UPDATE `testdb`.`hello`
### WHERE
###   @1=3
###   @2='Will'
### SET
###   @1=3
###   @2='David'
# at 411
#180617 22:54:13 server id 1  end_log_pos 442 CRC32 0xef964db8     Xid = 13
COMMIT/*!*/;

若執(zhí)行如下 SQL：

mysql> insert hello (name) values ('Frank');
Query OK, 1 row affected (0.02 sec)

相應生成的 binlog 內(nèi)容：

# at 442
#180617 22:55:47 server id 1  end_log_pos 507 CRC32 0x79de08a7     Anonymous_GTID    last_committed=1    sequence_number=2    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 507
#180617 22:55:47 server id 1  end_log_pos 581 CRC32 0x56f9eb6a     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529247347/*!*/;
BEGIN
/*!*/;
# at 581
#180617 22:55:47 server id 1  end_log_pos 634 CRC32 0xedb73620     Table_map: `testdb`.`hello` mapped to number 110
# at 634
#180617 22:55:47 server id 1  end_log_pos 684 CRC32 0x525a6a70     Write_rows: table id 110 flags: STMT_END_F
### INSERT INTO `testdb`.`hello`
### SET
###   @1=4
###   @2='Frank'
# at 684
#180617 22:55:47 server id 1  end_log_pos 715 CRC32 0x09a0d4de     Xid = 14
COMMIT/*!*/;

若執(zhí)行如下 SQL：

mysql> delete from hello where id = 2;
Query OK, 1 row affected (0.02 sec)

相應生成的 binlog 內(nèi)容：

# at 715
#180617 22:56:44 server id 1  end_log_pos 780 CRC32 0x9f52450e     Anonymous_GTID    last_committed=2    sequence_number=3    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 780
#180617 22:56:44 server id 1  end_log_pos 854 CRC32 0x0959bc8d     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1529247404/*!*/;
BEGIN
/*!*/;
# at 854
#180617 22:56:44 server id 1  end_log_pos 907 CRC32 0x2945260f     Table_map: `testdb`.`hello` mapped to number 110
# at 907
#180617 22:56:44 server id 1  end_log_pos 956 CRC32 0xc70df255     Delete_rows: table id 110 flags: STMT_END_F
### DELETE FROM `testdb`.`hello`
### WHERE
###   @1=2
###   @2='Bill'
# at 956
#180617 22:56:44 server id 1  end_log_pos 987 CRC32 0x0c98f18e     Xid = 15
COMMIT/*!*/;

使用 binlog 增量恢復

MySQL 邏輯備份通常會結合全量備份和增量備份它掂，使用 mysqldump 定期全量備份數(shù)據(jù)庫，然后利用 binlog 保存增量數(shù)據(jù)溯泣∪悍ⅲ恢復數(shù)據(jù)時，就是用 mysqldump 備份的數(shù)據(jù)恢復到備份的時間點发乔。數(shù)據(jù)庫在備份時間點到當前時間的增量修改熟妓，則通過 mysqlbinlog 將 binlog 中的增量數(shù)據(jù)恢復到數(shù)據(jù)庫。現(xiàn)在假設已經(jīng)使用 mysqldump 將數(shù)據(jù)庫還原到：

mysql> select * from hello;
+----+------+
| id | name |
+----+------+
|  1 | Andy |
|  2 | Lily |
|  3 | Will |
+----+------+
3 rows in set (0.00 sec)

之后執(zhí)行的 SQL：

update hello set name = 'David' where id = 3;
insert hello (name) values ('Frank');
delete from hello where id = 2;

不管是使用 STATEMENT 還是 ROW栏尚，mysqlbinlog 命令都可以將 binlog 增量恢復到數(shù)據(jù)庫 [doc ]起愈。

觀察 binlog 可以看到，從最開始的 update hello set name = 'David' where id = 3; 到最終的 delete from hello where id = 2;，時間上從 "2018-06-17 22:54:13" 到 "2018-06-17 22:56:44"抬虽，所以基于時間點恢復官觅，命令如下：

$ sudo mysqlbinlog --start-datetime="2018-06-17 22:54:13" --stop-datetime="2018-06-17 22:56:44" mysql-bin.000002 | mysql -uroot -p123456

binlog 的事件位置號是從 "154" 到 "956"，但需要注意的是用 --start-position 和 --stop-position 指定位置點范圍阐污，邏輯上對應的是 start <= position < stop休涤，所以基于時間點恢復，命令如下：

$ sudo mysqlbinlog --start-position=154 --stop-position=957 mysql-bin.000002 | mysql -uroot -p123456

兩種方式任意執(zhí)行笛辟，都能將數(shù)據(jù)恢復到：

mysql> select * from hello;
+----+-------+
| id | name  |
+----+-------+
|  1 | Andy  |
|  3 | David |
|  4 | Frank |
+----+-------+
3 rows in set (0.00 sec)

使用 binlog2sql 閃回

binlog2sql功氨，作者為曹單鋒，大眾點評 DBA手幢。binlog2sql捷凄，從 MySQL binlog 解析出你要的 SQL。根據(jù)不同選項围来，你可以得到原始 SQL跺涤、回滾 SQL、去除主鍵的 INSERT SQL 等监透。binlog2sql桶错，底層實現(xiàn)依賴 python-mysql-replication，由該庫完成 MySQL 復制協(xié)議和 binlog 格式的解析胀蛮。

$ python binlog2sql/binlog2sql.py -h192.168.2.107 -uroot -p123456 --start-position=154 --stop-position=957 --start-file='mysql-bin.000002'
UPDATE `testdb`.`hello` SET `id`=3, `name`='David' WHERE `id`=3 AND `name`='Will' LIMIT 1; #start 4 end 411 time 2018-06-17 22:54:13
INSERT INTO `testdb`.`hello`(`id`, `name`) VALUES (4, 'Frank'); #start 442 end 684 time 2018-06-17 22:55:47
DELETE FROM `testdb`.`hello` WHERE `id`=2 AND `name`='Bill' LIMIT 1; #start 715 end 956 time 2018-06-17 22:56:44

生成回滾 sql：

$ python binlog2sql/binlog2sql.py --flashback -h192.168.2.107 -uroot -p123456 --start-position=154 --stop-position=956 --start-file='mysql-bin.000002'
INSERT INTO `testdb`.`hello`(`id`, `name`) VALUES (2, 'Bill'); #start 715 end 956 time 2018-06-17 22:56:44
DELETE FROM `testdb`.`hello` WHERE `id`=4 AND `name`='Frank' LIMIT 1; #start 442 end 684 time 2018-06-17 22:55:47
UPDATE `testdb`.`hello` SET `id`=3, `name`='Will' WHERE `id`=3 AND `name`='David' LIMIT 1; #start 154 end 411 time 2018-06-17 22:54:13

閃回的現(xiàn)實原理很簡單牛曹，先通過 MySQL 復制協(xié)議的 com-binlog-dump 命令 dump 出 binlog，然后按照 binlog 的格式規(guī)范解析 binlog醇滥，將 binlog 轉換成 SQL黎比，再將這些 SQL 轉換反向邏輯的 SQL，最后再倒序執(zhí)行鸳玩。具體可以看阅虫，binlog2sql 作者的文章 [ref ]。

Java 解析 binlog

上文中的 binlog2sql 其實底層依賴 python-mysql-replication 庫不跟，這是 Python 庫颓帝。如果想使用 Java 解析 binlog 可以使用 mysql-binlog-connector-java（github）庫。目前開源的 CDC 工具窝革，如 Zendesk maxwell购城、Redhat debezium、LinkedIn Databus 等都底層依賴 mysql-binlog-connector-java 或者其前身 open-replicator虐译。使用 mysql-binlog-connector-java 的示例代碼如下：

BinaryLogClient client = new BinaryLogClient("192.168.2.107", 3306, "root", "123456");
client.setBinlogFilename("mysql-bin.000001");
client.setBinlogPosition(4);
client.setBlocking(false);
client.registerEventListener(event -> {
    System.out.println(event);
});
client.connect();

輸出（省略部分內(nèi)容）：

...
Event{header=EventHeaderV4{timestamp=1529247253000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=346, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247253000, eventType=EXT_UPDATE_ROWS, serverId=1, headerLength=19, dataLength=46, nextPosition=411, flags=0}, data=UpdateRowsEventData{tableId=110, includedColumnsBeforeUpdate={0, 1}, includedColumns={0, 1}, rows=[
    {before=[3, Will], after=[3, David]}
]}}
...
Event{header=EventHeaderV4{timestamp=1529247347000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=634, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247347000, eventType=EXT_WRITE_ROWS, serverId=1, headerLength=19, dataLength=31, nextPosition=684, flags=0}, data=WriteRowsEventData{tableId=110, includedColumns={0, 1}, rows=[
    [4, Frank]
]}}
...
Event{header=EventHeaderV4{timestamp=1529247404000, eventType=TABLE_MAP, serverId=1, headerLength=19, dataLength=34, nextPosition=907, flags=0}, data=TableMapEventData{tableId=110, database='testdb', table='hello', columnTypes=8, 15, columnMetadata=0, 40, columnNullability={1}}}
Event{header=EventHeaderV4{timestamp=1529247404000, eventType=EXT_DELETE_ROWS, serverId=1, headerLength=19, dataLength=30, nextPosition=956, flags=0}, data=DeleteRowsEventData{tableId=110, includedColumns={0, 1}, rows=[
    [2, Bill]
]}}

轉自
https://nullwy.me/2018/06/mysql-binlog/

?著作權歸作者所有,轉載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末瘪板，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子漆诽，更是在濱河造成了極大的恐慌侮攀，老刑警劉巖锣枝，帶你破解...
沈念sama閱讀 216,372評論 6贊 498
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異兰英，居然都是意外死亡撇叁，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 92,368評論 3贊 392
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門畦贸，熙熙樓的掌柜王于貴愁眉苦臉地迎上來陨闹，“玉大人，你說我怎么就攤上這事薄坏∏骼鳎” “怎么了？”我有些...
開封第一講書人閱讀 162,415評論 0贊 353
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵颤殴，是天一觀的道長觅廓。經(jīng)常有香客問我鼻忠，道長涵但，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 58,157評論 1贊 292
?港島之戀（遺憾婚禮）
正文為了忘掉前任帖蔓，我火速辦了婚禮矮瘟，結果婚禮上，老公的妹妹穿的比我還像新娘塑娇。我一直安慰自己澈侠，他們只是感情好，可當我...
茶點故事閱讀 67,171評論 6贊 388
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布埋酬。她就那樣靜靜地躺著哨啃，像睡著了一般。火紅的嫁衣襯著肌膚如雪写妥。梳的紋絲不亂的頭發(fā)上拳球，一...
開封第一講書人閱讀 51,125評論 1贊 297
城市分裂傳說
那天，我揣著相機與錄音珍特，去河邊找鬼祝峻。笑死，一個胖子當著我的面吹牛扎筒，可吹牛的內(nèi)容都是我干的莱找。我是一名探鬼主播，決...
沈念sama閱讀 40,028評論 3贊 417
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼嗜桌，長吁一口氣：“原來是場噩夢啊……” “哼奥溺！你這毒婦竟也來了？” 一聲冷哼從身側響起骨宠，我...
開封第一講書人閱讀 38,887評論 0贊 274
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤谚赎，失蹤者是張志新（化名）和其女友劉穎淫僻，沒想到半個月后，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體壶唤，經(jīng)...
沈念sama閱讀 45,310評論 1贊 310
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡雳灵，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 37,533評論 2贊 332
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了闸盔。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片悯辙。...
茶點故事閱讀 39,690評論 1贊 348
活死人
序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖迎吵，靈堂內(nèi)的尸體忽然破棺而出躲撰，到底是詐尸還是另有隱情，我是刑警寧澤击费，帶...
沈念sama閱讀 35,411評論 5贊 343
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布拢蛋，位于F島的核電站，受9級特大地震影響蔫巩，放射性物質(zhì)發(fā)生泄漏谆棱。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點故事閱讀 41,004評論 3贊 325
男人毒藥：我在死后第九天來索命
文/蒙蒙一圆仔、第九天我趴在偏房一處隱蔽的房頂上張望垃瞧。院中可真熱鬧，春花似錦坪郭、人聲如沸个从。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,659評論 0贊 22
一樁弒父案歪沃，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽嗦锐。三九已至，卻和暖如春沪曙，著一層夾襖步出監(jiān)牢的瞬間奕污，已是汗流浹背。一陣腳步聲響...
開封第一講書人閱讀 32,812評論 1贊 268
情欲美人皮
我被黑心中介騙來泰國打工珊蟀，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留菊值，地道東北人。一個月前我還...
沈念sama閱讀 47,693評論 2贊 368
代替公主和親
正文我出身青樓育灸，卻偏偏與公主長得像腻窒，于是被迫代替她去往敵國和親。傳聞我的和親對象是個殘疾皇子磅崭，可洞房花燭夜當晚...
茶點故事閱讀 44,577評論 2贊 353

MySQL binlog：格式挨务、增量恢復、閃回玉组、Java 解析