E-mail：chf.dba@gmail.com
Title: MySQL drop database Recovery
Author: DATABASE SOS©All rights reserved [without my consent, it may not be reproduced in any form, otherwise there is the right to further legal responsibility.]
I received a network service request last night. I accidentally clicked the clear data function on my product software. The MySQL database was dropped directly. No backup was made before, but after the failure, they immediately sealed the site and backed up the ibdata1 file. After receiving the request, by letting it send the ibdata1 file to it, analyze and restore it through the MySQL recovery tool. After 6 hours of processing, the core data 0 is basically lost.
Parse ibdata1 (innodb file)
[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]#
Analyze the data dictionary
[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
Regarding MySQL data dictionary recovery, you can also refer to another article: Extract MySQL data dictionary directly using tools
View records in the database
[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)
Through this, we can clearly know the number of pages of the table we need to restore. Here is an example to restore the table cnywmcom.zx_account_log, we need to analyze page 65
Analysis table recovery
[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"
At this point, the table’s data has been recovered. The remaining task is to save the data as a file and import it into the new database. Then process the related tables in turn. This completes the recovery of the drop database. During the recovery process, many encounters Pit, for example, some records cannot be found in the corresponding page, and additional processing is needed. For garbled characters, further processing is required. Through a series of recovery, all core tables are restored to the customer, and core data is lost. By this time, Recovery has proven that we are not only an industry leader in Oracle recovery. We also have a foothold in the various failures of the MySQL database. If there is a recovery of the MySQL database (accidentally deleting data / delete, accidentally deleting tables / drop tables, Truncate table / truncate table by mistake, drop database / drop database, MySQL cannot start, ibdata1 file is damaged / lost, etc. To reduce your losses:
E-Mail:chf.dba@gmail.com