MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

4.5.4 mysqldump — A Database Backup Program

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER 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 privileges required to execute the statements that it contains, such as the appropriate CREATE privileges for objects created by those statements.

mysqldump output can include ALTER DATABASE statements that change the database collation. These may be used when dumping stored programs to preserve their character encodings. To reload a dump file containing such statements, the ALTER privilege for the affected database is required.

Note

A dump made using PowerShell on Windows with output redirection creates a file that has UTF-16 encoding:

shell> mysqldump [options] > dump.sql

However, UTF-16 is not permitted as a connection character set (see Impermissible Client Character Sets), so the dump file will not load correctly. To work around this issue, use the --result-file option, which creates the output in ASCII format:

shell> mysqldump [options] --result-file=dump.sql

Performance and Scalability Considerations

mysqldump advantages include the convenience and flexibility of viewing or even editing the output before restoring. You can clone databases for development and DBA work, or produce slight variations of an existing database for testing. It is not intended as a fast or scalable solution for backing up substantial amounts of data. With large data sizes, even if the backup step takes a reasonable time, restoring the data can be very slow because replaying the SQL statements involves disk I/O for insertion, index creation, and so on.

For large-scale backup and restore, a physical backup is more appropriate, to copy the data files in their original format that can be restored quickly:

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, use the --skip-opt option instead of the --opt or --extended-insert option.

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

Invocation Syntax

There are in general three ways to use mysqldump—in order to dump a set of one or more tables, a set of one or more complete databases, or an entire MySQL server—as shown here:

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

To dump entire databases, do not name any tables following db_name, or use the --databases or --all-databases option.

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

Option Syntax - Alphabetical Summary

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 used by MySQL programs, see Section 4.2.2.2, “Using Option Files”.

Table 4.11 mysqldump Options

Format Description Introduced Removed
--add-drop-database Add DROP DATABASE statement before each CREATE DATABASE statement
--add-drop-table Add DROP TABLE statement before each CREATE TABLE statement
--add-drop-trigger Add DROP TRIGGER statement before each CREATE TRIGGER statement
--add-locks Surround each table dump with LOCK TABLES and UNLOCK TABLES statements
--all-databases Dump all tables in all databases
--allow-keywords Allow creation of column names that are keywords
--apply-slave-statements Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output
--bind-address Use specified network interface to connect to MySQL Server
--character-sets-dir Directory where character sets are installed
--column-statistics Write ANALYZE TABLE statements to generate statistics histograms 8.0.2
--comments Add comments to dump file
--compact Produce more compact output
--compatible Produce output that is more compatible with other database systems or with older MySQL servers
--complete-insert Use complete INSERT statements that include column names
--compress Compress all information sent between client and server
--create-options Include all MySQL-specific table options in CREATE TABLE statements
--databases Interpret all name arguments as database names
--debug Write debugging log
--debug-check Print debugging information when program exits
--debug-info Print debugging information, memory, and CPU statistics when program exits
--default-auth Authentication plugin to use
--default-character-set Specify default character set
--defaults-extra-file Read named option file in addition to usual option files
--defaults-file Read only named option file
--defaults-group-suffix Option group suffix value
--delete-master-logs On a master replication server, delete the binary logs after performing the dump operation
--disable-keys For each table, surround INSERT statements with statements to disable and enable keys
--dump-date Include dump date as "Dump completed on" comment if --comments is given
--dump-slave Include CHANGE MASTER statement that lists binary log coordinates of slave's master
--enable-cleartext-plugin Enable cleartext authentication plugin
--events Dump events from dumped databases
--extended-insert Use multiple-row INSERT syntax
--fields-enclosed-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--fields-escaped-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--fields-optionally-enclosed-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--fields-terminated-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--flush-logs Flush MySQL server log files before starting dump
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping mysql database
--force Continue even if an SQL error occurs during a table dump
--get-server-public-key Request RSA public key from server 8.0.3
--help Display help message and exit
--hex-blob Dump binary columns using hexadecimal notation
--host Host to connect to (IP address or hostname)
--ignore-error Ignore specified errors
--ignore-table Do not dump given table
--include-master-host-port Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with --dump-slave
--insert-ignore Write INSERT IGNORE rather than INSERT statements
--lines-terminated-by This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA
--lock-all-tables Lock all tables across all databases
--lock-tables Lock all tables before dumping them
--log-error Append warnings and errors to named file
--login-path Read login path options from .mylogin.cnf
--master-data Write the binary log file name and position to the output
--max-allowed-packet Maximum packet length to send to or receive from server
--net-buffer-length Buffer size for TCP/IP and socket communication
--network-timeout Increase network timeouts to permit larger table dumps 8.0.1
--no-autocommit Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements
--no-create-db Do not write CREATE DATABASE statements
--no-create-info Do not write CREATE TABLE statements that re-create each dumped table
--no-data Do not dump table contents
--no-defaults Read no option files
--no-set-names Same as --skip-set-charset
--no-tablespaces Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output
--opt Shorthand for --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset.
--order-by-primary Dump each table's rows sorted by its primary key, or by its first unique index
--password Password to use when connecting to server
--pipe On Windows, connect to server using named pipe
--plugin-dir Directory where plugins are installed
--port TCP/IP port number for connection
--print-defaults Print default options
--protocol Connection protocol to use
--quick Retrieve rows for a table from the server a row at a time
--quote-names Quote identifiers within backtick characters
--replace Write REPLACE statements rather than INSERT statements
--result-file Direct output to a given file
--routines Dump stored routines (procedures and functions) from dumped databases
--secure-auth Do not send passwords to server in old (pre-4.1) format 8.0.3
--server-public-key-path Path name to file containing RSA public key 8.0.4
--set-charset Add SET NAMES default_character_set to output
--set-gtid-purged Whether to add SET @@GLOBAL.GTID_PURGED to output
--shared-memory-base-name Name of shared memory to use for shared-memory connections
--single-transaction Issue a BEGIN SQL statement before dumping data from server
--skip-add-drop-table Do not add a DROP TABLE statement before each CREATE TABLE statement
--skip-add-locks Do not add locks
--skip-comments Do not add comments to dump file
--skip-compact Do not produce more compact output
--skip-disable-keys Do not disable keys
--skip-extended-insert Turn off extended-insert
--skip-opt Turn off options set by --opt
--skip-quick Do not retrieve rows for a table from the server a row at a time
--skip-quote-names Do not quote identifiers
--skip-set-charset Do not write SET NAMES statement
--skip-triggers Do not dump triggers
--skip-tz-utc Turn off tz-utc
--socket For connections to localhost, the Unix socket file to use
--ssl-ca File that contains list of trusted SSL Certificate Authorities
--ssl-capath Directory that contains trusted SSL Certificate Authority certificate files
--ssl-cert File that contains X.509 certificate
--ssl-cipher List of permitted ciphers for connection encryption
--ssl-crl File that contains certificate revocation lists
--ssl-crlpath Directory that contains certificate revocation-list files
--ssl-fips-mode Whether to enable FIPS mode on client side 8.0.11
--ssl-key File that contains X.509 key
--ssl-mode Security state of connection to server
--tab Produce tab-separated data files
--tables Override --databases or -B option
--tls-ciphersuites TLSv1.3 ciphersuites permitted for encrypted connections 8.0.16
--tls-version Protocols permitted for encrypted connections
--triggers Dump triggers for each dumped table
--tz-utc Add SET TIME_ZONE='+00:00' to dump file
--user MySQL user name to use when connecting to server
--verbose Verbose mode
--version Display version information and exit
--where Dump only rows selected by given WHERE condition
--xml Produce XML output

Connection Options

The mysqldump command logs into a MySQL server to extract information. The following options specify how to connect to the MySQL server, either on the same machine or a remote system.

Option-File Options

These options are used to control which option files to read.

DDL Options

Usage scenarios for mysqldump include setting up an entire new MySQL instance (including database tables), and replacing data inside an existing instance with existing databases and tables. The following options let you specify which things to tear down and set up when restoring a dump, by encoding various DDL statements within the dump file.

Debug Options

The following options print debugging information, encode debugging information in the dump file, or let the dump operation proceed regardless of potential problems.

Help Options

The following options display information about the mysqldump command itself.

Internationalization Options

The following options change how the mysqldump command represents character data with national language settings.

Replication Options

The mysqldump command is frequently used to create an empty instance, or an instance including data, on a slave server in a replication configuration. The following options apply to dumping and restoring data on replication master and slave servers.

Format Options

The following options specify how to represent the entire dump file or certain kinds of data in the dump file. They also control whether certain optional information is written to the dump file.

Filtering Options

The following options control which kinds of schema objects are written to the dump file: by category, such as triggers or events; by name, for example, choosing which databases and tables to dump; or even filtering rows from the table data using a WHERE clause.

Performance Options

The following options are the most relevant for the performance particularly of the restore operations. For large data sets, restore operation (processing the INSERT statements in the dump file) is the most time-consuming part. When it is urgent to restore data quickly, plan and test the performance of this stage in advance. For restore times measured in hours, you might prefer an alternative backup and restore solution, such as MySQL Enterprise Backup for InnoDB-only and mixed-use databases.

Performance is also affected by the transactional options, primarily for the dump operation.

Transactional Options

The following options trade off the performance of the dump operation, against the reliability and consistency of the exported data.

Option Groups

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.

Examples

To make a backup of an entire database:

shell> mysqldump db_name > backup-file.sql

To load the dump file back into the server:

shell> mysql db_name < backup-file.sql

Another way to reload the dump file:

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

You can 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 --master-data --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.4.4, “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”.

Restrictions

mysqldump does not dump the performance_schema or sys schema by default. To dump any of these, name them explicitly on the command line. You can also name them with the --databases option. For performance_schema, also use the --skip-lock-tables option.

mysqldump does not dump the INFORMATION_SCHEMA schema.

mysqldump does not dump InnoDB CREATE TABLESPACE statements.

mysqldump does not dump the NDB Cluster ndbinfo information database.

mysqldump includes statements to recreate the general_log and slow_query_log tables for dumps of the mysql database. Log table contents are not dumped.

If you encounter problems backing up views due to insufficient privileges, see Section C.5, “Restrictions on Views” for a workaround.