Backup and Recovery

From WikiContent

Revision as of 21:43, 24 January 2010 by Chuck.bell (Talk | contribs)
Jump to: navigation, search



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.

The syntax for the BACKUP DATABASE command is:

   { * | database_name [, database_name] ... }
   TO 'backup_image_file_name'

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_EVENT 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.

  1. Find the latest backup for the databases you need to restore. This information can be found in the backup_history log.
  2. Restore the latest backup image.
  3. 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:

mysqldump <connect and user information> <comma separated database list>

The output of this command is a stream of SQL commands which can be redirected to a file for restoring later.

Third Party Tools

There are a number of alternatives and third-party tools that you can use such as the following.

  • Physical file copy – if your server is shutdown, you can use tar and gzip to make a copy of the datadir files. This becomes an offline backup of your data.
  • Zmanda Recovery Manager – an open source enterprise backup option that uses the mysqldump functionality and binary logs to manage scheduled backups.
  • Innobase's InnoDB Hot Backup – a commercial solution for online backup and restore of InnoDB tables.
  • Logical Volume Manager snapshots – you can use a LVM and take snapshots to make copies of your data. The caveat here is you must momentarily lock your tables before taking the snapshot.

For more information about the MySQL backup and mysqldump features, see the online MySQL Reference Manual. [refman]

Personal tools