MySQL 閃回原理與實戰開胃菜閃回原理閃回工具參考資料

dba或開發人員，有時會誤删或者誤更新資料，如果是線上環境并且影響較大，就需要能快速復原。傳統恢複方法是利用備份重搭執行個體，再應用去除錯誤sql後的binlog來恢複資料。此法費時費力，甚至需要停機維護，并不适合快速復原。也有團隊利用lvm快照來縮短恢複時間，但快照的缺點是會影響mysql的性能。

mysql閃回(flashback)利用binlog直接進行復原，能快速恢複且不用停機。本文将介紹閃回原理，給出筆者的實戰經驗，并對現存的閃回工具作比較。

某天，小明因種種原因，誤删了大批線上使用者表的資料。他急忙找到公司dba請求幫助，“客服電話已被打爆，大量使用者投訴無法登陸，上司非常惱火。請問多久能恢複資料？”dba一臉懵逼，沉默十秒後，伸出一根手指。“你的意思是一分鐘就能恢複？太好了。”小明終于有些放松，露出了一絲笑容。“不，我們中有個人将會離開公司。”dba沉痛的說道。

勿讓悲劇發生，盡早将此文轉給公司dba。

binlog概述

mysql binlog以event的形式，記錄了mysql server從啟用binlog以來所有的變更資訊，能夠幫助重制這之間的所有變化。mysql引入binlog主要有兩個目的：一是為了主從複制；二是某些備份還原操作後需要重新應用binlog。

有三種可選的binlog格式，各有優缺點：

· 

statement：基于sql語句的模式，binlog資料量小，但是某些語句和函數在複制過程可能導緻資料不一緻甚至出錯；

row：基于行的模式，記錄的是行的完整變化。很安全，但是binlog會比其他兩種模式大很多；

mixed：混合模式，根據語句來選用是statement還是row模式；

利用binlog閃回，需要将binlog格式設定為row。row模式下，一條使用innodb的insert會産生如下格式的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轉化成了可讀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應該交換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`='小李';

閃回實戰

真實的閃回場景中，最關鍵的是能快速篩選出真正需要復原的sql。

我們使用開源工具binlog2sql來進行實戰演練。binlog2sql由美團點評dba團隊(上海)出品，多次線上上環境做快速復原。

首先我們安裝binlog2sql：

shell> git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

shell> pip install -r requirements.txt

背景：小明在11:44時誤删了test庫user表大批的資料，需要緊急復原。

test庫user表原有資料

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表大批資料被誤删除。與此同時，正常業務資料是在繼續寫入的

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，檢視目前的binlog檔案

mysql> show master logs;

+------------------+-----------+

| log_name         | file_size |

| mysql-bin.000053 | 168652863 |

| mysql-bin.000054 |    504549 |

最新的binlog檔案是mysql-bin.000054。我們的目标是篩選出需要復原的sql，由于誤操作人隻知道大緻的誤操作時間，我們首先根據時間做一次過濾。隻需要解析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 輸出：

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

根據位置資訊，我們确定了誤操作sql來自同一個事務，準确位置在257427-504272之間(binlog2sql對于同一個事務會輸出同樣的start position)。再根據位置過濾，使用 -b 選項生成復原sql，檢查復原sql是否正确。(注：真實場景下，生成的復原sql經常會需要進一步篩選。結合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

與業務方确認復原sql沒問題，執行復原語句。登入mysql，确認復原成功。

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

|    16389 |

閃回的關鍵是快速篩選出真正需要復原的sql。

先根據庫、表、時間做一次過濾，再根據位置做更準确的過濾。

由于資料一直在寫入，要確定復原sql中不包含其他資料。可根據是否是同一事務、誤操作行數、字段值的特征等等來幫助判斷。

執行復原sql時如有報錯，需要查實具體原因，一般是因為對應的資料已發生變化。由于是嚴格的行模式，隻要有唯一鍵(包括主鍵)存在，就隻會報某條資料不存在的錯，不必擔心會更新不該操作的資料。

如果待復原的表與其他表有關聯，要與開發說明復原和不復原各自的副作用，再确定方案。

復原後資料變化，可能對使用者和線上應用造成困惑(類似幻讀)。

mysql閃回特性最早由阿裡彭立勳開發，彭在2012年給官方送出了一個patch，并對閃回設計思路做了說明(設計思路很有啟發性，強烈推薦閱讀)。但是因為種種原因，業内安裝這個patch的團隊至今還是少數，真正應用到線上的更是少之又少。彭之後，又有多位人員針對不同mysql版本不同語言開發了閃回工具，原理用的都是彭的思路。

我将這些閃回工具按實作方式分成了三類。

第一類是以patch形式內建到官方工具mysqlbinlog中。以彭送出的patch為代表。

優點

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

支援離線解析。

缺點

相容性差、項目活躍度不高。由于binlog格式的變動，如果閃回工具作者不及時對更新檔更新，則閃回工具将無法使用。目前已有多位人員分别針對mysql5.5，5.6，5.7開發了patch，部分項目代碼公開，但總體上活躍度都不高。

難以添加新功能，實戰效果欠佳。在實戰中，經常會遇到現有patch不滿足需求的情況，比如要加個表過濾，很簡單的一個需求，代碼改動也不會大，但對大部分dba來說，改mysql源碼還是很困難的事。

安裝稍顯麻煩。需要對mysql源碼打更新檔再編譯生成。

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

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

相容性好。僞裝成slave拉binlog這項技術在業界應用的非常廣泛，多個開發語言都有這樣的活躍項目，mysql版本的相容性由這些項目搞定，閃回工具的相容問題不再突出。

添加新功能的難度小。更容易被改造成dba自己喜歡的形式。更适合實戰。

安裝和使用簡單。

必須開啟mysql server。

第三類是簡單腳本。先用mysqlbinlog解析出文本格式的binlog，再根據復原原理用正則進行比對并替換。

腳本寫起來友善，往往能快速搞定某個特定問題。

通用性不好。

可靠性不好。

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

本文所述的flashback僅針對dml語句的快速復原。但如果誤操作是ddl的話，是無法利用binlog做快速復原的，因為即使在row模式下，binlog對于ddl操作也不會記錄每行資料的變化。要實作ddl快速復原，必須修改mysql源碼，使得在執行ddl前先備份老資料。目前有多個mysql定制版本實作了ddl閃回特性，阿裡林曉斌團隊送出了patch給mysql官方，mariadb預計在不久後加入包含ddl的flashback特性。ddl閃回的副作用是會增加額外存儲。考慮到其應用頻次實在過低，本文不做詳述，有興趣的同學可以自己去了解，重要的幾篇文章我在參考資料中做了引用。

有任何問題，或有mysql閃回相關的優秀工具優秀文章遺漏，煩請告知。 [email protected]

[1] mysql internals manual , chapter 20 the binary log

[2] 彭立勳，mysql下實作閃回的設計思路

[3] lixun peng, provide the flashback feature by binlog

[4] 王廣友，mysqlbinlog flashback 5.6完全使用手冊與原理

[5] 姜承堯, 拿走不謝，flashback for mysql 5.7

[6] 林曉斌, mysql閃回方案讨論及實作

[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

<b>本文來自雲栖社群合作夥伴“dbgeek”</b>