subscribe

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

Web mentions

Comments

  • Roxy

    Roxy •

    Yah for you!
  • David

    David •

    Brilliant! Will need to look into this for my clients.
    Thanks for sharing.
  • Andy

    Andy •

    There's also a --start-datetime switch (or something along those lines) for mysqlbinlog. If you know the exact time of your last backup you can use that and skip step 7.
  • Les

    Les •

    Thanks for mentioning this, as this is a safety line I too wasn't aware of.

    Glad you have your comments back, but remember... back up as you go :)
  • Sheeri

    Sheeri •

    Yep! there's also an --end-datetime flag if you want data from a certain time period.

    There's a took called mk-slave-delay from www.maatkit.org which some people use to have a time delay, but I've found that working with the binary logs is always the best bet, because there's no guarantee that a 1-hour delay or a 4-hour delay will be enough....it depends on when you realize there's a problem.

    And there's basically no advance thought required -- just turn on the binary logs!

  • Anonymous •

    You can also use awk to extract only the queries you need from the binlogs.

    mysqlbinlog /var/log/mysql/mysql-bin.* | awk 'comment_table/,/;/' | more