昨天晚上接到一个网络服务请求，由于不小心点击了自己产品软件上面的清空数据功能(这个工具确实需要小心,在Oracle恢复案例中,也有xx企业erp该功能导致数据被删除请求恢复)，导致MySQL数据库被直接drop database掉了，之前没有做任何备份，只是发生故障之后，他们立即封存现场，备份出来了ibdata1文件。接到请求之后，通过让其把ibdata1文件发给过来，通过MySQL recovery工具进行分析恢复，经过6个小时的处理，基本上实现核心数据0丢失.

解析ibdata1(innodb文件)

[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]#

分析数据字典

[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

关于MySQL数据字典恢复,也可以参考另外一篇文章: 使用工具直接抽取MySQL数据字典

查看数据库中记录情况

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

通过这里，我们就可以明确的知道，我们需要恢复的表的page是多少了，这里举例说明恢复表cnywmcom.zx_account_log,我们需要去分析65号page

分析表恢复

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

至此该表的数据已经恢复出来,剩下任务就是把数据保存为文件,并且导入到新库中.然后依次处理相关表.至此完成这次drop database的恢复.在恢复过程中,遇到不少坑,比如有些记录在对应的page中无法找到,需要进行额外处理.对于乱码问题需要进一步处理等.通过一系列的恢复,给客户恢复了所有核心表,实现核心数据0丢失.通过这次的恢复,已经证明我们不光是Oracle恢复中的行业强者,在MySQL数据库的各种故障中,我们也有立足之地.如果有MySQL数据库的恢复(误删除数据/delete,误删除表/drop table,误截断表/truncate table,误删除数据库/drop database,MySQL不能启动,ibdata1文件损坏/丢失等MySQL各种恢复)请求,如果您遇到MySQL恢复问题无法自行解决,请联系我们提供专业服务,最大程度减小您的损失：

Phone:13429648788    Q Q: 107644445 E-Mail:dba@xifenfei.com