RESTORE FROM 'image_file_name';

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 where the server can read files.

As of MySQL 6.0.7, the backupdir system variable value is the default image file directory for RESTORE operations. If an image file is named as a relative pathname, it is interpreted relative to the value of backupdir. The default value is the data directory. Before MySQL 6.0.7, the file should be specified as a full pathname.

RESTORE requires the SUPER privilege.

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 Section 6.4, “Point-in-Time Recovery”).

Upon successful completion, the RESTORE statement returns a result set with the backup number. Warnings produced during the operation can be displayed with SHOW WARNINGS. If the restore operation fails, it returns an error.

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

While the operation is in progress, it can be monitored as described in Section 6.3.3, “MySQL Backup Status Reporting 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.)

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 Section 6.3, “Using MySQL Backup”. Limitations on the use of these statements are discussed in Section D.7, “Restrictions on BACKUP DATABASE and RESTORE”.

RESTORE was added in MySQL 6.0.5.