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

DBA或開發(fā)人員，有時會誤刪或者誤更新數(shù)據(jù)，如果是線上環(huán)境并且影響較大，就需要能快速回滾。傳統(tǒng)恢復(fù)方法是利用備份重搭實例，再應(yīng)用去除錯誤sql后的binlog來恢復(fù)數(shù)據(jù)。此法費時費力，甚至需要停機維護，并不適合快速回滾。也有團隊利用LVM快照來縮短恢復(fù)時間，但快照的缺點是會影響mysql的性能。

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

開胃菜

某天，小明因種種原因，誤刪了大批線上用戶表的數(shù)據(jù)。他急忙找到公司DBA請求幫助，“客服電話已被打爆，大量用戶投訴無法登陸，領(lǐng)導(dǎo)非常惱火。請問多久能恢復(fù)數(shù)據(jù)？”DBA一臉懵逼，沉默十秒后，伸出一根手指。“你的意思是一分鐘就能恢復(fù)？太好了。”小明終于有些放松，露出了一絲笑容?！安?，我們中有個人將會離開公司?！盌BA沉痛的說道。

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

閃回原理

binlog概述

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

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

1. statement：基于SQL語句的模式，binlog數(shù)據(jù)量小，但是某些語句和函數(shù)在復(fù)制過程可能導(dǎo)致數(shù)據(jù)不一致甚至出錯；
2. row：基于行的模式，記錄的是行的完整變化。很安全，但是binlog會比其他兩種模式大很多；
3. 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_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，從后往前回滾回去即可。

對于單個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`='小李';

閃回實戰(zhàn)

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

我們使用開源工具binlog2sql來進行實戰(zhàn)演練。binlog2sql由美團點評DBA團隊(上海)出品，多次在線上環(huán)境做快速回滾。

首先我們安裝binlog2sql：

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

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

test庫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時，user表大批數(shù)據(jù)被誤刪除。與此同時，正常業(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ù)步驟：

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

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

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

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 

根據(jù)位置信息，我們確定了誤操作sql來自同一個事務(wù)，準(zhǔn)確位置在257427-504272之間(binlog2sql對于同一個事務(wù)會輸出同樣的start position)。再根據(jù)位置過濾，使用 -B 選項生成回滾sql，檢查回滾sql是否正確。(注：真實場景下，生成的回滾SQL經(jīng)常會需要進一步篩選。結(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 

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

 shell> mysql -h127.0.0.1 -P3306 -uadmin -p'admin' < /tmp/rollback.sql

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

TIPS

再重復(fù)下最重要的兩點：篩選出正確SQL！溝通清楚！

閃回工具

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

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

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

優(yōu)點

上手成本低。mysqlbinlog原有的選項都能直接利用，只是多加了一個閃回選項。閃回特性未來有可能被官方收錄。
支持離線解析。

缺點

這些缺點，可能都是閃回沒有流行開來的原因。

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

優(yōu)點

缺點

必須開啟MySQL server。

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

優(yōu)點

缺點

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

關(guān)于DDL的flashback

本文所述的flashback僅針對DML語句的快速回滾。但如果誤操作是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閃回的副作用是會增加額外存儲?？紤]到其應(yīng)用頻次實在過低，本文不做詳述，有興趣的同學(xué)可以自己去了解，重要的幾篇文章我在參考資料中做了引用。

有任何問題，或有mysql閃回相關(guān)的優(yōu)秀工具優(yōu)秀文章遺漏，煩請告知。

您可能感興趣的文章:

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com