Restoring MyISAM Tables with RESTORE

The converse of BACKUP is RESTORE, which restores MyISAM tables previously created with BACKUP. It also re-creates the indexes, which can take some time for larger tables. The syntax is as follows:

 RESTORE TABLE tablename FROM '/db_backup_path'

You cannot restore over an existing table. If you tried to restore the sales table you'd backed up earlier, you'd get the following result:

mysql> RESTORE TABLE sales FROM '/db_backups'; +-------+---------+----------+---------------------------------------------+ | Table | Op      | Msg_type | Msg_text                                    | +-------+---------+----------+---------------------------------------------+ | sales | restore | error    | table exists, will not overwrite on restore | +-------+---------+----------+---------------------------------------------+ 1 row in set (0.01 sec)

At least this message is clearer than the failure to successfully complete a backup.

To test this backup, you're going to have to take a leap of faith and drop the sales table.

Warning

It may be obvious, but please don't test a backup of a live table by dropping the original table. Try and restore to a different database or server. If I'm too late, and the restoration fails, don't say you heard it from this book!

Now DROP and attempt to RESTORE the table:

mysql> DROP TABLE sales; Query OK, 0 rows affected (0.01 sec) mysql> RESTORE TABLE sales FROM 'db_backups'; +-------+---------+----------+--------------------------+ | Table | Op      | Msg_type | Msg_text                 | +-------+---------+----------+--------------------------+ | sales | restore | error    | Failed copying .frm file | +-------+---------+----------+--------------------------+ 1 row in set (0.01 sec)

Don't panic. Can you see the error in the previous code? The path is not correct. One of your worst enemies when something does go wrong will be panic. After seeing the previous result in a crisis situation, you could easily run screaming from the building cursing MySQL's dodgy software. But there's usually a simple reason that something does not work, such as the previous typo. The correct path will correctly restore the table, as this Unix example shows:

mysql> RESTORE TABLE sales FROM '/db_backups'; +---------------+---------+----------+----------+ | Table         | Op      | Msg_type | Msg_text | +---------------+---------+----------+----------+ | firstdb.sales | restore | status   | OK       | +---------------+---------+----------+----------+ 1 row in set (0.00 sec)

And this is the correct statement on Windows:

mysql> RESTORE TABLE sales FROM 'c:\\db_backups'; +---------------+---------+----------+----------+ | Table         | Op      | Msg_type | Msg_text | +---------------+---------+----------+----------+ | firstdb.sales | restore | status   | OK       | +---------------+---------+----------+----------+ 1 row in set (0.66 sec)

And, just to placate the most paranoid, let's see if the sales table did in fact restore correctly:

mysql> SELECT * FROM sales; +------+-----------+------+-------+ | code | sales_rep |   id | value | +------+-----------+------+-------+ |    1 |         1 |    1 |  2000 | |    2 |         4 |    3 |   250 | |    3 |         2 |    3 |   500 | |    4 |         1 |    4 |   450 | |    5 |         3 |    1 |  3800 | |    6 |         1 |    2 |   500 | |    7 |         2 | NULL |   670 | |    8 |         3 |    3 |  1000 | +------+-----------+------+-------+ 8 rows in set (0.00 sec)