环境说明:复制MySQL:5.7.44 OS:Redhat 7.51.2.

说明:undrop-for-innodb 是据被针对 MySQL innodb 的数据恢复工具，本文主要介绍在无备份、误删p无无binlog等场景下，备份如何恢复drop table的何恢表数据。

步骤:

复制1.模拟误删除(drop table); 2.安装、据被编译undrop-for-innodb工具； 3.扫描mysql数据文件所在磁盘； 4.扫描ibdata文件； 5.生成字典表； 6.恢复表结构； 7.恢复表数据；1.2.3.4.5.6.7.

1.模拟误删除(drop table);

创建测试数据:

复制mysql> create table cjc.txxxxx(id int,误删p无time datetime); mysql> insert into cjc.txxxxx values(1,now()); mysql> insert into cjc.txxxxx values(2,now()); mysql> insert into cjc.txxxxx values(3,now()); mysql> select * from cjc.txxxxx; +------+---------------------+ | id | time | +------+---------------------+ | 1 | 2024-08-04 09:54:21 | | 2 | 2024-08-04 09:54:34 | | 3 | 2024-08-04 09:55:01 | +------+---------------------+ 3 rows in set (0.00 sec) mysql> checksum table cjc.txxxxx; +------------+-----------+ | Table | Checksum | +------------+-----------+ | cjc.txxxxx | 403036823 | +------------+-----------+ 1 row in set (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME=cjc/txxxxx; +----------+------------+------+--------+-------+-------------+------------+---------------+------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | +----------+------------+------+--------+-------+-------------+------------+---------------+------------+ | 46 | cjc/txxxxx | 33 | 5 | 45 | Barracuda | Dynamic | 0 | Single | +----------+------------+------+--------+-------+-------------+------------+---------------+------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE TABLE_ID=46; +----------+------+-----+-------+--------+-----+ | TABLE_ID | NAME | POS | MTYPE | PRTYPE | LEN | +----------+------+-----+-------+--------+-----+ | 46 | id | 0 | 6 | 1027 | 4 | | 46 | time | 1 | 3 | 525324 | 5 | +----------+------+-----+-------+--------+-----+ 2 rows in set (0.00 sec) [mysql@cjc-db-01 data]$ ls -lrth cjc/txxxxx.* -rw-r----- 1 mysql mysql 8.4K Aug 4 09:54 cjc/txxxxx.frm -rw-r----- 1 mysql mysql 96K Aug 4 09:55 cjc/txxxxx.ibd [mysql@cjc-db-01 data]$ strings ibdata1 |grep -i txxxxx|more cjc/txxxxx .cjc/txxxxx Lcjc/txxxxx X./cjc/txxxxx.ibd YcjctxxxxxGEN_CLUST_INDEXn_diff_pfx01 icjctxxxxxGEN_CLUST_INDEXn_leaf_pages icjctxxxxxGEN_CLUST_INDEXsize X./cjc/txxxxx.ibd1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.

删除表，模拟误删除

复制mysql> drop table cjc.txxxxx; Query OK,备份 0 rows affected (0.35 sec) mysql> select * from cjc.txxxxx; ERROR 1146 (42S02): Table cjc.txxxxx doesnt exist1.2.3.4.5.6.

2.安装、编译undrop-for-innodb工具

下载恢复工具 复制[root@cjc-db-01 back]# git clone https://github.com/twindb/undrop-for-innodb.git Cloning into undrop-for-innodb... remote: Enumerating objects: 398,何恢 done. remote: Counting objects: 100% (34/34), done. remote: Compressing objects: 100% (30/30), done. remote: Total 398 (delta 7), reused 18 (delta 2), pack-reused 364 Receiving objects: 100% (398/398), 1.16 MiB | 1.55 MiB/s, done. Resolving deltas: 100% (127/127), done. [root@cjc-db-01 tools]# du -sh undrop-for-innodb/ 4.3M undrop-for-innodb/ [root@cjc-db-01 tools]# ls -lrth undrop-for-innodb/ total 220K -rw-r--r-- 1 root root 2.5K Aug 3 22:47 Makefile -rw-r--r-- 1 root root 18K Aug 3 22:47 LICENSE -rw-r--r-- 1 root root 2.0K Aug 3 22:47 README.md -rw-r--r-- 1 root root 29K Aug 3 22:47 c_parser.c -rw-r--r-- 1 root root 6.2K Aug 3 22:47 check_data.c -rw-r--r-- 1 root root 2.0K Aug 3 22:47 fetch_data.sh drwxr-xr-x 2 root root 96 Aug 3 22:47 dictionary -rw-r--r-- 1 root root 8.8K Aug 3 22:47 innochecksum.c drwxr-xr-x 2 root root 4.0K Aug 3 22:47 include -rw-r--r-- 1 root root 17K Aug 3 22:47 print_data.c drwxr-xr-x 4 root root 4.0K Aug 3 22:47 omnibus-undrop-for-innodb -rwxr-xr-x 1 root root 1.5K Aug 3 22:47 recover_dictionary.sh -rw-r--r-- 1 root root 8.3K Aug 3 22:47 sql_parser.l drwxr-xr-x 2 root root 4.0K Aug 3 22:47 sakila -rwxr-xr-x 1 root root 6.5K Aug 3 22:47 test.sh -rw-r--r-- 1 root root 2.2K Aug 3 22:47 tables_dict.c -rw-r--r-- 1 root root 15K Aug 3 22:47 sys_parser.c -rw-r--r-- 1 root root 25K Aug 3 22:47 stream_parser.c -rw-r--r-- 1 root root 26K Aug 3 22:47 sql_parser.y drwxr-xr-x 3 root root 44 Aug 3 22:47 vagrant1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36. 编译工具 复制[root@cjc-db-01 soft]# yum install -y make gcc flex bison [root@cjc-db-01 soft]# cd /mysqldata/tools/undrop-for-innodb/ [root@cjc-db-01 undrop-for-innodb]# make [root@cjc-db-01 tools]# du -sh undrop-for-innodb/ 6.9M undrop-for-innodb/ [root@cjc-db-01 tools]# ls -lrht undrop-for-innodb/ total 2.9M -rw-r--r-- 1 root root 2.5K Aug 3 22:47 Makefile -rw-r--r-- 1 root root 18K Aug 3 22:47 LICENSE -rw-r--r-- 1 root root 2.0K Aug 3 22:47 README.md -rw-r--r-- 1 root root 29K Aug 3 22:47 c_parser.c -rw-r--r-- 1 root root 6.2K Aug 3 22:47 check_data.c -rw-r--r-- 1 root root 2.0K Aug 3 22:47 fetch_data.sh drwxr-xr-x 2 root root 96 Aug 3 22:47 dictionary -rw-r--r-- 1 root root 8.8K Aug 3 22:47 innochecksum.c drwxr-xr-x 2 root root 4.0K Aug 3 22:47 include -rw-r--r-- 1 root root 17K Aug 3 22:47 print_data.c drwxr-xr-x 4 root root 4.0K Aug 3 22:47 omnibus-undrop-for-innodb -rwxr-xr-x 1 root root 1.5K Aug 3 22:47 recover_dictionary.sh -rw-r--r-- 1 root root 8.3K Aug 3 22:47 sql_parser.l drwxr-xr-x 2 root root 4.0K Aug 3 22:47 sakila -rwxr-xr-x 1 root root 6.5K Aug 3 22:47 test.sh -rw-r--r-- 1 root root 2.2K Aug 3 22:47 tables_dict.c -rw-r--r-- 1 root root 15K Aug 3 22:47 sys_parser.c -rw-r--r-- 1 root root 25K Aug 3 22:47 stream_parser.c -rw-r--r-- 1 root root 26K Aug 3 22:47 sql_parser.y drwxr-xr-x 3 root root 44 Aug 3 22:47 vagrant -rw-r--r-- 1 root root 111K Aug 3 22:56 stream_parser.o -rwxr-xr-x 1 root root 62K Aug 3 22:56 stream_parser -rw-r--r-- 1 root root 151K Aug 3 22:56 lex.yy.c -rw-r--r-- 1 root root 102K Aug 3 22:56 sql_parser.c -rw-r--r-- 1 root root 295K Aug 3 22:56 sql_parser.o -rw-r--r-- 1 root root 1010K Aug 3 22:56 c_parser.o -rw-r--r-- 1 root root 40K Aug 3 22:56 tables_dict.o -rw-r--r-- 1 root root 126K Aug 3 22:56 print_data.o -rw-r--r-- 1 root root 65K Aug 3 22:56 check_data.o -rwxr-xr-x 1 root root 719K Aug 3 22:56 c_parser -rwxr-xr-x 1 root root 36K Aug 3 22:56 innochecksum_changer1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42. 编译 sys_parser 工具 复制[root@cjc-db-01 undrop-for-innodb]# make sys_parser /usr/bin/mysql_config cc -o sys_parser sys_parser.c `mysql_config --cflags` `mysql_config --libs` [root@cjc-db-01 undrop-for-innodb]# ls -lrth sys_parser -rwxr-xr-x 1 root root 21K Aug 4 11:29 sys_parser [root@cjc-db-01 undrop-for-innodb]# ./sys_parser --help ./sys_parser: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory [root@cjc-db-01 ~]# find /mysqldata -name libmysqlclient.so.20 /mysqldata/app/5.7.44/lib/libmysqlclient.so.20 ln -s /mysqldata/app/5.7.44/lib/libmysqlclient.so.20 /usr/local/lib/libmysqlclient.so.20 [root@cjc-db-01 /]# vi /etc/ld.so.conf include ld.so.conf.d/*.conf /usr/local/lib1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23. 查看工具帮助信息 复制[mysql@cjc-db-01 undrop-for-innodb]$ ./sys_parser -help sys_parser [-h <host>] [-u <user>] [-p <passowrd>] [-d <db>] databases/table [mysql@cjc-db-01 undrop-for-innodb]$ ./stream_parser --help ./stream_parser: invalid option -- - Usage: ./stream_parser -f <innodb_datafile> [-T N:M] [-s size] [-t size] [-V|-g] Where: -h - Print this help -V or -g - Print debug information -s size - Amount of memory used for disk cache (allowed examples 1G 10M). Default 100M -T - retrieves only pages with index id = NM (N - high word, M - low word of id) -t size - Size of InnoDB tablespace to scan. Use it only if the parser cant determine it by himself.1.2.3.4.5.6.7.8.9.10.11.12.13.

3.扫描mysql数据文件所在磁盘:

复制[root@cjc-db-01 undrop-for-innodb]# df -k / Filesystem 1K-blocks Used Available Use% Mounted on /dev/mapper/vg_cjc-root 49246728 39799904 9446824 81% / [root@cjc-db-01 undrop-for-innodb]# ./stream_parser -f /dev/mapper/vg_cjc-root ...... Cant determine size of /dev/mapper/vg_cjc-root. Specify it manually with -t option All workers finished in 0 sec1.2.3.4.5.6.7.8.9. 需要指定 -t 参数 复制[root@cjc-db-01 undrop-for-innodb]# ./stream_parser -f /dev/mapper/vg_cjc-root -t 49246728k1. 复制Opening file: /dev/mapper/vg_cjc-root File information: ID of device containing file: 6 inode number: 10124 protection: 60660 (block device) number of hard links: 1 user ID of owner: 0 group ID of owner: 6 device ID (if special file): 63744 blocksize for filesystem I/O: 4096 number of blocks allocated: 0 time of last access: 1722733854 Sun Aug 4 09:10:54 2024 time of last modification: 1722733854 Sun Aug 4 09:10:54 2024 time of last status change: 1722733854 Sun Aug 4 09:10:54 2024 total size, in bytes: 0 (0.000 exp(+0)) Size to process: 50428649472 (46.965 GiB) Worker(0): 1.03% done. 2024-08-04 10:18:04 ETA(in 00:14:39). Processing speed: 54.119 MiB/sec Worker(0): 2.04% done. 2024-08-04 10:16:26 ETA(in 00:12:53). Processing speed: 60.882 MiB/sec ...... Worker(0): 95.61% done. 2024-08-04 10:15:28 ETA(in 00:00:38). Processing speed: 54.122 MiB/sec Worker(0): 96.63% done. 2024-08-04 10:15:28 ETA(in 00:00:29). Processing speed: 54.118 MiB/sec Worker(0): 97.64% done. 2024-08-04 10:15:28 ETA(in 00:00:20). Processing speed: 54.118 MiB/sec Worker(0): 98.65% done. 2024-08-04 10:15:26 ETA(in 00:00:10). Processing speed: 60.882 MiB/sec Worker(0): 99.67% done. 2024-08-04 10:15:25 ETA(in 00:00:02). Processing speed: 69.632 MiB/sec All workers finished in 729 sec1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.

查看文件:

复制[root@cjc-db-01 undrop-for-innodb]# du -sh pages-vg_cjc-root/ 40M pages-vg_cjc-root/ [root@cjc-db-01 undrop-for-innodb]# ls -lrht pages-vg_cjc-root/ total 16K drwxr-xr-x 2 root root 4.0K Aug 4 10:12 FIL_PAGE_TYPE_BLOB drwxr-xr-x 2 root root 8.0K Aug 4 10:13 FIL_PAGE_INDEX1.2.3.4.5.6.7.8.

4.扫描ibdata文件

复制[root@cjc-db-01 undrop-for-innodb]# ls -lrth /mysqldata/3308/data/ibdata1 -rw-r----- 1 mysql mysql 512M Aug 4 09:59 /mysqldata/3308/data/ibdata1 [root@cjc-db-01 undrop-for-innodb]# ./stream_parser -f /mysqldata/3308/data/ibdata1 Opening file: /mysqldata/3308/data/ibdata1 File information: ID of device containing file: 63744 inode number: 242014208 protection: 100640 (regular file) number of hard links: 1 user ID of owner: 621 group ID of owner: 621 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 1048584 time of last access: 1722736643 Sun Aug 4 09:57:23 2024 time of last modification: 1722736769 Sun Aug 4 09:59:29 2024 time of last status change: 1722736769 Sun Aug 4 09:59:29 2024 total size, in bytes: 536870912 (512.000 MiB) Size to process: 536870912 (512.000 MiB) Worker(0): 6.25% done. 2024-08-04 10:18:31 ETA(in 00:00:20). Processing speed: 24.000 MiB/sec Worker(0): 15.62% done. 2024-08-04 10:18:21 ETA(in 00:00:09). Processing speed: 48.000 MiB/sec All workers finished in 2 sec [root@cjc-db-01 undrop-for-innodb]# du -sh pages-ibdata1 1.2M pages-ibdata1 [root@cjc-db-01 ~]# cd /mysqldata/tools/undrop-for-innodb [root@cjc-db-01 undrop-for-innodb]# ./c_parser -4Df /mysqldata/tools/undrop-for-innodb/pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t ./dictionary/SYS_TABLES.sql | grep txxxxx SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE /mysqldata/tools/undrop-for-innodb/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": 27, "records_dumped": 2, "records_lost": true} STATUS END 000000001D19 350000002704C2 SYS_TABLES "cjc/txxxxx" 46 2 33 0 80 "" 451.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.

可以看到 cjc/txxxxx 的 ID 是 46。

参数解析： 复制4 表示文件格式是云服务器据被 REDUNDANT，系统表的误删p无格式默认值。另外可以取值 5 表示 COMPACT 格式，备份6 表示 MySQL 5.6 格式。何恢 D 表示只恢复被删除的据被记录。 f 后面跟着文件。误删p无 t 后面跟着 CREATE TABLE 语句，备份需要根据表的格式来解析文件。1.2.3.4.

扫描列信息：

复制[root@cjc-db-01 undrop-for-innodb]# ./c_parser -4Df /mysqldata/tools/undrop-for-innodb/pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -t ./dictionary/SYS_COLUMNS.sql | grep 46 SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE /mysqldata/tools/undrop-for-innodb/dumps/default/SYS_COLUMNS REPLACE INTO TABLE `SYS_COLUMNS` CHARACTER SET UTF8 FIELDS TERMINATED BY \t OPTIONALLY ENCLOSED BY " LINES STARTING BY SYS_COLUMNS\t (`TABLE_ID`, `POS`, `NAME`, `MTYPE`, `PRTYPE`, `LEN`, `PREC`); -- STATUS {"records_expected": 138, "records_dumped": 20, "records_lost": true} STATUS END 000000001D19 35000000270458 SYS_COLUMNS 46 0 "id" 6 1027 4 0 000000001D19 3500000027048D SYS_COLUMNS 46 1 "time" 3 525324 5 01.2.3.4.5.6.

扫描索引信息：

复制[root@cjc-db-01 undrop-for-innodb]# ./c_parser -4Df /mysqldata/tools/undrop-for-innodb/pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -t ./dictionary/SYS_FIELDS.sql | grep 46 SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE /mysqldata/tools/undrop-for-innodb/dumps/default/SYS_FIELDS REPLACE INTO TABLE `SYS_FIELDS` CHARACTER SET UTF8 FIELDS TERMINATED BY \t OPTIONALLY ENCLOSED BY " LINES STARTING BY SYS_FIELDS\t (`INDEX_ID`, `POS`, `COL_NAME`); -- STATUS {"records_expected": 138, "records_dumped": 20, "records_lost": true} STATUS END 000000001D19 35000000270458 SYS_FIELDS 46 0 "id" 000000001D19 3500000027048D SYS_FIELDS 46 1 "time"1.2.3.4.5.6.

扫描磁盘页：

复制[root@cjc-db-01 undrop-for-innodb]# ./c_parser -4Df /mysqldata/tools/undrop-for-innodb/pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t ./dictionary/SYS_INDEXES.sql | grep 46 SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE /mysqldata/tools/undrop-for-innodb/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": 98, "records_dumped": 4, "records_lost": true} STATUS END 000000001D19 3500000027041B SYS_INDEXES 46 47 "GEN\_CLUST\_INDEX" 0 1 45 42949672951.2.3.4.5.

ID 46对应的数值是亿华云47，这里的47就是txxxxx数据所在的的磁盘页ID。

复制[root@cjc-db-01 undrop-for-innodb]# ls -lrth pages-vg_cjc-root/FIL_PAGE_INDEX/0000000000000047.page -rw-r--r-- 1 mysql mysql 160K Aug 4 10:15 pages-vg_cjc-root/FIL_PAGE_INDEX/0000000000000047.page1.2.

5.生成字典表

复制[mysql@cjc-db-01 undrop-for-innodb]$ more recover_dictionary.xxx.sh1.

默认创建test库，并将字典表还原到test库下，也可以修改脚本指定其他库，默认没有指定mysql密码，可以手动添加密码。

复制[mysql@cjc-db-01 undrop-for-innodb]$ vi recover_dictionary.xxx.sh [mysql@cjc-db-01 undrop-for-innodb]$ sh recover_dictionary.xxx.sh Generating dictionary tables dumps... OK Creating test database ... mysql: [Warning] Using a password on the command line interface can be insecure. OK Creating dictionary tables in database test: SYS_TABLES ... mysql: [Warning] Using a password on the command line interface can be insecure. OK SYS_COLUMNS ... mysql: [Warning] Using a password on the command line interface can be insecure. OK SYS_INDEXES ... mysql: [Warning] Using a password on the command line interface can be insecure. OK SYS_FIELDS ... mysql: [Warning] Using a password on the command line interface can be insecure. OK All OK Loading dictionary tables data: SYS_TABLES ... mysql: [Warning] Using a password on the command line interface can be insecure. 29 recs OK SYS_COLUMNS ... mysql: [Warning] Using a password on the command line interface can be insecure. 158 recs OK SYS_INDEXES ... mysql: [Warning] Using a password on the command line interface can be insecure. 102 recs OK SYS_FIELDS ... mysql: [Warning] Using a password on the command line interface can be insecure. 44 recs OK All OK1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.

检查字段表:

复制mysql> use test; Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | sys_columns | | sys_fields | | sys_indexes | | sys_tables | +----------------+ 4 rows in set (0.00 sec) mysql> select * from sys_tables where name=cjc/txxxxx; +------------+----+--------+------+--------+---------+--------------+-------+ | NAME | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE | +------------+----+--------+------+--------+---------+--------------+-------+ | cjc/txxxxx | 46 | 2 | 33 | 0 | 80 | | 45 | +------------+----+--------+------+--------+---------+--------------+-------+ 1 row in set (0.00 sec)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.

6.恢复表结构

复制[root@cjc-db-01 undrop-for-innodb]# ./sys_parser -uroot -p1 -dtest cjc/txxxxx CREATE TABLE `txxxxx`( `id` INT, WARNING: Fields are not found for table cjc/txxxxx in SYS_FIELDS `time` DATETIME) ENGINE=InnoDB; [root@cjc-db-01 undrop-for-innodb]# mkdir txxxxx [root@cjc-db-01 undrop-for-innodb]# vi txxxxx/txxxxx_01.sql CREATE TABLE `txxxxx`( `id` INT, `time` DATETIME) ENGINE=InnoDB; mysql> use cjc; Database changed mysql> source /mysqldata/tools/undrop-for-innodb/txxxxx/txxxxx_01.sql Query OK, 0 rows affected (0.03 sec)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.

7.恢复表数据查看数据

复制[root@cjc-db-01 undrop-for-innodb]# ./c_parser -6f pages-vg_cjc-root/FIL_PAGE_INDEX/0000000000000047.page -t txxxxx/txxxxx_01.sql |more -- Page id: 387, Format: COMPACT, Records list: Valid, Expected records: (58 58) 000000000000 000100000000 02238100000085 txxxxx NULL "9736-55-67 81:56:80" 000000000000 000200000000 02238100000085 txxxxx -2108096512 "0000-00-00 15:16:34" 000000000000 000300000000 02238100000085 txxxxx -2090729472 "0000-00-00 21:71:70" ...... -- Page id: 417, Found records: 74, Lost records: NO, Leaf page: YES -- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (3 3) 000000000403 000000001D12 B00000001D0110 txxxxx 1 "2024-08-04 09:54:21.0" 000000000404 000000001D13 B10000001F0110 txxxxx 2 "2024-08-04 09:54:34.0" 000000000405 000000001D18 B40000001E0110 txxxxx 3 "2024-08-04 09:55:01.0" -- Page id: 3, Found records: 3, Lost records: NO, Leaf page: YES -- Page id: 51, Format: COMPACT, Records list: Valid, Expected records: (3 3) -- Page id: 51, Found records: 0, Lost records: YES, Leaf page: NO1.2.3.4.5.6.7.8.9.10.11.12.13.14.

生成数据:

复制[root@cjc-db-01 undrop-for-innodb]# ./c_parser -6f pages-vg_cjc-root/FIL_PAGE_INDEX/0000000000000047.page -t txxxxx/txxxxx_01.sql > txxxxx/txxxxx_02.sql SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE /mysqldata/tools/undrop-for-innodb/dumps/default/txxxxx REPLACE INTO TABLE `txxxxx` CHARACTER SET UTF8 FIELDS TERMINATED BY \t OPTIONALLY ENCLOSED BY " LINES STARTING BY txxxxx\t (`id`, `time`); -- STATUS {"records_expected": 574, "records_dumped": 574, "records_lost": false} STATUS END1.2.3.4.

编辑生成脚本

复制vi txxxxx/txxxxx_02.sql 000000000403 000000001D12 B00000001D0110 txxxxx 1 "2024-08-04 09:54:21.0" 000000000404 000000001D13 B10000001F0110 txxxxx 2 "2024-08-04 09:54:34.0" 000000000405 000000001D18 B40000001E0110 txxxxx 3 "2024-08-04 09:55:01.0"1.2.3.4.

恢复数据:

复制mysql> LOAD DATA LOCAL INFILE /mysqldata/tools/undrop-for-innodb/txxxxx/txxxxx_02.sql INTO TABLE txxxxx FIELDS TERMINATED BY \t OPTIONALLY ENCLOSED BY " LINES STARTING BY txxxxx\t (`id`, `time`); Query OK, 3 rows affected (0.02 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 01.2.3.4.5.6.7.8.9.10.

检查数据:

复制mysql> select * from txxxxx; +------+---------------------+ | id | time | +------+---------------------+ | 1 | 2024-08-04 09:54:21 | | 2 | 2024-08-04 09:54:34 | | 3 | 2024-08-04 09:55:01 | +------+---------------------+ 3 rows in set (0.00 sec) mysql> checksum table cjc.txxxxx; +------------+-----------+ | Table | Checksum | +------------+-----------+ | cjc.txxxxx | 403036823 | +------------+-----------+ 1 row in set (0.00 sec)1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.

参考：

复制https://github.com/twindb/undrop-for-innodb http://www.killdb.com/2017/08/01/%e6%97%a0%e5%a4%87%e4%bb%bd%e6%83%85%e5%86%b5%e4%b8%8b%e6%81%a2%e5%a4%8dmysql-drop-table/ https://www.modb.pro/db/69175 https://www.modb.pro/db/1976311.2.3.4.