[TOC]
MySQL Flashback 工具介紹
-
DML Flashback
- 獨立工具，通過偽裝成slave拉取binlog來進行處理
- MyFlash 「大眾點點評」
- binlog2sql 「大眾點評(上海)」
- mysqlbinlog_flashback (更傾向于阿里RDS) 「58到家」
- patch形式集成到官方工具mysqlbinlog
- 簡單腳本。先用mysqlbinlog解析出文本格式的binlog蛋辈，再根據(jù)回滾原理用正則進行匹配并替換
- 獨立工具，通過偽裝成slave拉取binlog來進行處理
-
DDL Flashback
- Percona Data Recovery Tool for InnoDB --> code
- undrop-for-innodb --> code 「2017-01-01 已經(jīng)閉源并收費」
DDL Falshback 要求離線瑟慈，比 DML Flashback 要求更多，生產(chǎn)環(huán)境數(shù)據(jù)可能覆蓋導(dǎo)致恢復(fù)不全
因為 5.6 開始仔雷，有單獨的 purge 線程蹂析，數(shù)據(jù)恢復(fù)可能性更低了
部署位置
-
IP1
/data1/dbatools/mysqlflashback
- flashback_57 「mysqlbinlog」(因為系統(tǒng)原因舔示，mysqlbinlog 放在 CentOS 7機器上)
- binlog2sql 「建議使用」
- MyFlash「建議使用」(測試暫時還不能在 CentOS 7上使用)
-
IP2
/data1/dbatools/mysqlflashback
- binlog2sql 「建議使用」
- MyFlash「建議使用」
MyFlash
github：MyFlash
MyFlash是由美團點評公司技術(shù)工程部開發(fā)維護的一個回滾DML操作的工具。該工具通過解析v4版本的binlog电抚，完成回滾操作惕稻。相對已有的回滾工具，其增加了更多的過濾選項蝙叛，讓回滾更加容易俺祠。
版本兼容
- MySQL 5.6、5.7
限制
- binlog格式必須為row借帘，且binlog_row_image=full
- 目前只支持單個 binlog 文件解析
- 只能回滾DML（增蜘渣、刪、改）
依賴
- glibc 版本
編譯
git clone https://github.com/Meituan-Dianping/MyFlash.git
cd MyFlash
gcc -w `pkg-config --cflags --libs glib-2.0` source/binlogParseGlib.c -o binary/flashback
使用
基本用法
cd binary
# 回滾整個文件 && 應(yīng)用
./flashback --binlogFileNames=mysql-bin.000005
mysqlbinlog binlog_output_base.flashback | mysql -h<host> -u<user> -p
# 回滾文件中 INSERT && 應(yīng)用
./flashback --sqlTypes='INSERT'--binlogFileNames=mysql-bin.000005
mysqlbinlog binlog_output_base.flashback | mysql -h<host> -u<user> -p
# 回滾大文件 && 應(yīng)用 (大于20M建議分割回滾文件)
./flashback --binlogFileNames=mysql-bin.000005
./flashback --maxSplitSize=1 --binlogFileNames=binlog_output_base.flashback
mysqlbinlog binlog_output_base.flashback.000001 | mysql -h<host> -u<user> -p
...
mysqlbinlog binlog_output_base.flashback.<N> | mysql -h<host> -u<user> -p
flashback 回滾的文件肺然，還是mysqlbinlog格式的蔫缸，如果想看 sql，還是需要原生 mysqlbinlog 進行解析
/usr/local/mysql-5.7.18/bin/mysqlbinlog binlog_output_base.flashback
開啟 gtid MySQL 應(yīng)用flashback報錯
ERROR 1782 (HY000) at line 16: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.
在導(dǎo)入時加入--skip-gtids mysqlbinlog --skip-gtids | mysql -uxxx -pxxx
選項
./flashback --help
Usage:
flashback [OPTION...]
Help Options:
-?, --help Show help options
Application Options:
--databaseNames databaseName to apply. if multiple, seperate by comma(,)
--tableNames tableName to apply. if multiple, seperate by comma(,)
--start-position start position
--stop-position stop position
--start-datetime start time (format %Y-%m-%d %H:%M:%S)
--stop-datetime stop time (format %Y-%m-%d %H:%M:%S)
--sqlTypes sql type to filter . support INSERT, UPDATE ,DELETE. if multiple, seperate by comma(,)
--maxSplitSize max file size after split, the uint is M
--binlogFileNames binlog files to process. if multiple, seperate by comma(,)
--outBinlogFileNameBase output binlog file name base
--logLevel log level, available option is debug,warning,error
--include-gtids gtids to process
--exclude-gtids gtids to skip
| 參數(shù) | 解釋 | |
|---|---|---|
| 1 | databaseNames | 指定需要回滾的數(shù)據(jù)庫名际起。多個用“,”隔開拾碌。不指定該參數(shù)，相當(dāng)于指定了所有數(shù)據(jù)庫 |
| 2 | tableNames | 指定需要回滾的表名街望。多個表可以用“,”隔開校翔。如果不指定該參數(shù)，相當(dāng)于指定了所有表 |
| 3 | start-position | 指定回滾開始的位置它匕。如不指定展融，從文件的開始處回滾。請指定正確的有效的位置豫柬，否則無法回滾 |
| 4 | stop-position | 指定回滾結(jié)束的位置告希。如不指定，回滾到文件結(jié)尾烧给。請指定正確的有效的位置燕偶，否則無法回滾 |
| 5 | start-datetime | 指定回滾的開始時間。格式必須是 %Y-%m-%d %H:%M:%S础嫡。 如不指定指么，則不限定時間 |
| 6 | stop-datetime | 定回滾的結(jié)束時間。格式必須是 %Y-%m-%d %H:%M:%S榴鼎。 如不指定伯诬，則不限定時間 |
| 7 | sqlTypes | 指定回滾的sql類型。支持的類型是INSERT, UPDATE ,DELETE巫财。多個類型可以用“,”隔開盗似。 |
| 8 | maxSplitSize | 一旦指定該參數(shù)，對文件進行固定尺寸的分割（單位為M）平项，過濾條件有效赫舒，但不進行回滾操作悍及。該參數(shù)主要用來將大的binlog文件切割，防止單次應(yīng)用的binlog尺寸過大接癌，對線上造成壓力 |
| 9 | binlogFileNames | 需要回滾的binlog文件心赶，目前只支持單個文件，后續(xù)會增加多個文件支持 |
| 10 | outBinlogFileNameBase | 輸出的binlog文件前綴缺猛，如不指定缨叫，則默認(rèn)為binlog_output_base.flashback |
| 11 | logLevel | 僅供開發(fā)者使用，默認(rèn)級別為error級別荔燎。在生產(chǎn)環(huán)境中不要修改這個級別弯汰，否則輸出過多 |
| 12 | include-gtids | 指定需要回滾的gtid,支持gtid的單個和范圍兩種形式。 |
| 13 | exclude-gtids | 指定不需要回滾的gtid湖雹，用法同include-gtids |
binlog2sql
偽裝成slave拉取binlog咏闪，從MySQL binlog解析出你要的SQL。根據(jù)不同選項摔吏，你可以得到原始SQL鸽嫂、回滾SQL、去除主鍵的INSERT SQL等征讲，建議表有主鍵据某。
github: binlog2sql
用途
數(shù)據(jù)快速回滾(閃回)
主從切換后新master丟數(shù)據(jù)的修復(fù)
-
從binlog生成標(biāo)準(zhǔn)SQL，帶來的衍生功能
?
版本兼容
- Python 2.6诗箍、2.7
- MySQL 5.6 (簡單測試在 5.5癣籽、5.7 gtid 均可用，不排除無 Bug滤祖，建議 5.6 以后版本使用)
依賴
- python-mysql-replication
- PyMySQL
- wheel
限制 (對比mysqlbinlog)
mysql server必須開啟筷狼，離線模式下不能解析
參數(shù) binlog_format 必須為ROW，參數(shù) binlog_row_image 必須為FULL匠童，暫不支持MINIMAL (5.6.2引入)
GEOMETRY 不能解析 (因為python-mysql-replication Limitations)
解析速度不如 mysqlbinlog
表有關(guān)聯(lián)表埂材，關(guān)聯(lián)表并不會被回滾，需與業(yè)務(wù)方溝通清楚
不支持DDL
-
建議回滾的表有主鍵
?
優(yōu)點 (對比mysqlbinlog)
- 純Python開發(fā)汤求，安裝與使用都很簡單
- 自帶flashback俏险、no-primary-key解析模式，無需再裝補丁
- flashback模式下扬绪，更適合閃回實戰(zhàn)
- 解析為標(biāo)準(zhǔn)SQL竖独，方便理解、調(diào)試
- 代碼容易改造挤牛，可以支持更多個性化解析
安裝
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt
升級依賴模塊
# cat requirements.txt
PyMySQL==0.7.11
wheel==0.29.0
mysql-replication==0.13
wget https://codeload.github.com/noplay/python-mysql-replication/tar.gz/0.15
tar zxf 0.15
cd python-mysql-replication-0.15
python2.7 setup.py install
使用
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
user需要的最小權(quán)限集合
select, super/replication client, replication slave
建議授權(quán)
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
權(quán)限說明
- 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 (<u>會有 DDL SQL格二，回滾模式是沒有 DDL的</u>)
python2.7 binlog2sql/binlog2sql.py -h127.0.0.1 -P6000 -umyadmin_common -p'password' -dtest -tt1 --start-file='mysql-bin.000192'
INSERT INTO `test`.`t1`(`id`, `name`) VALUES (5, 'name5'); #start 4 end 259 time 2017-09-14 19:59:31
DELETE FROM `test`.`t1` WHERE `id`=4 AND `name`='name4' LIMIT 1; #start 286 end 438 time 2017-09-14 19:59:42
UPDATE `test`.`t1` SET `id`=3, `name`='name300' WHERE `id`=3 AND `name`='name3' LIMIT 1; #start 465 end 631 time 2017-09-14 19:59:54
解析回滾SQL (在上面命令基礎(chǔ)上加上 --flashback 或 -B)
python2.7 binlog2sql/binlog2sql.py -h127.0.0.1 -P6000 -umyadmin_common -p'ZTlh916@mxq' -dtest -tt1 --start-file='mysql-bin.000192' -B
UPDATE `test`.`t1` SET `id`=3, `name`='name3' WHERE `id`=3 AND `name`='name300' LIMIT 1; #start 465 end 631 time 2017-09-14 19:59:54
INSERT INTO `test`.`t1`(`id`, `name`) VALUES (4, 'name4'); #start 286 end 438 time 2017-09-14 19:59:42
DELETE FROM `test`.`t1` WHERE `id`=5 AND `name`='name5' LIMIT 1; #start 4 end 259 time 2017-09-14 19:59:31
選項
mysql連接配置
-h host; -P port; -u user; -p password
解析模式
--stop-never 持續(xù)同步binlog】⒈模可選顶猜。不加則同步至執(zhí)行命令時最新的binlog位置。
-K, --no-primary-key 對INSERT語句去除主鍵痘括。可選懂算。
-B, --flashback 生成回滾語句航棱，可解析大文件呵晚，不受內(nèi)存限制，每打印一千行加一句SLEEP SELECT(1)胚膊。可選路召。與stop-never或no-primary-key不能同時添加。
解析范圍控制
--start-file 起始解析文件廷区。必須唯灵。
--start-position/--start-pos start-file的起始解析位置∠肚幔可選埠帕。默認(rèn)為start-file的起始位置。
--stop-file/--end-file 末尾解析文件玖绿×泊桑可選。默認(rèn)為start-file同一個文件斑匪。若解析模式為stop-never呐籽，此選項失效。
--stop-position/--end-pos stop-file的末尾解析位置〗频可選庶橱。默認(rèn)為stop-file的最末位置；若解析模式為stop-never贪惹，此選項失效苏章。
--start-datetime 從哪個時間點的binlog開始解析，格式必須為datetime馍乙，如'2016-11-11 11:11:11'〉媸停可選丝格。默認(rèn)不過濾。
--stop-datetime 到哪個時間點的binlog停止解析棵譬，格式必須為datetime显蝌，如'2016-11-11 11:11:11'《┫蹋可選曼尊。默認(rèn)不過濾。
對象過濾
-d, --databases 只輸出目標(biāo)db的sql脏嚷÷嫫玻可選。默認(rèn)為空父叙。
-t, --tables 只輸出目標(biāo)tables的sql神郊。可選趾唱。默認(rèn)為空涌乳。
恢復(fù)數(shù)據(jù)
誤操作大概時間范圍
python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-datetime='2016-12-13 20:25:00' --stop-datetime='2016-12-13 20:30:00'
找到具體位點進行恢復(fù)
python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-position=3346 --stop-position=3556 -B > rollback.sql | cat
<u>回滾前，一定找業(yè)務(wù)確認(rèn)回滾數(shù)據(jù)</u>
TIPS
閃回的目標(biāo)：快速篩選出真正需要回滾的數(shù)據(jù)甜癞。
先根據(jù)庫夕晓、表、時間做一次過濾悠咱，再根據(jù)位置做更準(zhǔn)確的過濾蒸辆。
由于數(shù)據(jù)一直在寫入，要確蔽黾龋回滾sql中不包含其他數(shù)據(jù)吁朦。可根據(jù)是否是同一事務(wù)渡贾、誤操作行數(shù)逗宜、字段值的特征等等來幫助判斷。
執(zhí)行回滾sql時如有報錯，需要查實具體原因纺讲，一般是因為對應(yīng)的數(shù)據(jù)已發(fā)生變化擂仍。由于是嚴(yán)格的行模式，只要有唯一鍵(包括主鍵)存在熬甚，就只會報某條數(shù)據(jù)不存在的錯逢渔，不必?fù)?dān)心會更新不該操作的數(shù)據(jù)。業(yè)務(wù)如果有特殊邏輯乡括，數(shù)據(jù)回滾可能會帶來影響肃廓。
-
如果只回滾某張表，并且該表有關(guān)聯(lián)表诲泌，關(guān)聯(lián)表并不會被回滾盲赊，需與業(yè)務(wù)方溝通清楚。
?
mysqlbinlog_flashback
跟 binlog2sql 一樣敷扫，偽裝成slave拉取binlog菱魔，從MySQL binlog解析出你要的SQL渣刷，目前已經(jīng)穩(wěn)定運行在阿里RDS、db字符集為 utf8 的產(chǎn)生環(huán)境，<u>其他環(huán)境要謹(jǐn)慎使用</u>验靡。
github: mysqlbinlog_flashback
版本兼容
- 阿里RDS
- 默認(rèn) utf8 DB (gbk等字符集的 DB 理論上支持)
限制
- mysql server必須開啟骏令，離線模式下不能解析
- 參數(shù) binlog_format 必須為ROW则果，參數(shù) binlog_row_image 必須為FULL岳掐，暫不支持MINIMAL (5.6.2引入)
- GEOMETRY 不能解析 (因為python-mysql-replication Limitations)
- 解析速度不如 mysqlbinlog
- 表有關(guān)聯(lián)表，關(guān)聯(lián)表并不會被回滾哮奇，需與業(yè)務(wù)方溝通清
- 不支持DDL
- <u>表必須有主鍵</u> (只限制在 mysqlbinlog_flashback将硝，雖然 binlog2sql 不強制，但建議表有主鍵)
<u>阿里 RDS屏镊、db 字符集為 utf8 的環(huán)境穩(wěn)定使用依疼，其他環(huán)境要謹(jǐn)慎使用</u> (只限制在 mysqlbinlog_flashback)
-
<u>修改 python-mysql-replication</u> (只限制在 mysqlbinlog_flashback)
- 某些數(shù)據(jù)被封裝，沒有暴露出來
- Ali-rds導(dǎo)致的bug
-
<u>字段類型限制</u> (只限制在 mysqlbinlog_flashback)
ALLOW_TYPE={ "varchar":True, "char":True, "datetime":True, "date":True, "time":True, "timestamp":True, "bigint":True, "mediumint":True, "smallint":True, "tinyint":True, "int":True, "smallint":True, "decimal":True, "float":True, "double":True, "longtext":True, "tinytext":True, "text":True, "mediumtext":True }
mysql 導(dǎo)入一定要指定 字符集 --default-character-set=utf8
安裝
git clone https://github.com/58daojia-dba/mysqlbinlog_flashback.git
cd mysqlbinlog_flashback
使用
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
基本用法
python2.7 mysqlbinlog_back.py --host="127.0.0.1" --port=7000 --user="mysql_user" --password='password' --schema=test --table=t2
ls -l log/*
-rw-r--r-- 1 root root 1.9K Sep 19 17:40 save_data_dml_test_20170919_174001.sql
-rw-r--r-- 1 root root 179 Sep 19 17:40 save_data_create_table_test_20170919_174001.sql
-rw-r--r-- 1 root root 1.1K Sep 19 17:40 flashback_test_20170919_174001.sql <--- 反向sq文件
mysql -uroot -p -h -P --default-character-set=utf8 < flashback_test_20170919_174001.sql
選項
-s SCHEMA, --schema=SCHEMA 必須的而芥，指定 DB
-t TABLES, --tables=TABLES 必須的律罢，指定 Table，用逗號分隔
-N BINLOG_END_TIME, --binlog_end_time=BINLOG_END_TIME
format yyyy-mm-dd hh24:mi:ss,default is current time
-S BINLOG_START_FILE_NAME, --binlog_start_file_name=BINLOG_START_FILE_NAME
binlog start file name,default is current logfile of db
-L BINLOG_START_FILE_POSITION, --binlog_start_file_position=BINLOG_START_FILE_POSITION
binlog start file name
-E BINLOG_START_TIME, --binlog_start_time=BINLOG_START_TIME
binlog start time,format yyyy-mm-dd hh24:mi:ss
-l OUTPUT_FILE_PATH, --output_file_path=OUTPUT_FILE_PATH
file path that sql generated,,default ./log
-I, --skip_insert skip insert(WriteRowsEvent) event
-U, --skip_update skip update(UpdateRowsEvent) event
-D, --skip_delete skip delete(DeleteRowsEvent) event
-a, --add_schema_name add schema name for flashback sql
mysqlbinlog
以patch形式集成到官方工具mysqlbinlog中棍丐。以彭立勛提交的patch為代表 MySQL下實現(xiàn)閃回的設(shè)計思路 (MySQL Flashback Feature)
mysqlbinlog 閃回工具
彭立勛 patch （只針對 MySQL 5.5) --> 具體步驟參考 嘗試mysqlbinlog的flashback功能
-
姜承堯 - 關(guān)于MySQL Flashback工具的使用及介紹
?
優(yōu)點
上手成本低，原 mysqlbinlog 選項可以直接利用歌逢，只是多一個閃回選項
支持離線解析
-
支持事務(wù)閃回巾钉，語句逆轉(zhuǎn)、順序也顛倒
?
缺點
- 兼容性差秘案、項目活躍度不高砰苍，mysql5.5潦匈，5.6，5.7 分別開發(fā)了patch
- 難以添加新功能赚导，實戰(zhàn)效果欠佳茬缩，例如過濾一張表 (姜承堯版本也是支持的)
- 安裝稍顯麻煩。需要對mysql源碼打補丁再編譯生成
限制
參數(shù) binlog_format 必須為ROW吼旧，參數(shù) binlog_row_image 必須為FULL凰锡，暫不支持MINIMAL
-
不支持DDL
?
使用
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
基本用法
解析 mysqlbinlog
./mysqlbinlog -vv mysql-bin.000067 --start-datetime="2017-09-19 15:00:00" --stop-datetime="2017-09-19 15:38:10"
回滾
./mysqlbinlog -vv mysql-bin.000067 --start-position=1427 --stop-position=2588 -B | mysql -umysqlha_common -p'password' -h10.13.2.29 -P 7000 test
position 位點找需要回滾事務(wù) BEGIN 前面那個 position.
選項
-B, --flashback Flashback data to start_postition or start_datetime.
-A, --skip-database=name 解析BinLog時過濾掉該數(shù)據(jù)庫 (local log only)
-a, --skip-table=name 解析BinLog時過濾掉該表，一般與skip_datebase配套使用 (local log only)
--skip-database 可以單獨使用圈暗，--skip-table需要與 skip-database配置使用掂为，<u>多個db或table不生效</u>
--skip-database=test --skip-table=t1
-O, --split-size-interval=# 將BinLog文件按照指定的大小拆分為多個段，解析結(jié)果為打印每個段的起始o(jì)ffset位置
注意员串，當(dāng)進行flashback時勇哗，flashback的內(nèi)容先保存在內(nèi)存中。若你的binlog大小為10G昵济，那么需要額外的10G內(nèi)存先暫時保存這部分信息智绸。在某些情況下野揪，如云環(huán)境访忿、或服務(wù)器內(nèi)存較小，會導(dǎo)致無法輸出flashback的日志斯稳。這時可以通過此參數(shù)來設(shè)置內(nèi)存保存文件的大小海铆，例如將此值設(shè)置為100M，那么每100M就會刷新到一個文件
-D, --datetime-to-pos=name 基于輸入的時間信息挣惰，解析出該時間對應(yīng)的第一個BinLog event偏移位置卧斟，格式參照start-datetime
flashback時要先找到起始的偏移量，DBA可以先通過此參數(shù)定位到具體位置憎茂，然后再進行flashback操作
「不是很好用啊珍语，少了一個」
-T, --table=name 僅解析該表，一般與database配套使用 (local log only)
-E, --fb-event=name 僅解析該類型的Log event竖幔，一般與database板乙、table選項配套使用。(local log only)
可選的值有:
- DELETE
- INSERT
- UPDATE
DDL Flashbackup
DDL Falshback 要求離線拳氢，比 DML Flashback 要求更多募逞，生產(chǎn)環(huán)境數(shù)據(jù)可能覆蓋導(dǎo)致恢復(fù)不全。
MySQL 對于空間的重用機制與Oracle 有很大區(qū)別馋评，對于Oracle 而言放接，如果是delete的數(shù)據(jù)，還是很難被覆蓋掉的留特，對于drop 和truncate 則領(lǐng)導(dǎo)別論纠脾。然而MySQL則有所不同玛瘸，MySQL 默認(rèn)會啟動一些purge 進程來進行空間重用，這是MySQL 5.6的情況
localhost.(none)>show variables like '%innodb_purge_threads';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_purge_threads | 1 |
+----------------------+-------+
1 row in set (0.00 sec)
在MySQL 5.7 版本中更為坑爹乳乌，MySQL 默認(rèn)會啟動4個purge 線程捧韵，因此很容易就會導(dǎo)致空間被重用，最終導(dǎo)致數(shù)據(jù)無法恢復(fù)汉操，如下是MySQL 5.7的purge相關(guān)參數(shù)
localhost.(none)>show variables like '%innodb_purge_threads';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_purge_threads | 4 |
+----------------------+-------+
1 row in set (0.00 sec)
Percona Data Recovery Tool for InnoDB
限制
只對InnoDB/XtraDB表有效再来，而無法恢復(fù)MyISAM表
離線恢復(fù)，MySQL Server 是關(guān)閉的
-
不能保證數(shù)據(jù)總一定可被恢復(fù)磷瘤。
被重寫的數(shù)據(jù)不能被恢復(fù)芒篷，這種情況下可能需要針對系統(tǒng)或物理的方式來恢復(fù)，不屬于本工具的范疇采缚。
恢復(fù)的最好時機是當(dāng)你發(fā)現(xiàn)數(shù)據(jù)丟失時针炉，盡快備份MySQL數(shù)據(jù)文件
innodb_file_per_table = 0
安裝
現(xiàn)在可以下載還是 0.5，2011-08-28扳抽，0.6 還未發(fā)布
yum install glibc glibc-static -y
wget https://launchpad.net/percona-data-recovery-tool-for-innodb/trunk/release-0.5/+download/percona-data-recovery-tool-for-innodb-0.5.tar.gz
tar -xvf percona-data-recovery-tool-for-innodb-0.5.tar.gz
cd percona-data-recovery-tool-for-innodb-0/mysql-source/
./configure
cd ..
make
# ll -rth
total 3.0M
-rw-r--r-- 1 510 wheel 2.0K Aug 28 2011 tables_dict.c
-rwxr-xr-x 1 510 wheel 302 Aug 28 2011 split_dump.pl
-rw-r--r-- 1 510 wheel 11K Aug 28 2011 print_data.c
-rw-r--r-- 1 510 wheel 15K Aug 28 2011 page_parser.c
-rw-r--r-- 1 510 wheel 2.7K Aug 28 2011 Makefile
-rw-r--r-- 1 510 wheel 74 Aug 28 2011 INSTALL
-rw-r--r-- 1 510 wheel 9.0K Aug 28 2011 innochecksum.c
-rw-r--r-- 1 510 wheel 8.1K Aug 28 2011 incrementalupdate.c
-rw-r--r-- 1 510 wheel 12K Aug 28 2011 ibdconnect.c
-rwxr-xr-x 1 510 wheel 2.0K Aug 28 2011 fetch_data.sh
-rwxr-xr-x 1 510 wheel 12K Aug 28 2011 create_defs.pl
-rw-r--r-- 1 510 wheel 22K Aug 28 2011 constraints_parser.c
-rw-r--r-- 1 510 wheel 6.2K Aug 28 2011 check_data.c
drwxr-xr-x 2 510 wheel 4.0K Aug 28 2011 include
drwxr-xr-x 2 510 wheel 4.0K Aug 28 2011 docs
drwxr-xr-x 40 510 wheel 4.0K Sep 26 17:53 mysql-source
drwxr-xr-x 2 root root 4.0K Sep 26 17:55 lib
-rwxr-xr-x 1 root root 725K Sep 26 17:55 constraints_parser
-rwxr-xr-x 1 root root 1.2M Sep 26 17:55 page_parser
-rwxr-xr-x 1 root root 15K Sep 26 17:55 innochecksum
-rwxr-xr-x 1 root root 973K Sep 26 17:55 ibdconnect
page_parser工具將根據(jù)InnoDB的底層實現(xiàn)原理篡帕，解析表的頁和行結(jié)構(gòu)。constraints_parser工具暫時不使用贸呢，后續(xù)還需要在定義表結(jié)構(gòu)之后镰烧，重新編譯生成它。
如果MySQL是5.0之前的版本楞陷，InnoDB采取的是REDUNDANT格式怔鳖，運行以下命令：
./page_parser -4 -f /path/to/ibdata1
如果MySQL是5.0以后的版本，InnoDB采取的是COMPACT格式固蛾，運行以下命令：
./page_parser -5 -f /path/to/ibdata1
運行后结执，page_parser工具會創(chuàng)建一個pages-<TIMESTAMP>的目錄，其中TIMESTAMP是UNIX系統(tǒng)時間戳艾凯。在這個目錄下献幔，為每個index ID，以頁的index ID創(chuàng)建一個子目錄
...
...
...
這個工具限制很多趾诗，沒有測試蜡感，具體步驟可以參考 "其他參考"
其他參考：
Percona Data Recovery Tool 單表恢復(fù)
使用Percona Data Recovery Tool for InnoDB恢復(fù)數(shù)據(jù)
Undrop MySQL InnoDB 中恢復(fù)被drop的表，當(dāng) innodb_file_per_table=off時
undrop-for-innodb
「2017-01-01 已經(jīng)閉源并收費」沧竟，來源wiki
MySQL · 數(shù)據(jù)恢復(fù) · undrop-for-innodb 「數(shù)據(jù)庫內(nèi)核月報 － 2017 / 11」
如果 innodb_file_per_table = on 恢復(fù)可能性更低
- idb 文件被臟寫
- 有單獨的 purge 線程
限制
- innodb_purge_threads = 0
innodb_purge_threads <=5.6.1铸敏，默認(rèn)為0，范圍 [0-1]
? = 5.6.2, <= 5.6.4 默認(rèn)為0悟泵，范圍 [0-32]
? = 5.6.5 默認(rèn)為1杈笔，范圍 [1-32]
? = 5.7.8 默認(rèn)為4，范圍 [4-32]
5.6 以后糕非，DDL 恢復(fù)還是比較困難的
安裝
git clone https://github.com/chhabhaiya/undrop-for-innodb
cd undrop-for-innodb
make
使用 「MySQL 5.6.24」
1蒙具、truncate table
localhost.test>select * from t_recover;
+----+--------+
| id | name |
+----+--------+
| 1 | test1 |
| 2 | test2 |
| 3 | test3 |
| 4 | test4 |
| 5 | test5 |
| 6 | test6 |
| 7 | test7 |
| 8 | test8 |
| 9 | test9 |
| 10 | test10 |
+----+--------+
10 rows in set (0.00 sec)
mysql> truncate table t_recover;
2球榆、獲取數(shù)據(jù)字典
# ./stream_parser -f /data1/mysql7000/ibdata1
Opening file: /data1/mysql7000/ibdata1
File information:
ID of device containing file: 2055
inode number: 7012359
protection: 100640 (regular file)
number of hard links: 1
user ID of owner: 647
group ID of owner: 553
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 205008
Opening file: /data1/mysql7000/ibdata1
File information:
time of last access: 1506651425 Fri Sep 29 10:17:05 2017
time of last modification: 1506651465 Fri Sep 29 10:17:45 2017
ID of device containing file: 2055
time of last status change: 1506651465 Fri Sep 29 10:17:45 2017
inode number: 7012359
protection: 100640 (regular file)
number of hard links: 1
user ID of owner: 647
group ID of owner: 553
device ID (if special file): 0
blocksize for filesystem I/O: 4096
Opening file: /data1/mysql7000/ibdata1
total size, in bytes: 104857600 (100.000 MiB)
number of blocks allocated: 205008
File information:
Size to process: 104857600 (100.000 MiB)
ID of device containing file: 2055
inode number: 7012359
protection: 100640 (regular file)
number of hard links: 1
user ID of owner: 647
group ID of owner: 553
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 205008
Opening file: /data1/mysql7000/ibdata1
File information:
ID of device containing file: 2055
inode number: 7012359
protection: 100640 (regular file)
time of last access: 1506651425 Fri Sep 29 10:17:05 2017
number of hard links: 1
user ID of owner: 647
group ID of owner: 553
device ID (if special file): 0
time of last modification: 1506651465 Fri Sep 29 10:17:45 2017
blocksize for filesystem I/O: 4096
number of blocks allocated: 205008
time of last status change: 1506651465 Fri Sep 29 10:17:45 2017
time of last access: 1506651425 Fri Sep 29 10:17:05 2017
time of last modification: 1506651465 Fri Sep 29 10:17:45 2017
time of last status change: 1506651465 Fri Sep 29 10:17:45 2017
total size, in bytes: 104857600 (100.000 MiB)
Opening file: /data1/mysql7000/ibdata1
total size, in bytes: 104857600 (100.000 MiB)
File information:
Size to process: 104857600 (100.000 MiB)
ID of device containing file: 2055
Size to process: 104857600 (100.000 MiB)
inode number: 7012359
protection: 100640 (regular file)
number of hard links: 1
user ID of owner: 647
group ID of owner: 553
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 205008
time of last access: 1506651425 Fri Sep 29 10:17:05 2017
time of last modification: 1506651465 Fri Sep 29 10:17:45 2017
time of last status change: 1506651465 Fri Sep 29 10:17:45 2017
total size, in bytes: 104857600 (100.000 MiB)
Opening file: /data1/mysql7000/ibdata1
File information:
Size to process: 104857600 (100.000 MiB)
ID of device containing file: 2055
inode number: 7012359
protection: 100640 (regular file)
number of hard links: 1
Opening file: /data1/mysql7000/ibdata1
user ID of owner: 647
time of last access: 1506651425 Fri Sep 29 10:17:05 2017
File information:
group ID of owner: 553
device ID (if special file): 0
time of last modification: 1506651465 Fri Sep 29 10:17:45 2017
blocksize for filesystem I/O: 4096
ID of device containing file: 2055
time of last status change: 1506651465 Fri Sep 29 10:17:45 2017
number of blocks allocated: 205008
inode number: 7012359
protection: 100640 (regular file)
number of hard links: 1
total size, in bytes: 104857600 (100.000 MiB)
user ID of owner: 647
group ID of owner: 553
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 205008
Size to process: 104857600 (100.000 MiB)
Opening file: /data1/mysql7000/ibdata1
File information:
ID of device containing file: 2055
inode number: 7012359
time of last access: 1506651425 Fri Sep 29 10:17:05 2017
protection: 100640 (regular file)
time of last access: 1506651425 Fri Sep 29 10:17:05 2017
time of last modification: 1506651465 Fri Sep 29 10:17:45 2017
number of hard links: 1
user ID of owner: 647
time of last modification: 1506651465 Fri Sep 29 10:17:45 2017
group ID of owner: 553
device ID (if special file): 0
time of last status change: 1506651465 Fri Sep 29 10:17:45 2017
blocksize for filesystem I/O: 4096
number of blocks allocated: 205008
total size, in bytes: 104857600 (100.000 MiB)
Size to process: 104857600 (100.000 MiB)
Opening file: /data1/mysql7000/ibdata1
File information:
ID of device containing file: 2055
inode number: 7012359
protection: 100640 (regular file)
number of hard links: 1
user ID of owner: 647
group ID of owner: 553
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 205008
time of last access: 1506651425 Fri Sep 29 10:17:05 2017
time of last modification: 1506651465 Fri Sep 29 10:17:45 2017
time of last status change: 1506651465 Fri Sep 29 10:17:45 2017
total size, in bytes: 104857600 (100.000 MiB)
time of last access: 1506651425 Fri Sep 29 10:17:05 2017
Size to process: 104857600 (100.000 MiB)
time of last modification: 1506651465 Fri Sep 29 10:17:45 2017
time of last status change: 1506651465 Fri Sep 29 10:17:45 2017
total size, in bytes: 104857600 (100.000 MiB)
Opening file: /data1/mysql7000/ibdata1
File information:
Size to process: 104857600 (100.000 MiB)
ID of device containing file: 2055
inode number: 7012359
protection: 100640 (regular file)
number of hard links: 1
user ID of owner: 647
group ID of owner: 553
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 205008
time of last access: 1506651425 Fri Sep 29 10:17:05 2017
time of last modification: 1506651465 Fri Sep 29 10:17:45 2017
time of last status change: 1506651465 Fri Sep 29 10:17:45 2017
total size, in bytes: 104857600 (100.000 MiB)
Size to process: 104857600 (100.000 MiB)
time of last status change: 1506651465 Fri Sep 29 10:17:45 2017
total size, in bytes: 104857600 (100.000 MiB)
Size to process: 104857600 (100.000 MiB)
Opening file: /data1/mysql7000/ibdata1
File information:
ID of device containing file: 2055
inode number: 7012359
protection: 100640 (regular file)
number of hard links: 1
user ID of owner: 647
group ID of owner: 553
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 205008
time of last access: 1506651425 Fri Sep 29 10:17:05 2017
time of last modification: 1506651465 Fri Sep 29 10:17:45 2017
time of last status change: 1506651465 Fri Sep 29 10:17:45 2017
total size, in bytes: 104857600 (100.000 MiB)
Size to process: 104857600 (100.000 MiB)
Opening file: /data1/mysql7000/ibdata1
File information:
ID of device containing file: 2055
inode number: 7012359
protection: 100640 (regular file)
number of hard links: 1
user ID of owner: 647
group ID of owner: 553
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 205008
time of last access: 1506651425 Fri Sep 29 10:17:05 2017
time of last modification: 1506651465 Fri Sep 29 10:17:45 2017
time of last status change: 1506651465 Fri Sep 29 10:17:45 2017
total size, in bytes: 104857600 (100.000 MiB)
Size to process: 104857600 (100.000 MiB)
All workers finished in 0 sec
stream_parser這個工具發(fā)現(xiàn)在ibdata1中，找到了存儲頁面類型（FIL_PAGE_INDEX或FIL_PAGE_TYPE_BLOB）由index_id的整理InnoDB的頁面
# cd pages-ibdata1/
# ll -rth
total 8.0K
drwxr-xr-x 2 root root 4.0K Sep 29 10:18 FIL_PAGE_TYPE_BLOB
drwxr-xr-x 2 root root 4.0K Sep 29 10:18 FIL_PAGE_INDEX
SYS_TABLES
SYS_TABLES本身是一個表禁筏，該表用于規(guī)范innodb各種表定義和保存innodb中各種表的基本信息持钉，結(jié)合SYS_COLUMNS，SYS_INDEXES和SYS_FOREIGN系統(tǒng)表定義了特定某個表的所有信息篱昔。
undrop-for-innodb]# ll pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page
-rw-r--r-- 1 root root 32768 Sep 29 10:18 pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page
SYS_INDEXES
SYS_INDEXES用于保存innodb中每個表定義的每個索引對象
undrop-for-innodb]# ll pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page
-rw-r--r-- 1 root root 16384 Sep 29 10:18 pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page
SYS_COLUMNS
SYS_COLUMNS用于保存innodb引擎每個表定義的列每强，與SYS_TABLES相似。
undrop-for-innodb]# ll pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page
-rw-r--r-- 1 root root 32768 Sep 29 10:18 pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page
SYS_FIELDS
SYS_FIELDS用于保存innodb中每個索引的每個列對象
undrop-for-innodb]# ll pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page
-rw-r--r-- 1 root root 32768 Sep 29 10:18 pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page
3州刽、掃描邏輯卷
./stream_parser -f /dev/sda7 -t 105000000k
Opening file: /dev/sda7
File information:
ID of device containing file: 5
inode number: 6047
protection: 60660 (block device)
number of hard links: 1
user ID of owner: 0
group ID of owner: 6
device ID (if special file): 2055
blocksize for filesystem I/O: 4096
number of blocks allocated: 0
Opening file: /dev/sda7
File information:
ID of device containing file: 5
inode number: 6047
protection: 60660 (block device)
number of hard links: 1
user ID of owner: 0
group ID of owner: 6
device ID (if special file): 2055
blocksize for filesystem I/O: 4096
number of blocks allocated: 0
time of last access: 1490933835 Fri Mar 31 12:17:15 2017
time of last modification: 1477372695 Tue Oct 25 13:18:15 2016
time of last status change: 1477372695 Tue Oct 25 13:18:15 2016
total size, in bytes: 0 (0.000 exp(+0))
Opening file: /dev/sda7
File information:
Size to process: 107520000000 (100.136 GiB)
time of last access: 1490933835 Fri Mar 31 12:17:15 2017
time of last modification: 1477372695 Tue Oct 25 13:18:15 2016
time of last status change: 1477372695 Tue Oct 25 13:18:15 2016
ID of device containing file: 5
inode number: 6047
protection: 60660 total size, in bytes: 0 (0.000 exp(+0))
(block device)
Opening file: /dev/sda7
number of hard links: 1
Size to process: 107520000000 (100.136 GiB)
File information:
user ID of owner: 0
group ID of owner: 6
device ID (if special file): 2055
blocksize for filesystem I/O: 4096
number of blocks allocated: 0
ID of device containing file: 5
inode number: 6047
protection: 60660 (block device)
number of hard links: 1
user ID of owner: 0
group ID of owner: 6
device ID (if special file): 2055
blocksize for filesystem I/O: 4096
number of blocks allocated: 0
Opening file: /dev/sda7
File information:
ID of device containing file: 5
inode number: 6047
protection: 60660 (block device)
number of hard links: 1
Opening file: /dev/sda7
user ID of owner: 0
group ID of owner: 6
File information:
device ID (if special file): 2055
blocksize for filesystem I/O: 4096
number of blocks allocated: 0
time of last access: 1490933835 Fri Mar 31 12:17:15 2017
time of last modification: 1477372695 Tue Oct 25 13:18:15 2016
time of last status change: 1477372695 Tue Oct 25 13:18:15 2016
ID of device containing file: 5
time of last access: 1490933835 Fri Mar 31 12:17:15 2017
inode number: 6047
protection: 60660 (block device)
number of hard links: 1
user ID of owner: 0
time of last modification: 1477372695 Tue Oct 25 13:18:15 2016
group ID of owner: 6
total size, in bytes: 0 (0.000 exp(+0))
time of last status change: 1477372695 Tue Oct 25 13:18:15 2016
device ID (if special file): 2055
blocksize for filesystem I/O: 4096
number of blocks allocated: 0
total size, in bytes: 0 (0.000 exp(+0))
Size to process: 107520000000 (100.136 GiB)
Size to process: 107520000000 (100.136 GiB)
time of last access: 1490933835 Fri Mar 31 12:17:15 2017
Opening file: /dev/sda7
time of last modification: 1477372695 Tue Oct 25 13:18:15 2016
File information:
time of last status change: 1477372695 Tue Oct 25 13:18:15 2016
ID of device containing file: 5
total size, in bytes: 0 (0.000 exp(+0))
inode number: 6047
protection: 60660 (block device)
number of hard links: 1
user ID of owner: 0
group ID of owner: 6
device ID (if special file): 2055
blocksize for filesystem I/O: 4096
Size to process: 107520000000 (100.136 GiB)
number of blocks allocated: 0
time of last access: 1490933835 Fri Mar 31 12:17:15 2017
time of last modification: 1477372695 Tue Oct 25 13:18:15 2016
Opening file: /dev/sda7
time of last status change: 1477372695 Tue Oct 25 13:18:15 2016
File information:
total size, in bytes: 0 (0.000 exp(+0))
ID of device containing file: 5
inode number: 6047
Size to process: 107520000000 (100.136 GiB)
protection: 60660 (block device)
number of hard links: 1
user ID of owner: 0
group ID of owner: 6
device ID (if special file): 2055
blocksize for filesystem I/O: 4096
number of blocks allocated: 0
time of last access: 1490933835 Fri Mar 31 12:17:15 2017
time of last modification: 1477372695 Tue Oct 25 13:18:15 2016
time of last status change: 1477372695 Tue Oct 25 13:18:15 2016
Opening file: /dev/sda7
total size, in bytes: 0 (0.000 exp(+0))
File information:
ID of device containing file: 5
Size to process: 107520000000 (100.136 GiB)
inode number: 6047
protection: 60660 (block device)
number of hard links: 1
user ID of owner: 0
group ID of owner: 6
device ID (if special file): 2055
blocksize for filesystem I/O: 4096
number of blocks allocated: 0
time of last access: 1490933835 Fri Mar 31 12:17:15 2017
time of last modification: 1477372695 Tue Oct 25 13:18:15 2016
time of last status change: 1477372695 Tue Oct 25 13:18:15 2016
total size, in bytes: 0 (0.000 exp(+0))
Size to process: 107520000000 (100.136 GiB)
time of last access: 1490933835 Fri Mar 31 12:17:15 2017
time of last modification: 1477372695 Tue Oct 25 13:18:15 2016
time of last status change: 1477372695 Tue Oct 25 13:18:15 2016
total size, in bytes: 0 (0.000 exp(+0))
Size to process: 107520000000 (100.136 GiB)
Opening file: /dev/sda7
File information:
Opening file: /dev/sda7
File information:
ID of device containing file: 5
inode number: 6047
protection: 60660 (block device)
number of hard links: 1
ID of device containing file: 5
user ID of owner: 0
inode number: 6047
group ID of owner: 6
protection: 60660 device ID (if special file): 2055
(block device)
blocksize for filesystem I/O: 4096
number of hard links: 1
number of blocks allocated: 0
user ID of owner: 0
group ID of owner: 6
device ID (if special file): 2055
blocksize for filesystem I/O: 4096
number of blocks allocated: 0
time of last access: 1490933835 Fri Mar 31 12:17:15 2017
time of last access: 1490933835 Fri Mar 31 12:17:15 2017
time of last modification: 1477372695 Tue Oct 25 13:18:15 2016
time of last modification: 1477372695 Tue Oct 25 13:18:15 2016
time of last status change: 1477372695 Tue Oct 25 13:18:15 2016
time of last status change: 1477372695 Tue Oct 25 13:18:15 2016
total size, in bytes: 0 (0.000 exp(+0))
total size, in bytes: 0 (0.000 exp(+0))
Size to process: 107520000000 (100.136 GiB)
Size to process: 107520000000 (100.136 GiB)
Opening file: /dev/sda7
File information:
ID of device containing file: 5
inode number: 6047
protection: 60660 (block device)
number of hard links: 1
user ID of owner: 0
group ID of owner: 6
device ID (if special file): 2055
blocksize for filesystem I/O: 4096
number of blocks allocated: 0
time of last access: 1490933835 Fri Mar 31 12:17:15 2017
time of last modification: 1477372695 Tue Oct 25 13:18:15 2016
time of last status change: 1477372695 Tue Oct 25 13:18:15 2016
total size, in bytes: 0 (0.000 exp(+0))
Size to process: 107520000000 (100.136 GiB)
Worker(2): 1.12% done. 2017-09-29 10:22:56 ETA(in 00:03:12). Processing speed: 44.000 MiB/sec
Worker(3): 1.12% done. 2017-09-29 10:22:56 ETA(in 00:03:12). Processing speed: 44.000 MiB/sec
....
....
....
Worker(11): 97.77% done. 2017-09-29 10:23:21 ETA(in 00:00:02). Processing speed: 87.828 MiB/sec
Worker(0): 97.74% done. 2017-09-29 10:23:21 ETA(in 00:00:02). Processing speed: 87.844 MiB/sec
Worker(9): 97.74% done. 2017-09-29 10:23:21 ETA(in 00:00:02). Processing speed: 87.844 MiB/sec
Worker(8): 99.80% done. 2017-09-29 10:23:20 ETA(in 00:00:00). Processing speed: 87.828 MiB/sec
Worker(11): 98.80% done. 2017-09-29 10:23:21 ETA(in 00:00:01). Processing speed: 87.828 MiB/sec
Worker(0): 98.77% done. 2017-09-29 10:23:21 ETA(in 00:00:01). Processing speed: 87.828 MiB/sec
Worker(9): 98.77% done. 2017-09-29 10:23:21 ETA(in 00:00:01). Processing speed: 87.828 MiB/sec
Worker(0): 99.80% done. 2017-09-29 10:23:21 ETA(in 00:00:00). Processing speed: 87.828 MiB/sec
Worker(11): 99.83% done. 2017-09-29 10:23:21 ETA(in 00:00:00). Processing speed: 87.828 MiB/sec
Worker(9): 99.80% done. 2017-09-29 10:23:21 ETA(in 00:00:00). Processing speed: 87.844 MiB/sec
All workers finished in 219 sec
[root@idaross29 undrop-for-innodb]# ./stream_parser -f /dev/sda7 -t 105000000k
Could not create directory pages-sda7
mkdir(): File exists
4空执、創(chuàng)建數(shù)據(jù)字典表
recover_dictionary.sh
修改 mysql 登陸相關(guān)命令 mysql -u -p -S
# ./recover_dictionary.sh
Generating dictionary tables dumps... OK
Creating test database ... OK
Creating dictionary tables in database test:
SYS_TABLES ... OK
SYS_COLUMNS ... OK
SYS_INDEXES ... OK
SYS_FIELDS ... OK
All OK
Loading dictionary tables data:
SYS_TABLES ... 24 recs OK
SYS_COLUMNS ... 148 recs OK
SYS_INDEXES ... 14 recs OK
SYS_FIELDS ... 34 recs OK
All OK
localhost.test>show tables;
+----------------+
| Tables_in_test |
+----------------+
| SYS_COLUMNS |
| SYS_FIELDS |
| SYS_INDEXES |
| SYS_TABLES |
5、查詢需要恢復(fù)的表的index_id信息
localhost.test>select * from SYS_TABLES where name like 'test/t_recover';
+----------------+----+--------+------+--------+---------+--------------+-------+
| NAME | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+----------------+----+--------+------+--------+---------+--------------+-------+
| test/t_recover | 25 | 2 | 1 | 0 | 80 | | 11 |
+----------------+----+--------+------+--------+---------+--------------+-------+
1 row in set (0.02 sec)
localhost.test>select * from SYS_INDEXES where table_id = 25;
+----------+----+---------+----------+------+-------+---------+
| TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+----+---------+----------+------+-------+---------+
| 25 | 28 | PRIMARY | 1 | 3 | 11 | 3 |
+----------+----+---------+----------+------+-------+---------+
1 row in set (0.01 sec)
可以看到被truncate的表的index_id 為28穗椅，我們應(yīng)該進一步從28 的page中獲取數(shù)據(jù)辨绊。
6、拿到表結(jié)構(gòu)匹表，并檢查數(shù)據(jù)是否可以恢復(fù)
- 表結(jié)構(gòu)
cat t_recover.sql
create table t_recover(id int primary key,name varchar(20));
- 確認(rèn)數(shù)據(jù)是否存在
undrop-for-innodb]# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000028.page -t t_recover.sql | head -5
SET FOREIGN_KEY_CHECKS=0;
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (0 0)
LOAD DATA LOCAL INFILE '-- Page id: 3, Found records: 0, Lost records: NO, Leaf page: YES
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (0 0)
/data1/jinlin3/undrop-for-innodb/dumps/default/t_recover' REPLACE INTO TABLE `t_recover` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't_recover\t' (`id`, `name`);
7门坷、抽取 Page 中的數(shù)據(jù)
undrop-for-innodb]# ./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000028.page -t t_recover.sql > dumps/default/t_recover 2> dumps/default/t_recover_load.sql
8、加載數(shù)據(jù)
source dumps/default/t_recover_load.sql
但是袍镀，我測試是 5.6 默蚌，數(shù)據(jù)被 purge 掉了
undrop-for-innodb]# cat dumps/default/t_recover
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (0 0)
-- Page id: 3, Found records: 0, Lost records: NO, Leaf page: YES
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (0 0)
-- Page id: 3, Found records: 0, Lost records: NO, Leaf page: YES
undrop-for-innodb]# cat dumps/default/t_recover_load.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/data1/jinlin3/undrop-for-innodb/dumps/default/t_recover' REPLACE INTO TABLE `t_recover` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't_recover\t' (`id`, `name`);
若 truncate table 后，立刻 shutdown MySQL流椒，文件是有內(nèi)容敏簿，但不對懊饕病宣虾！
undrop-for-innodb]# cat dumps/default/t_truncate
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (17 17)
0000000B0000 00000000000025 t_truncate -2147483648 ""
0000000C0000 00000000000025 t_truncate -2147483648 "\0\0@?"
0000000D0000 00000000000025 t_truncate -2147483648 "\0\0@\b"
0000000E0000 00000000000025 t_truncate -2147483648 ""
0000000E0000 00010000000025 t_truncate -2147483648 "\0"
0000000F0000 00000000000025 t_truncate -2147483648 "\0\0"
000000100000 00000000000025 t_truncate -2147483648 "\0\0"
000000120000 00030000000025 t_truncate -2147483648 "\0\0@ps"
000000130000 00000000000025 t_truncate -2147483648 ""
000000140000 00000000000025 t_truncate -2147483648 "\0\0"
000000140000 00010000000025 t_truncate -2147483648 "\0\0"
000000150000 00000000000025 t_truncate -2147483648 ""
000000160000 00000000000025 t_truncate -2147483648 ""
000000170000 00000000000025 t_truncate -2147483648 "\0\0"
000000180000 00000000000025 t_truncate -2147483648 "\0\0@ka"
0000001A0000 00000000000025 t_truncate -2147483648 ""
0000001C0000 00000000000025 t_truncate -2147483648 ""
-- Page id: 3, Found records: 17, Lost records: NO, Leaf page: YES
<u>這是一個悲傷的故事！</u>
其他參考：
undrop for innodb c_parser 源碼分析
MySQL 如何對InnoDB使用Undrop來恢復(fù)InnoDB數(shù)據(jù)
希望小伙伴永遠(yuǎn)不需要用到此方法温数，備份才是王道绣硝！
