MySQL drop database恢复(恢复方法同样适用MySQL drop table,delete,truncate table)
oNanyou
9年前
来自： 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]#
分析数据字典
[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恢复问题无法自行解决,请联系我们提供专业服务,最大程度减小您的损失：