Restoring MySQL Database Files on Debian GNU/Linux 6.0

Question

linux

Hi all.
I have a very uncomfortable situation.
Today I needed to rollback the DB. I
did it this way:
mysql -u root -p < /opt/bcp/project_db-2012-04-05-0141.sql
more than 40. There was a loss of very important data. Looking at the dump file, I saw that it was incomplete.
There is no point in discussing the reasons why the backup turned out to be crap, but I began to think about how to restore the current state of the database from which I rolled back, calling myself an idiot that I did not backup manually before rolling back.
Obviously, there is only one recovery option: try to recover the files themselves, in which MySQL stored the database tables. If the tables were of type MyIsam, then each table would be stored in a separate file, which could be restored using one of the utilities for recovering deleted files. But all my tables were of the InnoDB type, so the entire database was stored in one file.
I'm only a programmer, and I'm not even an advanced Linux user, so I don't know for sure if my option is possible. And the option is this:
In the flooded dump, the database was deleted before flooding (standard DROP DATABASE IF EXISTS), which means that the file in which the database was stored was also deleted, and then it was recreated when the database was created by the dump.
I want to know if it is possible that one of the utilities for recovering deleted files will be able to determine that such and such a file existed, that it was deleted, and whether it can be restored. Of course, I doubt it (although I hope), because the file was then re-created.
Thank you.

Answer 1

In a flooded dump, the database was deleted before flooding (standard DROP DATABASE IF EXISTS), which means that the file in which the database was stored was also deleted, and then it was recreated when the database was created by the dump.

"DB" in MySQL is a folder where files of forms (table structure)/indexes/data are stored. If the tables are InnoDB, then the indexes / data are stored elsewhere - all in one file, and this file is not deleted during DROP DATABASE - just the place is marked as free. DROP DATABASE deletes the forms folder.
If InnoDB is configured as innodb_file_per_table, then *.ibd data files are stored in the folder, and only metadata is stored in ibdata1.
If you have backups of some files, there are several recovery options: From .frm and ibdata1/ib_logfile{0,1} , if you have a schema and *.ibd files
In your case, IMHO, it’s easier to restore from the previous day’s backup…

Answer 2

> The reasons why the backup turned out to be crap, it makes no sense to discuss here.
At least give a hint so that others do not run into something like this

Answer 3

And throw out this mysqldump, use xtrabackup, it backups without a table lock, and it works much faster.