MySQL in a Nutshell

Name

BACKUP TABLE

Synopsis

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

Use this statement to make a backup copy of a table. You can specify additional tables in a comma-separated list. The absolute path to the directory to which MySQL is to copy files is given in quotes after the TO keyword. Only MyISAM tables work with this statement. This statement has been deprecated. It’s recommended that mysqlhotcopy (see Chapter 11) be used until this statement is replaced.

The statement copies the .frm file and the .MYD file, which contain the table structure and the table data, respectively. The .MYI file containing the index is not copied, but will be rebuilt with the RESTORE TABLE statement when restoring the table.

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 results 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. 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.”