1MYSQLPUMP(1)                 MySQL Database System                MYSQLPUMP(1)
2
3
4

NAME

6       mysqlpump - a database backup program
7

SYNOPSIS

9       mysqlpump [options] [db_name [tbl_name ...]]
10

DESCRIPTION

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           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
39       mysqlpump features include:
40
41       ·   Parallel processing of databases, and of objects within databases,
42           to speed up the dump process
43
44       ·   Better control over which databases and database objects (tables,
45           stored programs, user accounts) to dump
46
47       ·   Dumping of user accounts as account-management statements (CREATE
48           USER, GRANT) rather than as inserts into the mysql system database
49
50       ·   Capability of creating compressed output
51
52       ·   Progress indicator (the values are estimates)
53
54       ·   For dump file reloading, faster secondary index creation for InnoDB
55           tables by adding indexes after rows are inserted
56
57           Note
58           mysqlpump uses MySQL features introduced in MySQL 5.7, and thus
59           assumes use with MySQL 5.7 or higher.
60
61       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
68       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
72           Note
73           A dump made using PowerShell on Windows with output redirection
74           creates a file that has UTF-16 encoding:
75
76               shell> mysqlpump [options] > dump.sql
77
78           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
84               shell> mysqlpump [options] --result-file=dump.sql
85       mysqlpump Invocation Syntax
86
87       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
91           shell> mysqlpump --all-databases
92
93       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
96           shell> mysqlpump db_name
97           shell> mysqlpump db_name tbl_name1 tbl_name2 ...
98
99       To treat all name arguments as database names, use the --databases
100       option:
101
102           shell> mysqlpump --databases db_name1 db_name2 ...
103
104       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
110           shell> mysqlpump --exclude-databases=% --users
111
112       In the preceding command, % is a wildcard that matches all database
113       names for the --exclude-databases option.
114
115       mysqlpump supports several options for including or excluding
116       databases, tables, stored programs, and user definitions. See mysqlpump
117       Object Selection.
118
119       To reload a dump file, execute the statements that it contains. For
120       example, use the mysql client:
121
122           shell> mysqlpump [options] > dump.sql
123           shell> mysql < dump.sql
124
125       The following discussion provides additional mysqlpump usage examples.
126
127       To see a list of the options mysqlpump supports, issue the command
128       mysqlpump --help.  mysqlpump Option Summary
129
130       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
138       ·   --help, -?  Display a help message and exit.
139
140       ·   --add-drop-database Write a DROP DATABASE statement before each
141           CREATE DATABASE statement.
142
143               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
151               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
155       ·   --add-drop-table Write a DROP TABLE statement before each CREATE
156           TABLE statement.
157
158       ·   --add-drop-user Write a DROP USER statement before each CREATE USER
159           statement.
160
161       ·   --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
166           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
171           --add-locks and --single-transaction are mutually exclusive.
172
173       ·   --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
177           --all-databases and --databases are mutually exclusive.
178
179               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
194       ·   --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
198       ·   --character-sets-dir=path The directory where character sets are
199           installed. See Section 10.15, “Character Set Configuration”.
200
201       ·   --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
206       ·   --complete-insert Write complete INSERT statements that include
207           column names.
208
209       ·   --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
213           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
217       ·   --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
221           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
229       ·   --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
234           For more information, see Section 4.2.8, “Connection Compression
235           Control”.
236
237           This option was added in MySQL 8.0.18.
238
239       ·   --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
245           --all-databases and --databases are mutually exclusive.
246
247               Note
248               See the --add-drop-database description for information about
249               an incompatibility of that option with --databases.
250
251       ·   --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
255           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
259       ·   --debug-check Print some debugging information when the program
260           exits.
261
262           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
266       ·   --debug-info, -T Print debugging information and memory and CPU
267           usage statistics when the program exits.
268
269           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
273       ·   --default-auth=plugin A hint about which client-side authentication
274           plugin to use. See Section 6.2.17, “Pluggable Authentication”.
275
276       ·   --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           utf8.
280
281       ·   --default-parallelism=N The default number of threads for each
282           parallel processing queue. The default is 2.
283
284           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
288           With --default-parallelism=0 and no --parallel-schemas options,
289           mysqlpump runs as a single-threaded process and creates no queues.
290
291           With parallelism enabled, it is possible for output from different
292           databases to be interleaved.
293
294       ·   --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.  file_name is interpreted relative to the current directory
298           if given as a relative path name rather than a full path name.
299
300           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
304       ·   --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           file_name is interpreted relative to the current directory if given
307           as a relative path name rather than a full path name.
308
309           Exception: Even with --defaults-file, client programs read
310           .mylogin.cnf.
311
312           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
316       ·   --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 the --defaults-group-suffix=_other option is given,
320           mysqlpump also reads the [client_other] and [mysqlpump_other]
321           groups.
322
323           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
327       ·   --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
332           This option is enabled by default; use --skip-defer-table-indexes
333           to disable it.
334
335       ·   --events Include Event Scheduler events for the dumped databases in
336           the output. Event dumping requires the EVENT privileges for those
337           databases.
338
339           The output generated by using --events contains CREATE EVENT
340           statements to create the events.
341
342           This option is enabled by default; use --skip-events to disable it.
343
344       ·   --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
349       ·   --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
354       ·   --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
360       ·   --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
365       ·   --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
370       ·   --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
375       ·   --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
379           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
383       ·   --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
392           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
396           For information about the caching_sha2_password plugin, see
397           Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
398
399       ·   --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
405       ·   --host=host_name, -h host_name Dump data from the MySQL server on
406           the given host.
407
408       ·   --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
414       ·   --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
419       ·   --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
424       ·   --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
429       ·   --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
434       ·   --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
439       ·   --insert-ignore Write INSERT IGNORE statements rather than INSERT
440           statements.
441
442       ·   --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
446       ·   --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
453           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
457       ·   --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
461       ·   --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
468       ·   --no-create-db Suppress any CREATE DATABASE statements that might
469           otherwise be included in the output.
470
471       ·   --no-create-info, -t Do not write CREATE TABLE statements that
472           create each dumped table.
473
474       ·   --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
478           The exception is that the .mylogin.cnf file, if it exists, is read
479           in all cases. This permits passwords to be specified in a safer way
480           than on the command line even when --no-defaults is used.
481           (.mylogin.cnf is created by the mysql_config_editor utility. See
482           mysql_config_editor(1).)
483
484           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
488       ·   --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
494           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
500       ·   --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
507           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
512           To explicitly specify that there is no password and that mysqlpump
513           should not prompt for one, use the --skip-password option.
514
515       ·   --plugin-dir=dir_name The directory in which to look for plugins.
516           Specify this option if the --default-auth option is used to specify
517           an authentication plugin but mysqlpump does not find it. See
518           Section 6.2.17, “Pluggable Authentication”.
519
520       ·   --port=port_num, -P port_num For TCP/IP connections, the port
521           number to use.
522
523       ·   --print-defaults Print the program name and all options that it
524           gets from option files.
525
526           For additional information about this and other option-file
527           options, see Section 4.2.2.3, “Command-Line Options that Affect
528           Option-File Handling”.
529
530       ·   --protocol={TCP|SOCKET|PIPE|MEMORY} The transport protocol to use
531           for connecting to the server. It is useful when the other
532           connection parameters normally result in use of a protocol other
533           than the one you want. For details on the permissible values, see
534           Section 4.2.7, “Connection Transport Protocols”.
535
536       ·   --replace Write REPLACE statements rather than INSERT statements.
537
538       ·   --result-file=file_name Direct output to the named file. The result
539           file is created and its previous contents overwritten, even if an
540           error occurs while generating the dump.
541
542           This option should be used on Windows to prevent newline \n
543           characters from being converted to \r\n carriage return/newline
544           sequences.
545
546       ·   --routines Include stored routines (procedures and functions) for
547           the dumped databases in the output. This option requires the global
548           SELECT privilege.
549
550           The output generated by using --routines contains CREATE PROCEDURE
551           and CREATE FUNCTION statements to create the routines.
552
553           This option is enabled by default; use --skip-routines to disable
554           it.
555
556       ·   --server-public-key-path=file_name The path name to a file in PEM
557           format containing a client-side copy of the public key required by
558           the server for RSA key pair-based password exchange. This option
559           applies to clients that authenticate with the sha256_password or
560           caching_sha2_password authentication plugin. This option is ignored
561           for accounts that do not authenticate with one of those plugins. It
562           is also ignored if RSA-based password exchange is not used, as is
563           the case when the client connects to the server using a secure
564           connection.
565
566           If --server-public-key-path=file_name is given and specifies a
567           valid public key file, it takes precedence over
568           --get-server-public-key.
569
570           For sha256_password, this option applies only if MySQL was built
571           using OpenSSL.
572
573           For information about the sha256_password and caching_sha2_password
574           plugins, see Section 6.4.1.3, “SHA-256 Pluggable Authentication”,
575           and Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
576
577       ·   --set-charset Write SET NAMES default_character_set to the output.
578
579           This option is enabled by default. To disable it and suppress the
580           SET NAMES statement, use --skip-set-charset.
581
582       ·   --set-gtid-purged=value This option enables control over global
583           transaction ID (GTID) information written to the dump file, by
584           indicating whether to add a SET @@GLOBAL.gtid_purged statement to
585           the output. This option may also cause a statement to be written to
586           the output that disables binary logging while the dump file is
587           being reloaded.
588
589           The following table shows the permitted option values. The default
590           value is AUTO.
591
592           ┌──────┬────────────────────────────┐
593Value Meaning                    
594           ├──────┼────────────────────────────┤
595           │OFF   │ Add no SET statement to    │
596           │      │ the output.                │
597           ├──────┼────────────────────────────┤
598           │ON    │ Add a SET statement to the │
599           │      │ output. An error occurs if │
600           │      │                   GTIDs    │
601           │      │ are not enabled on the     │
602           │      │ server.                    │
603           ├──────┼────────────────────────────┤
604           │AUTO  │ Add a SET statement to the │
605           │      │ output if GTIDs are        │
606           │      │                   enabled  │
607           │      │ on the server.             │
608           └──────┴────────────────────────────┘
609           The --set-gtid-purged option has the following effect on binary
610           logging when the dump file is reloaded:
611
612           ·   --set-gtid-purged=OFF: SET @@SESSION.SQL_LOG_BIN=0; is not
613               added to the output.
614
615           ·   --set-gtid-purged=ON: SET @@SESSION.SQL_LOG_BIN=0; is added to
616               the output.
617
618           ·   --set-gtid-purged=AUTO: SET @@SESSION.SQL_LOG_BIN=0; is added
619               to the output if GTIDs are enabled on the server you are
620               backing up (that is, if AUTO evaluates to ON).
621
622       ·   --single-transaction This option sets the transaction isolation
623           mode to REPEATABLE READ and sends a START TRANSACTION SQL statement
624           to the server before dumping data. It is useful only with
625           transactional tables such as InnoDB, because then it dumps the
626           consistent state of the database at the time when START TRANSACTION
627           was issued without blocking any applications.
628
629           When using this option, you should keep in mind that only InnoDB
630           tables are dumped in a consistent state. For example, any MyISAM or
631           MEMORY tables dumped while using this option may still change
632           state.
633
634           While a --single-transaction dump is in process, to ensure a valid
635           dump file (correct table contents and binary log coordinates), no
636           other connection should use the following statements: ALTER TABLE,
637           CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A
638           consistent read is not isolated from those statements, so use of
639           them on a table to be dumped can cause the SELECT that is performed
640           by mysqlpump to retrieve the table contents to obtain incorrect
641           contents or fail.
642
643           --add-locks and --single-transaction are mutually exclusive.
644
645       ·   --skip-definer Omit DEFINER and SQL SECURITY clauses from the
646           CREATE statements for views and stored programs. The dump file,
647           when reloaded, creates objects that use the default DEFINER and SQL
648           SECURITY values. See Section 25.6, “Stored Object Access Control”.
649
650       ·   --skip-dump-rows, -d Do not dump table rows.
651
652       ·   --socket=path, -S path For connections to localhost, the Unix
653           socket file to use, or, on Windows, the name of the named pipe to
654           use.
655
656           On Windows, this option applies only if the server was started with
657           the named_pipe system variable enabled to support named-pipe
658           connections. In addition, the user making the connection must be a
659           member of the Windows group specified by the
660           named_pipe_full_access_group system variable.
661
662       ·   --ssl* Options that begin with --ssl specify whether to connect to
663           the server using SSL and indicate where to find SSL keys and
664           certificates. See the section called “Command Options for Encrypted
665           Connections”.
666
667       ·   --ssl-fips-mode={OFF|ON|STRICT} Controls whether to enable FIPS
668           mode on the client side. The --ssl-fips-mode option differs from
669           other --ssl-xxx options in that it is not used to establish
670           encrypted connections, but rather to affect which cryptographic
671           operations to permit. See Section 6.8, “FIPS Support”.
672
673           These --ssl-fips-mode values are permitted:
674
675           ·   OFF: Disable FIPS mode.
676
677           ·   ON: Enable FIPS mode.
678
679           ·   STRICT: Enable “strict” FIPS mode.
680
681
682               Note
683               If the OpenSSL FIPS Object Module is not available, the only
684               permitted value for --ssl-fips-mode is OFF. In this case,
685               setting --ssl-fips-mode to ON or STRICT causes the client to
686               produce a warning at startup and to operate in non-FIPS mode.
687
688       ·   --tls-ciphersuites=ciphersuite_list The permissible ciphersuites
689           for encrypted connections that use TLSv1.3. The value is a list of
690           one or more colon-separated ciphersuite names. The ciphersuites
691           that can be named for this option depend on the SSL library used to
692           compile MySQL. For details, see Section 6.3.2, “Encrypted
693           Connection TLS Protocols and Ciphers”.
694
695           This option was added in MySQL 8.0.16.
696
697       ·   --tls-version=protocol_list The permissible TLS protocols for
698           encrypted connections. The value is a list of one or more
699           comma-separated protocol names. The protocols that can be named for
700           this option depend on the SSL library used to compile MySQL. For
701           details, see Section 6.3.2, “Encrypted Connection TLS Protocols and
702           Ciphers”.
703
704       ·   --triggers Include triggers for each dumped table in the output.
705
706           This option is enabled by default; use --skip-triggers to disable
707           it.
708
709       ·   --tz-utc This option enables TIMESTAMP columns to be dumped and
710           reloaded between servers in different time zones.  mysqlpump sets
711           its connection time zone to UTC and adds SET TIME_ZONE='+00:00' to
712           the dump file. Without this option, TIMESTAMP columns are dumped
713           and reloaded in the time zones local to the source and destination
714           servers, which can cause the values to change if the servers are in
715           different time zones.  --tz-utc also protects against changes due
716           to daylight saving time.
717
718           This option is enabled by default; use --skip-tz-utc to disable it.
719
720       ·   --user=user_name, -u user_name The user name of the MySQL account
721           to use for connecting to the server.
722
723       ·   --users Dump user accounts as logical definitions in the form of
724           CREATE USER and GRANT statements.
725
726           User definitions are stored in the grant tables in the mysql system
727           database. By default, mysqlpump does not include the grant tables
728           in mysql database dumps. To dump the contents of the grant tables
729           as logical definitions, use the --users option and suppress all
730           database dumping:
731
732               shell> mysqlpump --exclude-databases=% --users
733
734       ·   --version, -V Display version information and exit.
735
736       ·   --watch-progress Periodically display a progress indicator that
737           provides information about the completed and total number of
738           tables, rows, and other objects.
739
740           This option is enabled by default; use --skip-watch-progress to
741           disable it.
742
743       ·   --zstd-compression-level=level The compression level to use for
744           connections to the server that use the zstd compression algorithm.
745           The permitted levels are from 1 to 22, with larger values
746           indicating increasing levels of compression. The default zstd
747           compression level is 3. The compression level setting has no effect
748           on connections that do not use zstd compression.
749
750           For more information, see Section 4.2.8, “Connection Compression
751           Control”.
752
753           This option was added in MySQL 8.0.18.
754       mysqlpump Object Selection
755
756       mysqlpump has a set of inclusion and exclusion options that enable
757       filtering of several object types and provide flexible control over
758       which objects to dump:
759
760       ·   --include-databases and --exclude-databases apply to databases and
761           all objects within them.
762
763       ·   --include-tables and --exclude-tables apply to tables. These
764           options also affect triggers associated with tables unless the
765           trigger-specific options are given.
766
767       ·   --include-triggers and --exclude-triggers apply to triggers.
768
769       ·   --include-routines and --exclude-routines apply to stored
770           procedures and functions. If a routine option matches a stored
771           procedure name, it also matches a stored function of the same name.
772
773       ·   --include-events and --exclude-events apply to Event Scheduler
774           events.
775
776       ·   --include-users and --exclude-users apply to user accounts.
777
778       Any inclusion or exclusion option may be given multiple times. The
779       effect is additive. Order of these options does not matter.
780
781       The value of each inclusion and exclusion option is a list of
782       comma-separated names of the appropriate object type. For example:
783
784           --exclude-databases=test,world
785           --include-tables=customer,invoice
786
787       Wildcard characters are permitted in the object names:
788
789       ·   % matches any sequence of zero or more characters.
790
791       ·   _ matches any single character.
792
793       For example, --include-tables=t%,__tmp matches all table names that
794       begin with t and all five-character table names that end with tmp.
795
796       For users, a name specified without a host part is interpreted with an
797       implied host of %. For example, u1 and u1@% are equivalent. This is the
798       same equivalence that applies in MySQL generally (see Section 6.2.4,
799       “Specifying Account Names”).
800
801       Inclusion and exclusion options interact as follows:
802
803       ·   By default, with no inclusion or exclusion options, mysqlpump dumps
804           all databases (with certain exceptions noted in mysqlpump
805           Restrictions).
806
807       ·   If inclusion options are given in the absence of exclusion options,
808           only the objects named as included are dumped.
809
810       ·   If exclusion options are given in the absence of inclusion options,
811           all objects are dumped except those named as excluded.
812
813       ·   If inclusion and exclusion options are given, all objects named as
814           excluded and not named as included are not dumped. All other
815           objects are dumped.
816
817       If multiple databases are being dumped, it is possible to name tables,
818       triggers, and routines in a specific database by qualifying the object
819       names with the database name. The following command dumps databases db1
820       and db2, but excludes tables db1.t1 and db2.t2:
821
822           shell> mysqlpump --include-databases=db1,db2 --exclude-tables=db1.t1,db2.t2
823
824       The following options provide alternative ways to specify which
825       databases to dump:
826
827       ·   The --all-databases option dumps all databases (with certain
828           exceptions noted in mysqlpump Restrictions). It is equivalent to
829           specifying no object options at all (the default mysqlpump action
830           is to dump everything).
831
832           --include-databases=% is similar to --all-databases, but selects
833           all databases for dumping, even those that are exceptions for
834           --all-databases.
835
836       ·   The --databases option causes mysqlpump to treat all name arguments
837           as names of databases to dump. It is equivalent to an
838           --include-databases option that names the same databases.
839       mysqlpump Parallel Processing
840
841       mysqlpump can use parallelism to achieve concurrent processing. You can
842       select concurrency between databases (to dump multiple databases
843       simultaneously) and within databases (to dump multiple objects from a
844       given database simultaneously).
845
846       By default, mysqlpump sets up one queue with two threads. You can
847       create additional queues and control the number of threads assigned to
848       each one, including the default queue:
849
850       ·   --default-parallelism=N specifies the default number of threads
851           used for each queue. In the absence of this option, N is 2.
852
853           The default queue always uses the default number of threads.
854           Additional queues use the default number of threads unless you
855           specify otherwise.
856
857       ·   --parallel-schemas=[N:]db_list sets up a processing queue for
858           dumping the databases named in db_list and optionally specifies how
859           many threads the queue uses.  db_list is a list of comma-separated
860           database names. If the option argument begins with N:, the queue
861           uses N threads. Otherwise, the --default-parallelism option
862           determines the number of queue threads.
863
864           Multiple instances of the --parallel-schemas option create multiple
865           queues.
866
867           Names in the database list are permitted to contain the same % and
868           _ wildcard characters supported for filtering options (see
869           mysqlpump Object Selection).
870
871       mysqlpump uses the default queue for processing any databases not named
872       explicitly with a --parallel-schemas option, and for dumping user
873       definitions if command options select them.
874
875       In general, with multiple queues, mysqlpump uses parallelism between
876       the sets of databases processed by the queues, to dump multiple
877       databases simultaneously. For a queue that uses multiple threads,
878       mysqlpump uses parallelism within databases, to dump multiple objects
879       from a given database simultaneously. Exceptions can occur; for
880       example, mysqlpump may block queues while it obtains from the server
881       lists of objects in databases.
882
883       With parallelism enabled, it is possible for output from different
884       databases to be interleaved. For example, INSERT statements from
885       multiple tables dumped in parallel can be interleaved; the statements
886       are not written in any particular order. This does not affect reloading
887       because output statements qualify object names with database names or
888       are preceded by USE statements as required.
889
890       The granularity for parallelism is a single database object. For
891       example, a single table cannot be dumped in parallel using multiple
892       threads.
893
894       Examples:
895
896           shell> mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
897
898       mysqlpump sets up a queue to process db1 and db2, another queue to
899       process db3, and a default queue to process all other databases. All
900       queues use two threads.
901
902           shell> mysqlpump --parallel-schemas=db1,db2 --parallel-schemas=db3
903                    --default-parallelism=4
904
905       This is the same as the previous example except that all queues use
906       four threads.
907
908           shell> mysqlpump --parallel-schemas=5:db1,db2 --parallel-schemas=3:db3
909
910       The queue for db1 and db2 uses five threads, the queue for db3 uses
911       three threads, and the default queue uses the default of two threads.
912
913       As a special case, with --default-parallelism=0 and no
914       --parallel-schemas options, mysqlpump runs as a single-threaded process
915       and creates no queues.  mysqlpump Restrictions
916
917       mysqlpump does not dump the performance_schema, ndbinfo, or sys schema
918       by default. To dump any of these, name them explicitly on the command
919       line. You can also name them with the --databases or
920       --include-databases option.
921
922       mysqlpump does not dump the INFORMATION_SCHEMA schema.
923
924       mysqlpump does not dump InnoDB CREATE TABLESPACE statements.
925
926       mysqlpump dumps user accounts in logical form using CREATE USER and
927       GRANT statements (for example, when you use the --include-users or
928       --users option). For this reason, dumps of the mysql system database do
929       not by default include the grant tables that contain user definitions:
930       user, db, tables_priv, columns_priv, procs_priv, or proxies_priv. To
931       dump any of the grant tables, name the mysql database followed by the
932       table names:
933
934           shell> mysqlpump mysql user db ...
935
937       Copyright © 1997, 2020, Oracle and/or its affiliates.
938
939       This documentation is free software; you can redistribute it and/or
940       modify it only under the terms of the GNU General Public License as
941       published by the Free Software Foundation; version 2 of the License.
942
943       This documentation is distributed in the hope that it will be useful,
944       but WITHOUT ANY WARRANTY; without even the implied warranty of
945       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
946       General Public License for more details.
947
948       You should have received a copy of the GNU General Public License along
949       with the program; if not, write to the Free Software Foundation, Inc.,
950       51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
951       http://www.gnu.org/licenses/.
952
953

NOTES

955        1. MySQL Shell dump utilities
956           https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html
957
958        2. MySQL Shell load dump utilities
959           https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html
960
961        3. here
962           https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html
963

SEE ALSO

965       For more information, please refer to the MySQL Reference Manual, which
966       may already be installed locally and which is also available online at
967       http://dev.mysql.com/doc/.
968

AUTHOR

970       Oracle Corporation (http://dev.mysql.com/).
971
972
973
974MySQL 8.0                         11/26/2020                      MYSQLPUMP(1)
Impressum