4.5.4 mysqldump — A Database Backup Program

The mysqldump client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, SUPER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.

To reload a dump file, you must have the same privileges needed to create each of the dumped objects by issuing CREATE statements manually.

If you are doing a backup on the server and your tables all are MyISAM tables, consider using the mysqlhotcopy instead because it can accomplish faster backups and faster restores. See Section 4.6.9, “mysqlhotcopy — A Database Backup Program”.

There are three general ways to invoke mysqldump:

shell> mysqldump [options] db_name [tbl_name ...]
shell> mysqldump [options] --databases db_name ...
shell> mysqldump [options] --all-databases

If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are dumped.

mysqldump does not dump the INFORMATION_SCHEMA database. If you name that database explicitly on the command line, mysqldump silently ignores it.

To see a list of the options your version of mysqldump supports, execute mysqldump --help.

Some mysqldump options are shorthand for groups of other options:

To reverse the effect of a group option, uses its --skip-xxx form (--skip-opt or --skip-compact). It is also possible to select only part of the effect of a group option by following it with options that enable or disable specific features. Here are some examples:

When you selectively enable or disable the effect of a group option, order is important because options are processed first to last. For example, --disable-keys --lock-tables --skip-opt would not have the intended effect; it is the same as --skip-opt by itself.

mysqldump can retrieve and dump table contents row by row, or it can retrieve the entire content from a table and buffer it in memory before dumping it. Buffering in memory can be a problem if you are dumping large tables. To dump tables row by row, use the --quick option (or --opt, which enables --quick). The --opt option (and hence --quick) is enabled by default, so to enable memory buffering, use --skip-quick.

If you are using a recent version of mysqldump to generate a dump to be reloaded into a very old MySQL server, you should not use the --opt or --extended-insert option. Use --skip-opt instead.

Before MySQL 4.1.2, out-of-range numeric values such as -inf and inf, as well as NaN (not-a-number) values are dumped by mysqldump as NULL. You can see this using the following sample table:

mysql> CREATE TABLE t (f DOUBLE);
mysql> INSERT INTO t VALUES(1e+111111111111111111111);
mysql> INSERT INTO t VALUES(-1e111111111111111111111);
mysql> SELECT f FROM t;
+------+
| f    |
+------+
|  inf |
| -inf |
+------+

For this table, mysqldump produces the following data output:

--
-- Dumping data for table `t`
--

INSERT INTO t VALUES (NULL);
INSERT INTO t VALUES (NULL);

The significance of this behavior is that if you dump and restore the table, the new table has contents that differ from the original contents. This problem is fixed as of MySQL 4.1.2; you cannot insert inf in the table, so this mysqldump behavior is only relevant when you deal with old servers.

For additional information about mysqldump, see Section 7.4, “Using mysqldump for Backups”.

mysqldump supports the following options, which can be specified on the command line or in the [mysqldump] and [client] groups of an option file. For information about option files, see Section 4.2.6, “Using Option Files”.

Table 4.5 mysqldump Options

FormatOption FileDescriptionIntroduced
--add-drop-databaseadd-drop-databaseAdd a DROP DATABASE statement before each CREATE DATABASE statement 
--add-drop-tableadd-drop-tableAdd a DROP TABLE statement before each CREATE TABLE statement 
--add-locksadd-locksSurround each table dump with LOCK TABLES and UNLOCK TABLES statements 
--all-databasesall-databasesDump all tables in all databases 
--allow-keywordsallow-keywordsAllow creation of column names that are keywords 
--commentscommentsAdd comments to the dump file 
--compactcompactProduce more compact output 
--compatible=name[,name,...]compatibleProduce output that is more compatible with other database systems or with older MySQL servers 
--complete-insertcomplete-insertUse complete INSERT statements that include column names 
--create-optionscreate-optionsInclude all MySQL-specific table options in CREATE TABLE statements 
--databasesdatabasesDump several databases 
--debug[=debug_options]debugWrite a debugging log 
--debug-infodebug-infoPrint debugging information, memory and CPU statistics when the program exits5.0.32
--default-character-set=charset_namedefault-character-setUse charset_name as the default character set 
--defaults-extra-file=file_name Read option file in addition to the usual option files 
--defaults-file=file_name Read only the given option file 
--defaults-group-suffix=str Option group suffix value5.0.10
--delayed-insertdelayed-insertWrite INSERT DELAYED statements rather than INSERT statements 
--delete-master-logsdelete-master-logsOn a master replication server, delete the binary logs after performing the dump operation 
--disable-keysdisable-keysFor each table, surround the INSERT statements with statements to disable and enable keys 
--dump-datedump-dateInclude dump date as "Dump completed on" comment if --comments is given5.0.52
--extended-insertextended-insertUse multiple-row INSERT syntax that include several VALUES lists 
--fields-enclosed-by=stringfields-enclosed-byThis option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE 
--fields-escaped-byfields-escaped-byThis option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE 
--fields-optionally-enclosed-by=stringfields-optionally-enclosed-byThis option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE 
--fields-terminated-by=stringfields-terminated-byThis option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE 
--first-slavefirst-slaveDeprecated; use --lock-all-tables instead 
--flush-logsflush-logsFlush the MySQL server log files before starting the dump 
--flush-privilegesflush-privilegesEmit a FLUSH PRIVILEGES statement after dumping the mysql database 
--help Display help message and exit 
--hex-blobhex-blobDump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263) 
--hosthostHost to connect to (IP address or hostname) 
--ignore-table=db_name.tbl_nameignore-tableDo not dump the given table 
--insert-ignoreinsert-ignoreWrite INSERT IGNORE statements rather than INSERT statements 
--lines-terminated-by=stringlines-terminated-byThis option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE 
--lock-all-tableslock-all-tablesLock all tables across all databases 
--lock-tableslock-tablesLock all tables before dumping them 
--log-error=file_namelog-errorAppend warnings and errors to the named file5.0.42
--master-data[=value]master-dataWrite the binary log file name and position to the output 
--max_allowed_packet=valuemax_allowed_packetThe maximum packet length to send to or receive from the server 
--net_buffer_length=valuenet_buffer_lengthThe buffer size for TCP/IP and socket communication 
--no-autocommitno-autocommitEnclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements 
--no-create-dbno-create-dbThis option suppresses the CREATE DATABASE statements 
--no-create-infono-create-infoDo not write CREATE TABLE statements that re-create each dumped table 
--no-datano-dataDo not dump table contents 
--no-defaults Do not read any option files 
--no-set-namesno-set-namesSame as --skip-set-charset 
--optoptShorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. 
--order-by-primaryorder-by-primaryDump each table's rows sorted by its primary key, or by its first unique index 
--password[=password]passwordThe password to use when connecting to the server 
--pipe On Windows, connect to server using a named pipe 
--port=port_numportThe TCP/IP port number to use for the connection 
--print-defaults Print defaults 
--protocol=typeprotocolThe connection protocol to use 
--quickquickRetrieve rows for a table from the server a row at a time 
--quote-namesquote-namesQuote identifiers within backtick characters 
--result-file=fileresult-fileDirect output to a given file 
--routinesroutinesDump stored routines (procedures and functions) from the dumped databases5.0.13
--set-charsetset-charsetAdd SET NAMES default_character_set to output 
--single-transactionsingle-transactionThis option issues a BEGIN SQL statement before dumping data from the server 
--skip-add-drop-tableskip-add-drop-tableDo not add a DROP TABLE statement before each CREATE TABLE statement 
--skip-add-locksskip-add-locksDo not add locks 
--skip-commentsskip-commentsDo not add comments to the dump file 
--skip-compactskip-compactDo not produce more compact output 
--skip-disable-keysskip-disable-keysDo not disable keys 
--skip-extended-insertskip-extended-insertTurn off extended-insert 
--skip-optskip-optTurn off the options set by --opt 
--skip-quickskip-quickDo not retrieve rows for a table from the server a row at a time 
--skip-quote-namesskip-quote-namesDo not quote identifiers 
--skip-set-charsetskip-set-charsetSuppress the SET NAMES statement 
--skip-triggersskip-triggersDo not dump triggers5.0.11
--skip-tz-utcskip-tz-utcTurn off tz-utc5.0.15
--socket=pathsocketFor connections to localhost 
--ssl-ca=file_namessl-caThe path to a file that contains a list of trusted SSL CAs 
--ssl-capath=dir_namessl-capathThe path to a directory that contains trusted SSL CA certificates in PEM format 
--ssl-cert=file_namessl-certThe name of the SSL certificate file to use for establishing a secure connection 
--ssl-cipher=cipher_listssl-cipherA list of allowable ciphers to use for SSL encryption 
--ssl-key=file_namessl-keyThe name of the SSL key file to use for establishing a secure connection 
--ssl-verify-server-certssl-verify-server-certThe server's Common Name value in its certificate is verified against the host name used when connecting to the server 
--tab=pathtabProduce tab-separated data files 
--tablestablesOverride the --databases or -B option 
--triggerstriggersDump triggers for each dumped table 
--tz-utctz-utcAdd SET TIME_ZONE='+00:00' to the dump file5.0.15
--user=user_nameuserMySQL user name to use when connecting to server 
--verbose Verbose mode 
--version Display version information and exit 
--where='where_condition'whereDump only rows selected by the given WHERE condition 
--xmlxmlProduce XML output 

You can also set the following variables by using --var_name=value syntax:

It is also possible to set variables by using --var_name=value. The --set-variable format is deprecated.

A common use of mysqldump is for making a backup of an entire database:

shell> mysqldump db_name > backup-file.sql

You can load the dump file back into the server like this:

shell> mysql db_name < backup-file.sql

Or like this:

shell> mysql -e "source /path-to-backup/backup-file.sql" db_name

mysqldump is also very useful for populating databases by copying data from one MySQL server to another:

shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name

It is possible to dump several databases with one command:

shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

To dump all databases, use the --all-databases option:

shell> mysqldump --all-databases > all_databases.sql

For InnoDB tables, mysqldump provides a way of making an online backup:

shell> mysqldump --all-databases --single-transaction > all_databases.sql

This backup acquires a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary log coordinates are read and the lock is released. If long updating statements are running when the FLUSH statement is issued, the MySQL server may get stalled until those statements finish. After that, the dump becomes lock free and does not disturb reads and writes on the tables. If the update statements that the MySQL server receives are short (in terms of execution time), the initial lock period should not be noticeable, even with many updates.

For point-in-time recovery (also known as roll-forward, when you need to restore an old backup and replay the changes that happened since that backup), it is often useful to rotate the binary log (see Section 5.2.3, “The Binary Log”) or at least know the binary log coordinates to which the dump corresponds:

shell> mysqldump --all-databases --master-data=2 > all_databases.sql

Or:

shell> mysqldump --all-databases --flush-logs --master-data=2
              > all_databases.sql

The --master-data and --single-transaction options can be used simultaneously, which provides a convenient way to make an online backup suitable for use prior to point-in-time recovery if tables are stored using the InnoDB storage engine.

For more information on making backups, see Section 7.2, “Database Backup Methods”, and Section 7.3, “Example Backup and Recovery Strategy”.

If you encounter problems backing up views, please read the section that covers restrictions on views which describes a workaround for backing up views when this fails due to insufficient privileges. See Section D.4, “Restrictions on Views”.