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客户端实用程序执行逻辑备份，生成一组SQL语句，可以执行这些语句来重现原始数据库对象定义和表数据。它转储一个或多个MySQL数据库以进行备份或传输到另一个SQL服务器。

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 Database Service compatibility checks and modifications. Dumps can be easily imported into a MySQL Server instance or a MySQL Database 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.要重新加载转储文件，您必须具有执行其中包含的语句所需的权限，例如对这些语句创建的对象具有适当的CREATE权限。

Note注意

A dump made using PowerShell on Windows with output redirection creates a file that has UTF-16 encoding:在带有输出重定向的Windows上使用PowerShell进行的转储会创建一个具有UTF-16编码的文件：

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. 但是，UTF-16不允许作为连接字符集（请参阅第10.4节，“连接字符集和排序规则”），因此无法正确加载转储文件。To work around this issue, use the --result-file option, which creates the output in ASCII format:要解决此问题，请使用--result-file选项，该选项将创建ASCII格式的输出：

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

mysqlpump Invocation Syntaxmysqlpump调用语法

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表4.16 mysqlpump选项

Option NameDescriptionIntroducedDeprecated
--add-drop-databaseAdd DROP DATABASE statement before each CREATE DATABASE statement
--add-drop-tableAdd DROP TABLE statement before each CREATE TABLE statement
--add-drop-userAdd DROP USER statement before each CREATE USER statement
--add-locksSurround each table dump with LOCK TABLES and UNLOCK TABLES statements
--all-databasesDump all databases
--bind-addressUse specified network interface to connect to MySQL Server
--character-sets-dirDirectory where character sets are installed
--column-statisticsWrite ANALYZE TABLE statements to generate statistics histograms
--complete-insertUse complete INSERT statements that include column names
--compressCompress all information sent between client and server8.0.18
--compress-outputOutput compression algorithm
--compression-algorithmsPermitted compression algorithms for connections to server8.0.18
--databasesInterpret all name arguments as database names
--debugWrite debugging log
--debug-checkPrint debugging information when program exits
--debug-infoPrint debugging information, memory, and CPU statistics when program exits
--default-authAuthentication plugin to use
--default-character-setSpecify default character set
--default-parallelismDefault number of threads for parallel processing
--defaults-extra-fileRead named option file in addition to usual option files
--defaults-fileRead only named option file
--defaults-group-suffixOption group suffix value
--defer-table-indexesFor reloading, defer index creation until after loading table rows
--eventsDump events from dumped databases
--exclude-databasesDatabases to exclude from dump
--exclude-eventsEvents to exclude from dump
--exclude-routinesRoutines to exclude from dump
--exclude-tablesTables to exclude from dump
--exclude-triggersTriggers to exclude from dump
--exclude-usersUsers to exclude from dump
--extended-insertUse multiple-row INSERT syntax
--get-server-public-keyRequest RSA public key from server
--helpDisplay help message and exit
--hex-blobDump binary columns using hexadecimal notation
--hostHost on which MySQL server is located
--include-databasesDatabases to include in dump
--include-eventsEvents to include in dump
--include-routinesRoutines to include in dump
--include-tablesTables to include in dump
--include-triggersTriggers to include in dump
--include-usersUsers to include in dump
--insert-ignoreWrite INSERT IGNORE rather than INSERT statements
--log-error-fileAppend warnings and errors to named file
--login-pathRead login path options from .mylogin.cnf
--max-allowed-packetMaximum packet length to send to or receive from server
--net-buffer-lengthBuffer size for TCP/IP and socket communication
--no-create-dbDo not write CREATE DATABASE statements
--no-create-infoDo not write CREATE TABLE statements that re-create each dumped table
--no-defaultsRead no option files
--parallel-schemasSpecify schema-processing parallelism
--passwordPassword to use when connecting to server
--plugin-dirDirectory where plugins are installed
--portTCP/IP port number for connection
--print-defaultsPrint default options
--protocolTransport protocol to use
--replaceWrite REPLACE statements rather than INSERT statements
--result-fileDirect output to a given file
--routinesDump stored routines (procedures and functions) from dumped databases
--server-public-key-pathPath name to file containing RSA public key
--set-charsetAdd SET NAMES default_character_set to output
--set-gtid-purgedWhether to add SET @@GLOBAL.GTID_PURGED to output
--single-transactionDump tables within single transaction
--skip-definerOmit DEFINER and SQL SECURITY clauses from view and stored program CREATE statements
--skip-dump-rowsDo not dump table rows
--socketUnix socket file or Windows named pipe to use
--ssl-caFile that contains list of trusted SSL Certificate Authorities
--ssl-capathDirectory that contains trusted SSL Certificate Authority certificate files
--ssl-certFile that contains X.509 certificate
--ssl-cipherPermissible ciphers for connection encryption
--ssl-crlFile that contains certificate revocation lists
--ssl-crlpathDirectory that contains certificate revocation-list files
--ssl-fips-modeWhether to enable FIPS mode on client side
--ssl-keyFile that contains X.509 key
--ssl-modeDesired security state of connection to server
--tls-ciphersuitesPermissible TLSv1.3 ciphersuites for encrypted connections8.0.16
--tls-versionPermissible TLS protocols for encrypted connections
--triggersDump triggers for each dumped table
--tz-utcAdd SET TIME_ZONE='+00:00' to dump file
--userMySQL user name to use when connecting to server
--usersDump user accounts
--versionDisplay version information and exit
--watch-progressDisplay progress indicator
--zstd-compression-levelCompression level for connections to server that use zstd compression8.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 ...