MySQL勒索恢复

联系:手机/微信(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:MySQL勒索恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

最近遇到几个mysql数据库被黑客删除库,并且留下比特币勒索信息在每个库的WARNING表中

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)

大概的意思就是:我们已经把你的数据库备份,您交给我们0.06个比特币,我们把数据给你,如果10天之内我们没有收到款,即将把数据库给公开或者作为其他用途.根据我们以往接触的朋友经验,付款之后数据库也不会给你(很可能黑客根本就没有备份数据库,只是删除了数据库然后勒索比特币.

对于这类情况,通过分析,确认黑客是删除了数据库,在没有覆盖的情况下,我们可以对其数据进行恢复,处理类似:MySQL drop database恢复(恢复方法同样适用MySQL drop table,delete,truncate table)最大限度缓解因为数据库被破坏带来的损失.
20200303125417
如果您也遭遇到该问题,请保护现场,不要导入备份数据库,不要对数据所在分区进行写操作(现场保护的越好,数据恢复效果越好),对相关磁盘进行镜像,防止二次破坏.我们可以提供专业的mysql恢复服务,为您减少损失.

mysql 数据库目录被删除恢复

联系:手机/微信(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:mysql 数据库目录被删除恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

接到朋友请求,把mysql数据库的datadir目录给删除了,数据库目前还处于运行状态,但是很多操作已经无法正常进行
数据库可以登录,但是已经看不到任何业务数据库,可以结合表名查询

[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)

数据无法导出(into outfile不行是由于secure-file-priv参数默认导致)

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

因为mysql没有crash,因此相关文件已经存在(没有被真正删除)
rm_mysql_ibd


通过此类方法恢复相关数据文件到新服务器上,然后尝试启动数据库,发现无法正常启动,有部分文件丢失,最后对单独ibd单独处理进行恢复,具体参考:[MySQL异常恢复]mysql ibd文件恢复,实现绝大多数数据的恢复,对于部分无法通过此类方法恢复出来的数据,在磁盘级别没有覆盖的情况下,可以先按照os层面方法恢复,参考:extundelete恢复Linux被删除文件,如果此类方法也无法正常恢复,可以尝试数据库磁盘碎片级别恢复:MySQL drop database恢复(恢复方法同样适用MySQL drop table,delete,truncate table)

mysql数据库被加密恢复

联系:手机/微信(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:mysql数据库被加密恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有客户mysql数据库被加密,加密信息如下
20200129162256


由于是mysql 5.6的版本,默认情况innodb引擎,Innodb_file_per_table参数默认为true,因此数据为每个表存在为一个单独的ibd文件中,让客户提供需要恢复的表的ibd被加密文件
20200129162048

通过一系列底层操作,实现数据完美恢复
1
2

如果是Innodb_file_per_table参数为false(5.6之前版本默认为false),需要通过ibdata文件进行恢复
如果您的数据库(oracle,mysql sql server)不幸被比特币加密,可以联系我们
Tel/微信:13429648788    Q Q:107644445 QQ咨询惜分飞    E-Mail:dba@xifenfei.com提供专业的解密恢复服务.

mysql drop table 恢复

对于MySQL数据库的innodb引擎的数据库中,由于误操作删除表,或者由于sqldump自动生成语句含drop table create table语句导致数据丢失,在没有覆盖的情况下,可以实现完美恢复
创建测试表

mysql> CREATE TABLE recover.`t_drop` (
    ->   `messageId` varchar(30) NOT NULL,
    ->   `msgContent` varchar(1000) default NULL,
    ->   `scheduleDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    ->   `deliverState` int(1) default NULL,
    ->   PRIMARY KEY  (`messageId`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into recover.t_drop select messageId,msgContent,scheduleDate,deliverState from sms_send_record;
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> select * from recover.`t_drop`;
+--------------------+----------------------------------------------------------------------------------+---------------------+--------------+
| messageId          | msgContent                                                                       | scheduleDate        | deliverState |
+--------------------+----------------------------------------------------------------------------------+---------------------+--------------+
| 10235259536125650  | 尊敬的用户您好:您的手机验证码为474851如非本人操作,请拨打奥斯卡客服:400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 10235353811295807  | 尊敬的用户您好:您的手机验证码为444632如非本人操作,请拨打奥斯卡客服:400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 102354211240398235 | 尊敬的用户您好:您的手机验证码为478503如非本人操作,请拨打奥斯卡客服:400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 102354554052884567 | 尊敬的用户您好:您的手机验证码为216825如非本人操作,请拨打奥斯卡客服:400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 132213454294519126 | 尊敬的用户您好:您的手机验证码为854812如非本人操作,请拨打奥斯卡客服:400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 82329022242584577  | 尊敬的用户您好:您的手机验证码为253127如非本人操作,请拨打奥斯卡客服:400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 82329022242584581  | 尊敬的用户您好:您的手机验证码为253127如非本人操作,请拨打奥斯卡客服:400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 8233400415607376   | 尊敬的用户您好:您的手机验证码为338470如非本人操作,请拨打奥斯卡客服:400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 82334502212106951  | 尊敬的用户您好:您的手机验证码为916515如非本人操作,请拨打奥斯卡客服:400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 82339012756833423  | 尊敬的用户您好:您的手机验证码为396108如非本人操作,请拨打奥斯卡客服:400-620-757    | 2010-01-01 00:00:00 |            0 | 
| 8234322198577796   | 尊敬的用户您好:您的手机验证码为935297如非本人操作,请拨打奥斯卡客服:400-620-757    | 2010-01-01 00:00:00 |            0 | 
+--------------------+----------------------------------------------------------------------------------+---------------------+--------------+
11 rows in set (0.00 sec)

mysql> checksum table t_drop;
+-----------------+-----------+
| Table           | Checksum  |
+-----------------+-----------+
| recover.t_drop  | 920719058 | 
+-----------------+-----------+
1 row in set (0.00 sec)

删除测试表,后续用来恢复

mysql> drop table recover.t_drop;
Query OK, 0 rows affected (0.00 sec)

解析ibdata文件

[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:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.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:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.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:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.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:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.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:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.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:         463312
time of last access:            1440825416 Sat Aug 29 13:16:56 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.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:         463312
time of last access:            1440855928 Sat Aug 29 21:45:28 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.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:         463312
time of last access:            1440855928 Sat Aug 29 21:45:28 2015
time of last modification:      1440855835 Sat Aug 29 21:43:55 2015
time of last status change:     1440855835 Sat Aug 29 21:43:55 2015
total size, in bytes:            236978176 (226.000 MiB)

Size to process:                 236978176 (226.000 MiB)
Worker(1): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(0): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(4): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(3): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(2): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(7): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(5): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
Worker(6): 56.64% done. 2015-08-29 21:45:30 ETA(in 00:00:01). Processing speed: 8.000 MiB/sec
All workers finished in 1 sec

恢复mysql字典

[root@web103 mysql_recovery]# ./recover_dictionary.sh 
Generating dictionary tables dumps... OK
Creating test database ... OK
Creating dictionary tables in database test:
SYS_TABLES ... OK
SYS_COLUMNS ... OK
SYS_INDEXES ... OK
SYS_FIELDS ... OK
All OK
Loading dictionary tables data:
SYS_TABLES ... 162 recs OK
SYS_COLUMNS ... 1247 recs OK
SYS_INDEXES ... 216 recs OK
SYS_FIELDS ... 411 recs OK
All OK
[root@web103 mysql_recovery]# mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10162
Server version: 5.0.95 Source distribution

Copyright (c) 2000, 2011, 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> select * from SYS_TABLES WHERE name like 'recover%';
+-------------------------+-----+--------+------+--------+---------+--------------+-------+
| NAME                    | ID  | N_COLS | TYPE | MIX_ID | MIX_LEN | CLUSTER_NAME | SPACE |
+-------------------------+-----+--------+------+--------+---------+--------------+-------+
| recover/#sql-64a9_1f6d  | 120 |      8 |    1 |      0 |       0 |              |     0 | 
| recover/#sql2-64a9-1f6d | 115 |      8 |    1 |      0 |       0 |              |     0 | 
| recover/t_delete        |  74 |      2 |    1 |      0 |       0 |              |     0 | 
| recover/t_delete1       |  84 |      2 |    1 |      0 |       0 |              |     0 | 
| recover/t_drop          | 125 |      4 |    1 |      0 |       0 |              |     0 | 
| recover/t_truncate      | 120 |      8 |    1 |      0 |       0 |              |     0 | 
| recover/t_xifenfei      |  75 |      2 |    1 |      0 |       0 |              |     0 | 
| recover/zx_users        |  89 |     85 |    1 |      0 |       0 |              |     0 | 
+-------------------------+-----+--------+------+--------+---------+--------------+-------+
8 rows in set (0.00 sec)

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

恢复被删除表记录

[root@web103 mysql_recovery]# ./c_parser -5f pages-ibdata1/FIL_PAGE_INDEX/0000000000000142.page  -t dictionary/t_drop.sql >dumps/default/t_drop 2>2.sql
[root@web103 mysql_recovery]# more dumps/default/t_drop
-- Page id: 9860, Format: COMPACT, Records list: Valid, Expected records: (11 11)
00000099F9F2    80000026800110  t_drop  "10235259536125650"     "尊敬的用户您好:您的手机验证码为474851如非本人操作,请拨打奥斯卡客服:400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    80000026800129  t_drop  "10235353811295807"     "尊敬的用户您好:您的手机验证码为444632如非本人操作,请拨打奥斯卡客服:400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    80000026800142  t_drop  "102354211240398235"    "尊敬的用户您好:您的手机验证码为478503如非本人操作,请拨打奥斯卡客服:400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    8000002680015C  t_drop  "102354554052884567"    "尊敬的用户您好:您的手机验证码为216825如非本人操作,请拨打奥斯卡客服:400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    80000026800176  t_drop  "132213454294519126"    "尊敬的用户您好:您的手机验证码为854812如非本人操作,请拨打奥斯卡客服:400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    80000026800190  t_drop  "82329022242584577"     "尊敬的用户您好:您的手机验证码为253127如非本人操作,请拨打奥斯卡客服:400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    800000268001A9  t_drop  "82329022242584581"     "尊敬的用户您好:您的手机验证码为253127如非本人操作,请拨打奥斯卡客服:400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    800000268001C2  t_drop  "8233400415607376"      "尊敬的用户您好:您的手机验证码为338470如非本人操作,请拨打奥斯卡客服:400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    800000268001DA  t_drop  "82334502212106951"     "尊敬的用户您好:您的手机验证码为916515如非本人操作,请拨打奥斯卡客服:400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    800000268001F3  t_drop  "82339012756833423"     "尊敬的用户您好:您的手机验证码为396108如非本人操作,请拨打奥斯卡客服:400-620-7575。"  "2010-01-01 00:00:00"   0
00000099F9F2    8000002680020C  t_drop  "8234322198577796"      "尊敬的用户您好:您的手机验证码为935297如非本人操作,请拨打奥斯卡客服:400-620-7575。"  "2010-01-01 00:00:00"   0
-- Page id: 9860, Found records: 11, Lost records: NO, Leaf page: YES

恢复数据入库

mysql> source dictionary/t_drop.sql
Query OK, 0 rows affected (0.00 sec)

mysql> source 2.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 11 rows affected (0.00 sec)
Records: 11  Deleted: 0  Skipped: 0  Warnings: 0

mysql> checksum table t_drop;
+-----------------+-----------+
| Table           | Checksum  |
+-----------------+-----------+
| recover.t_drop  | 920719058 | 
+-----------------+-----------+
1 row in set (0.00 sec)

至此实现删除数据完美恢复

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)

mysql-delete-recovery-1


确定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-delete-recovery-2


加载数据并验证

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)

发生误操作之时,请尽可能保护现场,防止覆盖导致无可挽回的损失.