BACKUP & RESTORE TABLE in MySQL
BACKUP & RESTORE TABLE in MySQL
BACKUP TABLE
Syntax:
BACKUP TABLE tbl_name [, tbl_name] … TO ‘/path/to/backup/directory’
BACKUP TABLE copies to the backup directory the minimum number of table files needed to restore the table, after flushing any buffered changes to disk. The statement works only for MyISAM tables. It copies the .frm definition and .MYD data files.
The .MYI index file can be rebuilt from those two files. The directory should be specified as a full pathname. To restore the table, use RESTORE TABLE.
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 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.
