Tips for Restoring Data in a MySQL Database

Equally important as backing up data in a MySQL database is the ability to restore data.

Most books on the subject emphasize the importance of backing up your data regularly (and rightly so), but restoring the data is an often-overlooked aspect of this process. Backed-up files are useless if they can’t be accessed. Accordingly, you should regularly restore your files from backup to make certain they can be used in an emergency. In fact, it might not be too much to say that a backup job isn’t complete until you’ve confirmed that the backup files can be restored. Besides the peace of mind you’ll achieve, it pays to be thoroughly familiar with the process, because you certainly don’t want to waste time learning the restore procedure after the system goes down.

In the preceding section, you learned that the output of the mysqldump utility includes SQL statements such as CREATE TABLE to simplify the process of rebuilding lost data. Because of this, you can take a file generated by mysqldump and pipe it through the mysqlcommand-line client to quickly re-create a lost database or table.

Here’s an example:

[user@host]# /usr/local/mysql/bin/mysql db1 < mydump.sql

In this example, mydump.sql is the text file containing the output of a previous mysqldump run. The contents of this file (SQL commands) are executed through the mysql command-line client using standard input redirection. Note that the database must exist prior to piping the contents of the backup file through it.

CAUTION

The user who performs the restoration must have permission to create tables and databases. Accordingly, you might need to use the –user , –password , or –host options with the previous command.

If you don’t have access to (or don’t like) the command line, another option is to use the SOURCE command, as shown:

mysql> SOURCE mydump.sql

The SOURCE command uses the SQL instructions in the named text file to rebuild the database(s) or table(s) specified. To see the results of the restoration, use a simple SELECT statement to verify that the data has been successfully restored.

Another option is to use the LOAD DATA INFILE command to import data from a text file. Here’s an example:

mysql> LOAD DATA LOCAL INFILE '/tmp/mydump.sql'
    -> INTO TABLE p
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';
Query OK, 5 rows affected (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 0

Once you’re comfortable with the procedures to back up and restore your data, you’ll likely want to set up a regular schedule of backups for your organization. Both Windows and UNIX come with built-in tools that you can use for this purpose.

• The cron tool is a UNIX scheduling utility that can be used for this purpose. It allows you to schedule the mysqldump utility to run at designated times and dates. Type man cron at your UNIX command prompt to find out more about how to use this tool.
• In Windows NT, Windows 2000, or Windows XP, you can use either the AT command from the command prompt or the Task Scheduler (Start | Control Panel | Scheduled Tasks) to automate backups.