mysqldump [options] [db_name [tbl_name …]]
If you have tables that contain generated columns, use the mysqldump utility
provided with MySQL 5.7.9 or higher to create your dump files. The mysqldump
utility provided in earlier releases uses incorrect syntax for generated column
definitions (Bug #20769542). You can use the INFORMATION_SCHEMA.COLUMNS table to
identify tables with generated columns.
dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the –single-transaction
option is not used. Certain options might require other privileges as noted in the
that it contains, such as the appropriate CREATE privileges for objects created by
collation. These may be used when dumping stored programs to preserve their character
encodings. To reload a dump file containing such statements, the ALTER privilege for
the affected database is required.
A dump made using PowerShell on Windows with output redirection creates a file
that has UTF-16 encoding:
Section 11.1.5, “Connection Character Sets and Collations”), so the dump file will
not load correctly. To work around this issue, use the –result-file option, which
creates the output in ASCII format:
Performance and Scalability Considerations
mysqldump advantages include the
You can clone databases for development and DBA work, or produce slight variations of
an existing database for testing. It is not intended as a fast or scalable solution
for backing up substantial amounts of data. With large data sizes, even if the backup
step takes a reasonable time, restoring the data can be very slow because replaying
the SQL statements involves disk I/O for insertion, index creation, and so on.
data files in their original format that can be restored quickly:
Backup product. (Available as part of the Enterprise subscription.) It provides
the best performance for InnoDB backups with minimal disruption; it can also back
up tables from MyISAM and other storage engines; and it provides a number of
convenient options to accommodate different backup scenarios. See Section 27.2,
“MySQL Enterprise Backup Overview”.
entire content from a table and buffer it in memory before dumping it. Buffering in
memory can be a problem if you are dumping large tables. To dump tables row by row,
use the –quick option (or –opt, which enables –quick). The –opt option (and hence
–quick) is enabled by default, so to enable memory buffering, use –skip-quick.
a very old MySQL server, use the –skip-opt option instead of the –opt or
order to dump a set of one or more tables, a set of one or more complete databases, or
an entire MySQL server—as shown here:
shell> mysqldump [options] –databases db_name …
shell> mysqldump [options] –all-databases
–databases or –all-databases option.
Option Syntax – Alphabetical Summary
following options, which can be specified on the command line or in the [mysqldump]
and [client] groups of an option file. For information about option files used by
MySQL programs, see Section 5.2.6, “Using Option Files”.
option file. If the file does not exist or is otherwise inaccessible, an error
occurs. file_name is interpreted relative to the current directory if given as a
relative path name rather than a full path name.
inaccessible, an error occurs. file_name is interpreted relative to the current
directory if given as a relative path name rather than a full path name.
suffix of str. For example, mysqldump normally reads the [client] and [mysqldump]
groups. If the –defaults-group-suffix=_other option is given, mysqldump also
reads the [client_other] and [mysqldump_other] groups.
options from an option file, –no-defaults can be used to prevent them from being
This permits passwords to be specified in a safer way than on the command line
even when –no-defaults is used. (.mylogin.cnf is created by the
mysql_config_editor utility. See mysql_config_editor(1).)
information in the dump file, or let the dump operation proceed regardless of
column name with the table name.
version, and host. This option is enabled by default. To suppress this additional
information, use –skip-comments.
default value is d:t:o,/tmp/mysqldump.trace.
dump of the following form:
different, even if the data are otherwise identical. –dump-date and
–skip-dump-date control whether the date is added to the comment. The default is
–dump-date (include the date in the comment). –skip-dump-date suppresses date
encounters a view that has become invalid because the definition refers to a table
that has been dropped. Without –force, mysqldump exits with an error message.
With –force, mysqldump prints the error message, but it also writes an SQL
comment containing the view definition to the dump output and continues executing.
represents character data with national language settings.
Configuration”. If no character set is specified, mysqldump uses utf8.
default. To suppress the SET NAMES statement, use –skip-set-charset.
instance, or an instance including data, on a slave server in a replication
configuration. The following options apply to dumping and restoring data on
replication master and slave servers.
before the CHANGE MASTER TO statement and a START SLAVE statement at the end of
LOGS statement to the server after performing the dump operation. This option
automatically enables –master-data.
replication slave server to produce a dump file that can be used to set up another
server as a slave that has the same master as the dumped server. It causes the
dump output to include a CHANGE MASTER TO statement that indicates the binary log
coordinates (file name and position) of the dumped slave’s master. The CHANGE
MASTER TO statement reads the values of Relay_Master_Log_File and
Exec_Master_Log_Pos from the SHOW SLAVE STATUS output and uses them for
MASTER_LOG_FILE and MASTER_LOG_POS respectively. These are the master server
coordinates from which the slave should start replicating.
Inconsistencies in the sequence of transactions from the relay log which have
been executed can cause the wrong position to be used. See Section 22.214.171.124,
“Replication and Transaction Inconsistencies” for more information.
of the dumped server, as is done by the –master-data option. In addition,
specfiying this option causes the –master-data option to be overridden, if used,
and effectively ignored.
This option should not be used if the server where the dump is going to be
applied uses gtid_mode=ON and MASTER_AUTOPOSITION=1.
1 causes a CHANGE MASTER TO statement to be written to the dump, setting 2 causes
the statement to be written but encased in SQL comments) and has the same effect
as –master-data in terms of enabling or disabling other options and in how
locking is handled.
restart it again after.
–include-master-host-port options can also be used.
option, add MASTER_HOST and MASTER_PORT options for the host name and TCP/IP port
number of the slave’s master.
can be used to set up another server as a slave of the master. It causes the dump
output to include a CHANGE MASTER TO statement that indicates the binary log
coordinates (file name and position) of the dumped server. These are the master
server coordinates from which the slave should start replicating after you load
the dump file into the slave.
comment, and thus is informative only; it has no effect when the dump file is
reloaded. If the option value is 1, the statement is not written as a comment and
takes effect when the dump file is reloaded. If no option value is specified, the
default value is 1.
–lock-all-tables, unless –single-transaction also is specified, in which case, a
global read lock is acquired only for a short time at the beginning of the dump
(see the description for –single-transaction). In all cases, any action on logs
happens at the exact moment of the dump.
using the –dump-slave option, which overrides –master-data and causes it to be
ignored if both options are used.
to the dump file, by indicating whether to add a SET @@global.gtid_purged
statement to the output. This option may also cause a statement to be written to
the output that disables binary logging while the dump file is being reloaded.
│Value │ Meaning │
│OFF │ Add no SET statement to the │
│ │ output. │
│ON │ Add a SET statement to the │
│ │ output. An error occurs if │
│ │ GTIDs are │
│ │ not enabled on the server. │
│AUTO │ Add a SET statement to the │
│ │ output if GTIDs are │
│ │ enabled on │
│ │ the server. │
The –set-gtid-purged option has the following effect on binary logging when the
dump file is reloaded:
or certain kinds of data in the dump file. They also control whether certain optional
information is written to the dump file.
–skip-add-locks, –skip-comments, –skip-disable-keys, and –skip-set-charset
MySQL servers. The value of name can be ansi, mysql323, mysql40, postgresql,
oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options.
To use several values, separate them by commas. These values have the same meaning
as the corresponding options for setting the server SQL mode. See Section 6.1.8,
“Server SQL Modes”.
those SQL mode values that are currently available for making dump output more
compatible. For example, –compatible=oracle does not map data types to Oracle
types or use Oracle comment syntax.
corresponding FIELDS clauses for LOAD DATA INFILE. See Section 14.2.6, “LOAD DATA
0x616263). The affected data types are BINARY, VARBINARY, the BLOB types, and BIT.
corresponding LINES clause for LOAD DATA INFILE. See Section 14.2.6, “LOAD DATA
characters. If the ANSI_QUOTES SQL mode is enabled, identifiers are quoted within
“”” characters. This option is enabled by default. It can be disabled with
–skip-quote-names, but this option should be given after any option such as
–compatible that may enable –quote-names.
contents overwritten, even if an error occurs while generating the dump.
being converted to “\r\n” carriage return/newline sequences.
creates a tbl_name.sql file that contains the CREATE TABLE statement that creates
the table, and the server writes a tbl_name.txt file that contains its data. The
option value is the directory in which to write the files.
This option should be used only when mysqldump is run on the same machine as
the mysqld server. Because the server creates files *.txt file in the
directory that you specify, the directory must be writable by the server and
the MySQL account that you use must have the FILE privilege. Because mysqldump
creates *.sql in the same directory, it must be writable by your system login
values and a newline at the end of each line. The format can be specified
explicitly using the –fields-xxx and –lines-terminated-by options.
different time zones. mysqldump sets its connection time zone to UTC and adds SET
TIME_ZONE=’+00:00′ to the dump file. Without this option, TIMESTAMP columns are
dumped and reloaded in the time zones local to the source and destination servers,
which can cause the values to change if the servers are in different time zones.
–tz-utc also protects against changes due to daylight saving time. –tz-utc is
enabled by default. To disable it, use –skip-tz-utc.
empty string, and the string value ‘NULL’ are distinguished from one another in
the output generated by this option as follows.
│Value: │ XML Representation: │
│NULL (unknown value) │ <field name=”column_name” │
│ │ xsi:nil=”true” /> │
│” (empty string) │ <field │
│ │ name=”column_name”></field> │
│’NULL’ (string value) │ <field │
│ │ name=”column_name”>NULL</field> │
The output from the mysql client when run using the –xml option also follows the
preceding rules. (See the section called “MYSQL OPTIONS”.)
<field Field=”ID” Type=”int(11)” Null=”NO” Key=”PRI” Extra=”auto_increment” />
<field Field=”Name” Type=”char(35)” Null=”NO” Key=”” Default=”” Extra=”” />
<field Field=”CountryCode” Type=”char(3)” Null=”NO” Key=”” Default=”” Extra=”” />
<field Field=”District” Type=”char(20)” Null=”NO” Key=”” Default=”” Extra=”” />
<field Field=”Population” Type=”int(11)” Null=”NO” Key=”” Default=”0″ Extra=”” />
<key Table=”City” Non_unique=”0″ Key_name=”PRIMARY” Seq_in_index=”1″ Column_name=”ID”
Collation=”A” Cardinality=”4079″ Null=”” Index_type=”BTREE” Comment=”” />
<options Name=”City” Engine=”MyISAM” Version=”10″ Row_format=”Fixed” Rows=”4079″
Avg_row_length=”67″ Data_length=”273293″ Max_data_length=”18858823439613951″
Index_length=”43008″ Data_free=”0″ Auto_increment=”4080″
Create_time=”2007-03-31 01:47:01″ Update_time=”2007-03-31 01:47:02″
Collation=”latin1_swedish_ci” Create_options=”” Comment=”” />
particularly of the restore operations. For large data sets, restore operation
(processing the INSERT statements in the dump file) is the most time-consuming part.
When it is urgent to restore data quickly, plan and test the performance of this stage
in advance. For restore times measured in hours, you might prefer an alternative
backup and restore solution, such as MySQL Enterprise Backup for InnoDB-only and
DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; statements.
This makes loading the dump file faster because the indexes are created after all
rows are inserted. This option is effective only for nonunique indexes of MyISAM
lists. This results in a smaller dump file and speeds up inserts when the file is
–add-drop-table –add-locks –create-options –disable-keys –extended-insert
–lock-tables –quick –set-charset. It gives a fast dump operation and produces a
dump file that can be reloaded into a MySQL server quickly.
–skip-opt to turn off several default settings. See the discussion of mysqldump
option groups for information about selectively enabling or disabling a subset of
the options affected by –opt.
rows for a table from the server a row at a time rather than retrieving the entire
row set and buffering it in memory before writing it out.
operation, against the reliability and consistency of the exported data.
results in faster inserts when the dump file is reloaded. See Section 126.96.36.199,
“Speed of INSERT Statements”.
the RELOAD privilege. If you use this option in combination with the
–all-databases option, the logs are flushed for each database dumped. The
exception is when using –lock-all-tables, –master-data, or –single-transaction:
In this case, the logs are flushed only once, corresponding to the moment that all
tables are locked. If you want your dump and the log flush to happen at exactly
the same moment, you should use –flush-logs together with –lock-all-tables,
–master-data, or –single-transaction.
database. This option should be used any time the dump contains the mysql database
and any other database that depends on the data in the mysql database for proper
For upgrades to MySQL 5.7.2 or higher from older versions, do not use
–flush-privileges. For upgrade instructions in this case, see
Section 188.8.131.52, “Changes Affecting Upgrades to MySQL 5.7”.
lock for the duration of the whole dump. This option automatically turns off
–single-transaction and –lock-tables.
tables are locked with READ LOCAL to permit concurrent inserts in the case of
MyISAM tables. For transactional tables such as InnoDB, –single-transaction is a
much better option than –lock-tables because it does not need to lock the tables
not guarantee that the tables in the dump file are logically consistent between
databases. Tables in different databases may be dumped in completely different
override this, use –skip-lock-tables at the end of the option list.
such an index exists. This is useful when dumping a MyISAM table to be loaded into
an InnoDB table, but makes the dump operation take considerably longer.
memory to a local server. The default value is MYSQL. The shared-memory name is
START TRANSACTION SQL statement to the server before dumping data. It is useful
only with transactional tables such as InnoDB, because then it dumps the
consistent state of the database at the time when START TRANSACTION was issued
without blocking any applications.
in a consistent state. For example, any MyISAM or MEMORY tables dumped while using
this option may still change state.
(correct table contents and binary log coordinates), no other connection should
use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of
them on a table to be dumped can cause the SELECT that is performed by mysqldump
to retrieve the table contents to obtain incorrect contents or fail.
exclusive because LOCK TABLES causes any pending transactions to be committed
default. Thus you rarely if ever specify –opt. Instead, you can turn these
settings off as a group by specifying –skip-opt, the optionally re-enable certain
settings by specifying the associated options later on the command line.
with other options that re-enable certain settings, or turn all the settings on by
using the –skip-compact form.
important because options are processed first to last. For example, –disable-keys
–lock-tables –skip-opt would not have the intended effect; it is the same as
–skip-opt by itself.
server to another:
LOCK) at the beginning of the dump. As soon as this lock has been acquired, the binary
log coordinates are read and the lock is released. If long updating statements are
running when the FLUSH statement is issued, the MySQL server may get stalled until
those statements finish. After that, the dump becomes lock free and does not disturb
reads and writes on the tables. If the update statements that the MySQL server
receives are short (in terms of execution time), the initial lock period should not be
noticeable, even with many updates.
old backup and replay the changes that happened since that backup), it is often useful
to rotate the binary log (see Section 6.4.4, “The Binary Log”) or at least know the
binary log coordinates to which the dump corresponds:
provides a convenient way to make an online backup suitable for use prior to
point-in-time recovery if tables are stored using the InnoDB storage engine.
and Section 8.3, “Example Backup and Recovery Strategy”.
–skip-extended-insert –skip-quick. (Actually, –skip-extended-insert
–skip-quick is sufficient because –opt is on by default.)
(as of MySQL 5.7.8) sys schema by default. To dump any of these, name them explicitly
on the command line. You can also name them with the –databases option. For
INFORMATION_SCHEMA and performance_schema, also use the –skip-lock-tables option.
earlier server that has GTIDs enabled. See Section 184.108.40.206, “Restrictions on
Replication with GTIDs”.
for dumps of the mysql database. Log table contents are not dumped.
Section C.5, “Restrictions on Views” for a workaround.
- man 1 mysqldump，version 10.13 Distrib 5.7.16, for linux-glibc2.5 (x86_64)
- MySQL 5.7 Reference Manual/mysqldump: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html