1，閃回原理

【binlog】MySQL binlog以event的形式，記錄了MySQL server從啟用binlog以來所有的變更信息，能夠幫助重現(xiàn)這之間的所有變化。MySQL引入binlog主要有兩個目的：一是為了主從復(fù)制；二是某些備份還原操作后需要重新應(yīng)用binlog。

有三種可選的binlog格式，各有優(yōu)缺點：

• statement：基于SQL語句的模式，binlog數(shù)據(jù)量小，但是某些語句和函數(shù)在復(fù)制過程可能導(dǎo)致數(shù)據(jù)不一致甚至出錯；
• row：基于行的模式，記錄的是行的完整變化。很安全，但是binlog會比其他兩種模式大很多；
• mixed：混合模式，根據(jù)語句來選用是statement還是row模式；

利用binlog閃回，需要將binlog格式設(shè)置為row。row模式下，一條使用innodb的insert會產(chǎn)生如下格式的binlog：

# at 1129
#161225 23:15:38 server id 3773306082  end_log_pos 1197         Query   thread_id=1903021       exec_time=0     error_code=0
SET TIMESTAMP=1482678938/*!*/;
BEGIN
/*!*/;
# at 1197
#161225 23:15:38 server id 3773306082  end_log_pos 1245         Table_map: `test`.`user` mapped to number 290
# at 1245
#161225 23:15:38 server id 3773306082  end_log_pos 1352         Write_rows: table id 290 flags: STMT_END_FBINLOG '
muJfWBPiFOjgMAAAAN0EAAAAACIBAAAAAAEABHRlc3QABHVzZXIAAwMPEQMeAAAC
muJfWB7iFOjgawAAAEgFAAAAACIBAAAAAAEAAgAD//gBAAAABuWwj+i1tVhK1hH4AgAAAAblsI/p
krFYStYg+AMAAAAG5bCP5a2ZWE/onPgEAAAABuWwj+adjlhNeAD4BQAAAAJ0dFhRYJM=
'/*!*/;
# at 1352
#161225 23:15:38 server id 3773306082  end_log_pos 1379         Xid = 5327954
COMMIT/*!*/;

【閃回原理】既然binlog以event形式記錄了所有的變更信息，因此閃回就是把需要回滾的event，從后往前回滾回去即可。對于單個event的回滾，以表test.user為例：

mysql> show create table test.user\G
*************************** 1. row ***************************Table: user
Create Table: CREATE TABLE `user` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(10) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

• 對于delete操作，從binlog提取出delete信息，生成的回滾語句是insert。(注：為了方便解釋，用binlog2sql將原始binlog轉(zhuǎn)化成了可讀SQL)

 原始：DELETE FROM `test`.`user` WHERE `id`=1 AND `name`='小趙';回滾：INSERT INTO `test`.`user`(`id`, `name`) VALUES (1, '小趙');

• 對于insert操作，回滾SQL是delete。

 原始：INSERT INTO `test`.`user`(`id`, `name`) VALUES (2, '小錢');回滾：DELETE FROM `test`.`user` WHERE `id`=2 AND `name`='小錢';

• 對于update操作，回滾sql應(yīng)該交換SET和WHERE的值。

 原始：UPDATE `test`.`user` SET `id`=3, `name`='小李' WHERE `id`=3 AND `name`='小孫';回滾：UPDATE `test`.`user` SET `id`=3, `name`='小孫' WHERE `id`=3 AND `name`='小李';

2，閃回工具

【第一類】以patch形式集成到官方工具mysqlbinlog中。以彭提交的patch為代表。

• 上手成本低。mysqlbinlog原有的選項都能直接利用，只是多加了一個閃回選項。閃回特性未來有可能被官方收錄。
• 支持離線解析。
• 兼容性差、項目活躍度不高。由于binlog格式的變動，如果閃回工具作者不及時對補丁升級，則閃回工具將無法使用。目前已有多位人員分別針對mysql5.5，5.6，5.7開發(fā)了patch，部分項目代碼公開，但總體上活躍度都不高。
• 難以添加新功能，實戰(zhàn)效果欠佳。在實戰(zhàn)中，經(jīng)常會遇到現(xiàn)有patch不滿足需求的情況，比如要加個表過濾，很簡單的一個需求，代碼改動也不會大，但對大部分DBA來說，改mysql源碼還是很困難的事。
• 安裝稍顯麻煩。需要對mysql源碼打補丁再編譯生成。

【第二類】獨立工具，通過偽裝成slave拉取binlog來進行處理。以binlog2sql為代表。

• 兼容性好。偽裝成slave拉binlog這項技術(shù)在業(yè)界應(yīng)用的非常廣泛，多個開發(fā)語言都有這樣的活躍項目，MySQL版本的兼容性由這些項目搞定，閃回工具的兼容問題不再突出。
• 添加新功能的難度小。更容易被改造成DBA自己喜歡的形式。更適合實戰(zhàn)。
• 安裝和使用簡單。
• 必須開啟MySQL server。

【第三類】簡單腳本。先用mysqlbinlog解析出文本格式的binlog，再根據(jù)回滾原理用正則進行匹配并替換。

• 腳本寫起來方便，往往能快速搞定某個特定問題。
• 安裝和使用簡單。
• 支持離線解析。
• 通用性不好。
• 可靠性不好。

【關(guān)于DDL的閃回】如果誤操作是DDL的話，是無法利用binlog做快速回滾的，因為即使在row模式下，binlog對于DDL操作也不會記錄每行數(shù)據(jù)的變化。要實現(xiàn)DDL快速回滾，必須修改MySQL源碼，使得在執(zhí)行DDL前先備份老數(shù)據(jù)。目前有多個mysql定制版本實現(xiàn)了DDL閃回特性，阿里林曉斌團隊提交了patch給MySQL官方，MariaDB預(yù)計在不久后加入包含DDL的flashback特性。DDL閃回的副作用是會增加額外存儲。

3，使用方式

真實的閃回場景中，最關(guān)鍵的是能快速篩選出真正需要回滾的SQL。使用開源工具binlog2sql來進行實戰(zhàn)演練。binlog2sql由美團點評DBA團隊(上海)出品。

https://github.com/danfengcao/binlog2sql

MySQL server必須設(shè)置以下參數(shù)：

[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full

systemctl restart mysqld

user需要的最小權(quán)限集合：

select, super/replication client, replication slave建議授權(quán)
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 

• select：需要讀取server端information_schema.COLUMNS表，獲取表結(jié)構(gòu)的元信息，拼接成可視化的sql語句。
• super/replication client：兩個權(quán)限都可以，需要執(zhí)行'SHOW MASTER STATUS', 獲取server端的binlog列表。
• replication slave：通過BINLOG_DUMP協(xié)議獲取binlog內(nèi)容的權(quán)限。

【解析出標(biāo)準(zhǔn)SQL】

shell> python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -t test3 test4 --start-file='mysql-bin.000002'輸出：
INSERT INTO `test`.`test3`(`addtime`, `data`, `id`) VALUES ('2016-12-10 13:03:38', 'english', 4); #start 570 end 736
UPDATE `test`.`test3` SET `addtime`='2016-12-10 12:00:00', `data`='中文', `id`=3 WHERE `addtime`='2016-12-10 13:03:22' AND `data`='中文' AND `id`=3 LIMIT 1; #start 763 end 954
DELETE FROM `test`.`test3` WHERE `addtime`='2016-12-10 13:03:38' AND `data`='english' AND `id`=4 LIMIT 1; #start 981 end 1147

• mysql連接配置：

-h host; -P port; -u user; -p password

• 解析模式：

--stop-never 持續(xù)解析binlog?？蛇x。默認(rèn)False，同步至執(zhí)行命令時最新的binlog位置。-K, --no-primary-key 對INSERT語句去除主鍵?？蛇x。默認(rèn)False-B, --flashback 生成回滾SQL，可解析大文件，不受內(nèi)存限制?？蛇x。默認(rèn)False。與stop-never或no-primary-key不能同時添加。--back-interval -B模式下，每打印一千行回滾SQL，加一句SLEEP多少秒，如不想加SLEEP，請設(shè)為0。可選。默認(rèn)1.0。

• 解析范圍控制：

--start-file 起始解析文件，只需文件名，無需全路徑 。必須。--start-position/--start-pos 起始解析位置?？蛇x。默認(rèn)為start-file的起始位置。--stop-file/--end-file 終止解析文件?？蛇x。默認(rèn)為start-file同一個文件。若解析模式為stop-never，此選項失效。--stop-position/--end-pos 終止解析位置?？蛇x。默認(rèn)為stop-file的最末位置；若解析模式為stop-never，此選項失效。--start-datetime 起始解析時間，格式'%Y-%m-%d %H:%M:%S'?？蛇x。默認(rèn)不過濾。--stop-datetime 終止解析時間，格式'%Y-%m-%d %H:%M:%S'?？蛇x。默認(rèn)不過濾。

• 對象過濾：

-d, --databases 只解析目標(biāo)db的sql，多個庫用空格隔開，如-d db1 db2。可選。默認(rèn)為空。-t, --tables 只解析目標(biāo)table的sql，多張表用空格隔開，如-t tbl1 tbl2。可選。默認(rèn)為空。--only-dml 只解析dml，忽略ddl。可選。默認(rèn)False。--sql-type 只解析指定類型，支持INSERT, UPDATE, DELETE。多個類型用空格隔開，如--sql-type INSERT DELETE?？蛇x。默認(rèn)為增刪改都解析。用了此參數(shù)但沒填任何類型，則三者都不解析。

【解析出回滾SQL】

shell> python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttest3 --start-file='mysql-bin.000002' --start-position=763 --stop-position=1147輸出：
INSERT INTO `test`.`test3`(`addtime`, `data`, `id`) VALUES ('2016-12-10 13:03:38', 'english', 4); #start 981 end 1147
UPDATE `test`.`test3` SET `addtime`='2016-12-10 13:03:22', `data`='中文', `id`=3 WHERE `addtime`='2016-12-10 12:00:00' AND `data`='中文' AND `id`=3 LIMIT 1; #start 763 end 954

4，閃回實戰(zhàn)

背景：小明在 17:00 時誤刪了ry庫sys_dict_type表的全部數(shù)據(jù)，需要緊急回滾。

mysql> select * from sys_config;
...
10 rows in set (0.00 sec)

mysql> delete from sys_config;
Query OK, 10 rows affected (0.01 sec)
mysql> select * from sys_config;
Empty set (0.00 sec)

• 直接查看二進制日志內(nèi)容，發(fā)現(xiàn)亂碼。

mysqlbinlog --no-defaults mysql-bin.000001

• 登錄mysql，查看目前的binlog文件。

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       154 |
+------------------+-----------+
1 row in set (0.00 sec)

• 最新的binlog文件是mysql-bin.000001。目標(biāo)是篩選出需要回滾的SQL，由于誤操作人只知道大致的誤操作時間，首先根據(jù)時間做一次過濾。只需要解析test庫user表。(注：如果有多個sql誤操作，則生成的binlog可能分布在多個文件，需解析多個文件)。

python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'12345678' -dry -tsys_config --start-file='mysql-bin.000001' --start-datetime='2025-01-13 11:44:00' --stop-datetime='2025-01-13 18:00:00' > sql.log

DELETE FROM `ry`.`sys_config` WHERE `config_id`=1 AND `config_name`='主框架頁-默認(rèn)皮膚樣式名稱' AND `config_key`='sys.index.skinName' AND `config_value`='skin-blue' AND `config_type`='Y' AND `create_by`='admin' AND `create_time`='2018-03-16 11:33:00' AND `update_by`='ry' AND `update_time`='2018-03-16 11:33:00' AND `remark`='藍(lán)色 skin-blue、綠色 skin-green、紫色 skin-purple、紅色 skin-red、黃色 skin-yellow' LIMIT 1; #start 4 end 1014 time 2025-01-13 17:37:02
DELETE FROM `ry`.`sys_config` WHERE `config_id`=2 AND `config_name`='用戶管理-賬號初始密碼' AND `config_key`='sys.user.initPassword' AND `config_value`='123456' AND `config_type`='Y' AND `create_by`='admin' AND `create_time`='2018-03-16 11:33:00' AND `update_by`='ry' AND `update_time`='2018-03-16 11:33:00' AND `remark`='初始化密碼 123456' LIMIT 1; #start 4 end 1014 time 2025-01-13 17:37:02
DELETE FROM `ry`.`sys_config` WHERE `config_id`=3 AND `config_name`='主框架頁-側(cè)邊欄主題' AND `config_key`='sys.index.sideTheme' AND `config_value`='theme-dark' AND `config_type`='Y' AND `create_by`='admin' AND `create_time`='2018-03-16 11:33:00' AND `update_by`='ry' AND `update_time`='2018-03-16 11:33:00' AND `remark`='深黑主題theme-dark，淺色主題theme-light，深藍(lán)主題theme-blue' LIMIT 1; #start 4 end 1014 time 2025-01-13 17:37:02

• 著重關(guān)注上面的 #start 4 end 1014，執(zhí)行轉(zhuǎn)換操作：

python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'12345678' -dry -tsys_config --start-file='mysql-bin.000001' --start-position=4 --stop-position=1014 -B > rollback.sql

INSERT INTO `ry`.`sys_config`(`config_id`, `config_name`, `config_key`, `config_value`, `config_type`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (4, '賬號自助-是否開啟用戶注冊功能', 'sys.account.registerUser', 'false', 'Y', 'admin', '2018-03-16 11:33:00', 'ry', '2018-03-16 11:33:00', '是否開啟注冊用戶功能'); #start 4 end 1014 time 2025-01-13 17:37:02
INSERT INTO `ry`.`sys_config`(`config_id`, `config_name`, `config_key`, `config_value`, `config_type`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (3, '主框架頁-側(cè)邊欄主題', 'sys.index.sideTheme', 'theme-dark', 'Y', 'admin', '2018-03-16 11:33:00', 'ry', '2018-03-16 11:33:00', '深黑主題theme-dark，淺色主題theme-light，深藍(lán)主題theme-blue'); #start 4 end 1014 time 2025-01-13 17:37:02
INSERT INTO `ry`.`sys_config`(`config_id`, `config_name`, `config_key`, `config_value`, `config_type`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (2, '用戶管理-賬號初始密碼', 'sys.user.initPassword', '123456', 'Y', 'admin', '2018-03-16 11:33:00', 'ry', '2018-03-16 11:33:00', '初始化密碼 123456'); #start 4 end 1014 time 2025-01-13 17:37:02

• 與業(yè)務(wù)方確認(rèn)回滾sql沒問題，執(zhí)行回滾語句。登錄mysql，確認(rèn)回滾成功。

mysql -h127.0.0.1 -P3306 -uroot -p'12345678' < /root/rollback.sql

mysql> select * from sys_config;
...
10 rows in set (0.00 sec)