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

NOTES

1397        1. Downgrade Notes
1398           https://dev.mysql.com/doc/refman/5.7/en/downgrading-to-previous-series.html
1399

SEE ALSO

1401       For more information, please refer to the MySQL Reference Manual, which
1402       may already be installed locally and which is also available online at
1403       http://dev.mysql.com/doc/.
1404

AUTHOR

1406       Oracle Corporation (http://dev.mysql.com/).
1407
1408
1409
1410MySQL 8.0                         09/06/2019                      MYSQLDUMP(1)
Impressum