.\" ** You probably do not want to edit this file directly **
.\" It was generated using the DocBook XSL Stylesheets (version 1.69.1).
.\" Instead of manually editing it, you probably should edit the DocBook XML
.\" source for it and then use the DocBook XSL Stylesheets to regenerate it.
.TH "\\FBMYSQLDUMP\\FR" "1" "07/19/2006" "MySQL 4.1" "MySQL Database System"
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.SH "NAME"
mysqldump \- a database backup program
.SH "SYNOPSIS"
.HP 45
\fBmysqldump [\fR\fB\fIoptions\fR\fR\fB] [\fR\fB\fIdb_name\fR\fR\fB [\fR\fB\fItbl_name\fR\fR\fB ...]]\fR
.SH "DESCRIPTION"
.PP
The
\fBmysqldump\fR
client is a backup program originally written by Igor Romanenko. It can be used to dump a database or a collection of databases for backup or for transferring the data to another SQL server (not necessarily a MySQL server). The dump contains SQL statements to create the table or populate it, or both.
.PP
If you are doing a backup on the server, and your tables all are
MyISAM
tables, consider using the
\fBmysqlhotcopy\fR
instead because it can accomplish faster backups and faster restores. See
\fBmysqlhotcopy\fR(1).
.PP
There are three general ways to invoke
\fBmysqldump\fR:
.sp
.nf
shell> \fBmysqldump [\fR\fB\fIoptions\fR\fR\fB] \fR\fB\fIdb_name\fR\fR\fB [\fR\fB\fItables\fR\fR\fB]\fR
shell> \fBmysqldump [\fR\fB\fIoptions\fR\fR\fB] \-\-databases \fR\fB\fIdb_name1\fR\fR\fB [\fR\fB\fIdb_name2\fR\fR\fB \fR\fB\fIdb_name3\fR\fR\fB...]\fR
shell> \fBmysqldump [\fR\fB\fIoptions\fR\fR\fB] \-\-all\-databases\fR
.fi
.PP
If you do not name any tables following
\fIdb_name\fR
or if you use the
\fB\-\-databases\fR
or
\fB\-\-all\-databases\fR
option, entire databases are dumped.
.PP
To get a list of the options your version of
\fBmysqldump\fR
supports, execute
\fBmysqldump \-\-help\fR.
.PP
If you run
\fBmysqldump\fR
without the
\fB\-\-quick\fR
or
\fB\-\-opt\fR
option,
\fBmysqldump\fR
loads the whole result set into memory before dumping the result. This can be a problem if you are dumping a big database. As of MySQL 4.1,
\fB\-\-opt\fR
is enabled by default, but can be disabled with
\fB\-\-skip\-opt\fR.
.PP
If you are using a recent copy of the
\fBmysqldump\fR
program to generate a dump to be reloaded into a very old MySQL server, you should not use the
\fB\-\-opt\fR
or
\fB\-\-extended\-insert\fR
option. Use
\fB\-\-skip\-opt\fR
instead.
.PP
Before MySQL 4.1.2, out\-of\-range numeric values such as
\-inf
and
inf, as well as
NaN
(not\-a\-number) values are dumped by
\fBmysqldump\fR
as
NULL. You can see this using the following sample table:
.sp
.nf
mysql> \fBCREATE TABLE t (f DOUBLE);\fR
mysql> \fBINSERT INTO t VALUES(1e+111111111111111111111);\fR
mysql> \fBINSERT INTO t VALUES(\-1e111111111111111111111);\fR
mysql> \fBSELECT f FROM t;\fR
+\-\-\-\-\-\-+
| f    |
+\-\-\-\-\-\-+
|  inf |
| \-inf |
+\-\-\-\-\-\-+
.fi
.PP
For this table,
\fBmysqldump\fR
produces the following data output:
.sp
.nf
\-\-
\-\- Dumping data for table `t`
\-\-
INSERT INTO t VALUES (NULL);
INSERT INTO t VALUES (NULL);
.fi
.PP
The significance of this behavior is that if you dump and restore the table, the new table has contents that differ from the original contents. This problem is fixed as of MySQL 4.1.2; you cannot insert
inf
in the table, so this
\fBmysqldump\fR
behavior is only relevant when you deal with old servers.
.PP
\fBmysqldump\fR
supports the following options:
.TP 3
\(bu
\fB\-\-help\fR,
\fB\-?\fR
.sp
Display a help message and exit.
.TP
\(bu
\fB\-\-add\-drop\-database\fR
.sp
Add a
DROP DATABASE
statement before each
CREATE DATABASE
statement. Added in MySQL 4.1.13.
.TP
\(bu
\fB\-\-add\-drop\-table\fR
.sp
Add a
DROP TABLE
statement before each
CREATE TABLE
statement.
.TP
\(bu
\fB\-\-add\-locks\fR
.sp
Surround each table dump with
LOCK TABLES
and
UNLOCK TABLES
statements. This results in faster inserts when the dump file is reloaded. See
Section\ 2.13, \(lqSpeed of INSERT Statements\(rq.
.TP
\(bu
\fB\-\-all\-databases\fR,
\fB\-A\fR
.sp
Dump all tables in all databases. This is the same as using the
\fB\-\-databases\fR
option and naming all the databases on the command line.
.TP
\(bu
\fB\-\-allow\-keywords\fR
.sp
Allow creation of column names that are keywords. This works by prefixing each column name with the table name.
.TP
\(bu
\fB\-\-character\-sets\-dir=\fR\fB\fIpath\fR\fR
.sp
The directory where character sets are installed. See
Section\ 9.1, \(lqThe Character Set Used for Data and Sorting\(rq.
.TP
\(bu
\fB\-\-comments\fR,
\fB\-i\fR
.sp
Write additional information in the dump file such as program version, server version, and host. . This option is enabled by default. To suppress additional, use
\fB\-\-skip\-comments\fR. This option was added in MySQL 4.0.17.
.TP
\(bu
\fB\-\-compact\fR
.sp
Produce less verbose output. This option suppresses comments and enables the
\fB\-\-skip\-add\-drop\-table\fR,
\fB\-\-no\-set\-names\fR,
\fB\-\-skip\-disable\-keys\fR, and
\fB\-\-skip\-add\-locks\fR
options. Added in MySQL 4.1.2.
.TP
\(bu
\fB\-\-compatible=\fR\fB\fIname\fR\fR
.sp
Produce output that is more compatible with other database systems or with older 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
the section called \(lqTHE SERVER SQL MODE\(rq.
.sp
This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently available for making dump output more compatible. For example,
\fB\-\-compatible=oracle\fR
does not map data types to Oracle types or use Oracle comment syntax.
.sp
\fIThis option requires a server version of 4.1.0 or higher\fR. With older servers, it does nothing.
.TP
\(bu
\fB\-\-complete\-insert\fR,
\fB\-c\fR
.sp
Use complete
INSERT
statements that include column names.
.TP
\(bu
\fB\-\-compress\fR,
\fB\-C\fR
.sp
Compress all information sent between the client and the server if both support compression.
.TP
\(bu
\fB\-\-create\-options\fR
.sp
Include all MySQL\-specific table options in the
CREATE TABLE
statements. Before MySQL 4.1.2, use
\fB\-\-all\fR
instead.
.TP
\(bu
\fB\-\-databases\fR,
\fB\-B\fR
.sp
Dump several databases. Normally,
\fBmysqldump\fR
treats the first name argument on the command line as a database name and following names as table names. With this option, it treats all name arguments as database names.
CREATE DATABASE
and
USE
statements are included in the output before each new database.
.TP
\(bu
\fB\-\-debug[=\fR\fB\fIdebug_options\fR\fR\fB]\fR,
\fB\-# [\fR\fB\fIdebug_options\fR\fR\fB]\fR
.sp
Write a debugging log. The
\fIdebug_options\fR
string is often
\'d:t:o,\fIfile_name\fR'. The default is
\'d:t:o,/tmp/mysqldump.trace'.
.TP
\(bu
\fB\-\-default\-character\-set=\fR\fB\fIcharset_name\fR\fR
.sp
Use
\fIcharset_name\fR
as the default character set. See
Section\ 9.1, \(lqThe Character Set Used for Data and Sorting\(rq. If not specified,
\fBmysqldump\fR
from MySQL 4.1.2 or later uses
utf8, and earlier versions use
latin1.
.TP
\(bu
\fB\-\-delayed\-insert\fR
.sp
Write
INSERT DELAYED
statements rather than
INSERT
statements.
.TP
\(bu
\fB\-\-delete\-master\-logs\fR
.sp
On a master replication server, delete the binary logs after performing the dump operation. This option automatically enables
\fB\-\-first\-slave\fR
before MySQL 4.1.8 and enables
\fB\-\-master\-data\fR
thereafter. It was added in MySQL 3.23.57 (for MySQL 3.23) and MySQL 4.0.13 (for MySQL 4.0).
.TP
\(bu
\fB\-\-disable\-keys\fR,
\fB\-K\fR
.sp
For each table, surround the
INSERT
statements with
/*!40000 ALTER TABLE \fItbl_name\fR DISABLE KEYS */;
and
/*!40000 ALTER TABLE \fItbl_name\fR ENABLE KEYS */;
statements. This makes loading the dump file into a MySQL 4.0 or newer server faster because the indexes are created after all rows are inserted. This option is effective for
MyISAM
tables only.
.TP
\(bu
\fB\-\-extended\-insert\fR,
\fB\-e\fR
.sp
Use multiple\-row
INSERT
syntax that include several
VALUES
lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.
.TP
\(bu
\fB\-\-fields\-terminated\-by=...\fR,
\fB\-\-fields\-enclosed\-by=...\fR,
\fB\-\-fields\-optionally\-enclosed\-by=...\fR,
\fB\-\-fields\-escaped\-by=...\fR,
\fB\-\-lines\-terminated\-by=...\fR
.sp
These options are used with the
\fB\-T\fR
option and have the same meaning as the corresponding clauses for
LOAD DATA INFILE. See
Section\ 2.5, \(lqLOAD DATA INFILE Syntax\(rq.
.TP
\(bu
\fB\-\-first\-slave\fR,
\fB\-x\fR
.sp
Deprecated. Renamed to
\fB\-\-lock\-all\-tables\fR
in MySQL 4.1.8.
.TP
\(bu
\fB\-\-flush\-logs\fR,
\fB\-F\fR
.sp
Flush the MySQL server log files before starting the dump. This option requires the
RELOAD
privilege. Note that if you use this option in combination with the
\fB\-\-all\-databases\fR
(or
\fB\-A\fR) option, the logs are flushed
\fIfor each database dumped\fR. The exception is when using
\fB\-\-lock\-all\-tables\fR
or
\fB\-\-master\-data\fR: 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
\fB\-\-flush\-logs\fR
together with either
\fB\-\-lock\-all\-tables\fR
or
\fB\-\-master\-data\fR.
.TP
\(bu
\fB\-\-force\fR,
\fB\-f\fR
.sp
Continue even if an SQL error occurs during a table dump.
.TP
\(bu
\fB\-\-host=\fR\fB\fIhost_name\fR\fR,
\fB\-h \fR\fB\fIhost_name\fR\fR
.sp
Dump data from the MySQL server on the given host. The default host is
localhost.
.TP
\(bu
\fB\-\-hex\-blob\fR
.sp
Dump binary columns using hexadecimal notation (for example,
\'abc'
becomes
0x616263). The affected data types are
BINARY,
VARBINARY, and
BLOB
in MySQL 4.1 and up, and
CHAR BINARY,
VARCHAR BINARY, and
BLOB
in MySQL 4.0. This option was added in MySQL 4.0.23 and 4.1.8.
.TP
\(bu
\fB\-\-ignore\-table=\fR\fB\fIdb_name.tbl_name\fR\fR
.sp
Do not dump the given table, which must be specified using both the database and table names. To ignore multiple tables, use this option multiple times. This option was added in MySQL 4.1.9.
.TP
\(bu
\fB\-\-insert\-ignore\fR
.sp
Write
INSERT
statements with the
IGNORE
option. This option was added in MySQL 4.1.12.
.TP
\(bu
\fB\-\-lock\-all\-tables\fR,
\fB\-x\fR
.sp
Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off
\fB\-\-single\-transaction\fR
and
\fB\-\-lock\-tables\fR. Added in MySQL 4.1.8.
.TP
\(bu
\fB\-\-lock\-tables\fR,
\fB\-l\fR
.sp
Lock all tables before starting the dump. The tables are locked with
READ LOCAL
to allow concurrent inserts in the case of
MyISAM
tables. For transactional tables such as
InnoDB
and
BDB,
\fB\-\-single\-transaction\fR
is a much better option, because it does not need to lock the tables at all.
.sp
Please note that when dumping multiple databases,
\fB\-\-lock\-tables\fR
locks tables for each database separately. So, this option does not guarantee that the tables in the dump file are logically consistent between databases. Tables in different databases may be dumped in completely different states.
.TP
\(bu
\fB\-\-master\-data[=\fR\fB\fIvalue\fR\fR\fB]\fR
.sp
Write the binary log filename and position to the output. This option requires the
RELOAD
privilege and the binary log must be enabled. If the option value is equal to 1, the position and filename are written to the dump output in the form of a
CHANGE MASTER
statement that makes a slave server start from the correct position in the master's binary logs if you use this SQL dump of the master to set up a slave. If the option value is equal to 2, the
CHANGE MASTER
statement is written as an SQL comment. This is the default action if
\fIvalue\fR
is omitted.
\fIvalue\fR
may be given as of MySQL 4.1.8; before that, do not specify an option value.
.sp
The
\fB\-\-master\-data\fR
option turns on
\fB\-\-lock\-all\-tables\fR, unless
\fB\-\-single\-transaction\fR
also is specified (in which case, a global read lock is only acquired a short time at the beginning of the dump. See also the description for
\fB\-\-single\-transaction\fR. In all cases, any action on logs happens at the exact moment of the dump. This option automatically turns off
\fB\-\-lock\-tables\fR.
.TP
\(bu
\fB\-\-no\-autocommit\fR
.sp
Enclose the
INSERT
statements for each dumped table within
SET AUTOCOMMIT=0
and
COMMIT
statements.
.TP
\(bu
\fB\-\-no\-create\-db\fR,
\fB\-n\fR
.sp
This option suppresses the
CREATE DATABASE
statements that are otherwise included in the output if the
\fB\-\-databases\fR
or
\fB\-\-all\-databases\fR
option is given.
.TP
\(bu
\fB\-\-no\-create\-info\fR,
\fB\-t\fR
.sp
Do not write
CREATE TABLE
statements that re\-create each dumped table.
.TP
\(bu
\fB\-\-no\-data\fR,
\fB\-d\fR
.sp
Do not write any row information for the table. This is very useful if you want to dump only the
CREATE TABLE
statement for the table.
.TP
\(bu
\fB\-\-opt\fR
.sp
This option is shorthand; it is the same as specifying
\fB\-\-add\-drop\-table \-\-add\-locks \-\-create\-options \-\-disable\-keys \-\-extended\-insert \-\-lock\-tables \-\-quick \-\-set\-charset\fR. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly.
.sp
\fIAs of MySQL 4.1, \fR\fI\fB\-\-opt\fR\fR\fI is enabled by default. To disable the options that it enables, use \fR\fI\fB\-\-skip\-opt\fR\fR. To disable only certain of the options enabled by
\fB\-\-opt\fR, use their
\fB\-\-skip\fR
forms; for example,
\fB\-\-skip\-add\-drop\-table\fR
or
\fB\-\-skip\-quick\fR. Alternatively, use
\fB\-\-skip\-opt\fR
to disable the options enabled by
\fB\-\-opt\fR, followed by options to enable the features that you want. Options are processed in order, so the options to enable features must follow
\fB\-\-skip\-opt\fR. For example,
\fB\-\-skip\-opt \-\-extended\-insert\fR
enables extended inserts, but
\fB \-\-extended\-insert \-\-skip\-opt\fR
does not.
.TP
\(bu
\fB\-\-order\-by\-primary\fR
.sp
Sorts each table's rows by its primary key, or its first unique index, if such an index exists. This is useful when dumping a
MyISAM
table to be loaded into an
InnoDB
table, but will make the dump itself take considerably longer. This option was added in MySQL 4.1.8.
.TP
\(bu
\fB\-\-password[=\fR\fB\fIpassword\fR\fR\fB]\fR,
\fB\-p[\fR\fB\fIpassword\fR\fR\fB]\fR
.sp
The password to use when connecting to the server. If you use the short option form (\fB\-p\fR), you
\fIcannot\fR
have a space between the option and the password. If you omit the
\fIpassword\fR
value following the
\fB\-\-password\fR
or
\fB\-p\fR
option on the command line, you are prompted for one.
.sp
Specifying a password on the command line should be considered insecure. See
Section\ 7.6, \(lqKeeping Your Password Secure\(rq.
.TP
\(bu
\fB\-\-port=\fR\fB\fIport_num\fR\fR,
\fB\-P \fR\fB\fIport_num\fR\fR
.sp
The TCP/IP port number to use for the connection.
.TP
\(bu
\fB\-\-protocol={TCP|SOCKET|PIPE|MEMORY}\fR
.sp
The connection protocol to use. Added in MySQL 4.1.
.TP
\(bu
\fB\-\-quick\fR,
\fB\-q\fR
.sp
This option is useful for dumping large tables. It forces
\fBmysqldump\fR
to retrieve 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.
.TP
\(bu
\fB\-\-quote\-names\fR,
\fB\-Q\fR
.sp
Quote database, table, and column names within \(oq`\(cq characters. If the
ANSI_QUOTES
SQL mode is enabled, names are quoted within \(oq"\(cq characters. As of MySQL 4.1.1,
\fB\-\-quote\-names\fR
is enabled by default. It can be disabled with
\fB\-\-skip\-quote\-names\fR, but this option should be given after any option such as
\fB\-\-compatible\fR
that may enable
\fB\-\-quote\-names\fR.
.TP
\(bu
\fB\-\-result\-file=\fR\fB\fIfile\fR\fR,
\fB\-r \fR\fB\fIfile\fR\fR
.sp
Direct output to a given file. This option should be used on Windows to prevent newline \(oq\\n\(cq characters from being converted to \(oq\\r\\n\(cq carriage return/newline sequences.
.TP
\(bu
\fB\-\-set\-charset\fR
.sp
Add
SET NAMES \fIdefault_character_set\fR
to the output. This option is enabled by default. To suppress the
SET NAMES
statement, use
\fB\-\-skip\-set\-charset\fR. This option was added in MySQL 4.1.2.
.TP
\(bu
\fB\-\-single\-transaction\fR
.sp
This option issues a
BEGIN
SQL statement before dumping data from the server. It is useful only with transactional tables such as
InnoDB
and
BDB, because then it dumps the consistent state of the database at the time when
BEGIN
was issued without blocking any applications.
.sp
When using this option, you should keep in mind that only
InnoDB
tables are dumped in a consistent state. For example, any
MyISAM
or
MEMORY
tables dumped while using this option may still change state.
.sp
The
\fB\-\-single\-transaction\fR
option was added in MySQL 4.0.2. This option is mutually exclusive with the
\fB\-\-lock\-tables\fR
option, because
LOCK TABLES
causes any pending transactions to be committed implicitly.
.sp
This option is not supported for MySQL Cluster tables; the results cannot be guaranteed to be consistent due to the fact that the
NDBCluster
storage engine supports only the
READ_COMMITTED
transaction isolation level. You should always use
NDB
backup and restore instead.
.sp
To dump big tables, you should combine this option with
\fB\-\-quick\fR.
.TP
\(bu
\fB\-\-skip\-opt\fR
.sp
See the description for the
\fB\-\-opt\fR
option.
.TP
\(bu
\fB\-\-socket=\fR\fB\fIpath\fR\fR,
\fB\-S \fR\fB\fIpath\fR\fR
.sp
For connections to
localhost, the Unix socket file to use, or, on Windows, the name of the named pipe to use.
.TP
\(bu
\fB\-\-skip\-comments\fR
.sp
See the description for the
\fB\-\-comments\fR
option.
.TP
\(bu
\fB\-\-ssl*\fR
.sp
Options that begin with
\fB\-\-ssl\fR
specify whether to connect to the server via SSL and indicate where to find SSL keys and certificates. See
Section\ 7.7.3, \(lqSSL Command Options\(rq.
.TP
\(bu
\fB\-\-tab=\fR\fB\fIpath\fR\fR,
\fB\-T \fR\fB\fIpath\fR\fR
.sp
Produce tab\-separated data files. For each dumped table,
\fBmysqldump\fR
creates a
\fI\fItbl_name\fR\fR\fI.sql\fR
file that contains the
CREATE TABLE
statement that creates the table, and a
\fI\fItbl_name\fR\fR\fI.txt\fR
file that contains its data. The option value is the directory in which to write the files.
.sp
By default, the
\fI.txt\fR
data files are formatted using tab characters between column values and a newline at the end of each line. The format can be specified explicitly using the
\fB\-\-fields\-\fR\fB\fIxxx\fR\fR
and
\fB\-\-lines\-\-\fR\fB\fIxxx\fR\fR
options.
.sp
\fBNote\fR: This option should be used only when
\fBmysqldump\fR
is run on the same machine as the
\fBmysqld\fR
server. You must have the
FILE
privilege, and the server must have permission to write files in the directory that you specify.
.TP
\(bu
\fB\-\-tables\fR
.sp
Override the
\fB\-\-databases\fR
or
\fB\-B\fR
option. All name arguments following the option are regarded as table names.
.TP
\(bu
\fB\-\-user=\fR\fB\fIuser_name\fR\fR,
\fB\-u \fR\fB\fIuser_name\fR\fR
.sp
The MySQL username to use when connecting to the server.
.TP
\(bu
\fB\-\-verbose\fR,
\fB\-v\fR
.sp
Verbose mode. Print more information about what the program does.
.TP
\(bu
\fB\-\-version\fR,
\fB\-V\fR
.sp
Display version information and exit.
.TP
\(bu
\fB\-\-where='\fR\fB\fIwhere_condition\fR\fR\fB'\fR,
\fB\-w '\fR\fB\fIwhere_condition\fR\fR\fB'\fR
.sp
Dump only rows selected by the given
WHERE
condition. Note that quotes around the condition are mandatory if it contains spaces or other characters that are special to your command interpreter.
.sp
Examples:
.sp
.nf
\-\-where="user='jimf'"
\-w"userid>1"
\-w"userid<1"
.fi
.TP
\(bu
\fB\-\-xml\fR,
\fB\-X\fR
.sp
Write dump output as well\-formed XML.
.PP
You can also set the following variables by using
\fB\-\-\fR\fB\fIvar_name\fR\fR\fB=\fR\fB\fIvalue\fR\fR
syntax:
.TP 3
\(bu
max_allowed_packet
.sp
The maximum size of the buffer for client/server communication. The value of the variable can be up to 16MB before MySQL 4.0, and up to 1GB from MySQL 4.0 on.
.TP
\(bu
net_buffer_length
.sp
The initial size of the buffer for client/server communication. When creating multiple\-row\-insert statements (as with option
\fB\-\-extended\-insert\fR
or
\fB\-\-opt\fR),
\fBmysqldump\fR
creates rows up to
net_buffer_length
length. If you increase this variable, you should also ensure that the
net_buffer_length
variable in the MySQL server is at least this large.
.PP
It is also possible to set variables by using
\fB\-\-set\-variable=\fR\fB\fIvar_name\fR\fR\fB=\fR\fB\fIvalue\fR\fR
or
\fB\-O \fR\fB\fIvar_name\fR\fR\fB=\fR\fB\fIvalue\fR\fR
syntax. However, this syntax is deprecated as of MySQL 4.0.
.PP
The most common use of
\fBmysqldump\fR
is probably for making a backup of an entire database:
.sp
.nf
shell> \fBmysqldump \-\-opt \fR\fB\fIdb_name\fR\fR\fB > \fR\fB\fIbackup\-file.sql\fR\fR
.fi
.PP
You can read the dump file back into the server like this:
.sp
.nf
shell> \fBmysql \fR\fB\fIdb_name\fR\fR\fB < \fR\fB\fIbackup\-file.sql\fR\fR
.fi
.PP
Or like this:
.sp
.nf
shell> \fBmysql \-e "source \fR\fB\fI/path\-to\-backup/backup\-file.sql\fR\fR\fB" \fR\fB\fIdb_name\fR\fR
.fi
.PP
\fBmysqldump\fR
is also very useful for populating databases by copying data from one MySQL server to another:
.sp
.nf
shell> \fBmysqldump \-\-opt \fR\fB\fIdb_name\fR\fR\fB | mysql \-\-host=\fR\fB\fIremote_host\fR\fR\fB \-C \fR\fB\fIdb_name\fR\fR
.fi
.PP
It is possible to dump several databases with one command:
.sp
.nf
shell> \fBmysqldump \-\-databases \fR\fB\fIdb_name1\fR\fR\fB [\fR\fB\fIdb_name2\fR\fR\fB ...] > my_databases.sql\fR
.fi
.PP
To dump all databases, use the
\fB\-\-all\-databases\fR
option:
.sp
.nf
shell> \fBmysqldump \-\-all\-databases > all_databases.sql\fR
.fi
.PP
For
InnoDB
tables,
mysqldump
provides a way of making an online backup:
.sp
.nf
shell> \fBmysqldump \-\-all\-databases \-\-single\-transaction > all_databases.sql\fR
.fi
.PP
This backup just needs to acquire a global read lock on all tables (using
FLUSH TABLES WITH READ 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 and only if one long updating statement is running when the
FLUSH
statement is issued, the MySQL server may get stalled until that long statement finishes, and then the dump becomes lock\-free. 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.
.PP
For point\-in\-time recovery (also known as
\(lqroll\-forward,\(rq
when you need to restore an old backup and replay the changes that happened since that backup), it is often useful to rotate the binary log (see
Section\ 10.4, \(lqThe Binary Log\(rq) or at least know the binary log coordinates to which the dump corresponds:
.sp
.nf
shell> \fBmysqldump \-\-all\-databases \-\-master\-data=2 > all_databases.sql\fR
.fi
.PP
Or:
.sp
.nf
shell> \fBmysqldump \-\-all\-databases \-\-flush\-logs \-\-master\-data=2\fR
              \fB> all_databases.sql\fR
.fi
.PP
The simultaneous use of
\fB\-\-master\-data\fR
and
\fB\-\-single\-transaction\fR
works as of MySQL 4.1.8. It provides a convenient way to make an online backup suitable for point\-in\-time recovery if tables are stored in the
InnoDB
storage engine.
.PP
For more information on making backups, see
Section\ 8.1, \(lqDatabase Backups\(rq, and
Section\ 8.2, \(lqExample Backup and Recovery Strategy\(rq.
.SH "SEE ALSO"
isamchk(1),
isamlog(1),
msql2mysql(1),
myisam_ftdump(1),
myisamchk(1),
myisamlog(1),
myisampack(1),
mysql(1),
mysql.server(1),
mysql_config(1),
mysql_explain_log(1),
mysql_fix_privilege_tables(1),
mysql_zap(1),
mysqlaccess(1),
mysqladmin(1),
mysqlbinlog(1),
mysqlcheck(1),
mysqld(1),
mysqld_multi(1),
mysqld_safe(1),
mysqlhotcopy(1),
mysqlimport(1),
mysqlshow(1),
pack_isam(1),
perror(1),
replace(1),
safe_mysqld(1)
.P
For more information, please refer to the MySQL Reference Manual,
which may already be installed locally and which is also available
online at http://dev.mysql.com/doc/.
.SH AUTHOR
MySQL AB (http://www.mysql.com/).
This software comes with no warranty.