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.frmSo, 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 mysqlAnd 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]
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.
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.
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!
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.
I’ve forgotten that silly little “where” clause before. Luckily it was in a test Db but the curse words were still pouring out.
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.
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 ?
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!