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