Skip navigation links
Security in MySQL :: 5 Backup and Recovery :: 5.3 Using MySQL Backup :: 5.3.1 Quick Guide to MySQL Backup
5.3.2 How MySQL Backup Works »
Section Navigation      [Toggle]

5.3.1. Quick Guide to MySQL Backup

Use the BACKUP DATABASE and RESTORE statements like this:

  • BACKUP DATABASE backs up one or more databases to a named file: 

    BACKUP DATABASE world TO '/tmp/mybackupfile';

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

    BACKUP DATABASE world, sakila TO '/tmp/mybackupfile';

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

    BACKUP DATABASE * TO '/tmp/mybackupfile';

  • RESTORE restores databases using the contents of the backup file: 

    RESTORE FROM '/tmp/mybackupfile';

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.

Prior to MySQL 6.0.7, BACKUP DATABASE did not save any privileges in the backup image file and RESTORE did not restore privileges. As of MySQL 6.0.7, privileges are saved 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 are 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 restore 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.) Suppose that a backup contains this GRANT statement: 

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

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

    Restoration of privileges for accounts that do not exist is not done because that would implicitly create accounts that have no password, which is a security risk.

Storage of GRANT statement in backup image files has a security implication: Backup images should be stored in a secure location so that unauthorized users cannot modify the GRANT statements contained therein to change the privileges granted by restore operations.

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.

BACKUP DATABASE currently does not back up the contents of 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, reload the mysql database dump file using the mysql client: 

    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 the operation of the BACKUP DATABASE and RESTORE statements, see BACKUP DATABASE Syntax, and RESTORE Syntax.