Introduction

A good maintenance plan for any database system includes a means to preserve the data. The main objective is to archive the data in case of failure or accidental loss. There are many ways you can backup and restore data in MySQL. The two most prominent methods are the MySQL Backup feature and the mysqldump utility. The following sections describe these methods as well as a few of the popular third party solutions.

The MySQL Backup Feature

Starting in versions 5.5 and 6.0 betas, MySQL has provided the MySQL Backup feature which is comprised of two SQL commands (BACKUP DATABASE and RESTORE) as well as a command line utility (mysqlbackup). The SQL commands operate on the database-level whereby you can backup and restore a list of databases.

Backing up data

The BACKUP DATABASE command is used to make a backup copy of the data and objects in a list of databases. This command will make copies of all of the tables, views, events, triggers, procedures, and stored functions in each database listed. It also copies all of the permissions granted on objects in the databases listed (but not user accounts). The output of this command is called a backup image file.

As you can see from the syntax, there is also a compression option that compresses the backup image data. There is currently only one compression algorithm option (zlib) so there is no need to use that option.

Restoring data

The RESTORE command is used to restore data previously backed up with the BACKUP DATABASE command. This command is a destructive operation where the databases in the backup image are dropped (e.g. DROP DATABASE) first before the data is restored. Thus, any existing database objects are lost and replaced by those in the backup image. The good news is the default setting is to fail if any database in the backup image is already on the server. You can override the default by specifying the OVERWRITE option which will drop the databases before running the restore operation.

The syntax for the RESTORE command is:

RESTORE FROM 'backup_image_file_name' [OVERWRITE | SKIP_GAP_EVENT]

The SKIP_GAP_EVEN option is used to tell the server to not write an incident report to the binary log. See the online MySQL Reference Manual for more details about how MySQL backup and replication interact.

Backup Logs

The MySQL backup feature stores metadata about each operation in two logs stored in the mysql database. The first log, backup_progress, lists information about the progress of the operation. The second log, backup_history, contains information about the backup including when it was run, the binary log information, and more. Look to this log for detailed information about the success of the operation.

Using MySQL Backup and point-in-time recovery

MySQL replication provides the capability to perform point-in-time recovery where data can be restore to a particular time using the binary logs. This is done by replaying the events in the binary log on a computer to restore the data after some catastrophic event.

You can use MySQL backup in conjunction with binary logging to do point-in-time recovery. That is, you can restore your server using a particular backup image and then apply the binary logs to roll the data forward. The following list describes one procedure you can use to perform point-in-time recovery using the backup system.

Find the latest backup for the databases you need to restore. This information can be found in the backup_history log.

Restore the latest backup image.

Apply the binary log(s) since the last backup using the mysqlbinlog utility providing the starting position (or starting date time) from the backup history log. For example:

mysqlbinlog --start-datetime="2009-09-08 9:59:59" \

../logs/mysql-bin.123456 | mysql -u root -p

The mysqlbackup Utility

The mysqlbackup utility, also known as the backup client, retrieves statistical, metadata, and summary information from backup images. The mysqlbackup utility is installed with every binary installation and can be found in the same folder as your mysql client. Among other things, it can:

List the contents of a backup image

Show statistics for a backup image

Perform a search for objects in the backup image

Display the metadata for objects (the create statements)

The mysqldump Utility

The mysqldump utility is a command-line tool that you can use to backup and restore your data. It is unique in that it generates an output file that creates SQL commands to recreate all objects (e.g. CREATE TABLE) and statements to restore the data (i.e. INSERT INTO). This form of backup can be very useful in situations where you need to perform transformations or want to see the data to correct logic or data entry errors. There are many options for this command, but the basic synax is: