The System Administrator Notebook

Backup and Restore a MySQL database

Page Contents

Backup Recipes

Top Bottom

Server Backup

Top Bottom

For a complete server backup use one of the following commands:

  mysqldump --all-databases > backup.sql
  mysqldump --all-databases | gzip > backup.sql.gz
  mysqldump --all-databases | bzip2 > backup.sql.bz2

To restore the MySQL server use one of the following:

  mysql < backup.sql
  cat backup.sql | mysql
  zcat backup.sql.gz | mysql
  bzcat backup.sql.bz2 | mysql

To backup the server's databases, without table contents, use:

  mysqldump --all-databases --no-data > structure.sql

Database Backup

Top Bottom

To backup the data in a single database:

  mysqldump dbname > dbname.sql

To restore the data:

  cat dbname.sql | mysql dbname

If restoring to another server or if you need to restore the database in addition to the data, you will have to create the database first, and recreate permissions manually. To get a CREATE DATABASE statement included in the dump file, use the --databases (or -B) option

  mysqldump --databases dbname > dbname.sql

Multiple databases can be specified for the --databases option as a space-separated list. The --databases option generates CREATE DATABASE and USE statements, so when restoring the dump file, the database names do not need to be specified as an option to mysql:

  mysqldump --databases dbname1 dbname2 > multidb.sql
  cat multidb.sql | mysql

To exclude a specific table from a database backup, use the --ignore-table option:

  mysqldump dbname --ignore-table=dbname.tblname

Multiple --ignore-table options can be specified to skip multiple tables.

Table Backup

Top Bottom

To dump an individual table use:

  mysqldump dbname tblname

Multiple tables can be specified as a space-separated list.

  mysqldump dbname tblname1 tblname2 tblname3


Top Bottom

The mysqldump program is a command-line utility for creating MySQL database backups. The basic syntax is:

  mysqldump [options] [db_name [tbl_name...]]

To turn off an option, prepend the option with '--skip'. For example, to turn off '--quick', use '--skip-quick'.

Default Options

Top Bottom

By default, mysqldump runs with the following options set:

Add a DROP TABLE statement before each CREATE TABLE statement
wraps each table dump with lock/unlock statements, resulting in faster inserts when table is reloaded.
Include all MySQL-specific table options in the CREATE TABLE statements.
for each table, insert statements are wrapped with DISABLE/ENABLE KEYS statements. This allows the table to be reloaded quicker.
--extended-insert, -e
Use multiple row INSERT statements in dump file. Results in smaller dump files and faster inserts.
locks tables before dumping them
forces mysqldump to dump tables row by row. Without this option, tables are retrieved to memory before being written to disk, which can be a problem with large tables.
Add SET NAMES default_character_set to the output.
--comments, -i
Write program version, server version and host to dump file.
Adds DATE comment at the end of the dump file if --comments option enabled.

Additional Options

Top Bottom

Other options available:

--help, -?
displays a help message
Add a DROP DATABASE statement before each CREATE DATABASE statement
--all-databases, -A
Dump all tables in all databases.
Allow creation of column names that are keywords.
Specify path to installed character sets.
Produce output that is more compatible with other database systems. Possible values for name are:
  • ansi
  • mysql323
  • mysql40
  • postgressql
  • oracle
  • mssql
  • db2
  • maxdb
  • no_key_options
  • no_table_options
  • no_field_options
--complete-insert, -c
generate complete INSERT statements.
--compress, -C
Compress data sent between client and server.
--databases, -B
Allows specification of a space-seperated list of databases to dump. Prepends CREATE DATABASE and USE statements before each new database.
Write debugging log.
Print debugging information when program exits. Includes memory and CPU statistics.
Use charset_name as default. 'utf8' is used if none specified.
Write INSERT DELAYED instead of INSERT statements.
Deletes binary logs on a master replication server after dump completes.
--disable-keys, -K
Surrounds INSERT statements with ENABLE/DISABLE keys. Only effective on MyISAM tables.
--fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=...
Allows changing field separators, etc.
--flush-logs, -F
Flush the MySQL server log files before starting the dump.
Execute a FLUSH PRIVILEGES statement after dumping the 'mysql' database. Should be used when dumping the 'mysql' database or databases that depend on this database.
--force, -f
Continue even if an SQL error occurs during a table dump.
Dump data from hostname
Dump binary columns (BINARY, VARBINARY, BLOB) using hexadecimal notation.
Skip specified table.
Write INSERT statements with IGNORE option
Specify line-termination character.
--lock-all-tables, -x
Lock all tables across all databases.
Lock all tables before each database dump.
Log errors to filename
Used to dump a master replication server to produce a dump file that can be used to setup another slave server. If value is set to '2', CHANGE MASTER statement is written as a commment only. Default value is 1. Binary log option must be enabled for this option to work. Turns off --lock-tables option and enables --lock-all-tables.
Wrap INSERT statements for each table dumped within SET AUTOCOMMIT=0 and COMMIT statements.
--no-create-db, -n
Suppresses CREATE DATABASE statements.
--no-create-info, -t
Suppresses CREATE TABLE statements.
--no-data, -d
Do not write table row information. Dumps table structure only.
Sorts each table's rows by primary key or first unique index. Useful when dumping a MyISAM table for loading to a INNODB table.
--password[=password], -p
Password to use when connecting to the server.
--pipe, -W
On Windows, connect to the local server using a named pipe.
--port=port_number, -P port_number
The TCP/IP port number to use for the connection.
Specify connection protocol as one of: TCP, SOCKET, PIPE, MEMORY
--quote-names, -Q
Quote database, table and column names with '`' characters.
Writes output to filename. Useful on Windows to prevent '\n' being written as '\r\n'.
--routines, -R
Dump stored routines. Does not include attributes. To restore procedures with their creation and modification times preserved, dump the mysql.proc table instead.
Dumps transactional databases (InnoDB, BDB) in a consistent state. Adds a BEGIN SQL statement before dumping data. Cannot be used with --lock-tables, as --lock-tables causes pending transactions to be committed.

Group Options

Top Bottom
Turns on options:
  • --skip-add-drop-table
  • --skip-add-locks
  • --skip-comments
  • --skip-disable-keys
  • --skip-set-charset
Enabled by default. Normally, --opt will speed up the dump and restore process. Turns on options:
  • --add-drop-table
  • --add-locks
  • --create-options
  • --disable-keys
  • --extended-insert
  • --lock-tables
  • --quick
  • --set-charset