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. 考虑使用MySQL Shell转储实用程序，该实用程序提供多线程并行转储、文件压缩和进度信息显示，以及Oracle云基础设施对象存储流和MySQL数据库服务兼容性检查和修改等云功能。Dumps can be easily imported into a MySQL Server instance or a MySQL Database Service DB System using the MySQL Shell load dump utilities. 转储可以使用MySQL Shell加载转储实用程序轻松导入MySQL Server实例或MySQL数据库服务DB系统。Installation instructions for MySQL Shell can be found here.MySQL Shell的安装说明可以在这里找到。

mysqlpump features include:mysqlpump的功能包括：

Note注意

mysqlpump uses MySQL features introduced in MySQL 5.7, and thus assumes use with MySQL 5.7 or higher.mysqlpump使用MySQL 5.7中引入的MySQL功能，因此假设与MySQL 5.7或更高版本一起使用。

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. mysqlpump至少要求转储表具有SELECT权限，转储视图具有SHOW VIEW权限，转储触发器具有TRIGGER权限，如果不使用--single-transaction选项，则需要LOCK TABLES权限。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.转储用户定义需要mysql系统数据库上的SELECT权限。某些选项可能需要其他权限，如选项描述中所述。

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). 默认情况下，mysqlpump会转储所有数据库（mysqlpump限制中注明的某些例外情况除外）。To specify this behavior explicitly, use the --all-databases option:要明确指定此行为，请使用--all-databases选项：

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:要将所有名称参数视为数据库名称，请使用--databases选项：

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. 默认情况下，mysqlpump不会转储用户帐户定义，即使您转储了包含授权表的mysql系统数据库。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:要以CREATE USER和GRANT语句的形式将授权表内容作为逻辑定义转储，请使用--users选项并禁止所有数据库转储：

mysqlpump --exclude-databases=% --users

In the preceding command, % is a wildcard that matches all database names for the --exclude-databases option.在前面的命令中，%是一个通配符，与--exclude-databases选项的所有数据库名称匹配。

mysqlpump supports several options for including or excluding databases, tables, stored programs, and user definitions. See mysqlpump Object Selection.mysqlpump支持多种选项，用于包含或排除数据库、表、存储程序和用户定义。请参阅mysqlpump对象选择。

To reload a dump file, execute the statements that it contains. For example, use the mysql client:要重新加载转储文件，请执行其中包含的语句。例如，使用mysql客户端：

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

The following discussion provides additional mysqlpump usage examples.以下讨论提供了其他mysqlpump使用示例。

To see a list of the options mysqlpump supports, issue the command mysqlpump --help.要查看mysqlpump支持的选项列表，请发出命令mysqlpump --help。

mysqlpump Option Summarymysqlpump选项摘要

mysqlpump supports the following options, which can be specified on the command line or in the [mysqlpump] and [client] groups of an option file. mysqlpump支持以下选项，可以在命令行或选项文件的[mysqlpump]和[client]组中指定。(Prior to MySQL 8.0.20, mysqlpump read the [mysql_dump] group rather than [mysqlpump]. （在MySQL 8.0.20之前，mysqlpump读取的是[MySQL_dump]组，而不是[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”.截至8.0.20，[mysql_dump]仍然被接受，但已被弃用。)有关MySQL程序使用的选项文件的信息，请参阅第4.2.2.2节，“使用选项文件”。

Table 4.16 mysqlpump Options表4.16 mysqlpump选项

Option Name选项名称Description描述Introduced引入版本Deprecated弃用版本
--add-drop-databaseAdd DROP DATABASE statement before each CREATE DATABASE statement在每个CREATE DATABASE语句之前添加DROP DATABASE语句
--add-drop-tableAdd DROP TABLE statement before each CREATE TABLE statement在每个CREATE TABLE语句之前添加DROP TABLE语句
--add-drop-userAdd DROP USER statement before each CREATE USER statement在每个CREATE USER语句之前添加DROP USER语句
--add-locksSurround each table dump with LOCK TABLES and UNLOCK TABLES statements用LOCK TABLES和UNLOCK TABLES语句包围每个表转储
--all-databasesDump all databases转储所有数据库
--bind-addressUse specified network interface to connect to MySQL Server使用指定的网络接口连接MySQL服务器
--character-sets-dirDirectory where character sets are installed安装字符集的目录
--column-statisticsWrite ANALYZE TABLE statements to generate statistics histograms编写ANALYZE TABLE语句以生成统计直方图
--complete-insertUse complete INSERT statements that include column names使用包含列名的完整INSERT语句
--compressCompress all information sent between client and server压缩客户端和服务器之间发送的所有信息8.0.18
--compress-outputOutput compression algorithm输出压缩算法
--compression-algorithmsPermitted compression algorithms for connections to server服务器连接的允许压缩算法8.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程序退出时打印调试信息、内存和CPU统计信息
--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使用多行INSERT语法
--get-server-public-keyRequest RSA public key from server从服务器请求RSA公钥
--helpDisplay help message and exit显示帮助消息并退出
--hex-blobDump binary columns using hexadecimal notation使用十六进制表示法转储二进制列
--hostHost on which MySQL server is locatedMySQL服务器所在的主机
--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写INSERT IGNORE而不是INSERT语句
--log-error-fileAppend warnings and errors to named file将警告和错误附加到命名文件
--login-pathRead login path options from .mylogin.cnf从.mylogin.cnf读取登录路径选项
--max-allowed-packetMaximum packet length to send to or receive from server发送到服务器或从服务器接收的最大数据包长度
--net-buffer-lengthBuffer size for TCP/IP and socket communicationTCP/IP和套接字通信的缓冲区大小
--no-create-dbDo not write CREATE DATABASE statements不要编写CREATE DATABASE语句
--no-create-infoDo not write CREATE TABLE statements that re-create each dumped table不要编写CREATE 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用于连接的TCP/IP端口号
--print-defaultsPrint default options打印默认选项
--protocolTransport protocol to use要使用的传输协议
--replaceWrite REPLACE statements rather than INSERT statements编写REPLACE语句而不是INSERT语句
--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包含RSA公钥的文件的路径名
--set-charsetAdd SET NAMES default_character_set to output将SET NAMES default_character_SET添加到输出中
--set-gtid-purgedWhether to add SET @@GLOBAL.GTID_PURGED to output是否将SET @@GLOBAL.GTID_PURGED添加到输出
--single-transactionDump tables within single transaction在单个事务中转储表
--skip-definerOmit DEFINER and SQL SECURITY clauses from view and stored program CREATE statements从视图和存储程序CREATE语句中省略DEFINER和SQL SECURITY子句
--skip-dump-rowsDo not dump table rows不转储表行
--socketUnix socket file or Windows named pipe to use要使用的Unix套接字文件或Windows命名管道
--ssl-caFile that contains list of trusted SSL Certificate Authorities包含受信任SSL证书颁发机构列表的文件
--ssl-capathDirectory that contains trusted SSL Certificate Authority certificate files包含受信任的SSL证书颁发机构证书文件的目录
--ssl-certFile that contains X.509 certificate包含X.509证书的文件
--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是否在客户端启用FIPS模式
--ssl-keyFile that contains X.509 key包含X.509密钥的文件
--ssl-modeDesired security state of connection to server与服务器连接的所需安全状态
--tls-ciphersuitesPermissible TLSv1.3 ciphersuites for encrypted connections加密连接允许的TLSv1.3密码套件8.0.16
--tls-versionPermissible TLS protocols for encrypted connections加密连接的允许TLS协议
--triggersDump triggers for each dumped table每个转储表的转储触发器
--tz-utcAdd SET TIME_ZONE='+00:00' to dump file将SET TIME_ZONE='+00:00'添加到转储文件
--userMySQL user name to use when connecting to server连接到服务器时使用的MySQL用户名
--usersDump user accounts转储用户帐户
--versionDisplay version information and exit显示版本信息并退出
--watch-progressDisplay progress indicator显示进度指示器
--zstd-compression-levelCompression level for connections to server that use zstd compression使用zstd压缩的服务器连接的压缩级别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:mysqlpump有一组包含和排除选项，可以筛选多种对象类型，并灵活控制要转储的对象：

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.例如，--include-tables=t%,__tmp匹配以t开头的所有表名和以tmp结尾的所有五个字符的表名。

For users, a name specified without a host part is interpreted with an implied host of %. 对于用户，如果指定的名称没有主机部分，则将使用隐含的主机%进行解释。For example, u1 and u1@% are equivalent. 例如，u1和u1@%是等价的。This is the same equivalence that applies in MySQL generally (see Section 6.2.4, “Specifying Account Names”).这与MySQL中普遍适用的等效性相同（参阅第6.2.4节，“指定帐户名”）。

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:以下命令转储数据库db1和db2，但不包括表db1.t1和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 Processingmysqlpump并行处理

mysqlpump can use parallelism to achieve concurrent processing. mysqlpump可以使用并行性来实现并发处理。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会设置一个包含两个线程的队列。您可以创建其他队列并控制分配给每个队列的线程数，包括默认队列：

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.mysqlpump使用默认队列来处理任何未使用--parallel-schemas选项显式命名的数据库，并在命令选项选择用户定义时转储用户定义。

In general, with multiple queues, mysqlpump uses parallelism between the sets of databases processed by the queues, to dump multiple databases simultaneously. 一般来说，对于多个队列，mysqlpump使用队列处理的数据库集之间的并行性，同时转储多个数据库。For a queue that uses multiple threads, mysqlpump uses parallelism within databases, to dump multiple objects from a given database simultaneously. 对于使用多个线程的队列，mysqlpump使用数据库内的并行性，从给定的数据库中同时转储多个对象。Exceptions can occur; for example, mysqlpump may block queues while it obtains from the server lists of objects in databases.可能会出现例外情况；例如，mysqlpump在从服务器获取数据库中的对象列表时可能会阻止队列。

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. 启用并行性后，不同数据库的输出可以交错。例如，来自并行转储的多个表的INSERT语句可以交错；这些陈述没有按照任何特定的顺序写。This does not affect reloading because output statements qualify object names with database names or are preceded by USE statements as required.这不会影响重新加载，因为输出语句用数据库名称限定对象名称，或者根据需要在前面加上USE语句。

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设置了一个队列来处理db1和db2，另一个队列用于处理db3，还有一个默认队列用于处理所有其他数据库。所有队列都使用两个线程。

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.db1和db2的队列使用五个线程，db3的队列使用三个线程，默认队列使用两个线程的默认值。

As a special case, with --default-parallelism=0 and no --parallel-schemas options, mysqlpump runs as a single-threaded process and creates no queues.作为一种特殊情况，在--default-parallelism=0并且非--parallel-schemas选项下，mysqlpump作为单线程进程运行，不创建队列。

mysqlpump Restrictionsmysqlpump限制

mysqlpump does not dump the performance_schema, ndbinfo, or sys schema by default. 默认情况下，mysqlpump不会转储performance_schema、ndbinfo或sys模式。To dump any of these, name them explicitly on the command line. 要转储其中任何一个，请在命令行上明确命名它们。You can also name them with the --databases or --include-databases option.您还可以使用--databases或--included-databases选项来命名它们。

mysqlpump does not dump the INFORMATION_SCHEMA schema.mysqlpump不会转储INFORMATION_SCHEMA架构。

mysqlpump does not dump InnoDB CREATE TABLESPACE statements.mysqlpump不会转储InnoDB CREATE TABLESPACE语句。

mysqlpump dumps user accounts in logical form using CREATE USER and GRANT statements (for example, when you use the --include-users or --users option). mysqlpump使用CREATE USER和GRANT语句以逻辑形式转储用户帐户（例如，当您使用--include-users或--users选项时）。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. 因此，默认情况下，mysql系统数据库的转储不包括包含用户定义的授权表：user、db、tables_priv、columns_private、procs_priv或proxies_priv。To dump any of the grant tables, name the mysql database followed by the table names:要转储任何授权表，请命名mysql数据库，后跟表名：

mysqlpump mysql user db ...