- 当前位置：首页 >数据库 >MySQL 数据被误删除drop！无备份，如何恢复？
MySQL 数据被误删除drop！无备份，如何恢复？
发布时间：2025-11-04 14:23:06 来源：码上建站 作者：IT科技类资讯
环境说明:复制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.企商汇益华科技IT技术网IT资讯网亿华云源码库源码下载服务器租用香港云服务器亿华互联IT资讯网云智核益强数据堂益强智囊团亿华灵动益华科技全栈开发科技前瞻益华科技益强编程堂码上建站多维IT资讯益强智未来益强前沿资讯亿华云计算极客码头技术快报亿华智慧云益强IT技术网运维纵横益强科技亿华智造思维库智能时代极客编程编程之道亿华云益强编程舍码力社益强资讯优选汇智坊益强科技亿华科技
- Copyright © 2025 Powered by MySQL 数据被误删除drop！无备份，如何恢复？,码上建站 滇ICP备2023006006号-47sitemap