Restore your Database from a Backup

Some time ago, I did an short article on using MYSQLDUMP and CRON to backup databases.

Side Note: This article was written in 2007 so some of the references may be out-of-date.

Database backups are a good thing to have, but unfortunately someday you might just need do a restore from those backups.

Do You Know How?

The backups you created using the mysqldump utility earlier simply generate a text file full of SQL commands.

For example:

-- MySQL dump 10.9<br>
--<br>
-- Host: localhost    Database: db4wordpress <br>
-- ------------------------------------------------------<br>
-- Server version   4.1.20-log<br>
<br>
[...snip...]<br>
<br>
DROP TABLE IF EXISTS `categories`;<br>
CREATE TABLE `categories` (<br>
  `id` int(10) unsigned NOT NULL auto_increment,<br>
  `cat_name` varchar(80) NOT NULL default 'New Category',<br>
  `disp_position` int(10) NOT NULL default '0',<br>
  PRIMARY KEY  (`id`)<br>
) ENGINE=MyISAM DEFAULT CHARSET=latin1;<br>
<br>
[...snip...]<br>

It becomes a simple matter of running MySQL from the terminal session command line and piping the SQL commands into it to recreate the database.

mysql -h hostname -u username -pmydbpassword databasename < mybackupfile.sql

For example:

mysql -h localhost -u root -pABCD1234 db4wordpress < backup_20071231.sql

Test Your Backups

Quoting those words from The Hitchhiker’s Guide to the Galaxy – Don’t Panic.

It is a good idea to try a restore on a test database sometime long before you actually need to do one for real.

By Brad Trupp © 2007