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