Database administrator can use Backup Table syntax for backup and Restore Table syntax for restore MySQL database table. First we will know about how to take a backup of MySQL database table then after how to restore MySQL table from backup created using Backup Table syntax.
How to Get Table Backup? You can make backup of MySQL database table by Backup Table syntax. The syntax is:
BACKUP TABLE table_name [, table_name] ... TO '/path/to/backup/directory'
This syntax will copy only .frm & myd files. MYI index file can be created using those two files. During backup process, a read lock is applied for each table but one at a time. Suppose you want to copy all the MySQL table then issue a Lock Table syntax for the tables first.
Note: Above backup syntax does not work with MySQL database 5.5. For it, you can try mysqlhotcopy or mysqldump.
How to Restore Table? You can restore corrupt MySQL database tables from backup that was made with backup table syntax using restore table syntax. The syntax to <a href="http://www.mysql-database-recovery.com/">restore MySQL table</a> is:
RESTORE TABLE tbl_name [, tbl_name] ... FROM '/path/to/backup/directory'
As we have discussed above, backup file consists .frm & .myd files only so restore table syntax restores .frm & .myd files only and index file (.myi) can be created using those two files.
Important: Restore table syntax does not overwrite existing tables. If a database administrator tries to restore over an existing table then MySQL database through an error message:
“Table is already exist”
Note: Restore table syntax does not work with MySQL database 5.5.
Applied For: Backup & Restore Table syntax work with MyISAM tables only.
Glossary: .frm, .myd & .myi are the format, data and index files respectively.
Why restore takes longer time than backup? Restore table takes longer time than backup table due to the need to rebuild index files. If a table has more indexes them it takes longer time to restore in comparison with a table with less indexes.
No comments:
Post a Comment