MySQL 數(shù)據(jù)被誤刪除drop！無(wú)備份，如何恢復(fù)？

作者：chenjuchao 2024-08-09 10:06:09

undrop-for-innodb 是針對(duì) MySQL innodb 的數(shù)據(jù)恢復(fù)工具，本文主要介紹在無(wú)備份、無(wú)binlog等場(chǎng)景下，如何恢復(fù)drop table的表數(shù)據(jù)。

環(huán)境說(shuō)明:

MySQL:5.7.44
OS:Redhat 7.5

說(shuō)明:undrop-for-innodb 是針對(duì) MySQL innodb 的數(shù)據(jù)恢復(fù)工具，本文主要介紹在無(wú)備份、無(wú)binlog等場(chǎng)景下，如何恢復(fù)drop table的表數(shù)據(jù)。

步驟:

1.模擬誤刪除(drop table);
2.安裝、編譯undrop-for-innodb工具；
3.掃描mysql數(shù)據(jù)文件所在磁盤；
4.掃描ibdata文件；
5.生成字典表；
6.恢復(fù)表結(jié)構(gòu)；
7.恢復(fù)表數(shù)據(jù)；

1.模擬誤刪除(drop table);

創(chuàng)建測(cè)試數(shù)據(jù):

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|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.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工具

下載恢復(fù)工具

[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 vagrant

編譯工具

[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_changer

編譯 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/lib

查看工具幫助信息

[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 can't determine it by himself.

3.掃描mysql數(shù)據(jù)文件所在磁盤:

[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 option
All workers finished in 0 sec

需要指定 -t 參數(shù)

[root@cjc-db-01 undrop-for-innodb]# ./stream_parser -f /dev/mapper/vg_cjc-root  -t 49246728k

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 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 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_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/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    ""    45

可以看到 cjc/txxxxx 的 ID 是 46。
參數(shù)解析：

4 表示文件格式是 REDUNDANT，系統(tǒng)表的格式默認(rèn)值。另外可以取值 5 表示 COMPACT 格式，6 表示 MySQL 5.6 格式。
D 表示只恢復(fù)被刪除的記錄。
f 后面跟著文件。
t 后面跟著 CREATE TABLE 語(yǔ)句，需要根據(jù)表的格式來(lái)解析文件。

掃描列信息：

[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    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 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"

掃描磁盤頁(yè)：

[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    4294967295

ID 46對(duì)應(yīng)的數(shù)值是47，這里的47就是txxxxx數(shù)據(jù)所在的的磁盤頁(yè)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

默認(rèn)創(chuàng)建test庫(kù)，并將字典表還原到test庫(kù)下，也可以修改腳本指定其他庫(kù)，默認(rèn)沒有指定mysql密碼，可以手動(dòng)添加密碼。

[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 OK

檢查字段表:

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)

6.恢復(fù)表結(jié)構(gòu)

[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)

7.恢復(fù)表數(shù)據(jù)查看數(shù)據(jù)

[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: NO

生成數(shù)據(jù):

[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 END

編輯生成腳本

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"

恢復(fù)數(shù)據(jù):

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: 0

檢查數(shù)據(jù):

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-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/197631

責(zé)任編輯：武曉燕來(lái)源：瀟湘秦