《Mysql實例MySQL閃回(flashback)原理與實戰》要點：
本文介紹了Mysql實例MySQL閃回(flashback)原理與實戰，希望對您有用。如果有疑問，可以聯系我們。
MYSQL實例DBA或開發人員,有時會誤刪或者誤更新數據,如果是線上環境并且影響較大,就需要能快速回滾.傳統恢復辦法是利用備份重搭實例,再應用去除錯誤sql后的binlog來恢復數據.此法費時費力,甚至需要停機維護,并不適合快速回滾.也有團隊利用LVM快照來縮短恢復時間,但快照的缺點是會影響mysql的性能.
MYSQL實例MySQL閃回(flashback)利用binlog直接進行回滾,能快速恢復且不用停機.本文將介紹閃回原理,給出筆者的實戰經驗,并對現存的閃回工具作比擬.
MYSQL實例開胃菜
MYSQL實例某天,小明因種種原因,誤刪了大批線上用戶表的數據.他急忙找到公司DBA哀求幫助,“客服電話已被打爆,大量用戶投訴無法登陸,領導非常惱火.請問多久能恢復數據?”DBA一臉懵逼,沉默十秒后,伸出一根手指.“你的意思是一分鐘就能恢復?太好了.”小明終于有些放松,露出了一絲笑容.“不,我們中有個人將會離開公司.”DBA沉痛的說道.
MYSQL實例勿讓悲劇產生,盡早將此文轉給公司DBA.
MYSQL實例閃回原理
MYSQL實例binlog概述
MYSQL實例MySQL binlog以event的形式,記錄了MySQL server從啟用binlog以來所有的變更信息,能夠贊助重現這之間的所有變化.MySQL引入binlog主要有兩個目的：一是為了主從復制；二是某些備份還原操作后需要重新應用binlog.
MYSQL實例有三種可選的binlog格局,各有優缺點：
MYSQL實例利用binlog閃回,需要將binlog格式設置為row.row模式下,一條使用innodb的insert會產生如下格式的binlog：
MYSQL實例
# 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/*!*/;
MYSQL實例閃回原理
MYSQL實例既然binlog以event形式記錄了所有的變更信息,那么我們把必要回滾的event,從后往前回滾回去即可.
MYSQL實例對付單個event的回滾,我們以表test.user來演示原理
MYSQL實例
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
MYSQL實例對于delete操作,我們從binlog提取出delete信息,生成的回滾語句是insert.(注：為了便利解釋,我們用binlog2sql將原始binlog轉化成了可讀SQL)
MYSQL實例
原始：DELETE FROM `test`.`user` WHERE `id`=1 AND `name`='小趙';
回滾：INSERT INTO `test`.`user`(`id`, `name`) VALUES (1, '小趙');
MYSQL實例對付insert操作,回滾SQL是delete.
MYSQL實例
原始：INSERT INTO `test`.`user`(`id`, `name`) VALUES (2, '小錢');
回滾：DELETE FROM `test`.`user` WHERE `id`=2 AND `name`='小錢';
MYSQL實例對付update操作,回滾sql應該交換SET和WHERE的值.
MYSQL實例
原始：UPDATE `test`.`user` SET `id`=3, `name`='小李' WHERE `id`=3 AND `name`='小孫';
回滾：UPDATE `test`.`user` SET `id`=3, `name`='小孫' WHERE `id`=3 AND `name`='小李';
MYSQL實例閃回實戰
MYSQL實例真實的閃回場景中,最關鍵的是能快速篩選出真正必要回滾的SQL.
MYSQL實例我們使用開源對象binlog2sql來進行實戰演練.binlog2sql由美團點評DBA團隊(上海)出品,多次在線上環境做快速回滾.
MYSQL實例首先我們安裝binlog2sql：
MYSQL實例
shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
shell> pip install -r requirements.txt
MYSQL實例配景：小明在11:44時誤刪了test庫user表大批的數據,需要緊急回滾.
MYSQL實例test庫user表原有數據
MYSQL實例
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)
MYSQL實例11:44時,user表年夜批數據被誤刪除.與此同時,正常業務數據是在繼續寫入的
MYSQL實例
mysql> delete from user where addtime>'2014-01-01';
Query OK, 16128 rows affected (0.18 sec)
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
| 261 |
+----------+
MYSQL實例恢復數據步調：
MYSQL實例登錄mysql,查看目前的binlog文件
MYSQL實例
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000053 | 168652863 |
| mysql-bin.000054 | 504549 |
+------------------+-----------+
MYSQL實例最新的binlog文件是mysql-bin.000054.我們的目標是篩選出必要回滾的SQL,由于誤操作人只知道大致的誤操作時間,我們首先根據時間做一次過濾.只必要解析test庫user表.(注：如果有多個sql誤操作,則生成的binlog可能分布在多個文件,需解析多個文件)
MYSQL實例
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
...
MYSQL實例根據位置信息,我們確定了誤操作sql來自同一個事務,準確位置在257427-504272之間(binlog2sql對于同一個事務會輸出同樣的start position).再根據位置過濾,使用 -B 選項生成回滾sql,檢查回滾sql是否正確.(注：真實場景下,生成的回滾SQL經常會必要進一步篩選.結合grep、編輯器等)
MYSQL實例
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
MYSQL實例與業務方確認回滾sql沒問題,執行回滾語句.登錄mysql,確認回滾勝利.
MYSQL實例
shell> mysql -h127.0.0.1 -P3306 -uadmin -p'admin' < /tmp/rollback.sql
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
| 16389 |
+----------+
MYSQL實例TIPS
MYSQL實例再重復下最重要的兩點：篩選出正確SQL!溝通清楚!
MYSQL實例閃回對象
MYSQL實例MySQL閃回特性最早由阿里彭立勛開發,彭在2012年給官方提交了一個patch,并對閃回設計思路做了說明(設計思路很有啟發性,強烈保舉閱讀).但是因為種種原因,業內安裝這個patch的團隊至今還是少數,真正應用到線上的更是少之又少.彭之后,又有多位人員針對不同mysql版本不同語言開發了閃回工具,原理用的都是彭的思路.
MYSQL實例我將這些閃回對象按實現方式分成了三類.
MYSQL實例第一類是以patch形式集成到官方對象mysqlbinlog中.以彭提交的patch為代表.
MYSQL實例長處
MYSQL實例上手本錢低.mysqlbinlog原有的選項都能直接利用,只是多加了一個閃回選項.閃回特性未來有可能被官方收錄.
支持離線解析.
MYSQL實例缺點
MYSQL實例這些缺點,可能都是閃回沒有流行開來的原因.
MYSQL實例第二類是獨立工具,通過偽裝成slave拉取binlog來進行處置.以binlog2sql為代表.
MYSQL實例長處
MYSQL實例缺點
MYSQL實例必需開啟MySQL server.
MYSQL實例第三類是簡單腳本.先用mysqlbinlog解析出文本格局的binlog,再根據回滾原理用正則進行匹配并替換.
MYSQL實例長處
MYSQL實例缺點
MYSQL實例就目前的閃回工具而言,線上環境的閃回,筆者建議使用binlog2sql,離線解析使用mysqlbinlog.
MYSQL實例關于DDL的flashback
MYSQL實例本文所述的flashback僅針對DML語句的快速回滾.但如果誤操作是DDL的話,是無法利用binlog做快速回滾的,因為即使在row模式下,binlog對于DDL操作也不會記錄每行數據的變化.要實現DDL快速回滾,必須修改MySQL源碼,使得在執行DDL前先備份老數據.目前有多個mysql定制版本實現了DDL閃回特性,阿里林曉斌團隊提交了patch給MySQL官方,MariaDB預計在不久后加入包括DDL的flashback特性.DDL閃回的副作用是會增加額外存儲.考慮到其應用頻次實在過低,本文不做詳述,有興趣的同學可以自己去了解,重要的幾篇文章我在參考資料中做了引用.
MYSQL實例有任何問題,或有mysql閃回相關的優秀對象優秀文章遺漏,煩請告知.
維易PHP培訓學院每天發布《Mysql實例MySQL閃回(flashback)原理與實戰》等實戰技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培養人才。
