I accidentally dropped a MySQL database on my server. Are there any ways to recover a dropped database?
-
What platform is your MySQL database running on?– Jack DouglasAug 28, 2012 at 14:07
-
Any chance the drive your database is on was being backed up and the server admin can save you?– Kenneth FisherMay 29, 2013 at 19:19
-
There are variety of tools in the market for repairing corrupt MySQL databases but for the best and tested results you can always rely on Stellar tools to get your database repaired. The tool can repair InnoDB and MyISAM tables of MySQL database. You can also get a preview of the repaired database objects which will help you give an overview of the data being recovered. Trial Demo version of the software is available for free to repair the database with full integrity and original formatting, ensuring no data loss.– MitchelNov 5, 2019 at 9:15
Add a comment
|
3 Answers
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/image
It 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/ibdata1
Now 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 "" 0
158 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 4294967295
So, 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.sql
c_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:
-
is it still recoverable if after the table dropped, I create a new table and insert new records? Apr 14, 2016 at 5:01
-
In this case you lose index_id because CREATE overwrites the original value. You need to find it somehow. Usually I grep known strings. Following INSERTs may or may not overwrite the data. If not , it's recoverable. You have to try, there is no way to tell beforehand Apr 14, 2016 at 5:17
-
-
@akuzminsky I did till the "dictionary/SYS_INDEXES.sql | grep 158" part. And it gives me some index_id for my table, But it says there is no page file with my index_id. ex : 0000000000000376.page (when I run this c_parser -6f) Dec 26, 2018 at 14:15
-
@SampathSriAnuradha it must feel bad when Fortune is not on your side. I’m sorry. Dec 26, 2018 at 15:23
There's no easy way to break this to you. It doesn't matter if you dropped a db via phpmyadmin or the commandline. It is gone.
Human error is one reason to have a good backup regimen.
I'm not religious but I'll say a prayer hoping it wasn't anything too important.
-
-
8Best I could recommend in that case is unmount the filesystem your datadir was in. Shutdown the server if needed to do that. Call a data recovery service and be prepared to shell out thousands, if not tens of, all while paying for a crap shoot with no garauntees :-\– atxdbaAug 28, 2012 at 7:21
-
3If anyone in a similar situation reads this in the future, I think it's worth also suggesting that it's important to get the filesystem unmounted or read-only ASAP if you're going to hire a data recovery company. The longer writing continues on the filesystem, the more likely it is that the clusters containing your deleted files will get overwritten. May 26, 2013 at 17:10
Depends on your setup. It is possible to restore if you setup your system correctly. If you have a backup you can restore it. and then apply the binary logs up to the point just before you dropped the table.
http://dev.mysql.com/doc/refman/5.5/en/mysqlbinlog.html
I suggest you do this on another server, once you have the table restored you can use mysqldump to extract it and import back to your production server. This will not be a fast restore, but you can recover the data.
If you don't know what you are doing I would suggest openning a support contract with one of the mysql consulting companies (pythian, percona, palamino are probably the best) and have them help you with this.
I wish you the best of luck
