4.4.2 BACKUP TABLE Syntax

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

Copies to the backup directory the minimum number of table files needed to restore the table, after flushing any buffered changes to disk. Currently works only for MyISAM tables. For MyISAM tables, copies `.frm' (definition) and `.MYD' (data) files. The index file can be rebuilt from those two.

Before using this command, please see section 4.4.1 Database Backups.

During the backup, a read lock will be held for each table, one at time, as they are being backed up. If you want to back up several tables as a snapshot, you must first issue LOCK TABLES obtaining a read lock for each table in the group.

The command returns a table with the following columns:

Note that BACKUP TABLE is only available in MySQL version 3.23.25 and later.

User Comments

I'm using 3.23.54-nt, and am trying to backup tables using this syntax, but it doesn't work. It comes back with error
'Failed copying .frm file: errno = 2'
Any help is appreciated.
Thanks in advance,

You need to make sure the mysql user performing the backup command has write permissions to the specified directory.

To backup your database, the directory for "TO" must exist!

I have problems with permissions on the BACKUP_TABLE syntax

you need to specify the user and the password in the query?

l<0

w/ Microsoft the statement BACKUP TABLE ... TO 'c:\db-backup' will deliver error-code 2;
try ...TO '/db-backup' instead :)

If you use MySQL on Windows write somth like:
backup table access, adminlog to 'E:/backup/mysql_tables/applevb';

pay atantion on slash type(!!!)

Or, you can use 'C:\\dir1\\dir2' as your backup directory path ( Windows )

Add your own comment.