溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
不知道大家之前對類似MySQL中如何使用binlog恢復(fù)或閃回?cái)?shù)據(jù)庫數(shù)據(jù)的文章有無了解，今天我在這里給大家再簡單的講講。感興趣的話就一起來看看正文部分吧，相信看完MySQL中如何使用binlog恢復(fù)或閃回?cái)?shù)據(jù)庫數(shù)據(jù)你一定會有所收獲的。
STATEMENT 格式的 binlog
要想開啟 binlog，需要在啟動 MySQL 時(shí)傳入 --log-bin 參數(shù)?；蛘咭部梢栽?MySQL 配置文件 /etc/my.cnf，設(shè)置 log_bin 開啟 binlog。MySQL 5.7 開始，開啟 binlog 后，--server-id 參數(shù)也必須指定，否則 MySQL云服務(wù)器會啟動失敗。
binlog_format 支持 STATEMENT, ROW, MIXED 三種格式，MySQL 5.5 和 5.6 默認(rèn)為 STATEMENT，MySQL 5.7.7 開始默認(rèn)為 ROW。若 SQL 使用 UUID(), RAND(), VERSION() 等函數(shù)，或者使用存儲過程、自定義函數(shù)，基于 STATEMENT 的主從復(fù)時(shí)，是不安全的（很多人可能會認(rèn)為 NOW(), CURRENT_TIMESTAMP 這些函數(shù)也是不安全的，事實(shí)上是安全的） [doc1, doc2 ]?；?ROW 的主從復(fù)制，是最安全的復(fù)制方式。
現(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/，將會生成相應(yīng)的 binlog 文件，mysql-bin.index 和 mysql-bin.000001。.index 后綴的文件保存全部 binlog 文件名。mysql-bin.000001 文件記錄 binlog 內(nèi)容。每次 MySQL 啟動或者 flush 日志，都將按序號創(chuàng)建一個新的日志文件。另外，當(dāng)日志文件大小超過 max_binlog_size 時(shí)，也會創(chuàng)建一個新的日志文件。
現(xiàn)在來試一試 binlog 功能。假設(shè)在 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 為二進(jìn)制文件，需要使用 mysqlbinlog（doc, man）命令查看：
$ sudo mysqlbinlog /var/lib/mysql/mysql-bin.000001 # 直接在mysql云服務(wù)器上讀取 binlog 文件 $ mysqlbinlog -R -h292.168.2.107 -uroot -p123456 mysql-bin.000001 # 或者，遠(yuǎn)程讀取 binlog 文件
執(zhí)行 update 后相應(yīng)新增的 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 后相應(yīng)新增的 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)相應(yīng)生成的 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)
相應(yīng)生成的 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 增量恢復(fù)
MySQL 邏輯備份通常會結(jié)合全量備份和增量備份，使用 mysqldump 定期全量備份數(shù)據(jù)庫，然后利用 binlog 保存增量數(shù)據(jù)?；謴?fù)數(shù)據(jù)時(shí)，就是用 mysqldump 備份的數(shù)據(jù)恢復(fù)到備份的時(shí)間點(diǎn)。數(shù)據(jù)庫在備份時(shí)間點(diǎn)到當(dāng)前時(shí)間的增量修改，則通過 mysqlbinlog 將 binlog 中的增量數(shù)據(jù)恢復(fù)到數(shù)據(jù)庫?，F(xiàn)在假設(shè)已經(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 增量恢復(fù)到數(shù)據(jù)庫 [doc ]。
觀察 binlog 可以看到，從最開始的 update hello set name = 'David' where id = 3; 到最終的 delete from hello where id = 2;，時(shí)間上從 "2018-06-17 22:54:13" 到 "2018-06-17 22:56:44"，所以基于時(shí)間點(diǎn)恢復(fù)，命令如下：
$ 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 指定位置點(diǎn)范圍，邏輯上對應(yīng)的是 start <= position < stop，所以基于時(shí)間點(diǎn)恢復(fù)，命令如下：
$ sudo mysqlbinlog --start-position=154 --stop-position=957 mysql-bin.000002 | mysql -uroot -p123456
兩種方式任意執(zhí)行，都能將數(shù)據(jù)恢復(fù)到：
mysql> select * from hello; +----+-------+ | id | name | +----+-------+ | 1 | Andy | | 3 | David | | 4 | Frank | +----+-------+ 3 rows in set (0.00 sec)
使用 binlog2sql 閃回
binlog2sql，作者為曹單鋒，大眾點(diǎn)評 DBA。binlog2sql，從 MySQL binlog 解析出你要的 SQL。根據(jù)不同選項(xiàng)，你可以得到原始 SQL、回滾 SQL、去除主鍵的 INSERT SQL 等。binlog2sql，底層實(shí)現(xiàn)依賴 python-mysql-replication，由該庫完成 MySQL 復(fù)制協(xié)議和 binlog 格式的解析。
$ python binlog2sql/binlog2sql.py -h292.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 -h292.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)實(shí)原理很簡單，先通過 MySQL 復(fù)制協(xié)議的 com-binlog-dump 命令 dump 出 binlog，然后按照 binlog 的格式規(guī)范解析 binlog，將 binlog 轉(zhuǎn)換成 SQL，再將這些 SQL 轉(zhuǎn)換反向邏輯的 SQL，最后再倒序執(zhí)行。
Java 解析 binlog
上文中的 binlog2sql 其實(shí)底層依賴 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]
]}}看完MySQL中如何使用binlog恢復(fù)或閃回?cái)?shù)據(jù)庫數(shù)據(jù)這篇文章，大家覺得怎么樣？如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼