BACKUP {DATABASE | SCHEMA}
    { * | db_name [, db_name] ... }
    TO 'image_file_name'
    [WITH COMPRESSION [COMPRESSION_ALGORITHM [=] algorithm_name]]
    [OVERWRITE]

This statement backs up one or more databases and writes the backup contents to an image file (a file containing database contents) on the server host. The file must be named as a literal string. The file can be a regular file, in which case, an error occurs if the file already exists. The OVERWRITE option can be given to allow an existing file to be overwritten. On Unix, the file can be an existing FIFO. The file location must be in a directory where the server can create and write files. If the secure_backup_file_priv system variable is set to a nonempty directory name, the image file must be located in that directory.

Backup images should be stored in a secure location so that unauthorized users cannot modify them. The backupdir system variable value is the default image file directory for BACKUP DATABASE operations. If an image file is named as a relative path name, it is interpreted relative to the value of backupdir. The default value is the data directory.

BACKUP DATABASE requires the BACKUP privilege for the databases to be backed up. You might also need additional privileges, but which privileges these will be is currently under discussion.

The databases to back up may be specified using * to name all databases, or using a comma-separated list of one or more names. All specified databases are backed up to the same image file. If databases are named, no name can appear more than once, and all the databases must exist.

BACKUP DATABASE * TO '/tmp/all.backup';
BACKUP DATABASE world TO '/tmp/world.backup';
BACKUP DATABASE db1, db2 TO '/tmp/db1-db2.backup';

Upon successful completion, the BACKUP DATABASE statement returns a result set with the backup number.

mysql> BACKUP DATABASE test TO '/tmp/world.backup';
+-----------+
| backup_id |
+-----------+
| 8         |
+-----------+

The image file resulting from a successful backup operation contains information about which databases it contains and can be used later with a RESTORE statement to restore the contents of those databases to their state at the time of the backup operation. To inspect the contents of the image file, use the mysqlbackup program.

Warnings produced during a backup operation can be displayed with SHOW WARNINGS. If the operation fails, it returns an error. Any file created by the operation normally is removed. It is possible in rare cases that the incomplete image file will not be removed, in which case it should be removed manually. Using such an image file for RESTORE may render recovered databases unusable.

Error messages are written to the error log, recorded in the progress tables, and is available via the SHOW ERRORS and SHOW WARNINGS statements.

A backup operation fails with an error if it encounters objects that are illegal. For example, this occurs for a view for which an underlying table has been dropped or altered in such a way that the view definition has become invalid.

While a backup operation is in progress, it can be monitored as described in Section 1.7, “MySQL Backup Status Logging and Monitoring”.

You can use the WITH COMPRESSION clause to cause BACKUP DATABASE to compress the backup. This reduces the image size. The optional COMPRESSION_ALGORITHM clause may be given when using WITH COMPRESSION. The only allowable algorithm name is gzip, which is also the default. gzip compression is the same as that done by the gzip command-line utility; it follows the conventions described as RFC 1952.

If WITH COMPRESSION is given, the entire backup is compressed. It is not possible to selectively compress some databases but not others within a single backup operation.

Use of compression does not cause BACKUP DATABASE to modify the given image file name. It is recommended that you use an appropriate suffix. For example, if you name a backup image mybackup.bak normally, name it mybackup.bak.gz instead if you specify WITH COMPRESSION.

RESTORE detects whether a backup image is compressed and uncompresses it automatically as necessary.

It is possible to manually compress and uncompressed image or uncompress a compressed image by using a gzip-compatible tool. In either case, RESTORE detects whether the image needs to be compressed.

For a MySQL server to be able to produce compressed images, it must be compiled with zlib support (see Typical configure Options). If WITH COMPRESSION is specified and zlib support is not present, BACKUP DATABASE fails with an error.

If you produce a compressed image with a server that has zlib support, the image cannot be restored by another server unless that server also has zlib support, or unless you manually uncompress the image first. Otherwise, RESTORE operations fail with an error.

Use of compression may make backup and restore operations faster by reducing the amount of disk I/O. There is some tradeoff due to the increased CPU load required for compression and uncompression calculations, but in general this is expected to be outweighed by the time savings from reduced I/O.

BACKUP DATABASE backs up database and table definitions, table data, views, stored programs (functions, procedures, triggers, events), and privileges. TEMPORARY tables are not included. For additional information about how privileges are backed up, see Section 1.2, “Quick Guide to MySQL Backup”.

For anything else not explicitly listed, assume that it is not backed up. This includes but is not limited to items such as UDF definitions and files, logs, and option files.

The BACKUP DATABASE statement does not back up the mysql, INFORMATION_SCHEMA, or performance_schema databases, and silently ignores them if you use the * database selector syntax. Do not include them in the list of names if you specify database names explicitly. To back up the mysql database, you can use the mysqldump program. For an example backup strategy that combines BACKUP DATABASE with mysqldump, see Section 1.2, “Quick Guide to MySQL Backup”.

The BACKUP DATABASE statement is not written to the binary log and does not replicate to slave servers.

For general information about BACKUP DATABASE and RESTORE, see Chapter 1, Using MySQL Backup. Limitations on the use of these statements are discussed in Chapter 6, Restrictions on MySQL Backup.