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.PP By default, mysqlpump dumps all
75 databases (with certain exceptions noted in mysqlpump Restrictions). To
76 specify this behavior explicitly, use the --all-databases option:
77 shell> mysqlpump --all-databases
79 To dump a single database, or certain tables within that database, name
81 the database on the command line, optionally followed by table names:
82 shell> mysqlpump db_name
84 shell> mysqlpump db_name tbl_name1 tbl_name2 ...
85 To treat all name arguments as database names, use the --databases
87 option:
88 shell> mysqlpump --databases db_name1 db_name2 ...
90 By default, mysqlpump does not dump user account definitions, even if
92 you dump the mysql system database that contains the grant tables. To
93 dump grant table contents as logical definitions in the form of CREATE
94 USER and GRANT statements, use the --users option and suppress all
95 database dumping:
96 shell> mysqlpump --exclude-databases=% --users
98 In the preceding command, % is a wildcard that matches all database
100 names for the --exclude-databases option.
101 mysqlpump supports several options for including or excluding
103 databases, tables, stored programs, and user definitions. See mysqlpump
104 Object Selection.
105 To reload a dump file, execute the statements that it contains. For
107 example, use the mysql client:
108 shell> mysqlpump [options] > dump.sql
110 shell> mysql < dump.sql
111 The following discussion provides additional mysqlpump usage examples.
113 To see a list of the options mysqlpump supports, issue the command
115 mysqlpump --help. mysqlpump Option Summary.PP mysqlpump supports the
116 following options, which can be specified on the command line or in the
117 [mysqlpump] and [client] groups of an option file. For information
118 about option files used by MySQL programs, see Section 4.2.2.2, “Using
119 Option Files”. mysqlpump Option Descriptions
120 · --help, -?
122 Display a help message and exit.
124 · --add-drop-database
126 Write a DROP DATABASE statement before each CREATE DATABASE
128 statement.
129 · --add-drop-table
131 Write a DROP TABLE statement before each CREATE TABLE statement.
133 · --add-drop-user
135 Write a DROP USER statement before each CREATE USER statement.
137 · --add-locks
139 Surround each table dump with LOCK TABLES and UNLOCK TABLES
141 statements. This results in faster inserts when the dump file is
142 reloaded. See Section 8.2.5.1, “Optimizing INSERT Statements”.
143 This option does not work with parallelism because INSERT
145 statements from different tables can be interleaved and UNLOCK
146 TABLES following the end of the inserts for one table could release
147 locks on tables for which inserts remain.
148 --add-locks and --single-transaction are mutually exclusive.
150 · --all-databases, -A
152 Dump all databases (with certain exceptions noted in mysqlpump
154 Restrictions). This is the default behavior if no other is
155 specified explicitly.
156 --all-databases and --databases are mutually exclusive.
158 Prior to MySQL 8.0, the --routines and --events options for
160 mysqldump and mysqlpump were not required to include stored
161 routines and events when using the --all-databases option: The dump
162 included the mysql system database, and therefore also the
163 mysql.proc and mysql.event tables containing stored routine and
164 event definitions. As of MySQL 8.0, the mysql.event and mysql.proc
165 tables are not used. Definitions for the corresponding objects are
166 stored in data dictionary tables, but those tables are not dumped.
167 To include stored routines and events in a dump made using
168 --all-databases, use the --routines and --events options
169 explicitly.
170 · --bind-address=ip_address
172 On a computer having multiple network interfaces, use this option
174 to select which interface to use for connecting to the MySQL
175 server.
176 · --character-sets-dir=path
178 The directory where character sets are installed. See
180 Section 10.15, “Character Set Configuration”.
181 · --column-statistics Add ANALYZE TABLE statements to the output to
183 generate histogram statistics for dumped tables when the dump file
184 is reloaded. This option is disabled by default because histogram
185 generation for large tables can take a long time.
186 · --complete-insert
188 Write complete INSERT statements that include column names.
190 · --compress, -C
192 Compress all information sent between the client and the server if
194 possible. See Section 4.2.6, “Connection Compression Control”.
195 As of MySQL 8.0.18, this option is deprecated. It will be removed
197 in a future MySQL version. See the section called “Legacy
198 Connection Compression Configuration”.
199 · --compress-output=algorithm
201 By default, mysqlpump does not compress output. This option
203 specifies output compression using the specified algorithm.
204 Permitted algorithms are LZ4 and ZLIB.
205 To uncompress compressed output, you must have an appropriate
207 utility. If the system commands lz4 and openssl zlib are not
208 available, MySQL distributions include lz4_decompress and
209 zlib_decompress utilities that can be used to decompress mysqlpump
210 output that was compressed using the --compress-output=LZ4 and
211 --compress-output=ZLIB options. For more information, see
212 lz4_decompress(1), and zlib_decompress(1).
213 · --compression-algorithms=value The permitted compression algorithms
215 for connections to the server. The available algorithms are the
216 same as for the protocol_compression_algorithms system variable.
217 The default value is uncompressed.
218 For more information, see Section 4.2.6, “Connection Compression
220 Control”.
221 This option was added in MySQL 8.0.18.
223 · --databases, -B
225 Normally, mysqlpump treats the first name argument on the command
227 line as a database name and any following names as table names.
228 With this option, it treats all name arguments as database names.
229 CREATE DATABASE statements are included in the output before each
230 new database.
231 --all-databases and --databases are mutually exclusive.
233 · --debug[=debug_options], -# [debug_options]
235 Write a debugging log. A typical debug_options string is
237 d:t:o,file_name. The default is d:t:O,/tmp/mysqlpump.trace.
238 · --debug-check
240 Print some debugging information when the program exits.
242 · --debug-info, -T
244 Print debugging information and memory and CPU usage statistics
246 when the program exits.
247 · --default-auth=plugin
249 A hint about which client-side authentication plugin to use. See
251 Section 6.2.17, “Pluggable Authentication”.
252 · --default-character-set=charset_name
254 Use charset_name as the default character set. See Section 10.15,
256 “Character Set Configuration”. If no character set is specified,
257 mysqlpump uses utf8.
258 · --default-parallelism=N
260 The default number of threads for each parallel processing queue.
262 The default is 2.
263 The --parallel-schemas option also affects parallelism and can be
265 used to override the default number of threads. For more
266 information, see mysqlpump Parallel Processing.
267 With --default-parallelism=0 and no --parallel-schemas options,
269 mysqlpump runs as a single-threaded process and creates no queues.
270 With parallelism enabled, it is possible for output from different
272 databases to be interleaved.
273 · --defaults-extra-file=file_name
275 Read this option file after the global option file but (on Unix)
277 before the user option file. If the file does not exist or is
278 otherwise inaccessible, an error occurs. file_name is interpreted
279 relative to the current directory if given as a relative path name
280 rather than a full path name.
281 For additional information about this and other option-file
283 options, see Section 4.2.2.3, “Command-Line Options that Affect
284 Option-File Handling”.
285 · --defaults-file=file_name
287 Use only the given option file. If the file does not exist or is
289 otherwise inaccessible, an error occurs. file_name is interpreted
290 relative to the current directory if given as a relative path name
291 rather than a full path name.
292 Exception: Even with --defaults-file, client programs read
294 .mylogin.cnf.
295 For additional information about this and other option-file
297 options, see Section 4.2.2.3, “Command-Line Options that Affect
298 Option-File Handling”.
299 · --defaults-group-suffix=str
301 Read not only the usual option groups, but also groups with the
303 usual names and a suffix of str. For example, mysqlpump normally
304 reads the [client] and [mysqlpump] groups. If the
305 --defaults-group-suffix=_other option is given, mysqlpump also
306 reads the [client_other] and [mysqlpump_other] groups.
307 For additional information about this and other option-file
309 options, see Section 4.2.2.3, “Command-Line Options that Affect
310 Option-File Handling”.
311 · --defer-table-indexes
313 In the dump output, defer index creation for each table until after
315 its rows have been loaded. This works for all storage engines, but
316 for InnoDB applies only for secondary indexes.
317 This option is enabled by default; use --skip-defer-table-indexes
319 to disable it.
320 · --events
322 Include Event Scheduler events for the dumped databases in the
324 output. Event dumping requires the EVENT privileges for those
325 databases.
326 The output generated by using --events contains CREATE EVENT
328 statements to create the events.
329 This option is enabled by default; use --skip-events to disable it.
331 · --exclude-databases=db_list
333 Do not dump the databases in db_list, which is a list of one or
335 more comma-separated database names. Multiple instances of this
336 option are additive. For more information, see mysqlpump Object
337 Selection.
338 · --exclude-events=event_list
340 Do not dump the databases in event_list, which is a list of one or
342 more comma-separated event names. Multiple instances of this option
343 are additive. For more information, see mysqlpump Object Selection.
344 · --exclude-routines=routine_list
346 Do not dump the events in routine_list, which is a list of one or
348 more comma-separated routine (stored procedure or function) names.
349 Multiple instances of this option are additive. For more
350 information, see mysqlpump Object Selection.
351 · --exclude-tables=table_list
353 Do not dump the tables in table_list, which is a list of one or
355 more comma-separated table names. Multiple instances of this option
356 are additive. For more information, see mysqlpump Object Selection.
357 · --exclude-triggers=trigger_list
359 Do not dump the triggers in trigger_list, which is a list of one or
361 more comma-separated trigger names. Multiple instances of this
362 option are additive. For more information, see mysqlpump Object
363 Selection.
364 · --exclude-users=user_list
366 Do not dump the user accounts in user_list, which is a list of one
368 or more comma-separated account names. Multiple instances of this
369 option are additive. For more information, see mysqlpump Object
370 Selection.
371 · --extended-insert=N
373 Write INSERT statements using multiple-row syntax that includes
375 several VALUES lists. This results in a smaller dump file and
376 speeds up inserts when the file is reloaded.
377 The option value indicates the number of rows to include in each
379 INSERT statement. The default is 250. A value of 1 produces one
380 INSERT statement per table row.
381 · --get-server-public-key
383 Request from the server the public key required for RSA key
385 pair-based password exchange. This option applies to clients that
386 authenticate with the caching_sha2_password authentication plugin.
387 For that plugin, the server does not send the public key unless
388 requested. This option is ignored for accounts that do not
389 authenticate with that plugin. It is also ignored if RSA-based
390 password exchange is not used, as is the case when the client
391 connects to the server using a secure connection.
392 If --server-public-key-path=file_name is given and specifies a
394 valid public key file, it takes precedence over
395 --get-server-public-key.
396 For information about the caching_sha2_password plugin, see
398 Section 6.4.1.3, “Caching SHA-2 Pluggable Authentication”.
399 · --hex-blob
401 Dump binary columns using hexadecimal notation (for example, 'abc'
403 becomes 0x616263). The affected data types are BINARY, VARBINARY,
404 BLOB types, BIT, all spatial data types, and other non-binary data
405 types when used with the binary character set.
406 · --host=host_name, -h host_name
408 Dump data from the MySQL server on the given host.
410 · --include-databases=db_list
412 Dump the databases in db_list, which is a list of one or more
414 comma-separated database names. The dump includes all objects in
415 the named databases. Multiple instances of this option are
416 additive. For more information, see mysqlpump Object Selection.
417 · --include-events=event_list
419 Dump the events in event_list, which is a list of one or more
421 comma-separated event names. Multiple instances of this option are
422 additive. For more information, see mysqlpump Object Selection.
423 · --include-routines=routine_list
425 Dump the routines in routine_list, which is a list of one or more
427 comma-separated routine (stored procedure or function) names.
428 Multiple instances of this option are additive. For more
429 information, see mysqlpump Object Selection.
430 · --include-tables=table_list
432 Dump the tables in table_list, which is a list of one or more
434 comma-separated table names. Multiple instances of this option are
435 additive. For more information, see mysqlpump Object Selection.
436 · --include-triggers=trigger_list
438 Dump the triggers in trigger_list, which is a list of one or more
440 comma-separated trigger names. Multiple instances of this option
441 are additive. For more information, see mysqlpump Object Selection.
442 · --include-users=user_list
444 Dump the user accounts in user_list, which is a list of one or more
446 comma-separated user names. Multiple instances of this option are
447 additive. For more information, see mysqlpump Object Selection.
448 · --insert-ignore
450 Write INSERT IGNORE statements rather than INSERT statements.
452 · --log-error-file=file_name
454 Log warnings and errors by appending them to the named file. If
456 this option is not given, mysqlpump writes warnings and errors to
457 the standard error output.
458 · --login-path=name
460 Read options from the named login path in the .mylogin.cnf login
462 path file. A “login path” is an option group containing options
463 that specify which MySQL server to connect to and which account to
464 authenticate as. To create or modify a login path file, use the
465 mysql_config_editor utility. See mysql_config_editor(1).
466 For additional information about this and other option-file
468 options, see Section 4.2.2.3, “Command-Line Options that Affect
469 Option-File Handling”.
470 · --max-allowed-packet=N
472 The maximum size of the buffer for client/server communication. The
474 default is 24MB, the maximum is 1GB.
475 · --net-buffer-length=N
477 The initial size of the buffer for client/server communication.
479 When creating multiple-row INSERT statements (as with the
480 --extended-insert option), mysqlpump creates rows up to N bytes
481 long. If you use this option to increase the value, ensure that the
482 MySQL server net_buffer_length system variable has a value at least
483 this large.
484 · --no-create-db
486 Suppress any CREATE DATABASE statements that might otherwise be
488 included in the output.
489 · --no-create-info, -t
491 Do not write CREATE TABLE statements that create each dumped table.
493 · --no-defaults
495 Do not read any option files. If program startup fails due to
497 reading unknown options from an option file, --no-defaults can be
498 used to prevent them from being read.
499 The exception is that the .mylogin.cnf file, if it exists, is read
501 in all cases. This permits passwords to be specified in a safer way
502 than on the command line even when --no-defaults is used.
503 (.mylogin.cnf is created by the mysql_config_editor utility. See
504 mysql_config_editor(1).)
505 For additional information about this and other option-file
507 options, see Section 4.2.2.3, “Command-Line Options that Affect
508 Option-File Handling”.
509 · --parallel-schemas=[N:]db_list
511 Create a queue for processing the databases in db_list, which is a
513 list of one or more comma-separated database names. If N is given,
514 the queue uses N threads. If N is not given, the
515 --default-parallelism option determines the number of queue
516 threads.
517 Multiple instances of this option create multiple queues.
519 mysqlpump also creates a default queue to use for databases not
520 named in any --parallel-schemas option, and for dumping user
521 definitions if command options select them. For more information,
522 see mysqlpump Parallel Processing.
523 · --password[=password], -p[password]
525 The password of the MySQL account used for connecting to the
527 server. The password value is optional. If not given, mysqlpump
528 prompts for one. If given, there must be no space between
529 --password= or -p and the password following it. If no password
530 option is specified, the default is to send no password.
531 Specifying a password on the command line should be considered
533 insecure. To avoid giving the password on the command line, use an
534 option file. See Section 6.1.2.1, “End-User Guidelines for Password
535 Security”.
536 To explicitly specify that there is no password and that mysqlpump
538 should not prompt for one, use the --skip-password option.
539 · --plugin-dir=dir_name
541 The directory in which to look for plugins. Specify this option if
543 the --default-auth option is used to specify an authentication
544 plugin but mysqlpump does not find it. See Section 6.2.17,
545 “Pluggable Authentication”.
546 · --port=port_num, -P port_num
548 For TCP/IP connections, the port number to use.
550 · --print-defaults
552 Print the program name and all options that it gets from option
554 files.
555 For additional information about this and other option-file
557 options, see Section 4.2.2.3, “Command-Line Options that Affect
558 Option-File Handling”.
559 · --protocol={TCP|SOCKET|PIPE|MEMORY}
561 The connection protocol to use for connecting to the server. It is
563 useful when the other connection parameters normally result in use
564 of a protocol other than the one you want. For details on the
565 permissible values, see Section 4.2.4, “Connecting to the MySQL
566 Server Using Command Options”.
567 · --replace
569 Write REPLACE statements rather than INSERT statements.
571 · --result-file=file_name
573 Direct output to the named file. The result file is created and its
575 previous contents overwritten, even if an error occurs while
576 generating the dump.
577 This option should be used on Windows to prevent newline \n
579 characters from being converted to \r\n carriage return/newline
580 sequences.
581 · --routines
583 Include stored routines (procedures and functions) for the dumped
585 databases in the output. This option requires the global SELECT
586 privilege.
587 The output generated by using --routines contains CREATE PROCEDURE
589 and CREATE FUNCTION statements to create the routines.
590 This option is enabled by default; use --skip-routines to disable
592 it.
593 · --secure-auth
595 This option was removed in MySQL 8.0.3.
597 · --server-public-key-path=file_name
599 The path name to a file containing a client-side copy of the public
601 key required by the server for RSA key pair-based password
602 exchange. The file must be in PEM format. This option applies to
603 clients that authenticate with the sha256_password or
604 caching_sha2_password authentication plugin. This option is ignored
605 for accounts that do not authenticate with one of those plugins. It
606 is also ignored if RSA-based password exchange is not used, as is
607 the case when the client connects to the server using a secure
608 connection.
609 If --server-public-key-path=file_name is given and specifies a
611 valid public key file, it takes precedence over
612 --get-server-public-key.
613 For sha256_password, this option applies only if MySQL was built
615 using OpenSSL.
616 For information about the sha256_password and caching_sha2_password
618 plugins, see Section 6.4.1.2, “SHA-256 Pluggable Authentication”,
619 and Section 6.4.1.3, “Caching SHA-2 Pluggable Authentication”.
620 · --set-charset
622 Write SET NAMES default_character_set to the output.
624 This option is enabled by default. To disable it and suppress the
626 SET NAMES statement, use --skip-set-charset.
627 · --set-gtid-purged=value
629 This option enables control over global transaction ID (GTID)
631 information written to the dump file, by indicating whether to add
632 a SET @@GLOBAL.gtid_purged statement to the output. This option may
633 also cause a statement to be written to the output that disables
634 binary logging while the dump file is being reloaded.
635 The following table shows the permitted option values. The default
637 value is AUTO.
638 ┌──────┬────────────────────────────┐
640 │Value │ Meaning │
641 ├──────┼────────────────────────────┤
642 │OFF │ Add no SET statement to │
643 │ │ the output. │
644 ├──────┼────────────────────────────┤
645 │ON │ Add a SET statement to the │
646 │ │ output. An error occurs if │
647 │ │ GTIDs │
648 │ │ are not enabled on the │
649 │ │ server. │
650 ├──────┼────────────────────────────┤
651 │AUTO │ Add a SET statement to the │
652 │ │ output if GTIDs are │
653 │ │ enabled │
654 │ │ on the server. │
655 └──────┴────────────────────────────┘
656 The --set-gtid-purged option has the following effect on binary
657 logging when the dump file is reloaded:
658 · --set-gtid-purged=OFF: SET @@SESSION.SQL_LOG_BIN=0; is not
660 added to the output.
661 · --set-gtid-purged=ON: SET @@SESSION.SQL_LOG_BIN=0; is added to
663 the output.
664 · --set-gtid-purged=AUTO: SET @@SESSION.SQL_LOG_BIN=0; is added
666 to the output if GTIDs are enabled on the server you are
667 backing up (that is, if AUTO evaluates to ON).
668 · --single-transaction
670 This option sets the transaction isolation mode to REPEATABLE READ
672 and sends a START TRANSACTION SQL statement to the server before
673 dumping data. It is useful only with transactional tables such as
674 InnoDB, because then it dumps the consistent state of the database
675 at the time when START TRANSACTION was issued without blocking any
676 applications.
677 When using this option, you should keep in mind that only InnoDB
679 tables are dumped in a consistent state. For example, any MyISAM or
680 MEMORY tables dumped while using this option may still change
681 state.
682 While a --single-transaction dump is in process, to ensure a valid
684 dump file (correct table contents and binary log coordinates), no
685 other connection should use the following statements: ALTER TABLE,
686 CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A
687 consistent read is not isolated from those statements, so use of
688 them on a table to be dumped can cause the SELECT that is performed
689 by mysqlpump to retrieve the table contents to obtain incorrect
690 contents or fail.
691 --add-locks and --single-transaction are mutually exclusive.
693 · --skip-definer
695 Omit DEFINER and SQL SECURITY clauses from the CREATE statements
697 for views and stored programs. The dump file, when reloaded,
698 creates objects that use the default DEFINER and SQL SECURITY
699 values. See Section 24.6, “Stored Object Access Control”.
700 · --skip-dump-rows, -d
702 Do not dump table rows.
704 · --socket=path, -S path
706 For connections to localhost, the Unix socket file to use, or, on
708 Windows, the name of the named pipe to use.
709 On Windows, this option applies only if the server was started with
711 the named_pipe system variable enabled to support named-pipe
712 connections. In addition, the user making the connection must be a
713 member of the Windows group specified by the
714 named_pipe_full_access_group system variable.
715 · --ssl*
717 Options that begin with --ssl specify whether to connect to the
719 server using SSL and indicate where to find SSL keys and
720 certificates. See the section called “Command Options for Encrypted
721 Connections”.
722 · --ssl-fips-mode={OFF|ON|STRICT} Controls whether to enable FIPS
724 mode on the client side. The --ssl-fips-mode option differs from
725 other --ssl-xxx options in that it is not used to establish
726 encrypted connections, but rather to affect which cryptographic
727 operations are permitted. See Section 6.5, “FIPS Support”.
728 These --ssl-fips-mode values are permitted:
730 · OFF: Disable FIPS mode.
732 · ON: Enable FIPS mode.
734 · STRICT: Enable “strict” FIPS mode.
736 Note
739 If the OpenSSL FIPS Object Module is not available, the only
740 permitted value for --ssl-fips-mode is OFF. In this case,
741 setting --ssl-fips-mode to ON or STRICT causes the client to
742 produce a warning at startup and to operate in non-FIPS mode.
743 · --tls-ciphersuites=ciphersuite_list
745 The permissible ciphersuites for encrypted connections that use
747 TLSv1.3. The value is a list of one or more colon-separated
748 ciphersuite names. The ciphersuites that can be named for this
749 option depend on the SSL library used to compile MySQL. For
750 details, see Section 6.3.2, “Encrypted Connection TLS Protocols and
751 Ciphers”.
752 This option was added in MySQL 8.0.16.
754 · --tls-version=protocol_list
756 The permissible TLS protocols for encrypted connections. The value
758 is a list of one or more comma-separated protocol names. The
759 protocols that can be named for this option depend on the SSL
760 library used to compile MySQL. For details, see Section 6.3.2,
761 “Encrypted Connection TLS Protocols and Ciphers”.
762 · --triggers
764 Include triggers for each dumped table in the output.
766 This option is enabled by default; use --skip-triggers to disable
768 it.
769 · --tz-utc
771 This option enables TIMESTAMP columns to be dumped and reloaded
773 between servers in different time zones. mysqlpump sets its
774 connection time zone to UTC and adds SET TIME_ZONE='+00:00' to the
775 dump file. Without this option, TIMESTAMP columns are dumped and
776 reloaded in the time zones local to the source and destination
777 servers, which can cause the values to change if the servers are in
778 different time zones. --tz-utc also protects against changes due
779 to daylight saving time.
780 This option is enabled by default; use --skip-tz-utc to disable it.
782 · --user=user_name, -u user_name
784 The user name of the MySQL account to use for connecting to the
786 server.
787 · --users
789 Dump user accounts as logical definitions in the form of CREATE
791 USER and GRANT statements.
792 User definitions are stored in the grant tables in the mysql system
794 database. By default, mysqlpump does not include the grant tables
795 in mysql database dumps. To dump the contents of the grant tables
796 as logical definitions, use the --users option and suppress all
797 database dumping:
798 shell> mysqlpump --exclude-databases=% --users
800 · --version, -V
802 Display version information and exit.
804 · --watch-progress
806 Periodically display a progress indicator that provides information
808 about the completed and total number of tables, rows, and other
809 objects.
810 This option is enabled by default; use --skip-watch-progress to
812 disable it.
813 · --zstd-compression-level=level The compression level to use for
815 connections to the server that use the zstd compression algorithm.
816 The permitted levels are from 1 to 22, with larger values
817 indicating increasing levels of compression. The default zstd
818 compression level is 3. The compression level setting has no effect
819 on connections that do not use zstd compression.
820 For more information, see Section 4.2.6, “Connection Compression
822 Control”.
823 This option was added in MySQL 8.0.18.
825 mysqlpump Object Selection.PP mysqlpump has a set of inclusion and
826 exclusion options that enable filtering of several object types and
827 provide flexible control over which objects to dump:
828 · --include-databases and --exclude-databases apply to databases and
830 all objects within them.
831 · --include-tables and --exclude-tables apply to tables. These
833 options also affect triggers associated with tables unless the
834 trigger-specific options are given.
835 · --include-triggers and --exclude-triggers apply to triggers.
837 · --include-routines and --exclude-routines apply to stored
839 procedures and functions. If a routine option matches a stored
840 procedure name, it also matches a stored function of the same name.
841 · --include-events and --exclude-events apply to Event Scheduler
843 events.
844 · --include-users and --exclude-users apply to user accounts.
846 Any inclusion or exclusion option may be given multiple times. The
848 effect is additive. Order of these options does not matter.
849 The value of each inclusion and exclusion option is a list of
851 comma-separated names of the appropriate object type. For example:
852 --exclude-databases=test,world
854 --include-tables=customer,invoice
855 Wildcard characters are permitted in the object names:
857 · % matches any sequence of zero or more characters.
859 · _ matches any single character.
861 For example, --include-tables=t%,__tmp matches all table names that
863 begin with t and all five-character table names that end with tmp.
864 For users, a name specified without a host part is interpreted with an
866 implied host of %. For example, u1 and u1@% are equivalent. This is the
867 same equivalence that applies in MySQL generally (see Section 6.2.4,
868 “Specifying Account Names”).
869 Inclusion and exclusion options interact as follows:
871 · By default, with no inclusion or exclusion options, mysqlpump dumps
873 all databases (with certain exceptions noted in mysqlpump
874 Restrictions).
875 · If inclusion options are given in the absence of exclusion options,
877 only the objects named as included are dumped.
878 · If exclusion options are given in the absence of inclusion options,
880 all objects are dumped except those named as excluded.
881 · If inclusion and exclusion options are given, all objects named as
883 excluded and not named as included are not dumped. All other
884 objects are dumped.
885 If multiple databases are being dumped, it is possible to name tables,
887 triggers, and routines in a specific database by qualifying the object
888 names with the database name. The following command dumps databases db1
889 and db2, but excludes tables db1.t1 and db2.t2:
890 shell> mysqlpump --include-databases=db1,db2 --exclude-tables=db1.t1,db2.t2
892 The following options provide alternative ways to specify which
894 databases to dump:
895 · The --all-databases option dumps all databases (with certain
897 exceptions noted in mysqlpump Restrictions). It is equivalent to
898 specifying no object options at all (the default mysqlpump action
899 is to dump everything).
900 --include-databases=% is similar to --all-databases, but selects
902 all databases for dumping, even those that are exceptions for
903 --all-databases.
904 · The --databases option causes mysqlpump to treat all name arguments
906 as names of databases to dump. It is equivalent to an
907 --include-databases option that names the same databases.
908 mysqlpump Parallel Processing.PP mysqlpump can use parallelism to
909 achieve concurrent processing. You can select concurrency between
910 databases (to dump multiple databases simultaneously) and within
911 databases (to dump multiple objects from a given database
912 simultaneously).
913 By default, mysqlpump sets up one queue with two threads. You can
915 create additional queues and control the number of threads assigned to
916 each one, including the default queue:
917 · --default-parallelism=N specifies the default number of threads
919 used for each queue. In the absence of this option, N is 2.
920 The default queue always uses the default number of threads.
922 Additional queues use the default number of threads unless you
923 specify otherwise.
924 · --parallel-schemas=[N:]db_list sets up a processing queue for
926 dumping the databases named in db_list and optionally specifies how
927 many threads the queue uses. db_list is a list of comma-separated
928 database names. If the option argument begins with N:, the queue
929 uses N threads. Otherwise, the --default-parallelism option
930 determines the number of queue threads.
931 Multiple instances of the --parallel-schemas option create multiple
933 queues.
934 Names in the database list are permitted to contain the same % and
936 _ wildcard characters supported for filtering options (see
937 mysqlpump Object Selection).
938 mysqlpump uses the default queue for processing any databases not named
940 explicitly with a --parallel-schemas option, and for dumping user
941 definitions if command options select them.
942 In general, with multiple queues, mysqlpump uses parallelism between
944 the sets of databases processed by the queues, to dump multiple
945 databases simultaneously. For a queue that uses multiple threads,
946 mysqlpump uses parallelism within databases, to dump multiple objects
947 from a given database simultaneously. Exceptions can occur; for
948 example, mysqlpump may block queues while it obtains from the server
949 lists of objects in databases.
950 With parallelism enabled, it is possible for output from different
952 databases to be interleaved. For example, INSERT statements from
953 multiple tables dumped in parallel can be interleaved; the statements
954 are not written in any particular order. This does not affect reloading
955 because output statements qualify object names with database names or
956 are preceded by USE statements as required.
957 The granularity for parallelism is a single database object. For
959 example, a single table cannot be dumped in parallel using multiple
960 threads.
961 Examples:
963 shell> mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
965 mysqlpump sets up a queue to process db1 and db2, another queue to
967 process db3, and a default queue to process all other databases. All
968 queues use two threads.
969 shell> mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
971 --default-parallelism=4
972 This is the same as the previous example except that all queues use
974 four threads.
975 shell> mysqlpump --parallel-schemas=5:db1,db2 --parallel-schemas=3:db3
977 The queue for db1 and db2 uses five threads, the queue for db3 uses
979 three threads, and the default queue uses the default of two threads.
980 As a special case, with --default-parallelism=0 and no
982 --parallel-schemas options, mysqlpump runs as a single-threaded process
983 and creates no queues. mysqlpump Restrictions.PP mysqlpump does not
984 dump the performance_schema, ndbinfo, or sys schema by default. To dump
985 any of these, name them explicitly on the command line. You can also
986 name them with the --databases or --include-databases option.
987 mysqlpump does not dump the INFORMATION_SCHEMA schema.
989 mysqlpump does not dump InnoDB CREATE TABLESPACE statements.
991 mysqlpump dumps user accounts in logical form using CREATE USER and
993 GRANT statements (for example, when you use the --include-users or
994 --users option). For this reason, dumps of the mysql system database do
995 not by default include the grant tables that contain user definitions:
996 user, db, tables_priv, columns_priv, procs_priv, or proxies_priv. To
997 dump any of the grant tables, name the mysql database followed by the
998 table names:
999 shell> mysqlpump mysql user db ...
1001
1003 Copyright © 1997, 2019, Oracle and/or its affiliates. All rights
1004 reserved.
1005 This documentation is free software; you can redistribute it and/or
1007 modify it only under the terms of the GNU General Public License as
1008 published by the Free Software Foundation; version 2 of the License.
1009 This documentation is distributed in the hope that it will be useful,
1011 but WITHOUT ANY WARRANTY; without even the implied warranty of
1012 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
1013 General Public License for more details.
1014 You should have received a copy of the GNU General Public License along
1016 with the program; if not, write to the Free Software Foundation, Inc.,
1017 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
1018 http://www.gnu.org/licenses/.
1019
1022 For more information, please refer to the MySQL Reference Manual, which
1023 may already be installed locally and which is also available online at
1024 http://dev.mysql.com/doc/.
1025
1027 Oracle Corporation (http://dev.mysql.com/).
1028MySQL 8.0 09/06/2019 MYSQLPUMP(1)