I accidentally deleted my word press mysql DB. Below is the list of log files that i see in my directory where i am hoping i can get back some of my data.
I actually converted the first file (mysql-bin.000001) to sql and saw the create statements for wordpress tables. That made me hopeful about the fact that i might be able to recover all my data. However, i ran all the mysql commands in the below mysql binary log files, and could only bring up a very old version of the wordpress blog.
Can i recover my data using the ibdata1, ib_logfile1, ib_logfile0 files?
Best Answer
Ouch. How much hope there was for recovery depended on whether your tables were using the MyISAM or the InnoDB storage engine. If MyISAM, then... "none," and if InnoDB, then "some."
MyISAM, the older engine, stores its data in files named after the table, with an
MYD
extension, while InnoDB stores its data either in files named after the table, with anibd
extension, or in theibdata1
file, which houses the system tablespace, depending on the setting ofinnodb_file_per_table
in configuration. The definition of each the table (column names, data types, index definitions), for either engine, is stored in files named after the tables, with anfrm
extension.The large size of your
ibdata1
file suggested that maybe you didn't haveinnodb_file_per_table
enabled, and that some variation of the following steps might have worked, if you were indeed using InnoDB without innodb_file_per_table:Since you have already created new tables, presumably with the same names, on the existing server, any hope for that plan to have succeeded are likely to be eliminated, or significantly diminished to the point that it is unlikely to be worth trying, since your best hope if you tried it would be to see what you are seeing now -- old data that you inserted into tables you created from the binary logs.
So, what's plan B? To borrow a fitting phrase from the source I'll cite:
http://www.percona.com/docs/wiki/innodb-data-recovery-tool:start
The Percona InnoDB Data Recovery Tool is the only meaningful approach I can think of that might get you where you need to be... or any comparable tools, assuming there are comparable tools. I don't see any simple path... you're going to have to go "low level" if you want to rescue your data.
You have deleted important files, and in an effort to recover from the first problem, you have altered the one remaining file that might have been salvageable, and I'm not entirely sure how InnoDB will have handled those abandoned tables in its internal structures. The data may still be hiding.
There is one reason for optimism, though, and that is in the strings utility. The link I've provided is not to the genuine GNU "strings" utility, it's Microsoft's analogous semi-equivalent, but the point of this tool is to display strings embedded in files that the internal algorithm thinks might be human-meaningful strings.
There are two uses for this: one, you can try using it on your ibdata1 file (it only reads, shouldn't modify) to see what your eyeballs tell you about what you find in there. It offers essentially a brute-force window into the raw content living in the file, and if you find text in there that is newer than anything you put in using the binlog files, that's a good sign that time spent with the Percona tools could be worthwhile because the old data is actually in the file. If you see nothing at all that you recognize, there may be a problem with the method (though I've verified it works on Linux with genuine GNU "strings"), but if you see old content and not new, then the new content is likely gone.
Of course, the other potential use of "strings" if you do see the content and if it's primarily content that is the most valuable thing you've lost -- not configuration and customization -- then you could just use "strings" to extract the raw content, redirect it into another file, and proceed with a new wordpress installation where you could use that raw text to recreate things.
If the data is "that" important, Percona and SkySQL (I'm not affiliated with either company, but those seem to be the two big players in MySQL space) and other companies offer paid recovery services.