6.5.6 mysqlpump — A Database Backup Program (original) (raw)

6.5.6 mysqlpump — A Database Backup Program

The mysqlpump client utility performslogical 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 theSELECT 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 appropriateCREATE 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 12.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 inmysqlpump 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 themysql system database that contains the grant tables. To dump grant table contents as logical definitions in the form of CREATE USER andGRANT 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 additionalmysqlpump 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 6.2.2.2, “Using Option Files”.

mysqlpump Option Descriptions

mysqlpump --exclude-databases=% --users

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

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 8.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 anddb2, but excludes tablesdb1.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 byUSE 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 processdb1 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 theperformance_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 theINFORMATION_SCHEMA schema.

mysqlpump does not dumpInnoDB CREATE TABLESPACE statements.

mysqlpump dumps user accounts in logical form using CREATE USER andGRANT 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, orproxies_priv. To dump any of the grant tables, name the mysql database followed by the table names:

mysqlpump mysql user db ...