MySQL in a Nutshell, 2nd Edition

Name

BACKUP TABLE

Synopsis

BACKUP TABLE table[, ...] TO '/path'

This statement makes a backup copy of a MyISAM table. However, it has been deprecated because it does not work reliably. It’s recommended that you use mysqlhotcopy (see Chapter 16) until this statement is replaced.

You can specify additional tables in a comma-separated list. The absolute path to the directory to which MySQL is to copy files appears within quotes after the TO keyword.

The statement copies each table’s .frm file and .MYD file, which contain the table structure and the table data, respectively. The .MYI file containing the index is not copied, but it will be rebuilt with the RESTORE TABLE statement when restoring the table. Here is an example:

BACKUP TABLE clients TO '/tmp/backup';

+----------------------+--------+----------+----------+
| Table                | Op     | Msg_type | Msg_text |
+----------------------+--------+----------+----------+
| workrequests.clients | backup | status   | OK       |
+----------------------+--------+----------+----------+

If the backup succeeds, the results will look like the preceding output and two files will be created for each table backed up: a .frm file and a .MYD file. If MySQL does not have the filesystem privileges necessary to write to the backup directory, or if a file with the same name is already in the directory, the backup will fail. In that case, the results set will include one row with an error message type and another with a status type and the message text stating, “Operation failed.” ...