RESTORE FROM 'image_file_name'
    [OVERWRITE] [SKIP_GAP_EVENT]

Given a backup image file created by the BACKUP DATABASE statement, RESTORE restores the databases contained in the image. The image file must be named as a literal string. Its location must be in a directory on the server host where the server can read files. If the secure_file_priv system variable is set to a nonempty directory name, the image file must be located in that directory.

The backupdir system variable value is the default image file directory for RESTORE operations. If an image file is named as a relative path name, it is interpreted relative to the value of backupdir. The default value is the data directory.

Be sure that the image file was created from a successful BACKUP DATABASE operation and has not been tampered with or modified. A RESTORE using a compromised image file may render recovered databases unusable.

RESTORE requires the RESTORE privilege for the databases to be restored. You might also need additional privileges, but which privileges these will be is currently under discussion.

The RESTORE statement takes no database names specifying which databases to restore. It restores the entire contents of the image file. The databases are restored to their state at the time that the image file was created. Restoring the image file can be combined with use of the binary log to achieve point-in-time recovery (see Point-in-Time (Incremental) Recovery Using the Binary Log).

The server on which a restore operation is performed must have all storage engines enabled that are represented by tables in the backup image. For example, if a backup image contains ARCHIVE tables and the server does not have the ARCHIVE storage engine enabled, the table cannot be restored and the operation fails.

Upon successful completion, the RESTORE statement returns a result set with the backup number.

mysql> RESTORE FROM '/tmp/world.backup';
+-----------+
| backup_id |
+-----------+
| 9         |
+-----------+

Warnings produced during the restore operation can be displayed with SHOW WARNINGS. If the operation fails, it returns an error. The error is written to the error log, recorded in the progress tables, and is available via the SHOW ERRORS and SHOW WARNINGS statements.

A restore operation fails with an error if it encounters objects that are illegal. For example, this occurs if the backup contains a table for a storage engine that is not enabled.

While a restore operation is in progress, it can be monitored as described in Section 1.7, “MySQL Backup Status Logging and Monitoring”.

RESTORE detects whether the image file is compressed and uncompresses it automatically as necessary. Compressed image files can be produced by using the WITH COMPRESSION clause in the BACKUP DATABASE statement.

RESTORE aborts with an error if the backup image contains any databases that currently exist on the server, unless the optional keyword OVERWRITE is given following the image file name. With OVERWRITE, RESTORE is a destructive operation. Each restored database is first dropped and then created and populated with the tables contained in the backup image. There is no warning about existing data being overwritten.

The SKIP_GAP_EVENT can be useful when executing a RESTORE statement on a replication master. Normally, when a restore operation executes on a master, an incident event is written to the binary log to signal all slaves to stop executing events. This is a protective measure to ensure that whatever changes the restore makes on the master do not break replication.

The normal process is for the user to assess the effect of the restore and, if appropriate, to apply the restore on the slaves as well prior to restarting replication. However, it is possible that the slaves are not replicating the databases in the backup image. In this case, the restore operation would have no effect on the slave. Under these conditions, the slaves need not be stopped and the restore is safe to execute without disrupting replication. The SKIP_GAP_EVENT option accomplishes this because it causes the incident event not to be written to the binary log. See Section 5.2, “RESTORE and Replication of Non-Replicated Databases”.

During a RESTORE operation, foreign key constraints are disabled so that the operation can create and populate tables without causing warnings or errors related to foreign keys.

The RESTORE statement is not written to the binary log and does not replicate to slave servers.

For general information about BACKUP DATABASE and RESTORE, see Chapter 1, Using MySQL Backup. Limitations on the use of these statements are discussed in Chapter 6, Restrictions on MySQL Backup.