Skip navigation links

Additional languages
MySQL 6.0 Reference Manual :: 6 Backup and Recovery :: 6.3 Using BACKUP DATABASE and RESTORE :: 6.3.1 Quick Guide to BACKUP DATABASE and RESTORE
6.3.2 How BACKUP DATABASE and RESTORE Work »
Section Navigation      [Toggle]

6.3.1. Quick Guide to BACKUP DATABASE and RESTORE

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. 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.

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 pathname 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 non-blocking 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 Section 12.5.3.1, “BACKUP DATABASE Syntax”, and Section 12.5.3.2, “RESTORE Syntax”.