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

This statement backs up one or more databases and writes the backup contents to an image file (a file containing database contents). The file must be named as a literal string. The file can be a regular file, in which case, it must not already exist. As of MySQL 6.0.6, the file can be an existing FIFO on Unix. The file is written to the server host. Its location must be in a directory where the server can create and write files.

As of MySQL 6.0.7, the backupdir system variable value is the default image file directory for BACKUP DATABASE operations. If an image file is named as a relative pathname, it is interpreted relative to the value of backupdir. The default value is the data directory. Before MySQL 6.0.7, the file should be specified as a full pathname.

BACKUP DATABASE requires the SUPER and FILE privileges, as well as the SELECT privilege for all objects to be backed up.

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';

The resulting image file 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.

Upon successful completion, the BACKUP DATABASE statement returns a result set with the backup number. Warnings produced during the operation can be displayed with SHOW WARNINGS. If the backup operation fails, it returns an error and any file created by the operation is deleted.

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

While the operation is in progress, it can be monitored as described in Section 6.3.3, “MySQL Backup Status Reporting and Monitoring”.

As of MySQL 6.0.7, 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 http://www.faqs.org/rfcs/rfc1952.html.

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. We recommend 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 will detect 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 Section 2.9.2, “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, the RESTORE operation will 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 we expect this to be outweighed by the time savings from reduced I/O.

BACKUP DATABASE backs up database and table definitions, table data, stored routines, triggers, events, and views. TEMPORARY tables are not included. Tablespace backup support is limited to the Falcon storage engine. Privileges are not saved before MySQL 6.0.7. As of 6.0.7, privileges for backed-up databases are saved. This includes privileges at the database level and below (table, column, routine). Global privileges are not saved. For additional information about how privileges are backed up, see Section 6.3.1, “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 or INFORMATION_SCHEMA databases. The statement 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 6.3.1, “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 Section 6.3, “Using MySQL Backup”. Limitations on the use of these statements are discussed in Section D.7, “Restrictions on BACKUP DATABASE and RESTORE”.

BACKUP DATABASE was added in MySQL 6.0.5.