介绍
当MySQL数据库没有备份时，可以通过TwinDB Data Recovery Toolkit从操作系统底层恢复MySQL数据文件。目前它可以在CentOS 4、5、6、7、Debian、Ubuntu和Amazon Linux上运行，只支持64位系统。
支持以下故障场景：
- 表或库被drop
- InnoDB表空间损坏
- 硬盘故障
- 文件系统损坏
- delete删除表数据
- 表被truncate
- InnoDB文件（.ibd）被删除
- 表被drop然后create
安装
[root]# git clone https://github.com/twindb/undrop-for-innodb.git
[root]# yum install -y make gcc flex bison
[root]# cd undrop-for-innodb/
[root undrop-for-innodb-develop]# make
[undrop-for-innodb-develop]# gcc `/usr/local/mysql/bin/mysql_config --cflags` `/usr/local/mysql/bin/mysql_config --libs` -o sys_parser sys_parser.c场景1 表被drop
环境介绍
MySQL 5.7.21
innodb_file_per_table = ON
engine = innodb/myisam
提示：如果innodb_file_per_table = OFF，那么只需要解析ibdata1。
模拟故障
(root@localhost) [gaoyu]> create table items(id int not null,name char(100),age int check( age > 18 and age < 55),primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
(root@localhost) [gaoyu]> select * from items;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | gaoyu | 32 |
| 2 | yuanyuan | 30 |
| 3 | qiqi | 2 |
+----+----------+------+
(root@localhost) [gaoyu]> drop table items;故障恢复
解析ibdata1
[undrop-for-innodb-develop]# ./stream_parser -f /home/mysql_data/5.7_data/ibdata1
Opening file: /home/mysql_data/5.7_data/ibdata1
File information:
ID of device containing file: 64770
inode number: 4965101
protection: 100750 (regular file)
number of hard links: 1
user ID of owner: 1001
group ID of owner: 1001
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 417792
time of last access: 1640701218 Tue Dec 28 22:20:18 2021
time of last modification: 1640701220 Tue Dec 28 22:20:20 2021
time of last status change: 1640701220 Tue Dec 28 22:20:20 2021
total size, in bytes: 213909504 (204.000 MiB)
Size to process: 213909504 (204.000 MiB)
Worker(0): 58.72% done. 2021-12-28 22:22:49 ETA(in 00:00:00). Processing speed: 111.781 MiB/sec
Worker(0): 82.20% done. 2021-12-28 22:22:56 ETA(in 00:00:03). Processing speed: 11.977 MiB/sec
All workers finished in 5 sec
解析datadir所在磁盘
[rootundrop-for-innodb-develop]# df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/centos-root 52403200 49082152 3321048 94% /
devtmpfs 3982932 0 3982932 0% /dev
tmpfs 4000008 12 3999996 1% /dev/shm
tmpfs 4000008 53596 3946412 2% /run
tmpfs 4000008 0 4000008 0% /sys/fs/cgroup
/dev/sdb 20961280 3300288 17660992 16% /data
/dev/sda1 1038336 182896 855440 18% /boot
/dev/mapper/centos-home 43093444 8236104 34857340 20% /home
tmpfs 800004 0 800004 0% /run/user/0
tmpfs 800004 36 799968 1% /run/user/1000
/dev/sr0 4480476 4480476 0 100% /run/media/haha/CentOS 7 x86_64
[undrop-for-innodb-develop]# ./stream_parser -f /dev/mapper/centos-home -t 43093444k
Opening file: /dev/mapper/centos-home
File information:
ID of device containing file: 5
inode number: 38652
protection: 60660 (block device)
number of hard links: 1
user ID of owner: 0
group ID of owner: 6
device ID (if special file): 64770
blocksize for filesystem I/O: 4096
number of blocks allocated: 0
time of last access: 1640701711 Tue Dec 28 22:28:31 2021
time of last modification: 1635846679 Tue Nov 2 17:51:19 2021
time of last status change: 1635846679 Tue Nov 2 17:51:19 2021
total size, in bytes: 0 (0.000 exp(+0))
Size to process: 44127686656 (41.097 GiB)
Worker(0): 1.02% done. 2021-12-28 22:58:30 ETA(in 00:03:16). Processing speed: 211.605 MiB/sec
Worker(0): 2.03% done. 2021-12-28 22:58:30 ETA(in 00:03:14). Processing speed: 211.586 MiB/sec
Worker(0): 3.04% done. 2021-12-28 22:56:53 ETA(in 00:01:36). Processing speed: 423.691 MiB/sec
Worker(0): 4.04% done. 2021-12-28 22:56:53 ETA(in 00:01:35). Processing speed: 423.984 MiB/sec
Worker(0): 5.05% done. 2021-12-28 22:56:53 ETA(in 00:01:34). Processing speed: 423.781 MiB/sec
Worker(0): 6.06% done. 2021-12-28 22:58:27 ETA(in 00:03:06). Processing speed: 211.656 MiB/sec
Worker(0): 7.06% done. 2021-12-28 22:56:54 ETA(in 00:01:32). Processing speed: 423.488 MiB/sec
Worker(0): 8.07% done. 2021-12-28 22:58:26 ETA(in 00:03:02). Processing speed: 211.596 MiB/sec
Worker(0): 9.07% done. 2021-12-28 22:58:26 ETA(in 00:03:00). Processing speed: 211.586 MiB/sec
Worker(0): 10.08% done. 2021-12-28 22:56:56 ETA(in 00:01:29). Processing speed: 423.543 MiB/sec
Worker(0): 11.09% done. 2021-12-28 22:58:25 ETA(in 00:02:56). Processing speed: 211.773 MiB/sec
Worker(0): 12.09% done. 2021-12-28 22:56:57 ETA(in 00:01:27). Processing speed: 423.316 MiB/sec
Worker(0): 13.10% done. 2021-12-28 22:56:57 ETA(in 00:01:26). Processing speed: 424.000 MiB/sec
Worker(0): 14.11% done. 2021-12-28 22:56:57 ETA(in 00:01:25). Processing speed: 424.000 MiB/sec
Worker(0): 15.12% done. 2021-12-28 22:56:57 ETA(in 00:01:24). Processing speed: 423.984 MiB/sec
Worker(0): 16.12% done. 2021-12-28 22:56:57 ETA(in 00:01:23). Processing speed: 424.000 MiB/sec
Worker(0): 17.13% done. 2021-12-28 22:58:20 ETA(in 00:02:44). Processing speed: 211.818 MiB/sec
Worker(0): 18.14% done. 2021-12-28 22:56:58 ETA(in 00:01:21). Processing speed: 423.457 MiB/sec
Worker(0): 19.14% done. 2021-12-28 22:56:58 ETA(in 00:01:20). Processing speed: 423.859 MiB/sec
Worker(0): 20.15% done. 2021-12-28 22:56:58 ETA(in 00:01:19). Processing speed: 423.418 MiB/sec
Worker(0): 21.16% done. 2021-12-28 22:58:17 ETA(in 00:02:36). Processing speed: 211.990 MiB/sec
Worker(0): 22.16% done. 2021-12-28 22:58:17 ETA(in 00:02:34). Processing speed: 211.711 MiB/sec
Worker(0): 23.17% done. 2021-12-28 22:57:00 ETA(in 00:01:16). Processing speed: 423.605 MiB/sec
Worker(0): 24.18% done. 2021-12-28 22:57:00 ETA(in 00:01:15). Processing speed: 423.500 MiB/sec
Worker(0): 25.18% done. 2021-12-28 22:58:15 ETA(in 00:02:28). Processing speed: 211.980 MiB/sec
Worker(0): 26.19% done. 2021-12-28 22:57:01 ETA(in 00:01:13). Processing speed: 423.836 MiB/sec
Worker(0): 27.20% done. 2021-12-28 22:57:01 ETA(in 00:01:12). Processing speed: 423.828 MiB/sec
Worker(0): 28.20% done. 2021-12-28 22:57:01 ETA(in 00:01:11). Processing speed: 423.328 MiB/sec
Worker(0): 29.21% done. 2021-12-28 22:57:01 ETA(in 00:01:10). Processing speed: 423.641 MiB/sec
Worker(0): 30.22% done. 2021-12-28 22:58:11 ETA(in 00:02:18). Processing speed: 211.879 MiB/sec
Worker(0): 31.22% done. 2021-12-28 22:57:02 ETA(in 00:01:08). Processing speed: 423.609 MiB/sec
Worker(0): 32.23% done. 2021-12-28 22:57:02 ETA(in 00:01:07). Processing speed: 423.594 MiB/sec
Worker(0): 33.24% done. 2021-12-28 22:58:09 ETA(in 00:02:12). Processing speed: 211.672 MiB/sec
Worker(0): 34.24% done. 2021-12-28 22:57:03 ETA(in 00:01:05). Processing speed: 423.641 MiB/sec
Worker(0): 35.25% done. 2021-12-28 22:57:03 ETA(in 00:01:04). Processing speed: 424.000 MiB/sec
Worker(0): 36.26% done. 2021-12-28 22:58:07 ETA(in 00:02:06). Processing speed: 211.805 MiB/sec
Worker(0): 37.26% done. 2021-12-28 22:57:04 ETA(in 00:01:02). Processing speed: 423.469 MiB/sec
Worker(0): 38.27% done. 2021-12-28 22:57:04 ETA(in 00:01:01). Processing speed: 423.766 MiB/sec
Worker(0): 39.28% done. 2021-12-28 22:58:05 ETA(in 00:02:00). Processing speed: 211.586 MiB/sec
Worker(0): 40.28% done. 2021-12-28 22:59:05 ETA(in 00:02:57). Processing speed: 141.323 MiB/sec
Worker(0): 41.29% done. 2021-12-28 23:04:02 ETA(in 00:07:46). Processing speed: 52.966 MiB/sec
Worker(0): 42.30% done. 2021-12-28 23:04:02 ETA(in 00:07:38). Processing speed: 52.947 MiB/sec
Worker(0): 43.30% done. 2021-12-28 23:04:02 ETA(in 00:07:30). Processing speed: 52.963 MiB/sec
Worker(0): 44.31% done. 2021-12-28 23:01:13 ETA(in 00:04:36). Processing speed: 84.650 MiB/sec
Worker(0): 45.32% done. 2021-12-28 23:00:18 ETA(in 00:03:37). Processing speed: 105.793 MiB/sec
Worker(0): 46.32% done. 2021-12-28 22:59:24 ETA(in 00:02:40). Processing speed: 141.073 MiB/sec
Worker(0): 47.33% done. 2021-12-28 23:00:17 ETA(in 00:03:29). Processing speed: 105.793 MiB/sec
Worker(0): 48.33% done. 2021-12-28 23:02:02 ETA(in 00:05:08). Processing speed: 70.529 MiB/sec
Worker(0): 49.34% done. 2021-12-28 23:01:10 ETA(in 00:04:11). Processing speed: 84.634 MiB/sec
Worker(0): 50.34% done. 2021-12-28 22:59:30 ETA(in 00:02:28). Processing speed: 141.057 MiB/sec
Worker(0): 51.35% done. 2021-12-28 22:59:30 ETA(in 00:02:25). Processing speed: 141.057 MiB/sec
Worker(0): 52.35% done. 2021-12-28 22:58:41 ETA(in 00:01:34). Processing speed: 211.586 MiB/sec
Worker(0): 53.36% done. 2021-12-28 22:59:29 ETA(in 00:02:19). Processing speed: 141.057 MiB/sec
Worker(0): 54.37% done. 2021-12-28 22:58:42 ETA(in 00:01:30). Processing speed: 211.586 MiB/sec
Worker(0): 55.37% done. 2021-12-28 23:00:13 ETA(in 00:02:57). Processing speed: 105.793 MiB/sec
Worker(0): 56.38% done. 2021-12-28 22:59:29 ETA(in 00:02:10). Processing speed: 141.057 MiB/sec
Worker(0): 57.38% done. 2021-12-28 22:58:45 ETA(in 00:01:24). Processing speed: 211.586 MiB/sec
Worker(0): 58.39% done. 2021-12-28 22:58:45 ETA(in 00:01:22). Processing speed: 211.586 MiB/sec
Worker(0): 59.39% done. 2021-12-28 22:58:45 ETA(in 00:01:20). Processing speed: 211.586 MiB/sec
Worker(0): 60.40% done. 2021-12-28 22:58:45 ETA(in 00:01:18). Processing speed: 211.586 MiB/sec
Worker(0): 61.40% done. 2021-12-28 22:59:25 ETA(in 00:01:55). Processing speed: 141.057 MiB/sec
Worker(0): 62.41% done. 2021-12-28 22:58:46 ETA(in 00:01:14). Processing speed: 211.586 MiB/sec
Worker(0): 63.42% done. 2021-12-28 22:58:46 ETA(in 00:01:12). Processing speed: 211.586 MiB/sec
Worker(0): 64.42% done. 2021-12-28 22:58:46 ETA(in 00:01:10). Processing speed: 211.586 MiB/sec
Worker(0): 65.43% done. 2021-12-28 22:58:46 ETA(in 00:01:08). Processing speed: 211.586 MiB/sec
Worker(0): 66.43% done. 2021-12-28 22:58:46 ETA(in 00:01:06). Processing speed: 211.586 MiB/sec
Worker(0): 67.44% done. 2021-12-28 22:58:46 ETA(in 00:01:04). Processing speed: 211.586 MiB/sec
Worker(0): 68.44% done. 2021-12-28 22:58:46 ETA(in 00:01:02). Processing speed: 211.603 MiB/sec
Worker(0): 69.45% done. 2021-12-28 22:58:46 ETA(in 00:01:00). Processing speed: 211.590 MiB/sec
Worker(0): 70.46% done. 2021-12-28 22:58:46 ETA(in 00:00:58). Processing speed: 211.793 MiB/sec
Worker(0): 71.46% done. 2021-12-28 22:58:46 ETA(in 00:00:56). Processing speed: 212.000 MiB/sec
Worker(0): 72.47% done. 2021-12-28 22:58:46 ETA(in 00:00:54). Processing speed: 211.992 MiB/sec
Worker(0): 73.48% done. 2021-12-28 22:58:46 ETA(in 00:00:52). Processing speed: 212.000 MiB/sec
Worker(0): 74.48% done. 2021-12-28 22:59:13 ETA(in 00:01:16). Processing speed: 141.188 MiB/sec
Worker(0): 75.49% done. 2021-12-28 23:00:03 ETA(in 00:02:01). Processing speed: 84.666 MiB/sec
Worker(0): 76.50% done. 2021-12-28 22:59:39 ETA(in 00:01:33). Processing speed: 105.805 MiB/sec
Worker(0): 77.50% done. 2021-12-28 22:59:39 ETA(in 00:01:29). Processing speed: 105.793 MiB/sec
Worker(0): 78.51% done. 2021-12-28 22:59:17 ETA(in 00:01:04). Processing speed: 141.078 MiB/sec
Worker(0): 79.51% done. 2021-12-28 22:59:59 ETA(in 00:01:41). Processing speed: 84.647 MiB/sec
Worker(0): 80.52% done. 2021-12-28 22:58:58 ETA(in 00:00:38). Processing speed: 211.586 MiB/sec
Worker(0): 81.52% done. 2021-12-28 22:58:58 ETA(in 00:00:36). Processing speed: 211.586 MiB/sec
Worker(0): 82.53% done. 2021-12-28 22:59:17 ETA(in 00:00:52). Processing speed: 141.057 MiB/sec
Worker(0): 83.54% done. 2021-12-28 22:58:59 ETA(in 00:00:32). Processing speed: 211.586 MiB/sec
Worker(0): 84.54% done. 2021-12-28 22:59:16 ETA(in 00:00:46). Processing speed: 141.057 MiB/sec
Worker(0): 85.55% done. 2021-12-28 22:59:00 ETA(in 00:00:28). Processing speed: 211.650 MiB/sec
Worker(0): 86.55% done. 2021-12-28 22:59:00 ETA(in 00:00:26). Processing speed: 211.653 MiB/sec
Worker(0): 87.56% done. 2021-12-28 22:59:14 ETA(in 00:00:37). Processing speed: 141.214 MiB/sec
Worker(0): 88.57% done. 2021-12-28 22:59:01 ETA(in 00:00:22). Processing speed: 211.813 MiB/sec
Worker(0): 89.57% done. 2021-12-28 22:59:01 ETA(in 00:00:20). Processing speed: 211.635 MiB/sec
Worker(0): 90.58% done. 2021-12-28 22:59:01 ETA(in 00:00:18). Processing speed: 211.586 MiB/sec
Worker(0): 91.58% done. 2021-12-28 22:59:01 ETA(in 00:00:16). Processing speed: 211.586 MiB/sec
Worker(0): 92.59% done. 2021-12-28 22:59:01 ETA(in 00:00:14). Processing speed: 211.766 MiB/sec
Worker(0): 93.59% done. 2021-12-28 22:59:01 ETA(in 00:00:12). Processing speed: 211.625 MiB/sec
Worker(0): 94.60% done. 2021-12-28 22:59:01 ETA(in 00:00:10). Processing speed: 211.586 MiB/sec
Worker(0): 95.61% done. 2021-12-28 22:59:01 ETA(in 00:00:08). Processing speed: 211.734 MiB/sec
Worker(0): 96.80% done. 2021-12-28 22:58:56 ETA(in 00:00:02). Processing speed: 504.000 MiB/sec
Worker(0): 98.90% done. 2021-12-28 22:58:57 ETA(in 00:00:01). Processing speed: 440.000 MiB/sec
Worker(0): 99.90% done. 2021-12-28 22:59:06 ETA(in 00:00:00). Processing speed: 42.400 MiB/sec
All workers finished in 234 sec
通过表名（gaoyu/items）查找表ID，ID为480
[undrop-for-innodb-develop]# ./c_parser -6Df ./pages-centos-home/FIL_PAGE_INDEX/0000000000000001.page -t ./dictionary/SYS_TABLES.sql | grep "gaoyu/items"
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/root/undrop-for-innodb-develop/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES\t' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`);
-- STATUS {"records_expected": 143, "records_dumped": 20, "records_lost": true} STATUS END
0000054DCD51 3C00000E6A1D6C SYS_TABLES "gaoyu/items" 480 3 33 0 80 "" 557
通过表ID（480）查找数据所在的数据页ID，ID为696
[undrop-for-innodb-develop]# ./c_parser -6Df ./pages-centos-home/FIL_PAGE_INDEX/0000000000000003.page -t ./dictionary/SYS_INDEXES.sql | grep SYS_INDEXES | AWK '$4==480 {print $0}}'
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/root/undrop-for-innodb-develop/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`);
-- STATUS {"records_expected": 504, "records_dumped": 196, "records_lost": true} STATUS END
0000054DCD51 3C00000E6A1C90 SYS_INDEXES 480 696 "PRIMARY" 1 3 557 4294967295
查看696数据页
[undrop-for-innodb-develop]# ls -rtl ./pages-centos-home/FIL_PAGE_INDEX/*696.page
-rw-r--r-- 1 root root 16384 Dec 29 00:27 ./pages-centos-home/FIL_PAGE_INDEX/0000000000000696.page
生成test库
[root]# vi /etc/mysql/my.cnf
[client]
user = root
password = P@ssw0rd
socket = /tmp/mysql.sock
[undrop-for-innodb-develop]# sh recover_dictionary.sh
(root@localhost) [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sys_columns |
| sys_fields |
| sys_indexes |
| sys_tables |
+----------------+
生成建表语句（可选）
[undrop‐for‐innodb]# ln ‐s /usr/local/mysql/lib/libmysqlclient.so.20 /usr/lib64/libmysqlclient.so.20
[undrop-for-innodb-develop]# ./sys_parser -h localhost -uroot -pGaoyu@029 -d test gaoyu/items >> /tmp/items.sql
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 ... 165 recs OK
SYS_COLUMNS ... 920 recs OK
SYS_INDEXES ... 577 recs OK
SYS_FIELDS ... 397 recs OK
All OK
[undrop-for-innodb-develop]# cat /tmp/items.sql
CREATE TABLE `items`(
`id` INT NOT NULL,
`name` CHAR(100) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci',
`age` INT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
解析数据文件
[undrop-for-innodb-develop]# ./c_parser -6f pages-centos-home/FIL_PAGE_INDEX/0000000000000696.page -t /tmp/items.sql | more
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (3 3)
0000054DCD49 B600001B810110 items 1 "gaoyu " 32
0000054DCD4B B800001B890110 items 2 "yuanyuan " 30
0000054DCD50 BB00001B8A0110 items 3 "qiqi " 2
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/root/undrop-for-innodb-develop/dumps/default/items' REPLACE INTO TABLE `items` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'items\t' (`id`, `name`, `age`);
-- STATUS {"records_expected": 3, "records_dumped": 3, "records_lost": false} STATUS END
-- Page id: 3, Found records: 3, Lost records: NO, Leaf page: YES
解析数据文件并导出（items.sql是LOAD导入语句，items是数据）
[undrop-for-innodb-develop]# mkdir -p dumps/default/
[undrop-for-innodb-develop]# ./c_parser -6f pages-centos-home/FIL_PAGE_INDEX/0000000000000696.page -t /tmp/items.sql > dumps/default/items 2> dumps/default/items.sql
[undrop-for-innodb-develop]# cat items.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/root/undrop-for-innodb-develop/dumps/default/items' REPLACE INTO TABLE `items` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'items\t' (`id`, `name`, `age`);
-- STATUS {"records_expected": 3, "records_dumped": 3, "records_lost": false} STATUS END
[root@host-211 undrop-for-innodb-develop]# cat items
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (3 3)
0000054DCD49 B600001B810110 items 1 "gaoyu " 32
0000054DCD4B B800001B890110 items 2 "yuanyuan " 30
0000054DCD50 BB00001B8A0110 items 3 "qiqi " 2
-- Page id: 3, Found records: 3, Lost records: NO, Leaf page: YES
重新创建表并导入表数据
(root@localhost) [gaoyu]> source /tmp/items.sql
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [gaoyu]> source /root/undrop-for-innodb-develop/dumps/default/items.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 3 rows affected (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
(root@localhost) [gaoyu]> select * from items;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | gaoyu | 32 |
| 2 | yuanyuan | 30 |
| 3 | qiqi | 2 |
+----+----------+------+场景2 表被drop后create
环境介绍
MySQL 5.7.21
innodb_file_per_table = ON
engine = innodb
提示：如果innodb_file_per_table = OFF，那么只需要解析ibdata1。
模拟故障
(root@localhost) [gaoyu]> create table items(id int not null,name char(100),age int check( age > 18 and age < 55),primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
(root@localhost) [gaoyu]> select * from items;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | gaoyu | 32 |
| 2 | yuanyuan | 30 |
| 3 | qiqi | 2 |
+----+----------+------+
(root@localhost) [gaoyu]> drop table items;
(root@localhost) [gaoyu]> create table items(id int not null,name char(100),age int check( age > 18 and age < 55),primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)故障恢复
解析ibdata1
[undrop-for-innodb-develop]# ./stream_parser -f /home/mysql_data/5.7_data/ibdata1
./stream_parser -f /home/mysql_data/5.7_data/ibdata1
解析datadir所在磁盘
[undrop-for-innodb-develop]# df -k
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/mapper/centos-root 52403200 49082152 3321048 94% /
devtmpfs 3982932 0 3982932 0% /dev
tmpfs 4000008 12 3999996 1% /dev/shm
tmpfs 4000008 53596 3946412 2% /run
tmpfs 4000008 0 4000008 0% /sys/fs/cgroup
/dev/sdb 20961280 3300288 17660992 16% /data
/dev/sda1 1038336 182896 855440 18% /boot
/dev/mapper/centos-home 43093444 8236104 34857340 20% /home
tmpfs 800004 0 800004 0% /run/user/0
tmpfs 800004 36 799968 1% /run/user/1000
/dev/sr0 4480476 4480476 0 100% /run/media/haha/CentOS 7 x86_64
[undrop-for-innodb-develop]# ./stream_parser -f /dev/mapper/centos-home -t 43093444k
Opening file: /dev/mapper/centos-home
File information:
ID of device containing file: 5
inode number: 38652
protection: 60660 (block device)
number of hard links: 1
user ID of owner: 0
group ID of owner: 6
device ID (if special file): 64770
blocksize for filesystem I/O: 4096
number of blocks allocated: 0
time of last access: 1640854344 Thu Dec 30 16:52:24 2021
time of last modification: 1635846679 Tue Nov 2 17:51:19 2021
time of last status change: 1635846679 Tue Nov 2 17:51:19 2021
total size, in bytes: 0 (0.000 exp(+0))
Size to process: 44127686656 (41.097 GiB)
Worker(0): 1.02% done. 2021-12-31 10:19:37 ETA(in 00:04:55). Processing speed: 141.070 MiB/sec
Worker(0): 2.03% done. 2021-12-31 10:17:58 ETA(in 00:03:14). Processing speed: 211.586 MiB/sec
Worker(0): 3.04% done. 2021-12-31 10:19:35 ETA(in 00:04:48). Processing speed: 141.230 MiB/sec
Worker(0): 4.04% done. 2021-12-31 10:17:59 ETA(in 00:03:10). Processing speed: 211.992 MiB/sec
Worker(0): 5.05% done. 2021-12-31 10:21:10 ETA(in 00:06:17). Processing speed: 105.945 MiB/sec
Worker(0): 6.06% done. 2021-12-31 10:18:01 ETA(in 00:03:06). Processing speed: 211.656 MiB/sec
Worker(0): 7.06% done. 2021-12-31 10:21:08 ETA(in 00:06:09). Processing speed: 105.872 MiB/sec
Worker(0): 8.07% done. 2021-12-31 10:19:36 ETA(in 00:04:34). Processing speed: 141.064 MiB/sec
Worker(0): 9.07% done. 2021-12-31 10:21:07 ETA(in 00:06:01). Processing speed: 105.793 MiB/sec
Worker(0): 10.08% done. 2021-12-31 10:19:37 ETA(in 00:04:28). Processing speed: 141.181 MiB/sec
Worker(0): 11.09% done. 2021-12-31 10:18:07 ETA(in 00:02:56). Processing speed: 211.773 MiB/sec
Worker(0): 12.09% done. 2021-12-31 10:19:36 ETA(in 00:04:22). Processing speed: 141.105 MiB/sec
Worker(0): 13.10% done. 2021-12-31 10:19:35 ETA(in 00:04:18). Processing speed: 141.333 MiB/sec
Worker(0): 14.11% done. 2021-12-31 10:21:02 ETA(in 00:05:41). Processing speed: 106.000 MiB/sec
Worker(0): 15.12% done. 2021-12-31 10:19:36 ETA(in 00:04:12). Processing speed: 141.328 MiB/sec
Worker(0): 16.12% done. 2021-12-31 10:21:01 ETA(in 00:05:33). Processing speed: 106.000 MiB/sec
Worker(0): 17.13% done. 2021-12-31 10:21:01 ETA(in 00:05:29). Processing speed: 105.909 MiB/sec
Worker(0): 18.14% done. 2021-12-31 10:23:46 ETA(in 00:08:08). Processing speed: 70.576 MiB/sec
Worker(0): 19.14% done. 2021-12-31 10:21:03 ETA(in 00:05:21). Processing speed: 105.965 MiB/sec
Worker(0): 20.15% done. 2021-12-31 10:22:23 ETA(in 00:06:36). Processing speed: 84.684 MiB/sec
Worker(0): 21.16% done. 2021-12-31 10:22:23 ETA(in 00:06:31). Processing speed: 84.796 MiB/sec
Worker(0): 22.16% done. 2021-12-31 10:23:42 ETA(in 00:07:44). Processing speed: 70.570 MiB/sec
Worker(0): 23.17% done. 2021-12-31 10:22:24 ETA(in 00:06:21). Processing speed: 84.721 MiB/sec
Worker(0): 24.18% done. 2021-12-31 10:22:24 ETA(in 00:06:16). Processing speed: 84.700 MiB/sec
Worker(0): 25.18% done. 2021-12-31 10:23:39 ETA(in 00:07:25). Processing speed: 70.660 MiB/sec
Worker(0): 26.19% done. 2021-12-31 10:22:25 ETA(in 00:06:06). Processing speed: 84.767 MiB/sec
Worker(0): 27.20% done. 2021-12-31 10:23:38 ETA(in 00:07:13). Processing speed: 70.638 MiB/sec
Worker(0): 28.20% done. 2021-12-31 10:23:39 ETA(in 00:07:08). Processing speed: 70.555 MiB/sec
Worker(0): 29.21% done. 2021-12-31 10:23:38 ETA(in 00:07:01). Processing speed: 70.607 MiB/sec
Worker(0): 30.22% done. 2021-12-31 10:21:18 ETA(in 00:04:37). Processing speed: 105.939 MiB/sec
Worker(0): 31.22% done. 2021-12-31 10:22:27 ETA(in 00:05:41). Processing speed: 84.722 MiB/sec
Worker(0): 32.23% done. 2021-12-31 10:24:44 ETA(in 00:07:51). Processing speed: 60.513 MiB/sec
Worker(0): 33.24% done. 2021-12-31 10:23:37 ETA(in 00:06:38). Processing speed: 70.557 MiB/sec
Worker(0): 34.24% done. 2021-12-31 10:21:24 ETA(in 00:04:21). Processing speed: 105.910 MiB/sec
Worker(0): 35.25% done. 2021-12-31 10:21:24 ETA(in 00:04:17). Processing speed: 106.000 MiB/sec
Worker(0): 36.26% done. 2021-12-31 10:21:24 ETA(in 00:04:13). Processing speed: 105.902 MiB/sec
Worker(0): 37.26% done. 2021-12-31 10:22:27 ETA(in 00:05:11). Processing speed: 84.694 MiB/sec
Worker(0): 38.27% done. 2021-12-31 10:22:27 ETA(in 00:05:06). Processing speed: 84.753 MiB/sec
Worker(0): 39.28% done. 2021-12-31 10:21:26 ETA(in 00:04:01). Processing speed: 105.793 MiB/sec
Worker(0): 40.28% done. 2021-12-31 10:20:25 ETA(in 00:02:57). Processing speed: 141.323 MiB/sec
Worker(0): 41.29% done. 2021-12-31 10:20:25 ETA(in 00:02:54). Processing speed: 141.242 MiB/sec
Worker(0): 42.30% done. 2021-12-31 10:22:22 ETA(in 00:04:46). Processing speed: 84.716 MiB/sec
Worker(0): 43.30% done. 2021-12-31 10:22:22 ETA(in 00:04:41). Processing speed: 84.741 MiB/sec
Worker(0): 44.31% done. 2021-12-31 10:20:30 ETA(in 00:02:46). Processing speed: 141.083 MiB/sec
Worker(0): 45.32% done. 2021-12-31 10:20:30 ETA(in 00:02:43). Processing speed: 141.057 MiB/sec
Worker(0): 46.32% done. 2021-12-31 10:20:30 ETA(in 00:02:40). Processing speed: 141.073 MiB/sec
Worker(0): 47.33% done. 2021-12-31 10:21:23 ETA(in 00:03:29). Processing speed: 105.793 MiB/sec
Worker(0): 48.33% done. 2021-12-31 10:22:15 ETA(in 00:04:16). Processing speed: 84.634 MiB/sec
Worker(0): 49.34% done. 2021-12-31 10:23:07 ETA(in 00:05:02). Processing speed: 70.529 MiB/sec
Worker(0): 50.34% done. 2021-12-31 10:21:26 ETA(in 00:03:17). Processing speed: 105.793 MiB/sec
Worker(0): 51.35% done. 2021-12-31 10:20:37 ETA(in 00:02:25). Processing speed: 141.057 MiB/sec
Worker(0): 52.35% done. 2021-12-31 10:19:48 ETA(in 00:01:34). Processing speed: 211.586 MiB/sec
Worker(0): 53.36% done. 2021-12-31 10:20:36 ETA(in 00:02:19). Processing speed: 141.057 MiB/sec
Worker(0): 54.37% done. 2021-12-31 10:19:49 ETA(in 00:01:30). Processing speed: 211.586 MiB/sec
Worker(0): 55.37% done. 2021-12-31 10:20:35 ETA(in 00:02:13). Processing speed: 141.057 MiB/sec
Worker(0): 56.38% done. 2021-12-31 10:19:50 ETA(in 00:01:26). Processing speed: 211.586 MiB/sec
Worker(0): 57.38% done. 2021-12-31 10:20:34 ETA(in 00:02:07). Processing speed: 141.057 MiB/sec
Worker(0): 58.39% done. 2021-12-31 10:19:51 ETA(in 00:01:22). Processing speed: 211.586 MiB/sec
Worker(0): 59.39% done. 2021-12-31 10:19:51 ETA(in 00:01:20). Processing speed: 211.586 MiB/sec
Worker(0): 60.40% done. 2021-12-31 10:19:51 ETA(in 00:01:18). Processing speed: 211.586 MiB/sec
Worker(0): 61.40% done. 2021-12-31 10:19:51 ETA(in 00:01:16). Processing speed: 211.586 MiB/sec
Worker(0): 62.41% done. 2021-12-31 10:19:51 ETA(in 00:01:14). Processing speed: 211.586 MiB/sec
Worker(0): 63.42% done. 2021-12-31 10:20:29 ETA(in 00:01:49). Processing speed: 141.057 MiB/sec
Worker(0): 64.42% done. 2021-12-31 10:19:52 ETA(in 00:01:10). Processing speed: 211.586 MiB/sec
Worker(0): 65.43% done. 2021-12-31 10:19:52 ETA(in 00:01:08). Processing speed: 211.586 MiB/sec
Worker(0): 66.43% done. 2021-12-31 10:19:52 ETA(in 00:01:06). Processing speed: 211.586 MiB/sec
Worker(0): 67.44% done. 2021-12-31 10:19:52 ETA(in 00:01:04). Processing speed: 211.586 MiB/sec
Worker(0): 68.44% done. 2021-12-31 10:19:52 ETA(in 00:01:02). Processing speed: 211.603 MiB/sec
Worker(0): 69.45% done. 2021-12-31 10:20:24 ETA(in 00:01:31). Processing speed: 141.060 MiB/sec
Worker(0): 70.46% done. 2021-12-31 10:19:53 ETA(in 00:00:58). Processing speed: 211.793 MiB/sec
Worker(0): 71.46% done. 2021-12-31 10:19:53 ETA(in 00:00:56). Processing speed: 212.000 MiB/sec
Worker(0): 72.47% done. 2021-12-31 10:20:21 ETA(in 00:01:21). Processing speed: 141.328 MiB/sec
Worker(0): 73.48% done. 2021-12-31 10:20:21 ETA(in 00:01:18). Processing speed: 141.333 MiB/sec
Worker(0): 74.48% done. 2021-12-31 10:20:48 ETA(in 00:01:41). Processing speed: 105.891 MiB/sec
Worker(0): 75.49% done. 2021-12-31 10:21:13 ETA(in 00:02:01). Processing speed: 84.666 MiB/sec
Worker(0): 76.50% done. 2021-12-31 10:20:49 ETA(in 00:01:33). Processing speed: 105.805 MiB/sec
Worker(0): 77.50% done. 2021-12-31 10:21:12 ETA(in 00:01:51). Processing speed: 84.634 MiB/sec
Worker(0): 78.51% done. 2021-12-31 10:20:05 ETA(in 00:00:42). Processing speed: 211.617 MiB/sec
Worker(0): 79.51% done. 2021-12-31 10:20:27 ETA(in 00:01:01). Processing speed: 141.078 MiB/sec
Worker(0): 80.52% done. 2021-12-31 10:20:27 ETA(in 00:00:58). Processing speed: 141.057 MiB/sec
Worker(0): 81.52% done. 2021-12-31 10:20:07 ETA(in 00:00:36). Processing speed: 211.586 MiB/sec
Worker(0): 82.53% done. 2021-12-31 10:20:07 ETA(in 00:00:34). Processing speed: 211.586 MiB/sec
Worker(0): 83.54% done. 2021-12-31 10:20:25 ETA(in 00:00:49). Processing speed: 141.057 MiB/sec
Worker(0): 84.54% done. 2021-12-31 10:20:08 ETA(in 00:00:30). Processing speed: 211.586 MiB/sec
Worker(0): 85.55% done. 2021-12-31 10:20:39 ETA(in 00:00:57). Processing speed: 105.825 MiB/sec
Worker(0): 86.55% done. 2021-12-31 10:20:10 ETA(in 00:00:26). Processing speed: 211.653 MiB/sec
Worker(0): 87.56% done. 2021-12-31 10:20:10 ETA(in 00:00:24). Processing speed: 211.820 MiB/sec
Worker(0): 88.57% done. 2021-12-31 10:20:10 ETA(in 00:00:22). Processing speed: 211.813 MiB/sec
Worker(0): 89.57% done. 2021-12-31 10:20:10 ETA(in 00:00:20). Processing speed: 211.635 MiB/sec
Worker(0): 90.58% done. 2021-12-31 10:20:10 ETA(in 00:00:18). Processing speed: 211.586 MiB/sec
Worker(0): 91.58% done. 2021-12-31 10:20:10 ETA(in 00:00:16). Processing speed: 211.586 MiB/sec
Worker(0): 92.59% done. 2021-12-31 10:20:10 ETA(in 00:00:14). Processing speed: 211.766 MiB/sec
Worker(0): 93.59% done. 2021-12-31 10:20:10 ETA(in 00:00:12). Processing speed: 211.625 MiB/sec
Worker(0): 94.60% done. 2021-12-31 10:21:27 ETA(in 00:01:15). Processing speed: 30.227 MiB/sec
Worker(0): 95.61% done. 2021-12-31 10:20:33 ETA(in 00:00:17). Processing speed: 105.867 MiB/sec
Worker(0): 96.61% done. 2021-12-31 10:20:20 ETA(in 00:00:03). Processing speed: 424.000 MiB/sec
Worker(0): 98.15% done. 2021-12-31 10:20:19 ETA(in 00:00:01). Processing speed: 648.000 MiB/sec
All workers finished in 339 sec
通过表名（gaoyu/items）查找表ID，ID为485
[undrop-for-innodb-develop]# ./c_parser -6Df ./pages-centos-home/FIL_PAGE_INDEX/0000000000000001.page -t ./dictionary/SYS_TABLES.sql | grep "gaoyu/items"
0000054DCDFB 2C00000E462F03 SYS_TABLES "gaoyu/items" 485 3 33 0 80 "" 562
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/root/undrop-for-innodb-develop/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_TABLES\t' (`NAME`, `ID`, `N_COLS`, `TYPE`, `MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`);
-- STATUS {"records_expected": 8937, "records_dumped": 7614, "records_lost": true} STATUS END
通过表ID（480）查找数据所在的数据页ID，ID为701
[undrop-for-innodb-develop]# ./c_parser -6Df ./pages-centos-home/FIL_PAGE_INDEX/0000000000000003.page -t ./dictionary/SYS_INDEXES.sql | grep SYS_INDEXES | AWK '$4==485 {print $0}}'
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/root/undrop-for-innodb-develop/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`);
-- STATUS {"records_expected": 458, "records_dumped": 119, "records_lost": true} STATUS END
0000054DCDFB 2C00000E462E27 SYS_INDEXES 485 701 "PRIMARY" 1 3 562 4294967295
查看701数据页
[undrop-for-innodb-develop]# ls -rtl ./pages-centos-home/FIL_PAGE_INDEX/*701.page
-rw-r--r-- 1 root root 16384 Dec 31 10:14 ./pages-centos-home/FIL_PAGE_INDEX/0000000000000701.page
生成test库
[root]# vi /etc/mysql/my.cnf
[client]
user = root
password = P@ssw0rd
socket = /tmp/mysql.sock
[undrop-for-innodb-develop]# sh 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 ... 165 recs OK
SYS_COLUMNS ... 920 recs OK
SYS_INDEXES ... 577 recs OK
SYS_FIELDS ... 397 recs OK
All OK
(root@localhost) [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| sys_columns |
| sys_fields |
| sys_indexes |
| sys_tables |
+----------------+
生成建表语句（可选）
[undrop‐for‐innodb]# ln ‐s /usr/local/mysql/lib/libmysqlclient.so.20 /usr/lib64/libmysqlclient.so.20
[undrop-for-innodb-develop]# ./sys_parser -h localhost -uroot -pGaoyu@029 -d test gaoyu/items >> /tmp/items.sql
[undrop-for-innodb-develop]# cat /tmp/items.sql
CREATE TABLE `items`(
`id` INT NOT NULL,
`name` CHAR(100) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci',
`age` INT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
解析数据文件
[undrop-for-innodb-develop]# ./c_parser -6f pages-centos-home/FIL_PAGE_INDEX/0000000000000701.page -t /tmp/items.sql | tail -10
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/root/undrop-for-innodb-develop/dumps/default/items' REPLACE INTO TABLE `items` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'items\t' (`id`, `name`, `age`);
-- STATUS {"records_expected": 3, "records_dumped": 3, "records_lost": false} STATUS END
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (3 3)
0000054DCDA0 EF00001B9E0110 items 1 "gaoyu " 32
0000054DCDA0 EF00001B9E011D items 2 "yuanyuan " 30
0000054DCDA0 EF00001B9E012A items 3 "qiqi " 2
-- Page id: 3, Found records: 3, Lost records: NO, Leaf page: YES
解析数据文件并导出（items.sql是LOAD导入语句，items是数据）
[undrop-for-innodb-develop]# mkdir -p dumps/default/
[undrop-for-innodb-develop]# ./c_parser -6f pages-centos-home/FIL_PAGE_INDEX/0000000000000701.page -t /tmp/items.sql > dumps/default/items 2> dumps/default/items.sql
[undrop-for-innodb-develop]# cat items.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/root/undrop-for-innodb-develop/dumps/default/items' REPLACE INTO TABLE `items` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'items\t' (`id`, `name`, `age`);
-- STATUS {"records_expected": 3, "records_dumped": 3, "records_lost": false} STATUS END
[root@host-211 undrop-for-innodb-develop]# cat items
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (3 3)
0000054DCD49 B600001B810110 items 1 "gaoyu " 32
0000054DCD4B B800001B890110 items 2 "yuanyuan " 30
0000054DCD50 BB00001B8A0110 items 3 "qiqi " 2
-- Page id: 3, Found records: 3, Lost records: NO, Leaf page: YES
导入表数据
(root@localhost) [gaoyu]> source /root/undrop-for-innodb-develop/dumps/default/items.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 3 rows affected (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
(root@localhost) [gaoyu]> select * from items;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | gaoyu | 32 |
| 2 | yuanyuan | 30 |
| 3 | qiqi | 2 |
+----+----------+------+
PS：其余场景还有待验证。
