Backing Up MyISAM Tables with BACKUP

One of the easiest ways to back up is with the BACKUP command. This currently works only with MyISAM tables. The syntax is as follows:

BACKUP TABLE tablename TO '/db_backup_path';

The backup path needs to be the full path to the directory you want to save to, and should not be a filename. This makes a copy of the .frm (definition) and .MYD (data) files, but not the .MYI (index) file. You can rebuild the index once the database has been restored.

When dealing with files, you'll need to watch out for file permissions. MySQL does not give the most friendly error message to warn you if, when backing up, you do not have the correct permissions to all the files and directories.

Using BACKUP with Unix

The following example is run on a Unix machine, where the user performing the operations is the root user (see the next section for a Windows example):

 % cd / % mkdir db_backups

This creates the directory off the root directory, where you want to place the backups in this case. Connect to the firstdb database, and run the BACKUP command, as follows:

 % mysql firstdb Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 13 to server version: 4.0.1-alpha-max-log mysql> BACKUP TABLE sales TO '/db_backups'; +---------------+--------+----------+--------------------------------------+ | Table         | Op     | Msg_type | Msg_text                             | +---------------+--------+----------+--------------------------------------+ | firstdb.sales | backup | error    | Failed copying .frm file: errno = 13 | | firstdb.sales | backup | status   | Operation failed                     | +---------------+--------+----------+--------------------------------------+

The problem in this example is that MySQL does not have permission to write files to the
/db_backups directory. You need to exit MySQL, and from the command line make the mysql user the owner of the directory:

mysql> exit Bye % chown mysql db_backups/

Warning

You need to have the correct permissions to do this. Ensure that the user you're working as has the correct permissions. In this example it's root, so there is no problem, but you may not be working as root. If you have problems, you may need help from your systems administrator.

Now the BACKUP statement will run correctly:

% mysql firstdb; Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 15 to server version: 4.0.1-alpha-max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> BACKUP TABLE sales TO '/db_backups'; +---------------+--------+----------+----------+ | Table         | Op     | Msg_type | Msg_text | +---------------+--------+----------+----------+ | firstdb.sales | backup | status   | OK       | +---------------+--------+----------+----------+ 1 row in set (0.00 sec) mysql> exit Bye

This time the backup has been successful, and you can view the newly created files by exiting to the command line once again:

% ls -l db_backups/ total 10 -rw-rw----   1 mysql    mysql         136 May 26 14:07 sales.MYD -rw-rw----   1 mysql    mysql        8634 May 26 14:07 sales.frm

There are the two files, newly created.

Tip

If you have any problems making a backup in this way, it's likely to be because of file permissions. Ask your systems administrator for help if you don't have access to create the files or if you're not sure. Also, remember that BACKUP currently only works for MyISAM tables (check your latest documentation though, as this may no longer be the case by the time you read this).

BACKUP places a read lock on the table before backing it up to ensure that the backed-up table is consistent.

You can also back up more than one table at a time, by listing more than one table name:

mysql> BACKUP TABLE sales,sales_rep,customer TO '/db_backups'; +-------------------+--------+----------+----------+ | Table             | Op     | Msg_type | Msg_text | +-------------------+--------+----------+----------+ | firstdb.sales     | backup | status   | OK       | | firstdb.sales_rep | backup | status   | OK       | | firstdb.customer  | backup | status   | OK       | +-------------------+--------+----------+----------+ 3 rows in set (0.05 sec)

The lock is placed on one table at a time, first sales, then after sales is backed up, on sales_rep, and so on. This allows for consistent individual tables, but if you want to achieve a consistent snapshot of all the tables at the same time, you'll have to place your own locks on the tables:

mysql> LOCK TABLES customer READ,sales READ,sales_rep READ; Query OK, 0 rows affected (0.00 sec) mysql> BACKUP TABLE sales,sales_rep,customer TO '/db_backups'; +-------------------+--------+----------+----------+ | Table             | Op     | Msg_type | Msg_text | +-------------------+--------+----------+----------+ | firstdb.sales     | backup | status   | OK       | | firstdb.sales_rep | backup | status   | OK       | | firstdb.customer  | backup | status   | OK       | +-------------------+--------+----------+----------+ 3 rows in set (0.00 sec)

Note that you cannot lock tables individually:

mysql> LOCK TABLE sales READ; Query OK, 0 rows affected (0.00 sec) mysql> LOCK TABLE sales_rep READ; Query OK, 0 rows affected (0.00 sec) mysql> LOCK TABLE customer READ; Query OK, 0 rows affected (0.00 sec) mysql> BACKUP TABLE sales,sales_rep,customer TO '/db_backups'; +-------------------+--------+---------- +---------------------------------------------------+ | Table             | Op     | Msg_type | Msg_t     | +-------------------+--------+---------- +---------------------------------------------------+      firstdb.sales    | backup | error     | Table 'sales' was not locked with LOCK TABLES     | | firstdb.sales_rep | backup | error     | Table 'sales_rep' was not locked with LOCK TABLES | | firstdb.customer  | backup | status    | OK                                                | +-------------------+--------+---------- +---------------------------------------------------+ 3 rows in set (0.00 sec)

LOCK TABLE automatically releases all locks held by the same thread, so the only lock still held by the time of the backup was on the customer table.

Note

To be able to lock a table, you need the LOCK TABLES privilege and the SELECT privilege for the table you're trying to lock.

mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)

Using BACKUP with Windows

You should read through the preceding example, even if you are not using Unix, as it explains some of the concepts of the BACKUP statement. The following example deals with a problem specific to Windows. Windows may not have the same complexity with file permissions, but it does have its own problems. Take a look at this example, and see if you can spot what's causing the error:

C:\MySQL\bin>cd \ C:\>mkdir db_backups C:\>mysql firstdb Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 3 to server version: 4.0.1-alpha-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> BACKUP TABLE sales TO 'c:\db_backups'; +---------------+--------+----------+-------------------------------------+ | Table         | Op     | Msg_type | Msg_text                            | +---------------+--------+----------+-------------------------------------+ | firstdb.sales | backup | error    | Failed copying .frm file: errno = 2 | | firstdb.sales | backup | status   | Operation failed                    | +---------------+--------+----------+-------------------------------------+ 2 rows in set (0.33 sec)

Unfortunately, the error message is not too clear. The problem is that the backslash (\) is the MySQL escape character, used to escape other special characters such as single or double quotes. To use the backslash for a path in Windows, you need to escape it with another escape character:

mysql> BACKUP TABLE sales TO 'c:\\db_backups'; +---------------+--------+----------+----------+ | Table         | Op     | Msg_type | Msg_text | +---------------+--------+----------+----------+ | firstdb.sales | backup | status   | OK       | +---------------+--------+----------+----------+ 1 row in set (0.55 sec)