MySQL drop database恢復(恢復方法同樣適用MySQL drop table,delete,truncate table)
來自： http://ourmysql.com/archives/1425
昨天晚上接到一個網絡服務請求，由于不小心點擊了自己產品軟件上面的清空數據功能(這個工具確實需要小心,在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]#</pre>
分析數據字典
[root@localhost recovery_MySQL]# ./recover_dictionary.shGenerating 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</pre>
關于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)</pre>
通過這里，我們就可以明確的知道，我們需要恢復的表的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
</div>