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.7, “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.14, “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 both support compression.
195 · --compress-output=algorithm
197 By default, mysqlpump does not compress output. This option
199 specifies output compression using the specified algorithm.
200 Permitted algorithms are LZ4 and ZLIB.
201 To uncompress compressed output, you must have an appropriate
203 utility. If the system commands lz4 and openssl zlib are not
204 available, MySQL distributions include lz4_decompress and
205 zlib_decompress utilities that can be used to decompress mysqlpump
206 output that was compressed using the --compress-output=LZ4 and
207 --compress-output=ZLIB options. For more information, see
208 lz4_decompress(1), and zlib_decompress(1).
209 · --databases, -B
211 Normally, mysqlpump treats the first name argument on the command
213 line as a database name and any following names as table names.
214 With this option, it treats all name arguments as database names.
215 CREATE DATABASE statements are included in the output before each
216 new database.
217 --all-databases and --databases are mutually exclusive.
219 · --debug[=debug_options], -# [debug_options]
221 Write a debugging log. A typical debug_options string is
223 d:t:o,file_name. The default is d:t:O,/tmp/mysqlpump.trace.
224 · --debug-check
226 Print some debugging information when the program exits.
228 · --debug-info, -T
230 Print debugging information and memory and CPU usage statistics
232 when the program exits.
233 · --default-auth=plugin
235 A hint about the client-side authentication plugin to use. See
237 Section 6.3.10, “Pluggable Authentication”.
238 · --default-character-set=charset_name
240 Use charset_name as the default character set. See Section 10.14,
242 “Character Set Configuration”. If no character set is specified,
243 mysqlpump uses utf8.
244 · --default-parallelism=N
246 The default number of threads for each parallel processing queue.
248 The default is 2.
249 The --parallel-schemas option also affects parallelism and can be
251 used to override the default number of threads. For more
252 information, see mysqlpump Parallel Processing.
253 With --default-parallelism=0 and no --parallel-schemas options,
255 mysqlpump runs as a single-threaded process and creates no queues.
256 With parallelism enabled, it is possible for output from different
258 databases to be interleaved.
259 · --defaults-extra-file=file_name
261 Read this option file after the global option file but (on Unix)
263 before the user option file. If the file does not exist or is
264 otherwise inaccessible, an error occurs. file_name is interpreted
265 relative to the current directory if given as a relative path name
266 rather than a full path name.
267 For additional information about this and other option-file
269 options, see Section 4.2.8, “Command-Line Options that Affect
270 Option-File Handling”.
271 · --defaults-file=file_name
273 Use only the given option file. If the file does not exist or is
275 otherwise inaccessible, an error occurs. file_name is interpreted
276 relative to the current directory if given as a relative path name
277 rather than a full path name.
278 Exception: Even with --defaults-file, client programs read
280 .mylogin.cnf.
281 For additional information about this and other option-file
283 options, see Section 4.2.8, “Command-Line Options that Affect
284 Option-File Handling”.
285 · --defaults-group-suffix=str
287 Read not only the usual option groups, but also groups with the
289 usual names and a suffix of str. For example, mysqlpump normally
290 reads the [client] and [mysqlpump] groups. If the
291 --defaults-group-suffix=_other option is given, mysqlpump also
292 reads the [client_other] and [mysqlpump_other] groups.
293 For additional information about this and other option-file
295 options, see Section 4.2.8, “Command-Line Options that Affect
296 Option-File Handling”.
297 · --defer-table-indexes
299 In the dump output, defer index creation for each table until after
301 its rows have been loaded. This works for all storage engines, but
302 for InnoDB applies only for secondary indexes.
303 This option is enabled by default; use --skip-defer-table-indexes
305 to disable it.
306 · --events
308 Include Event Scheduler events for the dumped databases in the
310 output. Event dumping requires the EVENT privileges for those
311 databases.
312 The output generated by using --events contains CREATE EVENT
314 statements to create the events.
315 This option is enabled by default; use --skip-events to disable it.
317 · --exclude-databases=db_list
319 Do not dump the databases in db_list, which is a list of one or
321 more comma-separated database names. Multiple instances of this
322 option are additive. For more information, see mysqlpump Object
323 Selection.
324 · --exclude-events=event_list
326 Do not dump the databases in event_list, which is a list of one or
328 more comma-separated event names. Multiple instances of this option
329 are additive. For more information, see mysqlpump Object Selection.
330 · --exclude-routines=routine_list
332 Do not dump the events in routine_list, which is a list of one or
334 more comma-separated routine (stored procedure or function) names.
335 Multiple instances of this option are additive. For more
336 information, see mysqlpump Object Selection.
337 · --exclude-tables=table_list
339 Do not dump the tables in table_list, which is a list of one or
341 more comma-separated table names. Multiple instances of this option
342 are additive. For more information, see mysqlpump Object Selection.
343 · --exclude-triggers=trigger_list
345 Do not dump the triggers in trigger_list, which is a list of one or
347 more comma-separated trigger names. Multiple instances of this
348 option are additive. For more information, see mysqlpump Object
349 Selection.
350 · --exclude-users=user_list
352 Do not dump the user accounts in user_list, which is a list of one
354 or more comma-separated account names. Multiple instances of this
355 option are additive. For more information, see mysqlpump Object
356 Selection.
357 · --extended-insert=N
359 Write INSERT statements using multiple-row syntax that includes
361 several VALUES lists. This results in a smaller dump file and
362 speeds up inserts when the file is reloaded.
363 The option value indicates the number of rows to include in each
365 INSERT statement. The default is 250. A value of 1 produces one
366 INSERT statement per table row.
367 · --get-server-public-key
369 Request from the server the public key required for RSA key
371 pair-based password exchange. This option applies to clients that
372 that authenticate with the caching_sha2_password authentication
373 plugin. For that plugin, the server does not send the public key
374 unless requested. This option is ignored for accounts that do not
375 authenticate with that plugin. It is also ignored if RSA-based
376 password exchange is not used, as is the case when the client
377 connects to the server using a secure connection.
378 If --server-public-key-path=file_name is given and specifies a
380 valid public key file, it takes precedence over
381 --get-server-public-key.
382 For information about the caching_sha2_password plugin, see
384 Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
385 · --hex-blob
387 Dump binary columns using hexadecimal notation (for example, 'abc'
389 becomes 0x616263). The affected data types are BINARY, VARBINARY,
390 the BLOB types, and BIT.
391 · --host=host_name, -h host_name
393 Dump data from the MySQL server on the given host.
395 · --include-databases=db_list
397 Dump the databases in db_list, which is a list of one or more
399 comma-separated database names. The dump includes all objects in
400 the named databases. Multiple instances of this option are
401 additive. For more information, see mysqlpump Object Selection.
402 · --include-events=event_list
404 Dump the events in event_list, which is a list of one or more
406 comma-separated event names. Multiple instances of this option are
407 additive. For more information, see mysqlpump Object Selection.
408 · --include-routines=routine_list
410 Dump the routines in routine_list, which is a list of one or more
412 comma-separated routine (stored procedure or function) names.
413 Multiple instances of this option are additive. For more
414 information, see mysqlpump Object Selection.
415 · --include-tables=table_list
417 Dump the tables in table_list, which is a list of one or more
419 comma-separated table names. Multiple instances of this option are
420 additive. For more information, see mysqlpump Object Selection.
421 · --include-triggers=trigger_list
423 Dump the triggers in trigger_list, which is a list of one or more
425 comma-separated trigger names. Multiple instances of this option
426 are additive. For more information, see mysqlpump Object Selection.
427 · --include-users=user_list
429 Dump the user accounts in user_list, which is a list of one or more
431 comma-separated user names. Multiple instances of this option are
432 additive. For more information, see mysqlpump Object Selection.
433 · --insert-ignore
435 Write INSERT IGNORE statements rather than INSERT statements.
437 · --log-error-file=file_name
439 Log warnings and errors by appending them to the named file. If
441 this option is not given, mysqlpump writes warnings and errors to
442 the standard error output.
443 · --login-path=name
445 Read options from the named login path in the .mylogin.cnf login
447 path file. A “login path” is an option group containing options
448 that specify which MySQL server to connect to and which account to
449 authenticate as. To create or modify a login path file, use the
450 mysql_config_editor utility. See mysql_config_editor(1).
451 For additional information about this and other option-file
453 options, see Section 4.2.8, “Command-Line Options that Affect
454 Option-File Handling”.
455 · --max-allowed-packet=N
457 The maximum size of the buffer for client/server communication. The
459 default is 24MB, the maximum is 1GB.
460 · --net-buffer-length=N
462 The initial size of the buffer for client/server communication.
464 When creating multiple-row INSERT statements (as with the
465 --extended-insert option), mysqlpump creates rows up to N bytes
466 long. If you use this option to increase the value, ensure that the
467 MySQL server net_buffer_length system variable has a value at least
468 this large.
469 · --no-create-db
471 Suppress any CREATE DATABASE statements that might otherwise be
473 included in the output.
474 · --no-create-info, -t
476 Do not write CREATE TABLE statements that create each dumped table.
478 · --no-defaults
480 Do not read any option files. If program startup fails due to
482 reading unknown options from an option file, --no-defaults can be
483 used to prevent them from being read.
484 The exception is that the .mylogin.cnf file, if it exists, is read
486 in all cases. This permits passwords to be specified in a safer way
487 than on the command line even when --no-defaults is used.
488 (.mylogin.cnf is created by the mysql_config_editor utility. See
489 mysql_config_editor(1).)
490 For additional information about this and other option-file
492 options, see Section 4.2.8, “Command-Line Options that Affect
493 Option-File Handling”.
494 · --parallel-schemas=[N:]db_list
496 Create a queue for processing the databases in db_list, which is a
498 list of one or more comma-separated database names. If N is given,
499 the queue uses N threads. If N is not given, the
500 --default-parallelism option determines the number of queue
501 threads.
502 Multiple instances of this option create multiple queues.
504 mysqlpump also creates a default queue to use for databases not
505 named in any --parallel-schemas option, and for dumping user
506 definitions if command options select them. For more information,
507 see mysqlpump Parallel Processing.
508 · --password[=password], -p[password]
510 The password to use when connecting to the server. If you use the
512 short option form (-p), you cannot have a space between the option
513 and the password. If you omit the password value following the
514 --password or -p option on the command line, mysqlpump prompts for
515 one.
516 Specifying a password on the command line should be considered
518 insecure. See Section 6.1.2.1, “End-User Guidelines for Password
519 Security”. You can use an option file to avoid giving the password
520 on the command line.
521 · --plugin-dir=dir_name
523 The directory in which to look for plugins. Specify this option if
525 the --default-auth option is used to specify an authentication
526 plugin but mysqlpump does not find it. See Section 6.3.10,
527 “Pluggable Authentication”.
528 · --port=port_num, -P port_num
530 The TCP/IP port number to use for the connection.
532 · --print-defaults
534 Print the program name and all options that it gets from option
536 files.
537 For additional information about this and other option-file
539 options, see Section 4.2.8, “Command-Line Options that Affect
540 Option-File Handling”.
541 · --protocol={TCP|SOCKET|PIPE|MEMORY}
543 The connection protocol to use for connecting to the server. It is
545 useful when the other connection parameters normally would cause a
546 protocol to be used other than the one you want. For details on the
547 permissible values, see Section 4.2.2, “Connecting to the MySQL
548 Server”.
549 · --replace
551 Write REPLACE statements rather than INSERT statements.
553 · --result-file=file_name
555 Direct output to the named file. The result file is created and its
557 previous contents overwritten, even if an error occurs while
558 generating the dump.
559 This option should be used on Windows to prevent newline \n
561 characters from being converted to \r\n carriage return/newline
562 sequences.
563 · --routines
565 Include stored routines (procedures and functions) for the dumped
567 databases in the output. This option requires the global SELECT
568 privilege.
569 The output generated by using --routines contains CREATE PROCEDURE
571 and CREATE FUNCTION statements to create the routines.
572 This option is enabled by default; use --skip-routines to disable
574 it.
575 · --secure-auth
577 This option was removed in MySQL 8.0.3.
579 · --server-public-key-path=file_name
581 The path name to a file containing a client-side copy of the public
583 key required by the server for RSA key pair-based password
584 exchange. The file must be in PEM format. This option applies to
585 clients that authenticate with the sha256_password or
586 caching_sha2_password authentication plugin. This option is ignored
587 for accounts that do not authenticate with one of those plugins. It
588 is also ignored if RSA-based password exchange is not used, as is
589 the case when the client connects to the server using a secure
590 connection.
591 If --server-public-key-path=file_name is given and specifies a
593 valid public key file, it takes precedence over
594 --get-server-public-key.
595 For sha256_password, this option applies only if MySQL was built
597 using OpenSSL.
598 For information about the sha256_password and caching_sha2_password
600 plugins, see Section 6.5.1.2, “SHA-256 Pluggable Authentication”,
601 and Section 6.5.1.3, “Caching SHA-2 Pluggable Authentication”.
602 · --set-charset
604 Write SET NAMES default_character_set to the output.
606 This option is enabled by default. To disable it and suppress the
608 SET NAMES statement, use --skip-set-charset.
609 · --set-gtid-purged=value
611 This option enables control over global transaction ID (GTID)
613 information written to the dump file, by indicating whether to add
614 a SET @@GLOBAL.gtid_purged statement to the output. This option may
615 also cause a statement to be written to the output that disables
616 binary logging while the dump file is being reloaded.
617 The following table shows the permitted option values. The default
619 value is AUTO.
620 ┌──────┬────────────────────────────┐
622 │Value │ Meaning │
623 ├──────┼────────────────────────────┤
624 │OFF │ Add no SET statement to │
625 │ │ the output. │
626 ├──────┼────────────────────────────┤
627 │ON │ Add a SET statement to the │
628 │ │ output. An error occurs if │
629 │ │ GTIDs │
630 │ │ are not enabled on the │
631 │ │ server. │
632 ├──────┼────────────────────────────┤
633 │AUTO │ Add a SET statement to the │
634 │ │ output if GTIDs are │
635 │ │ enabled │
636 │ │ on the server. │
637 └──────┴────────────────────────────┘
638 The --set-gtid-purged option has the following effect on binary
639 logging when the dump file is reloaded:
640 · --set-gtid-purged=OFF: SET @@SESSION.SQL_LOG_BIN=0; is not
642 added to the output.
643 · --set-gtid-purged=ON: SET @@SESSION.SQL_LOG_BIN=0; is added to
645 the output.
646 · --set-gtid-purged=AUTO: SET @@SESSION.SQL_LOG_BIN=0; is added
648 to the output if GTIDs are enabled on the server you are
649 backing up (that is, if AUTO evaluates to ON).
650 · --single-transaction
652 This option sets the transaction isolation mode to REPEATABLE READ
654 and sends a START TRANSACTION SQL statement to the server before
655 dumping data. It is useful only with transactional tables such as
656 InnoDB, because then it dumps the consistent state of the database
657 at the time when START TRANSACTION was issued without blocking any
658 applications.
659 When using this option, you should keep in mind that only InnoDB
661 tables are dumped in a consistent state. For example, any MyISAM or
662 MEMORY tables dumped while using this option may still change
663 state.
664 While a --single-transaction dump is in process, to ensure a valid
666 dump file (correct table contents and binary log coordinates), no
667 other connection should use the following statements: ALTER TABLE,
668 CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A
669 consistent read is not isolated from those statements, so use of
670 them on a table to be dumped can cause the SELECT that is performed
671 by mysqlpump to retrieve the table contents to obtain incorrect
672 contents or fail.
673 --add-locks and --single-transaction are mutually exclusive.
675 · --skip-definer
677 Omit DEFINER and SQL SECURITY clauses from the CREATE statements
679 for views and stored programs. The dump file, when reloaded,
680 creates objects that use the default DEFINER and SQL SECURITY
681 values. See Section 24.6, “Access Control for Stored Programs and
682 Views”.
683 · --skip-dump-rows, -d
685 Do not dump table rows.
687 · --socket={file_name|pipe_name}, -S {file_name|pipe_name}
689 For connections to localhost, the Unix socket file to use, or, on
691 Windows, the name of the named pipe to use.
692 · --ssl*
694 Options that begin with --ssl specify whether to connect to the
696 server using SSL and indicate where to find SSL keys and
697 certificates. See Section 6.4.2, “Command Options for Encrypted
698 Connections”.
699 · --ssl-fips-mode={OFF|ON|STRICT} Controls whether to enable FIPS
701 mode on the client side. The --ssl-fips-mode option differs from
702 other --ssl-xxx options in that it is not used to establish
703 encrypted connections, but rather to affect which cryptographic
704 operations are permitted. See Section 6.6, “FIPS Support”.
705 These --ssl-fips-mode values are permitted:
707 · OFF: Disable FIPS mode.
709 · ON: Enable FIPS mode.
711 · STRICT: Enable “strict” FIPS mode.
713 Note
716 If the OpenSSL FIPS Object Module is not available, the only
717 permitted value for --ssl-fips-mode is OFF. In this case,
718 setting --ssl-fips-mode to ON or STRICT causes the client to
719 produce a warning at startup and to operate in non-FIPS mode.
720 · --tls-ciphersuites=ciphersuite_list
722 For client programs, specifies which TLSv1.3 ciphersuites the
724 client permits for encrypted connections. The value is a list of
725 one or more colon-separated ciphersuite names. The ciphersuites
726 that can be named for this option depend on the SSL library used to
727 compile MySQL. For details, see Section 6.4.6, “Encrypted
728 Connection Protocols and Ciphers”.
729 This option was added in MySQL 8.0.16.
731 · --tls-version=protocol_list
733 The protocols the client permits for encrypted connections. The
735 value is a list of one or more comma-separated protocol names. The
736 protocols that can be named for this option depend on the SSL
737 library used to compile MySQL. For details, see Section 6.4.6,
738 “Encrypted Connection Protocols and Ciphers”.
739 · --triggers
741 Include triggers for each dumped table in the output.
743 This option is enabled by default; use --skip-triggers to disable
745 it.
746 · --tz-utc
748 This option enables TIMESTAMP columns to be dumped and reloaded
750 between servers in different time zones. mysqlpump sets its
751 connection time zone to UTC and adds SET TIME_ZONE='+00:00' to the
752 dump file. Without this option, TIMESTAMP columns are dumped and
753 reloaded in the time zones local to the source and destination
754 servers, which can cause the values to change if the servers are in
755 different time zones. --tz-utc also protects against changes due
756 to daylight saving time.
757 This option is enabled by default; use --skip-tz-utc to disable it.
759 · --user=user_name, -u user_name
761 The MySQL user name to use when connecting to the server.
763 · --users
765 Dump user accounts as logical definitions in the form of CREATE
767 USER and GRANT statements.
768 User definitions are stored in the grant tables in the mysql system
770 database. By default, mysqlpump does not include the grant tables
771 in mysql database dumps. To dump the contents of the grant tables
772 as logical definitions, use the --users option and suppress all
773 database dumping:
774 shell> mysqlpump --exclude-databases=% --users
776 · --version, -V
778 Display version information and exit.
780 · --watch-progress
782 Periodically display a progress indicator that provides information
784 about the completed and total number of tables, rows, and other
785 objects.
786 This option is enabled by default; use --skip-watch-progress to
788 disable it.
789 mysqlpump Object Selection.PP mysqlpump has a set of inclusion and
790 exclusion options that enable filtering of several object types and
791 provide flexible control over which objects to dump:
792 · --include-databases and --exclude-databases apply to databases and
794 all objects within them.
795 · --include-tables and --exclude-tables apply to tables. These
797 options also affect triggers associated with tables unless the
798 trigger-specific options are given.
799 · --include-triggers and --exclude-triggers apply to triggers.
801 · --include-routines and --exclude-routines apply to stored
803 procedures and functions. If a routine option matches a stored
804 procedure name, it also matches a stored function of the same name.
805 · --include-events and --exclude-events apply to Event Scheduler
807 events.
808 · --include-users and --exclude-users apply to user accounts.
810 Any inclusion or exclusion option may be given multiple times. The
812 effect is additive. Order of these options does not matter.
813 The value of each inclusion and exclusion option is a list of
815 comma-separated names of the appropriate object type. For example:
816 --exclude-databases=test,world
818 --include-tables=customer,invoice
819 Wildcard characters are permitted in the object names:
821 · % matches any sequence of zero or more characters.
823 · _ matches any single character.
825 For example, --include-tables=t%,__tmp matches all table names that
827 begin with t and all five-character table names that end with tmp.
828 For users, a name specified without a host part is interpreted with an
830 implied host of %. For example, u1 and u1@% are equivalent. This is the
831 same equivalence that applies in MySQL generally (see Section 6.2.4,
832 “Specifying Account Names”).
833 Inclusion and exclusion options interact as follows:
835 · By default, with no inclusion or exclusion options, mysqlpump dumps
837 all databases (with certain exceptions noted in mysqlpump
838 Restrictions).
839 · If inclusion options are given in the absence of exclusion options,
841 only the objects named as included are dumped.
842 · If exclusion options are given in the absence of inclusion options,
844 all objects are dumped except those named as excluded.
845 · If inclusion and exclusion options are given, all objects named as
847 excluded and not named as included are not dumped. All other
848 objects are dumped.
849 If multiple databases are being dumped, it is possible to name tables,
851 triggers, and routines in a specific database by qualifying the object
852 names with the database name. The following command dumps databases db1
853 and db2, but excludes tables db1.t1 and db2.t2:
854 shell> mysqlpump --include-databases=db1,db2 --exclude-tables=db1.t1,db2.t2
856 The following options provide alternative ways to specify which
858 databases to dump:
859 · The --all-databases option dumps all databases (with certain
861 exceptions noted in mysqlpump Restrictions). It is equivalent to
862 specifying no object options at all (the default mysqlpump action
863 is to dump everything).
864 --include-databases=% is similar to --all-databases, but selects
866 all databases for dumping, even those that are exceptions for
867 --all-databases.
868 · The --databases option causes mysqlpump to treat all name arguments
870 as names of databases to dump. It is equivalent to an
871 --include-databases option that names the same databases.
872 mysqlpump Parallel Processing.PP mysqlpump can use parallelism to
873 achieve concurrent processing. You can select concurrency between
874 databases (to dump multiple databases simultaneously) and within
875 databases (to dump multiple objects from a given database
876 simultaneously).
877 By default, mysqlpump sets up one queue with two threads. You can
879 create additional queues and control the number of threads assigned to
880 each one, including the default queue:
881 · --default-parallelism=N specifies the default number of threads
883 used for each queue. In the absence of this option, N is 2.
884 The default queue always uses the default number of threads.
886 Additional queues use the default number of threads unless you
887 specify otherwise.
888 · --parallel-schemas=[N:]db_list sets up a processing queue for
890 dumping the databases named in db_list and optionally specifies how
891 many threads the queue uses. db_list is a list of comma-separated
892 database names. If the option argument begins with N:, the queue
893 uses N threads. Otherwise, the --default-parallelism option
894 determines the number of queue threads.
895 Multiple instances of the --parallel-schemas option create multiple
897 queues.
898 Names in the database list are permitted to contain the same % and
900 _ wildcard characters supported for filtering options (see
901 mysqlpump Object Selection).
902 mysqlpump uses the default queue for processing any databases not named
904 explicitly with a --parallel-schemas option, and for dumping user
905 definitions if command options select them.
906 In general, with multiple queues, mysqlpump uses parallelism between
908 the sets of databases processed by the queues, to dump multiple
909 databases simultaneously. For a queue that uses multiple threads,
910 mysqlpump uses parallelism within databases, to dump multiple objects
911 from a given database simultaneously. Exceptions can occur; for
912 example, mysqlpump may block queues while it obtains from the server
913 lists of objects in databases.
914 With parallelism enabled, it is possible for output from different
916 databases to be interleaved. For example, INSERT statements from
917 multiple tables dumped in parallel can be interleaved; the statements
918 are not written in any particular order. This does not affect reloading
919 because output statements qualify object names with database names or
920 are preceded by USE statements as required.
921 The granularity for parallelism is a single database object. For
923 example, a single table cannot be dumped in parallel using multiple
924 threads.
925 Examples:
927 shell> mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
929 mysqlpump sets up a queue to process db1 and db2, another queue to
931 process db3, and a default queue to process all other databases. All
932 queues use two threads.
933 shell> mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
935 --default-parallelism=4
936 This is the same as the previous example except that all queues use
938 four threads.
939 shell> mysqlpump --parallel-schemas=5:db1,db2 --parallel-schemas=3:db3
941 The queue for db1 and db2 uses five threads, the queue for db3 uses
943 three threads, and the default queue uses the default of two threads.
944 As a special case, with --default-parallelism=0 and no
946 --parallel-schemas options, mysqlpump runs as a single-threaded process
947 and creates no queues. mysqlpump Restrictions.PP mysqlpump does not
948 dump the performance_schema, ndbinfo, or sys schema by default. To dump
949 any of these, name them explicitly on the command line. You can also
950 name them with the --databases or --include-databases option.
951 mysqlpump does not dump the INFORMATION_SCHEMA schema.
953 mysqlpump does not dump InnoDB CREATE TABLESPACE statements.
955 mysqlpump dumps user accounts in logical form using CREATE USER and
957 GRANT statements (for example, when you use the --include-users or
958 --users option). For this reason, dumps of the mysql system database do
959 not by default include the grant tables that contain user definitions:
960 user, db, tables_priv, columns_priv, procs_priv, or proxies_priv. To
961 dump any of the grant tables, name the mysql database followed by the
962 table names:
963 shell> mysqlpump mysql user db ...
965
967 Copyright © 1997, 2019, Oracle and/or its affiliates. All rights
968 reserved.
969 This documentation is free software; you can redistribute it and/or
971 modify it only under the terms of the GNU General Public License as
972 published by the Free Software Foundation; version 2 of the License.
973 This documentation is distributed in the hope that it will be useful,
975 but WITHOUT ANY WARRANTY; without even the implied warranty of
976 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
977 General Public License for more details.
978 You should have received a copy of the GNU General Public License along
980 with the program; if not, write to the Free Software Foundation, Inc.,
981 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
982 http://www.gnu.org/licenses/.
983
986 For more information, please refer to the MySQL Reference Manual, which
987 may already be installed locally and which is also available online at
988 http://dev.mysql.com/doc/.
989
991 Oracle Corporation (http://dev.mysql.com/).
992MySQL 8.0 02/20/2019 MYSQLPUMP(1)