MySQL delete Recovery – Database SOS

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)

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

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.