恢复MySQL 5.7被Drop掉的表
innodb_file_per_table=ON 场景下，drop 表后，.ibd 文件也被删除了，需要扫描磁盘获取相应的数据页进行恢复。而如果是innodb_file_per_table=OFF，则数据存放在共享表空间ibdata1里的，只要扫描该ibdata1 文件即可。下面以innodb_file_per_table=ON 场景下恢复为例
一.安装undrop-for-innodb软件
1:上传undrop-for-innodb-develop.zip
略
2:安装编译环境
yum install make gcc flex bison
3：解压
[root@localhost /]# unzip undrop-for-innodb-master.zip Archive: undrop-for-innodb-master.zip ade0fbb31218f655eeff97f559d47c6545478734 creating: undrop-for-innodb-master/ inflating: undrop-for-innodb-master/LICENSE inflating: undrop-for-innodb-master/Makefile inflating: undrop-for-innodb-master/README.md inflating: undrop-for-innodb-master/c_parser.c inflating: undrop-for-innodb-master/check_data.c creating: undrop-for-innodb-master/dictionary/ inflating: undrop-for-innodb-master/dictionary/SYS_COLUMNS.sql inflating: undrop-for-innodb-master/dictionary/SYS_FIELDS.sql inflating: undrop-for-innodb-master/dictionary/SYS_INDEXES.sql inflating: undrop-for-innodb-master/dictionary/SYS_TABLES.sql inflating: undrop-for-innodb-master/fetch_data.sh creating: undrop-for-innodb-master/include/ inflating: undrop-for-innodb-master/include/bchange.c inflating: undrop-for-innodb-master/include/bcmp.c inflating: undrop-for-innodb-master/include/bfill.c inflating: undrop-for-innodb-master/include/bmove.c inflating: undrop-for-innodb-master/include/bmove512.c inflating: undrop-for-innodb-master/include/bmove_upp.c inflating: undrop-for-innodb-master/include/btr0btr.h inflating: undrop-for-innodb-master/include/btr0btr.ic inflating: undrop-for-innodb-master/include/btr0cur.h inflating: undrop-for-innodb-master/include/btr0cur.ic inflating: undrop-for-innodb-master/include/btr0types.h inflating: undrop-for-innodb-master/include/buf0buf.h inflating: undrop-for-innodb-master/include/buf0buf.ic inflating: undrop-for-innodb-master/include/buf0flu.h inflating: undrop-for-innodb-master/include/buf0flu.ic inflating: undrop-for-innodb-master/include/buf0lru.h inflating: undrop-for-innodb-master/include/buf0lru.ic inflating: undrop-for-innodb-master/include/buf0rea.h inflating: undrop-for-innodb-master/include/buf0types.h inflating: undrop-for-innodb-master/include/check_data.h inflating: undrop-for-innodb-master/include/ctype-bin.c inflating: undrop-for-innodb-master/include/ctype-latin1.c inflating: undrop-for-innodb-master/include/ctype-mb.c inflating: undrop-for-innodb-master/include/ctype-simple.c inflating: undrop-for-innodb-master/include/ctype-utf8.c inflating: undrop-for-innodb-master/include/ctype.c inflating: undrop-for-innodb-master/include/data0data.h inflating: undrop-for-innodb-master/include/data0data.ic inflating: undrop-for-innodb-master/include/data0type.h inflating: undrop-for-innodb-master/include/data0type.ic inflating: undrop-for-innodb-master/include/data0types.h inflating: undrop-for-innodb-master/include/db0err.h inflating: undrop-for-innodb-master/include/decimal.c inflating: undrop-for-innodb-master/include/decimal.h inflating: undrop-for-innodb-master/include/dict0dict.h inflating: undrop-for-innodb-master/include/dict0dict.ic inflating: undrop-for-innodb-master/include/dict0load.h inflating: undrop-for-innodb-master/include/dict0load.ic inflating: undrop-for-innodb-master/include/dict0mem.h inflating: undrop-for-innodb-master/include/dict0mem.ic inflating: undrop-for-innodb-master/include/dict0types.h inflating: undrop-for-innodb-master/include/dyn0dyn.h inflating: undrop-for-innodb-master/include/dyn0dyn.ic inflating: undrop-for-innodb-master/include/fil0fil.h inflating: undrop-for-innodb-master/include/fsp0fsp.h inflating: undrop-for-innodb-master/include/fsp0fsp.ic inflating: undrop-for-innodb-master/include/fut0fut.h inflating: undrop-for-innodb-master/include/fut0fut.ic inflating: undrop-for-innodb-master/include/fut0lst.h inflating: undrop-for-innodb-master/include/fut0lst.ic inflating: undrop-for-innodb-master/include/ha0ha.h inflating: undrop-for-innodb-master/include/ha0ha.ic inflating: undrop-for-innodb-master/include/hash0hash.h inflating: undrop-for-innodb-master/include/hash0hash.ic inflating: undrop-for-innodb-master/include/ib_config.h inflating: undrop-for-innodb-master/include/ibuf0types.h inflating: undrop-for-innodb-master/include/innochecksum.h inflating: undrop-for-innodb-master/include/int2str.c inflating: undrop-for-innodb-master/include/is_prefix.c inflating: undrop-for-innodb-master/include/llstr.c inflating: undrop-for-innodb-master/include/lock0types.h inflating: undrop-for-innodb-master/include/longlong2str.c inflating: undrop-for-innodb-master/include/m_ctype.h inflating: undrop-for-innodb-master/include/m_string.h inflating: undrop-for-innodb-master/include/mach0data.h inflating: undrop-for-innodb-master/include/mach0data.ic inflating: undrop-for-innodb-master/include/mem0dbg.h inflating: undrop-for-innodb-master/include/mem0dbg.ic inflating: undrop-for-innodb-master/include/mem0mem.h inflating: undrop-for-innodb-master/include/mem0mem.ic inflating: undrop-for-innodb-master/include/mem0pool.h inflating: undrop-for-innodb-master/include/mem0pool.ic inflating: undrop-for-innodb-master/include/mtr0log.h inflating: undrop-for-innodb-master/include/mtr0log.ic inflating: undrop-for-innodb-master/include/mtr0mtr.h inflating: undrop-for-innodb-master/include/mtr0mtr.ic inflating: undrop-for-innodb-master/include/mtr0types.h inflating: undrop-for-innodb-master/include/my_alloc.h inflating: undrop-for-innodb-master/include/my_attribute.h inflating: undrop-for-innodb-master/include/my_base.h inflating: undrop-for-innodb-master/include/my_config.h inflating: undrop-for-innodb-master/include/my_dbug.h inflating: undrop-for-innodb-master/include/my_global.h inflating: undrop-for-innodb-master/include/my_list.h inflating: undrop-for-innodb-master/include/my_pthread.h inflating: undrop-for-innodb-master/include/my_strtoll10.c inflating: undrop-for-innodb-master/include/my_sys.h inflating: undrop-for-innodb-master/include/my_vsnprintf.c inflating: undrop-for-innodb-master/include/my_xml.h inflating: undrop-for-innodb-master/include/myisampack.h inflating: undrop-for-innodb-master/include/mysql_def.h inflating: undrop-for-innodb-master/include/os0file.h inflating: undrop-for-innodb-master/include/os0proc.h inflating: undrop-for-innodb-master/include/os0proc.ic inflating: undrop-for-innodb-master/include/os0sync.h inflating: undrop-for-innodb-master/include/os0sync.ic inflating: undrop-for-innodb-master/include/os0thread.h inflating: undrop-for-innodb-master/include/os0thread.ic inflating: undrop-for-innodb-master/include/page0cur.h inflating: undrop-for-innodb-master/include/page0cur.ic inflating: undrop-for-innodb-master/include/page0page.h inflating: undrop-for-innodb-master/include/page0page.ic inflating: undrop-for-innodb-master/include/page0types.h inflating: undrop-for-innodb-master/include/print_data.h inflating: undrop-for-innodb-master/include/que0types.h inflating: undrop-for-innodb-master/include/r_strinstr.c inflating: undrop-for-innodb-master/include/raid.h inflating: undrop-for-innodb-master/include/read0types.h inflating: undrop-for-innodb-master/include/rem0cmp.h inflating: undrop-for-innodb-master/include/rem0cmp.ic inflating: undrop-for-innodb-master/include/rem0rec.h inflating: undrop-for-innodb-master/include/rem0rec.ic inflating: undrop-for-innodb-master/include/rem0types.h inflating: undrop-for-innodb-master/include/row0types.h inflating: undrop-for-innodb-master/include/srv0srv.h inflating: undrop-for-innodb-master/include/str2int.c inflating: undrop-for-innodb-master/include/str_alloc.c inflating: undrop-for-innodb-master/include/strappend.c inflating: undrop-for-innodb-master/include/strcend.c inflating: undrop-for-innodb-master/include/strcont.c inflating: undrop-for-innodb-master/include/strend.c inflating: undrop-for-innodb-master/include/strfill.c inflating: undrop-for-innodb-master/include/strinstr.c inflating: undrop-for-innodb-master/include/strmake.c inflating: undrop-for-innodb-master/include/strmov.c inflating: undrop-for-innodb-master/include/strnlen.c inflating: undrop-for-innodb-master/include/strnmov.c inflating: undrop-for-innodb-master/include/strstr.c inflating: undrop-for-innodb-master/include/strtod.c inflating: undrop-for-innodb-master/include/strtol.c inflating: undrop-for-innodb-master/include/strtoll.c inflating: undrop-for-innodb-master/include/strtoul.c inflating: undrop-for-innodb-master/include/strtoull.c inflating: undrop-for-innodb-master/include/strxmov.c inflating: undrop-for-innodb-master/include/strxnmov.c inflating: undrop-for-innodb-master/include/sync0arr.h inflating: undrop-for-innodb-master/include/sync0arr.ic inflating: undrop-for-innodb-master/include/sync0rw.h inflating: undrop-for-innodb-master/include/sync0rw.ic inflating: undrop-for-innodb-master/include/sync0sync.h inflating: undrop-for-innodb-master/include/sync0sync.ic inflating: undrop-for-innodb-master/include/sync0types.h inflating: undrop-for-innodb-master/include/tables_dict.h inflating: undrop-for-innodb-master/include/trx0sys.h inflating: undrop-for-innodb-master/include/trx0sys.ic inflating: undrop-for-innodb-master/include/trx0trx.h inflating: undrop-for-innodb-master/include/trx0trx.ic inflating: undrop-for-innodb-master/include/trx0types.h inflating: undrop-for-innodb-master/include/trx0undo.h inflating: undrop-for-innodb-master/include/trx0undo.ic inflating: undrop-for-innodb-master/include/trx0xa.h inflating: undrop-for-innodb-master/include/typelib.h inflating: undrop-for-innodb-master/include/univ.i inflating: undrop-for-innodb-master/include/usr0types.h inflating: undrop-for-innodb-master/include/ut0byte.h inflating: undrop-for-innodb-master/include/ut0byte.ic inflating: undrop-for-innodb-master/include/ut0dbg.h inflating: undrop-for-innodb-master/include/ut0lst.h inflating: undrop-for-innodb-master/include/ut0mem.h inflating: undrop-for-innodb-master/include/ut0mem.ic inflating: undrop-for-innodb-master/include/ut0rnd.h inflating: undrop-for-innodb-master/include/ut0rnd.ic inflating: undrop-for-innodb-master/include/ut0ut.h inflating: undrop-for-innodb-master/include/ut0ut.ic inflating: undrop-for-innodb-master/include/xml.c inflating: undrop-for-innodb-master/innochecksum.c inflating: undrop-for-innodb-master/print_data.c inflating: undrop-for-innodb-master/recover_dictionary.sh creating: undrop-for-innodb-master/sakila/ inflating: undrop-for-innodb-master/sakila/actor.sql inflating: undrop-for-innodb-master/sakila/address.sql inflating: undrop-for-innodb-master/sakila/category.sql inflating: undrop-for-innodb-master/sakila/city.sql inflating: undrop-for-innodb-master/sakila/country.sql inflating: undrop-for-innodb-master/sakila/customer.sql inflating: undrop-for-innodb-master/sakila/film.sql inflating: undrop-for-innodb-master/sakila/film_actor.sql inflating: undrop-for-innodb-master/sakila/film_category.sql inflating: undrop-for-innodb-master/sakila/inventory.sql inflating: undrop-for-innodb-master/sakila/language.sql inflating: undrop-for-innodb-master/sakila/payment.sql inflating: undrop-for-innodb-master/sakila/rental.sql inflating: undrop-for-innodb-master/sakila/sakila-db.tar.gz inflating: undrop-for-innodb-master/sakila/staff.sql inflating: undrop-for-innodb-master/sakila/store.sql inflating: undrop-for-innodb-master/sql_parser.l inflating: undrop-for-innodb-master/sql_parser.y inflating: undrop-for-innodb-master/stream_parser.c inflating: undrop-for-innodb-master/sys_parser.c inflating: undrop-for-innodb-master/tables_dict.c inflating: undrop-for-innodb-master/test.sh
4:编译
[root@localhost /]# cd undrop-for-innodb-master
[root@localhost undrop-for-innodb-master]# ll
total 208
-rw-r--r--. 1 root root 6271 Oct 2 2015 check_data.c
-rw-r--r--. 1 root root 28689 Oct 2 2015 c_parser.c
drwxr-xr-x. 2 root root 92 Oct 2 2015 dictionary
-rw-r--r--. 1 root root 1978 Oct 2 2015 fetch_data.sh
drwxr-xr-x. 2 root root 4096 Oct 2 2015 include
-rw-r--r--. 1 root root 8936 Oct 2 2015 innochecksum.c
-rw-r--r--. 1 root root 18047 Oct 2 2015 LICENSE
-rw-r--r--. 1 root root 1816 Oct 2 2015 Makefile
-rw-r--r--. 1 root root 16585 Oct 2 2015 print_data.c
-rw-r--r--. 1 root root 3467 Oct 2 2015 README.md
-rwxr-xr-x. 1 root root 1536 Oct 2 2015 recover_dictionary.sh
drwxr-xr-x. 2 root root 4096 Oct 2 2015 sakila
-rw-r--r--. 1 root root 7244 Oct 2 2015 sql_parser.l
-rw-r--r--. 1 root root 25607 Oct 2 2015 sql_parser.y
-rw-r--r--. 1 root root 23004 Oct 2 2015 stream_parser.c
-rw-r--r--. 1 root root 14764 Oct 2 2015 sys_parser.c
-rw-r--r--. 1 root root 2237 Oct 2 2015 tables_dict.c
-rwxr-xr-x. 1 root root 6178 Oct 2 2015 test.sh
[root@localhost undrop-for-innodb-master]# make
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c stream_parser.c
stream_parser.c: In function ‘valid_innodb_page’:
stream_parser.c:151:31: warning: variable ‘oldcsumfield’ set but not used [-Wunused-but-set-variable]
unsigned int page_n_heap, oldcsumfield;
^
stream_parser.c: In function ‘process_ibpage’:
stream_parser.c:313:9: warning: variable ‘sem’ set but not used [-Wunused-but-set-variable]
int sem = (page_type == FIL_PAGE_INDEX)
^
stream_parser.c: In function ‘process_ibfile’:
stream_parser.c:333:13: warning: variable ‘prev_disk_offset’ set but not used [-Wunused-but-set-variable]
off64_t prev_disk_offset = 0;
^
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -pthread -lm stream_parser.o -o stream_parser
flex sql_parser.l
bison -o sql_parser.c sql_parser.y
sql_parser.y: warning: 6 shift/reduce conflicts [-Wconflicts-sr]
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c sql_parser.c
sql_parser.y: In function ‘yyparse’:
sql_parser.y:166:39: warning: variable ‘none’ set but not used [-Wunused-but-set-variable]
field_def_t trx_id, roll_ptr, none;
^
In file included from sql_parser.y:660:0:
sql_parser.y: At top level:
lex.yy.c:3085:17: warning: ‘yyunput’ defined but not used [-Wunused-function]
static void yyunput (int c, register char * yy_bp )
^
lex.yy.c:3126:16: warning: ‘input’ defined but not used [-Wunused-function]
static int input (void)
^
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c c_parser.c
In file included from c_parser.c:39:0:
decimal.c: In function ‘decimal_intg’:
decimal.c:1923:9: warning: variable ‘tmp_res’ set but not used [-Wunused-but-set-variable]
In file included from c_parser.c:35:0:
c_parser.c: At top level:
./include/ctype-latin1.c:359:5: warning: ‘my_mb_wc_latin1’ defined but not used [-Wunused-function]
int my_mb_wc_latin1(CHARSET_INFO *cs __attribute__((unused)),
^
./include/ctype-latin1.c:372:5: warning: ‘my_wc_mb_latin1’ defined but not used [-Wunused-function]
int my_wc_mb_latin1(CHARSET_INFO *cs __attribute__((unused)),
^
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c tables_dict.c
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c print_data.c
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c check_data.c
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -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 -I./include -o innochecksum_changer innochecksum.c
[root@localhost undrop-for-innodb-master]# ll
total 2904
-rw-r--r--. 1 root root 6271 Oct 2 2015 check_data.c
-rw-r--r--. 1 root root 66088 Jan 17 16:59 check_data.o
-rwxr-xr-x. 1 root root 725572 Jan 17 16:59 c_parser
-rw-r--r--. 1 root root 28689 Oct 2 2015 c_parser.c
-rw-r--r--. 1 root root 1038536 Jan 17 16:59 c_parser.o
drwxr-xr-x. 2 root root 92 Oct 2 2015 dictionary
-rw-r--r--. 1 root root 1978 Oct 2 2015 fetch_data.sh
drwxr-xr-x. 2 root root 4096 Oct 2 2015 include
-rw-r--r--. 1 root root 8936 Oct 2 2015 innochecksum.c
-rwxr-xr-x. 1 root root 36399 Jan 17 16:59 innochecksum_changer
-rw-r--r--. 1 root root 134778 Jan 17 16:59 lex.yy.c
-rw-r--r--. 1 root root 18047 Oct 2 2015 LICENSE
-rw-r--r--. 1 root root 1816 Oct 2 2015 Makefile
-rw-r--r--. 1 root root 16585 Oct 2 2015 print_data.c
-rw-r--r--. 1 root root 127136 Jan 17 16:59 print_data.o
-rw-r--r--. 1 root root 3467 Oct 2 2015 README.md
-rwxr-xr-x. 1 root root 1536 Oct 2 2015 recover_dictionary.sh
drwxr-xr-x. 2 root root 4096 Oct 2 2015 sakila
-rw-r--r--. 1 root root 102053 Jan 17 16:59 sql_parser.c
-rw-r--r--. 1 root root 7244 Oct 2 2015 sql_parser.l
-rw-r--r--. 1 root root 287296 Jan 17 16:59 sql_parser.o
-rw-r--r--. 1 root root 25607 Oct 2 2015 sql_parser.y
-rwxr-xr-x. 1 root root 63657 Jan 17 16:59 stream_parser
-rw-r--r--. 1 root root 23004 Oct 2 2015 stream_parser.c
-rw-r--r--. 1 root root 114944 Jan 17 16:59 stream_parser.o
-rw-r--r--. 1 root root 14764 Oct 2 2015 sys_parser.c
-rw-r--r--. 1 root root 2237 Oct 2 2015 tables_dict.c
-rw-r--r--. 1 root root 40176 Jan 17 16:59 tables_dict.o
-rwxr-xr-x. 1 root root 6178 Oct 2 2015 test.sh
[root@localhost undrop-for-innodb-master]# make sys_parser
/mysqlsoft/mysql/bin/mysql_config
cc -o sys_parser sys_parser.c `mysql_config --cflags` `mysql_config --libs`
5:验证
编译完成后在undrop-for-innodb 目录下生成stream_parser 和c_parser, sys_parser 文件。
[root@localhost undrop-for-innodb-master]# ll total 2928 -rw-r--r--. 1 root root 6271 Oct 2 2015 check_data.c -rw-r--r--. 1 root root 66088 Jan 17 16:59 check_data.o -rwxr-xr-x. 1 root root 725572 Jan 17 16:59 c_parser -rw-r--r--. 1 root root 28689 Oct 2 2015 c_parser.c -rw-r--r--. 1 root root 1038536 Jan 17 16:59 c_parser.o drwxr-xr-x. 2 root root 92 Oct 2 2015 dictionary -rw-r--r--. 1 root root 1978 Oct 2 2015 fetch_data.sh drwxr-xr-x. 2 root root 4096 Oct 2 2015 include -rw-r--r--. 1 root root 8936 Oct 2 2015 innochecksum.c -rwxr-xr-x. 1 root root 36399 Jan 17 16:59 innochecksum_changer -rw-r--r--. 1 root root 134778 Jan 17 16:59 lex.yy.c -rw-r--r--. 1 root root 18047 Oct 2 2015 LICENSE -rw-r--r--. 1 root root 1816 Oct 2 2015 Makefile -rw-r--r--. 1 root root 16585 Oct 2 2015 print_data.c -rw-r--r--. 1 root root 127136 Jan 17 16:59 print_data.o -rw-r--r--. 1 root root 3467 Oct 2 2015 README.md -rwxr-xr-x. 1 root root 1536 Oct 2 2015 recover_dictionary.sh drwxr-xr-x. 2 root root 4096 Oct 2 2015 sakila -rw-r--r--. 1 root root 102053 Jan 17 16:59 sql_parser.c -rw-r--r--. 1 root root 7244 Oct 2 2015 sql_parser.l -rw-r--r--. 1 root root 287296 Jan 17 16:59 sql_parser.o -rw-r--r--. 1 root root 25607 Oct 2 2015 sql_parser.y -rwxr-xr-x. 1 root root 63657 Jan 17 16:59 stream_parser -rw-r--r--. 1 root root 23004 Oct 2 2015 stream_parser.c -rw-r--r--. 1 root root 114944 Jan 17 16:59 stream_parser.o -rwxr-xr-x. 1 root root 20650 Jan 17 17:05 sys_parser -rw-r--r--. 1 root root 14764 Oct 2 2015 sys_parser.c -rw-r--r--. 1 root root 2237 Oct 2 2015 tables_dict.c -rw-r--r--. 1 root root 40176 Jan 17 16:59 tables_dict.o -rwxr-xr-x. 1 root root 6178 Oct 2 2015 test.sh
二.执行drop table恢复操作
1：删除表
mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> desc articles; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | title | varchar(200) | YES | MUL | NULL | | | body | text | YES | | NULL | | +-------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> select * from articles; +----+-------------------+------------------------------------------+ | id | title | body | +----+-------------------+------------------------------------------+ | 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... | | 5 | MySQL vs. YourSQL | In the following database comparison ... | | 6 | MySQL Security | When configured properly, MySQL ... | +----+-------------------+------------------------------------------+ 3 rows in set (0.00 sec) [root@localhost test]# ll total 516 -rw-r-----. 1 mysql mysql 8618 Jan 18 23:44 articles.frm -rw-r-----. 1 mysql mysql 98304 Jan 18 23:45 articles.ibd -rw-r-----. 1 mysql mysql 67 Mar 15 2022 db.opt -rw-r-----. 1 mysql mysql 8554 Mar 16 2022 t2.frm -rw-r-----. 1 mysql mysql 98304 Mar 16 2022 t2#P#p0.ibd -rw-r-----. 1 mysql mysql 98304 Mar 16 2022 t2#P#p1.ibd -rw-r-----. 1 mysql mysql 375 Mar 16 2022 t2#P#p2.cfg -rw-r-----. 1 mysql mysql 98304 Mar 16 2022 t2#P#p2.ibd -rw-r-----. 1 mysql mysql 375 Mar 16 2022 t2#P#p3.cfg -rw-r-----. 1 mysql mysql 98304 Mar 16 2022 t2#P#p3.ibd mysql> drop table articles; Query OK, 0 rows affected (0.35 sec) [root@localhost test]# ll total 408 -rw-r-----. 1 mysql mysql 67 Mar 15 2022 db.opt -rw-r-----. 1 mysql mysql 8554 Mar 16 2022 t2.frm -rw-r-----. 1 mysql mysql 98304 Mar 16 2022 t2#P#p0.ibd -rw-r-----. 1 mysql mysql 98304 Mar 16 2022 t2#P#p1.ibd -rw-r-----. 1 mysql mysql 375 Mar 16 2022 t2#P#p2.cfg -rw-r-----. 1 mysql mysql 98304 Mar 16 2022 t2#P#p2.ibd -rw-r-----. 1 mysql mysql 375 Mar 16 2022 t2#P#p3.cfg -rw-r-----. 1 mysql mysql 98304 Mar 16 2022 t2#P#p3.ibd
2：关闭数据库
立刻关闭数据库，防止磁盘内的页被覆盖，影响数据的恢复成功率
mysql> shutdown; Query OK, 0 rows affected (0.00 sec) [root@localhost undrop-for-innodb-master]# ps -ef | grep mysqld root 23505 3805 0 15:42 pts/3 00:00:00 grep --color=auto mysqld
3：确认硬盘分区
[root@localhost undrop-for-innodb-master]# more /mysqlsoft/mysql/my.cnf | grep datadir datadir=/mysqldata/mysql [root@localhost undrop-for-innodb-master]# df -h /mysqldata/mysql Filesystem Size Used Avail Use% Mounted on /dev/mapper/ol-root 72G 64G 8.0G 89% /
4：扫描硬盘分区
由于innodb_file_per_table=ON，一旦数据库drop 了某个表，该表对应的ibd 文件也会从磁盘中被删除，因此无法从ibdata 文件中恢复数据，只能从磁盘中进行恢复，因此需要扫描整个磁盘分区。
[root@localhost undrop-for-innodb-master]# ./stream_parser -f /dev/mapper/ol-root -t 72G Opening file: /dev/mapper/ol-root File information: Opening file: /dev/mapper/ol-root File information: Opening file: /dev/mapper/ol-root File information: ID of device containing file: 5 ID of device containing file: 5 ID of device containing file: 5 inode number: 10610 inode number: 10610 inode number: 10610 protection: 60660 protection: 60660 protection: 60660 (block device) (block device) (block device) number of hard links: 1 number of hard links: 1 number of hard links: 1 user ID of owner: 0 user ID of owner: 0 user ID of owner: 0 group ID of owner: 6 group ID of owner: 6 group ID of owner: 6 device ID (if special file): 64768 device ID (if special file): 64768 device ID (if special file): 64768 blocksize for filesystem I/O: 4096 blocksize for filesystem I/O: 4096 blocksize for filesystem I/O: 4096 number of blocks allocated: 0 number of blocks allocated: 0 number of blocks allocated: 0 time of last access: 1661331760 Wed Aug 24 17:02:40 2022 time of last access: 1661331760 Wed Aug 24 17:02:40 2022 time of last access: 1661331760 Wed Aug 24 17:02:40 2022 time of last modification: 1661337281 Wed Aug 24 18:34:41 2022 time of last modification: 1661337281 Wed Aug 24 18:34:41 2022 time of last modification: 1661337281 Wed Aug 24 18:34:41 2022 time of last status change: 1661337281 Wed Aug 24 18:34:41 2022 time of last status change: 1661337281 Wed Aug 24 18:34:41 2022 time of last status change: 1661337281 Wed Aug 24 18:34:41 2022 total size, in bytes: 0 (0.000 exp(+0)) total size, in bytes: 0 (0.000 exp(+0)) total size, in bytes: 0 (0.000 exp(+0)) Size to process: 77309411328 (72.000 GiB) Size to process: 77309411328 (72.000 GiB) Size to process: 77309411328 (72.000 GiB) Opening file: /dev/mapper/ol-root File information: ID of device containing file: 5 inode number: 10610 protection: 60660 (block device) number of hard links: 1 user ID of owner: 0 group ID of owner: 6 device ID (if special file): 64768 blocksize for filesystem I/O: 4096 number of blocks allocated: 0 time of last access: 1661331760 Wed Aug 24 17:02:40 2022 time of last modification: 1661337281 Wed Aug 24 18:34:41 2022 time of last status change: 1661337281 Wed Aug 24 18:34:41 2022 total size, in bytes: 0 (0.000 exp(+0)) Size to process: 77309411328 (72.000 GiB) Worker(0): 1.08% done. 2024-01-18 23:58:28 ETA(in 00:07:55). Processing speed: 38.325 MiB/sec Worker(2): 1.08% done. 2024-01-18 23:58:28 ETA(in 00:07:55). Processing speed: 38.328 MiB/sec Worker(3): 1.08% done. 2024-01-19 00:00:04 ETA(in 00:09:30). Processing speed: 31.938 MiB/sec Worker(1): 1.08% done. 2024-01-19 00:00:04 ETA(in 00:09:30). Processing speed: 31.938 MiB/sec Worker(0): 2.12% done. 2024-01-18 23:58:28 ETA(in 00:07:50). Processing speed: 38.325 MiB/sec Worker(2): 2.12% done. 2024-01-18 23:58:28 ETA(in 00:07:50). Processing speed: 38.325 MiB/sec Worker(1): 2.12% done. 2024-01-18 23:56:54 ETA(in 00:06:16). Processing speed: 47.906 MiB/sec Worker(3): 2.12% done. 2024-01-18 23:58:29 ETA(in 00:07:50). Processing speed: 38.325 MiB/sec Worker(2): 3.16% done. 2024-01-19 00:00:02 ETA(in 00:09:18). Processing speed: 31.938 MiB/sec Worker(1): 3.16% done. 2024-01-19 00:00:02 ETA(in 00:09:18). Processing speed: 31.938 MiB/sec Worker(0): 3.16% done. 2024-01-19 00:00:02 ETA(in 00:09:18). Processing speed: 31.938 MiB/sec Worker(3): 3.16% done. 2024-01-19 00:00:03 ETA(in 00:09:18). Processing speed: 31.938 MiB/sec Worker(2): 4.20% done. 2024-01-18 23:56:56 ETA(in 00:06:08). Processing speed: 47.906 MiB/sec Worker(1): 4.20% done. 2024-01-18 23:56:56 ETA(in 00:06:08). Processing speed: 47.910 MiB/sec Worker(0): 4.20% done. 2024-01-19 00:00:02 ETA(in 00:09:12). Processing speed: 31.938 MiB/sec Worker(3): 4.20% done. 2024-01-18 23:58:30 ETA(in 00:07:40). Processing speed: 38.325 MiB/sec Worker(2): 5.24% done. 2024-01-18 23:56:56 ETA(in 00:06:04). Processing speed: 47.907 MiB/sec Worker(1): 5.24% done. 2024-01-18 23:58:28 ETA(in 00:07:35). Processing speed: 38.326 MiB/sec Worker(0): 5.24% done. 2024-01-18 23:58:30 ETA(in 00:07:35). Processing speed: 38.325 MiB/sec Worker(3): 5.24% done. 2024-01-19 00:00:02 ETA(in 00:09:06). Processing speed: 31.938 MiB/sec Worker(2): 6.28% done. 2024-01-18 23:58:27 ETA(in 00:07:30). Processing speed: 38.326 MiB/sec Worker(1): 6.28% done. 2024-01-18 23:58:28 ETA(in 00:07:30). Processing speed: 38.325 MiB/sec Worker(0): 6.28% done. 2024-01-18 23:58:30 ETA(in 00:07:30). Processing speed: 38.325 MiB/sec Worker(3): 6.28% done. 2024-01-18 23:58:31 ETA(in 00:07:30). Processing speed: 38.325 MiB/sec Worker(2): 7.32% done. 2024-01-18 23:56:57 ETA(in 00:05:56). Processing speed: 47.906 MiB/sec Worker(1): 7.32% done. 2024-01-18 23:56:58 ETA(in 00:05:56). Processing speed: 47.911 MiB/sec Worker(2): 8.36% done. 2024-01-18 23:56:57 ETA(in 00:05:52). Processing speed: 47.941 MiB/sec Worker(3): 7.32% done. 2024-01-18 23:57:01 ETA(in 00:05:56). Processing speed: 47.906 MiB/sec Worker(0): 7.32% done. 2024-01-18 23:58:30 ETA(in 00:07:25). Processing speed: 38.331 MiB/sec Worker(1): 8.36% done. 2024-01-18 23:58:27 ETA(in 00:07:20). Processing speed: 38.327 MiB/sec Worker(2): 9.40% done. 2024-01-18 23:58:25 ETA(in 00:07:15). Processing speed: 38.334 MiB/sec Worker(3): 8.36% done. 2024-01-18 23:59:59 ETA(in 00:08:48). Processing speed: 31.938 MiB/sec Worker(0): 8.36% done. 2024-01-18 23:59:59 ETA(in 00:08:48). Processing speed: 31.945 MiB/sec Worker(1): 9.40% done. 2024-01-18 23:58:27 ETA(in 00:07:15). Processing speed: 38.328 MiB/sec Worker(2): 10.44% done. 2024-01-18 23:58:25 ETA(in 00:07:10). Processing speed: 38.328 MiB/sec Worker(3): 9.40% done. 2024-01-18 23:58:31 ETA(in 00:07:15). Processing speed: 38.325 MiB/sec Worker(0): 9.40% done. 2024-01-18 23:58:31 ETA(in 00:07:15). Processing speed: 38.356 MiB/sec Worker(1): 10.44% done. 2024-01-18 23:58:27 ETA(in 00:07:10). Processing speed: 38.325 MiB/sec Worker(2): 11.48% done. 2024-01-18 23:56:59 ETA(in 00:05:40). Processing speed: 47.910 MiB/sec Worker(0): 10.44% done. 2024-01-18 23:57:04 ETA(in 00:05:44). Processing speed: 47.962 MiB/sec Worker(3): 10.44% done. 2024-01-18 23:57:04 ETA(in 00:05:44). Processing speed: 47.910 MiB/sec Worker(1): 11.48% done. 2024-01-18 23:57:01 ETA(in 00:05:40). Processing speed: 47.914 MiB/sec Worker(2): 12.52% done. 2024-01-18 23:55:34 ETA(in 00:04:12). Processing speed: 63.970 MiB/sec Worker(0): 11.48% done. 2024-01-18 23:55:38 ETA(in 00:04:15). Processing speed: 63.876 MiB/sec Worker(3): 11.48% done. 2024-01-18 23:57:04 ETA(in 00:05:40). Processing speed: 47.916 MiB/sec Worker(2): 13.56% done. 2024-01-18 23:56:58 ETA(in 00:05:32). Processing speed: 47.930 MiB/sec Worker(1): 12.52% done. 2024-01-18 23:58:26 ETA(in 00:07:00). Processing speed: 38.325 MiB/sec Worker(0): 12.52% done. 2024-01-18 23:58:28 ETA(in 00:07:00). Processing speed: 38.331 MiB/sec Worker(3): 12.52% done. 2024-01-18 23:59:54 ETA(in 00:08:24). Processing speed: 31.943 MiB/sec Worker(1): 13.56% done. 2024-01-18 23:57:02 ETA(in 00:05:32). Processing speed: 47.906 MiB/sec Worker(2): 14.60% done. 2024-01-18 23:56:58 ETA(in 00:05:28). Processing speed: 47.938 MiB/sec Worker(2): 15.64% done. 2024-01-18 23:56:58 ETA(in 00:05:24). Processing speed: 47.906 MiB/sec Worker(1): 14.60% done. 2024-01-18 23:57:02 ETA(in 00:05:28). Processing speed: 47.906 MiB/sec Worker(3): 13.56% done. 2024-01-18 23:58:30 ETA(in 00:06:55). Processing speed: 38.325 MiB/sec Worker(0): 13.56% done. 2024-01-19 00:02:41 ETA(in 00:11:05). Processing speed: 23.955 MiB/sec Worker(1): 15.64% done. 2024-01-18 23:58:24 ETA(in 00:06:45). Processing speed: 38.325 MiB/sec Worker(2): 16.68% done. 2024-01-18 23:58:19 ETA(in 00:06:40). Processing speed: 38.325 MiB/sec Worker(3): 14.60% done. 2024-01-18 23:57:07 ETA(in 00:05:28). Processing speed: 47.906 MiB/sec Worker(0): 14.60% done. 2024-01-18 23:58:31 ETA(in 00:06:50). Processing speed: 38.325 MiB/sec Worker(2): 17.72% done. 2024-01-18 23:55:39 ETA(in 00:03:57). Processing speed: 63.880 MiB/sec Worker(1): 16.68% done. 2024-01-18 23:57:03 ETA(in 00:05:20). Processing speed: 47.912 MiB/sec Worker(3): 15.64% done. 2024-01-18 23:59:51 ETA(in 00:08:06). Processing speed: 31.940 MiB/sec Worker(0): 15.64% done. 2024-01-18 23:57:09 ETA(in 00:05:24). Processing speed: 47.914 MiB/sec Worker(2): 18.76% done. 2024-01-18 23:56:58 ETA(in 00:05:12). Processing speed: 47.906 MiB/sec Worker(1): 17.72% done. 2024-01-18 23:55:43 ETA(in 00:03:57). Processing speed: 63.909 MiB/sec Worker(3): 16.68% done. 2024-01-18 23:58:30 ETA(in 00:06:40). Processing speed: 38.335 MiB/sec Worker(2): 19.80% done. 2024-01-18 23:56:58 ETA(in 00:05:08). Processing speed: 47.906 MiB/sec Worker(1): 18.76% done. 2024-01-18 23:58:21 ETA(in 00:06:30). Processing speed: 38.325 MiB/sec Worker(0): 16.68% done. 2024-01-18 23:59:51 ETA(in 00:08:00). Processing speed: 31.940 MiB/sec Worker(2): 20.84% done. 2024-01-18 23:56:58 ETA(in 00:05:04). Processing speed: 47.953 MiB/sec Worker(3): 17.72% done. 2024-01-18 23:57:10 ETA(in 00:05:16). Processing speed: 47.910 MiB/sec Worker(1): 19.80% done. 2024-01-18 23:57:03 ETA(in 00:05:08). Processing speed: 47.906 MiB/sec Worker(0): 17.72% done. 2024-01-18 23:58:31 ETA(in 00:06:35). Processing speed: 38.325 MiB/sec Worker(2): 21.88% done. 2024-01-18 23:56:58 ETA(in 00:05:00). Processing speed: 47.906 MiB/sec Worker(3): 18.76% done. 2024-01-18 23:57:10 ETA(in 00:05:12). Processing speed: 47.906 MiB/sec Worker(1): 20.84% done. 2024-01-18 23:57:03 ETA(in 00:05:04). Processing speed: 47.906 MiB/sec Worker(0): 18.76% done. 2024-01-18 23:58:31 ETA(in 00:06:30). Processing speed: 38.325 MiB/sec Worker(2): 22.92% done. 2024-01-18 23:56:58 ETA(in 00:04:56). Processing speed: 47.906 MiB/sec Worker(1): 21.88% done. 2024-01-18 23:57:03 ETA(in 00:05:00). Processing speed: 47.906 MiB/sec Worker(3): 19.80% done. 2024-01-18 23:58:28 ETA(in 00:06:25). Processing speed: 38.325 MiB/sec Worker(2): 23.96% done. 2024-01-18 23:56:58 ETA(in 00:04:52). Processing speed: 47.943 MiB/sec Worker(0): 19.80% done. 2024-01-18 23:58:31 ETA(in 00:06:25). Processing speed: 38.331 MiB/sec Worker(1): 22.92% done. 2024-01-18 23:57:03 ETA(in 00:04:56). Processing speed: 47.906 MiB/sec Worker(3): 20.84% done. 2024-01-18 23:58:28 ETA(in 00:06:20). Processing speed: 38.325 MiB/sec Worker(2): 25.00% done. 2024-01-18 23:55:45 ETA(in 00:03:36). Processing speed: 63.948 MiB/sec Worker(1): 23.96% done. 2024-01-18 23:57:03 ETA(in 00:04:52). Processing speed: 47.914 MiB/sec Worker(0): 20.84% done. 2024-01-18 23:59:48 ETA(in 00:07:36). Processing speed: 31.938 MiB/sec Worker(3): 21.88% done. 2024-01-18 23:58:28 ETA(in 00:06:15). Processing speed: 38.329 MiB/sec Worker(2): 26.04% done. 2024-01-18 23:58:09 ETA(in 00:05:55). Processing speed: 38.325 MiB/sec Worker(1): 25.00% done. 2024-01-18 23:57:03 ETA(in 00:04:48). Processing speed: 47.945 MiB/sec Worker(0): 21.88% done. 2024-01-18 23:58:32 ETA(in 00:06:15). Processing speed: 38.325 MiB/sec Worker(3): 22.92% done. 2024-01-18 23:58:28 ETA(in 00:06:10). Processing speed: 38.325 MiB/sec Worker(2): 27.08% done. 2024-01-18 23:56:58 ETA(in 00:04:40). Processing speed: 47.914 MiB/sec Worker(1): 26.04% done. 2024-01-18 23:57:03 ETA(in 00:04:44). Processing speed: 47.906 MiB/sec Worker(0): 22.92% done. 2024-01-18 23:57:17 ETA(in 00:04:56). Processing speed: 47.910 MiB/sec Worker(3): 23.96% done. 2024-01-18 23:58:28 ETA(in 00:06:05). Processing speed: 38.325 MiB/sec Worker(2): 28.12% done. 2024-01-18 23:58:08 ETA(in 00:05:45). Processing speed: 38.332 MiB/sec Worker(1): 27.08% done. 2024-01-18 23:57:03 ETA(in 00:04:40). Processing speed: 47.906 MiB/sec Worker(0): 23.96% done. 2024-01-18 23:58:31 ETA(in 00:06:05). Processing speed: 38.325 MiB/sec Worker(3): 25.00% done. 2024-01-18 23:57:15 ETA(in 00:04:48). Processing speed: 47.910 MiB/sec Worker(2): 29.16% done. 2024-01-18 23:56:59 ETA(in 00:04:32). Processing speed: 47.906 MiB/sec Worker(1): 28.12% done. 2024-01-18 23:58:13 ETA(in 00:05:45). Processing speed: 38.325 MiB/sec Worker(0): 25.00% done. 2024-01-18 23:57:18 ETA(in 00:04:48). Processing speed: 47.906 MiB/sec Worker(3): 26.04% done. 2024-01-18 23:58:27 ETA(in 00:05:55). Processing speed: 38.325 MiB/sec Worker(1): 29.16% done. 2024-01-18 23:57:04 ETA(in 00:04:32). Processing speed: 47.906 MiB/sec Worker(2): 30.20% done. 2024-01-18 23:59:15 ETA(in 00:06:42). Processing speed: 31.938 MiB/sec Worker(0): 26.04% done. 2024-01-18 23:58:30 ETA(in 00:05:55). Processing speed: 38.333 MiB/sec Worker(3): 27.07% done. 2024-01-18 23:57:16 ETA(in 00:04:40). Processing speed: 47.907 MiB/sec Worker(1): 30.20% done. 2024-01-18 23:58:12 ETA(in 00:05:35). Processing speed: 38.326 MiB/sec Worker(2): 31.24% done. 2024-01-18 23:57:01 ETA(in 00:04:24). Processing speed: 47.906 MiB/sec Worker(0): 27.08% done. 2024-01-18 23:57:19 ETA(in 00:04:40). Processing speed: 47.953 MiB/sec Worker(3): 28.11% done. 2024-01-18 23:58:26 ETA(in 00:05:45). Processing speed: 38.325 MiB/sec Worker(1): 31.23% done. 2024-01-18 23:58:12 ETA(in 00:05:30). Processing speed: 38.325 MiB/sec Worker(2): 32.28% done. 2024-01-18 23:59:13 ETA(in 00:06:30). Processing speed: 31.938 MiB/sec Worker(0): 28.12% done. 2024-01-18 23:58:29 ETA(in 00:05:45). Processing speed: 38.325 MiB/sec Worker(3): 29.16% done. 2024-01-18 23:57:17 ETA(in 00:04:32). Processing speed: 47.965 MiB/sec Worker(1): 32.27% done. 2024-01-18 23:57:06 ETA(in 00:04:20). Processing speed: 47.906 MiB/sec Worker(2): 33.32% done. 2024-01-18 23:57:03 ETA(in 00:04:16). Processing speed: 47.911 MiB/sec Worker(3): 30.20% done. 2024-01-18 23:57:17 ETA(in 00:04:28). Processing speed: 47.922 MiB/sec Worker(0): 29.16% done. 2024-01-18 23:58:29 ETA(in 00:05:40). Processing speed: 38.325 MiB/sec Worker(1): 33.31% done. 2024-01-18 23:57:06 ETA(in 00:04:16). Processing speed: 47.906 MiB/sec Worker(2): 34.36% done. 2024-01-18 23:55:59 ETA(in 00:03:09). Processing speed: 63.932 MiB/sec Worker(3): 31.24% done. 2024-01-18 23:57:17 ETA(in 00:04:24). Processing speed: 47.906 MiB/sec Worker(2): 35.40% done. 2024-01-18 23:57:02 ETA(in 00:04:08). Processing speed: 47.922 MiB/sec Worker(1): 34.35% done. 2024-01-18 23:57:06 ETA(in 00:04:12). Processing speed: 47.906 MiB/sec Worker(0): 30.20% done. 2024-01-18 23:59:37 ETA(in 00:06:42). Processing speed: 31.942 MiB/sec Worker(3): 32.27% done. 2024-01-18 23:59:29 ETA(in 00:06:30). Processing speed: 31.938 MiB/sec Worker(2): 36.44% done. 2024-01-18 23:58:04 ETA(in 00:05:05). Processing speed: 38.325 MiB/sec Worker(1): 35.39% done. 2024-01-18 23:58:09 ETA(in 00:05:10). Processing speed: 38.328 MiB/sec Worker(0): 31.24% done. 2024-01-19 00:00:44 ETA(in 00:07:42). Processing speed: 27.377 MiB/sec Worker(3): 33.31% done. 2024-01-18 23:57:19 ETA(in 00:04:16). Processing speed: 47.906 MiB/sec Worker(1): 36.43% done. 2024-01-18 23:57:07 ETA(in 00:04:04). Processing speed: 47.906 MiB/sec Worker(2): 37.48% done. 2024-01-18 23:58:04 ETA(in 00:05:00). Processing speed: 38.325 MiB/sec Worker(1): 37.47% done. 2024-01-18 23:57:07 ETA(in 00:04:00). Processing speed: 47.945 MiB/sec Worker(2): 38.52% done. 2024-01-18 23:57:04 ETA(in 00:03:56). Processing speed: 47.906 MiB/sec Worker(3): 34.35% done. 2024-01-18 23:58:23 ETA(in 00:05:15). Processing speed: 38.325 MiB/sec Worker(0): 32.28% done. 2024-01-18 23:59:38 ETA(in 00:06:30). Processing speed: 31.938 MiB/sec Worker(1): 38.51% done. 2024-01-18 23:56:07 ETA(in 00:02:57). Processing speed: 63.979 MiB/sec Worker(3): 35.40% done. 2024-01-18 23:56:17 ETA(in 00:03:06). Processing speed: 64.000 MiB/sec Worker(2): 39.56% done. 2024-01-18 23:56:05 ETA(in 00:02:54). Processing speed: 63.875 MiB/sec Worker(1): 39.56% done. 2024-01-18 23:56:07 ETA(in 00:02:54). Processing speed: 63.932 MiB/sec Worker(0): 33.32% done. 2024-01-18 23:58:33 ETA(in 00:05:20). Processing speed: 38.325 MiB/sec Worker(3): 36.44% done. 2024-01-18 23:55:15 ETA(in 00:02:02). Processing speed: 96.000 MiB/sec Worker(2): 40.60% done. 2024-01-18 23:56:05 ETA(in 00:02:51). Processing speed: 63.875 MiB/sec Worker(3): 37.48% done. 2024-01-18 23:55:15 ETA(in 00:02:00). Processing speed: 96.000 MiB/sec Worker(1): 40.60% done. 2024-01-18 23:55:09 ETA(in 00:01:54). Processing speed: 96.000 MiB/sec Worker(0): 34.36% done. 2024-01-18 23:57:29 ETA(in 00:04:12). Processing speed: 47.906 MiB/sec Worker(3): 38.52% done. 2024-01-18 23:55:15 ETA(in 00:01:58). Processing speed: 96.000 MiB/sec Worker(1): 41.64% done. 2024-01-18 23:55:09 ETA(in 00:01:52). Processing speed: 96.000 MiB/sec Worker(2): 41.64% done. 2024-01-18 23:56:05 ETA(in 00:02:48). Processing speed: 63.875 MiB/sec Worker(3): 39.56% done. 2024-01-18 23:55:15 ETA(in 00:01:56). Processing speed: 96.000 MiB/sec Worker(1): 42.68% done. 2024-01-18 23:56:05 ETA(in 00:02:45). Processing speed: 64.000 MiB/sec Worker(0): 35.40% done. 2024-01-18 23:57:29 ETA(in 00:04:08). Processing speed: 47.906 MiB/sec Worker(3): 40.60% done. 2024-01-18 23:55:15 ETA(in 00:01:54). Processing speed: 96.000 MiB/sec Worker(2): 42.68% done. 2024-01-18 23:57:57 ETA(in 00:04:35). Processing speed: 38.325 MiB/sec Worker(1): 43.72% done. 2024-01-18 23:55:10 ETA(in 00:01:48). Processing speed: 96.000 MiB/sec Worker(3): 41.65% done. 2024-01-18 23:54:18 ETA(in 00:00:56). Processing speed: 192.000 MiB/sec Worker(1): 44.76% done. 2024-01-18 23:54:16 ETA(in 00:00:53). Processing speed: 192.000 MiB/sec Worker(3): 42.69% done. 2024-01-18 23:54:18 ETA(in 00:00:55). Processing speed: 192.000 MiB/sec Worker(0): 36.44% done. 2024-01-18 23:55:25 ETA(in 00:02:02). Processing speed: 95.898 MiB/sec Worker(1): 46.37% done. 2024-01-18 23:53:57 ETA(in 00:00:33). Processing speed: 296.000 MiB/sec Worker(3): 43.73% done. 2024-01-18 23:54:18 ETA(in 00:00:54). Processing speed: 192.000 MiB/sec Worker(0): 37.48% done. 2024-01-18 23:54:24 ETA(in 00:01:00). Processing speed: 191.984 MiB/sec Worker(2): 43.72% done. 2024-01-18 23:55:12 ETA(in 00:01:48). Processing speed: 95.813 MiB/sec Worker(1): 48.32% done. 2024-01-18 23:53:51 ETA(in 00:00:26). Processing speed: 360.000 MiB/sec Worker(3): 45.03% done. 2024-01-18 23:54:07 ETA(in 00:00:42). Processing speed: 240.000 MiB/sec Worker(0): 38.52% done. 2024-01-18 23:54:24 ETA(in 00:00:59). Processing speed: 192.000 MiB/sec Worker(0): 39.69% done. 2024-01-18 23:54:17 ETA(in 00:00:51). Processing speed: 216.000 MiB/sec Worker(1): 49.54% done. 2024-01-18 23:54:07 ETA(in 00:00:41). Processing speed: 224.000 MiB/sec Worker(3): 46.29% done. 2024-01-18 23:54:08 ETA(in 00:00:42). Processing speed: 232.000 MiB/sec Worker(2): 44.76% done. 2024-01-18 23:55:12 ETA(in 00:01:46). Processing speed: 95.813 MiB/sec Worker(3): 47.33% done. 2024-01-18 23:54:17 ETA(in 00:00:50). Processing speed: 192.000 MiB/sec Worker(0): 40.73% done. 2024-01-18 23:54:23 ETA(in 00:00:56). Processing speed: 192.000 MiB/sec Worker(1): 50.58% done. 2024-01-18 23:54:14 ETA(in 00:00:47). Processing speed: 192.000 MiB/sec Worker(2): 45.80% done. 2024-01-18 23:55:12 ETA(in 00:01:44). Processing speed: 95.813 MiB/sec Worker(3): 48.37% done. 2024-01-18 23:54:17 ETA(in 00:00:49). Processing speed: 192.000 MiB/sec Worker(0): 41.77% done. 2024-01-18 23:54:23 ETA(in 00:00:55). Processing speed: 192.000 MiB/sec Worker(1): 51.62% done. 2024-01-18 23:54:14 ETA(in 00:00:46). Processing speed: 192.000 MiB/sec Worker(3): 49.41% done. 2024-01-18 23:54:17 ETA(in 00:00:48). Processing speed: 192.000 MiB/sec Worker(0): 42.82% done. 2024-01-18 23:55:19 ETA(in 00:01:49). Processing speed: 96.000 MiB/sec Worker(1): 52.66% done. 2024-01-18 23:55:00 ETA(in 00:01:30). Processing speed: 96.000 MiB/sec Worker(2): 46.84% done. 2024-01-18 23:55:12 ETA(in 00:01:42). Processing speed: 95.813 MiB/sec Worker(3): 50.46% done. 2024-01-18 23:54:17 ETA(in 00:00:47). Processing speed: 192.000 MiB/sec Worker(0): 43.86% done. 2024-01-18 23:54:24 ETA(in 00:00:53). Processing speed: 192.000 MiB/sec Worker(1): 53.70% done. 2024-01-18 23:54:15 ETA(in 00:00:44). Processing speed: 192.000 MiB/sec Worker(3): 51.50% done. 2024-01-18 23:54:17 ETA(in 00:00:46). Processing speed: 192.000 MiB/sec Worker(1): 54.75% done. 2024-01-18 23:54:15 ETA(in 00:00:43). Processing speed: 192.000 MiB/sec Worker(0): 44.90% done. 2024-01-18 23:54:24 ETA(in 00:00:52). Processing speed: 192.000 MiB/sec Worker(3): 52.54% done. 2024-01-18 23:54:17 ETA(in 00:00:45). Processing speed: 192.000 MiB/sec Worker(2): 47.88% done. 2024-01-18 23:55:12 ETA(in 00:01:40). Processing speed: 95.813 MiB/sec Worker(1): 55.79% done. 2024-01-18 23:54:15 ETA(in 00:00:42). Processing speed: 192.000 MiB/sec Worker(3): 53.58% done. 2024-01-18 23:55:03 ETA(in 00:01:29). Processing speed: 96.000 MiB/sec Worker(0): 45.94% done. 2024-01-18 23:55:17 ETA(in 00:01:43). Processing speed: 96.000 MiB/sec Worker(2): 48.92% done. 2024-01-18 23:56:02 ETA(in 00:02:27). Processing speed: 63.875 MiB/sec Worker(1): 56.83% done. 2024-01-18 23:54:57 ETA(in 00:01:22). Processing speed: 96.000 MiB/sec Worker(3): 54.62% done. 2024-01-18 23:54:18 ETA(in 00:00:43). Processing speed: 192.000 MiB/sec Worker(0): 46.98% done. 2024-01-18 23:54:25 ETA(in 00:00:50). Processing speed: 192.000 MiB/sec Worker(1): 57.91% done. 2024-01-18 23:54:14 ETA(in 00:00:38). Processing speed: 200.000 MiB/sec Worker(3): 55.71% done. 2024-01-18 23:54:16 ETA(in 00:00:40). Processing speed: 200.000 MiB/sec Worker(2): 49.96% done. 2024-01-18 23:54:24 ETA(in 00:00:48). Processing speed: 191.937 MiB/sec Worker(0): 48.02% done. 2024-01-18 23:54:25 ETA(in 00:00:49). Processing speed: 192.000 MiB/sec Worker(1): 59.52% done. 2024-01-18 23:54:02 ETA(in 00:00:25). Processing speed: 296.000 MiB/sec Worker(3): 56.79% done. 2024-01-18 23:54:16 ETA(in 00:00:39). Processing speed: 200.000 MiB/sec Worker(2): 51.43% done. 2024-01-18 23:54:09 ETA(in 00:00:32). Processing speed: 272.000 MiB/sec Worker(0): 49.07% done. 2024-01-18 23:54:25 ETA(in 00:00:48). Processing speed: 192.000 MiB/sec Worker(1): 61.13% done. 2024-01-18 23:54:02 ETA(in 00:00:24). Processing speed: 296.000 MiB/sec Worker(2): 52.95% done. 2024-01-18 23:54:08 ETA(in 00:00:30). Processing speed: 280.000 MiB/sec Worker(3): 57.92% done. 2024-01-18 23:54:15 ETA(in 00:00:37). Processing speed: 208.000 MiB/sec Worker(0): 50.24% done. 2024-01-18 23:54:20 ETA(in 00:00:42). Processing speed: 216.000 MiB/sec Worker(2): 53.99% done. 2024-01-18 23:54:23 ETA(in 00:00:44). Processing speed: 192.000 MiB/sec Worker(1): 62.17% done. 2024-01-18 23:54:15 ETA(in 00:00:36). Processing speed: 192.000 MiB/sec Worker(3): 58.96% done. 2024-01-18 23:54:18 ETA(in 00:00:39). Processing speed: 192.000 MiB/sec Worker(0): 51.28% done. 2024-01-18 23:54:25 ETA(in 00:00:46). Processing speed: 192.000 MiB/sec Worker(2): 55.03% done. 2024-01-18 23:54:23 ETA(in 00:00:43). Processing speed: 192.000 MiB/sec Worker(1): 63.21% done. 2024-01-18 23:54:15 ETA(in 00:00:35). Processing speed: 192.000 MiB/sec Worker(0): 52.32% done. 2024-01-18 23:54:25 ETA(in 00:00:45). Processing speed: 192.000 MiB/sec Worker(3): 60.01% done. 2024-01-18 23:54:18 ETA(in 00:00:38). Processing speed: 192.000 MiB/sec Worker(1): 64.86% done. 2024-01-18 23:54:02 ETA(in 00:00:21). Processing speed: 304.000 MiB/sec Worker(2): 56.42% done. 2024-01-18 23:54:12 ETA(in 00:00:31). Processing speed: 256.000 MiB/sec Worker(0): 53.36% done. 2024-01-18 23:54:25 ETA(in 00:00:44). Processing speed: 192.000 MiB/sec Worker(3): 61.05% done. 2024-01-18 23:54:18 ETA(in 00:00:37). Processing speed: 192.000 MiB/sec Worker(0): 54.71% done. 2024-01-18 23:54:15 ETA(in 00:00:33). Processing speed: 248.000 MiB/sec Worker(1): 66.25% done. 2024-01-18 23:54:06 ETA(in 00:00:24). Processing speed: 256.000 MiB/sec Worker(3): 62.26% done. 2024-01-18 23:54:13 ETA(in 00:00:31). Processing speed: 224.000 MiB/sec Worker(2): 57.94% done. 2024-01-18 23:54:09 ETA(in 00:00:27). Processing speed: 280.000 MiB/sec Worker(2): 59.46% done. 2024-01-18 23:54:09 ETA(in 00:00:26). Processing speed: 280.000 MiB/sec Worker(0): 56.05% done. 2024-01-18 23:54:15 ETA(in 00:00:32). Processing speed: 248.000 MiB/sec Worker(1): 67.55% done. 2024-01-18 23:54:07 ETA(in 00:00:24). Processing speed: 240.000 MiB/sec Worker(3): 63.78% done. 2024-01-18 23:54:06 ETA(in 00:00:23). Processing speed: 280.000 MiB/sec Worker(1): 69.29% done. 2024-01-18 23:54:01 ETA(in 00:00:17). Processing speed: 320.000 MiB/sec Worker(3): 65.47% done. 2024-01-18 23:54:04 ETA(in 00:00:20). Processing speed: 312.000 MiB/sec Worker(0): 57.23% done. 2024-01-18 23:54:20 ETA(in 00:00:36). Processing speed: 216.000 MiB/sec Worker(2): 60.85% done. 2024-01-18 23:54:12 ETA(in 00:00:28). Processing speed: 256.000 MiB/sec Worker(0): 58.40% done. 2024-01-18 23:54:20 ETA(in 00:00:35). Processing speed: 216.000 MiB/sec Worker(3): 67.25% done. 2024-01-18 23:54:03 ETA(in 00:00:18). Processing speed: 328.000 MiB/sec Worker(1): 71.11% done. 2024-01-18 23:54:00 ETA(in 00:00:15). Processing speed: 336.000 MiB/sec Worker(2): 62.02% done. 2024-01-18 23:54:17 ETA(in 00:00:32). Processing speed: 216.000 MiB/sec Worker(2): 63.50% done. 2024-01-18 23:54:10 ETA(in 00:00:24). Processing speed: 272.000 MiB/sec Worker(0): 60.13% done. 2024-01-18 23:54:08 ETA(in 00:00:22). Processing speed: 320.000 MiB/sec Worker(3): 69.12% done. 2024-01-18 23:54:02 ETA(in 00:00:16). Processing speed: 344.000 MiB/sec Worker(1): 72.24% done. 2024-01-18 23:54:10 ETA(in 00:00:24). Processing speed: 208.000 MiB/sec Worker(0): 61.65% done. 2024-01-18 23:54:12 ETA(in 00:00:25). Processing speed: 280.000 MiB/sec Worker(3): 70.77% done. 2024-01-18 23:54:04 ETA(in 00:00:17). Processing speed: 304.000 MiB/sec Worker(2): 64.97% done. 2024-01-18 23:54:10 ETA(in 00:00:23). Processing speed: 272.000 MiB/sec Worker(1): 73.58% done. 2024-01-18 23:54:06 ETA(in 00:00:19). Processing speed: 248.000 MiB/sec Worker(0): 63.35% done. 2024-01-18 23:54:09 ETA(in 00:00:21). Processing speed: 312.000 MiB/sec Worker(3): 72.37% done. 2024-01-18 23:54:05 ETA(in 00:00:17). Processing speed: 296.000 MiB/sec Worker(2): 66.58% done. 2024-01-18 23:54:08 ETA(in 00:00:20). Processing speed: 296.000 MiB/sec Worker(1): 75.19% done. 2024-01-18 23:54:03 ETA(in 00:00:15). Processing speed: 296.000 MiB/sec Worker(0): 64.95% done. 2024-01-18 23:54:10 ETA(in 00:00:21). Processing speed: 296.000 MiB/sec Worker(2): 68.36% done. 2024-01-18 23:54:06 ETA(in 00:00:17). Processing speed: 328.000 MiB/sec Worker(1): 76.71% done. 2024-01-18 23:54:04 ETA(in 00:00:15). Processing speed: 280.000 MiB/sec Worker(3): 73.98% done. 2024-01-18 23:54:05 ETA(in 00:00:16). Processing speed: 296.000 MiB/sec Worker(3): 75.76% done. 2024-01-18 23:54:03 ETA(in 00:00:13). Processing speed: 328.000 MiB/sec Worker(1): 78.05% done. 2024-01-18 23:54:06 ETA(in 00:00:16). Processing speed: 248.000 MiB/sec Worker(0): 66.56% done. 2024-01-18 23:54:10 ETA(in 00:00:20). Processing speed: 296.000 MiB/sec Worker(2): 70.18% done. 2024-01-18 23:54:06 ETA(in 00:00:16). Processing speed: 336.000 MiB/sec Worker(3): 77.50% done. 2024-01-18 23:54:03 ETA(in 00:00:12). Processing speed: 320.000 MiB/sec Worker(1): 79.57% done. 2024-01-18 23:54:04 ETA(in 00:00:13). Processing speed: 280.000 MiB/sec Worker(0): 67.99% done. 2024-01-18 23:54:13 ETA(in 00:00:22). Processing speed: 264.000 MiB/sec Worker(2): 71.92% done. 2024-01-18 23:54:07 ETA(in 00:00:16). Processing speed: 320.000 MiB/sec Worker(3): 79.28% done. 2024-01-18 23:54:03 ETA(in 00:00:11). Processing speed: 328.000 MiB/sec Worker(1): 81.22% done. 2024-01-18 23:54:03 ETA(in 00:00:11). Processing speed: 304.000 MiB/sec Worker(2): 73.70% done. 2024-01-18 23:54:06 ETA(in 00:00:14). Processing speed: 328.000 MiB/sec Worker(0): 69.29% done. 2024-01-18 23:54:15 ETA(in 00:00:23). Processing speed: 240.000 MiB/sec Worker(2): 75.26% done. 2024-01-18 23:54:08 ETA(in 00:00:15). Processing speed: 288.000 MiB/sec Worker(1): 82.91% done. 2024-01-18 23:54:03 ETA(in 00:00:10). Processing speed: 312.000 MiB/sec Worker(3): 80.71% done. 2024-01-18 23:54:06 ETA(in 00:00:13). Processing speed: 264.000 MiB/sec Worker(0): 70.81% done. 2024-01-18 23:54:12 ETA(in 00:00:19). Processing speed: 280.000 MiB/sec Worker(1): 84.52% done. 2024-01-18 23:54:03 ETA(in 00:00:09). Processing speed: 296.000 MiB/sec Worker(2): 76.91% done. 2024-01-18 23:54:08 ETA(in 00:00:14). Processing speed: 304.000 MiB/sec Worker(3): 82.31% done. 2024-01-18 23:54:05 ETA(in 00:00:11). Processing speed: 296.000 MiB/sec Worker(0): 72.37% done. 2024-01-18 23:54:11 ETA(in 00:00:17). Processing speed: 288.000 MiB/sec Worker(3): 83.75% done. 2024-01-18 23:54:06 ETA(in 00:00:11). Processing speed: 264.000 MiB/sec Worker(1): 86.43% done. 2024-01-18 23:54:02 ETA(in 00:00:07). Processing speed: 352.000 MiB/sec Worker(0): 73.94% done. 2024-01-18 23:54:11 ETA(in 00:00:16). Processing speed: 288.000 MiB/sec Worker(2): 78.30% done. 2024-01-18 23:54:10 ETA(in 00:00:15). Processing speed: 256.000 MiB/sec Worker(2): 79.69% done. 2024-01-18 23:54:10 ETA(in 00:00:14). Processing speed: 256.000 MiB/sec Worker(1): 88.38% done. 2024-01-18 23:54:01 ETA(in 00:00:05). Processing speed: 360.000 MiB/sec Worker(3): 85.22% done. 2024-01-18 23:54:06 ETA(in 00:00:10). Processing speed: 272.000 MiB/sec Worker(0): 75.33% done. 2024-01-18 23:54:13 ETA(in 00:00:17). Processing speed: 256.000 MiB/sec Worker(2): 81.12% done. 2024-01-18 23:54:10 ETA(in 00:00:13). Processing speed: 264.000 MiB/sec Worker(0): 77.15% done. 2024-01-18 23:54:09 ETA(in 00:00:12). Processing speed: 336.000 MiB/sec Worker(1): 90.21% done. 2024-01-18 23:54:02 ETA(in 00:00:05). Processing speed: 336.000 MiB/sec Worker(3): 86.44% done. 2024-01-18 23:54:08 ETA(in 00:00:11). Processing speed: 224.000 MiB/sec Worker(0): 79.10% done. 2024-01-18 23:54:08 ETA(in 00:00:10). Processing speed: 360.000 MiB/sec Worker(3): 88.04% done. 2024-01-18 23:54:05 ETA(in 00:00:07). Processing speed: 296.000 MiB/sec Worker(1): 91.51% done. 2024-01-18 23:54:04 ETA(in 00:00:06). Processing speed: 240.000 MiB/sec Worker(2): 82.60% done. 2024-01-18 23:54:09 ETA(in 00:00:11). Processing speed: 272.000 MiB/sec Worker(0): 80.40% done. 2024-01-18 23:54:14 ETA(in 00:00:15). Processing speed: 240.000 MiB/sec Worker(1): 92.98% done. 2024-01-18 23:54:03 ETA(in 00:00:04). Processing speed: 272.000 MiB/sec Worker(3): 89.65% done. 2024-01-18 23:54:05 ETA(in 00:00:06). Processing speed: 296.000 MiB/sec Worker(2): 84.29% done. 2024-01-18 23:54:08 ETA(in 00:00:09). Processing speed: 312.000 MiB/sec Worker(1): 94.59% done. 2024-01-18 23:54:03 ETA(in 00:00:03). Processing speed: 296.000 MiB/sec Worker(3): 91.12% done. 2024-01-18 23:54:06 ETA(in 00:00:06). Processing speed: 272.000 MiB/sec Worker(2): 86.02% done. 2024-01-18 23:54:08 ETA(in 00:00:08). Processing speed: 320.000 MiB/sec Worker(0): 81.88% done. 2024-01-18 23:54:12 ETA(in 00:00:12). Processing speed: 272.000 MiB/sec Worker(2): 87.37% done. 2024-01-18 23:54:10 ETA(in 00:00:09). Processing speed: 248.000 MiB/sec Worker(1): 96.33% done. 2024-01-18 23:54:03 ETA(in 00:00:02). Processing speed: 320.000 MiB/sec Worker(0): 83.18% done. 2024-01-18 23:54:13 ETA(in 00:00:12). Processing speed: 240.000 MiB/sec Worker(3): 92.86% done. 2024-01-18 23:54:05 ETA(in 00:00:04). Processing speed: 320.000 MiB/sec Worker(2): 89.32% done. 2024-01-18 23:54:07 ETA(in 00:00:05). Processing speed: 360.000 MiB/sec Worker(3): 94.60% done. 2024-01-18 23:54:05 ETA(in 00:00:03). Processing speed: 320.000 MiB/sec Worker(1): 97.98% done. 2024-01-18 23:54:03 ETA(in 00:00:01). Processing speed: 304.000 MiB/sec Worker(0): 84.22% done. 2024-01-18 23:54:17 ETA(in 00:00:15). Processing speed: 192.000 MiB/sec Worker(0): 85.74% done. 2024-01-18 23:54:12 ETA(in 00:00:09). Processing speed: 280.000 MiB/sec Worker(2): 91.28% done. 2024-01-18 23:54:07 ETA(in 00:00:04). Processing speed: 360.000 MiB/sec Worker(3): 95.90% done. 2024-01-18 23:54:06 ETA(in 00:00:03). Processing speed: 240.000 MiB/sec Worker(1): 99.02% done. 2024-01-18 23:54:03 ETA(in 00:00:00). Processing speed: 191.922 MiB/sec Worker(2): 92.93% done. 2024-01-18 23:54:08 ETA(in 00:00:04). Processing speed: 304.000 MiB/sec Worker(0): 87.56% done. 2024-01-18 23:54:10 ETA(in 00:00:06). Processing speed: 336.000 MiB/sec Worker(0): 89.86% done. 2024-01-18 23:54:09 ETA(in 00:00:04). Processing speed: 424.000 MiB/sec Worker(2): 95.14% done. 2024-01-18 23:54:07 ETA(in 00:00:02). Processing speed: 408.000 MiB/sec Worker(2): 97.48% done. 2024-01-18 23:54:07 ETA(in 00:00:01). Processing speed: 432.000 MiB/sec Worker(0): 92.17% done. 2024-01-18 23:54:09 ETA(in 00:00:03). Processing speed: 424.000 MiB/sec Worker(0): 94.77% done. 2024-01-18 23:54:09 ETA(in 00:00:02). Processing speed: 480.000 MiB/sec Worker(2): 98.74% done. 2024-01-18 23:54:08 ETA(in 00:00:01). Processing speed: 231.814 MiB/sec Worker(0): 97.33% done. 2024-01-18 23:54:09 ETA(in 00:00:01). Processing speed: 472.000 MiB/sec Worker(2): 99.78% done. 2024-01-18 23:54:08 ETA(in 00:00:00). Processing speed: 191.641 MiB/sec Worker(0): 99.46% done. 2024-01-18 23:54:09 ETA(in 00:00:00). Processing speed: 391.986 MiB/sec All workers finished in 222 sec
运行完成后，会在当前目录下生成pages-ol-root 文件目录， 目录下按照每个页为一个文件，分为索引页和数据较大的BLOB 页，我们访问系统表的话，是存在索引页中的
5：扫描数据字典文件
./stream_parser -f /mysqldata/mysql/ibdata1
一般情况下表结构是存储在frm 文件中，drop table 会删除frm 文件，还好我们可以从innodb 系统表里读取一些信息恢复表结构。innodb 系统表有SYS_COLUMNS | SYS_FIELDS |SYS_INDEXES | SYS_TABLES。需要从系统表中恢复，而系统表是保存在$datadir/ibdata1 文件中的解析ibdata 文件，扫描出所有符合innodb 格式的数据页，结果会按照index_id 进行重新组织[root@localhost undrop-for-innodb-master]# ./stream_parser -f /mysqldata/mysql/ibdata1 Opening file: /mysqldata/mysql/ibdata1 File information: ID of device containing file: 64768 inode number: 4591360 protection: 100640 Opening file: /mysqldata/mysql/ibdata1 (regular file) number of hard links: 1 user ID of owner: 27 File information: group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 151560 ID of device containing file: 64768 inode number: 4591360 protection: 100640 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 Opening file: /mysqldata/mysql/ibdata1 number of blocks allocated: 151560 File information: ID of device containing file: 64768 inode number: 4591360 protection: 100640 (regular file) number of hard links: 1 user ID of owner: 27 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 151560 time of last access: 1705592841 Thu Jan 18 23:47:21 2024 Opening file: /mysqldata/mysql/ibdata1 time of last modification: 1705592841 Thu Jan 18 23:47:21 2024 File information: time of last status change: 1705592841 Thu Jan 18 23:47:21 2024 time of last access: 1705592841 Thu Jan 18 23:47:21 2024 ID of device containing file: 64768 inode number: 4591360 total size, in bytes: 77594624 (74.000 MiB) time of last modification: 1705592841 Thu Jan 18 23:47:21 2024 protection: 100640 (regular file) number of hard links: 1 user ID of owner: 27 time of last status change: 1705592841 Thu Jan 18 23:47:21 2024 group ID of owner: 27 device ID (if special file): 0 blocksize for filesystem I/O: 4096 number of blocks allocated: 151560 Size to process: 77594624 (74.000 MiB) total size, in bytes: 77594624 (74.000 MiB) time of last access: 1705592841 Thu Jan 18 23:47:21 2024 Size to process: 77594624 (74.000 MiB) time of last modification: 1705592841 Thu Jan 18 23:47:21 2024 time of last status change: 1705592841 Thu Jan 18 23:47:21 2024 total size, in bytes: 77594624 (74.000 MiB) Size to process: 77594624 (74.000 MiB) time of last access: 1705592841 Thu Jan 18 23:47:21 2024 time of last modification: 1705592841 Thu Jan 18 23:47:21 2024 time of last status change: 1705592841 Thu Jan 18 23:47:21 2024 total size, in bytes: 77594624 (74.000 MiB) Size to process: 77594624 (74.000 MiB) All workers finished in 0 sec [root@localhost undrop-for-innodb-master]# cd dictionary/ [root@localhost dictionary]# ll total 16 -rw-r--r--. 1 root root 1845 Oct 2 2015 SYS_COLUMNS.sql -rw-r--r--. 1 root root 1685 Oct 2 2015 SYS_FIELDS.sql -rw-r--r--. 1 root root 1876 Oct 2 2015 SYS_INDEXES.sql -rw-r--r--. 1 root root 1895 Oct 2 2015 SYS_TABLES.sql [root@localhost undrop-for-innodb-master]# cd pages-ibdata1 [root@localhost pages-ibdata1]# ll total 4 drwxr-xr-x. 2 root root 4096 Jan 19 00:03 FIL_PAGE_INDEX drwxr-xr-x. 2 root root 6 Jan 19 00:03 FIL_PAGE_TYPE_BLOB [root@localhost pages-ibdata1]# cd FIL_PAGE_INDEX [root@localhost FIL_PAGE_INDEX]# ll total 1312 -rw-r--r--. 1 root root 32768 Jan 19 00:03 0000000000000001.page -rw-r--r--. 1 root root 16384 Jan 19 00:03 0000000000000002.page -rw-r--r--. 1 root root 16384 Jan 19 00:03 0000000000000003.page -rw-r--r--. 1 root root 16384 Jan 19 00:03 0000000000000004.page -rw-r--r--. 1 root root 32768 Jan 19 00:03 0000000000000005.page -rw-r--r--. 1 root root 16384 Jan 19 00:03 0000000000000011.page -rw-r--r--. 1 root root 16384 Jan 19 00:03 0000000000000012.page -rw-r--r--. 1 root root 16384 Jan 19 00:03 0000000000000013.page -rw-r--r--. 1 root root 16384 Jan 19 00:03 0000000000000014.page -rw-r--r--. 1 root root 32768 Jan 19 00:03 0000000000000015.page -rw-r--r--. 1 root root 32768 Jan 19 00:03 0000000000000016.page -rw-r--r--. 1 root root 16384 Jan 19 00:03 0000000000000017.page -rw-r--r--. 1 root root 475136 Jan 19 00:03 0000000000000025.page -rw-r--r--. 1 root root 49152 Jan 19 00:03 0000000000000026.page -rw-r--r--. 1 root root 16384 Jan 19 00:03 0000000000000027.page -rw-r--r--. 1 root root 16384 Jan 19 00:03 0000000000000047.page -rw-r--r--. 1 root root 16384 Jan 19 00:03 18446744069414584320.page6：解析数据字典表
被删除表的数据字典信息由于数据字典是存放在ibdata 文件中，通过上面的stream_parser 解析出来的数据页，是按照index_id 排序组织的，其中01 是SYS_TABLES，02 是SYS_COLUMNS，03 是SYS_INDEXES，04 是SYS_FIELDS。解析对应的page，获取对应的表结构信息。这里有个地方需要注意，就是这个输出的文件路径./dumps/default，这是工具里代码固定的，因此在此步骤之前我们必须要创建这个目录$BASEDIR/dumps/default
[root@localhost undrop-for-innodb-master]# mkdir -p dumps/default [root@localhost undrop-for-innodb-master]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql >./dumps/default/SYS_TABLES 2>./dumps/default/SYS_TABLES.sql; [root@localhost undrop-for-innodb-master]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql >./dumps/default/SYS_INDEXES 2>./dumps/default/SYS_INDEXES.sql; [root@localhost undrop-for-innodb-master]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000002.page -t dictionary/SYS_COLUMNS.sql>./dumps/default/SYS_COLUMNS 2>./dumps/default/SYS_COLUMNS.sql; [root@localhost undrop-for-innodb-master]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000004.page -t dictionary/SYS_FIELDS.sql >./dumps/default/SYS_FIELDS 2>./dumps/default/SYS_FIELDS.sql;7：查看相应的数据字典信息
带有.sql 后缀的是根据表结构生成的load data 语句，其中load 语句中需要加载的文件就是不带.sql 的文本文件[root@localhost undrop-for-innodb-master]# cd dumps/default/ [root@localhost default]# ll total 32 -rw-r--r--. 1 root root 470 Jan 19 22:38 SYS_COLUMNS -rw-r--r--. 1 root root 286 Jan 19 22:38 SYS_COLUMNS.sql -rw-r--r--. 1 root root 200 Jan 19 22:38 SYS_FIELDS -rw-r--r--. 1 root root 253 Jan 19 22:38 SYS_FIELDS.sql -rw-r--r--. 1 root root 317 Jan 19 22:37 SYS_INDEXES -rw-r--r--. 1 root root 291 Jan 19 22:37 SYS_INDEXES.sql -rw-r--r--. 1 root root 580 Jan 19 22:34 SYS_TABLES -rw-r--r--. 1 root root 300 Jan 19 22:34 SYS_TABLES.sql [root@localhost default]# cat SYS_TABLES.sql SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/undrop-for-innodb-master/dumps/default/SYS_TABLES' REPLACE INTO TABLE `SYS_TABLES` 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`); [root@localhost default]# cat SYS_TABLES -- Page id: 8, Format: REDUNDANT, Records list: Invalid, Expected records: (0 28) 000000000511 2D000001360182 SYS_TABLES "test/t" 36 1 33 0 80 "" 22 000000000B10 2E0000013404D8 SYS_TABLES "test/articles" 52 3 33 0 80 "" 33 -- Page id: 8, Found records: 2, Lost records: YES, Leaf page: YES -- Page id: 8, Format: REDUNDANT, Records list: Invalid, Expected records: (0 28) 000000000511 2D000001360182 SYS_TABLES "test/t" 36 1 33 0 80 "" 22 000000000B10 2E0000013404D8 SYS_TABLES "test/articles" 52 3 33 0 80 "" 33 -- Page id: 8, Found records: 2, Lost records: YES, Leaf page: YES [root@localhost default]# cat SYS_FIELDS.sql SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/undrop-for-innodb-master/dumps/default/SYS_FIELDS' REPLACE INTO TABLE `SYS_FIELDS` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_FIELDS\t' (`INDEX_ID`, `POS`, `COL_NAME`); [root@localhost default]# cat SYS_FIELDS -- Page id: 12, Format: REDUNDANT, Records list: Invalid, Expected records: (0 43) 000000000B10 2E0000013403C7 SYS_FIELDS 48 0 "id" -- Page id: 12, Found records: 1, Lost records: YES, Leaf page: YES [root@localhost default]# cat SYS_COLUMNS.sql SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/undrop-for-innodb-master/dumps/default/SYS_COLUMNS' REPLACE INTO TABLE `SYS_COLUMNS` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_COLUMNS\t' (`TABLE_ID`, `POS`, `NAME`, `MTYPE`, `PRTYPE`, `LEN`, `PREC`); [root@localhost default]# cat SYS_COLUMNS -- Page id: 10, Format: REDUNDANT, Records list: Invalid, Expected records: (0 133) 000000000582 5A0000016001BA SYS_COLUMNS 48 0 "i" 6 1027 4 0 000000000585 5C0000016201BA SYS_COLUMNS 49 0 "i" 6 1027 4 0 000000000B10 2E000001340439 SYS_COLUMNS 52 0 "id" 6 1283 4 0 000000000B10 2E00000134046E SYS_COLUMNS 52 1 "title" 12 2953231 800 0 000000000B10 2E0000013404A3 SYS_COLUMNS 52 2 "body" 5 2949372 10 0 -- Page id: 10, Found records: 5, Lost records: YES, Leaf page: YES [root@localhost default]# cat SYS_INDEXES.sql SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/undrop-for-innodb-master/dumps/default/SYS_INDEXES' REPLACE INTO TABLE `SYS_INDEXES` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'SYS_INDEXES\t' (`TABLE_ID`, `ID`, `NAME`, `N_FIELDS`, `TYPE`, `SPACE`, `PAGE_NO`); [root@localhost default]# cat SYS_INDEXES -- Page id: 11, Format: REDUNDANT, Records list: Invalid, Expected records: (0 33) 000000000582 5A0000016001EF SYS_INDEXES 48 46 "GEN\_CLUST\_INDEX" 0 1 4294967295 4294967295 000000000B10 2E0000013403FC SYS_INDEXES 52 48 "PRIMARY" 1 3 33 4294967295 -- Page id: 11, Found records: 2, Lost records: YES, Leaf page: YES7：导入新的数据字典信息
注意这里需要是另外一个mysql 实例，因为为了避免数据文件被复写，我们此前已经将事故实例停止了服务。
1:创建数据字典表，该语句来自工具中的dictionary 目录[root@localhost undrop-for-innodb-master]# cd dictionary/ [root@localhost dictionary]# ll total 16 -rw-r--r--. 1 root root 1845 Oct 2 2015 SYS_COLUMNS.sql -rw-r--r--. 1 root root 1685 Oct 2 2015 SYS_FIELDS.sql -rw-r--r--. 1 root root 1876 Oct 2 2015 SYS_INDEXES.sql -rw-r--r--. 1 root root 1895 Oct 2 2015 SYS_TABLES.sql [root@localhost dictionary]# mysql -h10.13.13.25 -P3306 -uroot -pxxzx7817600 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.26-log Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +------------------------+ | Database | +------------------------+ | information_schema | | employees | | mysql | | #mysql50#mysql-keyring | | performance_schema | | sys | | test | | undo | +------------------------+ 8 rows in set (0.01 sec) mysql> create database dbrecover; Query OK, 1 row affected (0.01 sec) mysql> use dbrecover Database changed mysql> source /undrop-for-innodb-master/dictionary/SYS_TABLES.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> source /undrop-for-innodb-master/dictionary/SYS_INDEXES.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> source /undrop-for-innodb-master/dictionary/SYS_FIELDS.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> source /undrop-for-innodb-master/dictionary/SYS_COLUMNS.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) [root@localhost mysql]# mysqlshow -h10.13.13.25 -P3306 -uroot -pxxzx7817600 -vv dbrecover mysqlshow: [Warning] Using a password on the command line interface can be insecure. Database: dbrecover +-------------+----------+------------+ | Tables | Columns | Total Rows | +-------------+----------+------------+ | sys_columns | 7 | 0 | | sys_fields | 3 | 0 | | sys_indexes | 7 | 0 | | sys_tables | 8 | 0 | +-------------+----------+------------+ 4 rows in set.2：导入前面dumps/default 目录生成的LOAD DATA 语句，用于恢复数据字典记录
mysql> source /undrop-for-innodb-master/dumps/default/SYS_TABLES.sql Query OK, 0 rows affected (0.00 sec) Query OK, 4 rows affected (0.04 sec) Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 mysql> source /undrop-for-innodb-master/dumps/default/SYS_COLUMNS.sql Query OK, 0 rows affected (0.00 sec) Query OK, 5 rows affected (0.01 sec) Records: 5 Deleted: 0 Skipped: 0 Warnings: 0 mysql> source /undrop-for-innodb-master/dumps/default/SYS_INDEXES.sql Query OK, 0 rows affected (0.00 sec) Query OK, 2 rows affected (0.01 sec) Records: 2 Deleted: 0 Skipped: 0 Warnings: 0 mysql> source /undrop-for-innodb-master/dumps/default/SYS_FIELDS.sql Query OK, 0 rows affected (0.00 sec) Query OK, 1 row affected (0.01 sec) Records: 1 Deleted: 0 Skipped: 0 Warnings: 08：解析数据字典获取DDL 语句
[root@localhost undrop-for-innodb-master]# ./sys_parser -h10.13.13.25 -P3306 -uroot -pxxzx7817600 -d dbrcover test/articles ./sys_parser: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory [root@localhost lib]# ln -s /mysqlsoft/mysql/lib/libmysqlclient.so.20 /usr/lib64/libmysqlclient.so.20 [root@localhost undrop-for-innodb-master]# ./sys_parser -h10.13.13.25 -P3306 -uroot -pxxzx7817600 -d dbrecover test/articles ./sys_parser: invalid option -- 'P' sys_parser [-h] [-u ] [-p ] [-d ] databases/table 请注意：只能是3306 端口 [root@localhost undrop-for-innodb-master]# ./sys_parser -h10.13.13.25 -uroot -pxxzx7817600 -d dbrecover test/articles CREATE TABLE `articles`( `id` INT NOT NULL, `title` VARCHAR(200) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci', `body` TEXT, PRIMARY KEY (`id`) ) ENGINE=InnoDB; 9：查找drop 表数据对应的page 页
Mysql 是聚簇表，数据都在主键索引上，因此需要确认该primary key 对应的数据页。也有可能没有创建primary ke，数据库会自动隐藏主建。主建primary key 对应的页为56[root@localhost dictionary]# mysql -h10.13.13.25 -P3306 -uroot -pxxzx7817600 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.26-log Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use dbrecover Database changed mysql> show tables; +---------------------+ | Tables_in_dbrecover | +---------------------+ | sys_columns | | sys_fields | | sys_indexes | | sys_tables | +---------------------+ 4 rows in set (0.01 sec) mysql> select * from SYS_TABLES; +---------------+----+--------+------+--------+---------+--------------+-------+ | NAME | ID | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE | +---------------+----+--------+------+--------+---------+--------------+-------+ | test/articles | 52 | 3 | 33 | 0 | 80 | | 33 | | test/t | 36 | 1 | 33 | 0 | 80 | | 22 | +---------------+----+--------+------+--------+---------+--------------+-------+ 2 rows in set (0.00 sec) mysql> select * from SYS_INDEXES WHERE TABLE_ID=52; +----------+----+---------+----------+------+-------+------------+ | TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE | PAGE_NO | +----------+----+---------+----------+------+-------+------------+ | 52 | 48 | PRIMARY | 1 | 3 | 33 | 4294967295 | +----------+----+---------+----------+------+-------+------------+ 1 row in set (0.00 sec)10：解析磁盘页中删除表的记录
注意: 此处几个文件名程序把导出的两个数据文件的文件名关系是写死的, 以下dumps/default/articles 中的articles 是需要和表名一致,在articles_load.sql 中会引用此文件路经./tmp/articles.sql:恢复的表结构的语句[root@localhost undrop-for-innodb-master]# vi /tmp/articles.sql CREATE TABLE `articles`( `id` INT NOT NULL, `title` VARCHAR(200) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci', `body` TEXT, PRIMARY KEY (`id`) ) ENGINE=InnoDB; [root@localhost undrop-for-innodb-master]# ./c_parser -6f /undrop-for-innodb-master/pages-ol-root/FIL_PAGE_INDEX/0000000000000048.page -t /tmp/articles.sql>./dumps/default/articles 2>./dumps/default/articles_load.sql; [root@localhost undrop-for-innodb-master]# ll dumps/default/ total 40 -rw-r--r--. 1 root root 437 Jan 20 00:16 articles -rw-r--r--. 1 root root 239 Jan 20 00:16 articles_load.sql -rw-r--r--. 1 root root 470 Jan 19 22:38 SYS_COLUMNS -rw-r--r--. 1 root root 286 Jan 19 22:38 SYS_COLUMNS.sql -rw-r--r--. 1 root root 200 Jan 19 22:38 SYS_FIELDS -rw-r--r--. 1 root root 253 Jan 19 22:38 SYS_FIELDS.sql -rw-r--r--. 1 root root 317 Jan 19 22:37 SYS_INDEXES -rw-r--r--. 1 root root 291 Jan 19 22:37 SYS_INDEXES.sql -rw-r--r--. 1 root root 580 Jan 19 22:34 SYS_TABLES -rw-r--r--. 1 root root 300 Jan 19 22:34 SYS_TABLES.sql [root@localhost undrop-for-innodb-master]# more ./dumps/default/articles -- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (3 3) 000000000B0B AB0000011E0110 articles 1 "1001 MySQL Tricks" "1. Never run mysqld as root. 2. ... " 000000000B0B AB0000011E011C articles 2 "MySQL vs. YourSQL" "In the following database comparison ..." 000000000B0B AB0000011E0128 articles 3 "MySQL Security" "When configured properly, MySQL ..." -- Page id: 3, Found records: 3, Lost records: NO, Leaf page: YES [root@localhost undrop-for-innodb-master]# more ./dumps/default/articles_load.sql SET FOREIGN_KEY_CHECKS=0; LOAD DATA LOCAL INFILE '/undrop-for-innodb-master/dumps/default/articles' REPLACE INTO TABLE `articles` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'articles\t' (`id`, `title`, `body`);11：恢复表结构并导入数据
mysql> source /tmp/articles.sql Query OK, 0 rows affected (0.01 sec) mysql> source /undrop-for-innodb-master/dumps/default/articles_load.sql Query OK, 0 rows affected (0.00 sec) Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from articles; +----+-------------------+------------------------------------------+ | id | title | body | +----+-------------------+------------------------------------------+ | 1 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... | | 2 | MySQL vs. YourSQL | In the following database comparison ... | | 3 | MySQL Security | When configured properly, MySQL ... | +----+-------------------+------------------------------------------+ 3 rows in set (0.00 sec)至此数据恢复了。