Skip navigation links
12.5.3 Plugin and User-Defined Function Statements »
Section Navigation      [Toggle]

12.5.2.7. RESTORE TABLE Syntax

RESTORE TABLE tbl_name [, tbl_name] ... FROM '/path/to/backup/directory'

RESTORE TABLE restores the table or tables from a backup that was made with BACKUP TABLE. The directory should be specified as a full path name.

Existing tables are not overwritten; if you try to restore over an existing table, an error occurs. Just as for BACKUP TABLE, RESTORE TABLE currently works only for MyISAM tables. Restored tables are not replicated from master to slave.

The backup for each table consists of its .frm format file and .MYD data file. The restore operation restores those files, and then uses them to rebuild the .MYI index file. Restoring takes longer than backing up due to the need to rebuild the indexes. The more indexes the table has, the longer it takes.

RESTORE TABLE returns a result set with the following columns.

Column Value
Table The table name
Op Always restore
Msg_type One of status, error, info, or warning
Msg_text The message

User Comments

Posted by Nick Gaugler on January 14 2004 6:50pm[Delete] [Edit]

This takes much longer then a BACKUP and is very unefficient. First, it must copy the entire .frm and .MYD file from the location of your backup. Then it recreates the .MYD into a .TMD, just like an ALTER TABLE, then it finally rebuilds the .MYI. So make sure you have enough space for 2X the .MYD file then enough for the MYD + the MYI. It'd be nice if MySQL had a function to just rebuild an index based off a data file.

Posted by rpande rpande on February 23 2004 5:40pm[Delete] [Edit]

If you don't have enough space for a repair, try the myisamchk statement.
'myisamchk -r -q table_name', worked for me. Updates slowly(spprox. 6 hours for a 25 million record table) but at least helps save on the space :)

Posted by Craig Taylor on May 31 2006 7:24pm[Delete] [Edit]

You cannot use RESTORE TABLE from within an application that is currently using the database, because it will not over write existing tables. The tables have to be deleted first and that requires shutting down the server. When progmatically shutting down the server in Windows XP, then resarting the server after a restore operation, being able to reconnect is random.

Add your own comment.