How to recreate a table and restore from binary logs?

  • Configuration: Mandriva 2007 Linux, homebrew desktop dual processor athlon PC, 2x250GB hard drives, 4 GB RAM.

    A few weeks ago, I noticed that my disk drive was full, when it should have only been about 20% full. I checked the disk, and found a bunch of unconnected inodes. I did an fsck and fixed the disk, and everything seemed OK.

    Except… when started up the machine, all the comments in my blog were deleted with errors such as:

    WordPress database error: [Can’t find file: ‘wp_comments’ (errno: 2)]
    SELECT * FROM wp_comments WHERE comment_post_ID = ‘282’ AND comment_approved = ‘1’ ORDER BY comment_date

    So, I look at /var/lib/mysql, and find that, indeed, the comment table is missing.

    The wp_comments.frm file exists, but not the wp_comments.MYD and wp_comments.MYI.

    But, it turns out, that I have a ton of binary log files sitting there, and when I look at them, it seems that most of the comments are buried in there.

    So, I google and read a bit on binary log files and it seems I ought to be able to reconstruct stuff using these files.

    I try:
    mysqlbinlog –database=billoblog mysql-bin.[0-9]* | mysql -p

    and get the error:

    ERROR 1017 (HY000) at line 1721: Can’t find file: ‘wp_comments’ (errno: 2)

    Doh.

    So, do I need to recreate the table? Can I use the binary log to create it? Should I recreate it manually?

    I’m really casting about here, and can’t seem to find a good tutorial on using the binary logs.

    Can anybody point me towards an idiot guide to reconstruction? Any help would be appreciated.

    Thanks in advance,

    billo

Viewing 3 replies - 1 through 3 (of 3 total)
  • Moderator Samuel Wood (Otto)

    (@otto42)

    www.ads-software.com Admin

    I feel your pain. Same thing happened to me at one point, on my old server.

    Short version was that I couldn’t work out a way to do it. That part of mySQL is some pretty esoteric stuff. Eventually I resorted to restoring several things by hand. Yeah, I know. Sucks.

    Now I backup the whole database to flat files. Often.

    Thread Starter billo

    (@billo)

    Heh. Well that’s not what I was hoping to hear. But you know, I’d settle for some documentation telling me the structure of the wp_comments table. I would like to try creating the table by hand and seeing if the logs could restore to an existing but empty table. Unfortunately, I don’t know the structure of the wp_comment table.

    Thanks for the reply!

    Moderator Samuel Wood (Otto)

    (@otto42)

    www.ads-software.com Admin

    Look in the upgrade-schema.php file, in the wp-admin directory. The whole schema is there.

Viewing 3 replies - 1 through 3 (of 3 total)
  • The topic ‘How to recreate a table and restore from binary logs?’ is closed to new replies.