Once in a while somone will accidently overwrite a bunch of data, drop a table by mistake or find some other way to corrupt a database table. This is when you get the call to please restore data from the backups (which you have right). Well, I do this once in a while and noticed today that most information about this for mysql assumes that you have a sql dump of the data. What if you use mysqlhotcopy to backup your data? There’s not much I could find about it on the web so I thought I would just write a quick note.

We assume this is MySQL and you use mysqlhotcopy to do backups.

We also assume you are the root admin.

It’s pretty easy.

Login:
mysql -u root -p

and give you password when prompted.

switch database context:
mysql> use database_name;

drop the table:
mysql> DROP TABLE table_name;

run your restore:
mysql>RESTORE TABLE table_name FROM ‘/path/to/backup/database/directory/’; e.g /var/lib/mysqlbackup/database_name/

You ought to see:

+---------------------------+---------+----------+----------+
| Table                          | Op      | Msg_type | Msg_text |
+---------------------------------+---------+----------+----------+
| table_name                 | restore | status     | OK       |
+---------------------------+---------+----------+----------+
1 row in set (0.01 sec)

If you get an error make sure that you have privs to read/write the files.

Good luck.