联系：手机/微信(+86 17813235971) QQ(107644445)
标题：[MySQL异常恢复]mysql delete 数据恢复
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在mysql(innodb引擎)中,有些时候犹豫误操作导致表中数据被删除,从而导致不可挽回的损失,本文模拟在数据库被误delete的情况下,实现较为完美删除,当然在实际中可能有少量不覆盖或者无法恢复回来,但是在覆盖不多或者未覆盖的情况下,可以实现绝大多数甚至全部恢复.因此在发生误操作时候,应当第一时间保护现场,尽可能防止复写导致不可挽回的损失.在测试恢复过程中,由于mysql和操作系统编码问题,折腾了很久，感谢Lunar的指点
创建模拟表并插入数据
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)
确定innodb文件对应位置
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)
删除表记录
模拟错误操作,误删除表所有数据
mysql> delete from sms_send_record_del; Query OK, 11 rows affected (0.00 sec)
解析ibdata1文件
[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
分析数据字典
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)
找回被删除记录
[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> 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)
发生误操作之时,请尽可能保护现场,防止覆盖导致无可挽回的损失.