The Sysadmin Notebook  

Sitemap

MYSQL Backup

Backup and Restore a MySQL database

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

mysqldump

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-drop-table
Add a DROP TABLE statement before each CREATE TABLE statement
--add-locks.
wraps each table dump with lock/unlock statements, resulting in faster inserts when table is reloaded.
--create-options
Include all MySQL-specific table options in the CREATE TABLE statements.
--disable-keys
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.
--lock-tables
locks tables before dumping them
--quick
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.
--set-charset
Add SET NAMES default_character_set to the output.
--comments, -i
Write program version, server version and host to dump file.
--dump-date
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-drop-database
Add a DROP DATABASE statement before each CREATE DATABASE statement
--all-databases, -A
Dump all tables in all databases.
--allow-keywords
Allow creation of column names that are keywords.
--character-sets-dir=path
Specify path to installed character sets.
--compatible=name,...,name
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.
--debug
Write debugging log.
--debug-info
Print debugging information when program exits. Includes memory and CPU statistics.
--default-character-set=charset_name
Use charset_name as default. 'utf8' is used if none specified.
--delayed-insert
Write INSERT DELAYED instead of INSERT statements.
--delete-master-logs
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.
--flush-privileges
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.
--host=hostname
Dump data from hostname
--hex-blob
Dump binary columns (BINARY, VARBINARY, BLOB) using hexadecimal notation.
--ignore-table=dbname.tblname
Skip specified table.
--insert-ignore
Write INSERT statements with IGNORE option
--lines-terminated-by=...
Specify line-termination character.
--lock-all-tables, -x
Lock all tables across all databases.
--lock-tables
Lock all tables before each database dump.
--log-error=filename
Log errors to filename
--master-data[=value]
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.
--no-autocommit
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.
--order-by-primary
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.
--protocol=...
Specify connection protocol as one of: TCP, SOCKET, PIPE, MEMORY
--quote-names, -Q
Quote database, table and column names with '`' characters.
--results-file=filename
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.
--single-transaction
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
--compact
Turns on options:
  • --skip-add-drop-table
  • --skip-add-locks
  • --skip-comments
  • --skip-disable-keys
  • --skip-set-charset
--opt
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