转载自&＃xff1a;https://www.cnblogs.com/gomysql/p/3586822.html
前几天写过update或者delete忘加where条件的数据恢复。今天介绍一款开源的MySQL数据库InnoDB数据恢复工具&＃xff1a;innodb-tools&＃xff0c;它通过从原始数据文件中提取表的行记录&＃xff0c;实现从丢失的或者被毁坏的MySQL表中恢复数据。例如&＃xff0c;当你不小心执行DROP TABLE、TRUNCATE TABLE之后&＃xff0c;可以通过以下方式恢复数据。
在介绍innodb-tools工具进行数据恢复之前&＃xff0c;首先明确以下几点&＃xff1a;
1、这个工具只能对InnoDB/XtraDB表有效&＃xff0c;而无法恢复MyISAM表
2、这个工具是以保存的MySQL数据文件进行恢复的&＃xff0c;而不用MySQL Server运行。
3、不能保证数据总一定可被恢复。例如&＃xff0c;被重写的数据不能被恢复&＃xff0c;这种情况下可能需要针对系统或物理的方式来恢复&＃xff0c;不属于本工具的范畴。
4、恢复的最好时机是当你发现数据丢失时&＃xff0c;尽快备份MySQL数据文件。
5、使用这个工具需要手动做一些工作&＃xff0c;并不是全自动完成的。
6、恢复过程依赖于你对丢失数据的了解程度&＃xff0c;在恢复过程中可能需要在不同版本的数据之间做出选择。那么如果你越了解自己的数据&＃xff0c;恢复的可能性就越大。
接下来&＃xff0c;下面通过一个例子来介绍如何通过这个工具进行恢复。
1. 前提条件
首先&＃xff0c;需要理解的是innodb-tools工具不是通过连接到在线的database进行数据恢复&＃xff0c;而是通过离线拷贝数据的方式进行的。注意&＃xff1a;不要在MySQL运行的时候&＃xff0c;直接拷贝InnoDB文件&＃xff0c;这样是不安全的&＃xff0c;会影响数据恢复过程。
为了完成数据恢复&＃xff0c;必须知道将要被恢复的表结构&＃xff08;列名、数据类型&＃xff09;。最简单的方式就是SHOW CREATE TABLE&＃xff0c;当然后续会介绍几种可替代的方式。因此&＃xff0c;如果有一个MySQL server作为备份&＃xff0c;即使数据是很早的甚至表中没有记录&＃xff0c;可以有助于使用innodb-tools工具进行恢复。不过这个不是必须的。
2.简单例子
mysql> use book;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> select count(*) from million_words;
&＃43;----------&＃43;
| count(*) |
&＃43;----------&＃43;
| 1000000 |
&＃43;----------&＃43;
1 row in set (0.13 sec)mysql> truncate million_words;
Query OK, 0 rows affected (0.23 sec)mysql>
3.构建工具
1、下载解压innodb-tools工具源码&＃xff1a;
安装依赖&＃xff0c;否则抛出如下错误&＃xff1a;
/usr/bin/ld: cannot find -lrt
collect2: ld returned 1 exit
status
make: *** [page_parser] Error 1
[root&＃64;localhost ~]# yum install glibc-static -y
wget https://launchpad.net/percona-data-recovery-tool-for-innodb/trunk/release-0.5/&＃43;download/percona-data-recovery-tool-for-innodb-0.5.tar.gz
tar -xvf percona-data-recovery-tool-for-innodb-0.5.tar.gz -C /usr/local/
cd /usr/local
ln -s percona-data-recovery-tool-for-innodb-0.5 percona-data-recovery-tool
2、进入解压后根目录下的mysql-source目录&＃xff0c;运行配置命令&＃xff08;注&＃xff1a;不运行make命令&＃xff09;:
[root&＃64;localhost mysql-source]# cd /usr/local/percona-data-recovery-tool/mysql-source/
[root&＃64;localhost mysql-source]# ./configure
3、完成配置步骤后&＃xff0c;回到解压后的根目录&＃xff0c;运行make命令&＃xff0c;编译生成page_parser和constraints_parser工具&＃xff1a;
[root&＃64;localhost mysql-source]# cd ..
[root&＃64;localhost percona-data-recovery-tool]# make
page_parser工具将根据InnoDB的底层实现原理&＃xff0c;解析表的页和行结构。constraints_parser工具暂时不使用&＃xff0c;后续还需要在定义表结构之后&＃xff0c;重新编译生成它。
4. 提取需要的页
InnoDB页的默认大小是16K&＃xff0c;每个页属于一个特定表中的一个特定的index。page_parser工具通过读取数据文件&＃xff0c;根据页头中的index ID&＃xff0c;拷贝每个页到一个单独的文件中。
如果启用了innodb_file_per_table&＃61;1&＃xff0c;也就是独立表空间文件&＃xff0c;那么将无法完全恢复数据&＃xff0c;本人也已经测试过&＃xff0c;官方文档也没有提到启用独立表空间是否可以成功&＃xff0c;在官方文档中&＃xff0c;是设置innodb_file_per_table&＃61;0。
参考资料如下&＃xff1a;
http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:example_data_loss_scenario
4.1 切分页
运行page_parser工具进行切分&＃xff1a;
如果MySQL是5.0之前的版本&＃xff0c;InnoDB采取的是REDUNDANT格式&＃xff0c;运行以下命令&＃xff1a;
./page_parser -4 -f /path/to/ibdata1
如果MySQL是5.0以后的版本&＃xff0c;InnoDB采取的是COMPACT格式&＃xff0c;运行以下命令&＃xff1a;
./page_parser -5 -f /path/to/ibdata1
运行后&＃xff0c;page_parser工具会创建一个pages-
[root&＃64;localhost percona-data-recovery-tool]# ./page_parser -5 -f /data/mysql/ibdata1
Opening file: /data/mysql/ibdata1:
2053 ID of device containing file
130915 inode number
33200 protection
1 number of hard links
500 user ID of owner
501 group ID of owner
0 device ID (if special file)
371195904 total size, in bytes
4096 blocksize for filesystem I/O
725000 number of blocks allocated
1394179630 time of last access
1394179669 time of last modification
1394179669 time of last status change
371195904 Size to process in bytes
104857600 Disk cache size in bytes
1.00% done. 2014-03-07 16:31:45 ETA(in 00:04 hours). Processing speed: 1237320 B/sec
2.00% done. 2014-03-07 16:28:27 ETA(in 00:01 hours). Processing speed: 3726376 B/sec
8.80% done. 2014-03-07 16:27:04 ETA(in 00:00 hours). Processing speed: 25231360 B/sec
9.80% done. 2014-03-07 16:28:22 ETA(in 00:01 hours). Processing speed: 3719168 B/sec
20.80% done. 2014-03-07 16:27:00 ETA(in 00:00 hours). Processing speed: 40819863 B/sec
28.25% done. 2014-03-07 16:27:35 ETA(in 00:00 hours). Processing speed: 6912218 B/sec
40.96% done. 2014-03-07 16:27:02 ETA(in 00:00 hours). Processing speed: 47167140 B/sec
51.43% done. 2014-03-07 16:27:03 ETA(in 00:00 hours). Processing speed: 38881628 B/sec
56.49% done. 2014-03-07 16:27:37 ETA(in 00:00 hours). Processing speed: 4698112 B/sec
76.23% done. 2014-03-07 16:27:05 ETA(in 00:00 hours). Processing speed: 73252864 B/sec
83.23% done. 2014-03-07 16:27:07 ETA(in 00:00 hours). Processing speed: 26001408 B/sec
84.74% done. 2014-03-07 16:28:00 ETA(in 00:00 hours). Processing speed: 1117388 B/sec
90.79% done. 2014-03-07 16:27:12 ETA(in 00:00 hours). Processing speed: 22452811 B/sec
99.00% done. 2014-03-07 16:27:12 ETA(in 00:00 hours). Processing speed: 30479427 B/sec
[root&＃64;localhost percona-data-recovery-tool]#
[root&＃64;localhost percona-data-recovery-tool]# ll pages-1394180806/FIL_PAGE_INDEX/0-1
total 16
-rw-r--r-- 1 root root 16384 Mar 7 16:26 1-00000008.page
4.2 选择需要的Index ID
一般来说&＃xff0c;我们需要根据表的主键&＃xff08;PRIMARY index&＃xff09;进行恢复&＃xff0c;主键中包含了所有的行。以下是一些可以实现的步骤&＃xff1a;
如果数据库仍处于运行状态&＃xff0c;并且表没有被drop掉&＃xff0c;那么可以启动InnoDB Tablespace Monitor&＃xff0c;输出所有表和indexes&＃xff0c;index IDs到MySQL server的错误日志文件。创建innodb_table_monitor表用于收集innodb存储引擎表及其索引的存储方式&＃xff1a;
mysql> CREATE TABLE test.innodb_table_monitor (id int) ENGINE&＃61;InnoDB;
Query OK, 0 rows affected (0.31 sec)mysql>
如果innodb_table_monitor已经存在&＃xff0c;drop表然后重新create表。等MySQL错误日志输出后&＃xff0c;可以drop掉这张表以停止打印输出更多的监控。一个输出的例子如下&＃xff1a;
TABLE: name book/million_words, id 239, flags 1, columns 5, indexes 2, appr.rows 0COLUMNS: id: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4; word: DATA_VARMYSQL DATA_NOT_NULL len 150; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DAT
A_SYS prtype 258 len 7;INDEX: name PRIMARY, id 374, fields 1/4, uniq 1, type 3root page 16419, appr.key vals 0, leaf pages 1, size pages 1FIELDS: id DB_TRX_ID DB_ROLL_PTR wordINDEX: name word, id 375, fields 1/2, uniq 1, type 2root page 16420, appr.key vals 0, leaf pages 1, size pages 1FIELDS: word id
这里&＃xff0c;我们恢复的是sakila库下的customer表&＃xff0c;从上面可以获取其主键信息&＃xff1a;
INDEX: name PRIMARY, id 374, fields 1/4, uniq 1, type 3
Index ID是0 374&＃xff0c;因此我们需要恢复的InnoDB页位于0-374子目录下。
备注&＃xff1a;参考文档原文中之描述了以上这种获取表的index ID的方法&＃xff0c;本文在实际操作中&＃xff0c;采取了更简单的一种方式&＃xff0c;即直接恢复page_parser生成的所有InnoDB页。实践证明这种方法也是可行的.
5. 生成表定义
步骤4中&＃xff0c;我们已经找到了需要的数据&＃xff0c;接下来需要找到表结构&＃xff0c;创建表定义&＃xff0c;将其编译到constraints_parser中&＃xff0c;然后使用这个工具从InnoDB页中提取表中的行。
表定义包含了表中的列、列顺序、数据类型。如果MySQL server仍处于运行且表未被drop掉&＃xff0c;那么简单实用SHOW CREATE TABLE就可以收集到这些信息。接下来将使用这些表结构信息来创建一个C结构体标识的表定义&＃xff0c;然后编译到constraints_parser工具。C结构体的定义存放在include/table_defs.h中。
最简单的方式是create_defs.pl Perl 脚本&＃xff0c;连接到MySQL server&＃xff0c;读取SHOW CREATE TABLE的结果&＃xff0c;输出生成的表定义到标准输出。下面是个例子&＃xff0c;其中直接将结果重定向到了include/table_defs.h中&＃xff1a;
[root&＃64;localhost percona-data-recovery-tool]# ./create_defs.pl --host&＃61;127.0.0.1 --user&＃61;root --password&＃61;yayun --db&＃61;book --table&＃61;million_words > include/table_defs.h
下面是生成的表定义&＃xff1a;
#ifndef table_defs_h
#define table_defs_h// Table definitions
table_def_t table_definitions[] &＃61; {{name: "million_words",{{ /* int(10) unsigned */name: "id",type: FT_UINT,fixed_length: 4,has_limits: FALSE,limits: {can_be_null: FALSE,uint_min_val: 0,uint_max_val: 4294967295ULL},can_be_null: FALSE},{ /* */name: "DB_TRX_ID",type: FT_INTERNAL,fixed_length: 6,can_be_null: FALSE},{ /* */name: "DB_ROLL_PTR",type: FT_INTERNAL,fixed_length: 7,can_be_null: FALSE},{ /* varchar(50) */name: "word",type: FT_CHAR,min_length: 0,max_length: 150,has_limits: FALSE,limits: {can_be_null: FALSE,char_min_len: 0,char_max_len: 150,char_ascii_only: TRUE},can_be_null: FALSE},{ type: FT_NONE }}},
};#endif
如果需要&＃xff0c;可以根据需要编辑修改include/table_defs.h&＃xff1b;然后根据include/table_defs.h&＃xff0c;重新编译constraints_parser工具&＃xff1a;
[root&＃64;localhost percona-data-recovery-tool]# make
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS&＃61;64 -D_LARGEFILE64_SOURCE&＃61;1 -D_LARGEFILE_SOURCE&＃61;1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -c tables_dict.c -o lib/tables_dict.o
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS&＃61;64 -D_LARGEFILE64_SOURCE&＃61;1 -D_LARGEFILE_SOURCE&＃61;1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -c print_data.c -o lib/print_data.o
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS&＃61;64 -D_LARGEFILE64_SOURCE&＃61;1 -D_LARGEFILE_SOURCE&＃61;1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -c check_data.c -o lib/check_data.o
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS&＃61;64 -D_LARGEFILE64_SOURCE&＃61;1 -D_LARGEFILE_SOURCE&＃61;1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -o constraints_parser constraints_parser.c lib/tables_dict.o lib/print_data.o lib/check_data.o lib/libut.a lib/libmystrings.a
gcc -DHAVE_OFFSET64_T -D_FILE_OFFSET_BITS&＃61;64 -D_LARGEFILE64_SOURCE&＃61;1 -D_LARGEFILE_SOURCE&＃61;1 -Wall -O3 -g -I include -I mysql-source/include -I mysql-source/innobase/include -static -lrt -o page_parser page_parser.c lib/tables_dict.o lib/libut.a
[root&＃64;localhost percona-data-recovery-tool]#
6. 从页中提取行记录
6.1 合并页到一个文件
前面已经提到&＃xff0c;我们需要恢复的index ID 0 374&＃xff0c;包含数据的页位于pages-1394180806/FIL_PAGE_INDEX/0-374/ 目录。
[root&＃64;localhost percona-data-recovery-tool]# cd pages-1394180806/FIL_PAGE_INDEX/0-374/
[root&＃64;localhost 0-374]# ll
total 36176
-rw-r--r-- 1 root root 16384 Mar 7 16:26 104-00000306.page
-rw-r--r-- 1 root root 16384 Mar 7 16:26 106-00000309.page
-rw-r--r-- 1 root root 16384 Mar 7 16:26 107-00000310.page
-rw-r--r-- 1 root root 16384 Mar 7 16:26 109-00000312.page
-rw-r--r-- 1 root root 16384 Mar 7 16:26 110-00000314.page
-rw-r--r-- 1 root root 16384 Mar 7 16:26 11-00001416.page
-rw-r--r-- 1 root root 16384 Mar 7 16:26 112-00000316.page
-rw-r--r-- 1 root root 16384 Mar 7 16:27 11732-00016419.page
-rw-r--r-- 1 root root 16384 Mar 7 16:26 13-00016419.page
-rw-r--r-- 1 root root 16384 Mar 7 16:26 16-00003437.page
................................
................................
输入以下命令进行合并页&＃xff1a;
[root&＃64;localhost percona-data-recovery-tool]# find pages-1394180806/FIL_PAGE_INDEX/0-374/ -type f -name &＃39;*.page&＃39; | sort -n | xargs cat > pages-1394180806/FIL_PAGE_INDEX/0-374/customer_pages_concatenated
[root&＃64;localhost percona-data-recovery-tool]#
生成的结果文件:pages-1394180806/FIL_PAGE_INDEX/0-374/customer_pages_concatenated,将作为constraints_parser工具的输入。
6.2 运行constraints_parser工具
下面到恢复数据最核心的步骤——运行constraints_parser工具以提取行记录。和page_parser工具一样&＃xff0c;需要通过-5或-4参数指定InnoDB页格式&＃xff08;COMPACT/REDUNDANT&＃xff09;&＃xff0c;-f指定输入文件。
回到例子中&＃xff0c;我们可以这样运行constraints_parser工具
我们可以这样运行constraints_parser工具&＃xff08;下面的命令是恢复一个单一的页&＃xff0c;也可以直接恢复经过6.1步骤合并所有页之后的文件&＃xff09;&＃xff1a;
./constraints_parser -5 -f pages-1394180806/FIL_PAGE_INDEX/0-374/76-00003397.page
会输出恢复数据相关语句:
LOAD DATA INFILE &＃39;/usr/local/percona-data-recovery-tool/dumps/default/million_words&＃39; REPLACE INTO TABLE &＃96;million_words&＃96; FIELDS TERMINATED BY &＃39;\t&＃39; OPTIONALLY ENCLOSED BY &＃39;"&＃39; LINES STARTING BY &＃39;million_words\t&＃39; (id, word);
既然这样&＃xff0c;那么我们就创建dumps/default/文件夹
[root&＃64;localhost percona-data-recovery-tool]# pwd
/usr/local/percona-data-recovery-tool
[root&＃64;localhost percona-data-recovery-tool]# mkdir dumps/default -p
[root&＃64;localhost percona-data-recovery-tool]#
恢复全部页的数据到/dumps/default/million_words
[root&＃64;localhost percona-data-recovery-tool]# ./constraints_parser -5 -f pages-1394180806/FIL_PAGE_INDEX/0-374/customer_pages_concatenated >> dumps/default/million_words
输出提示如下,省略了一些内容&＃xff1a;
95.98% done
96.42% done
96.86% done
97.30% done
97.74% done
98.19% done
98.63% done
99.07% done
99.51% done
99.96% done
LOAD DATA INFILE &＃39;/usr/local/percona-data-recovery-tool/dumps/default/million_words&＃39; REPLACE INTO TABLE &＃96;million_words&＃96; FIELDS TERMINATED BY &＃39;\t&＃39; OPTIONALLY ENCLOSED BY &＃39;"&＃39; LINES STARTING BY &＃39;million_words\t&＃39; (id, word);
7. 导入数据到数据库中
mysql> use book
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql> LOAD DATA INFILE &＃39;/usr/local/percona-data-recovery-tool/dumps/default/million_words&＃39; REPLACE INTO TABLE &＃96;million_words&＃96; FIELDS TERMINATED BY &＃39;\t&＃39; OPTIONALLY ENCLOSED BY &＃39;"&＃39; LINES STARTING BY &＃39;million_words\t&＃39; (id, word);
Query OK, 1062600 rows affected (14.99 sec)
Records: 1031300 Deleted: 31300 Skipped: 0 Warnings: 0mysql> select count(*) from million_words;
&＃43;----------&＃43;
| count(*) |
&＃43;----------&＃43;
| 1000000 |
&＃43;----------&＃43;
1 row in set (0.20 sec)mysql>
可以看见数据已经恢复回来&＃xff0c;我测试的MySQL版本如下&＃xff1a;
mysql> select version();
&＃43;-------------&＃43;
| version() |
&＃43;-------------&＃43;
| 5.5.25a-log |
&＃43;-------------&＃43;
1 row in set (0.00 sec)mysql> show variables like &＃39;innodb_file_per_table&＃39;;
&＃43;-----------------------&＃43;-------&＃43;
| Variable_name | Value |
&＃43;-----------------------&＃43;-------&＃43;
| innodb_file_per_table | OFF |
&＃43;-----------------------&＃43;-------&＃43;
1 row in set (0.00 sec)mysql>
希望各位小伙伴永远不需要用到此方法&＃xff0c;备份才是王道啊&＃xff01;
参考资料如下&＃xff1a;
http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:start
http://hidba.org/?p&＃61;852
京公网安备 11010802041100号