mysql ibd file is encrypted and recovery

E-mail：chf.dba@gmail.com

Title: mysql ibd file is encrypted and recovery

Author: DATABASE SOS©All rights reserved [without my consent, it may not be reproduced in any form, otherwise there is the right to further legal responsibility.]

Another mysql ibd file was encrypted and ransomware recovery request
20200504203408

Through analysis, only part of the file is encrypted. In theory, through the underlying analysis, part of the data that has not been damaged can be recovered
20200504203850

Recover data by processing
20200504212047

Once again proved that we can also achieve better recovery from the ransomware that the mysql file is encrypted
If the Innodb_file_per_table parameter is false (the pre-5.6 version defaults to false), you need to restore through the ibdata file.If Innodb_file_per_table is true, you need to restore through each individual ibd.If your database (oracle, mysql sql server) is unfortunately encrypted by Bitcoin, you can contact us at chf.dba@gamil.com to provide professional decryption and recovery services.

MySQL Ransomware Recovery

E-mail：chf.dba@gmail.com

Title: MySQL Ransomware Recovery

Author: DATABASE SOS©All rights reserved [without my consent, it may not be reproduced in any form, otherwise there is the right to further legal responsibility.]

Recently encountered several mysql databases that were deleted by hackers and left Bitcoin ransomware in the WARNING table of each library

mysql> desc WARNING
    -> ;
+-----------------+----------+------+-----+---------+-------+
| Field           | Type     | Null | Key | Default | Extra |
+-----------------+----------+------+-----+---------+-------+
| id              | int(11)  | YES  |     | NULL    |       |
| warning         | longtext | YES  |     | NULL    |       |
| Bitcoin_Address | longtext | YES  |     | NULL    |       |
| Email           | longtext | YES  |     | NULL    |       |
+-----------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> select * from WARNING;
+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------+------------------+
| id   | warning                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        | Bitcoin_Address                    | Email            |
+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------+------------------+
|    1 | To recover your lost Database and avoid leaking it: Send us 0.06 Bitcoin (BTC) to our Bitcoin address 1BLYhUDmnmVPVjcTWgc6gFT6DCYwbVieUD and contact us by Email with your Server IP or Domain name and a Proof of Payment. If you are unsure if we have your data, contact us and we will send you a proof. Your Database is downloaded and backed up on our servers. Backups that we have right now: xxxx,xxxxxx,xxxxxxxx,xxxxxxx . If we dont receive your payment in the next 10 Days, we will make your database public or use them otherwise. | 1BLYhUDmnmVPVjcTWgc6gFT6DCYwbVieUD | contact@sqldb.to |
+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------+------------------+
1 row in set (0.00 sec)

The general meaning is: We have backed up your database, and you gave us 0.06 bitcoins, and we give you the data. If we do not receive the payment within 10 days, the database will be made public or used for other purposes. The experience of friends we have contacted in the past, the database will not be given to you after payment (most likely the hacker did not back up the database at all, just deleted the database and then extorted Bitcoin.

For such cases, through analysis, it is confirmed that the hacker has deleted the database.In the case of no coverage, we can recover its data.MySQL drop database recovery (the recovery method is also applicable to MySQL drop table, delete, truncate table)Minimize the loss caused by database corruption.
20200303125417
If you also encounter this problem, please protect the site, do not import the backup database, do not write to the partition where the data is located (the better the site protection, the better the data recovery effect), mirror the relevant disk to prevent secondary damage. We can provide professional mysql recovery services to reduce your losses.

rm mysql datadir recovery

E-mail：chf.dba@gmail.com

Title: rm mysql datadir recovery

Author: DATABASE SOS©All rights reserved [without my consent, it may not be reproduced in any form, otherwise there is the right to further legal responsibility.]

Received a friend’s request to delete the datadir directory of the mysql database. The database is currently running, but many operations can no longer be performed normally.
The database can log in, but no business database can be seen, you can query it in conjunction with the table name

[root@hy-db-xff-s-110 mysql3306]# mysql -uroot -ptSQghoV^J1GE^U8*wPElImv5
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 443214
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, 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 |
+--------------------+
1 row in set (0.00 sec)

mysql> select count(1) from xifenfei.orders;
+----------+
| count(1) |
+----------+
| 16451326 |
+----------+
1 row in set (4.17 sec)

Data cannot be exported (into outfile does not work due to the default of the secure-file-priv parameter)

mysql> select * from xifenfei.orders into outfile '/bakcup/orders_new.sql' 
   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

[root@hy-db-cps-s-110 fd]# mysqldump  -uroot -pwww.xifenfei.com xifenfei orders >/linshi/1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1049: Unknown database 'xifenfei' when selecting the database

Because mysql has no crash, the related files already exist (not really deleted)
rm_mysql_ibd

Use this method to restore the relevant data files to the new server, and then try to start the database. It is found that the database cannot be started normally. Some files are lost. Finally, the individual ibds are processed separately.[MySQL Recovery]mysql ibd file recoveryTo achieve the recovery of most data, for some data that cannot be recovered by this method, if the disk level is not covered, you can first recover according to the os level method, refer to:extundeleteRecover Linux deleted files,If this method also fails to recover normally, you can try database disk fragmentation level recovery: MySQL drop database recovery (recovery method also applies to MySQL drop table, delete, truncate table)

mysql database is encrypted recovery

E-mail：chf.dba@gmail.com

Title: mysql database is encrypted recovery

Author: DATABASE SOS©All rights reserved [without my consent, it may not be reproduced in any form, otherwise there is the right to further legal responsibility.]

Some customers mysql database is encrypted, the encrypted information is as follows
20200129162256

Since it is the version of mysql 5.6, the default is the innodb engine, and the Innodb_file_per_table parameter is true by default, so the data exists for each table in a separate ibd file, allowing customers to provide the ibd encrypted file of the table that needs to be recovered
20200129162048

Achieve perfect data recovery through a series of low-level operations
1
2

If the Innodb_file_per_table parameter is false (defaults to false before 5.6), you need to restore through the ibdata file
If your database (oracle, mysql sql server) is unfortunately encrypted by Bitcoin, you can contact us
E-Mail:chf.dba@gmail.comProvide professional decryption recovery services.

MySQL ibd Recovery

E-mail：chf.dba@gmail.com

Title: MySQL ibd Recovery

Author: DATABASE SOS©All rights reserved [without my consent, it may not be reproduced in any form, otherwise there is the right to further legal responsibility.]

The ibd file is saved in mysql for some reason, but the table has been deleted or the frm file is damaged or the ibdata file is damaged / lost. This article simulates that in this case, ibd file recovery can be completed through mysql’s own technology.
Test environment mysql version 

mysql> select version ();
+ ----------- +
| version () |
+ ----------- +
| 5.6.25 |
+ ----------- +
1 row in set (0.00 sec)

mysql main parameters 

mysql> show variables like 'innodb_file_per_table';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| innodb_file_per_table | ON |
+ ----------------------- + ------- +
1 row in set (0.00 sec)
mysql> show variables like 'innodb_force_recovery';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| innodb_force_recovery | 0 |
+ ----------------------- + ------- +
1 row in set (0.00 sec)

The innodb_file_per_table parameter is on to enable each table to store a separate ibd file.The default range of the innodb_force_recovery parameter is 0

Test table situation 

mysql> use xifenfei;
Database changed
mysql> show tables;
+ ----------------------------- +
| Tables_in_xifenfei |
+ ----------------------------- +
| user_login |
+ ----------------------------- +
1 rows in set (0.00 sec)

mysql> select count (*) from user_login;
+ ---------- +
| count (*) |
+ ---------- +
| 48 |
+ ---------- +
1 row in set (0.02 sec)

mysql> desc user_login;
+ ------------ + -------------- + ------ + ----- + -------- -+ ------- +
Field | Type | Null | Key | Default | Extra |
+ ------------ + -------------- + ------ + ----- + -------- -+ ------- +
| ID | varchar (255) | NO | PRI | NULL | |
| ACCOUNT | varchar (255) | YES | | NULL | |
| LifeCycle | int (11) | YES | | NULL | |
| Name | varchar (255) | YES | | NULL | |
| Password | varchar (255) | YES | | NULL | |
| Role | varchar (255) | YES | | NULL | |
| UTime | varchar (255) | YES | | NULL | |
UserID | varchar (255) | YES | | NULL | |
| UserName | varchar (255) | YES | | NULL | |
UserStatus | int (11) | YES | | NULL | |
+ ------------ + -------------- + ------ + ----- + -------- -+ ------- +
10 rows in set (0.05 sec)

mysql> select * from user_login limit 1;
+ ---------------------------------- + --------- + ---- ------- + ----------- + ----------
------------------------ + ------ + ------------------ --- + --------------------------
-------- + ---------- + ------------ +
| ID | ACCOUNT | LifeCycle | Name | Password
                        | Role | UTime | UserID
        | UserName | UserStatus |
+ ---------------------------------- + --------- + ---- ------- + ----------- + ----------
------------------------ + ------ + ------------------ --- + --------------------------
-------- + ---------- + ------------ +
| 010d6c85a76c44cba80d07cbd8590bb2 | hyh | 0 | Hu Yuanhui | 698d51a19
d8a121ce581499d7b701668 | | 6 | | 2016-08-30 06:04:32 | 0fe3bc4dd9654687a4b85065e
d5cfee8 | NULL | 1 |
+ ---------------------------------- + --------- + ---- ------- + ----------- + ----------
------------------------ + ------ + ------------------ --- + --------------------------
-------- + ---------- + ------------ +
1 row in set (0.00 sec)

mysql> show create table user_login \ G;
*************************** 1. row *************
       Table: user_login
Create Table: CREATE TABLE `user_login` (
  `ID` varchar (255) NOT NULL,
  `ACCOUNT` varchar (255) DEFAULT NULL,
  `LifeCycle` int (11) DEFAULT NULL,
  `Name` varchar (255) DEFAULT NULL,
  `Password` varchar (255) DEFAULT NULL,
  `Role` varchar (255) DEFAULT NULL,
  `UTime` varchar (255) DEFAULT NULL,
  `UserID` varchar (255) DEFAULT NULL,
  `UserName` varchar (255) DEFAULT NULL,
  `UserStatus` int (11) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
1 row in set (0.00 sec)


mysql> show variables like 'datadir';
+ --------------- + --------------------------------- -------------- +
| Variable_name | Value |
+ --------------- + --------------------------------- -------------- +
datadir | D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ |
+ --------------- + --------------------------------- -------------- +
1 row in set (0.00 sec)

Back up ibd files 

C: \ Users \ XIFENFEI> dir D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei \ user_login.ibd
 The volume in drive D has no labels.
 The serial number of the volume is 4215-1F18

 D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei

2016-12-02 20:07 98,304 user_login.ibd
               1 file 98,304 bytes
               0 directories 78,789,591,040 available bytes
C: \ Users \ XIFENFEI> cp D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei \ user_login.ibd d: /
C: \ Users \ XIFENFEI> dir d: \ user_login.ibd
 The volume in drive D has no labels.
 The serial number of the volume is 4215-1F18

 d: \ directory

2016-12-25 23:15 98,304 user_login.ibd
               1 file 98,304 bytes
               0 directories 78,789,591,040 available bytes

Simulated delete table (ibd file is also deleted) 

mysql> drop table xifenfei.user_login;
Query OK, 0 rows affected (0.03 sec)


C: \ Users \ XIFENFEI> dir D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei \ user_login.ibd
 The volume in drive D has no labels.
 The serial number of the volume is 4215-1F18

 D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei

File not found

Create a new table 

mysql> CREATE TABLE `user_login` (
    -> `ID` varchar (255) NOT NULL,
    -> `ACCOUNT` varchar (255) DEFAULT NULL,
    -> `LifeCycle` int (11) DEFAULT NULL,
    -> `Name` varchar (255) DEFAULT NULL,
    -> `Password` varchar (255) DEFAULT NULL,
    -> `Role` varchar (255) DEFAULT NULL,
    -> `UTime` varchar (255) DEFAULT NULL,
    -> `UserID` varchar (255) DEFAULT NULL,
    -> `UserName` varchar (255) DEFAULT NULL,
    -> `UserStatus` int (11) DEFAULT NULL,
    -> PRIMARY KEY (`ID`)
    ->) ENGINE = InnoDB DEFAULT CHARSET = utf8;
Query OK, 0 rows affected (0.03 sec)

C: \ Users \ XIFENFEI> dir D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei \ user_login.ibd
 The volume in drive D has no labels.
 The serial number of the volume is 4215-1F18

 D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei

2016-12-25 23:19 98,304 user_login.ibd
               1 file 98,304 bytes
               0 directories 78,789,591,040 available bytes

mysql> select count (*) from xifenfei.user_login;
+ ---------- +
| count (*) |
+ ---------- +
| 0 |
+ ---------- +
1 row in set (0.00 sec)

Stop mysql and replace user_login.ibd 

C: \ Users \ XIFENFEI> dir D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei \ user_login.ibd
 The volume in drive D has no labels.
 The serial number of the volume is 4215-1F18

 D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei

2016-12-25 23:22 98,304 user_login.ibd
               1 file 98,304 bytes
               0 directories 78,787,141,632 bytes available

C: \ Users \ XIFENFEI> cp d: \ user_login.ibd D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei \ user_login.ibd
C: \ Users \ XIFENFEI> dir D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei \ user_login.ibd
 The volume in drive D has no labels.
 The serial number of the volume is 4215-1F18

 D: \ xifenfei \ mysql-5.6.25-winx64 \ data \ xifenfei

2016-12-02 20:07 98,304 user_login.ibd
               1 file 98,304 bytes
               0 directories 78,787,141,632 bytes available

Start the mysql service and query the database 

mysql> select count (*) from xifenfei.user_login;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> exit
Bye

C: \ Users \ XIFENFEI> mysql -uroot
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

mysql log error 

2016-12-25 23:31:07 11632 [Note] MySQL: ready for connections.
Version: '5.6.25' socket: '' port: 3306 MySQL Community Server (GPL)
InnoDB: Error: tablespace id is 56 in the data dictionary
InnoDB: but in file. \ Xifenfei \ user_login.ibd it is 47!
2016-12-25 23:31:31 2eb8 InnoDB: Assertion failure in thread 11960 in file fil0fil.cc line 796
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be

Obviously, because the replaced ibd file and the dictionary information of the page of the ibd file recorded in the database do not match, because the database cannot query the data normally, and mysql directly crashed the instance for security.

Resume operation 

mysql> show variables like 'innodb_force_recovery';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| innodb_force_recovery | 1 |
+ ----------------------- + ------- +
1 row in set (0.00 sec)
mysql> alter table xifenfei.user_login discard tablespace;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> alter table xifenfei.user_login import tablespace;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> select count (*) from xifenfei.user_login;
+ ---------- +
| count (*) |
+ ---------- +
| 48 |
+ ---------- +
1 row in set (0.00 sec)

mysql> select * from xifenfei.user_login limit 1;
+ ---------------------------------- + --------- + ---- ------- + ----------- + ----------
------------------------ + ------ + ------------------ --- + --------------------------
-------- + ---------- + ------------ +
| ID | ACCOUNT | LifeCycle | Name | Password
                        | Role | UTime | UserID
        | UserName | UserStatus |
+ ---------------------------------- + --------- + ---- ------- + ----------- + ----------
------------------------ + ------ + ------------------ --- + --------------------------
-------- + ---------- + ------------ +
| 010d6c85a76c44cba80d07cbd8590bb2 | hyh | 0 | Hu Yuanhui | 698d51a19
d8a121ce581499d7b701668 | | 6 | | 2016-08-30 06:04:32 | 0fe3bc4dd9654687a4b85065e
d5cfee8 | NULL | 1 |
+ ---------------------------------- + --------- + ---- ------- + ----------- + ----------
------------------------ + ------ + ------------------ --- + --------------------------
-------- + ---------- + ------------ +
1 row in set (0.00 sec)

After the discard tablespace and import tablespace operations provided by mysql, the table data can be completed.
mysql log 

2016-12-25 23:34:08 10464 [ERROR] InnoDB: Failed to find tablespace for table '"xifenfei". "User_login"' in the cache. Attempting to load the tablespace with space id 56.
2016-12-25 23:34:08 10464 [ERROR] InnoDB: In file '. \ Xifenfei \ user_login.ibd', tablespace id and flags are 47 and 0, but in the InnoDB data dictionary they are 56 and 0. Have you moved InnoDB .ibd files around without using the commands DISCARD TABLESPACE and IMPORT TABLESPACE? Please refer to http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2016-12-25 23:34:08 10464 [ERROR] InnoDB: Could not find a valid tablespace file for 'xifenfei / user_login'. See http://dev.mysql.com/doc/refman/5.6/en/innodb -troubleshooting-datadict.html for how to resolve the issue.
2016-12-25 23:34:08 30e8 InnoDB: cannot calculate statistics for table "xifenfei". "User_login" because the .ibd file is missing. For help, please refer to http://dev.mysql.com/doc /refman/5.6/en/innodb-troubleshooting.html
2016-12-25 23:34:08 10464 [ERROR] InnoDB: Cannot delete tablespace 56 because it is not found in the tablespace memory cache.
2016-12-25 23:34:08 10464 [Warning] InnoDB: Cannot delete tablespace 56 in DISCARD TABLESPACE. Tablespace not found
2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk
2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk-done!
2016-12-25 23:34:41 10464 [Note] InnoDB: Phase I-Update all pages
2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk
2016-12-25 23:34:41 10464 [Note] InnoDB: Sync to disk-done!
2016-12-25 23:34:41 10464 [Warning] InnoDB: Tablespace 'xifenfei / user_login' exists in the cache with id 47! = 56
2016-12-25 23:34:41 10464 [Warning] InnoDB: Freeing existing tablespace 'xifenfei / user_login' entry from the cache with id 56
2016-12-25 23:34:41 10464 [Note] InnoDB: Phase III-Flush changes to disk
2016-12-25 23:34:41 10464 [Note] InnoDB: Phase IV-Flush complete

The mysql log still reports that the page dictionary information does not match. But the data is already accessible, you can re-create the table through mysqldump export. If this method cannot be restored due to ibd damage, please refer to: MySQL drop database recovery (the recovery method is also applicable to MySQL drop table, delete, truncate table)

MySQL delete Recovery

E-mail：chf.dba@gmail.com

Title: MySQL delete Recovery

Author: DATABASE SOS©All rights reserved [without my consent, it may not be reproduced in any form, otherwise there is the right to further legal responsibility.]

In mysql (innodb engine), there are times when hesitant misoperation causes data in the table to be deleted, resulting in irreparable loss.This article simulates the perfect deletion in the case of the database being deleted by mistake. Of course, there may be a small amount in practice. It is not overwritten or cannot be recovered, but under the condition of little or no overwriting, the vast majority or even all of them can be recovered. Therefore, when misoperation occurs, the site should be protected as soon as possible to prevent irreparable overwriting caused by overwriting. Loss. During the test recovery process, due to mysql and operating system coding issues, tossed for a long time, thank you Lunar ‘s guidance
Create a mock table and insert data 

mysql> CREATE TABLE `sms_send_record_del` (
    ->   `messageId` varchar(30) NOT NULL,
    ->   `tokenId` varchar(20) NOT NULL,
    ->   `mobile` varchar(14) default NULL,
    ->   `msgFormat` int(1) NOT NULL,
    ->   `msgContent` varchar(1000) default NULL,
    ->   `scheduleDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    ->   `deliverState` int(1) default NULL,
    ->   `deliverdTime` timestamp NOT NULL default '0000-00-00 00:00:00',
    ->   PRIMARY KEY  (`messageId`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into sms_send_record_del select * from sms_send_record;
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> checksum table sms_send_record_del;
+---------------------------------+------------+
| Table                           | Checksum   |
+---------------------------------+------------+
| sms_service.sms_send_record_del | 2258631583 | 
+---------------------------------+------------+
1 row in set (0.00 sec)

mysql> checksum table sms_send_record;    
+-----------------------------+------------+
| Table                       | Checksum   |
+-----------------------------+------------+
| sms_service.sms_send_record | 2258631583 | 
+-----------------------------+------------+
1 row in set (0.00 sec)

mysql-delete-recovery-1

Determine the corresponding location of the innodb file 

mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ | 
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   | 
+-----------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:10M:autoextend | 
+-----------------------+------------------------+
1 row in set (0.00 sec)

Delete table records
Simulate an error operation and delete all data in the table by mistake

mysql> delete from sms_send_record_del;
Query OK, 11 rows affected (0.00 sec)

Parse ibdata1 file 

[root@web103 mysql_recovery]# ./stream_parser -f /var/lib/mysql/ibdata1 
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (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:         315712
time of last access:            1440599559 Wed Aug 26 22:32:39 2015
time of last modification:      1440601853 Wed Aug 26 23:10:53 2015
time of last status change:     1440601853 Wed Aug 26 23:10:53 2015
total size, in bytes:            161480704 (154.000 MiB)

Size to process:                 161480704 (154.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (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:         315712
time of last access:            1440599559 Wed Aug 26 22:32:39 2015
time of last modification:      1440601853 Wed Aug 26 23:10:53 2015
time of last status change:     1440601853 Wed Aug 26 23:10:53 2015
total size, in bytes:            161480704 (154.000 MiB)

Size to process:                 161480704 (154.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (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:         315712
time of last access:            1440599559 Wed Aug 26 22:32:39 2015
time of last modification:      1440601853 Wed Aug 26 23:10:53 2015
time of last status change:     1440601853 Wed Aug 26 23:10:53 2015
total size, in bytes:            161480704 (154.000 MiB)

Size to process:                 161480704 (154.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (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:         315712
time of last access:            1440599559 Wed Aug 26 22:32:39 2015
time of last modification:      1440601853 Wed Aug 26 23:10:53 2015
time of last status change:     1440601853 Wed Aug 26 23:10:53 2015
total size, in bytes:            161480704 (154.000 MiB)

Size to process:                 161480704 (154.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (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:         315712
Opening file: /var/lib/mysql/ibdata1
time of last access:            1440599559 Wed Aug 26 22:32:39 2015
time of last modification:      1440601853 Wed Aug 26 23:10:53 2015
time of last status change:     1440601853 Wed Aug 26 23:10:53 2015
File information:

total size, in bytes:            161480704 (154.000 MiB)

ID of device containing file:         2049
Size to process:                 161480704 (154.000 MiB)
Opening file: /var/lib/mysql/ibdata1
File information:

ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (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:         315712
time of last access:            1440599559 Wed Aug 26 22:32:39 2015
time of last modification:      1440601853 Wed Aug 26 23:10:53 2015
time of last status change:     1440601853 Wed Aug 26 23:10:53 2015
total size, in bytes:            161480704 (154.000 MiB)

Size to process:                 161480704 (154.000 MiB)
Opening file: /var/lib/mysql/ibdata1
inode number:                      1344553
protection:                         100660 (regular file)
number of hard links:                    1
user ID of owner:                       27
group ID of owner:                      27
device ID (if special file):             0
File information:

blocksize for filesystem I/O:         4096
number of blocks allocated:         315712
ID of device containing file:         2049
inode number:                      1344553
protection:                         100660 (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:         315712
time of last access:            1440599559 Wed Aug 26 22:32:39 2015
time of last modification:      1440601853 Wed Aug 26 23:10:53 2015
time of last status change:     1440601853 Wed Aug 26 23:10:53 2015
total size, in bytes:            161480704 (154.000 MiB)

Size to process:                 161480704 (154.000 MiB)
time of last access:            1440601884 Wed Aug 26 23:11:24 2015
time of last modification:      1440601853 Wed Aug 26 23:10:53 2015
time of last status change:     1440601853 Wed Aug 26 23:10:53 2015
total size, in bytes:            161480704 (154.000 MiB)

Size to process:                 161480704 (154.000 MiB)
All workers finished in 0 sec

Analysis data dictionary 

mysql> show tables
    -> ;
+----------------+
| Tables_in_test |
+----------------+
| SYS_COLUMNS    | 
| SYS_FIELDS     | 
| SYS_INDEXES    | 
| SYS_TABLES     | 
+----------------+
4 rows in set (0.00 sec)

mysql> select * from SYS_TABLES;
+----------------------------------------+----+-------------+------+--------+---------+--------------+-------+
| NAME                                   | ID | N_COLS      | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+----------------------------------------+----+-------------+------+--------+---------+--------------+-------+
| recover/t_delete                       | 74 |           2 |    1 |      0 |       0 |              |     0 | 
| recover/t_delete1                      | 84 |           2 |    1 |      0 |       0 |              |     0 | 
| recover/t_xifenfei                     | 75 |           2 |    1 |      0 |       0 |              |     0 | 
| recover/zx_users                       | 89 |          85 |    1 |      0 |       0 |              |     0 | 
| sms_service/sms_send_record            | 36 |           8 |    1 |      0 |       0 |              |     0 | 
| sms_service/sms_send_record_del        | 90 |           8 |    1 |      0 |       0 |              |     0 | 
| SYS_FOREIGN                            | 11 | -2147483644 |    1 |      0 |       0 |              |     0 | 
| SYS_FOREIGN_COLS                       | 12 | -2147483644 |    1 |      0 |       0 |              |     0 | 
| test/SYS_COLUMNS                       | 86 |           7 |    1 |      0 |       0 |              |     0 | 
| test/SYS_FIELDS                        | 88 |           3 |    1 |      0 |       0 |              |     0 | 
| test/SYS_INDEXES                       | 87 |           7 |    1 |      0 |       0 |              |     0 | 
| test/SYS_TABLES                        | 85 |           8 |    1 |      0 |       0 |              |     0 | 
| test/zx_users                          | 43 |          85 |    1 |      0 |       0 |              |     0 | 
| xifenfei/t_delete                      | 44 |           8 |    1 |      0 |       0 |              |     0 | 
| xifenfei/t_xifenfei                    | 59 |           2 |    1 |      0 |       0 |              |     0 | 
+----------------------------------------+----+-------------+------+--------+---------+--------------+-------+
39 rows in set (0.00 sec)

mysql> select * from SYS_INDEXES WHERE TABLE_ID=90;
+----------+-----+---------+----------+------+-------+---------+
| TABLE_ID | ID  | NAME    | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+-----+---------+----------+------+-------+---------+
|       90 | 110 | PRIMARY |        1 |    3 |     0 |    2955 | 
+----------+-----+---------+----------+------+-------+---------+
1 row in set (0.00 sec)

Retrieve deleted records 

[root@web103 mysql_recovery]# ./c_parser -5Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000110.page \
[root@web103 mysql_recovery]#    -t dictionary/sms_send_record_del.sql >/tmp/t_1.txt 2>/tmp/t_1.sql

mysql-delete-recovery-2

Load data and verify

mysql> use sms_service;
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> source /tmp/t_1.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 11 rows affected, 8 warnings (0.01 sec)
Records: 11  Deleted: 0  Skipped: 0  Warnings: 8

mysql> checksum table sms_send_record_del;
+---------------------------------+------------+
| Table                           | Checksum   |
+---------------------------------+------------+
| sms_service.sms_send_record_del | 2258631583 | 
+---------------------------------+------------+
1 row in set (0.00 sec)

When mishandling occurs, please protect the site as much as possible to prevent irreparable losses caused by coverage.

MySQL drop database Recovery

E-mail：chf.dba@gmail.com

Title: MySQL drop database Recovery

Author: DATABASE SOS©All rights reserved [without my consent, it may not be reproduced in any form, otherwise there is the right to further legal responsibility.]

I received a network service request last night. I accidentally clicked the clear data function on my product software. The MySQL database was dropped directly. No backup was made before, but after the failure, they immediately sealed the site and backed up the ibdata1 file. After receiving the request, by letting it send the ibdata1 file to it, analyze and restore it through the MySQL recovery tool. After 6 hours of processing, the core data 0 is basically lost.
Parse ibdata1 (innodb file)

[root@localhost recovery_MySQL]# ./stream_parser -f /tmp/ibdata1
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
Opening file: /tmp/ibdata1
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
File information:

time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 total size, in bytes:             18874368 (18.000 MiB)

(regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Opening file: /tmp/ibdata1
File information:

Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
number of hard links:                    1
user ID of owner:                        0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
group ID of owner:                       0
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
total size, in bytes:             18874368 (18.000 MiB)

Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
group ID of owner:                       0
device ID (if special file):             0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
blocksize for filesystem I/O:         4096
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
number of blocks allocated:          36920
Opening file: /tmp/ibdata1
total size, in bytes:             18874368 (18.000 MiB)

File information:

Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
blocksize for filesystem I/O:         4096
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
number of blocks allocated:          36920
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
Opening file: /tmp/ibdata1
total size, in bytes:             18874368 (18.000 MiB)

File information:

Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
Opening file: /tmp/ibdata1
File information:

total size, in bytes:             18874368 (18.000 MiB)

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 Size to process:                  18874368 (18.000 MiB)
(regular file)
number of hard links:                    1
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
user ID of owner:                        0
group ID of owner:                       0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
Opening file: /tmp/ibdata1
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
device ID (if special file):             0
File information:

blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
total size, in bytes:             18874368 (18.000 MiB)

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
Size to process:                  18874368 (18.000 MiB)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
Opening file: /tmp/ibdata1
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
File information:

time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
ID of device containing file:         2055
total size, in bytes:             18874368 (18.000 MiB)

inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
Size to process:                  18874368 (18.000 MiB)
group ID of owner:                       0
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
device ID (if special file):             0
blocksize for filesystem I/O:         4096
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
number of blocks allocated:          36920
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Opening file: /tmp/ibdata1
Size to process:                  18874368 (18.000 MiB)
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
user ID of owner:                        0
group ID of owner:                       0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
device ID (if special file):             0
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
total size, in bytes:             18874368 (18.000 MiB)

Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
device ID (if special file):             0
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
blocksize for filesystem I/O:         4096
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
number of blocks allocated:          36920
total size, in bytes:             18874368 (18.000 MiB)

Opening file: /tmp/ibdata1
File information:

Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Opening file: /tmp/ibdata1
File information:

Size to process:                  18874368 (18.000 MiB)
ID of device containing file:         2055
inode number:                        97257
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
protection:                         100644 (regular file)
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
number of hard links:                    1
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
total size, in bytes:             18874368 (18.000 MiB)

number of blocks allocated:          36920
Size to process:                  18874368 (18.000 MiB)
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Size to process:                  18874368 (18.000 MiB)
Opening file: /tmp/ibdata1
File information:

ID of device containing file:         2055
inode number:                        97257
protection:                         100644 (regular file)
number of hard links:                    1
user ID of owner:                        0
group ID of owner:                       0
device ID (if special file):             0
blocksize for filesystem I/O:         4096
number of blocks allocated:          36920
time of last access:            1440083236 Thu Aug 20 23:07:16 2015
time of last modification:      1440062236 Thu Aug 20 17:17:16 2015
time of last status change:     1440081904 Thu Aug 20 22:45:04 2015
total size, in bytes:             18874368 (18.000 MiB)

Size to process:                  18874368 (18.000 MiB)
All workers finished in 0 sec

[root@localhost recovery_MySQL]# cd pages-ibdata1
[root@localhost pages-ibdata1]# ls -l
total 16
drwxr-xr-x 2 root root 4096 Aug 20 22:53 FIL_PAGE_INDEX
drwxr-xr-x 2 root root 4096 Aug 20 22:53 FIL_PAGE_TYPE_BLOB
[root@localhost pages-ibdata1]# cd FIL_PAGE_INDEX
[root@localhost FIL_PAGE_INDEX]# ls -l
total 9924
-rw-r--r-- 1 root root   32768 Aug 20 22:53 0000000000000001.page
-rw-r--r-- 1 root root  278528 Aug 20 22:53 0000000000000002.page
-rw-r--r-- 1 root root   32768 Aug 20 22:53 0000000000000003.page
-rw-r--r-- 1 root root   32768 Aug 20 22:53 0000000000000004.page
-rw-r--r-- 1 root root   32768 Aug 20 22:53 0000000000000005.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000011.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000012.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000013.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000014.page
-rw-r--r-- 1 root root 2883584 Aug 20 22:53 0000000000000065.page
-rw-r--r-- 1 root root  475136 Aug 20 22:53 0000000000000066.page
-rw-r--r-- 1 root root  737280 Aug 20 22:53 0000000000000067.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000068.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000069.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000070.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000071.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000072.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000073.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000074.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000075.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000076.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 0000000000000077.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000078.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000079.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000080.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000081.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000082.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000083.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000084.page
-rw-r--r-- 1 root root  753664 Aug 20 22:53 0000000000000085.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000086.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000087.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000088.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000089.page
-rw-r--r-- 1 root root  901120 Aug 20 22:53 0000000000000110.page
-rw-r--r-- 1 root root 2097152 Aug 20 22:53 0000000000000115.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000116.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000117.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000118.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000119.page
-rw-r--r-- 1 root root   49152 Aug 20 22:53 0000000000000120.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000121.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000122.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000123.page
-rw-r--r-- 1 root root   98304 Aug 20 22:53 0000000000000124.page
-rw-r--r-- 1 root root   16384 Aug 20 22:53 18446744069414584320.page
[root@localhost FIL_PAGE_INDEX]#

Analyze the data dictionary

[root@localhost recovery_MySQL]# ./recover_dictionary.sh

Generating dictionary tables dumps... OK
Creating test database ... Warning: Using a password on the command line interface can be insecure.
OK
Creating dictionary tables in database test:
SYS_TABLES ... Warning: Using a password on the command line interface can be insecure.
OK
SYS_COLUMNS ... Warning: Using a password on the command line interface can be insecure.
OK
SYS_INDEXES ... Warning: Using a password on the command line interface can be insecure.
OK
SYS_FIELDS ... Warning: Using a password on the command line interface can be insecure.
OK
All OK
Loading dictionary tables data:
SYS_TABLES ... Warning: Using a password on the command line interface can be insecure.
34 recs OK
SYS_COLUMNS ... Warning: Using a password on the command line interface can be insecure.
3061 recs OK
SYS_INDEXES ... Warning: Using a password on the command line interface can be insecure.
206 recs OK
SYS_FIELDS ... Warning: Using a password on the command line interface can be insecure.
206 recs OK
All OK

Regarding MySQL data dictionary recovery, you can also refer to another article: Extract MySQL data dictionary directly using tools

View records in the database 

[root@localhost recovery_MySQL]# MySQL -uroot -p123456
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 34954
Server version: 5.6.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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 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> show tables;
+----------------+
| Tables_in_test |
+----------------+
| SYS_COLUMNS    |
| SYS_FIELDS     |
| SYS_INDEXES    |
| SYS_TABLES     |
+----------------+
4 rows in set (0.00 sec)

MySQL> SELECT NAME,ID from SYS_TABLES;
+-------------------------+----+
| NAME                    | ID |
+-------------------------+----+
| cnywmcom/zx_account_log | 31 |
| cnywmcom/zx_area_code   | 32 |
| cnywmcom/zx_goods_group | 33 |
| cnywmcom/zx_manage_type | 34 |
| cnywmcom/zx_order_goods | 35 |
| cnywmcom/zx_order_info  | 36 |
| cnywmcom/zx_param       | 37 |
| cnywmcom/zx_users       | 46 |
| cnywmcom/zx_user_type   | 38 |
| SYS_FOREIGN             | 11 |
| SYS_FOREIGN_COLS        | 12 |
+-------------------------+----+
11 rows in set (0.00 sec)

MySQL> select * from SYS_INDEXES;
+----------+-----+-----------------+----------+------+-------+---------+
| TABLE_ID | ID  | NAME            | N_FIELDS | TYPE | SPACE | PAGE_NO |
+----------+-----+-----------------+----------+------+-------+---------+
|       11 |  11 | ID_IND          |        1 |    3 |     0 |      46 |
|       11 |  12 | FOR_IND         |        1 |    0 |     0 |      47 |
|       11 |  13 | REF_IND         |        1 |    0 |     0 |      48 |
|       12 |  14 | ID_IND          |        2 |    3 |     0 |      49 |
|       31 |  65 | PRIMARY         |        1 |    3 |     0 |      50 |
|       31 |  66 | user_id         |        1 |    0 |     0 |     216 |
|       32 |  67 | PRIMARY         |        1 |    3 |     0 |      52 |
|       33 |  68 | PRIMARY         |        1 |    3 |     0 |     205 |
|       34 |  69 | PRIMARY         |        1 |    3 |     0 |      54 |
|       35 |  70 | PRIMARY         |        1 |    3 |     0 |      55 |
|       35 |  71 | order_id        |        1 |    0 |     0 |      56 |
|       35 |  72 | goods_id        |        1 |    0 |     0 |      57 |
|       36 |  73 | PRIMARY         |        1 |    3 |     0 |      59 |
|       36 |  74 | order_sn        |        1 |    2 |     0 |      60 |
|       36 |  75 | user_id         |        1 |    0 |     0 |      61 |
|       36 |  76 | order_status    |        1 |    0 |     0 |      62 |
|       36 |  77 | shipping_status |        1 |    0 |     0 |      63 |
|       36 |  78 | pay_status      |        1 |    0 |     0 |     192 |
|       36 |  79 | shipping_id     |        1 |    0 |     0 |     193 |
|       36 |  80 | pay_id          |        1 |    0 |     0 |     194 |
|       36 |  81 | extension_code  |        2 |    0 |     0 |     195 |
|       36 |  82 | agency_id       |        1 |    0 |     0 |     203 |
|       37 |  83 | PRIMARY         |        1 |    3 |     0 |     196 |
|       38 |  84 | PRIMARY         |        1 |    3 |     0 |     206 |
|       46 | 120 | PRIMARY         |        1 |    3 |     0 |      58 |
|       46 | 121 | user_name       |        1 |    2 |     0 |     256 |
|       46 | 122 | email           |        1 |    0 |     0 |     257 |
|       46 | 123 | parent_id       |        1 |    0 |     0 |     258 |
|       46 | 124 | flag            |        1 |    0 |     0 |     259 |
+----------+-----+-----------------+----------+------+-------+---------+
29 rows in set (0.00 sec)

Through this, we can clearly know the number of pages of the table we need to restore. Here is an example to restore the table cnywmcom.zx_account_log, we need to analyze page 65

Analysis table recovery 

[root@localhost recovery_MySQL]# ./c_parser -5f pages-ibdata1/FIL_PAGE_INDEX/0000000000000065.page -t dictionary/zx_account_log.sql|more
-- Page id: 223, Format: COMPACT, Records list: Invalid, Expected records: (0 7)
0B0C2000101C    55000230000000  zx_account_log  2979181 270888  "0"0"0.00"      "0.00"  "0.00"  2147483649      ""      0       ""      "0.00"  "-10643256.27"  "06."   "06."   "0782
7251.51"        "0.5"   "0.5"   "0.5"   "0.5"
000000042228    00000001040135  zx_account_log  561     1       "0.00"  "0.00"  "0.00"  "1.00"  "0.00"  1436840718      "会员注册"      99      "13818289939"   "1.00"  "0.00"  "0.00"  "0.00
"       "0.00"  "0.00"  "0.00"  "0.00"  "0.00"
000000042228    0000000104015A  zx_account_log  562     136     "0.00"  "0.00"  "0.00"  "1.00"  "0.00"  1436841067      "会员注册"      99      "17828025855"   "1.00"  "0.00"  "0.00"  "0.00
"       "0.00"  "0.00"  "0.00"  "0.00"  "0.00"
000000042228    0000000104017F  zx_account_log  563     137     "0.00"  "0.00"  "0.00"  "1.00"  "0.00"  1436842239      "会员注册"      99      "18617157667"   "1.00"  "0.00"  "0.00"  "0.00
"       "0.00"  "0.00"  "0.00"  "0.00"  "0.00"
000000042228    000000010401A4  zx_account_log  564     139     "0.00"  "0.00"  "0.00"  "1.00"  "0.00"  1436846738      "会员注册"      99      "18113070688"   "1.00"  "0.00"  "0.00"  "0.00
"       "0.00"  "0.00"  "0.00"  "0.00"  "0.00"
000000042228    000000010401C9  zx_account_log  565     1       "0.00"  "0.00"  "0.00"  "1.00"  "0.00"  1436848523      "会员注册"      99      "13623646573"   "1.00"  "0.00"  "0.00"  "0.00
"       "0.00"  "0.00"  "0.00"  "0.00"  "0.00"

At this point, the table’s data has been recovered. The remaining task is to save the data as a file and import it into the new database. Then process the related tables in turn. This completes the recovery of the drop database. During the recovery process, many encounters Pit, for example, some records cannot be found in the corresponding page, and additional processing is needed. For garbled characters, further processing is required. Through a series of recovery, all core tables are restored to the customer, and core data is lost. By this time, Recovery has proven that we are not only an industry leader in Oracle recovery. We also have a foothold in the various failures of the MySQL database. If there is a recovery of the MySQL database (accidentally deleting data / delete, accidentally deleting tables / drop tables, Truncate table / truncate table by mistake, drop database / drop database, MySQL cannot start, ibdata1 file is damaged / lost, etc. To reduce your losses:

E-Mail:chf.dba@gmail.com