MySQL閃回原理與實(shí)戰(zhàn)

DBA或開發(fā)人員士嚎，有時(shí)會(huì)誤刪或者誤更新數(shù)據(jù)米奸，如果是線上環(huán)境并且影響較大堂飞，就需要能快速回滾灌旧。傳統(tǒng)恢復(fù)方法是利用備份重搭實(shí)例绑咱，再應(yīng)用去除錯(cuò)誤sql后的binlog來恢復(fù)數(shù)據(jù)。此法費(fèi)時(shí)費(fèi)力枢泰，甚至需要停機(jī)維護(hù)羡玛，并不適合快速回滾。也有團(tuán)隊(duì)利用LVM快照來縮短恢復(fù)時(shí)間宗苍，但快照的缺點(diǎn)是會(huì)影響mysql的性能稼稿。

MySQL閃回(flashback)利用binlog直接進(jìn)行回滾，能快速恢復(fù)且不用停機(jī)讳窟。本文將介紹閃回原理让歼，給出筆者的實(shí)戰(zhàn)經(jīng)驗(yàn)，并對(duì)現(xiàn)存的閃回工具作比較丽啡。

開胃菜

某天谋右，小明因種種原因，誤刪了大批線上用戶表的數(shù)據(jù)补箍。他急忙找到公司DBA請(qǐng)求幫助改执，“客服電話已被打爆，大量用戶投訴無法登陸坑雅，領(lǐng)導(dǎo)非常惱火辈挂。請(qǐng)問多久能恢復(fù)數(shù)據(jù)？”DBA一臉懵逼裹粤，沉默十秒后终蒂，伸出一根手指∫Ｋ撸“你的意思是一分鐘就能恢復(fù)拇泣？太好了“猓”小明終于有些放松霉翔，露出了一絲笑容“浚“不债朵，我們中有個(gè)人將會(huì)離開公司∶ㄧ裕”DBA沉痛的說道葱弟。

勿讓悲劇發(fā)生，盡早將此文轉(zhuǎn)給公司DBA猜丹。

閃回原理

binlog概述

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

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

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

利用binlog閃回怠苔，需要將binlog格式設(shè)置為row同廉。row模式下，一條使用innodb的insert會(huì)產(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_F

BINLOG '
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迫肖，從后往前回滾回去即可。

對(duì)于單個(gè)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

• 對(duì)于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, '小趙');
  
  

• 對(duì)于insert操作，回滾SQL是delete奶段。

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

• 對(duì)于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`='小李';
  
  

閃回實(shí)戰(zhàn)

真實(shí)的閃回場(chǎng)景中痹籍，最關(guān)鍵的是能快速篩選出真正需要回滾的SQL。

我們使用開源工具binlog2sql來進(jìn)行實(shí)戰(zhàn)演練晦鞋。binlog2sql由美團(tuán)點(diǎn)評(píng)DBA團(tuán)隊(duì)(上海)出品蹲缠，多次在線上環(huán)境做快速回滾。

首先我們安裝binlog2sql：

shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
shell> pip install -r requirements.txt

背景：小明在11:44時(shí)誤刪了test庫(kù)user表大批的數(shù)據(jù)悠垛，需要緊急回滾线定。

test庫(kù)user表原有數(shù)據(jù)
mysql> select * from user;
+----+--------+---------------------+
| id | name   | addtime             |
+----+--------+---------------------+
|  1 | 小趙   | 2013-11-11 00:04:33 |
|  2 | 小錢   | 2014-11-11 00:04:48 |
|  3 | 小孫   | 2016-11-11 20:25:00 |
|  4 | 小李   | 2013-11-11 00:00:00 |
.........
+----+--------+---------------------+
16384 rows in set (0.04 sec)

11:44時(shí)，user表大批數(shù)據(jù)被誤刪除确买。與此同時(shí)斤讥，正常業(yè)務(wù)數(shù)據(jù)是在繼續(xù)寫入的
mysql> delete from user where addtime>'2014-01-01';
Query OK, 16128 rows affected (0.18 sec)

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|      261 |
+----------+

恢復(fù)數(shù)據(jù)步驟：

1. 登錄mysql，查看目前的binlog文件

   mysql> show master logs;
   +------------------+-----------+
   | Log_name         | File_size |
   +------------------+-----------+
   | mysql-bin.000053 | 168652863 |
   | mysql-bin.000054 |    504549 |
   +------------------+-----------+
   

2. 最新的binlog文件是mysql-bin.000054湾趾。我們的目標(biāo)是篩選出需要回滾的SQL芭商，由于誤操作人只知道大致的誤操作時(shí)間派草，我們首先根據(jù)時(shí)間做一次過濾。只需要解析test庫(kù)user表铛楣。(注：如果有多個(gè)sql誤操作近迁，則生成的binlog可能分布在多個(gè)文件，需解析多個(gè)文件)

   shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -tuser --start-file='mysql-bin.000054' --start-datetime='2016-12-26 11:44:00' --stop-datetime='2016-12-26 11:50:00' > /tmp/raw.sql
   
   raw.sql輸出：
   DELETE FROM `test`.`user` WHERE `addtime`='2014-11-11 00:04:48' AND `id`=2 AND `name`='小錢' LIMIT 1; #start 257427 end 265754 time 2016-12-26 11:44:56
   DELETE FROM `test`.`user` WHERE `addtime`='2015-11-11 20:25:00' AND `id`=3 AND `name`='小孫' LIMIT 1; #start 257427 end 265754 time 2016-12-26 11:44:56
   ...
   DELETE FROM `test`.`user` WHERE `addtime`='2016-12-14 23:09:07' AND `id`=24530 AND `name`='tt' LIMIT 1; #start 257427 end 504272 time 2016-12-26 11:44:56
   INSERT INTO `test`.`user`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:33', 32722, '小王'); #start 504299 end 504522 time 2016-12-26 11:49:42
   ...
   

3. 根據(jù)位置信息簸州，我們確定了誤操作sql來自同一個(gè)事務(wù)鉴竭，準(zhǔn)確位置在257427-504272之間(binlog2sql對(duì)于同一個(gè)事務(wù)會(huì)輸出同樣的start position)。再根據(jù)位置過濾岸浑，使用 -B 選項(xiàng)生成回滾sql搏存，檢查回滾sql是否正確。(注：真實(shí)場(chǎng)景下助琐，生成的回滾SQL經(jīng)常會(huì)需要進(jìn)一步篩選祭埂。結(jié)合grep、編輯器等)

   shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -tuser --start-file='mysql-bin.000054' --start-position=257427 --stop-position=504272 -B > /tmp/rollback.sql
   
   rollback.sql 輸出：
   INSERT INTO `test`.`user`(`addtime`, `id`, `name`) VALUES ('2016-12-14 23:09:07', 24530, 'tt'); #start 257427 end 504272 time 2016-12-26 11:44:56
   INSERT INTO `test`.`user`(`addtime`, `id`, `name`) VALUES ('2016-12-12 00:00:00', 24529, '小李'); #start 257427 end 504272 time 2016-12-26 11:44:56
   ...
   INSERT INTO `test`.`user`(`addtime`, `id`, `name`) VALUES ('2014-11-11 00:04:48', 2, '小錢'); #start 257427 end 265754 time 2016-12-26 11:44:56
   
   shell> wc -l /tmp/rollback.sql
   16128 /tmp/rollback.sql
   

4. 與業(yè)務(wù)方確認(rèn)回滾sql沒問題兵钮，執(zhí)行回滾語句蛆橡。登錄mysql，確認(rèn)回滾成功掘譬。

   shell> mysql -h127.0.0.1 -P3306 -uadmin -p'admin' < /tmp/rollback.sql
   
   mysql> select count(*) from user;
   +----------+
   | count(*) |
   +----------+
   |    16389 |
   +----------+
   

TIPS

• 閃回的目標(biāo)：快速篩選出真正需要回滾的數(shù)據(jù)泰演。
• 先根據(jù)庫(kù)、表葱轩、時(shí)間做一次過濾睦焕，再根據(jù)位置做更準(zhǔn)確的過濾。
• 由于數(shù)據(jù)一直在寫入靴拱，要確崩埃回滾sql中不包含其他數(shù)據(jù)⊥嗫唬可根據(jù)是否是同一事務(wù)本谜、誤操作行數(shù)、字段值的特征等等來幫助判斷偎窘。
• 執(zhí)行回滾sql時(shí)如有報(bào)錯(cuò)乌助，需要查實(shí)具體原因，一般是因?yàn)閷?duì)應(yīng)的數(shù)據(jù)已發(fā)生變化陌知。由于是嚴(yán)格的行模式他托，只要有唯一鍵(包括主鍵)存在，就只會(huì)報(bào)某條數(shù)據(jù)不存在的錯(cuò)仆葡，不必?fù)?dān)心會(huì)更新不該操作的數(shù)據(jù)赏参。業(yè)務(wù)如果有特殊邏輯，數(shù)據(jù)回滾可能會(huì)帶來影響。
• 如果只回滾某張表登刺，并且該表有關(guān)聯(lián)表籽腕，關(guān)聯(lián)表并不會(huì)被回滾，需與業(yè)務(wù)方溝通清楚纸俭。

哪些數(shù)據(jù)需要回滾皇耗，讓業(yè)務(wù)方來判斷！

閃回工具

MySQL閃回特性最早由阿里彭立勛開發(fā)揍很，彭在2012年給官方提交了一個(gè)patch郎楼，并對(duì)閃回設(shè)計(jì)思路做了說明(設(shè)計(jì)思路很有啟發(fā)性，強(qiáng)烈推薦閱讀)窒悔。但是因?yàn)榉N種原因呜袁，業(yè)內(nèi)安裝這個(gè)patch的團(tuán)隊(duì)至今還是少數(shù)，真正應(yīng)用到線上的更是少之又少简珠。彭之后阶界，又有多位人員針對(duì)不同mysql版本不同語言開發(fā)了閃回工具，原理用的都是彭的思路聋庵。

我將這些閃回工具按實(shí)現(xiàn)方式分成了三類膘融。

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

  優(yōu)點(diǎn)

  • 上手成本低氧映。mysqlbinlog原有的選項(xiàng)都能直接利用，只是多加了一個(gè)閃回選項(xiàng)脱货。閃回特性未來有可能被官方收錄岛都。
  • 支持離線解析。

  缺點(diǎn)

  • 兼容性差振峻、項(xiàng)目活躍度不高臼疫。由于binlog格式的變動(dòng)，如果閃回工具作者不及時(shí)對(duì)補(bǔ)丁升級(jí)扣孟，則閃回工具將無法使用多矮。目前已有多位人員分別針對(duì)mysql5.5，5.6哈打，5.7開發(fā)了patch，部分項(xiàng)目代碼公開讯壶，但總體上活躍度都不高料仗。
  • 難以添加新功能，實(shí)戰(zhàn)效果欠佳伏蚊。在實(shí)戰(zhàn)中立轧，經(jīng)常會(huì)遇到現(xiàn)有patch不滿足需求的情況，比如要加個(gè)表過濾，很簡(jiǎn)單的一個(gè)需求氛改，代碼改動(dòng)也不會(huì)大帐萎，但對(duì)大部分DBA來說，改mysql源碼還是很困難的事胜卤。
  • 安裝稍顯麻煩疆导。需要對(duì)mysql源碼打補(bǔ)丁再編譯生成。

  這些缺點(diǎn)葛躏，可能都是閃回沒有流行開來的原因澈段。

• 第二類是獨(dú)立工具，通過偽裝成slave拉取binlog來進(jìn)行處理舰攒。以binlog2sql為代表败富。

  優(yōu)點(diǎn)

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

  缺點(diǎn)

  • 必須開啟MySQL server。

• 第三類是簡(jiǎn)單腳本材彪。先用mysqlbinlog解析出文本格式的binlog观挎，再根據(jù)回滾原理用正則進(jìn)行匹配并替換。

  優(yōu)點(diǎn)

  • 腳本寫起來方便段化，往往能快速搞定某個(gè)特定問題嘁捷。
  • 安裝和使用簡(jiǎn)單。
  • 支持離線解析显熏。

  缺點(diǎn)

  • 通用性不好雄嚣。
  • 可靠性不好。

就目前的閃回工具而言喘蟆，線上環(huán)境的閃回缓升，筆者建議使用binlog2sql，離線解析使用mysqlbinlog蕴轨。

關(guān)于DDL的flashback

本文所述的flashback僅針對(duì)DML語句的快速回滾港谊。但如果誤操作是DDL的話，是無法利用binlog做快速回滾的橙弱，因?yàn)榧词乖趓ow模式下歧寺，binlog對(duì)于DDL操作也不會(huì)記錄每行數(shù)據(jù)的變化燥狰。要實(shí)現(xiàn)DDL快速回滾，必須修改MySQL源碼斜筐，使得在執(zhí)行DDL前先備份老數(shù)據(jù)龙致。目前有多個(gè)mysql定制版本實(shí)現(xiàn)了DDL閃回特性，阿里林曉斌團(tuán)隊(duì)提交了patch給MySQL官方顷链，MariaDB預(yù)計(jì)在不久后加入包含DDL的flashback特性目代。DDL閃回的副作用是會(huì)增加額外存儲(chǔ)≡塘剩考慮到其應(yīng)用頻次實(shí)在過低像啼，本文不做詳述，有興趣的同學(xué)可以自己去了解潭苞，重要的幾篇文章我在參考資料中做了引用忽冻。

有任何問題，或有mysql閃回相關(guān)的優(yōu)秀工具優(yōu)秀文章遺漏此疹，煩請(qǐng)告知僧诚。 danfengcao.info@gmail.com

參考資料

[1] MySQL Internals Manual , Chapter 20 The Binary Log

[2] 彭立勛，MySQL下實(shí)現(xiàn)閃回的設(shè)計(jì)思路

[3] Lixun Peng, Provide the flashback feature by binlog

[4] 王廣友蝗碎，mysqlbinlog flashback 5.6完全使用手冊(cè)與原理

[5] 姜承堯, 拿走不謝湖笨，F(xiàn)lashback for MySQL 5.7

[6] 林曉斌, MySQL閃回方案討論及實(shí)現(xiàn)

[7] xiaobin lin, flashback from binlog for MySQL

[8] mariadb.com, AliSQL and some features that have made it into MariaDB Server

[9] danfengcao, binlog2sql: Parse MySQL binlog to SQL you want