1MYSQLPUMP(1) MySQL Database System MYSQLPUMP(1)
2
6 mysqlpump - a database backup program
7
9 mysqlpump [options] [db_name [tbl_name ...]]
10
12 · mysqlpump Invocation Syntax
13 · mysqlpump Option Summary
15 · mysqlpump Option Descriptions
17 · mysqlpump Object Selection
19 · mysqlpump Parallel Processing
21 · mysqlpump Restrictions
23 The mysqlpump client utility performs logical backups, producing a set
25 of SQL statements that can be executed to reproduce the original
26 database object definitions and table data. It dumps one or more MySQL
27 databases for backup or transfer to another SQL server.
28 mysqlpump features include:
30 · Parallel processing of databases, and of objects within databases,
32 to speed up the dump process
33 · Better control over which databases and database objects (tables,
35 stored programs, user accounts) to dump
36 · Dumping of user accounts as account-management statements (CREATE
38 USER, GRANT) rather than as inserts into the mysql system database
39 · Capability of creating compressed output
41 · Progress indicator (the values are estimates)
43 · For dump file reloading, faster secondary index creation for InnoDB
45 tables by adding indexes after rows are inserted
46 Note
48 mysqlpump uses MySQL features introduced in MySQL 5.7, and thus
49 assumes use with MySQL 5.7 or higher.
50 mysqlpump requires at least the SELECT privilege for dumped tables,
52 SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK
53 TABLES if the --single-transaction option is not used. The SELECT
54 privilege on the mysql system database is required to dump user
55 definitions. Certain options might require other privileges as noted in
56 the option descriptions.
57 To reload a dump file, you must have the privileges required to execute
59 the statements that it contains, such as the appropriate CREATE
60 privileges for objects created by those statements.
61 Note
63 A dump made using PowerShell on Windows with output redirection
64 creates a file that has UTF-16 encoding:
65 shell> mysqlpump [options] > dump.sql
67 However, UTF-16 is not permitted as a connection character set (see
69 Section 10.4, “Connection Character Sets and Collations”), so the
70 dump file will not load correctly. To work around this issue, use
71 the --result-file option, which creates the output in ASCII format:
72 shell> mysqlpump [options] --result-file=dump.sql
74 mysqlpump Invocation Syntax
75 By default, mysqlpump dumps all databases (with certain exceptions
77 noted in mysqlpump Restrictions). To specify this behavior explicitly,
78 use the --all-databases option:
79 shell> mysqlpump --all-databases
81 To dump a single database, or certain tables within that database, name
83 the database on the command line, optionally followed by table names:
84 shell> mysqlpump db_name
86 shell> mysqlpump db_name tbl_name1 tbl_name2 ...
87 To treat all name arguments as database names, use the --databases
89 option:
90 shell> mysqlpump --databases db_name1 db_name2 ...
92 By default, mysqlpump does not dump user account definitions, even if
94 you dump the mysql system database that contains the grant tables. To
95 dump grant table contents as logical definitions in the form of CREATE
96 USER and GRANT statements, use the --users option and suppress all
97 database dumping:
98 shell> mysqlpump --exclude-databases=% --users
100 In the preceding command, % is a wildcard that matches all database
102 names for the --exclude-databases option.
103 mysqlpump supports several options for including or excluding
105 databases, tables, stored programs, and user definitions. See mysqlpump
106 Object Selection.
107 To reload a dump file, execute the statements that it contains. For
109 example, use the mysql client:
110 shell> mysqlpump [options] > dump.sql
112 shell> mysql < dump.sql
113 The following discussion provides additional mysqlpump usage examples.
115 To see a list of the options mysqlpump supports, issue the command
117 mysqlpump --help. mysqlpump Option Summary
118 mysqlpump supports the following options, which can be specified on the
120 command line or in the [mysqlpump] and [client] groups of an option
121 file. (Prior to MySQL 8.0.20, mysqlpump read the [mysql_dump] group
122 rather than [mysqlpump]. As of 8.0.20, [mysql_dump] is still accepted
123 but is deprecated.) For information about option files used by MySQL
124 programs, see Section 4.2.2.2, “Using Option Files”. mysqlpump Option
125 Descriptions
126 · --help, -?
128 Display a help message and exit.
130 · --add-drop-database
132 Write a DROP DATABASE statement before each CREATE DATABASE
134 statement.
135 · --add-drop-table
137 Write a DROP TABLE statement before each CREATE TABLE statement.
139 · --add-drop-user
141 Write a DROP USER statement before each CREATE USER statement.
143 · --add-locks
145 Surround each table dump with LOCK TABLES and UNLOCK TABLES
147 statements. This results in faster inserts when the dump file is
148 reloaded. See Section 8.2.5.1, “Optimizing INSERT Statements”.
149 This option does not work with parallelism because INSERT
151 statements from different tables can be interleaved and UNLOCK
152 TABLES following the end of the inserts for one table could release
153 locks on tables for which inserts remain.
154 --add-locks and --single-transaction are mutually exclusive.
156 · --all-databases, -A
158 Dump all databases (with certain exceptions noted in mysqlpump
160 Restrictions). This is the default behavior if no other is
161 specified explicitly.
162 --all-databases and --databases are mutually exclusive.
164 Prior to MySQL 8.0, the --routines and --events options for
166 mysqldump and mysqlpump were not required to include stored
167 routines and events when using the --all-databases option: The dump
168 included the mysql system database, and therefore also the
169 mysql.proc and mysql.event tables containing stored routine and
170 event definitions. As of MySQL 8.0, the mysql.event and mysql.proc
171 tables are not used. Definitions for the corresponding objects are
172 stored in data dictionary tables, but those tables are not dumped.
173 To include stored routines and events in a dump made using
174 --all-databases, use the --routines and --events options
175 explicitly.
176 · --bind-address=ip_address
178 On a computer having multiple network interfaces, use this option
180 to select which interface to use for connecting to the MySQL
181 server.
182 · --character-sets-dir=path
184 The directory where character sets are installed. See
186 Section 10.15, “Character Set Configuration”.
187 · --column-statistics Add ANALYZE TABLE statements to the output to
189 generate histogram statistics for dumped tables when the dump file
190 is reloaded. This option is disabled by default because histogram
191 generation for large tables can take a long time.
192 · --complete-insert
194 Write complete INSERT statements that include column names.
196 · --compress, -C
198 Compress all information sent between the client and the server if
200 possible. See Section 4.2.6, “Connection Compression Control”.
201 As of MySQL 8.0.18, this option is deprecated. It will be removed
203 in a future MySQL version. See the section called “Legacy
204 Connection Compression Configuration”.
205 · --compress-output=algorithm
207 By default, mysqlpump does not compress output. This option
209 specifies output compression using the specified algorithm.
210 Permitted algorithms are LZ4 and ZLIB.
211 To uncompress compressed output, you must have an appropriate
213 utility. If the system commands lz4 and openssl zlib are not
214 available, MySQL distributions include lz4_decompress and
215 zlib_decompress utilities that can be used to decompress mysqlpump
216 output that was compressed using the --compress-output=LZ4 and
217 --compress-output=ZLIB options. For more information, see
218 lz4_decompress(1), and zlib_decompress(1).
219 · --compression-algorithms=value The permitted compression algorithms
221 for connections to the server. The available algorithms are the
222 same as for the protocol_compression_algorithms system variable.
223 The default value is uncompressed.
224 For more information, see Section 4.2.6, “Connection Compression
226 Control”.
227 This option was added in MySQL 8.0.18.
229 · --databases, -B
231 Normally, mysqlpump treats the first name argument on the command
233 line as a database name and any following names as table names.
234 With this option, it treats all name arguments as database names.
235 CREATE DATABASE statements are included in the output before each
236 new database.
237 --all-databases and --databases are mutually exclusive.
239 · --debug[=debug_options], -# [debug_options]
241 Write a debugging log. A typical debug_options string is
243 d:t:o,file_name. The default is d:t:O,/tmp/mysqlpump.trace.
244 This option is available only if MySQL was built using WITH_DEBUG.
246 MySQL release binaries provided by Oracle are not built using this
247 option.
248 · --debug-check
250 Print some debugging information when the program exits.
252 This option is available only if MySQL was built using WITH_DEBUG.
254 MySQL release binaries provided by Oracle are not built using this
255 option.
256 · --debug-info, -T
258 Print debugging information and memory and CPU usage statistics
260 when the program exits.
261 This option is available only if MySQL was built using WITH_DEBUG.
263 MySQL release binaries provided by Oracle are not built using this
264 option.
265 · --default-auth=plugin
267 A hint about which client-side authentication plugin to use. See
269 Section 6.2.17, “Pluggable Authentication”.
270 · --default-character-set=charset_name
272 Use charset_name as the default character set. See Section 10.15,
274 “Character Set Configuration”. If no character set is specified,
275 mysqlpump uses utf8.
276 · --default-parallelism=N
278 The default number of threads for each parallel processing queue.
280 The default is 2.
281 The --parallel-schemas option also affects parallelism and can be
283 used to override the default number of threads. For more
284 information, see mysqlpump Parallel Processing.
285 With --default-parallelism=0 and no --parallel-schemas options,
287 mysqlpump runs as a single-threaded process and creates no queues.
288 With parallelism enabled, it is possible for output from different
290 databases to be interleaved.
291 · --defaults-extra-file=file_name
293 Read this option file after the global option file but (on Unix)
295 before the user option file. If the file does not exist or is
296 otherwise inaccessible, an error occurs. file_name is interpreted
297 relative to the current directory if given as a relative path name
298 rather than a full path name.
299 For additional information about this and other option-file
301 options, see Section 4.2.2.3, “Command-Line Options that Affect
302 Option-File Handling”.
303 · --defaults-file=file_name
305 Use only the given option file. If the file does not exist or is
307 otherwise inaccessible, an error occurs. file_name is interpreted
308 relative to the current directory if given as a relative path name
309 rather than a full path name.
310 Exception: Even with --defaults-file, client programs read
312 .mylogin.cnf.
313 For additional information about this and other option-file
315 options, see Section 4.2.2.3, “Command-Line Options that Affect
316 Option-File Handling”.
317 · --defaults-group-suffix=str
319 Read not only the usual option groups, but also groups with the
321 usual names and a suffix of str. For example, mysqlpump normally
322 reads the [client] and [mysqlpump] groups. If the
323 --defaults-group-suffix=_other option is given, mysqlpump also
324 reads the [client_other] and [mysqlpump_other] groups.
325 For additional information about this and other option-file
327 options, see Section 4.2.2.3, “Command-Line Options that Affect
328 Option-File Handling”.
329 · --defer-table-indexes
331 In the dump output, defer index creation for each table until after
333 its rows have been loaded. This works for all storage engines, but
334 for InnoDB applies only for secondary indexes.
335 This option is enabled by default; use --skip-defer-table-indexes
337 to disable it.
338 · --events
340 Include Event Scheduler events for the dumped databases in the
342 output. Event dumping requires the EVENT privileges for those
343 databases.
344 The output generated by using --events contains CREATE EVENT
346 statements to create the events.
347 This option is enabled by default; use --skip-events to disable it.
349 · --exclude-databases=db_list
351 Do not dump the databases in db_list, which is a list of one or
353 more comma-separated database names. Multiple instances of this
354 option are additive. For more information, see mysqlpump Object
355 Selection.
356 · --exclude-events=event_list
358 Do not dump the databases in event_list, which is a list of one or
360 more comma-separated event names. Multiple instances of this option
361 are additive. For more information, see mysqlpump Object Selection.
362 · --exclude-routines=routine_list
364 Do not dump the events in routine_list, which is a list of one or
366 more comma-separated routine (stored procedure or function) names.
367 Multiple instances of this option are additive. For more
368 information, see mysqlpump Object Selection.
369 · --exclude-tables=table_list
371 Do not dump the tables in table_list, which is a list of one or
373 more comma-separated table names. Multiple instances of this option
374 are additive. For more information, see mysqlpump Object Selection.
375 · --exclude-triggers=trigger_list
377 Do not dump the triggers in trigger_list, which is a list of one or
379 more comma-separated trigger names. Multiple instances of this
380 option are additive. For more information, see mysqlpump Object
381 Selection.
382 · --exclude-users=user_list
384 Do not dump the user accounts in user_list, which is a list of one
386 or more comma-separated account names. Multiple instances of this
387 option are additive. For more information, see mysqlpump Object
388 Selection.
389 · --extended-insert=N
391 Write INSERT statements using multiple-row syntax that includes
393 several VALUES lists. This results in a smaller dump file and
394 speeds up inserts when the file is reloaded.
395 The option value indicates the number of rows to include in each
397 INSERT statement. The default is 250. A value of 1 produces one
398 INSERT statement per table row.
399 · --get-server-public-key
401 Request from the server the public key required for RSA key
403 pair-based password exchange. This option applies to clients that
404 authenticate with the caching_sha2_password authentication plugin.
405 For that plugin, the server does not send the public key unless
406 requested. This option is ignored for accounts that do not
407 authenticate with that plugin. It is also ignored if RSA-based
408 password exchange is not used, as is the case when the client
409 connects to the server using a secure connection.
410 If --server-public-key-path=file_name is given and specifies a
412 valid public key file, it takes precedence over
413 --get-server-public-key.
414 For information about the caching_sha2_password plugin, see
416 Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
417 · --hex-blob
419 Dump binary columns using hexadecimal notation (for example, 'abc'
421 becomes 0x616263). The affected data types are BINARY, VARBINARY,
422 BLOB types, BIT, all spatial data types, and other non-binary data
423 types when used with the binary character set.
424 · --host=host_name, -h host_name
426 Dump data from the MySQL server on the given host.
428 · --include-databases=db_list
430 Dump the databases in db_list, which is a list of one or more
432 comma-separated database names. The dump includes all objects in
433 the named databases. Multiple instances of this option are
434 additive. For more information, see mysqlpump Object Selection.
435 · --include-events=event_list
437 Dump the events in event_list, which is a list of one or more
439 comma-separated event names. Multiple instances of this option are
440 additive. For more information, see mysqlpump Object Selection.
441 · --include-routines=routine_list
443 Dump the routines in routine_list, which is a list of one or more
445 comma-separated routine (stored procedure or function) names.
446 Multiple instances of this option are additive. For more
447 information, see mysqlpump Object Selection.
448 · --include-tables=table_list
450 Dump the tables in table_list, which is a list of one or more
452 comma-separated table names. Multiple instances of this option are
453 additive. For more information, see mysqlpump Object Selection.
454 · --include-triggers=trigger_list
456 Dump the triggers in trigger_list, which is a list of one or more
458 comma-separated trigger names. Multiple instances of this option
459 are additive. For more information, see mysqlpump Object Selection.
460 · --include-users=user_list
462 Dump the user accounts in user_list, which is a list of one or more
464 comma-separated user names. Multiple instances of this option are
465 additive. For more information, see mysqlpump Object Selection.
466 · --insert-ignore
468 Write INSERT IGNORE statements rather than INSERT statements.
470 · --log-error-file=file_name
472 Log warnings and errors by appending them to the named file. If
474 this option is not given, mysqlpump writes warnings and errors to
475 the standard error output.
476 · --login-path=name
478 Read options from the named login path in the .mylogin.cnf login
480 path file. A “login path” is an option group containing options
481 that specify which MySQL server to connect to and which account to
482 authenticate as. To create or modify a login path file, use the
483 mysql_config_editor utility. See mysql_config_editor(1).
484 For additional information about this and other option-file
486 options, see Section 4.2.2.3, “Command-Line Options that Affect
487 Option-File Handling”.
488 · --max-allowed-packet=N
490 The maximum size of the buffer for client/server communication. The
492 default is 24MB, the maximum is 1GB.
493 · --net-buffer-length=N
495 The initial size of the buffer for client/server communication.
497 When creating multiple-row INSERT statements (as with the
498 --extended-insert option), mysqlpump creates rows up to N bytes
499 long. If you use this option to increase the value, ensure that the
500 MySQL server net_buffer_length system variable has a value at least
501 this large.
502 · --no-create-db
504 Suppress any CREATE DATABASE statements that might otherwise be
506 included in the output.
507 · --no-create-info, -t
509 Do not write CREATE TABLE statements that create each dumped table.
511 · --no-defaults
513 Do not read any option files. If program startup fails due to
515 reading unknown options from an option file, --no-defaults can be
516 used to prevent them from being read.
517 The exception is that the .mylogin.cnf file, if it exists, is read
519 in all cases. This permits passwords to be specified in a safer way
520 than on the command line even when --no-defaults is used.
521 (.mylogin.cnf is created by the mysql_config_editor utility. See
522 mysql_config_editor(1).)
523 For additional information about this and other option-file
525 options, see Section 4.2.2.3, “Command-Line Options that Affect
526 Option-File Handling”.
527 · --parallel-schemas=[N:]db_list
529 Create a queue for processing the databases in db_list, which is a
531 list of one or more comma-separated database names. If N is given,
532 the queue uses N threads. If N is not given, the
533 --default-parallelism option determines the number of queue
534 threads.
535 Multiple instances of this option create multiple queues.
537 mysqlpump also creates a default queue to use for databases not
538 named in any --parallel-schemas option, and for dumping user
539 definitions if command options select them. For more information,
540 see mysqlpump Parallel Processing.
541 · --password[=password], -p[password]
543 The password of the MySQL account used for connecting to the
545 server. The password value is optional. If not given, mysqlpump
546 prompts for one. If given, there must be no space between
547 --password= or -p and the password following it. If no password
548 option is specified, the default is to send no password.
549 Specifying a password on the command line should be considered
551 insecure. To avoid giving the password on the command line, use an
552 option file. See Section 6.1.2.1, “End-User Guidelines for Password
553 Security”.
554 To explicitly specify that there is no password and that mysqlpump
556 should not prompt for one, use the --skip-password option.
557 · --plugin-dir=dir_name
559 The directory in which to look for plugins. Specify this option if
561 the --default-auth option is used to specify an authentication
562 plugin but mysqlpump does not find it. See Section 6.2.17,
563 “Pluggable Authentication”.
564 · --port=port_num, -P port_num
566 For TCP/IP connections, the port number to use.
568 · --print-defaults
570 Print the program name and all options that it gets from option
572 files.
573 For additional information about this and other option-file
575 options, see Section 4.2.2.3, “Command-Line Options that Affect
576 Option-File Handling”.
577 · --protocol={TCP|SOCKET|PIPE|MEMORY}
579 The connection protocol to use for connecting to the server. It is
581 useful when the other connection parameters normally result in use
582 of a protocol other than the one you want. For details on the
583 permissible values, see Section 4.2.4, “Connecting to the MySQL
584 Server Using Command Options”.
585 · --replace
587 Write REPLACE statements rather than INSERT statements.
589 · --result-file=file_name
591 Direct output to the named file. The result file is created and its
593 previous contents overwritten, even if an error occurs while
594 generating the dump.
595 This option should be used on Windows to prevent newline \n
597 characters from being converted to \r\n carriage return/newline
598 sequences.
599 · --routines
601 Include stored routines (procedures and functions) for the dumped
603 databases in the output. This option requires the global SELECT
604 privilege.
605 The output generated by using --routines contains CREATE PROCEDURE
607 and CREATE FUNCTION statements to create the routines.
608 This option is enabled by default; use --skip-routines to disable
610 it.
611 · --server-public-key-path=file_name
613 The path name to a file containing a client-side copy of the public
615 key required by the server for RSA key pair-based password
616 exchange. The file must be in PEM format. This option applies to
617 clients that authenticate with the sha256_password or
618 caching_sha2_password authentication plugin. This option is ignored
619 for accounts that do not authenticate with one of those plugins. It
620 is also ignored if RSA-based password exchange is not used, as is
621 the case when the client connects to the server using a secure
622 connection.
623 If --server-public-key-path=file_name is given and specifies a
625 valid public key file, it takes precedence over
626 --get-server-public-key.
627 For sha256_password, this option applies only if MySQL was built
629 using OpenSSL.
630 For information about the sha256_password and caching_sha2_password
632 plugins, see Section 6.4.1.3, “SHA-256 Pluggable Authentication”,
633 and Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
634 · --set-charset
636 Write SET NAMES default_character_set to the output.
638 This option is enabled by default. To disable it and suppress the
640 SET NAMES statement, use --skip-set-charset.
641 · --set-gtid-purged=value
643 This option enables control over global transaction ID (GTID)
645 information written to the dump file, by indicating whether to add
646 a SET @@GLOBAL.gtid_purged statement to the output. This option may
647 also cause a statement to be written to the output that disables
648 binary logging while the dump file is being reloaded.
649 The following table shows the permitted option values. The default
651 value is AUTO.
652 ┌──────┬────────────────────────────┐
654 │Value │ Meaning │
655 ├──────┼────────────────────────────┤
656 │OFF │ Add no SET statement to │
657 │ │ the output. │
658 ├──────┼────────────────────────────┤
659 │ON │ Add a SET statement to the │
660 │ │ output. An error occurs if │
661 │ │ GTIDs │
662 │ │ are not enabled on the │
663 │ │ server. │
664 ├──────┼────────────────────────────┤
665 │AUTO │ Add a SET statement to the │
666 │ │ output if GTIDs are │
667 │ │ enabled │
668 │ │ on the server. │
669 └──────┴────────────────────────────┘
670 The --set-gtid-purged option has the following effect on binary
671 logging when the dump file is reloaded:
672 · --set-gtid-purged=OFF: SET @@SESSION.SQL_LOG_BIN=0; is not
674 added to the output.
675 · --set-gtid-purged=ON: SET @@SESSION.SQL_LOG_BIN=0; is added to
677 the output.
678 · --set-gtid-purged=AUTO: SET @@SESSION.SQL_LOG_BIN=0; is added
680 to the output if GTIDs are enabled on the server you are
681 backing up (that is, if AUTO evaluates to ON).
682 · --single-transaction
684 This option sets the transaction isolation mode to REPEATABLE READ
686 and sends a START TRANSACTION SQL statement to the server before
687 dumping data. It is useful only with transactional tables such as
688 InnoDB, because then it dumps the consistent state of the database
689 at the time when START TRANSACTION was issued without blocking any
690 applications.
691 When using this option, you should keep in mind that only InnoDB
693 tables are dumped in a consistent state. For example, any MyISAM or
694 MEMORY tables dumped while using this option may still change
695 state.
696 While a --single-transaction dump is in process, to ensure a valid
698 dump file (correct table contents and binary log coordinates), no
699 other connection should use the following statements: ALTER TABLE,
700 CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A
701 consistent read is not isolated from those statements, so use of
702 them on a table to be dumped can cause the SELECT that is performed
703 by mysqlpump to retrieve the table contents to obtain incorrect
704 contents or fail.
705 --add-locks and --single-transaction are mutually exclusive.
707 · --skip-definer
709 Omit DEFINER and SQL SECURITY clauses from the CREATE statements
711 for views and stored programs. The dump file, when reloaded,
712 creates objects that use the default DEFINER and SQL SECURITY
713 values. See Section 24.6, “Stored Object Access Control”.
714 · --skip-dump-rows, -d
716 Do not dump table rows.
718 · --socket=path, -S path
720 For connections to localhost, the Unix socket file to use, or, on
722 Windows, the name of the named pipe to use.
723 On Windows, this option applies only if the server was started with
725 the named_pipe system variable enabled to support named-pipe
726 connections. In addition, the user making the connection must be a
727 member of the Windows group specified by the
728 named_pipe_full_access_group system variable.
729 · --ssl*
731 Options that begin with --ssl specify whether to connect to the
733 server using SSL and indicate where to find SSL keys and
734 certificates. See the section called “Command Options for Encrypted
735 Connections”.
736 · --ssl-fips-mode={OFF|ON|STRICT} Controls whether to enable FIPS
738 mode on the client side. The --ssl-fips-mode option differs from
739 other --ssl-xxx options in that it is not used to establish
740 encrypted connections, but rather to affect which cryptographic
741 operations are permitted. See Section 6.5, “FIPS Support”.
742 These --ssl-fips-mode values are permitted:
744 · OFF: Disable FIPS mode.
746 · ON: Enable FIPS mode.
748 · STRICT: Enable “strict” FIPS mode.
750 Note
753 If the OpenSSL FIPS Object Module is not available, the only
754 permitted value for --ssl-fips-mode is OFF. In this case,
755 setting --ssl-fips-mode to ON or STRICT causes the client to
756 produce a warning at startup and to operate in non-FIPS mode.
757 · --tls-ciphersuites=ciphersuite_list
759 The permissible ciphersuites for encrypted connections that use
761 TLSv1.3. The value is a list of one or more colon-separated
762 ciphersuite names. The ciphersuites that can be named for this
763 option depend on the SSL library used to compile MySQL. For
764 details, see Section 6.3.2, “Encrypted Connection TLS Protocols and
765 Ciphers”.
766 This option was added in MySQL 8.0.16.
768 · --tls-version=protocol_list
770 The permissible TLS protocols for encrypted connections. The value
772 is a list of one or more comma-separated protocol names. The
773 protocols that can be named for this option depend on the SSL
774 library used to compile MySQL. For details, see Section 6.3.2,
775 “Encrypted Connection TLS Protocols and Ciphers”.
776 · --triggers
778 Include triggers for each dumped table in the output.
780 This option is enabled by default; use --skip-triggers to disable
782 it.
783 · --tz-utc
785 This option enables TIMESTAMP columns to be dumped and reloaded
787 between servers in different time zones. mysqlpump sets its
788 connection time zone to UTC and adds SET TIME_ZONE='+00:00' to the
789 dump file. Without this option, TIMESTAMP columns are dumped and
790 reloaded in the time zones local to the source and destination
791 servers, which can cause the values to change if the servers are in
792 different time zones. --tz-utc also protects against changes due
793 to daylight saving time.
794 This option is enabled by default; use --skip-tz-utc to disable it.
796 · --user=user_name, -u user_name
798 The user name of the MySQL account to use for connecting to the
800 server.
801 · --users
803 Dump user accounts as logical definitions in the form of CREATE
805 USER and GRANT statements.
806 User definitions are stored in the grant tables in the mysql system
808 database. By default, mysqlpump does not include the grant tables
809 in mysql database dumps. To dump the contents of the grant tables
810 as logical definitions, use the --users option and suppress all
811 database dumping:
812 shell> mysqlpump --exclude-databases=% --users
814 · --version, -V
816 Display version information and exit.
818 · --watch-progress
820 Periodically display a progress indicator that provides information
822 about the completed and total number of tables, rows, and other
823 objects.
824 This option is enabled by default; use --skip-watch-progress to
826 disable it.
827 · --zstd-compression-level=level The compression level to use for
829 connections to the server that use the zstd compression algorithm.
830 The permitted levels are from 1 to 22, with larger values
831 indicating increasing levels of compression. The default zstd
832 compression level is 3. The compression level setting has no effect
833 on connections that do not use zstd compression.
834 For more information, see Section 4.2.6, “Connection Compression
836 Control”.
837 This option was added in MySQL 8.0.18.
839 mysqlpump Object Selection
840 mysqlpump has a set of inclusion and exclusion options that enable
842 filtering of several object types and provide flexible control over
843 which objects to dump:
844 · --include-databases and --exclude-databases apply to databases and
846 all objects within them.
847 · --include-tables and --exclude-tables apply to tables. These
849 options also affect triggers associated with tables unless the
850 trigger-specific options are given.
851 · --include-triggers and --exclude-triggers apply to triggers.
853 · --include-routines and --exclude-routines apply to stored
855 procedures and functions. If a routine option matches a stored
856 procedure name, it also matches a stored function of the same name.
857 · --include-events and --exclude-events apply to Event Scheduler
859 events.
860 · --include-users and --exclude-users apply to user accounts.
862 Any inclusion or exclusion option may be given multiple times. The
864 effect is additive. Order of these options does not matter.
865 The value of each inclusion and exclusion option is a list of
867 comma-separated names of the appropriate object type. For example:
868 --exclude-databases=test,world
870 --include-tables=customer,invoice
871 Wildcard characters are permitted in the object names:
873 · % matches any sequence of zero or more characters.
875 · _ matches any single character.
877 For example, --include-tables=t%,__tmp matches all table names that
879 begin with t and all five-character table names that end with tmp.
880 For users, a name specified without a host part is interpreted with an
882 implied host of %. For example, u1 and u1@% are equivalent. This is the
883 same equivalence that applies in MySQL generally (see Section 6.2.4,
884 “Specifying Account Names”).
885 Inclusion and exclusion options interact as follows:
887 · By default, with no inclusion or exclusion options, mysqlpump dumps
889 all databases (with certain exceptions noted in mysqlpump
890 Restrictions).
891 · If inclusion options are given in the absence of exclusion options,
893 only the objects named as included are dumped.
894 · If exclusion options are given in the absence of inclusion options,
896 all objects are dumped except those named as excluded.
897 · If inclusion and exclusion options are given, all objects named as
899 excluded and not named as included are not dumped. All other
900 objects are dumped.
901 If multiple databases are being dumped, it is possible to name tables,
903 triggers, and routines in a specific database by qualifying the object
904 names with the database name. The following command dumps databases db1
905 and db2, but excludes tables db1.t1 and db2.t2:
906 shell> mysqlpump --include-databases=db1,db2 --exclude-tables=db1.t1,db2.t2
908 The following options provide alternative ways to specify which
910 databases to dump:
911 · The --all-databases option dumps all databases (with certain
913 exceptions noted in mysqlpump Restrictions). It is equivalent to
914 specifying no object options at all (the default mysqlpump action
915 is to dump everything).
916 --include-databases=% is similar to --all-databases, but selects
918 all databases for dumping, even those that are exceptions for
919 --all-databases.
920 · The --databases option causes mysqlpump to treat all name arguments
922 as names of databases to dump. It is equivalent to an
923 --include-databases option that names the same databases.
924 mysqlpump Parallel Processing
925 mysqlpump can use parallelism to achieve concurrent processing. You can
927 select concurrency between databases (to dump multiple databases
928 simultaneously) and within databases (to dump multiple objects from a
929 given database simultaneously).
930 By default, mysqlpump sets up one queue with two threads. You can
932 create additional queues and control the number of threads assigned to
933 each one, including the default queue:
934 · --default-parallelism=N specifies the default number of threads
936 used for each queue. In the absence of this option, N is 2.
937 The default queue always uses the default number of threads.
939 Additional queues use the default number of threads unless you
940 specify otherwise.
941 · --parallel-schemas=[N:]db_list sets up a processing queue for
943 dumping the databases named in db_list and optionally specifies how
944 many threads the queue uses. db_list is a list of comma-separated
945 database names. If the option argument begins with N:, the queue
946 uses N threads. Otherwise, the --default-parallelism option
947 determines the number of queue threads.
948 Multiple instances of the --parallel-schemas option create multiple
950 queues.
951 Names in the database list are permitted to contain the same % and
953 _ wildcard characters supported for filtering options (see
954 mysqlpump Object Selection).
955 mysqlpump uses the default queue for processing any databases not named
957 explicitly with a --parallel-schemas option, and for dumping user
958 definitions if command options select them.
959 In general, with multiple queues, mysqlpump uses parallelism between
961 the sets of databases processed by the queues, to dump multiple
962 databases simultaneously. For a queue that uses multiple threads,
963 mysqlpump uses parallelism within databases, to dump multiple objects
964 from a given database simultaneously. Exceptions can occur; for
965 example, mysqlpump may block queues while it obtains from the server
966 lists of objects in databases.
967 With parallelism enabled, it is possible for output from different
969 databases to be interleaved. For example, INSERT statements from
970 multiple tables dumped in parallel can be interleaved; the statements
971 are not written in any particular order. This does not affect reloading
972 because output statements qualify object names with database names or
973 are preceded by USE statements as required.
974 The granularity for parallelism is a single database object. For
976 example, a single table cannot be dumped in parallel using multiple
977 threads.
978 Examples:
980 shell> mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
982 mysqlpump sets up a queue to process db1 and db2, another queue to
984 process db3, and a default queue to process all other databases. All
985 queues use two threads.
986 shell> mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
988 --default-parallelism=4
989 This is the same as the previous example except that all queues use
991 four threads.
992 shell> mysqlpump --parallel-schemas=5:db1,db2 --parallel-schemas=3:db3
994 The queue for db1 and db2 uses five threads, the queue for db3 uses
996 three threads, and the default queue uses the default of two threads.
997 As a special case, with --default-parallelism=0 and no
999 --parallel-schemas options, mysqlpump runs as a single-threaded process
1000 and creates no queues. mysqlpump Restrictions
1001 mysqlpump does not dump the performance_schema, ndbinfo, or sys schema
1003 by default. To dump any of these, name them explicitly on the command
1004 line. You can also name them with the --databases or
1005 --include-databases option.
1006 mysqlpump does not dump the INFORMATION_SCHEMA schema.
1008 mysqlpump does not dump InnoDB CREATE TABLESPACE statements.
1010 mysqlpump dumps user accounts in logical form using CREATE USER and
1012 GRANT statements (for example, when you use the --include-users or
1013 --users option). For this reason, dumps of the mysql system database do
1014 not by default include the grant tables that contain user definitions:
1015 user, db, tables_priv, columns_priv, procs_priv, or proxies_priv. To
1016 dump any of the grant tables, name the mysql database followed by the
1017 table names:
1018 shell> mysqlpump mysql user db ...
1020
1022 Copyright © 1997, 2020, Oracle and/or its affiliates. All rights
1023 reserved.
1024 This documentation is free software; you can redistribute it and/or
1026 modify it only under the terms of the GNU General Public License as
1027 published by the Free Software Foundation; version 2 of the License.
1028 This documentation is distributed in the hope that it will be useful,
1030 but WITHOUT ANY WARRANTY; without even the implied warranty of
1031 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
1032 General Public License for more details.
1033 You should have received a copy of the GNU General Public License along
1035 with the program; if not, write to the Free Software Foundation, Inc.,
1036 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
1037 http://www.gnu.org/licenses/.
1038
1041 For more information, please refer to the MySQL Reference Manual, which
1042 may already be installed locally and which is also available online at
1043 http://dev.mysql.com/doc/.
1044
1046 Oracle Corporation (http://dev.mysql.com/).
1047MySQL 8.0 03/06/2020 MYSQLPUMP(1)