Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 38.2Mb
PDF (A4) - 38.3Mb
PDF (RPM) - 33.1Mb
HTML Download (TGZ) - 8.1Mb
HTML Download (Zip) - 8.1Mb
HTML Download (RPM) - 7.0Mb
Man Pages (TGZ) - 133.2Kb
Man Pages (Zip) - 189.3Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  mysqlpump — A Database Backup Program

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.

mysqlpump features include:

  • Parallel processing of databases, and of objects within databases, to speed up the dump process

  • Better control over which databases and database objects (tables, stored programs, user accounts) to dump

  • Dumping of user accounts as account-management statements (CREATE USER, GRANT) rather than as inserts into the mysql system database

  • Capability of creating compressed output

  • Progress indicator (the values are estimates)

  • For dump file reloading, faster secondary index creation for InnoDB tables by adding indexes after rows are inserted

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:

shell> 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 will not load correctly. To work around this issue, use the --result-file option, which creates the output in ASCII format:

shell> 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:

shell> 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:

shell> mysqlpump db_name
shell> mysqlpump db_name tbl_name1 tbl_name2 ...

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

shell> 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:

shell> 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:

shell> mysqlpump [options] > dump.sql
shell> 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. For information about option files used by MySQL programs, see Section 4.2.7, “Using Option Files”.

Table 4.13 mysqlpump 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-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 8.0.2
--complete-insert Use complete INSERT statements that include column names
--compress Compress all information sent between client and server
--compress-output Output compression algorithm
--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 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)
--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
--plugin-dir Directory where plugins are installed
--port TCP/IP port number for connection
--print-defaults Print default options
--protocol Connection 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
--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 8.0.1
--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
--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 the client side 8.0.11
--ssl-key File that contains X.509 key
--ssl-mode Security state of connection to server
--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
--users Dump user accounts
--version Display version information and exit
--watch-progress Display progress indicator

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 comma-separated list of names of the appropriate object type. For example:

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

Wildcard characters are permitted in the object names:

  • % matches any sequence of zero or more characters.

  • _ matches any single character.

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:

  • By default, with no inclusion or exclusion options, mysqlpump dumps all databases (with certain exceptions noted in mysqlpump Restrictions).

  • If inclusion options are given in the absence of exclusion options, only the objects named as included are dumped.

  • If exclusion options are given in the absence of inclusion options, all objects are dumped except those named as excluded.

  • If inclusion and exclusion options are given, all objects named as excluded and not named as included are not dumped. All other objects are dumped.

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:

shell> 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:

  • --default-parallelism=N specifies the default number of threads used for each queue. In the absence of this option, N is 2.

    The default queue always uses the default number of threads. Additional queues use the default number of threads unless you specify otherwise.

  • --parallel-schemas=[N:]db_list sets up a processing queue for dumping the databases named in db_list and optionally specifies how many threads the queue uses. db_list is a comma-separated list of database names. If the option argument begins with N:, the queue uses N threads. Otherwise, the --default-parallelism option determines the number of queue threads.

    Multiple instances of the --parallel-schemas option create multiple queues.

    Names in the database list are permitted to contain the same % and _ wildcard characters supported for filtering options (see mysqlpump Object Selection).

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:

shell> 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.

shell> 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.

shell> 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:

shell> mysqlpump mysql user db ...

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
Sign Up Login You must be logged in to post a comment.