Comments are back!

Comments are back!: "

Thanks to a suggestion from Mohammad Lahlouh, all deleted comments are restored. Thanks!

I was able to do this with the mysql binlog, which is a binary log containing all statements executed on the server. If you face a similar problem, here are the steps to restore the DB:

1. See if you're actually storing binlogs. For me this happened to be the case, although I never actually payed attention (My friend Jens Meijer is so kind to provide hosting for me). In my case they were stored in /var/log/mysql.


2. Grab your latest backup. In my case it was from October 2008.


3. Make sure you also create a new backup, just in case this process goes wrong.


4. Restore your old back.


5. Find the binlog that corresponds to the date of your backup. I did a simple copy of the ~300 binlogs to a different directory.


6. Run the binlog through 'mysqlbinlog' and pipe into a new file. I had to append the --database argument, because I wasn't interested in all the other databases. The full command for me was mysqlbinlog mysql-bin.* --database evert2 > commentsbinlog.sql
.

7. Open the new file, and make sure the first queries are correct. Likely there are some updates/inserts/deletes on top of the file that were already part of your last backup.


8. Go all the way to the bottom of the file, and remove the offending queries. In my case I had to remove the query that deleted all my comments.


9. Apply the sql file to your database: mysql -u root -p evert2 < < commentsbinlog.sql

It's wise to assure nobody can access the DB while you are doing this. I hope this helps anyone else with this problem.

PlanetMySQL Voting:
Vote UP /
Vote DOWN"