1
.\" Title: \fBmysqldump\fR
1
.\" Title: \fBdrizzledump\fR
3
3
.\" Generator: DocBook XSL Stylesheets v1.70.1 <http://docbook.sf.net/>
5
.\" Manual: MySQL Database System
5
.\" Manual: drizzle Database System
6
.\" Source: drizzle 6.0
8
.TH "\fBMYSQLDUMP\fR" "1" "05/23/2009" "MySQL 6.0" "MySQL Database System"
8
.TH "\fBMYSQLDUMP\fR" "1" "05/23/2009" "drizzle" "drizzle Database System"
9
9
.\" disable hyphenation
11
11
.\" disable justification (adjust text to left margin only)
14
mysqldump \- a database backup program
14
drizzledump \- a database backup program
17
\fBmysqldump [\fR\fB\fIoptions\fR\fR\fB] [\fR\fB\fIdb_name\fR\fR\fB [\fR\fB\fItbl_name\fR\fR\fB ...]]\fR
17
\fBdrizzledump [\fR\fB\fIoptions\fR\fR\fB] [\fR\fB\fIdb_name\fR\fR\fB [\fR\fB\fItbl_name\fR\fR\fB ...]]\fR
22
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 transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However,
22
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 transfer to another SQL server (not necessarily a drizzle server). The dump typically contains SQL statements to create the table, populate it, or both. However,
24
24
can also be used to generate files in CSV, other delimited text, or XML format.
26
If you are doing a backup on the server and your tables all are
28
tables, consider using the
30
instead because it can accomplish faster backups and faster restores. See
31
\fBmysqlhotcopy\fR(1).
33
26
There are three general ways to invoke
38
shell> \fBmysqldump [\fR\fB\fIoptions\fR\fR\fB] \fR\fB\fIdb_name\fR\fR\fB [\fR\fB\fItables\fR\fR\fB]\fR
39
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
40
shell> \fBmysqldump [\fR\fB\fIoptions\fR\fR\fB] \-\-all\-databases\fR
31
shell> \fBdrizzledump [\fR\fB\fIoptions\fR\fR\fB] \fR\fB\fIdb_name\fR\fR\fB [\fR\fB\fItables\fR\fR\fB]\fR
32
shell> \fBdrizzledump [\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
33
shell> \fBdrizzledump [\fR\fB\fIoptions\fR\fR\fB] \-\-all\-databases\fR
49
42
\fB\-\-all\-databases\fR
50
43
option, entire databases are dumped.
55
48
database. If you name that database explicitly on the command line,
57
50
silently ignores it.
59
52
To get a list of the options your version of
62
\fBmysqldump \-\-help\fR.
55
\fBdrizzledump \-\-help\fR.
66
59
options are shorthand for groups of other options.
97
90
option for each feature. For example, to disable extended inserts and memory buffering, use
99
92
\fB\-\-skip\-extended\-insert\fR
100
\fB\-\-skip\-quick\fR. (As of MySQL 6.0,
93
\fB\-\-skip\-quick\fR. (
101
94
\fB\-\-skip\-extended\-insert\fR
102
95
\fB\-\-skip\-quick\fR
103
96
is sufficient because
130
123
\fB\-\-quick\fR). The
132
125
option (and hence
133
\fB\-\-quick\fR) is enabled by default in MySQL 6.0; to enable memory buffering, use
126
\fB\-\-quick\fR) is enabled by default; to enable memory buffering, use
134
127
\fB\-\-skip\-quick\fR.
136
129
If you are using a recent version of
138
to generate a dump to be reloaded into a very old MySQL server, you should not use the
131
to generate a dump to be reloaded into a very old drizzle server, you should not use the
141
134
\fB\-\-extended\-insert\fR
181
statements. This results in faster inserts when the dump file is reloaded. See
182
Section\ 7.2.25, \(lqSpeed of INSERT Statements\(rq.
173
statements. This results in faster inserts when the dump file is reloaded.
185
176
\fB\-\-all\-databases\fR,
208
statement at the end of the output. This option was added in MySQL 6.0.4.
199
statement at the end of the output.
211
202
\fB\-\-character\-sets\-dir=\fR\fB\fIpath\fR\fR
213
The directory where character sets are installed. See
214
Section\ 9.2, \(lqThe Character Set Used for Data and Sorting\(rq.
204
The directory where character sets are installed.
217
207
\fB\-\-comments\fR,
249
239
no_table_options, or
250
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
251
Section\ 5.1.7, \(lqServer SQL Modes\(rq.
240
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.
253
242
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,
254
243
\fB\-\-compatible=oracle\fR
297
286
\fIdebug_options\fR
299
288
\'d:t:o,\fIfile_name\fR'. The default value is
300
\'d:t:o,/tmp/mysqldump.trace'.
289
\'d:t:o,/tmp/drizzledump.trace'.
303
292
\fB\-\-debug\-check\fR
316
305
\fIcharset_name\fR
317
as the default character set. See
318
Section\ 9.2, \(lqThe Character Set Used for Data and Sorting\(rq. If no character set is specified,
306
as the default character set. If no character set is specified,
321
utf8, and earlier versions use
324
311
This option has no effect for output data files produced by using the
370
357
\fB\-\-dump\-date\fR
371
358
(include the date in the comment).
372
359
\fB\-\-skip\-dump\-date\fR
373
suppresses date printing. This option was added in MySQL 6.0.4.
360
suppresses date printing.
376
363
\fB\-\-dump\-slave[=\fR\fB\fIvalue\fR\fR\fB]\fR
382
369
statement that indicates the binary log coordinates (file name and position) of the dumped slave's master (rather than the coordinates of the dumped server, as is done by the
383
370
\fB\-\-master\-data\fR
384
option). These are the master server coordinates from which the slave should start replicating. This option was added in MySQL 6.0.4.
371
option). These are the master server coordinates from which the slave should start replicating.
386
373
The option value is handled the same way as for
387
374
\fB\-\-master\-data\fR
471
457
Continue even if an SQL error occurs during a table dump.
473
459
One use for this option is to cause
475
461
to continue executing even when it encounters a view that has become invalid because the definition refers to a table that has been dropped. Without
478
464
exits with an error message. With
481
467
prints the error message, but it also writes an SQL comment containing the view definition to the dump output and continues executing.
484
470
\fB\-\-host=\fR\fB\fIhost_name\fR\fR,
485
471
\fB\-h \fR\fB\fIhost_name\fR\fR
487
Dump data from the MySQL server on the given host. The default host is
473
Dump data from the drizzle server on the given host. The default host is
532
518
This option is used with the
534
520
option and has the same meaning as the corresponding clause for
535
LOAD DATA INFILE. See
536
Section\ 12.2.6, \(lqLOAD DATA INFILE Syntax\(rq.
539
524
\fB\-\-lock\-all\-tables\fR,
652
637
\fB\-\-extended\-insert\fR
653
638
\fB\-\-lock\-tables\fR
655
\fB\-\-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.
640
\fB\-\-set\-charset\fR. It should give you a fast dump operation and produce a dump file that can be reloaded into a drizzle server quickly.
657
642
\fIThe \fR\fI\fB\-\-opt\fR\fR\fI option is enabled by default. Use \fR\fI\fB\-\-skip\-opt\fR\fR\fI to disable it.\fR
658
643
See the discussion at the beginning of this section for information about selectively enabling or disabling certain of the options affected by
682
667
option on the command line, you are prompted for one.
684
Specifying a password on the command line should be considered insecure. See
685
Section\ 5.5.6.2, \(lqEnd\-User Guidelines for Password Security\(rq.
669
Specifying a password on the command line should be considered insecure.
700
684
\fB\-\-protocol={TCP|SOCKET|PIPE|MEMORY}\fR
702
The connection protocol to use for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want. For details on the allowable values, see
703
Section\ 4.2.2, \(lqConnecting to the MySQL Server\(rq.
686
The connection protocol to use for connecting to the server. It is useful when the other connection parameters normally would cause a protocol to be used other than the one you want.
709
692
This option is useful for dumping large tables. It forces
711
694
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.
764
747
If you require routines to be re\-created with their original timestamp attributes, do not use
765
748
\fB\-\-routines\fR. Instead, dump and reload the contents of the
767
table directly, using a MySQL account that has appropriate privileges for the
750
table directly, using a drizzle account that has appropriate privileges for the
848
831
Options that begin with
850
specify whether to connect to the server via SSL and indicate where to find SSL keys and certificates. See
851
Section\ 5.5.7.3, \(lqSSL Command Options\(rq.
833
specify whether to connect to the server via SSL and indicate where to find SSL keys and certificates.
854
836
\fB\-\-tab=\fR\fB\fIpath\fR\fR,
855
837
\fB\-T \fR\fB\fIpath\fR\fR
857
839
Produce tab\-separated data files. For each dumped table,
860
842
\fI\fItbl_name\fR\fR\fI.sql\fR
861
843
file that contains the
886
868
This option should be used only when
888
870
is run on the same machine as the
890
872
server. You must have the
892
874
privilege, and the server must have permission to write files in the directory that you specify.
930
912
\fB\-\-user=\fR\fB\fIuser_name\fR\fR,
931
913
\fB\-u \fR\fB\fIuser_name\fR\fR
933
The MySQL user name to use when connecting to the server.
915
The drizzle user name to use when connecting to the server.
936
918
\fB\-\-verbose\fR,
1009
991
The output from the
1011
993
client when run using the
1013
995
option also follows these rules. (See
1014
996
the section called \(lq\fBMYSQL\fR OPTIONS\(rq.)
1018
1000
includes the XML namespace, as shown here:
1022
shell> \fBmysqldump \-\-xml \-u root world City\fR
1004
shell> \fBdrizzledump \-\-xml \-u root world City\fR
1023
1005
<?xml version="1.0"?>
1024
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema\-instance">
1006
<drizzledump xmlns:xsi="http://www.w3.org/2001/XMLSchema\-instance">
1025
1007
<database name="world">
1026
1008
<table_structure name="City">
1027
1009
<field Field="ID" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" />
1076
1058
\fB\-\-extended\-insert\fR
1078
1060
\fB\-\-opt\fR),
1080
1062
creates rows up to
1081
1063
net_buffer_length
1082
1064
length. If you increase this variable, you should also ensure that the
1083
1065
net_buffer_length
1084
variable in the MySQL server is at least this large.
1066
variable in the drizzle server is at least this large.
1088
1070
The most common use of
1090
1072
is probably for making a backup of an entire database:
1094
shell> \fBmysqldump \fR\fB\fIdb_name\fR\fR\fB > \fR\fB\fIbackup\-file.sql\fR\fR
1076
shell> \fBdrizzledump \fR\fB\fIdb_name\fR\fR\fB > \fR\fB\fIbackup\-file.sql\fR\fR
1110
shell> \fBmysql \-e "source \fR\fB\fI/path\-to\-backup/backup\-file.sql\fR\fR\fB" \fR\fB\fIdb_name\fR\fR
1092
shell> \fBdrizzle \-e "source \fR\fB\fI/path\-to\-backup/backup\-file.sql\fR\fR\fB" \fR\fB\fIdb_name\fR\fR
1115
is also very useful for populating databases by copying data from one MySQL server to another:
1097
is also very useful for populating databases by copying data from one drizzle server to another:
1119
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
1101
shell> \fBdrizzledump \-\-opt \fR\fB\fIdb_name\fR\fR\fB | drizzle \-\-host=\fR\fB\fIremote_host\fR\fR\fB \-C \fR\fB\fIdb_name\fR\fR
1137
shell> \fBmysqldump \-\-all\-databases > all_databases.sql\fR
1119
shell> \fBdrizzledump \-\-all\-databases > all_databases.sql\fR
1145
1127
provides a way of making an online backup:
1149
shell> \fBmysqldump \-\-all\-databases \-\-single\-transaction > all_databases.sql\fR
1131
shell> \fBdrizzledump \-\-all\-databases \-\-single\-transaction > all_databases.sql\fR
1153
1135
This backup acquires a global read lock on all tables (using
1154
1136
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 long updating statements are running when the
1156
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.
1138
statement is issued, the drizzle 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 drizzle server receives are short (in terms of execution time), the initial lock period should not be noticeable, even with many updates.
1158
1140
For point\-in\-time recovery (also known as
1159
1141
\(lqroll\-forward,\(rq
1160
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
1161
Section\ 5.2.4, \(lqThe Binary Log\(rq) or at least know the binary log coordinates to which the dump corresponds:
1142
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 , \(lqThe Binary Log\(rq) or at least know the binary log coordinates to which the dump corresponds:
1165
shell> \fBmysqldump \-\-all\-databases \-\-master\-data=2 > all_databases.sql\fR
1146
shell> \fBdrizzledump \-\-all\-databases \-\-master\-data=2 > all_databases.sql\fR
1184
1165
storage engine.
1186
For more information on making backups, see
1187
Section\ 6.1, \(lqDatabase Backups\(rq, and
1188
Section\ 6.2, \(lqExample Backup and Recovery Strategy\(rq.
1190
If you encounter problems backing up views, please read the section that covers restrictions on views which describes a workaround for backing up views when this fails due to insufficient privileges. See
1191
Section\ D.5, \(lqRestrictions on Views\(rq.
1167
If you encounter problems backing up views, please read the section that covers restrictions on views which describes a workaround for backing up views when this fails due to insufficient privileges.
1192
1168
.SH "COPYRIGHT"
1194
1170
Copyright 2007\-2008 MySQL AB, 2009 Sun Microsystems, Inc.
1171
Copyright 2010 Drizzle Team
1196
1173
This documentation is free software; you can redistribute it and/or modify it only under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License.
1200
1177
You should have received a copy of the GNU General Public License along with the program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110\-1301 USA or see http://www.gnu.org/licenses/.
1202
For more information, please refer to the MySQL Reference Manual,
1203
which may already be installed locally and which is also available
1204
online at http://dev.mysql.com/doc/.
1179
For more information, please refer to http://www.drizzle.org/wiki.
1206
Sun Microsystems, Inc. (http://www.mysql.com/).
1181
Sun Microsystems, Inc. (http://www.mysql.com/), Drizzle Team
added
removed
client/drizzledump.1
