Skip navigation links
MySQL Backup Reference Manual :: 1 Using MySQL Backup :: 1.2 Quick Guide to MySQL Backup
1.3 How MySQL Backup Works »
Section Navigation      [Toggle]

1.2. Quick Guide to MySQL Backup

To make a backup, use the BACKUP DATABASE statement, which backs up one or more databases to a named file on the server host: 

mysql> BACKUP DATABASE world TO '/tmp/mybackupfile';
+-----------+
| backup_id |
+-----------+
| 5506      |
+-----------+

To back up more than one database, separate the names by commas: 

mysql> BACKUP DATABASE world, sakila TO '/tmp/mybackupfile';
+-----------+
| backup_id |
+-----------+
| 5507      |
+-----------+

To select all databases for backup, use the * selector as a shortcut: 

mysql> BACKUP DATABASE * TO '/tmp/mybackupfile';
+-----------+
| backup_id |
+-----------+
| 5508      |
+-----------+

To restore databases from the contents of a backup file, use the RESTORE statement: 

mysql> RESTORE FROM '/tmp/mybackupfile';
+-----------+
| backup_id |
+-----------+
| 5509      |
+-----------+

If the backup file restores databases that already exist, an error occurs. To overwrite them, add the OVERWRITE option: 

mysql> RESTORE FROM '/tmp/mybackupfile' OVERWRITE;
+-----------+
| backup_id |
+-----------+
| 5510      |
+-----------+

BACKUP DATABASE backs up database and table definitions, table data, views, stored programs (functions, procedures, triggers, events), and privileges. TEMPORARY tables are not included. BACKUP DATABASE currently does not back up the mysql or INFORMATION_SCHEMA databases.

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.

Privileges are backed up and restored according to these rules:

  • BACKUP DATABASE saves privileges for the backed-up databases in the backup image file. The privileges are stored in the form of GRANT statements.

  • Only privileges at the database level or below (table, column, routine) are saved. Global privileges are not saved because they are not specific to the databases included in the backup.

  • Privileges that specify the database name using a pattern (containing the '%' or '_' wildcard character) are not saved because they might apply to databases not included in the backup.

  • For restore operations, only those privileges are restored that pertain to accounts that exist on the MySQL server performing the restore. Other privileges are ignored with a warning. (These warnings can be displayed with SHOW WARNINGS.) Restoration of privileges for nonexistent accounts is not done because that would implicitly create accounts that have no password, which is a security risk. Suppose that a backup contains this GRANT statement: 

    GRANT SELECT, INSERT ON db1.* to 'someuser'@'localhost'

    The privileges specified by this statement are restored if the 'someuser'@'localhost' account exists, and ignored with a warning otherwise.

Backup images should be stored in a secure location so that unauthorized users cannot modify them, for example, by modifying the GRANT statements contained therein to change the privileges granted by restore operations.

All storage engines that are represented by tables in the backup image must be enabled in the server on which a restore operation is performed. For example, if a backup image contains ARCHIVE tables and the server does not have the ARCHIVE storage engine enabled, the table cannot be restored and the operation fails.

As previously mentioned, BACKUP DATABASE does not back up the mysql database. This database contains the grant tables that define user accounts and their privileges, as well as other system information. To make a full server instance backup that includes account information in addition to data, use the BACKUP DATABASE statement together with the mysqldump program. In the following instructions, path represents the full path name to the directory where you store your backup files.

  1. Use mysqldump to back up the mysql database. This is a blocking operation that prevents changes to the database during the dump, but the mysql database normally is relatively small and can be dumped quickly: 

    shell> mysqldump --databases mysql > path/mysql-db.sql

  2. Use BACKUP DATABASE to back up the data from other databases. This is a nonblocking operation: 

    mysql> BACKUP DATABASE * TO 'path/other-dbs.bak';

Restore the server instance later like this:

  1. To restore the user accounts, use the mysql client to reload the mysql database dump file produced by mysqldump: 

    shell> mysql -u root -p < path/mysql-db.sql

  2. To restore the data for other databases, use RESTORE with the image file produced by BACKUP DATABASE: 

    mysql> RESTORE FROM 'path/other-dbs.bak';

For more information about BACKUP DATABASE and RESTORE, see Chapter 2, MySQL Backup Statements.