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