Recover a MySQL database with only bin-log file

Question

Here is the CATASTROPHE:

We are working in our company with a web application for test management: TestLink. One team was working on it for more than 1 month till now. Yesterday, another team wanted to start using testLink too, so i was asked to install it for them.

The installation was made on the same server where the first TestLink was running, i'm using WampServer.2.0 in which i run testLink, so i started installing it again, unfortunately on the same WampServer.. i was a little bit tired, and i didn't mentioned in the installation that I was giving the second TestLink the same database name as the first one.. installation succeeds... But WAIT!! all data in the first database was LOST!! overwritten by an empty and new database..! I felt somebody is definitely going to kill me!

So i start googling, but since no database backup was made since the installation of the first TestLink.. i really felt i have no solution.. i tried running softwares to backup overwritten and accidentally removed files, that didn't help.. of course...

i found in this blog a story about MySQL binary logs, but looks like it's necessary to have at least one backup to restore lost data. In the configuration of my my.ini file, log-bin=mysql-bin line is uncommented and i can find files under mysql\data\ that looks like this: mysql-bin.00000x

I ran the mysqlbinlog command on one of them using: mysqlbinlog --start-datetime="2011-04-01 00:00:00" mysql-bin.000006, that made my screen look like a matrix one, i can recognize lines that looks like TestLink commands, the command seems to end successfully.. but no.. tables in the database stay unchanged, empty...

Am I missing something? is there a light of hope?

Please help..

Answer 1

The bin-log will record all queries from whenever it was started so you have basically three options:

1. The bin-log was enabled from the very start and recorded all queries on the database. In this case you can simply restore the entire database from the bin-log.
2. You have an old backup taken sometime after the bin-log was enabled. In this case you can restore from the backup and starting from the location in the bin-log that the backup was taken (incremental backup).
3. The bin-log doesn't contain all queries from the origin of the database and you have no backup. In this case you will (eventually) be able to restore all data contained in the bin-log but any data not present there is lost forever. You can attempt to manually restore any missing data but depending on your data structure/size this may be simple to impossible.

From the sounds of it you have no backups which leaves options 1 and 3. Check the first bin-log file and see what the earliest entry in it is from to see which case applies to you. If you're looking for specific usage of mysqlbinlog see the official documentation which explains things pretty well.

Should you ever recover from this the next thing you should/must do is implement a regular backup schedule of the database including off-site copies.

Answer 2

I finally got rid of the problem, and minimized damages using the bin-log files as uesp said. I used the mysqlbinlog binlog_file | mysql -u root -p to recover 75% of the data contained in the DB, plus its structure. The remaining 25% was manually written, it took us about half a day to bring back the whole.

Yes, that was a bad experience, but an enriching one, i set up automatic backup run every day at 03:00am, compressing the .sql dump, attaching it to an email and sending it to other machines, in case of the main one burns :)

Thank you all for your help, the MySQL official documentation was also truly helpful!

Have a great day!