Restore your Database from a Backup

xawk.com -- Tags -- MySql -- Restore your Database from a Backup

Restore your Database from a Backup

Restore your Database from a Backup
By Brad Trupp (c) 2007

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

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
--
-- Host: localhost Database: db4wordpress
-- ------------------------------------------------------
-- Server version 4.1.20-log

[...snip...]

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
`id` int(10) unsigned NOT NULL auto_increment,
`cat_name` varchar(80) NOT NULL default 'New Category',
`disp_position` int(10) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[...snip...]

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.

Tags: MySql

Xawk