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