Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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. binary files

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?

share|improve this question
    
Presumably, since you're asking, you don't have a proper backup made with mysqldump that you could restore... but do you have any backups of the files and directories that you deleted, at all, regardless of age? Was this server originally set up around 2013-06-20, or has it been running for longer than that? What happened on 2013-07-04 that apparently stopped the writing of binary logs? Do you still have your my.cnf configuration file? –  Michael - sqlbot Nov 27 '13 at 1:57
    
I dont have backups of any files/folders. It seems that some script i ran somehow deleted my root user and wordpress DB. I seem to have commands in my.ini which make mysql write the binary logs 1.) log-bin=mysql-bin, 2.) binlog_format=mixed, 3.) server-id = 1 . I don't know why the mysql bin stopped writing. Is there anything specific you need me to check in the my.ini? –  N.M. Nov 27 '13 at 3:42
    
Ah, Windows. Yes, my.ini. Did you have an entry for innodb_file_per_table? It looks like you didn't, or it was = 0 and while it's usually best to have that turned on, it might work in your favor if you didn't in this case. –  Michael - sqlbot Nov 27 '13 at 3:56
    
I did not. Also, would like to mention that i re-ran the sql statements in the binary log files on the same DB instance (which gave me a older version of my wordpress tables). I am really hoping i can recover my data up until say some point in last week cause that was when i had all the data in place that i need to rollback to. Thanks for your help, really hoping i can pull this off. –  N.M. Nov 27 '13 at 4:01

1 Answer 1

up vote 0 down vote accepted

i re-ran the sql statements in the binary log files on the same DB instance (which gave me a older version of my wordpress tables)

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 an ibd extension, or in the ibdata1 file, which houses the system tablespace, depending on the setting of innodb_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 an frm extension.

The large size of your ibdata1 file suggested that maybe you didn't have innodb_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:

  1. Make a backup copy of everything.
  2. Set up a separate, new MySQL server.
  3. Apply the binlogs you have to the new server, in the interest of recreating the .frm files for the table definitions, and then manually altering any tables to make any changes you recall that aren't accounted for in the older binlogs.
  4. Stop the new server.
  5. Copy directories inside your data directory and their .frm files to the appropriate places on the old server
  6. Start the old server, using innodb_force_recovery if needed, to see how well InnoDB would manage to pair up the .frm files with the table data in ibdata1, which it should, if the table definitions were the same.

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:

"It is a tedious process that requires an understanding of InnoDB's internal data storage format, C programming, and quite a bit of intuition and experience."

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.

share|improve this answer
    
From your response i believe Strings utility is the only realistic way left for me to recover my data. i will definitely try that. I also would really like to thank you for your detailed response. –  N.M. Nov 27 '13 at 8:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.