MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

4.5.6 mysqlpump — A Database Backup Program

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

Note

mysqlpump is deprecated as of MySQL 8.0.34; expect it to be removed in a future version of MySQL. You can use such MySQL programs as mysqldump and MySQL Shell to perform logical backups, dump databases, and similar tasks instead.

Tip

Consider using the MySQL Shell dump utilities, which provide parallel dumping with multiple threads, file compression, and progress information display, as well as cloud features such as Oracle Cloud Infrastructure Object Storage streaming, and MySQL HeatWave Service compatibility checks and modifications. Dumps can be easily imported into a MySQL Server instance or a MySQL HeatWave Service DB System using the MySQL Shell load dump utilities. Installation instructions for MySQL Shell can be found here.

mysqlpump features include:

Note

mysqlpump uses MySQL features introduced in MySQL 5.7, and thus assumes use with MySQL 5.7 or higher.

mysqlpump 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. The SELECT privilege on the mysql system database is required to dump user definitions. 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.

Note

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

mysqlpump [options] > dump.sql

However, UTF-16 is not permitted as a connection character set (see Section 10.4, “Connection Character Sets and Collations”), so the dump file cannot be loaded correctly. To work around this issue, use the --result-file option, which creates the output in ASCII format: 

mysqlpump [options] --result-file=dump.sql

mysqlpump Invocation Syntax

By default, mysqlpump dumps all databases (with certain exceptions noted in mysqlpump Restrictions). To specify this behavior explicitly, use the --all-databases option: 

mysqlpump --all-databases

To dump a single database, or certain tables within that database, name the database on the command line, optionally followed by table names: 

mysqlpump db_name
mysqlpump db_name tbl_name1 tbl_name2 ...

To treat all name arguments as database names, use the --databases option: 

mysqlpump --databases db_name1 db_name2 ...

By default, mysqlpump does not dump user account definitions, even if you dump the mysql system database that contains the grant tables. To dump grant table contents as logical definitions in the form of CREATE USER and GRANT statements, use the --users option and suppress all database dumping: 

mysqlpump --exclude-databases=% --users

In the preceding command, % is a wildcard that matches all database names for the --exclude-databases option.

mysqlpump supports several options for including or excluding databases, tables, stored programs, and user definitions. See mysqlpump Object Selection.

To reload a dump file, execute the statements that it contains. For example, use the mysql client: 

mysqlpump [options] > dump.sql
mysql < dump.sql

The following discussion provides additional mysqlpump usage examples.

To see a list of the options mysqlpump supports, issue the command mysqlpump --help.

mysqlpump Option Summary

mysqlpump supports the following options, which can be specified on the command line or in the [mysqlpump] and [client] groups of an option file. (Prior to MySQL 8.0.20, mysqlpump read the [mysql_dump] group rather than [mysqlpump]. As of 8.0.20, [mysql_dump] is still accepted but is deprecated.) For information about option files used by MySQL programs, see Section 4.2.2.2, “Using Option Files”.

Table 4.16 mysqlpump Options

Option Name Description Introduced Deprecated
--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-user Add DROP USER statement before each CREATE USER statement
--add-locks Surround each table dump with LOCK TABLES and UNLOCK TABLES statements
--all-databases Dump all databases
--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
--complete-insert Use complete INSERT statements that include column names
--compress Compress all information sent between client and server 8.0.18
--compress-output Output compression algorithm
--compression-algorithms Permitted compression algorithms for connections to server 8.0.18
--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
--default-parallelism Default number of threads for parallel processing
--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
--defer-table-indexes For reloading, defer index creation until after loading table rows
--events Dump events from dumped databases
--exclude-databases Databases to exclude from dump
--exclude-events Events to exclude from dump
--exclude-routines Routines to exclude from dump
--exclude-tables Tables to exclude from dump
--exclude-triggers Triggers to exclude from dump
--exclude-users Users to exclude from dump
--extended-insert Use multiple-row INSERT syntax
--get-server-public-key Request RSA public key from server
--help Display help message and exit
--hex-blob Dump binary columns using hexadecimal notation
--host Host on which MySQL server is located
--include-databases Databases to include in dump
--include-events Events to include in dump
--include-routines Routines to include in dump
--include-tables Tables to include in dump
--include-triggers Triggers to include in dump
--include-users Users to include in dump
--insert-ignore Write INSERT IGNORE rather than INSERT statements
--log-error-file Append warnings and errors to named file
--login-path Read login path options from .mylogin.cnf
--max-allowed-packet Maximum packet length to send to or receive from server
--net-buffer-length Buffer size for TCP/IP and socket communication
--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-defaults Read no option files
--parallel-schemas Specify schema-processing parallelism
--password Password to use when connecting to server
--password1 First multifactor authentication password to use when connecting to server 8.0.27
--password2 Second multifactor authentication password to use when connecting to server 8.0.27
--password3 Third multifactor authentication password to use when connecting to server 8.0.27
--plugin-dir Directory where plugins are installed
--port TCP/IP port number for connection
--print-defaults Print default options
--protocol Transport protocol to use
--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
--server-public-key-path Path name to file containing RSA public key
--set-charset Add SET NAMES default_character_set to output
--set-gtid-purged Whether to add SET @@GLOBAL.GTID_PURGED to output
--single-transaction Dump tables within single transaction
--skip-definer Omit DEFINER and SQL SECURITY clauses from view and stored program CREATE statements
--skip-dump-rows Do not dump table rows
--skip-generated-invisible-primary-key Do not dump information about generated invisible primary keys 8.0.30
--socket Unix socket file or Windows named pipe 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 Permissible 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.34
--ssl-key File that contains X.509 key
--ssl-mode Desired security state of connection to server
--ssl-session-data File that contains SSL session data 8.0.29
--ssl-session-data-continue-on-failed-reuse Whether to establish connections if session reuse fails 8.0.29
--tls-ciphersuites Permissible TLSv1.3 ciphersuites for encrypted connections 8.0.16
--tls-version Permissible TLS protocols 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
--users Dump user accounts
--version Display version information and exit
--watch-progress Display progress indicator
--zstd-compression-level Compression level for connections to server that use zstd compression 8.0.18

mysqlpump Option Descriptions

mysqlpump Object Selection

mysqlpump has a set of inclusion and exclusion options that enable filtering of several object types and provide flexible control over which objects to dump:

Any inclusion or exclusion option may be given multiple times. The effect is additive. Order of these options does not matter.

The value of each inclusion and exclusion option is a list of comma-separated names of the appropriate object type. For example: 

--exclude-databases=test,world
--include-tables=customer,invoice

Wildcard characters are permitted in the object names:

For example, --include-tables=t%,__tmp matches all table names that begin with t and all five-character table names that end with tmp.

For users, a name specified without a host part is interpreted with an implied host of %. For example, u1 and u1@% are equivalent. This is the same equivalence that applies in MySQL generally (see Section 6.2.4, “Specifying Account Names”).

Inclusion and exclusion options interact as follows:

If multiple databases are being dumped, it is possible to name tables, triggers, and routines in a specific database by qualifying the object names with the database name. The following command dumps databases db1 and db2, but excludes tables db1.t1 and db2.t2: 

mysqlpump --include-databases=db1,db2 --exclude-tables=db1.t1,db2.t2

The following options provide alternative ways to specify which databases to dump:

mysqlpump Parallel Processing

mysqlpump can use parallelism to achieve concurrent processing. You can select concurrency between databases (to dump multiple databases simultaneously) and within databases (to dump multiple objects from a given database simultaneously).

By default, mysqlpump sets up one queue with two threads. You can create additional queues and control the number of threads assigned to each one, including the default queue:

mysqlpump uses the default queue for processing any databases not named explicitly with a --parallel-schemas option, and for dumping user definitions if command options select them.

In general, with multiple queues, mysqlpump uses parallelism between the sets of databases processed by the queues, to dump multiple databases simultaneously. For a queue that uses multiple threads, mysqlpump uses parallelism within databases, to dump multiple objects from a given database simultaneously. Exceptions can occur; for example, mysqlpump may block queues while it obtains from the server lists of objects in databases.

With parallelism enabled, it is possible for output from different databases to be interleaved. For example, INSERT statements from multiple tables dumped in parallel can be interleaved; the statements are not written in any particular order. This does not affect reloading because output statements qualify object names with database names or are preceded by USE statements as required.

The granularity for parallelism is a single database object. For example, a single table cannot be dumped in parallel using multiple threads.

Examples: 

mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3

mysqlpump sets up a queue to process db1 and db2, another queue to process db3, and a default queue to process all other databases. All queues use two threads. 

mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
          --default-parallelism=4

This is the same as the previous example except that all queues use four threads. 

mysqlpump --parallel-schemas=5:db1,db2 --parallel-schemas=3:db3

The queue for db1 and db2 uses five threads, the queue for db3 uses three threads, and the default queue uses the default of two threads.

As a special case, with --default-parallelism=0 and no --parallel-schemas options, mysqlpump runs as a single-threaded process and creates no queues.

mysqlpump Restrictions

mysqlpump does not dump the performance_schema, ndbinfo, 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 or --include-databases option.

mysqlpump does not dump the INFORMATION_SCHEMA schema.

mysqlpump does not dump InnoDB CREATE TABLESPACE statements.

mysqlpump dumps user accounts in logical form using CREATE USER and GRANT statements (for example, when you use the --include-users or --users option). For this reason, dumps of the mysql system database do not by default include the grant tables that contain user definitions: user, db, tables_priv, columns_priv, procs_priv, or proxies_priv. To dump any of the grant tables, name the mysql database followed by the table names: 

mysqlpump mysql user db ...
MySQL Documentation Library