BACKUP {DATABASE | SCHEMA}
    { * | db_name [, db_name] ... }
    TO 'image_file_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 and must not already exist. It should be specified as a full pathname. The file is written to the server host. Its location must be in a directory where the server can create and write files.

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, “BACKUP DATABASE and RESTORE Status Reporting and Monitoring”.

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. For anything else not explicitly listed, assume that it is not backed up. This includes but is not limited to items such as privileges, 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.

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 BACKUP DATABASE and RESTORE”.

BACKUP DATABASE was added in MySQL 6.0.5.