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 Note
30 mysqlpump is deprecated as of MySQL 8.0.34; expect it to be removed
31 in a future version of MySQL. You can use such MySQL programs as
32 mysqldump and MySQL Shell to perform logical backups, dump
33 databases, and similar tasks instead.
34 Tip
36 Consider using the MySQL Shell dump utilities[1], which provide
37 parallel dumping with multiple threads, file compression, and
38 progress information display, as well as cloud features such as
39 Oracle Cloud Infrastructure Object Storage streaming, and MySQL
40 HeatWave Service compatibility checks and modifications. Dumps can
41 be easily imported into a MySQL Server instance or a MySQL HeatWave
42 Service DB System using the MySQL Shell load dump utilities[2].
43 Installation instructions for MySQL Shell can be found here[3].
44 mysqlpump features include:
46 • Parallel processing of databases, and of objects within databases,
48 to speed up the dump process
49 • Better control over which databases and database objects (tables,
51 stored programs, user accounts) to dump
52 • Dumping of user accounts as account-management statements (CREATE
54 USER, GRANT) rather than as inserts into the mysql system database
55 • Capability of creating compressed output
57 • Progress indicator (the values are estimates)
59 • For dump file reloading, faster secondary index creation for InnoDB
61 tables by adding indexes after rows are inserted
62 Note
64 mysqlpump uses MySQL features introduced in MySQL 5.7, and thus
65 assumes use with MySQL 5.7 or higher.
66 mysqlpump requires at least the SELECT privilege for dumped tables,
68 SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK
69 TABLES if the --single-transaction option is not used. The SELECT
70 privilege on the mysql system database is required to dump user
71 definitions. Certain options might require other privileges as noted in
72 the option descriptions.
73 To reload a dump file, you must have the privileges required to execute
75 the statements that it contains, such as the appropriate CREATE
76 privileges for objects created by those statements.
77 Note
79 A dump made using PowerShell on Windows with output redirection
80 creates a file that has UTF-16 encoding:
81 mysqlpump [options] > dump.sql
83 However, UTF-16 is not permitted as a connection character set (see
85 Section 10.4, “Connection Character Sets and Collations”), so the
86 dump file cannot be loaded correctly. To work around this issue,
87 use the --result-file option, which creates the output in ASCII
88 format:
89 mysqlpump [options] --result-file=dump.sql
91 mysqlpump Invocation Syntax
92 By default, mysqlpump dumps all databases (with certain exceptions
94 noted in mysqlpump Restrictions). To specify this behavior explicitly,
95 use the --all-databases option:
96 mysqlpump --all-databases
98 To dump a single database, or certain tables within that database, name
100 the database on the command line, optionally followed by table names:
101 mysqlpump db_name
103 mysqlpump db_name tbl_name1 tbl_name2 ...
104 To treat all name arguments as database names, use the --databases
106 option:
107 mysqlpump --databases db_name1 db_name2 ...
109 By default, mysqlpump does not dump user account definitions, even if
111 you dump the mysql system database that contains the grant tables. To
112 dump grant table contents as logical definitions in the form of CREATE
113 USER and GRANT statements, use the --users option and suppress all
114 database dumping:
115 mysqlpump --exclude-databases=% --users
117 In the preceding command, % is a wildcard that matches all database
119 names for the --exclude-databases option.
120 mysqlpump supports several options for including or excluding
122 databases, tables, stored programs, and user definitions. See mysqlpump
123 Object Selection.
124 To reload a dump file, execute the statements that it contains. For
126 example, use the mysql client:
127 mysqlpump [options] > dump.sql
129 mysql < dump.sql
130 The following discussion provides additional mysqlpump usage examples.
132 To see a list of the options mysqlpump supports, issue the command
134 mysqlpump --help. mysqlpump Option Summary
135 mysqlpump supports the following options, which can be specified on the
137 command line or in the [mysqlpump] and [client] groups of an option
138 file. (Prior to MySQL 8.0.20, mysqlpump read the [mysql_dump] group
139 rather than [mysqlpump]. As of 8.0.20, [mysql_dump] is still accepted
140 but is deprecated.) For information about option files used by MySQL
141 programs, see Section 4.2.2.2, “Using Option Files”. mysqlpump Option
142 Descriptions
143 • --help, -? Display a help message and exit.
145 • --add-drop-database Write a DROP DATABASE statement before each
147 CREATE DATABASE statement.
148 Note
150 In MySQL 8.0, the mysql schema is considered a system schema
151 that cannot be dropped by end users. If --add-drop-database is
152 used with --all-databases or with --databases where the list of
153 schemas to be dumped includes mysql, the dump file contains a
154 DROP DATABASE `mysql` statement that causes an error when the
155 dump file is reloaded.
156 Instead, to use --add-drop-database, use --databases with a
158 list of schemas to be dumped, where the list does not include
159 mysql.
160 • --add-drop-table Write a DROP TABLE statement before each CREATE
162 TABLE statement.
163 • --add-drop-user Write a DROP USER statement before each CREATE USER
165 statement.
166 • --add-locks Surround each table dump with LOCK TABLES and UNLOCK
168 TABLES statements. This results in faster inserts when the dump
169 file is reloaded. See Section 8.2.5.1, “Optimizing INSERT
170 Statements”.
171 This option does not work with parallelism because INSERT
173 statements from different tables can be interleaved and UNLOCK
174 TABLES following the end of the inserts for one table could release
175 locks on tables for which inserts remain.
176 --add-locks and --single-transaction are mutually exclusive.
178 • --all-databases, -A Dump all databases (with certain exceptions
180 noted in mysqlpump Restrictions). This is the default behavior if
181 no other is specified explicitly.
182 --all-databases and --databases are mutually exclusive.
184 Note
186 See the --add-drop-database description for information about
187 an incompatibility of that option with --all-databases.
188 Prior to MySQL 8.0, the --routines and --events options for
189 mysqldump and mysqlpump were not required to include stored
190 routines and events when using the --all-databases option: The dump
191 included the mysql system database, and therefore also the
192 mysql.proc and mysql.event tables containing stored routine and
193 event definitions. As of MySQL 8.0, the mysql.event and mysql.proc
194 tables are not used. Definitions for the corresponding objects are
195 stored in data dictionary tables, but those tables are not dumped.
196 To include stored routines and events in a dump made using
197 --all-databases, use the --routines and --events options
198 explicitly.
199 • --bind-address=ip_address On a computer having multiple network
201 interfaces, use this option to select which interface to use for
202 connecting to the MySQL server.
203 • --character-sets-dir=path The directory where character sets are
205 installed. See Section 10.15, “Character Set Configuration”.
206 • --column-statistics Add ANALYZE TABLE statements to the output to
208 generate histogram statistics for dumped tables when the dump file
209 is reloaded. This option is disabled by default because histogram
210 generation for large tables can take a long time.
211 • --complete-insert Write complete INSERT statements that include
213 column names.
214 • --compress, -C Compress all information sent between the client and
216 the server if possible. See Section 4.2.8, “Connection Compression
217 Control”.
218 As of MySQL 8.0.18, this option is deprecated. Expect it to be
220 removed in a future version of MySQL. See the section called
221 “Configuring Legacy Connection Compression”.
222 • --compress-output=algorithm By default, mysqlpump does not compress
224 output. This option specifies output compression using the
225 specified algorithm. Permitted algorithms are LZ4 and ZLIB.
226 To uncompress compressed output, you must have an appropriate
228 utility. If the system commands lz4 and openssl zlib are not
229 available, MySQL distributions include lz4_decompress and
230 zlib_decompress utilities that can be used to decompress mysqlpump
231 output that was compressed using the --compress-output=LZ4 and
232 --compress-output=ZLIB options. For more information, see
233 lz4_decompress(1), and zlib_decompress(1).
234 • --compression-algorithms=value The permitted compression algorithms
236 for connections to the server. The available algorithms are the
237 same as for the protocol_compression_algorithms system variable.
238 The default value is uncompressed.
239 For more information, see Section 4.2.8, “Connection Compression
241 Control”.
242 This option was added in MySQL 8.0.18.
244 • --databases, -B Normally, mysqlpump treats the first name argument
246 on the command line as a database name and any following names as
247 table names. With this option, it treats all name arguments as
248 database names. CREATE DATABASE statements are included in the
249 output before each new database.
250 --all-databases and --databases are mutually exclusive.
252 Note
254 See the --add-drop-database description for information about
255 an incompatibility of that option with --databases.
256 • --debug[=debug_options], -# [debug_options] Write a debugging log.
258 A typical debug_options string is d:t:o,file_name. The default is
259 d:t:O,/tmp/mysqlpump.trace.
260 This option is available only if MySQL was built using WITH_DEBUG.
262 MySQL release binaries provided by Oracle are not built using this
263 option.
264 • --debug-check Print some debugging information when the program
266 exits.
267 This option is available only if MySQL was built using WITH_DEBUG.
269 MySQL release binaries provided by Oracle are not built using this
270 option.
271 • --debug-info, -T Print debugging information and memory and CPU
273 usage statistics when the program exits.
274 This option is available only if MySQL was built using WITH_DEBUG.
276 MySQL release binaries provided by Oracle are not built using this
277 option.
278 • --default-auth=plugin A hint about which client-side authentication
280 plugin to use. See Section 6.2.17, “Pluggable Authentication”.
281 • --default-character-set=charset_name Use charset_name as the
283 default character set. See Section 10.15, “Character Set
284 Configuration”. If no character set is specified, mysqlpump uses
285 utf8mb4.
286 • --default-parallelism=N The default number of threads for each
288 parallel processing queue. The default is 2.
289 The --parallel-schemas option also affects parallelism and can be
291 used to override the default number of threads. For more
292 information, see mysqlpump Parallel Processing.
293 With --default-parallelism=0 and no --parallel-schemas options,
295 mysqlpump runs as a single-threaded process and creates no queues.
296 With parallelism enabled, it is possible for output from different
298 databases to be interleaved.
299 • --defaults-extra-file=file_name Read this option file after the
301 global option file but (on Unix) before the user option file. If
302 the file does not exist or is otherwise inaccessible, an error
303 occurs. If file_name is not an absolute path name, it is
304 interpreted relative to the current directory.
305 For additional information about this and other option-file
307 options, see Section 4.2.2.3, “Command-Line Options that Affect
308 Option-File Handling”.
309 • --defaults-file=file_name Use only the given option file. If the
311 file does not exist or is otherwise inaccessible, an error occurs.
312 If file_name is not an absolute path name, it is interpreted
313 relative to the current directory.
314 Exception: Even with --defaults-file, client programs read
316 .mylogin.cnf.
317 For additional information about this and other option-file
319 options, see Section 4.2.2.3, “Command-Line Options that Affect
320 Option-File Handling”.
321 • --defaults-group-suffix=str Read not only the usual option groups,
323 but also groups with the usual names and a suffix of str. For
324 example, mysqlpump normally reads the [client] and [mysqlpump]
325 groups. If this option is given as --defaults-group-suffix=_other,
326 mysqlpump also reads the [client_other] and [mysqlpump_other]
327 groups.
328 For additional information about this and other option-file
330 options, see Section 4.2.2.3, “Command-Line Options that Affect
331 Option-File Handling”.
332 • --defer-table-indexes In the dump output, defer index creation for
334 each table until after its rows have been loaded. This works for
335 all storage engines, but for InnoDB applies only for secondary
336 indexes.
337 This option is enabled by default; use --skip-defer-table-indexes
339 to disable it.
340 • --events Include Event Scheduler events for the dumped databases in
342 the 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 Do not dump the databases in db_list,
351 which is a list of one or more comma-separated database names.
352 Multiple instances of this option are additive. For more
353 information, see mysqlpump Object Selection.
354 • --exclude-events=event_list Do not dump the databases in
356 event_list, which is a list of one or more comma-separated event
357 names. Multiple instances of this option are additive. For more
358 information, see mysqlpump Object Selection.
359 • --exclude-routines=routine_list Do not dump the events in
361 routine_list, which is a list of one or more comma-separated
362 routine (stored procedure or function) names. Multiple instances of
363 this option are additive. For more information, see mysqlpump
364 Object Selection.
365 • --exclude-tables=table_list Do not dump the tables in table_list,
367 which is a list of one or more comma-separated table names.
368 Multiple instances of this option are additive. For more
369 information, see mysqlpump Object Selection.
370 • --exclude-triggers=trigger_list Do not dump the triggers in
372 trigger_list, which is a list of one or more comma-separated
373 trigger names. Multiple instances of this option are additive. For
374 more information, see mysqlpump Object Selection.
375 • --exclude-users=user_list Do not dump the user accounts in
377 user_list, which is a list of one or more comma-separated account
378 names. Multiple instances of this option are additive. For more
379 information, see mysqlpump Object Selection.
380 • --extended-insert=N Write INSERT statements using multiple-row
382 syntax that includes several VALUES lists. This results in a
383 smaller dump file and speeds up inserts when the file is reloaded.
384 The option value indicates the number of rows to include in each
386 INSERT statement. The default is 250. A value of 1 produces one
387 INSERT statement per table row.
388 • --get-server-public-key Request from the server the public key
390 required for RSA key pair-based password exchange. This option
391 applies to clients that authenticate with the caching_sha2_password
392 authentication plugin. For that plugin, the server does not send
393 the public key unless requested. This option is ignored for
394 accounts that do not authenticate with that plugin. It is also
395 ignored if RSA-based password exchange is not used, as is the case
396 when the client connects to the server using a secure connection.
397 If --server-public-key-path=file_name is given and specifies a
399 valid public key file, it takes precedence over
400 --get-server-public-key.
401 For information about the caching_sha2_password plugin, see
403 Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
404 • --hex-blob Dump binary columns using hexadecimal notation (for
406 example, 'abc' becomes 0x616263). The affected data types are
407 BINARY, VARBINARY, BLOB types, BIT, all spatial data types, and
408 other non-binary data types when used with the binary character
409 set.
410 • --host=host_name, -h host_name Dump data from the MySQL server on
412 the given host.
413 • --include-databases=db_list Dump the databases in db_list, which is
415 a list of one or more comma-separated database names. The dump
416 includes all objects in the named databases. Multiple instances of
417 this option are additive. For more information, see mysqlpump
418 Object Selection.
419 • --include-events=event_list Dump the events in event_list, which is
421 a list of one or more comma-separated event names. Multiple
422 instances of this option are additive. For more information, see
423 mysqlpump Object Selection.
424 • --include-routines=routine_list Dump the routines in routine_list,
426 which is a list of one or more comma-separated routine (stored
427 procedure or function) names. Multiple instances of this option are
428 additive. For more information, see mysqlpump Object Selection.
429 • --include-tables=table_list Dump the tables in table_list, which is
431 a list of one or more comma-separated table names. Multiple
432 instances of this option are additive. For more information, see
433 mysqlpump Object Selection.
434 • --include-triggers=trigger_list Dump the triggers in trigger_list,
436 which is a list of one or more comma-separated trigger names.
437 Multiple instances of this option are additive. For more
438 information, see mysqlpump Object Selection.
439 • --include-users=user_list Dump the user accounts in user_list,
441 which is a list of one or more comma-separated user names. Multiple
442 instances of this option are additive. For more information, see
443 mysqlpump Object Selection.
444 • --insert-ignore Write INSERT IGNORE statements rather than INSERT
446 statements.
447 • --log-error-file=file_name Log warnings and errors by appending
449 them to the named file. If this option is not given, mysqlpump
450 writes warnings and errors to the standard error output.
451 • --login-path=name Read options from the named login path in the
453 .mylogin.cnf login path file. A “login path” is an option group
454 containing options that specify which MySQL server to connect to
455 and which account to authenticate as. To create or modify a login
456 path file, use the mysql_config_editor utility. See
457 mysql_config_editor(1).
458 For additional information about this and other option-file
460 options, see Section 4.2.2.3, “Command-Line Options that Affect
461 Option-File Handling”.
462 • --max-allowed-packet=N The maximum size of the buffer for
464 client/server communication. The default is 24MB, the maximum is
465 1GB.
466 • --net-buffer-length=N The initial size of the buffer for
468 client/server communication. When creating multiple-row INSERT
469 statements (as with the --extended-insert option), mysqlpump
470 creates rows up to N bytes long. If you use this option to increase
471 the value, ensure that the MySQL server net_buffer_length system
472 variable has a value at least this large.
473 • --no-create-db Suppress any CREATE DATABASE statements that might
475 otherwise be included in the output.
476 • --no-create-info, -t Do not write CREATE TABLE statements that
478 create each dumped table.
479 • --no-defaults Do not read any option files. If program startup
481 fails due to reading unknown options from an option file,
482 --no-defaults can be used to prevent them from being read.
483 The exception is that the .mylogin.cnf file is read in all cases,
485 if it exists. This permits passwords to be specified in a safer way
486 than on the command line even when --no-defaults is used. To create
487 .mylogin.cnf, use the mysql_config_editor utility. See
488 mysql_config_editor(1).
489 For additional information about this and other option-file
491 options, see Section 4.2.2.3, “Command-Line Options that Affect
492 Option-File Handling”.
493 • --parallel-schemas=[N:]db_list Create a queue for processing the
495 databases in db_list, which is a list of one or more
496 comma-separated database names. If N is given, the queue uses N
497 threads. If N is not given, the --default-parallelism option
498 determines the number of queue threads.
499 Multiple instances of this option create multiple queues.
501 mysqlpump also creates a default queue to use for databases not
502 named in any --parallel-schemas option, and for dumping user
503 definitions if command options select them. For more information,
504 see mysqlpump Parallel Processing.
505 • --password[=password], -p[password] The password of the MySQL
507 account used for connecting to the server. The password value is
508 optional. If not given, mysqlpump prompts for one. If given, there
509 must be no space between --password= or -p and the password
510 following it. If no password option is specified, the default is to
511 send no password.
512 Specifying a password on the command line should be considered
514 insecure. To avoid giving the password on the command line, use an
515 option file. See Section 6.1.2.1, “End-User Guidelines for Password
516 Security”.
517 To explicitly specify that there is no password and that mysqlpump
519 should not prompt for one, use the --skip-password option.
520 • --password1[=pass_val] The password for multifactor authentication
522 factor 1 of the MySQL account used for connecting to the server.
523 The password value is optional. If not given, mysqlpump prompts for
524 one. If given, there must be no space between --password1= and the
525 password following it. If no password option is specified, the
526 default is to send no password.
527 Specifying a password on the command line should be considered
529 insecure. To avoid giving the password on the command line, use an
530 option file. See Section 6.1.2.1, “End-User Guidelines for Password
531 Security”.
532 To explicitly specify that there is no password and that mysqlpump
534 should not prompt for one, use the --skip-password1 option.
535 --password1 and --password are synonymous, as are --skip-password1
537 and --skip-password.
538 • --password2[=pass_val] The password for multifactor authentication
540 factor 2 of the MySQL account used for connecting to the server.
541 The semantics of this option are similar to the semantics for
542 --password1; see the description of that option for details.
543 • --password3[=pass_val] The password for multifactor authentication
545 factor 3 of the MySQL account used for connecting to the server.
546 The semantics of this option are similar to the semantics for
547 --password1; see the description of that option for details.
548 • --plugin-dir=dir_name The directory in which to look for plugins.
550 Specify this option if the --default-auth option is used to specify
551 an authentication plugin but mysqlpump does not find it. See
552 Section 6.2.17, “Pluggable Authentication”.
553 • --port=port_num, -P port_num For TCP/IP connections, the port
555 number to use.
556 • --print-defaults Print the program name and all options that it
558 gets from option files.
559 For additional information about this and other option-file
561 options, see Section 4.2.2.3, “Command-Line Options that Affect
562 Option-File Handling”.
563 • --protocol={TCP|SOCKET|PIPE|MEMORY} The transport protocol to use
565 for connecting to the server. It is useful when the other
566 connection parameters normally result in use of a protocol other
567 than the one you want. For details on the permissible values, see
568 Section 4.2.7, “Connection Transport Protocols”.
569 • --replace Write REPLACE statements rather than INSERT statements.
571 • --result-file=file_name Direct output to the named file. The result
573 file is created and its previous contents overwritten, even if an
574 error occurs while generating the dump.
575 This option should be used on Windows to prevent newline \n
577 characters from being converted to \r\n carriage return/newline
578 sequences.
579 • --routines Include stored routines (procedures and functions) for
581 the dumped databases in the output. This option requires the global
582 SELECT privilege.
583 The output generated by using --routines contains CREATE PROCEDURE
585 and CREATE FUNCTION statements to create the routines.
586 This option is enabled by default; use --skip-routines to disable
588 it.
589 • --server-public-key-path=file_name The path name to a file in PEM
591 format containing a client-side copy of the public key required by
592 the server for RSA key pair-based password exchange. This option
593 applies to clients that authenticate with the sha256_password or
594 caching_sha2_password authentication plugin. This option is ignored
595 for accounts that do not authenticate with one of those plugins. It
596 is also ignored if RSA-based password exchange is not used, as is
597 the case when the client connects to the server using a secure
598 connection.
599 If --server-public-key-path=file_name is given and specifies a
601 valid public key file, it takes precedence over
602 --get-server-public-key.
603 For sha256_password, this option applies only if MySQL was built
605 using OpenSSL.
606 For information about the sha256_password and caching_sha2_password
608 plugins, see Section 6.4.1.3, “SHA-256 Pluggable Authentication”,
609 and Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
610 • --set-charset Write SET NAMES default_character_set to the output.
612 This option is enabled by default. To disable it and suppress the
614 SET NAMES statement, use --skip-set-charset.
615 • --set-gtid-purged=value This option enables control over global
617 transaction ID (GTID) information written to the dump file, by
618 indicating whether to add a SET @@GLOBAL.gtid_purged statement to
619 the output. This option may also cause a statement to be written to
620 the output that disables binary logging while the dump file is
621 being reloaded.
622 The following table shows the permitted option values. The default
624 value is AUTO.
625 ┌──────┬────────────────────────────┐
627 │Value │ Meaning │
628 ├──────┼────────────────────────────┤
629 │OFF │ Add no SET statement to │
630 │ │ the output. │
631 ├──────┼────────────────────────────┤
632 │ON │ Add a SET statement to the │
633 │ │ output. An error occurs if │
634 │ │ GTIDs │
635 │ │ are not enabled on the │
636 │ │ server. │
637 ├──────┼────────────────────────────┤
638 │AUTO │ Add a SET statement to the │
639 │ │ output if GTIDs are │
640 │ │ enabled │
641 │ │ on the server. │
642 └──────┴────────────────────────────┘
643 The --set-gtid-purged option has the following effect on binary
644 logging when the dump file is reloaded:
645 • --set-gtid-purged=OFF: SET @@SESSION.SQL_LOG_BIN=0; is not
647 added to the output.
648 • --set-gtid-purged=ON: SET @@SESSION.SQL_LOG_BIN=0; is added to
650 the output.
651 • --set-gtid-purged=AUTO: SET @@SESSION.SQL_LOG_BIN=0; is added
653 to the output if GTIDs are enabled on the server you are
654 backing up (that is, if AUTO evaluates to ON).
655 • --single-transaction This option sets the transaction isolation
657 mode to REPEATABLE READ and sends a START TRANSACTION SQL statement
658 to the server before dumping data. It is useful only with
659 transactional tables such as InnoDB, because then it dumps the
660 consistent state of the database at the time when START TRANSACTION
661 was issued without blocking any applications.
662 When using this option, you should keep in mind that only InnoDB
664 tables are dumped in a consistent state. For example, any MyISAM or
665 MEMORY tables dumped while using this option may still change
666 state.
667 While a --single-transaction dump is in process, to ensure a valid
669 dump file (correct table contents and binary log coordinates), no
670 other connection should use the following statements: ALTER TABLE,
671 CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A
672 consistent read is not isolated from those statements, so use of
673 them on a table to be dumped can cause the SELECT that is performed
674 by mysqlpump to retrieve the table contents to obtain incorrect
675 contents or fail.
676 --add-locks and --single-transaction are mutually exclusive.
678 • --skip-definer Omit DEFINER and SQL SECURITY clauses from the
680 CREATE statements for views and stored programs. The dump file,
681 when reloaded, creates objects that use the default DEFINER and SQL
682 SECURITY values. See Section 25.6, “Stored Object Access Control”.
683 • --skip-dump-rows, -d Do not dump table rows.
685 • --skip-generated-invisible-primary-key This option is available
687 beginning with MySQL 8.0.30, and causes generated invisible primary
688 keys (GIPKs) to be excluded from the dump. See Section 13.1.20.11,
689 “Generated Invisible Primary Keys”, for more information about
690 GIPKs and GIPK mode.
691 • --socket=path, -S path For connections to localhost, the Unix
693 socket file to use, or, on Windows, the name of the named pipe to
694 use.
695 On Windows, this option applies only if the server was started with
697 the named_pipe system variable enabled to support named-pipe
698 connections. In addition, the user making the connection must be a
699 member of the Windows group specified by the
700 named_pipe_full_access_group system variable.
701 • --ssl* Options that begin with --ssl specify whether to connect to
703 the server using encryption and indicate where to find SSL keys and
704 certificates. See the section called “Command Options for Encrypted
705 Connections”.
706 • --ssl-fips-mode={OFF|ON|STRICT} Controls whether to enable FIPS
708 mode on the client side. The --ssl-fips-mode option differs from
709 other --ssl-xxx options in that it is not used to establish
710 encrypted connections, but rather to affect which cryptographic
711 operations to permit. See Section 6.8, “FIPS Support”.
712 These --ssl-fips-mode values are permitted:
714 • OFF: Disable FIPS mode.
716 • ON: Enable FIPS mode.
718 • STRICT: Enable “strict” FIPS mode.
720 Note
723 If the OpenSSL FIPS Object Module is not available, the only
724 permitted value for --ssl-fips-mode is OFF. In this case,
725 setting --ssl-fips-mode to ON or STRICT causes the client to
726 produce a warning at startup and to operate in non-FIPS mode.
727 As of MySQL 8.0.34, this option is deprecated. Expect it to be
728 removed in a future version of MySQL.
729 • --tls-ciphersuites=ciphersuite_list The permissible ciphersuites
731 for encrypted connections that use TLSv1.3. The value is a list of
732 one or more colon-separated ciphersuite names. The ciphersuites
733 that can be named for this option depend on the SSL library used to
734 compile MySQL. For details, see Section 6.3.2, “Encrypted
735 Connection TLS Protocols and Ciphers”.
736 This option was added in MySQL 8.0.16.
738 • --tls-version=protocol_list The permissible TLS protocols for
740 encrypted connections. The value is a list of one or more
741 comma-separated protocol names. The protocols that can be named for
742 this option depend on the SSL library used to compile MySQL. For
743 details, see Section 6.3.2, “Encrypted Connection TLS Protocols and
744 Ciphers”.
745 • --triggers Include triggers for each dumped table in the output.
747 This option is enabled by default; use --skip-triggers to disable
749 it.
750 • --tz-utc This option enables TIMESTAMP columns to be dumped and
752 reloaded between servers in different time zones. mysqlpump sets
753 its connection time zone to UTC and adds SET TIME_ZONE='+00:00' to
754 the dump file. Without this option, TIMESTAMP columns are dumped
755 and reloaded in the time zones local to the source and destination
756 servers, which can cause the values to change if the servers are in
757 different time zones. --tz-utc also protects against changes due
758 to daylight saving time.
759 This option is enabled by default; use --skip-tz-utc to disable it.
761 • --user=user_name, -u user_name The user name of the MySQL account
763 to use for connecting to the server.
764 If you are using the Rewriter plugin with MySQL 8.0.31 or later,
766 you should grant this user the SKIP_QUERY_REWRITE privilege.
767 • --users Dump user accounts as logical definitions in the form of
769 CREATE USER and GRANT statements.
770 User definitions are stored in the grant tables in the mysql system
772 database. By default, mysqlpump does not include the grant tables
773 in mysql database dumps. To dump the contents of the grant tables
774 as logical definitions, use the --users option and suppress all
775 database dumping:
776 mysqlpump --exclude-databases=% --users
778 • --version, -V Display version information and exit.
780 • --watch-progress Periodically display a progress indicator that
782 provides information about the completed and total number of
783 tables, rows, and other objects.
784 This option is enabled by default; use --skip-watch-progress to
786 disable it.
787 • --zstd-compression-level=level The compression level to use for
789 connections to the server that use the zstd compression algorithm.
790 The permitted levels are from 1 to 22, with larger values
791 indicating increasing levels of compression. The default zstd
792 compression level is 3. The compression level setting has no effect
793 on connections that do not use zstd compression.
794 For more information, see Section 4.2.8, “Connection Compression
796 Control”.
797 This option was added in MySQL 8.0.18.
799 mysqlpump Object Selection
800 mysqlpump has a set of inclusion and exclusion options that enable
802 filtering of several object types and provide flexible control over
803 which objects to dump:
804 • --include-databases and --exclude-databases apply to databases and
806 all objects within them.
807 • --include-tables and --exclude-tables apply to tables. These
809 options also affect triggers associated with tables unless the
810 trigger-specific options are given.
811 • --include-triggers and --exclude-triggers apply to triggers.
813 • --include-routines and --exclude-routines apply to stored
815 procedures and functions. If a routine option matches a stored
816 procedure name, it also matches a stored function of the same name.
817 • --include-events and --exclude-events apply to Event Scheduler
819 events.
820 • --include-users and --exclude-users apply to user accounts.
822 Any inclusion or exclusion option may be given multiple times. The
824 effect is additive. Order of these options does not matter.
825 The value of each inclusion and exclusion option is a list of
827 comma-separated names of the appropriate object type. For example:
828 --exclude-databases=test,world
830 --include-tables=customer,invoice
831 Wildcard characters are permitted in the object names:
833 • % matches any sequence of zero or more characters.
835 • _ matches any single character.
837 For example, --include-tables=t%,__tmp matches all table names that
839 begin with t and all five-character table names that end with tmp.
840 For users, a name specified without a host part is interpreted with an
842 implied host of %. For example, u1 and u1@% are equivalent. This is the
843 same equivalence that applies in MySQL generally (see Section 6.2.4,
844 “Specifying Account Names”).
845 Inclusion and exclusion options interact as follows:
847 • By default, with no inclusion or exclusion options, mysqlpump dumps
849 all databases (with certain exceptions noted in mysqlpump
850 Restrictions).
851 • If inclusion options are given in the absence of exclusion options,
853 only the objects named as included are dumped.
854 • If exclusion options are given in the absence of inclusion options,
856 all objects are dumped except those named as excluded.
857 • If inclusion and exclusion options are given, all objects named as
859 excluded and not named as included are not dumped. All other
860 objects are dumped.
861 If multiple databases are being dumped, it is possible to name tables,
863 triggers, and routines in a specific database by qualifying the object
864 names with the database name. The following command dumps databases db1
865 and db2, but excludes tables db1.t1 and db2.t2:
866 mysqlpump --include-databases=db1,db2 --exclude-tables=db1.t1,db2.t2
868 The following options provide alternative ways to specify which
870 databases to dump:
871 • The --all-databases option dumps all databases (with certain
873 exceptions noted in mysqlpump Restrictions). It is equivalent to
874 specifying no object options at all (the default mysqlpump action
875 is to dump everything).
876 --include-databases=% is similar to --all-databases, but selects
878 all databases for dumping, even those that are exceptions for
879 --all-databases.
880 • The --databases option causes mysqlpump to treat all name arguments
882 as names of databases to dump. It is equivalent to an
883 --include-databases option that names the same databases.
884 mysqlpump Parallel Processing
885 mysqlpump can use parallelism to achieve concurrent processing. You can
887 select concurrency between databases (to dump multiple databases
888 simultaneously) and within databases (to dump multiple objects from a
889 given database simultaneously).
890 By default, mysqlpump sets up one queue with two threads. You can
892 create additional queues and control the number of threads assigned to
893 each one, including the default queue:
894 • --default-parallelism=N specifies the default number of threads
896 used for each queue. In the absence of this option, N is 2.
897 The default queue always uses the default number of threads.
899 Additional queues use the default number of threads unless you
900 specify otherwise.
901 • --parallel-schemas=[N:]db_list sets up a processing queue for
903 dumping the databases named in db_list and optionally specifies how
904 many threads the queue uses. db_list is a list of comma-separated
905 database names. If the option argument begins with N:, the queue
906 uses N threads. Otherwise, the --default-parallelism option
907 determines the number of queue threads.
908 Multiple instances of the --parallel-schemas option create multiple
910 queues.
911 Names in the database list are permitted to contain the same % and
913 _ wildcard characters supported for filtering options (see
914 mysqlpump Object Selection).
915 mysqlpump uses the default queue for processing any databases not named
917 explicitly with a --parallel-schemas option, and for dumping user
918 definitions if command options select them.
919 In general, with multiple queues, mysqlpump uses parallelism between
921 the sets of databases processed by the queues, to dump multiple
922 databases simultaneously. For a queue that uses multiple threads,
923 mysqlpump uses parallelism within databases, to dump multiple objects
924 from a given database simultaneously. Exceptions can occur; for
925 example, mysqlpump may block queues while it obtains from the server
926 lists of objects in databases.
927 With parallelism enabled, it is possible for output from different
929 databases to be interleaved. For example, INSERT statements from
930 multiple tables dumped in parallel can be interleaved; the statements
931 are not written in any particular order. This does not affect reloading
932 because output statements qualify object names with database names or
933 are preceded by USE statements as required.
934 The granularity for parallelism is a single database object. For
936 example, a single table cannot be dumped in parallel using multiple
937 threads.
938 Examples:
940 mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
942 mysqlpump sets up a queue to process db1 and db2, another queue to
944 process db3, and a default queue to process all other databases. All
945 queues use two threads.
946 mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
948 --default-parallelism=4
949 This is the same as the previous example except that all queues use
951 four threads.
952 mysqlpump --parallel-schemas=5:db1,db2 --parallel-schemas=3:db3
954 The queue for db1 and db2 uses five threads, the queue for db3 uses
956 three threads, and the default queue uses the default of two threads.
957 As a special case, with --default-parallelism=0 and no
959 --parallel-schemas options, mysqlpump runs as a single-threaded process
960 and creates no queues. mysqlpump Restrictions
961 mysqlpump does not dump the performance_schema, ndbinfo, or sys schema
963 by default. To dump any of these, name them explicitly on the command
964 line. You can also name them with the --databases or
965 --include-databases option.
966 mysqlpump does not dump the INFORMATION_SCHEMA schema.
968 mysqlpump does not dump InnoDB CREATE TABLESPACE statements.
970 mysqlpump dumps user accounts in logical form using CREATE USER and
972 GRANT statements (for example, when you use the --include-users or
973 --users option). For this reason, dumps of the mysql system database do
974 not by default include the grant tables that contain user definitions:
975 user, db, tables_priv, columns_priv, procs_priv, or proxies_priv. To
976 dump any of the grant tables, name the mysql database followed by the
977 table names:
978 mysqlpump mysql user db ...
980
982 Copyright © 1997, 2023, Oracle and/or its affiliates.
983 This documentation is free software; you can redistribute it and/or
985 modify it only under the terms of the GNU General Public License as
986 published by the Free Software Foundation; version 2 of the License.
987 This documentation is distributed in the hope that it will be useful,
989 but WITHOUT ANY WARRANTY; without even the implied warranty of
990 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
991 General Public License for more details.
992 You should have received a copy of the GNU General Public License along
994 with the program; if not, write to the Free Software Foundation, Inc.,
995 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
996 http://www.gnu.org/licenses/.
997
1000 1. MySQL Shell dump utilities
1001 https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html
1002 2. MySQL Shell load dump utilities
1004 https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html
1005 3. here
1007 https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html
1008
1010 For more information, please refer to the MySQL Reference Manual, which
1011 may already be installed locally and which is also available online at
1012 http://dev.mysql.com/doc/.
1013
1015 Oracle Corporation (http://dev.mysql.com/).
1016MySQL 8.0 08/31/2023 MYSQLPUMP(1)