RESTORE TABLEtbl_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
pathname.
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
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.
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 :)
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.