• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Chrispian H. Burks

coder. maker. writer. photographer.

  • Home
  • Dreams
  • Dev Links
  • About Me
  • Contact

MySQL Disaster Recovery

Published on: September 1, 2012

Today I was doing some database work for a client and I had to delete some recent entries from a database. Like a moron, I forgot to add a “where” close and emptied the entire thing! No big deal, I’m the one who wrote the backup script so I went to grab the backup for yesterday. Yup, you know it. There wasn’t one. The backup stopped working back on July 3rd! Ouch. That burns. Luckily the host is also a good friend of mine and I shot him an email asking if he had a recent backup of the raw MySQL files. I knew that he did regular backups of the entire server so the raw files should be there. I don’t count on that, you should never count on anyone elses backups, but in this case it saved my bacon. Gary had the backup and gave me the quick instructions on restoring them, which I’ll share with you:

Get a copy of your raw mysql file. In my case, I didn’t want the whole database, I wanted one table from one database. Luckily MySQL stores each table in a 3 files called:

database_table.MYI
database_table.MYD
database_table.frm

So, once I located them – Gary sent them to me – I just needed to restore them. It’s pretty easy.

1. Stop mysql
2. cp database_table.* /var/lib/mysql/
That’s the typical path, but of course it may vary from host to host.
3. Restart mysql

And if everything went well (it usually does) you’re back in business.

Caveat: This won’t work if your database type is innodb. Ugh. The disturbing thing to me is that my automated backup stopped running for some reason. It looks like the process was killed at some point and never restarted. You should add “check that backups are working” to your weekly, or at a minimum, monthly checklist. Never ever count on your host or someone else to do it. Murphy’s laws are still very much alive and in effect.

[tags]MySQL, Backup, Restore, Disaster[/tags]

Filed Under: Dev

About Chrispian

My name is Chrispian and I like to make things. I'm a web developer and have been working in the computer / internet field for almost 26 years now. I also draw, write, make things, take pictures and love figuring stuff out. Read More

Reader Interactions

Comments

  1. Ben Partch says

    November 14, 2007 at 3:38 pm

    Yes backups are a great thing. They seem to be unimportant until you need one. Then there is nothing more important.

    A few months back I decided to kill my old blog and start over. (it was doing weird things) The second after I killed the db, I thought “Oh yeah, I had some saved drafts of articles in there, *@%&@*”.

    The good news is I had recently made a backup. :) Now I write all posts in a text file first then save them to hard drive, then when they are ready, I copy/paste the code into WP and publish.

  2. Chrispian says

    November 14, 2007 at 3:42 pm

    That’s another good tip for anyone publishing on the web. Don’t write your drafts in a browser! I’ve lost so many posts to forums, blogs, comments, etc. when my browser crashed mid-draft. I used to run Lit.Org and I’d get reports of people losing their poetry, stories, etc. because they were writing it into the submit box. I write most of my blog posts on my laptop in a text editor as well because I’m usually working on 2-3 at a time as the ideas hit me and it’s a great way to keep backups and keep from losing them typing them live online.

  3. keith says

    November 14, 2007 at 4:59 pm

    I’m guilty of using the browser, but my post ussually aren’t that long……or frequent;)

    I was backing up photos to a USB hard drive and it locked up. 16 GB of pics. erg. Murphy in full effect, indeed!

  4. Chrispian says

    November 14, 2007 at 5:14 pm

    Yeah, USB just feels buggy, still! I have a few USB devices and that’s the only time I ever have any trouble with my Mac or PC.

  5. ses5909 says

    November 15, 2007 at 3:35 am

    I’ve forgotten that silly little “where” clause before. Luckily it was in a test Db but the curse words were still pouring out.

  6. Chrispian says

    November 15, 2007 at 10:35 am

    I remember when I was switching this site to WP many moons ago that I backed up my database and went to delete it so I could start from scratch and ended up deleting the wrong db – it was for another blog I did. Luckily it was brand new at the time so I didn’t lose much.

  7. Tomas says

    December 23, 2007 at 9:41 am

    Do you know how to fix a MySQL Injection what erases 3 fields of a table ? I had a really old backup … And I dont know what to do now! What do you think ?

  8. Chrispian says

    December 23, 2007 at 11:58 am

    Tomas, if I understand what you’re saying, someone used a mysql injection attack on one of your sites and managed to delete the data from 3 fields of one of your tables? If that’s correct, the only way to get that data back is by using a backup. Using an injection attack to delete data is basically the same thing as you going into phpmyadmin and deleting data. Once it’s deleted, it’s deleted. Everything I’ve ready on recovering lost, deleted or corrupted mysql data says either use a last known good backup or try a hard drive recovery program or company.

    Sorry the news wasn’t better!

Leave a Reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

My name is Chrispian and I like to make things. I'm a web developer and have been working in the computer / internet field for almost 26 years now. I also draw, write, make things, take pictures and love figuring stuff out. Read More

Let’s Connect

  • Facebook
  • GitHub
  • Instagram
  • LinkedIn
  • Twitter

Copyright © 2023 · Groovy 1976 Theme by Chrispian H. Burks · Built using Genesis Framework & WordPress Log in