1MYSQLDUMP(1) MySQL Database System MYSQLDUMP(1)
2
6 mysqldump - a database backup program
7
9 mysqldump [options] [db_name [tbl_name ...]]
10
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 · Performance and Scalability Considerations
20 · Invocation Syntax
22 · Option Syntax - Alphabetical Summary
24 · Connection Options
26 · Option-File Options
28 · DDL Options
30 · Debug Options
32 · Help Options
34 · Internationalization Options
36 · Replication Options
38 · Format Options
40 · Filtering Options
42 · Performance Options
44 · Transactional Options
46 · Option Groups
48 · Examples
50 · Restrictions
52 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 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 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 Note
69 A dump made using PowerShell on Windows with output redirection
70 creates a file that has UTF-16 encoding:
71 shell> mysqldump [options] > dump.sql
73 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 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 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 · 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 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 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 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 shell> mysqldump [options] db_name [tbl_name ...]
121 shell> mysqldump [options] --databases db_name ...
122 shell> mysqldump [options] --all-databases
123 To dump entire databases, do not name any tables following db_name, or
125 use the --databases or --all-databases option.
126 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 · --bind-address=ip_address
138 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 · --compress, -C
144 Compress all information sent between the client and the server if
146 possible. See Section 4.2.6, “Connection Compression Control”.
147 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 · --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 For more information, see Section 4.2.6, “Connection Compression
158 Control”.
159 This option was added in MySQL 8.0.18.
161 · --default-auth=plugin
163 A hint about which client-side authentication plugin to use. See
165 Section 6.2.17, “Pluggable Authentication”.
166 · --enable-cleartext-plugin
168 Enable the mysql_clear_password cleartext authentication plugin.
170 (See Section 6.4.1.4, “Client-Side Cleartext Pluggable
171 Authentication”.)
172 · --get-server-public-key
174 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 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 For information about the caching_sha2_password plugin, see
189 Section 6.4.1.3, “Caching SHA-2 Pluggable Authentication”.
190 · --host=host_name, -h host_name
192 Dump data from the MySQL server on the given host. The default host
194 is localhost.
195 · --login-path=name
197 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 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 · --password[=password], -p[password]
209 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 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 To explicitly specify that there is no password and that mysqldump
222 should not prompt for one, use the --skip-password option.
223 · --pipe, -W
225 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 · --plugin-dir=dir_name
234 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 · --port=port_num, -P port_num
241 For TCP/IP connections, the port number to use.
243 · --protocol={TCP|SOCKET|PIPE|MEMORY}
245 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 · --secure-auth
253 This option was removed in MySQL 8.0.3.
255 · --server-public-key-path=file_name
257 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 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 For sha256_password, this option applies only if MySQL was built
273 using OpenSSL.
274 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 · --socket=path, -S path
280 For connections to localhost, the Unix socket file to use, or, on
282 Windows, the name of the named pipe to use.
283 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 · --ssl*
291 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 · --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 These --ssl-fips-mode values are permitted:
304 · OFF: Disable FIPS mode.
306 · ON: Enable FIPS mode.
308 · STRICT: Enable “strict” FIPS mode.
310 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 · --tls-ciphersuites=ciphersuite_list
319 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 This option was added in MySQL 8.0.16.
328 · --tls-version=protocol_list
330 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 · --user=user_name, -u user_name
338 The user name of the MySQL account to use for connecting to the
340 server.
341 · --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 For more information, see Section 4.2.6, “Connection Compression
350 Control”.
351 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 · --defaults-extra-file=file_name
357 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 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 · --defaults-file=file_name
369 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 Exception: Even with --defaults-file, client programs read
376 .mylogin.cnf.
377 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 · --defaults-group-suffix=str
383 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 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 · --no-defaults
395 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 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 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 · --print-defaults
411 Print the program name and all options that it gets from option
413 files.
414 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 · --add-drop-database
426 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 · --add-drop-table
433 Write a DROP TABLE statement before each CREATE TABLE statement.
435 · --add-drop-trigger
437 Write a DROP TRIGGER statement before each CREATE TRIGGER
439 statement.
440 · --all-tablespaces, -Y
442 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 · --no-create-db, -n
449 Suppress the CREATE DATABASE statements that are otherwise included
451 in the output if the --databases or --all-databases option is
452 given.
453 · --no-create-info, -t
455 Do not write CREATE TABLE statements that create each dumped table.
457 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 · --no-tablespaces, -y
464 This option suppresses all CREATE LOGFILE GROUP and CREATE
466 TABLESPACE statements in the output of mysqldump.
467 · --replace
469 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 · --allow-keywords
476 Permit creation of column names that are keywords. This works by
478 prefixing each column name with the table name.
479 · --comments, -i
481 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 · --debug[=debug_options], -# [debug_options]
488 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 · --debug-check
493 Print some debugging information when the program exits.
495 · --debug-info
497 Print debugging information and memory and CPU usage statistics
499 when the program exits.
500 · --dump-date
502 If the --comments option is given, mysqldump produces a comment at
504 the end of the dump of the following form:
505 -- Dump completed on DATE
507 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 · --force, -f
515 Ignore all errors; continue even if an SQL error occurs during a
517 table dump.
518 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 If the --ignore-error option is also given to ignore specific
528 errors, --force takes precedence.
529 · --log-error=file_name
531 Log warnings and errors by appending them to the named file. The
533 default is to do no logging.
534 · --skip-comments
536 See the description for the --comments option.
538 · --verbose, -v
540 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 · --help, -?
546 Display a help message and exit.
548 · --version, -V
550 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 · --character-sets-dir=dir_name
557 The directory where character sets are installed. See
559 Section 10.15, “Character Set Configuration”.
560 · --default-character-set=charset_name
562 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 · --no-set-names, -N
568 Turns off the --set-charset setting, the same as specifying
570 --skip-set-charset.
571 · --set-charset
573 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 · --apply-slave-statements
583 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 · --delete-master-logs
589 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 · --dump-slave[=value]
595 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 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 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 This option causes mysqldump to stop the slave SQL thread before
631 the dump and restart it again after.
632 In conjunction with --dump-slave, the --apply-slave-statements and
634 --include-master-host-port options can also be used.
635 · --include-master-host-port
637 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 · --master-data[=value]
643 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 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 This option requires the RELOAD privilege and the binary log must
660 be enabled.
661 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 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 · --set-gtid-purged=value
674 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 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 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 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 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 The possible values for the --set-gtid-purged option are as
752 follows:
753 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 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 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 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 · --compact
799 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 · --compatible=name
805 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 · --complete-insert, -c
813 Use complete INSERT statements that include column names.
815 · --create-options
817 Include all MySQL-specific table options in the CREATE TABLE
819 statements.
820 · --fields-terminated-by=..., --fields-enclosed-by=...,
822 --fields-optionally-enclosed-by=..., --fields-escaped-by=...
823 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 · --hex-blob
829 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 · --lines-terminated-by=...
836 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 · --quote-names, -Q
842 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 · --result-file=file_name, -r file_name
851 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 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 · --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 This option was added in MySQL 8.0.18.
868 · --tab=dir_name, -T dir_name
870 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 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 Column values are converted to the character set specified by the
891 --default-character-set option.
892 · --tz-utc
894 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 · --xml, -X
906 Write dump output as well-formed XML.
908 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 ┌─────────────────────┬────────────────────────────────────────────┐
915 │Value: │ 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 XML output from mysqldump includes the XML namespace, as shown
936 here:
937 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 · --all-databases, -A
982 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 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 · --databases, -B
1000 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 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 · --events, -E
1012 Include Event Scheduler events for the dumped databases in the
1014 output. This option requires the EVENT privileges for those
1015 databases.
1016 The output generated by using --events contains CREATE EVENT
1018 statements to create the events.
1019 · --ignore-error=error[,error]...
1021 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 · --ignore-table=db_name.tbl_name
1028 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 · --no-data, -d
1035 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 · --routines, -R
1042 Include stored routines (procedures and functions) for the dumped
1044 databases in the output. This option requires the global SELECT
1045 privilege.
1046 The output generated by using --routines contains CREATE PROCEDURE
1048 and CREATE FUNCTION statements to create the routines.
1049 · --tables
1051 Override the --databases or -B option. mysqldump regards all name
1053 arguments following the option as table names.
1054 · --triggers
1056 Include triggers for each dumped table in the output. This option
1058 is enabled by default; disable it with --skip-triggers.
1059 To be able to dump a table's triggers, you must have the TRIGGER
1061 privilege for the table.
1062 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 · --where='where_condition', -w 'where_condition'
1073 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 Examples:
1079 --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 Performance is also affected by the transactional options, primarily
1093 for the dump operation.
1094 · --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 · --disable-keys, -K
1101 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 · --extended-insert, -e
1109 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 · --insert-ignore
1115 Write INSERT IGNORE statements rather than INSERT statements.
1117 · --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 · --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 · --network-timeout, -M
1130 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 · --opt
1137 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 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 · --quick, -q
1151 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 · --skip-opt
1158 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 · --add-locks
1165 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 · --flush-logs, -F
1171 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 · --flush-privileges
1184 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 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 · --lock-all-tables, -x
1197 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 · --lock-tables, -l
1203 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 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 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 · --no-autocommit
1221 Enclose the INSERT statements for each dumped table within SET
1223 autocommit = 0 and COMMIT statements.
1224 · --order-by-primary
1226 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 · --shared-memory-base-name=name
1233 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 This option applies only if the server was started with the
1239 shared_memory system variable enabled to support shared-memory
1240 connections.
1241 · --single-transaction
1243 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 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 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 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 To dump large tables, combine the --single-transaction option with
1270 the --quick option.
1271 Option Groups
1272 · 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 · 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 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 shell> mysqldump db_name > backup-file.sql
1293 To load the dump file back into the server:
1295 shell> mysql db_name < backup-file.sql
1297 Another way to reload the dump file:
1299 shell> mysql -e "source /path-to-backup/backup-file.sql" db_name
1301 mysqldump is also very useful for populating databases by copying data
1303 from one MySQL server to another:
1304 shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
1306 You can dump several databases with one command:
1308 shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql
1310 To dump all databases, use the --all-databases option:
1312 shell> mysqldump --all-databases > all_databases.sql
1314 For InnoDB tables, mysqldump provides a way of making an online backup:
1316 shell> mysqldump --all-databases --master-data --single-transaction > all_databases.sql
1318 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 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 shell> mysqldump --all-databases --master-data=2 > all_databases.sql
1336 Or:
1338 shell> mysqldump --all-databases --flush-logs --master-data=2
1340 > all_databases.sql
1341 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 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 · 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 · 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 mysqldump does not dump the INFORMATION_SCHEMA schema.
1365 mysqldump does not dump InnoDB CREATE TABLESPACE statements.
1367 mysqldump does not dump the NDB Cluster ndbinfo information database.
1369 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 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 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 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 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
1397 1. Downgrade Notes
1398 https://dev.mysql.com/doc/refman/5.7/en/downgrading-to-previous-series.html
1399
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
1406 Oracle Corporation (http://dev.mysql.com/).
1407MySQL 8.0 09/06/2019 MYSQLDUMP(1)