Skip navigation links

Additional languages
12.5.2.3 CHECK TABLE Syntax »
Section Navigation      [Toggle]

12.5.2.2. BACKUP TABLE Syntax

BACKUP TABLE tbl_name [, tbl_name] ... TO '/path/to/backup/directory'

Note

This statement is deprecated. We are working on a better replacement for it that will provide online backup capabilities. In the meantime, the mysqlhotcopy script can be used instead.

BACKUP TABLE copies to the backup directory the minimum number of table files needed to restore the table, after flushing any buffered changes to disk. The statement works only for MyISAM tables. It copies the .frm definition and .MYD data files. The .MYI index file can be rebuilt from those two files. The directory should be specified as a full pathname. To restore the table, use RESTORE TABLE.

During the backup, a read lock is held for each table, one at time, as they are being backed up. If you want to back up several tables as a snapshot (preventing any of them from being changed during the backup operation), issue a LOCK TABLES statement first, to obtain a read lock for all tables in the group.

BACKUP TABLE returns a result set with the following columns:

Column Value
Table The table name
Op Always backup
Msg_type One of status, error, info, or warning
Msg_text The message

User Comments

Posted by Stephen Dewey on October 23 2006 11:47pm[Delete] [Edit]

I'm not sure that this is the best way to accomplish a backup. I think a better strategy would be to use mysqldump with InnoDB tables, and then dump the database as a single transaction, which means that you don't have to put a lock on the table while it's operating, i.e. you can do inserts and updates in addition to reads. I'm not sure why MySQL has so many different ways of doing this, instead of one (mysqldump). Perhaps the BACKUP statement should simply be eliminated.

Posted by [name withheld] on November 14 2006 11:27pm[Delete] [Edit]

This command is handy for more than backups. I use it any time I need to take a complete snapshot of a set of tables, especially for moving data to a new server. The hotcopy scripts and other "live backup" techniques are great for when the data needs to be right back online from the present server, but this is preferable when you need to stop everything and move the data to a different machine without allowing updates in the meantime. LOCK TABLE and BACKUP TABLE on the old machine, then RESTORE TABLE on the new machine. This particular procedure would be more clumsy by any other means.

Posted by Philip Mak on December 21 2006 3:14pm[Delete] [Edit]

Note that:

(1) You need the FILE privilege on the MySQL server
(2) The backup file is created using the filesystem permissions of the MySQL server

so if you're not the administrator of the MySQL server, you probably can't use BACKUP TABLE.

Add your own comment.