MySQL 数据被误删除drop！无备份，如何恢复？
环境说明:
MySQL:5.7.44OS:Redhat 7.5
说明:
undrop-for-innodb 是针对 MySQL innodb 的数据恢复工具，本文主要介绍在无备份、无binlog等场景下，如何恢复drop table的表数据。
步骤:
1.模拟误删除(drop table);2.安装、编译undrop-for-innodb工具；3.扫描mysql数据文件所在磁盘；4.扫描ibdata文件；5.生成字典表；6.恢复表结构；7.恢复表数据；
1.模拟误删除(drop table);
创建测试数据:
mysql> create table cjc.txxxxx(id int,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|morecjc/txxxxx.cjc/txxxxxLcjc/txxxxxX./cjc/txxxxx.ibdYcjctxxxxxGEN_CLUST_INDEXn_diff_pfx01icjctxxxxxGEN_CLUST_INDEXn_leaf_pagesicjctxxxxxGEN_CLUST_INDEXsizeX./cjc/txxxxx.ibd
删除表，模拟误删除
mysql> drop table cjc.txxxxx;Query OK, 0 rows affected (0.35 sec)mysql> select * from cjc.txxxxx;ERROR 1146 (42S02): Table 'cjc.txxxxx' doesn't exist
2.安装、编译undrop-for-innodb工具
1.下载恢复工具
[root@cjc-db-01 back]# git clone https://github.com/twindb/undrop-for-innodb.gitCloning 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 364Receiving 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.shdrwxr-xr-x 2 root root 96 Aug 3 22:47 dictionary-rw-r--r-- 1 root root 8.8K Aug 3 22:47 innochecksum.cdrwxr-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.cdrwxr-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.ldrwxr-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.ydrwxr-xr-x 3 root root 44 Aug 3 22:47 vagrant
2.编译工具
[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.shdrwxr-xr-x 2 root root 96 Aug 3 22:47 dictionary-rw-r--r-- 1 root root 8.8K Aug 3 22:47 innochecksum.cdrwxr-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.cdrwxr-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.ldrwxr-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.ydrwxr-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_changer
编译 sys_parser 工具
[root@cjc-db-01 undrop-for-innodb]# make sys_parser/usr/bin/mysql_configcc -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.20ln -s /mysqldata/app/5.7.44/lib/libmysqlclient.so.20 /usr/local/lib/libmysqlclient.so.20[root@cjc-db-01 /]# vi /etc/ld.so.confinclude ld.so.conf.d/*.conf/usr/local/lib
查看工具帮助信息
[mysql@cjc-db-01 undrop-for-innodb]$ ./sys_parser -helpsys_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 can't determine it by himself.
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......Can't determine size of /dev/mapper/vg_cjc-root. Specify it manually with -t optionAll workers finished in 0 sec
需要指定 -t 参数
[root@cjc-db-01 undrop-for-innodb]# ./stream_parser -f /dev/mapper/vg_cjc-root -t 49246728kOpening file: /dev/mapper/vg_cjc-rootFile information:ID of device containing file: 6inode number: 10124protection: 60660 (block device)number of hard links: 1user ID of owner: 0group ID of owner: 6device ID (if special file): 63744blocksize for filesystem I/O: 4096number of blocks allocated: 0time of last access: 1722733854 Sun Aug 4 09:10:54 2024time of last modification: 1722733854 Sun Aug 4 09:10:54 2024time of last status change: 1722733854 Sun Aug 4 09:10:54 2024total 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/secWorker(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/secWorker(0): 96.63% done. 2024-08-04 10:15:28 ETA(in 00:00:29). Processing speed: 54.118 MiB/secWorker(0): 97.64% done. 2024-08-04 10:15:28 ETA(in 00:00:20). Processing speed: 54.118 MiB/secWorker(0): 98.65% done. 2024-08-04 10:15:26 ETA(in 00:00:10). Processing speed: 60.882 MiB/secWorker(0): 99.67% done. 2024-08-04 10:15:25 ETA(in 00:00:02). Processing speed: 69.632 MiB/secAll workers finished in 729 sec
查看文件:
[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 16Kdrwxr-xr-x 2 root root 4.0K Aug 4 10:12 FIL_PAGE_TYPE_BLOBdrwxr-xr-x 2 root root 8.0K Aug 4 10:13 FIL_PAGE_INDEX
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/ibdata1Opening file: /mysqldata/3308/data/ibdata1File information:ID of device containing file: 63744inode number: 242014208protection: 100640 (regular file)number of hard links: 1user ID of owner: 621group ID of owner: 621device ID (if special file): 0blocksize for filesystem I/O: 4096number of blocks allocated: 1048584time of last access: 1722736643 Sun Aug 4 09:57:23 2024time of last modification: 1722736769 Sun Aug 4 09:59:29 2024time of last status change: 1722736769 Sun Aug 4 09:59:29 2024total 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/secWorker(0): 15.62% done. 2024-08-04 10:18:21 ETA(in 00:00:09). Processing speed: 48.000 MiB/secAll workers finished in 2 sec[root@cjc-db-01 undrop-for-innodb]# du -sh pages-ibdata11.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 txxxxxSET 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 END000000001D19 350000002704C2 SYS_TABLES "cjc/txxxxx" 46 2 33 0 80 "" 45
可以看到 cjc/txxxxx 的 ID 是 46。
参数解析：
4 表示文件格式是 REDUNDANT，系统表的格式默认值。另外可以取值 5 表示 COMPACT 格式，6 表示 MySQL 5.6 格式。D 表示只恢复被删除的记录。f 后面跟着文件。t 后面跟着 CREATE TABLE 语句，需要根据表的格式来解析文件。
扫描列信息
[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 46SET 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 END000000001D19 35000000270458 SYS_COLUMNS 46 0 "id" 6 1027 4 0000000001D19 3500000027048D SYS_COLUMNS 46 1 "time" 3 525324 5 0
扫描索引信息
[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 46SET 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 END000000001D19 35000000270458 SYS_FIELDS 46 0 "id"000000001D19 3500000027048D SYS_FIELDS 46 1 "time"
扫描磁盘页
[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 46SET 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 END000000001D19 3500000027041B SYS_INDEXES 46 47 "GEN\_CLUST\_INDEX" 0 1 45 4294967295
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.page
5.生成字典表
[mysql@cjc-db-01 undrop-for-innodb]$ more recover_dictionary.xxx.sh 默认创建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.shGenerating dictionary tables dumps... OKCreating test database ... mysql: [Warning] Using a password on the command line interface can be insecure.OKCreating dictionary tables in database test:SYS_TABLES ... mysql: [Warning] Using a password on the command line interface can be insecure.OKSYS_COLUMNS ... mysql: [Warning] Using a password on the command line interface can be insecure.OKSYS_INDEXES ... mysql: [Warning] Using a password on the command line interface can be insecure.OKSYS_FIELDS ... mysql: [Warning] Using a password on the command line interface can be insecure.OKAll OKLoading dictionary tables data:SYS_TABLES ... mysql: [Warning] Using a password on the command line interface can be insecure.29 recs OKSYS_COLUMNS ... mysql: [Warning] Using a password on the command line interface can be insecure.158 recs OKSYS_INDEXES ... mysql: [Warning] Using a password on the command line interface can be insecure.102 recs OKSYS_FIELDS ... mysql: [Warning] Using a password on the command line interface can be insecure.44 recs OKAll OK
检查字段表:
mysql> use test;Database changedmysql> 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)
6.恢复表结构
[root@cjc-db-01 undrop-for-innodb]# ./sys_parser -uroot -p1 -dtest cjc/txxxxxCREATE 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.sqlCREATE TABLE `txxxxx`(`id` INT,`time` DATETIME) ENGINE=InnoDB;mysql> use cjc;Database changedmysql> source /mysqldata/tools/undrop-for-innodb/txxxxx/txxxxx_01.sqlQuery OK, 0 rows affected (0.03 sec)
7.恢复表数据
查看数据:
[root@cjc-db-01 undrop-for-innodb]-- 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: NO
生成数据:
[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.sqlSET 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 END
编辑生成脚本
vi txxxxx/txxxxx_02.sql000000000403 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"
恢复数据
mysql>LOAD DATA LOCAL INFILE '/mysqldata/tools/undrop-for-innodb/txxxxx/txxxxx_02.sql'INTO TABLE txxxxxFIELDS 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: 0
检查数据
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)
参考：
https://github.com/twindb/undrop-for-innodbhttp://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/69175https://www.modb.pro/db/197631
浙公网安备 33010602011771号