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

NAME

6       mysqldump - a database backup program
7

SYNOPSIS

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

DESCRIPTION

12       The mysqldump client utility performs logical backups, producing a set
13       of SQL statements that can be executed to reproduce the original
14       database object definitions and table data. It dumps one or more MySQL
15       databases for backup or transfer to another SQL server. The mysqldump
16       command can also generate output in CSV, other delimited text, or XML
17       format.
18
19           Tip
20           Consider using the MySQL Shell dump utilities[1], which provide
21           parallel dumping with multiple threads, file compression, and
22           progress information display, as well as cloud features such as
23           Oracle Cloud Infrastructure Object Storage streaming, and MySQL
24           Database Service compatibility checks and modifications. Dumps can
25           be easily imported into a MySQL Server instance or a MySQL Database
26           Service DB System using the MySQL Shell load dump utilities[2].
27           Installation instructions for MySQL Shell can be found here[3].
28
29       •   Performance and Scalability Considerations
30
31       •   Invocation Syntax
32
33       •   Option Syntax - Alphabetical Summary
34
35       •   Connection Options
36
37       •   Option-File Options
38
39       •   DDL Options
40
41       •   Debug Options
42
43       •   Help Options
44
45       •   Internationalization Options
46
47       •   Replication Options
48
49       •   Format Options
50
51       •   Filtering Options
52
53       •   Performance Options
54
55       •   Transactional Options
56
57       •   Option Groups
58
59       •   Examples
60
61       •   Restrictions
62
63       mysqldump requires at least the SELECT privilege for dumped tables,
64       SHOW VIEW for dumped views, TRIGGER for dumped triggers, LOCK TABLES if
65       the --single-transaction option is not used, and (as of MySQL 8.0.21)
66       PROCESS if the --no-tablespaces option is not used. Certain options
67       might require other privileges as noted in the option descriptions.
68
69       To reload a dump file, you must have the privileges required to execute
70       the statements that it contains, such as the appropriate CREATE
71       privileges for objects created by those statements.
72
73       mysqldump output can include ALTER DATABASE statements that change the
74       database collation. These may be used when dumping stored programs to
75       preserve their character encodings. To reload a dump file containing
76       such statements, the ALTER privilege for the affected database is
77       required.
78
79           Note
80           A dump made using PowerShell on Windows with output redirection
81           creates a file that has UTF-16 encoding:
82
83               mysqldump [options] > dump.sql
84
85           However, UTF-16 is not permitted as a connection character set (see
86           the section called “Impermissible Client Character Sets”), so the
87           dump file cannot be loaded correctly. To work around this issue,
88           use the --result-file option, which creates the output in ASCII
89           format:
90
91               mysqldump [options] --result-file=dump.sql
92       Performance and Scalability Considerations
93
94       mysqldump advantages include the convenience and flexibility of viewing
95       or even editing the output before restoring. You can clone databases
96       for development and DBA work, or produce slight variations of an
97       existing database for testing. It is not intended as a fast or scalable
98       solution for backing up substantial amounts of data. With large data
99       sizes, even if the backup step takes a reasonable time, restoring the
100       data can be very slow because replaying the SQL statements involves
101       disk I/O for insertion, index creation, and so on.
102
103       For large-scale backup and restore, a physical backup is more
104       appropriate, to copy the data files in their original format that can
105       be restored quickly:
106
107       •   If your tables are primarily InnoDB tables, or if you have a mix of
108           InnoDB and MyISAM tables, consider using the mysqlbackup command of
109           the MySQL Enterprise Backup product. (Available as part of the
110           Enterprise subscription.) It provides the best performance for
111           InnoDB backups with minimal disruption; it can also back up tables
112           from MyISAM and other storage engines; and it provides a number of
113           convenient options to accommodate different backup scenarios. See
114           Section 30.2, “MySQL Enterprise Backup Overview”.
115
116       mysqldump can retrieve and dump table contents row by row, or it can
117       retrieve the entire content from a table and buffer it in memory before
118       dumping it. Buffering in memory can be a problem if you are dumping
119       large tables. To dump tables row by row, use the --quick option (or
120       --opt, which enables --quick). The --opt option (and hence --quick) is
121       enabled by default, so to enable memory buffering, use --skip-quick.
122
123       If you are using a recent version of mysqldump to generate a dump to be
124       reloaded into a very old MySQL server, use the --skip-opt option
125       instead of the --opt or --extended-insert option.
126
127       For additional information about mysqldump, see Section 7.4, “Using
128       mysqldump for Backups”.  Invocation Syntax
129
130       There are in general three ways to use mysqldump—in order to dump a set
131       of one or more tables, a set of one or more complete databases, or an
132       entire MySQL server—as shown here:
133
134           mysqldump [options] db_name [tbl_name ...]
135           mysqldump [options] --databases db_name ...
136           mysqldump [options] --all-databases
137
138       To dump entire databases, do not name any tables following db_name, or
139       use the --databases or --all-databases option.
140
141       To see a list of the options your version of mysqldump supports, issue
142       the command mysqldump --help.  Option Syntax - Alphabetical Summary
143
144       mysqldump supports the following options, which can be specified on the
145       command line or in the [mysqldump] and [client] groups of an option
146       file. For information about option files used by MySQL programs, see
147       Section 4.2.2.2, “Using Option Files”.  Connection Options
148
149       The mysqldump command logs into a MySQL server to extract information.
150       The following options specify how to connect to the MySQL server,
151       either on the same machine or a remote system.
152
153--bind-address=ip_address On a computer having multiple network
154           interfaces, use this option to select which interface to use for
155           connecting to the MySQL server.
156
157--compress, -C Compress all information sent between the client and
158           the server if possible. See Section 4.2.8, “Connection Compression
159           Control”.
160
161           As of MySQL 8.0.18, this option is deprecated. Expect it to be
162           removed in a future version of MySQL. See the section called
163           “Configuring Legacy Connection Compression”.
164
165--compression-algorithms=value The permitted compression algorithms
166           for connections to the server. The available algorithms are the
167           same as for the protocol_compression_algorithms system variable.
168           The default value is uncompressed.
169
170           For more information, see Section 4.2.8, “Connection Compression
171           Control”.
172
173           This option was added in MySQL 8.0.18.
174
175--default-auth=plugin A hint about which client-side authentication
176           plugin to use. See Section 6.2.17, “Pluggable Authentication”.
177
178--enable-cleartext-plugin Enable the mysql_clear_password cleartext
179           authentication plugin. (See Section 6.4.1.4, “Client-Side Cleartext
180           Pluggable Authentication”.)
181
182--get-server-public-key Request from the server the public key
183           required for RSA key pair-based password exchange. This option
184           applies to clients that authenticate with the caching_sha2_password
185           authentication plugin. For that plugin, the server does not send
186           the public key unless requested. This option is ignored for
187           accounts that do not authenticate with that plugin. It is also
188           ignored if RSA-based password exchange is not used, as is the case
189           when the client connects to the server using a secure connection.
190
191           If --server-public-key-path=file_name is given and specifies a
192           valid public key file, it takes precedence over
193           --get-server-public-key.
194
195           For information about the caching_sha2_password plugin, see
196           Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
197
198--host=host_name, -h host_name Dump data from the MySQL server on
199           the given host. The default host is localhost.
200
201--login-path=name Read options from the named login path in the
202           .mylogin.cnf login path file. A “login path” is an option group
203           containing options that specify which MySQL server to connect to
204           and which account to authenticate as. To create or modify a login
205           path file, use the mysql_config_editor utility. See
206           mysql_config_editor(1).
207
208           For additional information about this and other option-file
209           options, see Section 4.2.2.3, “Command-Line Options that Affect
210           Option-File Handling”.
211
212--password[=password], -p[password] The password of the MySQL
213           account used for connecting to the server. The password value is
214           optional. If not given, mysqldump prompts for one. If given, there
215           must be no space between --password= or -p and the password
216           following it. If no password option is specified, the default is to
217           send no password.
218
219           Specifying a password on the command line should be considered
220           insecure. To avoid giving the password on the command line, use an
221           option file. See Section 6.1.2.1, “End-User Guidelines for Password
222           Security”.
223
224           To explicitly specify that there is no password and that mysqldump
225           should not prompt for one, use the --skip-password option.
226
227--pipe, -W On Windows, connect to the server using a named pipe.
228           This option applies only if the server was started with the
229           named_pipe system variable enabled to support named-pipe
230           connections. In addition, the user making the connection must be a
231           member of the Windows group specified by the
232           named_pipe_full_access_group system variable.
233
234--plugin-dir=dir_name The directory in which to look for plugins.
235           Specify this option if the --default-auth option is used to specify
236           an authentication plugin but mysqldump does not find it. See
237           Section 6.2.17, “Pluggable Authentication”.
238
239--port=port_num, -P port_num For TCP/IP connections, the port
240           number to use.
241
242--protocol={TCP|SOCKET|PIPE|MEMORY} The transport protocol to use
243           for connecting to the server. It is useful when the other
244           connection parameters normally result in use of a protocol other
245           than the one you want. For details on the permissible values, see
246           Section 4.2.7, “Connection Transport Protocols”.
247
248--server-public-key-path=file_name The path name to a file in PEM
249           format containing a client-side copy of the public key required by
250           the server for RSA key pair-based password exchange. This option
251           applies to clients that authenticate with the sha256_password or
252           caching_sha2_password authentication plugin. This option is ignored
253           for accounts that do not authenticate with one of those plugins. It
254           is also ignored if RSA-based password exchange is not used, as is
255           the case when the client connects to the server using a secure
256           connection.
257
258           If --server-public-key-path=file_name is given and specifies a
259           valid public key file, it takes precedence over
260           --get-server-public-key.
261
262           For sha256_password, this option applies only if MySQL was built
263           using OpenSSL.
264
265           For information about the sha256_password and caching_sha2_password
266           plugins, see Section 6.4.1.3, “SHA-256 Pluggable Authentication”,
267           and Section 6.4.1.2, “Caching SHA-2 Pluggable Authentication”.
268
269--socket=path, -S path For connections to localhost, the Unix
270           socket file to use, or, on Windows, the name of the named pipe to
271           use.
272
273           On Windows, this option applies only if the server was started with
274           the named_pipe system variable enabled to support named-pipe
275           connections. In addition, the user making the connection must be a
276           member of the Windows group specified by the
277           named_pipe_full_access_group system variable.
278
279--ssl* Options that begin with --ssl specify whether to connect to
280           the server using encryption and indicate where to find SSL keys and
281           certificates. See the section called “Command Options for Encrypted
282           Connections”.
283
284--ssl-fips-mode={OFF|ON|STRICT} Controls whether to enable FIPS
285           mode on the client side. The --ssl-fips-mode option differs from
286           other --ssl-xxx options in that it is not used to establish
287           encrypted connections, but rather to affect which cryptographic
288           operations to permit. See Section 6.8, “FIPS Support”.
289
290           These --ssl-fips-mode values are permitted:
291
292           •   OFF: Disable FIPS mode.
293
294           •   ON: Enable FIPS mode.
295
296           •   STRICT: Enable “strict” FIPS mode.
297
298
299               Note
300               If the OpenSSL FIPS Object Module is not available, the only
301               permitted value for --ssl-fips-mode is OFF. In this case,
302               setting --ssl-fips-mode to ON or STRICT causes the client to
303               produce a warning at startup and to operate in non-FIPS mode.
304
305--tls-ciphersuites=ciphersuite_list The permissible ciphersuites
306           for encrypted connections that use TLSv1.3. The value is a list of
307           one or more colon-separated ciphersuite names. The ciphersuites
308           that can be named for this option depend on the SSL library used to
309           compile MySQL. For details, see Section 6.3.2, “Encrypted
310           Connection TLS Protocols and Ciphers”.
311
312           This option was added in MySQL 8.0.16.
313
314--tls-version=protocol_list The permissible TLS protocols for
315           encrypted connections. The value is a list of one or more
316           comma-separated protocol names. The protocols that can be named for
317           this option depend on the SSL library used to compile MySQL. For
318           details, see Section 6.3.2, “Encrypted Connection TLS Protocols and
319           Ciphers”.
320
321--user=user_name, -u user_name The user name of the MySQL account
322           to use for connecting to the server.
323
324--zstd-compression-level=level The compression level to use for
325           connections to the server that use the zstd compression algorithm.
326           The permitted levels are from 1 to 22, with larger values
327           indicating increasing levels of compression. The default zstd
328           compression level is 3. The compression level setting has no effect
329           on connections that do not use zstd compression.
330
331           For more information, see Section 4.2.8, “Connection Compression
332           Control”.
333
334           This option was added in MySQL 8.0.18.
335       Option-File Options
336
337       These options are used to control which option files to read.
338
339--defaults-extra-file=file_name Read this option file after the
340           global option file but (on Unix) before the user option file. If
341           the file does not exist or is otherwise inaccessible, an error
342           occurs. If file_name is not an absolute path name, it is
343           interpreted relative to the current directory.
344
345           For additional information about this and other option-file
346           options, see Section 4.2.2.3, “Command-Line Options that Affect
347           Option-File Handling”.
348
349--defaults-file=file_name Use only the given option file. If the
350           file does not exist or is otherwise inaccessible, an error occurs.
351           If file_name is not an absolute path name, it is interpreted
352           relative to the current directory.
353
354           Exception: Even with --defaults-file, client programs read
355           .mylogin.cnf.
356
357           For additional information about this and other option-file
358           options, see Section 4.2.2.3, “Command-Line Options that Affect
359           Option-File Handling”.
360
361--defaults-group-suffix=str Read not only the usual option groups,
362           but also groups with the usual names and a suffix of str. For
363           example, mysqldump normally reads the [client] and [mysqldump]
364           groups. If this option is given as --defaults-group-suffix=_other,
365           mysqldump also reads the [client_other] and [mysqldump_other]
366           groups.
367
368           For additional information about this and other option-file
369           options, see Section 4.2.2.3, “Command-Line Options that Affect
370           Option-File Handling”.
371
372--no-defaults Do not read any option files. If program startup
373           fails due to reading unknown options from an option file,
374           --no-defaults can be used to prevent them from being read.
375
376           The exception is that the .mylogin.cnf file is read in all cases,
377           if it exists. This permits passwords to be specified in a safer way
378           than on the command line even when --no-defaults is used. To create
379           .mylogin.cnf, use the mysql_config_editor utility. See
380           mysql_config_editor(1).
381
382           For additional information about this and other option-file
383           options, see Section 4.2.2.3, “Command-Line Options that Affect
384           Option-File Handling”.
385
386--print-defaults Print the program name and all options that it
387           gets from option files.
388
389           For additional information about this and other option-file
390           options, see Section 4.2.2.3, “Command-Line Options that Affect
391           Option-File Handling”.
392       DDL Options
393
394       Usage scenarios for mysqldump include setting up an entire new MySQL
395       instance (including database tables), and replacing data inside an
396       existing instance with existing databases and tables. The following
397       options let you specify which things to tear down and set up when
398       restoring a dump, by encoding various DDL statements within the dump
399       file.
400
401--add-drop-database Write a DROP DATABASE statement before each
402           CREATE DATABASE statement. This option is typically used in
403           conjunction with the --all-databases or --databases option because
404           no CREATE DATABASE statements are written unless one of those
405           options is specified.
406
407               Note
408               In MySQL 8.0, the mysql schema is considered a system schema
409               that cannot be dropped by end users. If --add-drop-database is
410               used with --all-databases or with --databases where the list of
411               schemas to be dumped includes mysql, the dump file contains a
412               DROP DATABASE `mysql` statement that causes an error when the
413               dump file is reloaded.
414
415               Instead, to use --add-drop-database, use --databases with a
416               list of schemas to be dumped, where the list does not include
417               mysql.
418
419--add-drop-table Write a DROP TABLE statement before each CREATE
420           TABLE statement.
421
422--add-drop-trigger Write a DROP TRIGGER statement before each
423           CREATE TRIGGER statement.
424
425--all-tablespaces, -Y Adds to a table dump all SQL statements
426           needed to create any tablespaces used by an NDB table. This
427           information is not otherwise included in the output from mysqldump.
428           This option is currently relevant only to NDB Cluster tables.
429
430--no-create-db, -n Suppress the CREATE DATABASE statements that are
431           otherwise included in the output if the --databases or
432           --all-databases option is given.
433
434--no-create-info, -t Do not write CREATE TABLE statements that
435           create each dumped table.
436
437               Note
438               This option does not exclude statements creating log file
439               groups or tablespaces from mysqldump output; however, you can
440               use the --no-tablespaces option for this purpose.
441
442--no-tablespaces, -y This option suppresses all CREATE LOGFILE
443           GROUP and CREATE TABLESPACE statements in the output of mysqldump.
444
445--replace Write REPLACE statements rather than INSERT statements.
446       Debug Options
447
448       The following options print debugging information, encode debugging
449       information in the dump file, or let the dump operation proceed
450       regardless of potential problems.
451
452--allow-keywords Permit creation of column names that are keywords.
453           This works by prefixing each column name with the table name.
454
455--comments, -i Write additional information in the dump file such
456           as program version, server version, and host. This option is
457           enabled by default. To suppress this additional information, use
458           --skip-comments.
459
460--debug[=debug_options], -# [debug_options] Write a debugging log.
461           A typical debug_options string is d:t:o,file_name. The default
462           value is d:t:o,/tmp/mysqldump.trace.
463
464           This option is available only if MySQL was built using WITH_DEBUG.
465           MySQL release binaries provided by Oracle are not built using this
466           option.
467
468--debug-check Print some debugging information when the program
469           exits.
470
471           This option is available only if MySQL was built using WITH_DEBUG.
472           MySQL release binaries provided by Oracle are not built using this
473           option.
474
475--debug-info Print debugging information and memory and CPU usage
476           statistics when the program exits.
477
478           This option is available only if MySQL was built using WITH_DEBUG.
479           MySQL release binaries provided by Oracle are not built using this
480           option.
481
482--dump-date If the --comments option is given, mysqldump produces a
483           comment at the end of the dump of the following form:
484
485               -- Dump completed on DATE
486
487           However, the date causes dump files taken at different times to
488           appear to be different, even if the data are otherwise identical.
489           --dump-date and --skip-dump-date control whether the date is added
490           to the comment. The default is --dump-date (include the date in the
491           comment).  --skip-dump-date suppresses date printing.
492
493--force, -f Ignore all errors; continue even if an SQL error occurs
494           during a table dump.
495
496           One use for this option is to cause mysqldump to continue executing
497           even when it encounters a view that has become invalid because the
498           definition refers to a table that has been dropped. Without
499           --force, mysqldump exits with an error message. With --force,
500           mysqldump prints the error message, but it also writes an SQL
501           comment containing the view definition to the dump output and
502           continues executing.
503
504           If the --ignore-error option is also given to ignore specific
505           errors, --force takes precedence.
506
507--log-error=file_name Log warnings and errors by appending them to
508           the named file. The default is to do no logging.
509
510--skip-comments See the description for the --comments option.
511
512--verbose, -v Verbose mode. Print more information about what the
513           program does.
514       Help Options
515
516       The following options display information about the mysqldump command
517       itself.
518
519--help, -?  Display a help message and exit.
520
521--version, -V Display version information and exit.
522       Internationalization Options
523
524       The following options change how the mysqldump command represents
525       character data with national language settings.
526
527--character-sets-dir=dir_name The directory where character sets
528           are installed. See Section 10.15, “Character Set Configuration”.
529
530--default-character-set=charset_name Use charset_name as the
531           default character set. See Section 10.15, “Character Set
532           Configuration”. If no character set is specified, mysqldump uses
533           utf8.
534
535--no-set-names, -N Turns off the --set-charset setting, the same as
536           specifying --skip-set-charset.
537
538--set-charset Write SET NAMES default_character_set to the output.
539           This option is enabled by default. To suppress the SET NAMES
540           statement, use --skip-set-charset.
541       Replication Options
542
543       The mysqldump command is frequently used to create an empty instance,
544       or an instance including data, on a replica server in a replication
545       configuration. The following options apply to dumping and restoring
546       data on replication source servers and replicas.
547
548--apply-replica-statements From MySQL 8.0.26, use
549           --apply-replica-statements, and before MySQL 8.0.26, use
550           --apply-slave-statements. Both options have the same effect. For a
551           replica dump produced with the --dump-replica or --dump-slave
552           option, the options add a STOP REPLICA (or before MySQL 8.0.22,
553           STOP SLAVE) statement before the statement with the binary log
554           coordinates, and a START REPLICA statement at the end of the
555           output.
556
557--apply-slave-statements Use this option before MySQL 8.0.26 rather
558           than --apply-replica-statements. Both options have the same effect.
559
560--delete-source-logs From MySQL 8.0.26, use --delete-source-logs,
561           and before MySQL 8.0.26, use --delete-master-logs. Both options
562           have the same effect. On a replication source server, the options
563           delete the binary logs by sending a PURGE BINARY LOGS statement to
564           the server after performing the dump operation. The options require
565           the RELOAD privilege as well as privileges sufficient to execute
566           that statement. The options automatically enable --source-data or
567           --master-data.
568
569--delete-master-logs Use this option before MySQL 8.0.26 rather
570           than --delete-source-logs. Both options have the same effect.
571
572--dump-replica[=value] From MySQL 8.0.26, use --dump-replica, and
573           before MySQL 8.0.26, use --dump-slave. Both options have the same
574           effect. The options are similar to --source-data, except that they
575           are used to dump a replica server to produce a dump file that can
576           be used to set up another server as a replica that has the same
577           source as the dumped server. The options cause the dump output to
578           include a CHANGE REPLICATION SOURCE TO statement (from MySQL
579           8.0.23) or CHANGE MASTER TO statement (before MySQL 8.0.23) that
580           indicates the binary log coordinates (file name and position) of
581           the dumped replica's source. The CHANGE REPLICATION SOURCE TO
582           statement reads the values of Relay_Master_Log_File and
583           Exec_Master_Log_Pos from the SHOW REPLICA STATUS output and uses
584           them for SOURCE_LOG_FILE and SOURCE_LOG_POS respectively. These are
585           the replication source server coordinates from which the replica
586           starts replicating.
587
588               Note
589               Inconsistencies in the sequence of transactions from the relay
590               log which have been executed can cause the wrong position to be
591               used. See Section 17.5.1.34, “Replication and Transaction
592               Inconsistencies” for more information.
593           --dump-replica or --dump-slave cause the coordinates from the
594           source to be used rather than those of the dumped server, as is
595           done by the --source-data or --master-data option. In addition,
596           specifying this option causes the --source-data or --master-data
597           option to be overridden, if used, and effectively ignored.
598
599               Warning
600               --dump-replica and --dump-slave should not be used if the
601               server where the dump is going to be applied uses gtid_mode=ON
602               and SOURCE_AUTO_POSITION=1 or MASTER_AUTO_POSITION=1.
603           The option value is handled the same way as for --source-data.
604           Setting no value or 1 causes a CHANGE REPLICATION SOURCE TO
605           statement (from MySQL 8.0.23) or CHANGE MASTER TO statement (before
606           MySQL 8.0.23) to be written to the dump. Setting 2 causes the
607           statement to be written but encased in SQL comments. It has the
608           same effect as --source-data in terms of enabling or disabling
609           other options and in how locking is handled.
610
611           --dump-replica and --dump-slave cause mysqldump to stop the
612           replication SQL thread before the dump and restart it again after.
613
614           --dump-replica and --dump-slave send a SHOW REPLICA STATUS
615           statement to the server to obtain information, so they require
616           privileges sufficient to execute that statement.
617
618           --apply-replica-statements and --include-source-host-port options
619           can be used in conjunction with --dump-replica and --dump-slave.
620
621--dump-slave[=value] Use this option before MySQL 8.0.26 rather
622           than --dump-replica. Both options have the same effect.
623
624--include-source-host-port From MySQL 8.0.26, use
625           --include-source-host-port, and before MySQL 8.0.26, use
626           --include-master-host-port. Both options have the same effect. The
627           options add the SOURCE_HOST | MASTER_HOST and SOURCE_PORT |
628           MASTER_PORT options for the host name and TCP/IP port number of the
629           replica's source, to the CHANGE REPLICATION SOURCE TO statement
630           (from MySQL 8.0.23) or CHANGE MASTER TO statement (before MySQL
631           8.0.23) in a replica dump produced with the --dump-replica or
632           --dump-slave option.
633
634--include-master-host-port Use this option before MySQL 8.0.26
635           rather than --include-source-host-port. Both options have the same
636           effect.
637
638--source-data[=value] From MySQL 8.0.26, use --source-data, and
639           before MySQL 8.0.26, use --master-data. Both options have the same
640           effect. The options are used to dump a replication source server to
641           produce a dump file that can be used to set up another server as a
642           replica of the source. The options cause the dump output to include
643           a CHANGE REPLICATION SOURCE TO statement (from MySQL 8.0.23) or
644           CHANGE MASTER TO statement (before MySQL 8.0.23) that indicates the
645           binary log coordinates (file name and position) of the dumped
646           server. These are the replication source server coordinates from
647           which the replica should start replicating after you load the dump
648           file into the replica.
649
650           If the option value is 2, the CHANGE REPLICATION SOURCE TO | CHANGE
651           MASTER TO statement is written as an SQL comment, and thus is
652           informative only; it has no effect when the dump file is reloaded.
653           If the option value is 1, the statement is not written as a comment
654           and takes effect when the dump file is reloaded. If no option value
655           is specified, the default value is 1.
656
657           --source-data and --master-data send a SHOW MASTER STATUS statement
658           to the server to obtain information, so they require privileges
659           sufficient to execute that statement. This option also requires the
660           RELOAD privilege and the binary log must be enabled.
661
662           --source-data and --master-data automatically turn off
663           --lock-tables. They also turn on --lock-all-tables, unless
664           --single-transaction also is specified, in which case, a global
665           read lock is acquired only for a short time at the beginning of the
666           dump (see the description for --single-transaction). In all cases,
667           any action on logs happens at the exact moment of the dump.
668
669           It is also possible to set up a replica by dumping an existing
670           replica of the source, using the --dump-replica or --dump-slave
671           option, which overrides --source-data and --master-data and causes
672           them to be ignored.
673
674--master-data[=value] Use this option before MySQL 8.0.26 rather
675           than --source-data. Both options have the same effect.
676
677--set-gtid-purged=value This option is for servers that use
678           GTID-based replication (gtid_mode=ON). It controls the inclusion of
679           a SET @@GLOBAL.gtid_purged statement in the dump output, which
680           updates the value of gtid_purged on a server where the dump file is
681           reloaded, to add the GTID set from the source server's
682           gtid_executed system variable.  gtid_purged holds the GTIDs of all
683           transactions that have been applied on the server, but do not exist
684           on any binary log file on the server.  mysqldump therefore adds the
685           GTIDs for the transactions that were executed on the source server,
686           so that the target server records these transactions as applied,
687           although it does not have them in its binary logs.
688           --set-gtid-purged also controls the inclusion of a SET
689           @@SESSION.sql_log_bin=0 statement, which disables binary logging
690           while the dump file is being reloaded. This statement prevents new
691           GTIDs from being generated and assigned to the transactions in the
692           dump file as they are executed, so that the original GTIDs for the
693           transactions are used.
694
695           If you do not set the --set-gtid-purged option, the default is that
696           a SET @@GLOBAL.gtid_purged statement is included in the dump output
697           if GTIDs are enabled on the server you are backing up, and the set
698           of GTIDs in the global value of the gtid_executed system variable
699           is not empty. A SET @@SESSION.sql_log_bin=0 statement is also
700           included if GTIDs are enabled on the server.
701
702           In MySQL 5.6 and 5.7, you can replace the value of gtid_purged with
703           a specified GTID set, provided that gtid_executed and gtid_purged
704           are empty. From MySQL 8.0, you can either replace the value of
705           gtid_purged with a specified GTID set, or you can add a plus sign
706           (+) to the statement to append a specified GTID set to the GTID set
707           that is already held by gtid_purged.  mysqldump's SET
708           @@GLOBAL.gtid_purged statement includes a plus sign (+) in a
709           version comment that takes effect when the dump file is replayed on
710           releases from MySQL 8.0, meaning that for these releases, the GTID
711           set from the dump file is added to the existing gtid_purged value.
712           For MySQL 5.6 and 5.7, the value of gtid_purged is replaced with
713           the GTID set from the dump file, which can only happen when
714           gtid_executed is the empty set (so when replication has not been
715           started previously, or when replication was not previously using
716           GTIDs). For the exact details of how the SET @@GLOBAL.gtid_purged
717           statement operates, see the gtid_purged description for the release
718           where the dump file is to be replayed.
719
720           It is important to note that the value that is included by
721           mysqldump for the SET @@GLOBAL.gtid_purged statement includes the
722           GTIDs of all transactions in the gtid_executed set on the server,
723           even those that changed suppressed parts of the database, or other
724           databases on the server that were not included in a partial dump.
725           This can mean that after the gtid_purged value has been updated on
726           the server where the dump file is replayed, GTIDs are present that
727           do not relate to any data on the target server. If you do not
728           replay any further dump files on the target server, the extraneous
729           GTIDs do not cause any problems with the future operation of the
730           server, but they make it harder to compare or reconcile GTID sets
731           on different servers in the replication topology. If you do replay
732           a further dump file on the target server that contains the same
733           GTIDs (for example, another partial dump from the same origin
734           server), any SET @@GLOBAL.gtid_purged statement in the second dump
735           file fails. In this case, either remove the statement manually
736           before replaying the dump file, or output the dump file without the
737           statement.
738
739               Note
740               For MySQL 5.6 and 5.7, it is not recommended to load a dump
741               file when GTIDs are enabled on the server (gtid_mode=ON), if
742               your dump file includes system tables.  mysqldump issues DML
743               instructions for the system tables which use the
744               non-transactional MyISAM storage engine, and this combination
745               is not permitted when GTIDs are enabled.
746           If the SET @@GLOBAL.gtid_purged statement would not have the
747           desired result on your target server, you can exclude the statement
748           from the output, or (from MySQL 8.0.17) include it but comment it
749           out so that it is not actioned automatically. You can also include
750           the statement but manually edit it in the dump file to achieve the
751           desired result.
752
753           The possible values for the --set-gtid-purged option are as
754           follows:
755
756           AUTO
757               The default value. If GTIDs are enabled on the server you are
758               backing up and gtid_executed is not empty, SET
759               @@GLOBAL.gtid_purged is added to the output, containing the
760               GTID set from gtid_executed. If GTIDs are enabled, SET
761               @@SESSION.sql_log_bin=0 is added to the output. If GTIDs are
762               not enabled on the server, the statements are not added to the
763               output.
764
765           OFF
766               SET @@GLOBAL.gtid_purged is not added to the output, and SET
767               @@SESSION.sql_log_bin=0 is not added to the output. For a
768               server where GTIDs are not in use, use this option or AUTO.
769               Only use this option for a server where GTIDs are in use if you
770               are sure that the required GTID set is already present in
771               gtid_purged on the target server and should not be changed, or
772               if you plan to identify and add any missing GTIDs manually.
773
774           ON
775               If GTIDs are enabled on the server you are backing up, SET
776               @@GLOBAL.gtid_purged is added to the output (unless
777               gtid_executed is empty), and SET @@SESSION.sql_log_bin=0 is
778               added to the output. An error occurs if you set this option but
779               GTIDs are not enabled on the server. For a server where GTIDs
780               are in use, use this option or AUTO, unless you are sure that
781               the GTIDs in gtid_executed are not needed on the target server.
782
783           COMMENTED
784               Available from MySQL 8.0.17. If GTIDs are enabled on the server
785               you are backing up, SET @@GLOBAL.gtid_purged is added to the
786               output (unless gtid_executed is empty), but it is commented
787               out. This means that the value of gtid_executed is available in
788               the output, but no action is taken automatically when the dump
789               file is reloaded.  SET @@SESSION.sql_log_bin=0 is added to the
790               output, and it is not commented out. With COMMENTED, you can
791               control the use of the gtid_executed set manually or through
792               automation. For example, you might prefer to do this if you are
793               migrating data to another server that already has different
794               active databases.
795       Format Options
796
797       The following options specify how to represent the entire dump file or
798       certain kinds of data in the dump file. They also control whether
799       certain optional information is written to the dump file.
800
801--compact Produce more compact output. This option enables the
802           --skip-add-drop-table, --skip-add-locks, --skip-comments,
803           --skip-disable-keys, and --skip-set-charset options.
804
805--compatible=name Produce output that is more compatible with other
806           database systems or with older MySQL servers. The only permitted
807           value for this option is ansi, which has the same meaning as the
808           corresponding option for setting the server SQL mode. See
809           Section 5.1.11, “Server SQL Modes”.
810
811--complete-insert, -c Use complete INSERT statements that include
812           column names.
813
814--create-options Include all MySQL-specific table options in the
815           CREATE TABLE statements.
816
817--fields-terminated-by=..., --fields-enclosed-by=...,
818           --fields-optionally-enclosed-by=..., --fields-escaped-by=...  These
819           options are used with the --tab option and have the same meaning as
820           the corresponding FIELDS clauses for LOAD DATA. See Section 13.2.7,
821           “LOAD DATA Statement”.
822
823--hex-blob Dump binary columns using hexadecimal notation (for
824           example, 'abc' becomes 0x616263). The affected data types are
825           BINARY, VARBINARY, BLOB types, BIT, all spatial data types, and
826           other non-binary data types when used with the binary character
827           set.
828
829--lines-terminated-by=...  This option is used with the --tab
830           option and has the same meaning as the corresponding LINES clause
831           for LOAD DATA. See Section 13.2.7, “LOAD DATA Statement”.
832
833--quote-names, -Q Quote identifiers (such as database, table, and
834           column names) within ` characters. If the ANSI_QUOTES SQL mode is
835           enabled, identifiers are quoted within " characters. This option is
836           enabled by default. It can be disabled with --skip-quote-names, but
837           this option should be given after any option such as --compatible
838           that may enable --quote-names.
839
840--result-file=file_name, -r file_name Direct output to the named
841           file. The result file is created and its previous contents
842           overwritten, even if an error occurs while generating the dump.
843
844           This option should be used on Windows to prevent newline \n
845           characters from being converted to \r\n carriage return/newline
846           sequences.
847
848--show-create-skip-secondary-engine=value Excludes the SECONDARY
849           ENGINE clause from CREATE TABLE statements. It does so by enabling
850           the show_create_table_skip_secondary_engine system variable for the
851           duration of the dump operation. Alternatively, you can enable the
852           show_create_table_skip_secondary_engine system variable prior to
853           using mysqldump.
854
855           This option was added in MySQL 8.0.18. Attempting a mysqldump
856           operation with the --show-create-skip-secondary-engine option on a
857           release prior to MySQL 8.0.18 that does not support the
858           show_create_table_skip_secondary_engine variable causes an error.
859
860--tab=dir_name, -T dir_name Produce tab-separated text-format data
861           files. For each dumped table, mysqldump creates a tbl_name.sql file
862           that contains the CREATE TABLE statement that creates the table,
863           and the server writes a tbl_name.txt file that contains its data.
864           The option value is the directory in which to write the files.
865
866               Note
867               This option should be used only when mysqldump is run on the
868               same machine as the mysqld server. Because the server creates
869               *.txt files in the directory that you specify, the directory
870               must be writable by the server and the MySQL account that you
871               use must have the FILE privilege. Because mysqldump creates
872               *.sql in the same directory, it must be writable by your system
873               login account.
874           By default, the .txt data files are formatted using tab characters
875           between column values and a newline at the end of each line. The
876           format can be specified explicitly using the --fields-xxx and
877           --lines-terminated-by options.
878
879           Column values are converted to the character set specified by the
880           --default-character-set option.
881
882--tz-utc This option enables TIMESTAMP columns to be dumped and
883           reloaded between servers in different time zones.  mysqldump sets
884           its connection time zone to UTC and adds SET TIME_ZONE='+00:00' to
885           the dump file. Without this option, TIMESTAMP columns are dumped
886           and reloaded in the time zones local to the source and destination
887           servers, which can cause the values to change if the servers are in
888           different time zones.  --tz-utc also protects against changes due
889           to daylight saving time.  --tz-utc is enabled by default. To
890           disable it, use --skip-tz-utc.
891
892--xml, -X Write dump output as well-formed XML.
893
894           NULL, 'NULL', and Empty Values: For a column named column_name, the
895           NULL value, an empty string, and the string value 'NULL' are
896           distinguished from one another in the output generated by this
897           option as follows.
898
899           ┌─────────────────────┬────────────────────────────────────────────┐
900Value:               XML Representation:                        
901           ├─────────────────────┼────────────────────────────────────────────┤
902           │NULL (unknown value) │                                            │
903           │                     │            <field                          │
904           │                     │            name="column_name"              │
905           │                     │            xsi:nil="true"                  │
906           │                     │            />                              │
907           ├─────────────────────┼────────────────────────────────────────────┤
908           │                     │                                            │
909           │                     │            <field                          │
910           │                     │            name="column_name"></field>     │
911           ├─────────────────────┼────────────────────────────────────────────┤
912           │                     │                                            │
913           │                     │            <field                          │
914           │                     │            name="column_name">NULL</field> │
915           └─────────────────────┴────────────────────────────────────────────┘
916           The output from the mysql client when run using the --xml option
917           also follows the preceding rules. (See the section called “MYSQL
918           CLIENT OPTIONS”.)
919
920           XML output from mysqldump includes the XML namespace, as shown
921           here:
922
923               shell> mysqldump --xml -u root world City
924               <?xml version="1.0"?>
925               <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
926               <database name="world">
927               <table_structure name="City">
928               <field Field="ID" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" />
929               <field Field="Name" Type="char(35)" Null="NO" Key="" Default="" Extra="" />
930               <field Field="CountryCode" Type="char(3)" Null="NO" Key="" Default="" Extra="" />
931               <field Field="District" Type="char(20)" Null="NO" Key="" Default="" Extra="" />
932               <field Field="Population" Type="int(11)" Null="NO" Key="" Default="0" Extra="" />
933               <key Table="City" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="ID"
934               Collation="A" Cardinality="4079" Null="" Index_type="BTREE" Comment="" />
935               <options Name="City" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="4079"
936               Avg_row_length="67" Data_length="273293" Max_data_length="18858823439613951"
937               Index_length="43008" Data_free="0" Auto_increment="4080"
938               Create_time="2007-03-31 01:47:01" Update_time="2007-03-31 01:47:02"
939               Collation="latin1_swedish_ci" Create_options="" Comment="" />
940               </table_structure>
941               <table_data name="City">
942               <row>
943               <field name="ID">1</field>
944               <field name="Name">Kabul</field>
945               <field name="CountryCode">AFG</field>
946               <field name="District">Kabol</field>
947               <field name="Population">1780000</field>
948               </row>
949               ...
950               <row>
951               <field name="ID">4079</field>
952               <field name="Name">Rafah</field>
953               <field name="CountryCode">PSE</field>
954               <field name="District">Rafah</field>
955               <field name="Population">92020</field>
956               </row>
957               </table_data>
958               </database>
959               </mysqldump>
960       Filtering Options
961
962       The following options control which kinds of schema objects are written
963       to the dump file: by category, such as triggers or events; by name, for
964       example, choosing which databases and tables to dump; or even filtering
965       rows from the table data using a WHERE clause.
966
967--all-databases, -A Dump all tables in all databases. This is the
968           same as using the --databases option and naming all the databases
969           on the command line.
970
971               Note
972               See the --add-drop-database description for information about
973               an incompatibility of that option with --all-databases.
974           Prior to MySQL 8.0, the --routines and --events options for
975           mysqldump and mysqlpump were not required to include stored
976           routines and events when using the --all-databases option: The dump
977           included the mysql system database, and therefore also the
978           mysql.proc and mysql.event tables containing stored routine and
979           event definitions. As of MySQL 8.0, the mysql.event and mysql.proc
980           tables are not used. Definitions for the corresponding objects are
981           stored in data dictionary tables, but those tables are not dumped.
982           To include stored routines and events in a dump made using
983           --all-databases, use the --routines and --events options
984           explicitly.
985
986--databases, -B Dump several databases. Normally, mysqldump treats
987           the first name argument on the command line as a database name and
988           following names as table names. With this option, it treats all
989           name arguments as database names.  CREATE DATABASE and USE
990           statements are included in the output before each new database.
991
992           This option may be used to dump the performance_schema database,
993           which normally is not dumped even with the --all-databases option.
994           (Also use the --skip-lock-tables option.)
995
996               Note
997               See the --add-drop-database description for information about
998               an incompatibility of that option with --databases.
999
1000--events, -E Include Event Scheduler events for the dumped
1001           databases in the output. This option requires the EVENT privileges
1002           for those databases.
1003
1004           The output generated by using --events contains CREATE EVENT
1005           statements to create the events.
1006
1007--ignore-error=error[,error]...  Ignore the specified errors. The
1008           option value is a list of comma-separated error numbers specifying
1009           the errors to ignore during mysqldump execution. If the --force
1010           option is also given to ignore all errors, --force takes
1011           precedence.
1012
1013--ignore-table=db_name.tbl_name Do not dump the given table, which
1014           must be specified using both the database and table names. To
1015           ignore multiple tables, use this option multiple times. This option
1016           also can be used to ignore views.
1017
1018--no-data, -d Do not write any table row information (that is, do
1019           not dump table contents). This is useful if you want to dump only
1020           the CREATE TABLE statement for the table (for example, to create an
1021           empty copy of the table by loading the dump file).
1022
1023--routines, -R Include stored routines (procedures and functions)
1024           for the dumped databases in the output. This option requires the
1025           global SELECT privilege.
1026
1027           The output generated by using --routines contains CREATE PROCEDURE
1028           and CREATE FUNCTION statements to create the routines.
1029
1030--tables Override the --databases or -B option.  mysqldump regards
1031           all name arguments following the option as table names.
1032
1033--triggers Include triggers for each dumped table in the output.
1034           This option is enabled by default; disable it with --skip-triggers.
1035
1036           To be able to dump a table's triggers, you must have the TRIGGER
1037           privilege for the table.
1038
1039           Multiple triggers are permitted.  mysqldump dumps triggers in
1040           activation order so that when the dump file is reloaded, triggers
1041           are created in the same activation order. However, if a mysqldump
1042           dump file contains multiple triggers for a table that have the same
1043           trigger event and action time, an error occurs for attempts to load
1044           the dump file into an older server that does not support multiple
1045           triggers. (For a workaround, see Downgrade Notes[4]; you can
1046           convert triggers to be compatible with older servers.)
1047
1048--where='where_condition', -w 'where_condition' Dump only rows
1049           selected by the given WHERE condition. Quotes around the condition
1050           are mandatory if it contains spaces or other characters that are
1051           special to your command interpreter.
1052
1053           Examples:
1054
1055               --where="user='jimf'"
1056               -w"userid>1"
1057               -w"userid<1"
1058       Performance Options
1059
1060       The following options are the most relevant for the performance
1061       particularly of the restore operations. For large data sets, restore
1062       operation (processing the INSERT statements in the dump file) is the
1063       most time-consuming part. When it is urgent to restore data quickly,
1064       plan and test the performance of this stage in advance. For restore
1065       times measured in hours, you might prefer an alternative backup and
1066       restore solution, such as MySQL Enterprise Backup for InnoDB-only and
1067       mixed-use databases.
1068
1069       Performance is also affected by the transactional options, primarily
1070       for the dump operation.
1071
1072--column-statistics Add ANALYZE TABLE statements to the output to
1073           generate histogram statistics for dumped tables when the dump file
1074           is reloaded. This option is disabled by default because histogram
1075           generation for large tables can take a long time.
1076
1077--disable-keys, -K For each table, surround the INSERT statements
1078           with /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; and /*!40000
1079           ALTER TABLE tbl_name ENABLE KEYS */; statements. This makes loading
1080           the dump file faster because the indexes are created after all rows
1081           are inserted. This option is effective only for nonunique indexes
1082           of MyISAM tables.
1083
1084--extended-insert, -e Write INSERT statements using multiple-row
1085           syntax that includes several VALUES lists. This results in a
1086           smaller dump file and speeds up inserts when the file is reloaded.
1087
1088--insert-ignore Write INSERT IGNORE statements rather than INSERT
1089           statements.
1090
1091--max-allowed-packet=value The maximum size of the buffer for
1092           client/server communication. The default is 24MB, the maximum is
1093           1GB.
1094
1095--net-buffer-length=value The initial size of the buffer for
1096           client/server communication. When creating multiple-row INSERT
1097           statements (as with the --extended-insert or --opt option),
1098           mysqldump creates rows up to --net-buffer-length bytes long. If you
1099           increase this variable, ensure that the MySQL server
1100           net_buffer_length system variable has a value at least this large.
1101
1102--network-timeout, -M Enable large tables to be dumped by setting
1103           --max-allowed-packet to its maximum value and network read and
1104           write timeouts to a large value. This option is enabled by default.
1105           To disable it, use --skip-network-timeout.
1106
1107--opt This option, enabled by default, is shorthand for the
1108           combination of --add-drop-table --add-locks --create-options
1109           --disable-keys --extended-insert --lock-tables --quick
1110           --set-charset. It gives a fast dump operation and produces a dump
1111           file that can be reloaded into a MySQL server quickly.
1112
1113           Because the --opt option is enabled by default, you only specify
1114           its converse, the --skip-opt to turn off several default settings.
1115           See the discussion of mysqldump option groups for information about
1116           selectively enabling or disabling a subset of the options affected
1117           by --opt.
1118
1119--quick, -q This option is useful for dumping large tables. It
1120           forces mysqldump to retrieve rows for a table from the server a row
1121           at a time rather than retrieving the entire row set and buffering
1122           it in memory before writing it out.
1123
1124--skip-opt See the description for the --opt option.
1125       Transactional Options
1126
1127       The following options trade off the performance of the dump operation,
1128       against the reliability and consistency of the exported data.
1129
1130--add-locks Surround each table dump with LOCK TABLES and UNLOCK
1131           TABLES statements. This results in faster inserts when the dump
1132           file is reloaded. See Section 8.2.5.1, “Optimizing INSERT
1133           Statements”.
1134
1135--flush-logs, -F Flush the MySQL server log files before starting
1136           the dump. This option requires the RELOAD privilege. If you use
1137           this option in combination with the --all-databases option, the
1138           logs are flushed for each database dumped. The exception is when
1139           using --lock-all-tables, --source-data or --master-data, or
1140           --single-transaction. In these cases, the logs are flushed only
1141           once, corresponding to the moment that all tables are locked by
1142           FLUSH TABLES WITH READ LOCK. If you want your dump and the log
1143           flush to happen at exactly the same moment, you should use
1144           --flush-logs together with --lock-all-tables, --source-data or
1145           --master-data, or --single-transaction.
1146
1147--flush-privileges Add a FLUSH PRIVILEGES statement to the dump
1148           output after dumping the mysql database. This option should be used
1149           any time the dump contains the mysql database and any other
1150           database that depends on the data in the mysql database for proper
1151           restoration.
1152
1153           Because the dump file contains a FLUSH PRIVILEGES statement,
1154           reloading the file requires privileges sufficient to execute that
1155           statement.
1156
1157
1158               Note
1159               For upgrades to MySQL 5.7 or higher from older versions, do not
1160               use --flush-privileges. For upgrade instructions in this case,
1161               see Section 2.11.4, “Changes in MySQL 8.0”.
1162
1163--lock-all-tables, -x Lock all tables across all databases. This is
1164           achieved by acquiring a global read lock for the duration of the
1165           whole dump. This option automatically turns off
1166           --single-transaction and --lock-tables.
1167
1168--lock-tables, -l For each dumped database, lock all tables to be
1169           dumped before dumping them. The tables are locked with READ LOCAL
1170           to permit concurrent inserts in the case of MyISAM tables. For
1171           transactional tables such as InnoDB, --single-transaction is a much
1172           better option than --lock-tables because it does not need to lock
1173           the tables at all.
1174
1175           Because --lock-tables locks tables for each database separately,
1176           this option does not guarantee that the tables in the dump file are
1177           logically consistent between databases. Tables in different
1178           databases may be dumped in completely different states.
1179
1180           Some options, such as --opt, automatically enable --lock-tables. If
1181           you want to override this, use --skip-lock-tables at the end of the
1182           option list.
1183
1184--no-autocommit Enclose the INSERT statements for each dumped table
1185           within SET autocommit = 0 and COMMIT statements.
1186
1187--order-by-primary Dump each table's rows sorted by its primary
1188           key, or by its first unique index, if such an index exists. This is
1189           useful when dumping a MyISAM table to be loaded into an InnoDB
1190           table, but makes the dump operation take considerably longer.
1191
1192--shared-memory-base-name=name On Windows, the shared-memory name
1193           to use for connections made using shared memory to a local server.
1194           The default value is MYSQL. The shared-memory name is
1195           case-sensitive.
1196
1197           This option applies only if the server was started with the
1198           shared_memory system variable enabled to support shared-memory
1199           connections.
1200
1201--single-transaction This option sets the transaction isolation
1202           mode to REPEATABLE READ and sends a START TRANSACTION SQL statement
1203           to the server before dumping data. It is useful only with
1204           transactional tables such as InnoDB, because then it dumps the
1205           consistent state of the database at the time when START TRANSACTION
1206           was issued without blocking any applications.
1207
1208           When using this option, you should keep in mind that only InnoDB
1209           tables are dumped in a consistent state. For example, any MyISAM or
1210           MEMORY tables dumped while using this option may still change
1211           state.
1212
1213           While a --single-transaction dump is in process, to ensure a valid
1214           dump file (correct table contents and binary log coordinates), no
1215           other connection should use the following statements: ALTER TABLE,
1216           CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A
1217           consistent read is not isolated from those statements, so use of
1218           them on a table to be dumped can cause the SELECT that is performed
1219           by mysqldump to retrieve the table contents to obtain incorrect
1220           contents or fail.
1221
1222           The --single-transaction option and the --lock-tables option are
1223           mutually exclusive because LOCK TABLES causes any pending
1224           transactions to be committed implicitly.
1225
1226           To dump large tables, combine the --single-transaction option with
1227           the --quick option.
1228       Option Groups
1229
1230       •   The --opt option turns on several settings that work together to
1231           perform a fast dump operation. All of these settings are on by
1232           default, because --opt is on by default. Thus you rarely if ever
1233           specify --opt. Instead, you can turn these settings off as a group
1234           by specifying --skip-opt, the optionally re-enable certain settings
1235           by specifying the associated options later on the command line.
1236
1237       •   The --compact option turns off several settings that control
1238           whether optional statements and comments appear in the output.
1239           Again, you can follow this option with other options that re-enable
1240           certain settings, or turn all the settings on by using the
1241           --skip-compact form.
1242
1243       When you selectively enable or disable the effect of a group option,
1244       order is important because options are processed first to last. For
1245       example, --disable-keys --lock-tables --skip-opt would not have the
1246       intended effect; it is the same as --skip-opt by itself.  Examples
1247
1248       To make a backup of an entire database:
1249
1250           mysqldump db_name > backup-file.sql
1251
1252       To load the dump file back into the server:
1253
1254           mysql db_name < backup-file.sql
1255
1256       Another way to reload the dump file:
1257
1258           mysql -e "source /path-to-backup/backup-file.sql" db_name
1259
1260       mysqldump is also very useful for populating databases by copying data
1261       from one MySQL server to another:
1262
1263           mysqldump --opt db_name | mysql --host=remote_host -C db_name
1264
1265       You can dump several databases with one command:
1266
1267           mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql
1268
1269       To dump all databases, use the --all-databases option:
1270
1271           mysqldump --all-databases > all_databases.sql
1272
1273       For InnoDB tables, mysqldump provides a way of making an online backup:
1274
1275           mysqldump --all-databases --master-data --single-transaction > all_databases.sql
1276           or from MySQL 8.0.26:
1277           mysqldump --all-databases --source-data --single-transaction > all_databases.sql
1278
1279       This backup acquires a global read lock on all tables (using FLUSH
1280       TABLES WITH READ LOCK) at the beginning of the dump. As soon as this
1281       lock has been acquired, the binary log coordinates are read and the
1282       lock is released. If long updating statements are running when the
1283       FLUSH statement is issued, the MySQL server may get stalled until those
1284       statements finish. After that, the dump becomes lock free and does not
1285       disturb reads and writes on the tables. If the update statements that
1286       the MySQL server receives are short (in terms of execution time), the
1287       initial lock period should not be noticeable, even with many updates.
1288
1289       For point-in-time recovery (also known as “roll-forward,” when you need
1290       to restore an old backup and replay the changes that happened since
1291       that backup), it is often useful to rotate the binary log (see
1292       Section 5.4.4, “The Binary Log”) or at least know the binary log
1293       coordinates to which the dump corresponds:
1294
1295           mysqldump --all-databases --master-data=2 > all_databases.sql
1296           or from MySQL 8.0.26:
1297           mysqldump --all-databases --source-data=2 > all_databases.sql
1298
1299       Or:
1300
1301           mysqldump --all-databases --flush-logs --master-data=2 > all_databases.sql
1302           or from MySQL 8.0.26:
1303           mysqldump --all-databases --flush-logs --source-data=2 > all_databases.sql
1304
1305       The --source-data or --master-data option can be used simultaneously
1306       with the --single-transaction option, which provides a convenient way
1307       to make an online backup suitable for use prior to point-in-time
1308       recovery if tables are stored using the InnoDB storage engine.
1309
1310       For more information on making backups, see Section 7.2, “Database
1311       Backup Methods”, and Section 7.3, “Example Backup and Recovery
1312       Strategy”.
1313
1314       •   To select the effect of --opt except for some features, use the
1315           --skip option for each feature. To disable extended inserts and
1316           memory buffering, use --opt --skip-extended-insert --skip-quick.
1317           (Actually, --skip-extended-insert --skip-quick is sufficient
1318           because --opt is on by default.)
1319
1320       •   To reverse --opt for all features except index disabling and table
1321           locking, use --skip-opt --disable-keys --lock-tables.
1322       Restrictions
1323
1324       mysqldump does not dump the performance_schema or sys schema by
1325       default. To dump any of these, name them explicitly on the command
1326       line. You can also name them with the --databases option. For
1327       performance_schema, also use the --skip-lock-tables option.
1328
1329       mysqldump does not dump the INFORMATION_SCHEMA schema.
1330
1331       mysqldump does not dump InnoDB CREATE TABLESPACE statements.
1332
1333       mysqldump does not dump the NDB Cluster ndbinfo information database.
1334
1335       mysqldump includes statements to recreate the general_log and
1336       slow_query_log tables for dumps of the mysql database. Log table
1337       contents are not dumped.
1338
1339       If you encounter problems backing up views due to insufficient
1340       privileges, see Section 25.9, “Restrictions on Views” for a workaround.
1341
1343       Copyright © 1997, 2021, Oracle and/or its affiliates.
1344
1345       This documentation is free software; you can redistribute it and/or
1346       modify it only under the terms of the GNU General Public License as
1347       published by the Free Software Foundation; version 2 of the License.
1348
1349       This documentation is distributed in the hope that it will be useful,
1350       but WITHOUT ANY WARRANTY; without even the implied warranty of
1351       MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
1352       General Public License for more details.
1353
1354       You should have received a copy of the GNU General Public License along
1355       with the program; if not, write to the Free Software Foundation, Inc.,
1356       51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
1357       http://www.gnu.org/licenses/.
1358
1359

NOTES

1361        1. MySQL Shell dump utilities
1362           https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html
1363
1364        2. MySQL Shell load dump utilities
1365           https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html
1366
1367        3. here
1368           https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html
1369
1370        4. Downgrade Notes
1371           https://dev.mysql.com/doc/refman/5.7/en/downgrading-to-previous-series.html
1372

SEE ALSO

1374       For more information, please refer to the MySQL Reference Manual, which
1375       may already be installed locally and which is also available online at
1376       http://dev.mysql.com/doc/.
1377

AUTHOR

1379       Oracle Corporation (http://dev.mysql.com/).
1380
1381
1382
1383MySQL 8.0                         09/04/2021                      MYSQLDUMP(1)
Impressum