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