Home
News & Insights
- News & Insights Home
- Innovation
- IT Careers & Skills
- Cloud
- Cyber Security
- Future of Work
- All Categories
- Marketing
- HR
- Finance
Community
- Ask question
- Community Home
- Spiceworks Originals
- Cloud
- Collaboration
- Networking
- Water Cooler
- Windows
- All forums
- How-Tos
- Scripts
- Vendors
- Meetups
Reviews
Online Events
Login Join
Login Join
Priyal (Stellar Info Tech) This person is a verified professional. Verify your account to enable IT peers to see that you are a professional.
Apr 12, 2021 4 Minute Read
Spice
Reply (0)
Subscribe
- Share
Priyal Chugh
This person is a verified professional. Verify your account to enable IT peers to see that you are a professional.
Priyal (Stellar Info Tech)Product Consultant at Stellar Information Technology Pvt. Ltd.
Areas of expertise What's this? Microsoft Exchange
2329
Contributions
68
Best Answers
21
Helpful Posts
0
Projects
Main Areas of Contribution:
- Microsoft Exchange |
- Microsoft Office |
- Data Recovery |
- Microsoft Office 365 |
- Microsoft SQL Server
Register. Track Progress. Earn Credits.
Learning has never been so easy!
Sign UpRead these next...
If you act fast there is a high chance to get your database back. The chance is higher for InnoDB, for MyISAM it's non-zero, but close.
The matter is when MySQL executes DROP TABLE or DROP DATABASE (which is essentially the same) InnoDB doesn't wipe the data out. Pages with the data are still on disk.
Depending on innodb_file_per_table setting the recovery process differs. If innodb_file_per_table is OFF (default up until 5.5) then the dropped table remains in ibdata1. If innodb_file_per_table is ON (default as of 5.5) then the dropped table was in the respective .ibd file. MySQL removes this file when drops the table.
The very first thing to do is to stop any possible writes so your table isn't overwritten. If innodb_file_per_table is OFF it's enough to stop MySQL (kill -9 is even better, but make sure you kill safe_mysqld first). If innodb_file_per_table is ON then umount partition where MySQL stores its data. If the datadir is on the root partition I recommend to shut down server or at least take an image of the disk. Let me repeat, the goal is to prevent overwriting dropped table by MySQL or operating system.
There is a tool that allows to work with InnoDB pages at low level, TwinDB data recovery toolkit. I will use it to illustrate undrop recovery.
You need to take the media with dropped table (either ibdata1 or disk image) and find InnoDB pages on it. stream_parser tool from the toolkit does it.
./stream_parser -f /path/to/disk/imageIt will scan the file, find the InnoDB pages and sort them by type and index_id. index_id is an identifier that InnoDB uses to refer to an index. A table is stored in index PRIMARY. To find what index_id is your dropped table you need to recover InnoDB dictionary.
The InnoDB dictionary is stored in ibdat1 file. You need to scan ibdata1 file the same way as above:
./stream_parser -f /var/lib/mysql/ibdata1Now you need to get records from the InnoDB dictionary tables SYS_TABLES and SYS_INDEXES(let's say your table is sakila.actor):
./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000001.page -t dictionary/SYS_TABLES.sql | grep sakila/actor 000000000B28 2A000001430D4D SYS_TABLES "sakila/actor" 158 4 1 0 0 "" 0158 is table_id, remember it.
./c_parser -4Df pages-ibdata1/FIL_PAGE_INDEX/0000000000000003.page -t dictionary/SYS_INDEXES.sql | grep 158 000000000B28 2A000001430BCA SYS_INDEXES 158 376 "PRIMARY" 1 3 0 4294967295 000000000B28 2A000001430C3C SYS_INDEXES 158 377 "idx\_actor\_last\_name" 1 0 0 4294967295So, index_id of your dropped table(sakila.actor) is 376.
Now you can fetch records of the dropped table from InnoDB index_id 376. You need to have the table structure of the dropped table, exactly CREATE TABLE statement which the table was created with. Where you can get it? Either from old backup, or from elsewhere. It's also possible to recover the structure from the InnoDB dictionary, but I won't cover it in this answer. Let's just assume you have it.
./c_parser -6f pages-ibdata1/FIL_PAGE_INDEX/0000000000000376.page -t actor.sql > dump.tsv 2> load_cmd.sqlc_parser outputs records as tab-separated dump to stdout. The dump can be loaded with LOAD DATA command. c_parser prints it to stderr.
See more details in posts:
- Recover after DROP TABLE, innodb_file_per_table is ON
- Recover after DROP TABLE, innodb_file_per_table is OFF
- Recover InnoDB dictionary