mysql无备份情况下的恢复完整篇

mysql无备份情况下的恢复


一、环境：
CentOS release 6.5 (Final)
Server version: 5.7.17-11-log Percona Server (GPL), Release 11, Revision f60191c
innodb 存储引擎
工具：undrop-for-innodb


二、安装编译恢复工具undrop-for-innodb


2.1依赖包的安装
[root@cqgs_ywjk undrop-for-innodb-master]# yum install flex
[root@cqgs_ywjk undrop-for-innodb-master]# yum install bison
[root@cqgs_ywjk undrop-for-innodb-master]# yum install make
[root@cqgs_ywjk undrop-for-innodb-master]# yum install gcc


2.2解压编译
[root@cqgs_ywjk mysql]# unzip undrop-for-innodb-master.zip 
Archive:  undrop-for-innodb-master.zip
cf736de27758b73c07619f07d90c4ed8ee857d53
   creating: undrop-for-innodb-master/
  inflating: undrop-for-innodb-master/.editorconfig  
  inflating: undrop-for-innodb-master/.gitignore  
  inflating: undrop-for-innodb-master/LICENSE  
.........


[root@cqgs_ywjk undrop-for-innodb-master]# pwd
/mysql/undrop-for-innodb-master
[root@cqgs_ywjk undrop-for-innodb-master]# make
flex  sql_parser.l
sql_parser.l:66: warning, the character range [
                                               -Y] is ambiguous in a case-insensitive scanner
sql_parser.l:66: warning, the character range [a] is ambiguous in a case-insensitive scanner
bison  -o sql_parser.c sql_parser.y
sql_parser.y: conflicts: 6 shift/reduce
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c sql_parser.c
lex.yy.c:3430: warning: ‘yyunput’ defined but not used
lex.yy.c:3471: warning: ‘input’ defined but not used
cc -D_FILE_OFFSET_BITS=64 -Wall -g -O3 -pipe -I./include -c c_parser.c
./include/ctype-latin1.c:359: warning: ‘my_mb_wc_latin1’ defined but not used
./include/ctype-latin1.c:372: warning: ‘my_wc_mb_latin1’ defined but not used
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


2.3单独编译sys_parser工具（用于表机构的恢复）
[root@cqgs_ywjk undrop-for-innodb-master]# gcc `/usr/bin/mysql_config --cflags` `/usr/bin/mysql_config --libs` -o sys_parser sys_parser.c
In file included from /usr/include/stdio.h:27,
                 from sys_parser.c:1:
/usr/include/features.h:330:4: warning: #warning _FORTIFY_SOURCE requires compiling with optimization (-O)


三、测试truncat table的恢复(Innodb_file_per_table = ON)


3.1创建并truncat table
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
mysql> create table userbak select * from mysql.user;
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0
mysql> alter table userbak add primary key(user);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select count(*) from userbak;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)
mysql> truncate table userbak;
Query OK, 0 rows affected (0.03 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from userbak;
Empty set (0.00 sec)


3.2 备份表结构(恢复时会用到)
[root@cqgs_ywjk undrop-for-innodb-master]# mysqldump --opt -d -uroot -proot1234 test userbak >userbak.sql
注意：注释掉userbak.sql 里 DROP TABLE IF EXISTS `userbak`;一行


3.3 使用恢复工具扫描表userbak的数据文件userbak.ibd 
[root@cqgs_ywjk undrop-for-innodb-master]# ./stream_parser -f /mysql/Percona/data/test/userbak.ibd 
Opening file: /mysql/Percona/data/test/userbak.ibd
File information:


ID of device containing file:        64768
inode number:                       677667
protection:                         100640 (regular file)
number of hard links:                    1
user ID of owner:                      501
group ID of owner:                     503
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:            192
Opening file: /mysql/Percona/data/test/userbak.ibd
File information:


ID of device containing file:        64768
inode number:                       677667
protection:                         100640 (regular file)
number of hard links:                    1
user ID of owner:                      501
group ID of owner:                     503
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:            192
time of last access:            1520905467 Tue Mar 13 09:44:27 2018
time of last modification:      1520905716 Tue Mar 13 09:48:36 2018
time of last status change:     1520905716 Tue Mar 13 09:48:36 2018
total size, in bytes:                98304 (96.000 kiB)


time of last access:            1520905467 Tue Mar 13 09:44:27 2018
time of last modification:      1520905716 Tue Mar 13 09:48:36 2018
time of last status change:     1520905716 Tue Mar 13 09:48:36 2018
Size to process:                     98304 (96.000 kiB)
total size, in bytes:                98304 (96.000 kiB)


Size to process:                     98304 (96.000 kiB)
All workers finished in 0 sec


3.4 确定生成的page文件是否有我们的数据
[root@cqgs_ywjk undrop-for-innodb-master]# ./c_parser -6f pages-userbak.ibd/FIL_PAGE_INDEX/0000000000000117.page -t userbak.sql 
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (0 0)
-- Page id: 3, Found records: 0, Lost records: NO, Leaf page: YES
-- Page id: 4, Format: COMPACT, Records list: Valid, Expected records: (5 5)
0000000177E0 E80000020A013A userbak "hhj                             " "\%                                                           " 2 2 2 2 2 2 2 "mysql\_native\_password                                           " "*A6F243332EBF1FCD96DCFFAE0251A84E08D2FC62" 1 "2017-09-12 15:38:37" NULL 1
0000000177E0 E80000020A011E userbak "mysql.sys                       " "localhost                                                   " 1 1 1 1 1 1 1 "mysql\_native\_password                                           " "*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE" 1 "2017-03-29 16:42:15" NULL 2
0000000177E0 E80000020A012C userbak "rep                             " "98.12.106.139                                               " 1 1 1 1 1 1 1 "mysql\_native\_password                                           " "*08F5BB4670F148DB0376A1EE646F0C67EAE138CE" 1 "2017-03-29 17:11:44" NULL 1
0000000177E0 E80000020A0110 userbak "root                            " "localhost                                                   " 2 2 2 2 2 2 2 "mysql\_native\_password                                           " "*7FB1F1B8AD1B4CFD578E76ABC1B6ADFF70D04FA0" 1 "2017-03-29 16:56:38" NULL 1
0000000177E0 E80000020A0148 userbak "test                            " "\%                                                           " 1 1 1 1 1 1 1 "mysql\_native\_password                                           " "*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29" 1 "2018-01-15 15:19:32" NULL 1
-- Page id: 4, Found records: 5, Lost records: NO, Leaf page: YES
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (0 0)
-- Page id: 3, Found records: 0, Lost records: NO, Leaf page: YES
-- Page id: 4, Format: COMPACT, Records list: Valid, Expected records: (5 5)
0000000177E0 E80000020A013A userbak "hhj                             " "\%                                                           " 2 2 2 2 2 2 2 "mysql\_native\_password                                           " "*A6F243332EBF1FCD96DCFFAE0251A84E08D2FC62" 1 "2017-09-12 15:38:37" NULL 1
0000000177E0 E80000020A011E userbak "mysql.sys                       " "localhost                                                   " 1 1 1 1 1 1 1 "mysql\_native\_password                                           " "*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE" 1 "2017-03-29 16:42:15" NULL 2
0000000177E0 E80000020A012C userbak "rep                             " "98.12.106.139                                               " 1 1 1 1 1 1 1 "mysql\_native\_password                                           " "*08F5BB4670F148DB0376A1EE646F0C67EAE138CE" 1 "2017-03-29 17:11:44" NULL 1
0000000177E0 E80000020A0110 userbak "root                            " "localhost                                                   " 2 2 2 2 2 2 2 "mysql\_native\_password                                           " "*7FB1F1B8AD1B4CFD578E76ABC1B6ADFF70D04FA0" 1 "2017-03-29 16:56:38" NULL 1
0000000177E0 E80000020A0148 userbak "test                            " "\%                                                           " 1 1 1 1 1 1 1 "mysql\_native\_password                                           " "*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29" 1 "2018-01-15 15:19:32" NULL 1
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/mysql/undrop-for-innodb-master/dumps/default/userbak' REPLACE INTO TABLE `userbak` FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY 'userbak\t' (`User`, `Host`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `Event_priv`, `Trigger_priv`, `Create_tablespace_priv`, `ssl_type`, @var_ssl_cipher, @var_x509_issuer, @var_x509_subject, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`, `plugin`, `authentication_string`, `password_expired`, `password_last_changed`, `password_lifetime`, `account_locked`)
SET
    ssl_cipher = UNHEX(@var_ssl_cipher),
    x509_issuer = UNHEX(@var_x509_issuer),
    x509_subject = UNHEX(@var_x509_subject);
-- STATUS {"records_expected": 10, "records_dumped": 10, "records_lost": false} STATUS END
-- Page id: 4, Found records: 5, Lost records: NO, Leaf page: YES


3.5 抽取page文件中的数据到指定文件
[root@cqgs_ywjk undrop-for-innodb-master]# ./c_parser -6f pages-userbak.ibd/FIL_PAGE_INDEX/0000000000000117.page -t userbak.sql >dumps/default/userbak 2> dumps/default/userbak.sql
[root@cqgs_ywjk undrop-for-innodb-master]# cd dumps/default/
[root@cqgs_ywjk default]# ls
SYS_COLUMNS  SYS_COLUMNS.sql  SYS_FIELDS  SYS_FIELDS.sql  SYS_INDEXES  SYS_INDEXES.sql  SYS_TABLES  SYS_TABLES.sql  userbak  userbak.sql


3.6 加载数据到数据库userbak表中
mysql> source /mysql/undrop-for-innodb-master/dumps/default/userbak.sql
Query OK, 0 rows affected (0.00 sec)


Query OK, 10 rows affected (0.01 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0


mysql> select count(*) from userbak;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)




四、测试drop table的恢复(Innodb_file_per_table = ON)


4.1创建并drop table
mysql> show variables like 'innodb_file_per_table'
    -> ;
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
mysql> create table dropontable select * from mysql.user;
Query OK, 5 rows affected (0.04 sec)
Records: 5  Duplicates: 0  Warnings: 0


mysql> alter table dropontable add primary key(user);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> select count(*) from dropontable;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)


mysql> drop table dropontable;
Query OK, 0 rows affected (0.02 sec)


4.2 备份表结构(drop之前)


[root@cqgs_ywjk undrop-for-innodb-master]# mysqldump --opt -d -uroot -proot1234 test dropontable >dropontable.sql
注意：注释掉dropontable.sql 里 DROP TABLE IF EXISTS `dropontable`;一行


4.3  使用恢复工具扫描表 ibdata1 的数据文件(数据字典)
[root@cqgs_ywjk undrop-for-innodb-master]# ./stream_parser -f /mysql/Percona/data/ibdata1 
Opening file: /mysql/Percona/data/ibdata1
File information:


ID of device containing file:        64768
inode number:                       672917
protection:                         100640 (regular file)
number of hard links:                    1
user ID of owner:                      501
group ID of owner:                     503
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         155656
Opening file: /mysql/Percona/data/ibdata1
File information:


ID of device containing file:        64768
inode number:                       672917
protection:                         100640 (regular file)
number of hard links:                    1
user ID of owner:                      501
group ID of owner:                     503
time of last access:            1520905467 Tue Mar 13 09:44:27 2018
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         155656
time of last modification:      1521445851 Mon Mar 19 15:50:51 2018
time of last status change:     1521445851 Mon Mar 19 15:50:51 2018
total size, in bytes:             79691776 (76.000 MiB)


time of last access:            1520905467 Tue Mar 13 09:44:27 2018
time of last modification:      1521445851 Mon Mar 19 15:50:51 2018
time of last status change:     1521445851 Mon Mar 19 15:50:51 2018
total size, in bytes:             79691776 (76.000 MiB)


Size to process:                  79691776 (76.000 MiB)
Size to process:                  79691776 (76.000 MiB)
All workers finished in 0 sec


4.4 查看表dropontable的TABLE ID（0000000000000001.page包含SYS_TABLES数据字典）
[root@cqgs_ywjk undrop-for-innodb-master]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql | grep dropontable
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/mysql/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`, 000000017825 35000001DE0F6C SYS_TABLES "test/dropontable" 119 45 33 0 80 "" 102
`MIX_ID`, `MIX_LEN`, `CLUSTER_NAME`, `SPACE`);
-- STATUS {"records_expected": 43, "records_dumped": 16, "records_lost": true} STATUS END


4.5 通过TABLE ID查看表dropontable的索引ID（0000000000000003.page包含SYS_INDEXES数据字典）
[root@cqgs_ywjk undrop-for-innodb-master]# ./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql | grep 119
SET FOREIGN_KEY_CHECKS=0;
LOAD DATA LOCAL INFILE '/mysql/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`);
-- STATUS {"records_expected": 50, "records_dumped": 8, "records_lost": true} STATUS END
000000017825 35000001DE05DE SYS_INDEXES 119 119 "PRIMARY" 1 3 102 4294967295






4.6 使用恢复工具扫描表 mysql 数据文件所在的文件系统(因为drop表 dropontable.idb数据文件被删除)
[root@cqgs_ywjk test]# df -h
Filesystem                          Size  Used Avail Use% Mounted on
/dev/mapper/vg_cqgsgwclxt-lv_root    50G   24G   24G  50% /
tmpfs                               1.9G 1005M  900M  53% /dev/shm
/dev/xvda1                          485M   51M  410M  11% /boot
/dev/mapper/vg_cqgsgwclxt-lv_home   5.6G  1.7G  3.7G  32% /home
/mysql/Oracle Linux 6.5 x86_64.iso  3.7G  3.7G     0 100% /media
[root@cqgs_ywjk undrop-for-innodb-master]# ./stream_parser -f /dev/mapper/vg_cqgsgwclxt-lv_root -t 50G 
Opening file: /dev/mapper/vg_cqgsgwclxt-lv_root
File information:


Opening file: /dev/mapper/vg_cqgsgwclxt-lv_root
File information:


ID of device containing file:            5
inode number:                         7090
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
ID of device containing file:            5
inode number:                         7090
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:            1520497867 Thu Mar  8 16:31:07 2018
time of last modification:      1471251021 Mon Aug 15 16:50:21 2016
time of last status change:     1471251021 Mon Aug 15 16:50:21 2016
total size, in bytes:                    0 (0.000 exp(+0))


Size to process:              161061273600 (150.000 GiB)
time of last access:            1520497867 Thu Mar  8 16:31:07 2018
time of last modification:      1471251021 Mon Aug 15 16:50:21 2016
time of last status change:     1471251021 Mon Aug 15 16:50:21 2016
total size, in bytes:                    0 (0.000 exp(+0))


Size to process:              161061273600 (150.000 GiB)
Worker(1): -98.99% done. 2018-03-19 17:01:28 ETA(in 00:42:44). Processing speed: 59.583 MiB/sec
Worker(0): 1.02% done. 2018-03-19 16:39:59 ETA(in 00:21:15). Processing speed: 59.582 MiB/sec
Worker(1): -97.98% done. 2018-03-19 17:01:26 ETA(in 00:42:29). Processing speed: 59.633 MiB/sec


4.7 通过索引ID 119对应查看0000000000000119.page扫描出来drop的数据。
root@cqgs_ywjk undrop-for-innodb-master]# ./c_parser -6f pages-vg_cqgsgwclxt-lv_root/FIL_PAGE_INDEX/0000000000000119.page -t dropontable.sql  | more
-- Page id: 3, Format: COMPACT, Records list: Valid, Expected records: (5 5)
00000001780B A4000001BA013A dropontable "hhj                             " "\%                                                           " 2 2 2 2 2 2 "mysql\_native\_password                                           " "*A6F243332EBF1FCD96DCFFAE0251A84E08D2FC62" 1 "2017-09-12 15:38:37" NULL 1
00000001780B A4000001BA011E dropontable "mysql.sys                       " "localhost                                                   " 1 1 1 1 1 1 "mysql\_native\_password                                           " "*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE" 1 "2017-03-29 16:42:15" NULL 2
00000001780B A4000001BA012C dropontable "rep                             " "98.12.106.139                                               " 1 1 1 1 1 1 "mysql\_native\_password                                           " "*08F5BB4670F148DB0376A1EE646F0C67EAE138CE" 1 "2017-03-29 17:11:44" NULL 1
00000001780B A4000001BA0110 dropontable "root                            " "localhost                                                   " 2 2 2 2 2 2 "mysql\_native\_password                                           " "*7FB1F1B8AD1B4CFD578E76ABC1B6ADFF70D04FA0" 1 "2017-03-29 16:56:38" NULL 1
00000001780B A4000001BA0148 dropontable "test                            " "\%                                                           " 1 1 1 1 1 1 "mysql\_native\_password                                           " "*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29" 1 "2018-01-15 15:19:32" NULL 1
-- Page id: 3, Found records: 5, Lost records: NO, Leaf page: YES
-- Page id: 4, Format: COMPACT, Records list: Valid, Expected records: (5 5)
00000001780B A4000001BA013A dropontable "hhj                             " "\%                                                           " 2 2 2 2 2 2 "mysql\_native\_password                                           " "*A6F243332EBF1FCD96DCFFAE0251A84E08D2FC62" 1 "2017-09-12 15:38:37" NULL


4.8 抽取119.page文件中的数据到指定文件
./c_parser -6f pages-vg_cqgsgwclxt-lv_root/FIL_PAGE_INDEX/0000000000000119.page -t dropontable.sql  >dumps/default/dropontable 2> dumps/default/dropontable.sql




4.9 恢复表结构到数据库中
mysql> source /mysql/undrop-for-innodb-master/dropontable.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 (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.25 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)


4.10 加载数据到数据库中
mysql> source /mysql/undrop-for-innodb-master/dumps/default/dropontable.sql
Query OK, 0 rows affected (0.00 sec)


Query OK, 70 rows affected (0.05 sec)
Records: 70  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select count(*) from dropontable;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)






五.使用sys_parser恢复表结构


5.1 创建并drop table
mysql> create table sys_parser_test1 (id int auto_increment primary key,name varchar(10));
Query OK, 0 rows affected (0.04 sec)


mysql> show create table sys_parser_test1;
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                           |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sys_parser_test1 | CREATE TABLE `sys_parser_test1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)


mysql> drop table sys_parser_test1;
Query OK, 0 rows affected (0.02 sec)


5.2 使用恢复工具扫描表 ibdata1 的数据文件
[root@cqgs_ywjk undrop-for-innodb-master]# ./stream_parser -f /mysql/Percona/data/ibdata1 
Opening file: /mysql/Percona/data/ibdata1
File information:


ID of device containing file:        64768
inode number:                       672917
protection:                         100640 (regular file)
number of hard links:                    1
user ID of owner:                      501
group ID of owner:                     503
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:         155656
Opening file: /mysql/Percona/data/ibdata1
File information:


ID of device containing file:        64768
inode number:                       672917
protection:                         100640 (regular file)
number of hard links:                    1
user ID of owner:                      501
group ID of owner:                     503
device ID (if special file):             0
time of last access:            1521446013 Mon Mar 19 15:53:33 2018
blocksize for filesystem I/O:         4096
number of blocks allocated:         155656
time of last modification:      1521451232 Mon Mar 19 17:20:32 2018
time of last status change:     1521451232 Mon Mar 19 17:20:32 2018
total size, in bytes:             79691776 (76.000 MiB)


Size to process:                  79691776 (76.000 MiB)
time of last access:            1521446013 Mon Mar 19 15:53:33 2018
time of last modification:      1521451232 Mon Mar 19 17:20:32 2018
time of last status change:     1521451232 Mon Mar 19 17:20:32 2018
total size, in bytes:             79691776 (76.000 MiB)


Size to process:                  79691776 (76.000 MiB)
Worker(1): 63.16% done. 2018-03-19 17:22:19 ETA(in 00:00:00). Processing speed: 16.000 MiB/sec
Worker(0): 63.12% done. 2018-03-19 17:22:19 ETA(in 00:00:00). Processing speed: 15.984 MiB/sec
All workers finished in 1 sec


5.3 创建恢复数据字典表
[root@cqgs_ywjk undrop-for-innodb-master]# ./recover_dictionary.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.
59 recs OK
SYS_COLUMNS ... mysql: [Warning] Using a password on the command line interface can be insecure.
742 recs OK
SYS_INDEXES ... mysql: [Warning] Using a password on the command line interface can be insecure.
58 recs OK
SYS_FIELDS ... mysql: [Warning] Using a password on the command line interface can be insecure.
79 recs OK
All OK


5.4 drop表的结构恢复(注意：恢复的表结构很简单，比如AUTO_INCREMENT就没恢复出来,不建议使用)
[root@cqgs_ywjk undrop-for-innodb-master]# ./sys_parser -h127.0.0.1 -uroot -proot1234  -d test test/sys_parser_test1
CREATE TABLE `sys_parser_test1`(
`id` INT NOT NULL,
`name` VARCHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;






六、注意事项
undrop-for-innodb同样可以恢复delete table，drop database，数据文件损坏
生产库数据文件很容易被覆盖，导致数据恢复不全,出现以上情况建设立刻停止服务
对于Innodb_file_per_table = OFF 数据都在ibdata1中,恢复方法一样。
恢复的表最好是有主键
Mysql做好日常的备份工作