通过.ibd文件恢复表数据
已测试版本：5.5.18
OS ：RHEL 6.3
前提：数据库无法启动，无备份，ibd文件还在! ibd文件还在! ibd文件还在!
背景：升级失败，undrop-for-innodb 只能读取ibdata 中的表的信息。
方法一 （无工具）
1. 在其他服务器安装相同版本的MySQL,建立空库ybt
2. 在空库ybt中新建表 tdm_fws_module_recomment_server_ed
3. 在5.5 版本中，通过ibd文件来查看tablespace id
二进制的方式打开
vi tdm_fws_module_recomment_server_ed.ibd -b
在vi 下，输入 :%!xxd 转为16进制
0000000: 80b4 eafe 0000 0000 0000 0000 0000 0000 ................
0000010: 0000 0000 0037 b458 0008 0000 0000 0000 .....7.X........
0000020: 0000 0000 012a 0000 012a 0000 0000 0000 .....*...*......
0000030: 0006 0000 0040 0000 0000 0000 0004 0000 .....@..........
0000040: 0000 ffff ffff 0000 ffff ffff 0000 0000 ................
0000050: 0001 0000 0000 009e 0000 0000 009e 0000 ................
0000060: 0000 ffff ffff 0000 ffff ffff 0000 0000 ................
0000070: 0000 0000 0003 0000 0000 ffff ffff 0000 ................
0000080: ffff ffff 0000 0000 0001 0000 0002 0026 ...............&
其中，第三行的两个012a 为tablespace id
4. 备份已损坏库的ibd文件，并覆盖到新建库的ybt 对应目录下的ibd文件
5. 通过vi 打开拷贝后的ibd文件
在vi 下，输入 :%!xxd 转为16进制
将该ibd文件的两个 tablespace id 改为 012a
输入 :%!xxd -r 转换为二进制
输入 :wq! 保存
6. 修改innodb_force_recovery = 6，重启数据库
7. 导出数据
5.6 以后可以通过 以下sql 查询
mysql> select * from information_schema.INNODB_SYS_DATAFILES order by SPACE desc ;
+-------+---------------------------------------------------+
| SPACE | PATH |
+-------+---------------------------------------------------+
| 154 | ./test/sbtest2.ibd |
| 152 | ./test/#sql-ib161-1555929536.ibd |
| 146 | ./test/sbtest1.ibd |
| 141 | ./test/user.ibd |
重组表的时候，SPACE 会变
方法二 （有工具）
安装 mysql-utilities
下载地址 https://downloads.mysql.com/archives/c-python/
https://downloads.mysql.com/archives/utilities/
yum install mysql-connector-python-2.1.7-1.el6.x86_64.rpm
yum install mysql-utilities-1.6.5-1.el6.noarch.rpm
到 schema 目录下，解析全部的frm 文件修复数据
[root@dbmspressd202 wnm]# mysqlfrm --diagnostic ./ >wnm.sql
[root@dbmspressd202 wnm]# cat wnm.sql
# WARNING: Cannot generate character set or collation names without the --server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for ./t.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY `PRIMARY` (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;
# Reading .frm file for ./t2.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:
CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY `PRIMARY` (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;
#...done.
到新库下执行该sql 文件
到对应的目录下查看生成的对应的 *.frm 和 *.ibd文件，
mysql> create database wnm;
Query OK, 1 row affected (0.00 sec)
mysql> use wnm
Database changed
mysql> CREATE TABLE `t` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `c` int(11) DEFAULT NULL,
-> `d` int(11) DEFAULT NULL,
-> PRIMARY KEY `PRIMARY` (`id`),
-> UNIQUE KEY `c` (`c`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> system ls -l /mysql/data/wnm
total 132
-rw-r----- 1 mysql mysql 67 Jun 13 15:45 db.opt
-rw-r----- 1 mysql mysql 8604 Jun 13 15:45 t.frm
-rw-r----- 1 mysql mysql 114688 Jun 13 15:45 t.ibd
使用 DISCARD TABLESPACE 干掉对应表的.ibd文件
mysql> alter table t DISCARD TABLESPACE;
Query OK, 0 rows affected (0.05 sec)
mysql> system ls -l /mysql/data/wnm
total 16
-rw-r----- 1 mysql mysql 67 Jun 13 15:45 db.opt
-rw-r----- 1 mysql mysql 8604 Jun 13 15:45 t.frm
将之前的ibd文件copy过去，
然后执行 IMPORT TABLESPACE
mysql> alter table t IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> select * from t;
+----+------+------+
| id | c | d |
+----+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 4 | 4 |
+----+------+------+
4 rows in set (0.00 sec)
查询OK
ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)
可以使用alter table t row_format=compact
或者在建表语句中加上ROW_FORMAT=COMPACT
然后再执行alter table tablename IMPORT TABLESPACE
mysql> show variables like 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name | Value |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
1 row in set (0.00 sec)
默认指定为 dynamic，建表时不做修改的话，不会出现以上问题。
方法三 （使用第三方工具）
官网在早两年已经GG了，上不去了。辛亏源码还能下载，能支持到7的系统
The tool has been developed on Linux, it’s known to work on CentOS 4,5,6,7, Debian, Ubuntu and Amazon Linux. Only 64 bit systems are supported.
准备环境
install make gcc flex bison
编译
[root@dbmspressd202 undrop-for-innodb-master]# make
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -fgnu89-inline -I./include -c stream_parser.c
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -fgnu89-inline -I./include -pthread -lm stream_parser.o -o stream_parser
flex sql_parser.l
sql_parser.l:66: warning, the character range [-Y] is ambiguous in a case-insensitive scanner
sql_parser.l:66: warning, the character range [a] is ambiguous in a case-insensitive scanner
bison -o sql_parser.c sql_parser.y
sql_parser.y: conflicts: 6 shift/reduce
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -fgnu89-inline -I./include -c sql_parser.c
lex.yy.c:3430: warning: ‘yyunput’ defined but not used
lex.yy.c:3471: warning: ‘input’ defined but not used
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -fgnu89-inline -I./include -c c_parser.c
./include/ctype-latin1.c:359: warning: ‘my_mb_wc_latin1’ defined but not used
./include/ctype-latin1.c:372: warning: ‘my_wc_mb_latin1’ defined but not used
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -fgnu89-inline -I./include -c tables_dict.c
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -fgnu89-inline -I./include -c print_data.c
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -fgnu89-inline -I./include -c check_data.c
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -fgnu89-inline -I./include sql_parser.o c_parser.o tables_dict.o print_data.o check_data.o -o c_parser -pthread -lm
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -fgnu89-inline -I./include -o innochecksum_changer innochecksum.c
解析ibdata1 文件
[root@dbmspressd202 undrop-for-innodb-master]# ./stream_parser -f /mysql/data/ibdata1
Opening file: /mysql/data/ibdata1
File information:
ID of device containing file: 64770
inode number: 11
protection: 100640 (regular file)
number of hard links: 1
user ID of owner: 497
group ID of owner: 497
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 1049608
time of last access: 1655344672 Thu Jun 16 09:57:52 2022
time of last modification: 1645732135 Fri Feb 25 03:48:55 2022
time of last status change: 1655344372 Thu Jun 16 09:52:52 2022
total size, in bytes: 536870912 (512.000 MiB)
Size to process: 536870912 (512.000 MiB)
Worker(0): 21.87% done. 2022-06-16 15:02:42 ETA(in 00:00:03). Processing speed: 104.000 MiB/sec
Worker(0): 89.06% done. 2022-06-16 15:02:40 ETA(in 00:00:00). Processing speed: 344.000 MiB/sec
All workers finished in 2 sec
[root@dbmspressd202 undrop-for-innodb-master]# ls -l pages-ibdata1/*
pages-ibdata1/FIL_PAGE_INDEX:
total 256
-rw-r--r-- 1 root root 16384 Jun 16 15:02 0000000000000001.page
-rw-r--r-- 1 root root 16384 Jun 16 15:02 0000000000000002.page
-rw-r--r-- 1 root root 16384 Jun 16 15:02 0000000000000003.page
-rw-r--r-- 1 root root 16384 Jun 16 15:02 0000000000000004.page
-rw-r--r-- 1 root root 16384 Jun 16 15:02 0000000000000005.page
-rw-r--r-- 1 root root 16384 Jun 16 15:02 0000000000000011.page
-rw-r--r-- 1 root root 16384 Jun 16 15:02 0000000000000012.page
-rw-r--r-- 1 root root 16384 Jun 16 15:02 0000000000000013.page
-rw-r--r-- 1 root root 16384 Jun 16 15:02 0000000000000014.page
-rw-r--r-- 1 root root 16384 Jun 16 15:02 0000000000000015.page
-rw-r--r-- 1 root root 16384 Jun 16 15:02 0000000000000016.page
-rw-r--r-- 1 root root 16384 Jun 16 15:02 0000000000000017.page
-rw-r--r-- 1 root root 16384 Jun 16 15:02 0000000000000018.page
-rw-r--r-- 1 root root 16384 Jun 16 15:02 0000000000000019.page
-rw-r--r-- 1 root root 16384 Jun 16 15:02 0000000000000020.page
-rw-r--r-- 1 root root 16384 Jun 16 15:02 18446744069414584320.page
pages-ibdata1/FIL_PAGE_TYPE_BLOB:
total 0
读取前面4个页中数据字典表的数据：
mkdir -p dumps/wnm/
./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql > dumps/wnm/SYS_TABLES 2>dumps/default/SYS_TABLES.sql
./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -t dictionary/SYS_COLUMNS.sql > dumps/wnm/SYS_COLUMNS 2>dumps/default/SYS_COLUMNS.sql
./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql > dumps/wnm/SYS_INDEXES 2>dumps/default/SYS_INDEXES.sql
./c_parser -4f pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page -t dictionary/SYS_FIELDS.sql > dumps/wnm/SYS_FIELDS 2>dumps/default/SYS_FIELDS.sql
讲自动解析出对应的表结构和load 语句
[root@dbmspressd202 wnm]# cat SYS_TABLES
-- Page id: 8, Format: REDUNDANT, Records list: Valid, Expected records: (30 30)
000000000301 A1000001120194 SYS_TABLES "SYS\_DATAFILES" 14 -2147483646 1 0 64 "" 0
000000000300 800000010D0110 SYS_TABLES "SYS\_FOREIGN" 11 -2147483644 1 0 64 "" 0
000000000300 800000010D0201 SYS_TABLES "SYS\_FOREIGN\_COLS" 12 -2147483644 1 0 64 "" 0
000000000301 A1000001120110 SYS_TABLES "SYS\_TABLESPACES" 13 -2147483645 1 0 64 "" 0
000000000302 A2000001150110 SYS_TABLES "SYS\_VIRTUAL" 15 -2147483645 1 0 64 "" 0
000000000303 A3000001170110 SYS_TABLES "SYS\_ZIP\_DICT" 16 -2147483645 1 0 64 "" 0
000000000303 A30000011701BF SYS_TABLES "SYS\_ZIP\_DICT\_COLS" 17 -2147483645 1 0 64 "" 0
000000000319 B9000001300110 SYS_TABLES "mysql/engine\_cost" 36 6 33 0 80 "" 20
000000000314 B40000012B0110 SYS_TABLES "mysql/gtid\_executed" 34 3 33 0 80 "" 18
00000000031D 3C000001340110 SYS_TABLES "mysql/help\_category" 38 4 33 0 80 "" 5
00000000031F 3D000001360110 SYS_TABLES "mysql/help\_keyword" 39 2 33 0 80 "" 7
000000000321 3E000001380110 SYS_TABLES "mysql/help\_relation" 40 2 33 0 80 "" 6
00000000031B 3B000001320110 SYS_TABLES "mysql/help\_topic" 37 6 33 0 80 "" 4
000000000310 B0000001270110 SYS_TABLES "mysql/innodb\_index\_stats" 30 8 33 0 80 "" 14
00000000030F AF000001260110 SYS_TABLES "mysql/innodb\_table\_stats" 29 6 33 0 80 "" 13
000000000304 A40000011B0110 SYS_TABLES "mysql/plugin" 18 2 33 0 80 "" 2
000000000317 B70000012E0110 SYS_TABLES "mysql/server\_cost" 35 4 33 0 80 "" 19
000000000305 A50000011C0110 SYS_TABLES "mysql/servers" 19 9 33 0 80 "" 3
000000000312 B2000001290110 SYS_TABLES "mysql/slave\_master\_info" 32 25 33 0 80 "" 16
000000000311 B1000001280110 SYS_TABLES "mysql/slave\_relay\_log\_info" 31 9 33 0 80 "" 15
000000000313 B30000012A0110 SYS_TABLES "mysql/slave\_worker\_info" 33 13 33 0 80 "" 17
00000000030B AB000001220110 SYS_TABLES "mysql/time\_zone" 25 2 33 0 80 "" 9
00000000030E AE000001250110 SYS_TABLES "mysql/time\_zone\_leap\_second" 28 2 33 0 80 "" 12
00000000030A AA000001210110 SYS_TABLES "mysql/time\_zone\_name" 24 2 33 0 80 "" 8
00000000030C AC000001230110 SYS_TABLES "mysql/time\_zone\_transition" 26 3 33 0 80 "" 10
00000000030D AD000001240110 SYS_TABLES "mysql/time\_zone\_transition\_type" 27 5 33 0 80 "" 11
000000000324 C00000013A0110 SYS_TABLES "sys/sys\_config" 41 4 33 0 80 "" 21
00000000052B A1000001120110 SYS_TABLES "wnm/external\_kaisen\_data" 44 43 33 0 80 "" 25
00000000050D A9000001200110 SYS_TABLES "wnm/t" 42 3 33 0 80 "" 23
000000000519 B30000012A0110 SYS_TABLES "wnm/t2" 43 3 33 0 80 "" 24
-- Page id: 8, Found records: 30, Lost records: NO, Leaf page: YES
[root@dbmspressd202 wnm]# cat SYS_TABLES.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/mysql/bak/zzg/undrop-for-innodb-master/dumps/wnm/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": 30, "records_dumped": 30, "records_lost": false} STATUS END
将以上数据导入库 heheda（恢复用中间库） 中
建表语句在dictionary 下
[root@dbmspressd202 undrop-for-innodb-master]# ll dictionary
total 16
-rw-r--r-- 1 root root 1845 Oct 9 2018 SYS_COLUMNS.sql
-rw-r--r-- 1 root root 1685 Oct 9 2018 SYS_FIELDS.sql
-rw-r--r-- 1 root root 1876 Oct 9 2018 SYS_INDEXES.sql
-rw-r--r-- 1 root root 1895 Oct 9 2018 SYS_TABLES.sql
导入
mysql> source /mysql/bak/zzg/undrop-for-innodb-master/dumps/wnm/SYS_COLUMNS.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 188 rows affected (0.07 sec)
Records: 188 Deleted: 0 Skipped: 0 Warnings: 0
mysql> source /mysql/bak/zzg/undrop-for-innodb-master/dumps/wnm/SYS_FIELDS.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 55 rows affected (0.03 sec)
Records: 55 Deleted: 0 Skipped: 0 Warnings: 0
mysql> source /mysql/bak/zzg/undrop-for-innodb-master/dumps/wnm/SYS_INDEXES.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 40 rows affected (0.08 sec)
Records: 40 Deleted: 0 Skipped: 0 Warnings: 0
mysql> source /mysql/bak/zzg/undrop-for-innodb-master/dumps/wnm/SYS_TABLES.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 30 rows affected (0.15 sec)
Records: 30 Deleted: 0 Skipped: 0 Warnings: 0
编译sys_parser ，用于从数据字典表（heheda）中生成任意表的建表语句
[root@dbmspressd202 undrop-for-innodb-master]# make sys_parser
/usr/bin/mysql_config
cc -o sys_parser sys_parser.c `mysql_config --cflags` `mysql_config --libs
根据之前生成的数据字典信息，使用下面的命令查询出wnm/t的表ID：
[root@dbmspressd202 undrop-for-innodb-master]# ./sys_parser -u sndsadmin -p SuningRds@123 -h10.243.90.105 -d heheda wnm/t >t.sql
CREATE TABLE `t`(
`id` INT NOT NULL,
`c` INT,
`d` INT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
检查heheda库中的记录
mysql> select * from SYS_TABLES where NAME='wnm/t';
+-------+----+--------+------+--------+---------+--------------+-------+
| NAME | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+-------+----+--------+------+--------+---------+--------------+-------+
| wnm/t | 42 | 3 | 33 | 0 | 80 | | 23 |
+-------+----+--------+------+--------+---------+--------------+-------+
1 row in set (0.00 sec)
mysql> select * from SYS_INDEXES where TABLE_ID=42;
+----------+----+---------+----------+------+-------+---------+
| TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+----+---------+----------+------+-------+---------+
| 42 | 44 | PRIMARY | 1 | 3 | 23 | 3 |
| 42 | 45 | c | 1 | 2 | 23 | 4 |
+----------+----+---------+----------+------+-------+---------+
2 rows in set (0.00 sec)
这。。。好像有个BUG。。。解析出来的表结构，缺失了一个索引c，还缺失了一部分属性
对比下
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY `PRIMARY` (`id`),
UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;
开始解析数据文件
[root@dbmspressd202 undrop-for-innodb-master]# ./stream_parser -f /mysql/data/wnm/t.ibd
Opening file: /mysql/data/wnm/t.ibd
File information:
ID of device containing file: 64770
inode number: 25460740
protection: 100640 (regular file)
number of hard links: 1
user ID of owner: 497
group ID of owner: 497
device ID (if special file): 0
blocksize for filesystem I/O: 4096
number of blocks allocated: 232
time of last access: 1655342840 Thu Jun 16 09:27:20 2022
time of last modification: 1645598316 Wed Feb 23 14:38:36 2022
time of last status change: 1655344372 Thu Jun 16 09:52:52 2022
total size, in bytes: 114688 (112.000 kiB)
Size to process: 114688 (112.000 kiB)
All workers finished in 0 sec
查看解析后的文件
[root@dbmspressd202 undrop-for-innodb-master]# ll pages-t.ibd/FIL_PAGE_INDEX/
total 32
-rw-r--r-- 1 root root 16384 Jun 16 15:39 0000000000000044.page
-rw-r--r-- 1 root root 16384 Jun 16 15:39 0000000000000045.page
这边的44 和 45 分别对应上面PRIMARY 和 c 的id
解析
./c_parser -6f pages-t.ibd/FIL_PAGE_INDEX/0000000000000044.page -t t.sql > dumps/wnm/t 2> dumps/wnm/t.sql
检查解析的结果
[root@dbmspressd202 undrop-for-innodb-master]# ll dumps/wnm/
total 52
-rw-r--r-- 1 root root 287 Jun 16 15:42 t
-rw-r--r-- 1 root root 333 Jun 16 15:42 t.sql
[root@dbmspressd202 wnm]# cat t
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (4 4)
000000000511 AD000001240110 t 1 1 1
000000000512 AE000001250110 t 2 2 2
000000000517 B1000001280110 t 3 3 3
000000000518 B2000001290110 t 4 4 4
-- Page id: 3, Found records: 4, Lost records: NO, Leaf page: YES
[root@dbmspressd202 wnm]# cat t.sql
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/mysql/bak/zzg/undrop-for-innodb-master/dumps/default/t' REPLACE INTO TABLE `t` CHARACTER SET UTF8 FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 't\t' (`id`, `c`, `d`);
-- STATUS {"records_expected": 4, "records_dumped": 4, "records_lost": false} STATUS END
根据之前解析的表结构，在执行此处的 t.sql,将数据重新load 回表中
mysql> CREATE TABLE `t`(
-> `id` INT NOT NULL,
-> `c` INT,
-> `d` INT,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.21 sec)
mysql>
mysql> source /mysql/bak/zzg/undrop-for-innodb-master/dumps/default/t.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 4 rows affected (0.03 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
mysql> select * from t;
+----+------+------+
| id | c | d |
+----+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 4 | 4 |
+----+------+------+
4 rows in set (0.00 sec)
至此数据恢复完成
