MYSQLPUMP(1)

📦
version 8.0.46 · 5 versions
, in fedora 43, fedora 44, ubuntu jammy, ubuntu noble

💾 MYSQLPUMP(1) — MySQL Database System

okay so bestie, welcome to the mysqlpump page 💅 this is the manual for the MySQL database backup program, and she is a lot but we're gonna get through it together. no cap.

🌸 NAME

mysqlpump — a database backup program

🦋 SYNOPSIS

mysqlpump [options] [db_name [tbl_name ...]]

✨ DESCRIPTION

lowkey this page has six lil subsections, so here's the vibe check:

  • mysqlpump Invocation Syntax
  • mysqlpump Option Summary
  • mysqlpump Option Descriptions
  • mysqlpump Object Selection
  • mysqlpump Parallel Processing
  • mysqlpump Restrictions

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[1], 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 compatibility checks and modifications. Dumps can be easily imported into a MySQL Server instance or a MySQL HeatWave DB System using the MySQL Shell load dump utilities[2]. Installation instructions for MySQL Shell can be found here[3].

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: 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 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 6.2.2.2, “Using Option Files”.

💅 mysqlpump Option Descriptions

okay bestie, here come the options. one bullet each, periodt.

  • --help, -? — Display a help message and exit.
    • Command-Line Format: --help
  • --add-drop-database — Write a DROP DATABASE statement before each CREATE DATABASE statement.
    • Command-Line Format: --add-drop-database
Note ⚠️ In MySQL 8.0, the mysql schema is considered a system schema that cannot be dropped by end users. If --add-drop-database is used with --all-databases or with --databases where the list of schemas to be dumped includes mysql, the dump file contains a DROP DATABASE `mysql` statement that causes an error when the dump file is reloaded. Instead, to use --add-drop-database, use --databases with a list of schemas to be dumped, where the list does not include mysql.
  • --add-drop-table — Write a DROP TABLE statement before each CREATE TABLE statement.
    • Command-Line Format: --add-drop-table
  • --add-drop-user — Write a DROP USER statement before each CREATE USER statement.
    • Command-Line Format: --add-drop-user
  • --add-locks — Surround each table dump with LOCK TABLES and UNLOCK TABLES statements. This results in faster inserts when the dump file is reloaded. See Section 10.2.5.1, “Optimizing INSERT Statements”.
    • Command-Line Format: --add-locks

This option does not work with parallelism because INSERT statements from different tables can be interleaved and UNLOCK TABLES following the end of the inserts for one table could release locks on tables for which inserts remain.

--add-locks and --single-transaction are mutually exclusive.

  • --all-databases, -A — Dump all databases (with certain exceptions noted in mysqlpump Restrictions). This is the default behavior if no other is specified explicitly.
    • Command-Line Format: --all-databases

--all-databases and --databases are mutually exclusive.

Note 👀 See the --add-drop-database description for information about an incompatibility of that option with --all-databases.

Prior to MySQL 8.0, the --routines and --events options for mysqldump and mysqlpump were not required to include stored routines and events when using the --all-databases option: The dump included the mysql system database, and therefore also the mysql.proc and mysql.event tables containing stored routine and event definitions. As of MySQL 8.0, the mysql.event and mysql.proc tables are not used. Definitions for the corresponding objects are stored in data dictionary tables, but those tables are not dumped. To include stored routines and events in a dump made using --all-databases, use the --routines and --events options explicitly.

  • --bind-address=ip_address — On a computer having multiple network interfaces, use this option to select which interface to use for connecting to the MySQL server.
    • Command-Line Format: --bind-address=ip_address
  • --character-sets-dir=path — The directory where character sets are installed. See Section 12.15, “Character Set Configuration”.
    • Command-Line Format: --character-sets-dir=dir_name
    • Type: Directory name
  • --column-statistics — adds ANALYZE TABLE statements into the output so that when you reload the dump, histogram statistics get generated for the dumped tables. ✨
    • Command-Line Format: --column-statistics
    • Type: Boolean
    • Default Value: OFF

Lowkey important: this one is off by default because building histograms for big tables can take <em>forever</em>. Like, go-make-a-snack forever. 🍓

  • --complete-insert — writes full INSERT statements, column names and all. No guessing games, bestie. 💅
    • Command-Line Format: --complete-insert
  • --compress, -C — compresses everything the client and server send each other, if possible. See Section 6.2.8, "Connection Compression Control".
    • Command-Line Format: --compress[={OFF|ON}]
    • Deprecated: 8.0.18
    • Type: Boolean
    • Default Value: OFF
🫠 Deprecated alert! As of MySQL 8.0.18 this option is deprecated, and you should expect it to be removed in a future version of MySQL. See the section called "Configuring Legacy Connection Compression".
  • --compress-output=algorithm — tells mysqlpump to compress the output using a specific algorithm. By default, mysqlpump does <em>not</em> compress output at all. Permitted algorithms are LZ4 and ZLIB. 🌈
    • Command-Line Format: --compress-output=algorithm
    • Type: Enumeration
    • Valid Values:
      • LZ4
      • ZLIB

To uncompress that compressed output you need the right utility on deck. If the system commands lz4 and openssl zlib aren't available, MySQL distributions ship lz4_decompress and zlib_decompress utilities that can decompress mysqlpump output that was compressed with the --compress-output=LZ4 and --compress-output=ZLIB options. For more info, see lz4_decompress(1) and zlib_decompress(1). 🎀

  • --compression-algorithms=value — the permitted compression algorithms for connections to the server. The available algorithms are the same as for the protocol_compression_algorithms system variable. Default is uncompressed. 😌
    • Command-Line Format: --compression-algorithms=value
    • Introduced: 8.0.18
    • Type: Set
    • Default Value: uncompressed
    • Valid Values:
      • zlib
      • zstd
      • uncompressed

For more information, see Section 6.2.8, "Connection Compression Control".

This option was added in MySQL 8.0.18. ✨

  • --databases, -B — normally, mysqlpump reads the first name argument on the command line as a database name and any names after that as table names. With this option it treats <em>all</em> name arguments as database names, and CREATE DATABASE statements get included in the output before each new database. 🗂️
    • Command-Line Format: --databases

--all-databases and --databases are mutually exclusive — pick one, bestie, you can't have both. 💀

Note: go peek at the --add-drop-database description for the tea about that option's incompatibility with --databases.
  • --debug[=debug_options], -# [debug_options] — writes a debugging log. A typical debug_options string is d:t:o,file_name. The default is d:t:O,/tmp/mysqlpump.trace. 🐛
    • Command-Line Format: --debug[=debug_options]
    • Type: String
    • Default Value: d:t:O,/tmp/mysqlpump.trace
🙈 Heads up: this option is available only if MySQL was built using WITH_DEBUG. MySQL release binaries provided by Oracle are not built using this option.
  • --debug-check — prints some debugging information when the program exits. 👋
    • Command-Line Format: --debug-check
    • Type: Boolean
    • Default Value: FALSE
🙈 Heads up: this option is available only if MySQL was built using WITH_DEBUG. MySQL release binaries provided by Oracle are not built using this option.
  • --debug-info, -T — prints debugging information plus memory and CPU usage statistics when the program exits. Full receipts on how hard it worked. 🔥
    • Command-Line Format: --debug-info
    • Type: Boolean
    • Default Value: FALSE
🙈 Heads up: this option is available only if MySQL was built using WITH_DEBUG. MySQL release binaries provided by Oracle are not built using this option.
  • --default-auth=plugin — a lil hint about which client-side authentication plugin to use. See Section 8.2.17, "Pluggable Authentication". 🔐
    • Command-Line Format: --default-auth=plugin
    • Type: String
  • --default-character-set=charset_name
    • Command-Line Format: --default-character-set=charset_name
    • Type: String
    • Default Value: utf8

Use charset_name as the default character set 🎀 See Section 12.15, "Character Set Configuration". And bestie, heads up — if no character set is specified at all, mysqlpump rolls with utf8mb4. ✨

  • --default-parallelism=N
    • Command-Line Format: --default-parallelism=N
    • Type: Integer
    • Default Value: 2

This is the default number of threads for each parallel processing queue 💅 and the default is 2, no cap.

The --parallel-schemas option also messes with parallelism and can override that default thread count. Want the full tea? Go read mysqlpump Parallel Processing. 🦋

So if you do --default-parallelism=0 and pass no --parallel-schemas options, mysqlpump runs as a single-threaded process and creates no queues at all. Just vibes, one thread, iconic.

And with parallelism enabled? It is possible for output from different databases to be interleaved. So don't be shocked when your dump looks like it's texting three people at once 😭

  • --defaults-extra-file=file_name
    • Command-Line Format: --defaults-extra-file=file_name
    • Type: File name

Read this option file after the global option file but (on Unix) before the user option file. If the file does not exist or is otherwise inaccessible, an error occurs — no file, no service, periodt. And if file_name isn't an absolute path name, it's interpreted relative to the current directory.

Want more on this and its option-file siblings? See Section 6.2.2.3, "Command-Line Options that Affect Option-File Handling". 🫶

  • --defaults-file=file_name
    • Command-Line Format: --defaults-file=file_name
    • Type: File name

Use only the given option file. Nothing else. If the file does not exist or is otherwise inaccessible, an error occurs. And if file_name is not an absolute path name, it is interpreted relative to the current directory.

Exception bestie, write this down: Even with --defaults-file, client programs read .mylogin.cnf. 👑

For additional information about this and other option-file options, see Section 6.2.2.3, "Command-Line Options that Affect Option-File Handling".

  • --defaults-group-suffix=str
    • Command-Line Format: --defaults-group-suffix=str
    • Type: String

Not just the usual option groups — this makes it read groups with the usual names plus a suffix of str 🌸 So like, mysqlpump normally reads the [client] and [mysqlpump] groups. But if you give this option as --defaults-group-suffix=_other, mysqlpump also reads the [client_other] and [mysqlpump_other] groups. Cute, right? 🎀

For additional information about this and other option-file options, see Section 6.2.2.3, "Command-Line Options that Affect Option-File Handling".

  • --defer-table-indexes
    • Command-Line Format: --defer-table-indexes
    • Type: Boolean
    • Default Value: TRUE

In the dump output, hold off on creating indexes for each table until after its rows have been loaded 🦋 This works for all storage engines, but for InnoDB it applies only for secondary indexes.

This one is enabled by default, so use --skip-defer-table-indexes if you want it gone 💅

  • --events
    • Command-Line Format: --events
    • Type: Boolean
    • Default Value: TRUE

Include Event Scheduler events for the dumped databases in the output ⭐ Heads up though: event dumping requires the EVENT privileges for those databases, so no perms, no events, iykyk.

The output generated by using --events contains CREATE EVENT statements to create the events. ✨

Enabled by default! Use --skip-events to disable it. 🙈

  • --exclude-databases=db_list
    • Command-Line Format: --exclude-databases=db_list
    • Type: String

Do not dump the databases in db_list, which is a list of one or more comma-separated database names. Multiple instances of this option are additive — stack 'em up, they all count 🫶 For more information, see mysqlpump Object Selection.

  • --exclude-events=event_list
    • Command-Line Format: --exclude-events=event_list
    • Type: String

Do not dump the databases in event_list, which is a list of one or more comma-separated event names. Multiple instances of this option are additive. For more information, see mysqlpump Object Selection. 🌈

  • --exclude-routines=routine_list
    • Command-Line Format: --exclude-routines=routine_list
    • Type: String

Do not dump the events in routine_list, which is a list of one or more comma-separated routine (stored procedure or function) names. Multiple instances of this option are additive. For more information, see mysqlpump Object Selection. 💜

  • --exclude-tables=table_list
    • Command-Line Format: --exclude-tables=table_list
    • Type: String

Do not dump the tables in table_list, which is a list of one or more comma-separated table names 🍓 Multiple instances of this option are additive. For more information, see mysqlpump Object Selection.

  • --exclude-triggers=trigger_list 💅
    • Command-Line Format: --exclude-triggers=trigger_list
    • Type: String
    • Okay so this one says do NOT dump the triggers sitting in trigger_list, which is literally just a list of one or more trigger names, comma-separated, bestie. Stack this option more than once and it's additive (they all pile up, no cap). Want the full tea? Go peek at mysqlpump Object Selection. ✨
  • --exclude-users=user_list 🫶
    • Command-Line Format: --exclude-users=user_list
    • Type: String
    • Same energy, but for people: skip the user accounts in user_list — one or more comma-separated account names. Multiple instances? Additive, always. See mysqlpump Object Selection for the deets. 💖
  • --extended-insert=N 🎀
    • Command-Line Format: --extended-insert=N
    • Write those INSERT statements using the multiple-row syntax — the one that stacks several VALUES lists together. Result? A smaller dump file and faster inserts when you reload it. It's giving efficiency, fr fr.
    • The value N tells it how many rows go into each INSERT statement. Default is 250. Set it to 1 and you get one INSERT statement per table row. Periodt. 💅
  • --get-server-public-key 🔑
    • Command-Line Format: --get-server-public-key
    • Type: Boolean
    • Ask the server for the public key needed for RSA key pair-based password exchange. This one's for clients that authenticate with the caching_sha2_password authentication plugin — and for that plugin, the server does not send the public key unless you request it. 🙈
    • It gets ignored for accounts that don't auth with that plugin. Also ignored if RSA-based password exchange isn't being used at all — like when the client connects to the server over a secure connection. Not me pointing out it's just sitting there doing nothing. 💀
    • If --server-public-key-path=file_name is given and points at a valid public key file, it takes precedence over --get-server-public-key.
    • Curious about the plugin? See Section 8.4.1.2, "Caching SHA-2 Pluggable Authentication". 👑
  • --hex-blob 🌈
    • Command-Line Format: --hex-blob
    • Dump binary columns in hexadecimal notation — like, 'abc' becomes 0x616263, iconic. The data types that get this glow-up: BINARY, VARBINARY, BLOB types, BIT, all spatial data types, plus other non-binary data types when they're used with the binary character set. Slay. ✨
  • --host=host_name, -h host_name 🦋
    • Command-Line Format: --host
    • Dump data from the MySQL server living on the given host. That's it, that's the vibe. 💜
  • --include-databases=db_list 🌸
    • Command-Line Format: --include-databases=db_list
    • Type: String
    • Now we're including instead of excluding! Dump the databases in db_list — one or more comma-separated database names. The dump brings all objects in the named databases along for the ride. Multiple instances are additive. See mysqlpump Object Selection. 🎀
  • --include-events=event_list ⭐
    • Command-Line Format: --include-events=event_list
    • Type: String
    • Dump the events in event_list — one or more comma-separated event names. Multiple instances are additive. More info at mysqlpump Object Selection. 🫶
  • --include-routines=routine_list 💖
    • Command-Line Format: --include-routines=routine_list
    • Type: String
    • Dump the routines in routine_list — one or more comma-separated routine names, and yes, "routine" means a stored procedure or function. Multiple instances are additive. See mysqlpump Object Selection. ✨
  • --include-tables=table_list 🍓
    • Command-Line Format: --include-tables=table_list
    • Type: String
    • Dump the tables in table_list — one or more comma-separated table names. Multiple instances are additive. See mysqlpump Object Selection. 🔥
  • --include-triggers=trigger_list 🧚
    • Command-Line Format: --include-triggers=trigger_list
    • Type: String
    • Dump the triggers in trigger_list — one or more comma-separated trigger names. Multiple instances are additive. See mysqlpump Object Selection. 💅
  • --include-users=user_list 👑
    • Command-Line Format: --include-users=user_list
    • Type: String
    • Dump the user accounts in user_list — one or more comma-separated user names. Multiple instances are additive. See mysqlpump Object Selection. 💜
  • --insert-ignore 💀
    • Command-Line Format: --insert-ignore
    • Write INSERT IGNORE statements instead of plain INSERT statements. That's the whole thing. Chaos-free. 🌈
  • --log-error-file=file_name 😭
    • Command-Line Format: --log-error-file=file_name
    • Type: File name

Log warnings and errors by appending them to the named file. If this option is not given, mysqlpump writes warnings and errors to the standard error output.

  • --login-path=name — read options from the named login path in the .mylogin.cnf login path file 💫
    • Command-Line Format: --login-path=name
    • Type: String

Okay so a "login path" is literally just an option group — a little bundle of options that says which MySQL server to connect to and which account to authenticate as. It's giving saved-profile energy fr.

Wanna create or edit a login path file? That's the mysql_config_editor utility's whole job. See mysql_config_editor(1).

For extra deets on this and other option-file options, peep Section 6.2.2.3, "Command-Line Options that Affect Option-File Handling".

  • --max-allowed-packet=N — the max size of the buffer for client/server communication 📦
    • Command-Line Format: --max-allowed-packet=N
    • Type: Numeric
    • Default Value: 25165824

So the default is 24MB and the ceiling is 1GB. That's the whole vibe.

  • --net-buffer-length=N — the initial size of the buffer for client/server communication 🧺
    • Command-Line Format: --net-buffer-length=N
    • Type: Numeric
    • Default Value: 1047552

When mysqlpump is creating multiple-row INSERT statements (like it does with the --extended-insert option), it makes rows up to N bytes long.

Heads up bestie 💖 if you use this option to bump the value up, make sure the MySQL server's net_buffer_length system variable is at least that large. No cap.
  • --no-create-db — suppress any CREATE DATABASE statements that might otherwise show up in the output 🚫🗄️
    • Command-Line Format: --no-create-db
  • --no-create-info, -t — do not write the CREATE TABLE statements that create each dumped table 🚫📋
    • Command-Line Format: --no-create-info
  • --no-defaults — do not read any option files 🙅‍♀️
    • Command-Line Format: --no-defaults

If program startup is failing because it's reading unknown options from an option file, --no-defaults is your girl — she stops them from being read at all.

The one exception, the plot twist: the .mylogin.cnf file is read in all cases, if it exists. Why? So passwords can be specified in a safer way than on the command line, even when --no-defaults is in play. To create .mylogin.cnf, use the mysql_config_editor utility. See mysql_config_editor(1).

For extra deets on this and other option-file options, see Section 6.2.2.3, "Command-Line Options that Affect Option-File Handling".

  • --parallel-schemas=[N:]db_list — spin up a queue for processing the databases in db_list 🌀✨
    • Command-Line Format: --parallel-schemas=[N:]schema_list
    • Type: String

So db_list is a list of one or more comma-separated database names. If N is given, the queue uses N threads. If N is not given, then the --default-parallelism option decides how many queue threads you get.

Multiple instances of this option create multiple queues, iconic. mysqlpump also creates a default queue to use for databases not named in any --parallel-schemas option, and for dumping user definitions if command options select them. More info over at mysqlpump Parallel Processing.

  • --password[=password], -p[password] — the password of the MySQL account used for connecting to the server 🔐
    • Command-Line Format: --password[=password]
    • Type: String

The password value is optional. If you don't give it, mysqlpump prompts you for one. If you do give it, there must be no space between --password= or -p and the password that follows. And if no password option is specified at all, the default is to send no password.

Specifying a password on the command line should be considered insecure 😭 To avoid putting the password on the command line, use an option file. See Section 8.1.2.1, "End-User Guidelines for Password Security".

To explicitly say "there is no password and mysqlpump should NOT prompt for one", use the --skip-password option.

  • --password1[=pass_val] — the password for multifactor authentication factor 1 of the MySQL account used for connecting to the server 🔑

The password value is optional. If not given, mysqlpump prompts for one. If given, there must be no space between --password1= and the password following it. If no password option is specified, the default is to send no password.

Specifying a password on the command line should be considered insecure 😭 To avoid giving the password on the command line, use an option file. See Section 8.1.2.1, "End-User Guidelines for Password Security".

To explicitly specify that there is no password and that mysqlpump should not prompt for one, use the --skip-password1 option.

And fr fr: --password1 and --password are synonymous, as are --skip-password1 and --skip-password. Same thing, different name 💅

  • --password2[=pass_val] — the password for multifactor authentication factor 2 of the MySQL account used for connecting to the server 🔑🔑

The semantics of this option are similar to the semantics for --password1; see the description of that option for details.

  • --password3[=pass_val] — the password for multifactor authentication factor 3 of the MySQL account used for connecting to the server 🔑🔑🔑

The semantics of this option are similar to the semantics for --password1; see the description of that option for details.

  • --plugin-dir=dir_name — the directory in which to look for plugins 🧩
    • Command-Line Format: --plugin-dir=dir_name
    • Type: Directory name

Specify this option if the --default-auth option is used to specify an authentication plugin but mysqlpump can't find it. See Section 8.2.17, "Pluggable Authentication".

  • --port=port_num, -P port_num — for TCP/IP connections, the port number to use 🚪
    • Command-Line Format: --port=port_num
    • Type: Numeric
    • Default Value: 3306
  • --print-defaults
    • Command-Line Format: --print-defaults

Print the program name and all options that it gets from option files. 💖

For the extra tea on this one and its option-file besties, go look at Section 6.2.2.3, “Command-Line Options that Affect Option-File Handling”.

  • --protocol={TCP|SOCKET|PIPE|MEMORY} 🦋
    • Command-Line Format: --protocol=type
    • Type: String
    • Default Value: [see text]
    • Valid Values: TCP, SOCKET, PIPE, MEMORY

Okay so this is the transport protocol you use to connect to the server. It's the move when your other connection params would lowkey land you on a protocol that isn't the one you actually want. Deets on which values are allowed are in Section 6.2.7, “Connection Transport Protocols”. ✨

  • --replace 🎀
    • Command-Line Format: --replace

Writes REPLACE statements instead of INSERT statements. That's it, that's the whole vibe. 💅

  • --result-file=file_name 🌸
    • Command-Line Format: --result-file=file_name
    • Type: File name

Sends the output straight into the file you name. Heads up bestie: the result file gets created and its previous contents get overwritten, even if an error pops off while the dump is being generated. 😭

On Windows you should use this option, because it stops newline \n characters from getting converted into \r\n carriage return/newline sequences. No cap.

  • --routines 🧚
    • Command-Line Format: --routines
    • Type: Boolean
    • Default Value: TRUE

This one pulls the stored routines (procedures and functions) for the dumped databases into the output. It requires the global SELECT privilege, so don't forget that.

The output you get from --routines contains CREATE PROCEDURE and CREATE FUNCTION statements that create the routines.

It's enabled by default, so if you want it gone, hit it with --skip-routines. 🫶

  • --server-public-key-path=file_name 🔥
    • Command-Line Format: --server-public-key-path=file_name
    • Type: File name

This is the path name to a file in PEM format holding a client-side copy of the public key that the server needs for RSA key pair-based password exchange. It applies to clients that authenticate with the sha256_password or caching_sha2_password authentication plugin.

Now for the fine print, because there's a lot of it:

  • It's ignored for accounts that don't authenticate with one of those plugins.
  • It's also ignored if RSA-based password exchange isn't being used — like when the client connects to the server over a secure connection.

If --server-public-key-path=file_name is given and points at a valid public key file, it takes precedence over --get-server-public-key. 👑

For sha256_password, this option only applies if MySQL was built using OpenSSL.

Wanna know more about the sha256_password and caching_sha2_password plugins? Section 8.4.1.3, “SHA-256 Pluggable Authentication”, and Section 8.4.1.2, “Caching SHA-2 Pluggable Authentication”. Go read. 📖

  • --set-charset 💜
    • Command-Line Format: --set-charset

Writes SET NAMES default_character_set into the output.

It's enabled by default. To disable it and suppress the SET NAMES statement entirely, use --skip-set-charset. 🚫

  • --set-gtid-purged=value 🌈
    • Command-Line Format: --set-gtid-purged=value
    • Type: Enumeration
    • Default Value: AUTO
    • Valid Values: OFF, ON, AUTO

So this option gives you control over the global transaction ID (GTID) info written to the dump file, by saying whether or not to add a SET @@GLOBAL.gtid_purged statement to the output. It can also cause a statement to be written to the output that disables binary logging while the dump file is being reloaded. Two-for-one. ✨

Here's what each value actually means, and yes, the default is AUTO:

  • OFF — Add no SET statement to the output.
  • ON — Add a SET statement to the output. An error occurs if GTIDs are not enabled on the server. 💀
  • AUTO — Add a SET statement to the output if GTIDs are enabled on the server.

And here's how --set-gtid-purged messes with binary logging when the dump file gets reloaded:

  • --set-gtid-purged=OFF: SET @@SESSION.SQL_LOG_BIN=0; is not added to the output.
  • --set-gtid-purged=ON: SET @@SESSION.SQL_LOG_BIN=0; is added to the output.
  • --set-gtid-purged=AUTO: SET @@SESSION.SQL_LOG_BIN=0; is added to the output if GTIDs are enabled on the server you're backing up — that is, if AUTO evaluates to ON.
  • --single-transaction ⭐
    • Command-Line Format: --single-transaction

This one sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It's only useful with transactional tables like InnoDB, because then it dumps the consistent state of the database as of the moment START TRANSACTION was issued — all without blocking any applications. Main character energy, honestly. 👑

Okay but keep this in mind when you use it: only InnoDB tables get dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while you're using this option may still change state. 🙈

okay bestie, we're deep in the option buffet now and it is STILL serving — no skipping, we're eating every single one. 🍽️✨

real talk first tho — while a --single-transaction dump is in process, to make sure your dump file is actually valid (correct table contents AND correct binary log coordinates), no other connection should use these statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so if somebody runs one of them on a table you're dumping, the SELECT that mysqlpump runs to grab the table contents can end up with incorrect contents or just straight-up fail. 💀
  • --add-locks and --single-transaction are mutually exclusive.
  • --skip-definer — bestie, this one just yeets the DEFINER and SQL SECURITY clauses right out of the CREATE statements for views and stored programs. 🌈 When the dump file gets reloaded, the objects it creates use the default DEFINER and SQL SECURITY values instead. See Section 27.6, “Stored Object Access Control”.
    • Command-Line Format: --skip-definer
    • Type: Boolean
    • Default Value: FALSE
  • --skip-dump-rows, -d — do not dump table rows. that's it, that's the whole vibe. no rows, no notes. 🙈
    • Command-Line Format: --skip-dump-rows
    • Type: Boolean
    • Default Value: FALSE
  • --skip-generated-invisible-primary-key — available starting with MySQL 8.0.30, this one makes generated invisible primary keys (GIPKs) get excluded from the dump. ✨ Wanna know what a GIPK even is, and what "GIPK mode" means? Go read Section 15.1.20.11, “Generated Invisible Primary Keys”.
    • Command-Line Format: --skip-generated-invisible-primary-key
    • Introduced: 8.0.30
    • Type: Boolean
    • Default Value: FALSE
  • --socket=path, -S path — for connections to localhost, this is the Unix socket file to use, or, if you're on Windows, the name of the named pipe to use. 🦋
    • Command-Line Format: --socket={file_name|pipe_name}
    • Type: String
    • > Windows girlies listen up 🎀 on Windows this option only applies if the server was started with the named_pipe system variable enabled to support named-pipe connections. AND the user making the connection has to be a member of the Windows group named by the named_pipe_full_access_group system variable. two boxes to tick, no cap.
  • --ssl* — every option that starts with --ssl says whether to connect to the server using encryption, and also tells you where to find the SSL keys and certificates. 🔐 See the section called “Command Options for Encrypted Connections”.
  • --ssl-fips-mode={OFF|ON|STRICT} — controls whether to enable FIPS mode on the client side. Here's the gag tho: --ssl-fips-mode is different from the other --ssl-xxx options because it's not used to establish encrypted connections — it just affects which cryptographic operations are permitted. See Section 8.8, “FIPS Support”.
    • Command-Line Format: --ssl-fips-mode={OFF|ON|STRICT}
    • Deprecated: 8.0.34
    • Type: Enumeration
    • Default Value: OFF
    • Valid Values: OFF, ON, STRICT

These --ssl-fips-mode values are permitted, bestie:

  • OFF: Disable FIPS mode. 💤
  • ON: Enable FIPS mode. 🔥
  • STRICT: Enable “strict” FIPS mode. 👑
⚠️ Note ⚠️ If the OpenSSL FIPS Object Module is not available, the ONLY permitted value for --ssl-fips-mode is OFF. In that case, setting --ssl-fips-mode to ON or STRICT makes the client produce a warning at startup and operate in non-FIPS mode. so like… it's giving "we heard you but no". 😭

As of MySQL 8.0.34, this option is deprecated. Expect it to be removed in a future version of MySQL. 👋

  • --tls-ciphersuites=ciphersuite_list — the permissible ciphersuites for encrypted connections that use TLSv1.3. The value is a list of one or more colon-separated ciphersuite names. Which ciphersuites you can actually name here depends on the SSL library that MySQL was compiled with. For the details, see Section 8.3.2, “Encrypted Connection TLS Protocols and Ciphers”.
    • Command-Line Format: --tls-ciphersuites=ciphersuite_list
    • Introduced: 8.0.16
    • Type: String

This option was added in MySQL 8.0.16. ⭐

  • --tls-version=protocol_list — the permissible TLS protocols for encrypted connections. The value is a list of one or more comma-separated protocol names. Again, which protocols you can name depends on the SSL library MySQL was compiled with. Details live in Section 8.3.2, “Encrypted Connection TLS Protocols and Ciphers”. 🧚
    • Command-Line Format: --tls-version=protocol_list
    • Type: String
    • Default Value (≥ 8.0.16): TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 (OpenSSL 1.1.1 or higher), otherwise TLSv1,TLSv1.1,TLSv1.2
    • Default Value (≤ 8.0.15): TLSv1,TLSv1.1,TLSv1.2

and we are SO not done, keep scrolling bestie 💅

  • --triggers — bestie, this one tucks the triggers for every dumped table into the output 💅
    • Command-Line Format: --triggers
    • Type: Boolean
    • Default Value: TRUE

It's already on by default, so if you want it gone, use --skip-triggers to turn it off. periodt.

  • --tz-utc — the timezone-safe queen 👑
    • Command-Line Format: --tz-utc

This one makes TIMESTAMP columns dump and reload correctly between servers that live in different time zones. How? mysqlpump sets its connection time zone to UTC and adds SET TIME_ZONE='+00:00' to the dump file. Without it, TIMESTAMP columns get dumped and reloaded in the time zones local to the source and destination servers, which can literally change the values if those servers are in different time zones 😭. --tz-utc also protects you against changes caused by daylight saving time. no cap.

Enabled by default, so use --skip-tz-utc if you want it off.

  • --user=user_name, -u user_name — who are we logging in as 🫶
    • Command-Line Format: --user=user_name
    • Type: String

The user name of the MySQL account to use for connecting to the server.

heads up: if you're running the Rewriter plugin on MySQL 8.0.31 or later, you should grant this user the SKIP_QUERY_REWRITE privilege. iykyk.
  • --users — dump the whole cast of characters 💖
    • Command-Line Format: --users
    • Type: Boolean
    • Default Value: FALSE

Dumps user accounts as logical definitions, in the form of CREATE USER and GRANT statements.

User definitions live in the grant tables in the mysql system database. By default, mysqlpump does not include the grant tables in dumps of the mysql database. To dump the contents of the grant tables as logical definitions, use --users and suppress all database dumping:

mysqlpump --exclude-databases=% --users
  • --version, -V — the classic 🎀
    • Command-Line Format: --version

Display version information and exit. short and sweet, slay.

  • --watch-progress — the little progress bar that keeps you fed ✨
    • Command-Line Format: --watch-progress
    • Type: Boolean
    • Default Value: TRUE

Periodically displays a progress indicator telling you about the completed and total number of tables, rows, and other objects. obsessed.

Enabled by default; use --skip-watch-progress to disable it.

  • --zstd-compression-level=level — compression dial, turn it up 🔥
    • Command-Line Format: --zstd-compression-level=#
    • Introduced: 8.0.18
    • Type: Integer

The compression level to use for connections to the server that use the zstd compression algorithm. Permitted levels run from 1 to 22, and bigger values mean increasing levels of compression. The default zstd compression level is 3. This setting does absolutely nothing for connections that don't use zstd compression, bestie.

For more info, see Section 6.2.8, "Connection Compression Control".

This option was added in MySQL 8.0.18.

🧠 mysqlpump Object Selection ✨

mysqlpump has a set of inclusion and exclusion options that let you filter several object types and get flexible control over which objects to dump:

  • --include-databases and --exclude-databases apply to databases and all objects within them.
  • --include-tables and --exclude-tables apply to tables. These options also affect triggers associated with tables — unless the trigger-specific options are given.
  • --include-triggers and --exclude-triggers apply to triggers.
  • --include-routines and --exclude-routines apply to stored procedures and functions. If a routine option matches a stored procedure name, it also matches a stored function of the same name.
  • --include-events and --exclude-events apply to Event Scheduler events.
  • --include-users and --exclude-users apply to user accounts.

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:

  • % 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. iconic.

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 like this:

  • 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, you can 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 give you alternative ways to specify which databases to dump:

  • The --all-databases option dumps all databases (with certain exceptions noted in mysqlpump Restrictions). It is equivalent to specifying no object options at all — the default mysqlpump action is to dump everything.

--include-databases=% is similar to --all-databases, but selects all databases for dumping, even those that are exceptions for --all-databases. 👑

  • The --databases option causes mysqlpump to treat all name arguments as names of databases to dump. It is equivalent to an --include-databases option that names the same databases.

⚡ mysqlpump Parallel Processing 🌈

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

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 list of comma-separated 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).

okay bestie, so here's the tea on how the queue situation works 🫖✨

mysqlpump uses the default queue for processing any databases you didn't name explicitly with a --parallel-schemas option, and it also uses that default queue for dumping user definitions if command options select them. iykyk 💅

ngl, in general, when you've got multiple queues, mysqlpump uses parallelism between the sets of databases processed by the queues, so it can dump multiple databases at the same time. fr fr. and for a queue that uses multiple threads, mysqlpump uses parallelism within databases, to dump multiple objects from a given database simultaneously. ✨

but like, exceptions can occur — for example, mysqlpump may block queues while it obtains from the server lists of objects in databases. lowkey unavoidable, no cap.

⚠️ 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. so like… it's fine, don't panic 💖

the granularity for parallelism is a single database object. so for example, a single table cannot be dumped in parallel using multiple threads. it's giving "one object at a time, bestie" 😭

🌸 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. slay ✨

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. periodt 💅

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. iconic honestly 🎀

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

🚧 mysqlpump Restrictions 🚧

okay so here's where we keep it real about what mysqlpump will not do 🙈

  • 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. that's just how it is bestie 💀
  • mysqlpump does not dump InnoDB CREATE TABLESPACE statements. nope, not happening 🌸
  • 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 ...

Copyright © 1997, 2026, Oracle and/or its affiliates.

This documentation is free software; you can redistribute it and/or modify it only under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.

This documentation is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with the program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see http://www.gnu.org/licenses/.

🔖 NOTES 🔖

  1. MySQL Shell dump utilities — https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html
  2. MySQL Shell load dump utilities — https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html
  3. here — https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html

👀 SEE ALSO 👀

for more information, please refer to the MySQL Reference Manual, which may already be installed locally and which is also available online at http://dev.mysql.com/doc/. go read it bestie, it's giving ✨knowledge✨

💜 AUTHOR 💜

Oracle Corporation (http://dev.mysql.com/).

and that's a wrap on this whole page, bestie 🎀 MySQL 8.0, dated 03/23/2026, and yes you read the whole MYSQLPUMP(1) manpage — that's main character energy fr fr, so proud of you 💖✨🦋

📦 where this page comes from