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 is a backup program originally written by Igor
13 Romanenko. It can be used to dump a database or a collection of
14 databases for backup or transfer to another SQL server (not necessarily
15 a MySQL server). The dump typically contains SQL statements to create
16 the table, populate it, or both. However, mysqldump can also be used to
17 generate files in CSV, other delimited text, or XML format.
18 If you are doing a backup on the server and your tables all are MyISAM
20 tables, consider using the mysqlhotcopy instead because it can
21 accomplish faster backups and faster restores. See mysqlhotcopy(1).
22 There are three general ways to invoke mysqldump:
24 shell> mysqldump [options] db_name [tbl_name ...]
26 shell> mysqldump [options] --databases db_name ...
27 shell> mysqldump [options] --all-databases
28 If you do not name any tables following db_name or if you use the
30 --databases or --all-databases option, entire databases are dumped.
31 mysqldump does not dump the INFORMATION_SCHEMA database by default. As
33 of MySQL 5.1.38, mysqldump dumps INFORMATION_SCHEMA if you name it
34 explicitly on the command line, although you must also use the
35 --skip-lock-tables option. Before 5.1.38, mysqldump silently ignores
36 INFORMATION_SCHEMA even if you name it explicitly on the command line.
37 In MySQL Cluster NDB 7.1.7 and later, the ndbinfo information database
39 is ignored and not dumped by mysqldump.
40 To see a list of the options your version of mysqldump supports,
42 execute mysqldump --help.
43 Some mysqldump options are shorthand for groups of other options:
45 · Use of --opt is the same as specifying --add-drop-table,
47 --add-locks, --create-options, --disable-keys, --extended-insert,
48 --lock-tables, --quick, and --set-charset. All of the options that
49 --opt stands for also are on by default because --opt is on by
50 default.
51 · Use of --compact is the same as specifying --skip-add-drop-table,
53 --skip-add-locks, --skip-comments, --skip-disable-keys, and
54 --skip-set-charset options.
55 To reverse the effect of a group option, uses its --skip-xxx form
57 (--skip-opt or --skip-compact). It is also possible to select only part
58 of the effect of a group option by following it with options that
59 enable or disable specific features. Here are some examples:
60 · To select the effect of --opt except for some features, use the
62 --skip option for each feature. To disable extended inserts and
63 memory buffering, use --opt --skip-extended-insert --skip-quick.
64 (Actually, --skip-extended-insert --skip-quick is sufficient
65 because --opt is on by default.)
66 · To reverse --opt for all features except index disabling and table
68 locking, use --skip-opt --disable-keys --lock-tables.
69 When you selectively enable or disable the effect of a group option,
71 order is important because options are processed first to last. For
72 example, --disable-keys --lock-tables --skip-opt would not have the
73 intended effect; it is the same as --skip-opt by itself.
74 mysqldump can retrieve and dump table contents row by row, or it can
76 retrieve the entire content from a table and buffer it in memory before
77 dumping it. Buffering in memory can be a problem if you are dumping
78 large tables. To dump tables row by row, use the --quick option (or
79 --opt, which enables --quick). The --opt option (and hence --quick) is
80 enabled by default, so to enable memory buffering, use --skip-quick.
81 If you are using a recent version of mysqldump to generate a dump to be
83 reloaded into a very old MySQL server, you should not use the --opt or
84 --extended-insert option. Use --skip-opt instead.
85 Note
87 mysqldump from MySQL 5.1.21 cannot be used to create dumps from
88 MySQL server 5.1.20 and older. This issue is fixed in MySQL 5.1.22.
89 (Bug #30123)
90 mysqldump supports the following options, which can be specified on the
92 command line or in the [mysqldump] and [client] groups of an option
93 file. mysqldump also supports the options for processing option files
94 described at Section 4.2.3.3.1, “Command-Line Options that Affect
95 Option-File Handling”.
96 · --help, -?
98 Display a help message and exit.
100 · --add-drop-database
102 Add a DROP DATABASE statement before each CREATE DATABASE
104 statement. This option is typically used in conjunction with the
105 --all-databases or --databases option because no CREATE DATABASE
106 statements are written unless one of those options is specified.
107 · --add-drop-table
109 Add a DROP TABLE statement before each CREATE TABLE statement.
111 · --add-drop-trigger
113 Add a DROP TRIGGER statement before each CREATE TRIGGER statement.
115 Note
117 This option is supported only by mysqldump as supplied with
118 MySQL Cluster NDB 6.3.38, MySQL Cluster NDB 7.0.19, MySQL
119 Cluster NDB 7.1.8, and later MySQL Cluster releases. It is not
120 available when using MySQL 5.1.
121 · --add-locks
123 Surround each table dump with LOCK TABLES and UNLOCK TABLES
125 statements. This results in faster inserts when the dump file is
126 reloaded. See Section 7.3.2.1, “Speed of INSERT Statements”.
127 · --all-databases, -A
129 Dump all tables in all databases. This is the same as using the
131 --databases option and naming all the databases on the command
132 line.
133 · --all-tablespaces, -Y
135 Adds to a table dump all SQL statements needed to create any
137 tablespaces used by an NDBCLUSTER table. This information is not
138 otherwise included in the output from mysqldump. This option is
139 currently relevant only to MySQL Cluster tables.
140 This option was added in MySQL 5.1.6.
142 · --allow-keywords
144 Permit creation of column names that are keywords. This works by
146 prefixing each column name with the table name.
147 · --bind-address=ip_address
149 On a computer having multiple network interfaces, this option can
151 be used to select which interface is employed when connecting to
152 the MySQL server.
153 This option is supported only in the version of mysqldump that is
155 supplied with MySQL Cluster, beginning with MySQL Cluster NDB
156 6.3.4. It is not available in standard MySQL 5.1 releases.
157 · --character-sets-dir=path
159 The directory where character sets are installed. See Section 9.5,
161 “Character Set Configuration”.
162 · --comments, -i
164 Write additional information in the dump file such as program
166 version, server version, and host. This option is enabled by
167 default. To suppress this additional information, use
168 --skip-comments.
169 · --compact
171 Produce more compact output. This option enables the
173 --skip-add-drop-table, --skip-add-locks, --skip-comments,
174 --skip-disable-keys, and --skip-set-charset options.
175 Note
177 Prior to MySQL 5.1.21, this option did not create valid SQL if
178 the database dump contained views. The recreation of views
179 requires the creation and removal of temporary tables and this
180 option suppressed the removal of those temporary tables. As a
181 workaround, use --compact with the --add-drop-table option and
182 then manually adjust the dump file.
183 · --compatible=name
185 Produce output that is more compatible with other database systems
187 or with older MySQL servers. The value of name can be ansi,
188 mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb,
189 no_key_options, no_table_options, or no_field_options. To use
190 several values, separate them by commas. These values have the same
191 meaning as the corresponding options for setting the server SQL
192 mode. See Section 5.1.6, “Server SQL Modes”.
193 This option does not guarantee compatibility with other servers. It
195 only enables those SQL mode values that are currently available for
196 making dump output more compatible. For example,
197 --compatible=oracle does not map data types to Oracle types or use
198 Oracle comment syntax.
199 This option requires a server version of 4.1.0 or higher. With
201 older servers, it does nothing.
202 · --complete-insert, -c
204 Use complete INSERT statements that include column names.
206 · --compress, -C
208 Compress all information sent between the client and the server if
210 both support compression.
211 · --create-options
213 Include all MySQL-specific table options in the CREATE TABLE
215 statements.
216 · --databases, -B
218 Dump several databases. Normally, mysqldump treats the first name
220 argument on the command line as a database name and following names
221 as table names. With this option, it treats all name arguments as
222 database names. CREATE DATABASE and USE statements are included in
223 the output before each new database.
224 · --debug[=debug_options], -# [debug_options]
226 Write a debugging log. A typical debug_options string is
228 ´d:t:o,file_name´. The default value is
229 ´d:t:o,/tmp/mysqldump.trace´.
230 · --debug-check
232 Print some debugging information when the program exits. This
234 option was added in MySQL 5.1.21.
235 · --debug-info
237 Print debugging information and memory and CPU usage statistics
239 when the program exits. This option was added in MySQL 5.1.14.
240 · --default-character-set=charset_name
242 Use charset_name as the default character set. See Section 9.5,
244 “Character Set Configuration”. If no character set is specified,
245 mysqldump uses utf8, and earlier versions use latin1.
246 Prior to MySQL 5.1.38, this option has no effect for output data
248 files produced by using the --tab option. See the description for
249 that option.
250 · --delayed-insert
252 Write INSERT DELAYED statements rather than INSERT statements.
254 · --delete-master-logs
256 On a master replication server, delete the binary logs by sending a
258 PURGE BINARY LOGS statement to the server after performing the dump
259 operation. This option automatically enables --master-data.
260 · --disable-keys, -K
262 For each table, surround the INSERT statements with /*!40000 ALTER
264 TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name
265 ENABLE KEYS */; statements. This makes loading the dump file faster
266 because the indexes are created after all rows are inserted. This
267 option is effective only for nonunique indexes of MyISAM tables.
268 · --dump-date
270 If the --comments option is given, mysqldump produces a comment at
272 the end of the dump of the following form:
273 -- Dump completed on DATE
275 However, the date causes dump files taken at different times to
277 appear to be different, even if the data are otherwise identical.
278 --dump-date and --skip-dump-date control whether the date is added
279 to the comment. The default is --dump-date (include the date in the
280 comment). --skip-dump-date suppresses date printing. This option
281 was added in MySQL 5.1.23.
282 · --events, -E
284 Include Event Scheduler events for the dumped databases in the
286 output. This option was added in MySQL 5.1.8.
287 · --extended-insert, -e
289 Use multiple-row INSERT syntax that include several VALUES lists.
291 This results in a smaller dump file and speeds up inserts when the
292 file is reloaded.
293 · --fields-terminated-by=..., --fields-enclosed-by=...,
295 --fields-optionally-enclosed-by=..., --fields-escaped-by=...
296 These options are used with the --tab option and have the same
298 meaning as the corresponding FIELDS clauses for LOAD DATA INFILE.
299 See Section 12.2.6, “LOAD DATA INFILE Syntax”.
300 · --first-slave
302 Deprecated. Use --lock-all-tables instead. --first-slave is
304 removed in MySQL 5.5.
305 · --flush-logs, -F
307 Flush the MySQL server log files before starting the dump. This
309 option requires the RELOAD privilege. If you use this option in
310 combination with the --all-databases option, the logs are flushed
311 for each database dumped. The exception is when using
312 --lock-all-tables or --master-data: In this case, the logs are
313 flushed only once, corresponding to the moment that all tables are
314 locked. If you want your dump and the log flush to happen at
315 exactly the same moment, you should use --flush-logs together with
316 either --lock-all-tables or --master-data.
317 · --flush-privileges
319 Send a FLUSH PRIVILEGES statement to the server after dumping the
321 mysql database. This option should be used any time the dump
322 contains the mysql database and any other database that depends on
323 the data in the mysql database for proper restoration. This option
324 was added in MySQL 5.1.12.
325 · --force, -f
327 Continue even if an SQL error occurs during a table dump.
329 One use for this option is to cause mysqldump to continue executing
331 even when it encounters a view that has become invalid because the
332 definition refers to a table that has been dropped. Without
333 --force, mysqldump exits with an error message. With --force,
334 mysqldump prints the error message, but it also writes an SQL
335 comment containing the view definition to the dump output and
336 continues executing.
337 · --host=host_name, -h host_name
339 Dump data from the MySQL server on the given host. The default host
341 is localhost.
342 · --hex-blob
344 Dump binary columns using hexadecimal notation (for example, ´abc´
346 becomes 0x616263). The affected data types are BINARY, VARBINARY,
347 the BLOB types, and BIT.
348 · --ignore-table=db_name.tbl_name
350 Do not dump the given table, which must be specified using both the
352 database and table names. To ignore multiple tables, use this
353 option multiple times. This option also can be used to ignore
354 views.
355 · --insert-ignore
357 Write INSERT IGNORE statements rather than INSERT statements.
359 · --lines-terminated-by=...
361 This option is used with the --tab option and has the same meaning
363 as the corresponding LINES clause for LOAD DATA INFILE. See
364 Section 12.2.6, “LOAD DATA INFILE Syntax”.
365 · --lock-all-tables, -x
367 Lock all tables across all databases. This is achieved by acquiring
369 a global read lock for the duration of the whole dump. This option
370 automatically turns off --single-transaction and --lock-tables.
371 · --lock-tables, -l
373 For each dumped database, lock all tables to be dumped before
375 dumping them. The tables are locked with READ LOCAL to permit
376 concurrent inserts in the case of MyISAM tables. For transactional
377 tables such as InnoDB, --single-transaction is a much better option
378 than --lock-tables because it does not need to lock the tables at
379 all.
380 Because --lock-tables locks tables for each database separately,
382 this option does not guarantee that the tables in the dump file are
383 logically consistent between databases. Tables in different
384 databases may be dumped in completely different states.
385 · --log-error=file_name
387 Log warnings and errors by appending them to the named file. The
389 default is to do no logging. This option was added in MySQL 5.1.18.
390 · --master-data[=value]
392 Use this option to dump a master replication server to produce a
394 dump file that can be used to set up another server as a slave of
395 the master. It causes the dump output to include a CHANGE MASTER TO
396 statement that indicates the binary log coordinates (file name and
397 position) of the dumped server. These are the master server
398 coordinates from which the slave should start replicating after you
399 load the dump file into the slave.
400 If the option value is 2, the CHANGE MASTER TO statement is written
402 as an SQL comment, and thus is informative only; it has no effect
403 when the dump file is reloaded. If the option value is 1, the
404 statement is not written as a comment and takes effect when the
405 dump file is reloaded. If no option value is specified, the default
406 value is 1.
407 This option requires the RELOAD privilege and the binary log must
409 be enabled.
410 The --master-data option automatically turns off --lock-tables. It
412 also turns on --lock-all-tables, unless --single-transaction also
413 is specified, in which case, a global read lock is acquired only
414 for a short time at the beginning of the dump (see the description
415 for --single-transaction). In all cases, any action on logs happens
416 at the exact moment of the dump.
417 It is also possible to set up a slave by dumping an existing slave
419 of the master. To do this, use the following procedure on the
420 existing slave:
421 1. Stop the slave´s SQL thread and get its current status:
423 mysql> STOP SLAVE SQL_THREAD;
425 mysql> SHOW SLAVE STATUS;
426 2. From the output of the SHOW SLAVE STATUS statement, the binary
428 log coordinates of the master server from which the new slave
429 should start replicating are the values of the
430 Relay_Master_Log_File and Exec_Master_Log_Pos fields. Denote
431 those values as file_name and file_pos.
432 3. Dump the slave server:
434 shell> mysqldump --master-data=2 --all-databases > dumpfile
436 Using --master-data=2 works only if binary logging has been
438 enabled on the slave. Otherwise, mysqldump fails with the error
439 Binlogging on server not active. In this case you must handle
440 any locking issues in another manner, using one or more of
441 --add-locks, --lock-tables, --lock-all-tables, or
442 --single-transaction, as required by your application and
443 environment.
444 4. Restart the slave:
446 mysql> START SLAVE;
448 5. On the new slave, load the dump file:
450 shell> mysql < dumpfile
452 6. On the new slave, set the replication coordinates to those of
454 the master server obtained earlier:
455 mysql> CHANGE MASTER TO
457 -> MASTER_LOG_FILE = ´file_name´, MASTER_LOG_POS = file_pos;
458 The CHANGE MASTER TO statement might also need other
460 parameters, such as MASTER_HOST to point the slave to the
461 correct master server host. Add any such parameters as
462 necessary.
463 · --no-autocommit
465 Enclose the INSERT statements for each dumped table within SET
467 autocommit = 0 and COMMIT statements.
468 · --no-create-db, -n
470 This option suppresses the CREATE DATABASE statements that are
472 otherwise included in the output if the --databases or
473 --all-databases option is given.
474 · --no-create-info, -t
476 Do not write CREATE TABLE statements that re-create each dumped
478 table.
479 Note
481 This option does not not exclude statements creating log file
482 groups or tablespaces from mysqldump output; in MySQL 5.1.14
483 and later, you can use the --no-tablespaces option for this
484 purpose.
485 · --no-data, -d
487 Do not write any table row information (that is, do not dump table
489 contents). This is useful if you want to dump only the CREATE TABLE
490 statement for the table (for example, to create an empty copy of
491 the table by loading the dump file).
492 · --no-set-names, -N
494 This has the same effect as --skip-set-charset.
496 · --no-tablespaces, -y
498 This option suppresses all CREATE LOGFILE GROUP and CREATE
500 TABLESPACE statements in the output of mysqldump.
501 This option was added in MySQL 5.1.14.
503 · --opt
505 This option is shorthand. It is the same as specifying
507 --add-drop-table --add-locks --create-options --disable-keys
508 --extended-insert --lock-tables --quick --set-charset. It should
509 give you a fast dump operation and produce a dump file that can be
510 reloaded into a MySQL server quickly.
511 The --opt option is enabled by default. Use --skip-opt to disable
513 it. See the discussion at the beginning of this section for
514 information about selectively enabling or disabling a subset of the
515 options affected by --opt.
516 · --order-by-primary
518 Dump each table´s rows sorted by its primary key, or by its first
520 unique index, if such an index exists. This is useful when dumping
521 a MyISAM table to be loaded into an InnoDB table, but will make the
522 dump operation take considerably longer.
523 · --password[=password], -p[password]
525 The password to use when connecting to the server. If you use the
527 short option form (-p), you cannot have a space between the option
528 and the password. If you omit the password value following the
529 --password or -p option on the command line, mysqldump prompts for
530 one.
531 Specifying a password on the command line should be considered
533 insecure. See Section 5.3.2.2, “End-User Guidelines for Password
534 Security”. You can use an option file to avoid giving the password
535 on the command line.
536 · --pipe, -W
538 On Windows, connect to the server using a named pipe. This option
540 applies only if the server supports named-pipe connections.
541 · --port=port_num, -P port_num
543 The TCP/IP port number to use for the connection.
545 · --protocol={TCP|SOCKET|PIPE|MEMORY}
547 The connection protocol to use for connecting to the server. It is
549 useful when the other connection parameters normally would cause a
550 protocol to be used other than the one you want. For details on the
551 permissible values, see Section 4.2.2, “Connecting to the MySQL
552 Server”.
553 · --quick, -q
555 This option is useful for dumping large tables. It forces mysqldump
557 to retrieve rows for a table from the server a row at a time rather
558 than retrieving the entire row set and buffering it in memory
559 before writing it out.
560 · --quote-names, -Q
562 Quote identifiers (such as database, table, and column names)
564 within “`” characters. If the ANSI_QUOTES SQL mode is enabled,
565 identifiers are quoted within “"” characters. This option is
566 enabled by default. It can be disabled with --skip-quote-names, but
567 this option should be given after any option such as --compatible
568 that may enable --quote-names.
569 · --replace
571 Write REPLACE statements rather than INSERT statements. This option
573 was added in MySQL 5.1.3.
574 · --result-file=file_name, -r file_name
576 Direct output to a given file. This option should be used on
578 Windows to prevent newline “\n” characters from being converted to
579 “\r\n” carriage return/newline sequences. The result file is
580 created and its previous contents overwritten, even if an error
581 occurs while generating the dump.
582 · --routines, -R
584 Included stored routines (procedures and functions) for the dumped
586 databases in the output. Use of this option requires the SELECT
587 privilege for the mysql.proc table. The output generated by using
588 --routines contains CREATE PROCEDURE and CREATE FUNCTION statements
589 to re-create the routines. However, these statements do not include
590 attributes such as the routine creation and modification
591 timestamps. This means that when the routines are reloaded, they
592 will be created with the timestamps equal to the reload time.
593 If you require routines to be re-created with their original
595 timestamp attributes, do not use --routines. Instead, dump and
596 reload the contents of the mysql.proc table directly, using a MySQL
597 account that has appropriate privileges for the mysql database.
598 This option was added in MySQL 5.1.2. Before that, stored routines
600 are not dumped. Routine DEFINER values are not dumped until MySQL
601 5.1.8. This means that before 5.1.8, when routines are reloaded,
602 they will be created with the definer set to the reloading user. If
603 you require routines to be re-created with their original definer,
604 dump and load the contents of the mysql.proc table directly as
605 described earlier.
606 · --set-charset
608 Add SET NAMES default_character_set to the output. This option is
610 enabled by default. To suppress the SET NAMES statement, use
611 --skip-set-charset.
612 · --single-transaction
614 This option sends a START TRANSACTION SQL statement to the server
616 before dumping data. It is useful only with transactional tables
617 such as InnoDB, because then it dumps the consistent state of the
618 database at the time when BEGIN was issued without blocking any
619 applications.
620 When using this option, you should keep in mind that only InnoDB
622 tables are dumped in a consistent state. For example, any MyISAM or
623 MEMORY tables dumped while using this option may still change
624 state.
625 While a --single-transaction dump is in process, to ensure a valid
627 dump file (correct table contents and binary log coordinates), no
628 other connection should use the following statements: ALTER TABLE,
629 CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A
630 consistent read is not isolated from those statements, so use of
631 them on a table to be dumped can cause the SELECT that is performed
632 by mysqldump to retrieve the table contents to obtain incorrect
633 contents or fail.
634 The --single-transaction option and the --lock-tables option are
636 mutually exclusive because LOCK TABLES causes any pending
637 transactions to be committed implicitly.
638 This option is not supported for MySQL Cluster tables; the results
640 cannot be guaranteed to be consistent due to the fact that the
641 NDBCLUSTER storage engine supports only the READ_COMMITTED
642 transaction isolation level. You should always use NDB backup and
643 restore instead.
644 To dump large tables, you should combine the --single-transaction
646 option with --quick.
647 · --skip-comments
649 See the description for the --comments option.
651 · --skip-opt
653 See the description for the --opt option.
655 · --socket=path, -S path
657 For connections to localhost, the Unix socket file to use, or, on
659 Windows, the name of the named pipe to use.
660 · --ssl*
662 Options that begin with --ssl specify whether to connect to the
664 server using SSL and indicate where to find SSL keys and
665 certificates. See Section 5.5.6.3, “SSL Command Options”.
666 · --tab=path, -T path
668 Produce tab-separated text-format data files. For each dumped
670 table, mysqldump creates a tbl_name.sql file that contains the
671 CREATE TABLE statement that creates the table, and the server
672 writes a tbl_name.txt file that contains its data. The option value
673 is the directory in which to write the files.
674 Note
676 This option should be used only when mysqldump is run on the
677 same machine as the mysqld server. You must have the FILE
678 privilege, and the server must have permission to write files
679 in the directory that you specify.
680 By default, the .txt data files are formatted using tab characters
681 between column values and a newline at the end of each line. The
682 format can be specified explicitly using the --fields-xxx and
683 --lines-terminated-by options.
684 As of MySQL 5.1.38, column values are converted to the character
686 set specified by the --default-character-set option. Prior to
687 5.1.38 or if no such option is present, values are dumped using the
688 binary character set. In effect, there is no character set
689 conversion. If a table contains columns in several character sets,
690 the output data file will as well and you may not be able to reload
691 the file correctly.
692 · --tables
694 Override the --databases or -B option. mysqldump regards all name
696 arguments following the option as table names.
697 · --triggers
699 Include triggers for each dumped table in the output. This option
701 is enabled by default; disable it with --skip-triggers.
702 · --tz-utc
704 This option enables TIMESTAMP columns to be dumped and reloaded
706 between servers in different time zones. mysqldump sets its
707 connection time zone to UTC and adds SET TIME_ZONE=´+00:00´ to the
708 dump file. Without this option, TIMESTAMP columns are dumped and
709 reloaded in the time zones local to the source and destination
710 servers, which can cause the values to change if the servers are in
711 different time zones. --tz-utc also protects against changes due
712 to daylight saving time. --tz-utc is enabled by default. To
713 disable it, use --skip-tz-utc. This option was added in MySQL
714 5.1.2.
715 · --user=user_name, -u user_name
717 The MySQL user name to use when connecting to the server.
719 · --verbose, -v
721 Verbose mode. Print more information about what the program does.
723 · --version, -V
725 Display version information and exit.
727 · --where=´where_condition´, -w ´where_condition´
729 Dump only rows selected by the given WHERE condition. Quotes around
731 the condition are mandatory if it contains spaces or other
732 characters that are special to your command interpreter.
733 Examples:
735 --where="user=´jimf´"
737 -w"userid>1"
738 -w"userid<1"
739 · --xml, -X
741 Write dump output as well-formed XML.
743 NULL, ´NULL´, and Empty Values: For a column named column_name, the
745 NULL value, an empty string, and the string value ´NULL´ are
746 distinguished from one another in the output generated by this
747 option as follows.
748 ┌──────────────────────┬─────────────────────────────────┐
750 │Value: │ XML Representation: │
751 ├──────────────────────┼─────────────────────────────────┤
752 │NULL (unknown value) │ <field name="column_name" │
753 │ │ xsi:nil="true" /> │
754 ├──────────────────────┼─────────────────────────────────┤
755 │´´ (empty string) │ <field │
756 │ │ name="column_name"></field> │
757 ├──────────────────────┼─────────────────────────────────┤
758 │´NULL´ (string value) │ <field │
759 │ │ name="column_name">NULL</field> │
760 └──────────────────────┴─────────────────────────────────┘
761 Beginning with MySQL 5.1.12, the output from the mysql client when
762 run using the --xml option also follows the preceding rules. (See
763 the section called “MYSQL OPTIONS”.)
764 Beginning with MySQL 5.1.18, XML output from mysqldump includes the
766 XML namespace, as shown here:
767 shell> mysqldump --xml -u root world City
769 <?xml version="1.0"?>
770 <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
771 <database name="world">
772 <table_structure name="City">
773 <field Field="ID" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" />
774 <field Field="Name" Type="char(35)" Null="NO" Key="" Default="" Extra="" />
775 <field Field="CountryCode" Type="char(3)" Null="NO" Key="" Default="" Extra="" />
776 <field Field="District" Type="char(20)" Null="NO" Key="" Default="" Extra="" />
777 <field Field="Population" Type="int(11)" Null="NO" Key="" Default="0" Extra="" />
778 <key Table="City" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="ID"
779 Collation="A" Cardinality="4079" Null="" Index_type="BTREE" Comment="" />
780 <options Name="City" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="4079"
781 Avg_row_length="67" Data_length="273293" Max_data_length="18858823439613951"
782 Index_length="43008" Data_free="0" Auto_increment="4080"
783 Create_time="2007-03-31 01:47:01" Update_time="2007-03-31 01:47:02"
784 Collation="latin1_swedish_ci" Create_options="" Comment="" />
785 </table_structure>
786 <table_data name="City">
787 <row>
788 <field name="ID">1</field>
789 <field name="Name">Kabul</field>
790 <field name="CountryCode">AFG</field>
791 <field name="District">Kabol</field>
792 <field name="Population">1780000</field>
793 </row>
794 ...
795 <row>
796 <field name="ID">4079</field>
797 <field name="Name">Rafah</field>
798 <field name="CountryCode">PSE</field>
799 <field name="District">Rafah</field>
800 <field name="Population">92020</field>
801 </row>
802 </table_data>
803 </database>
804 </mysqldump>
805 You can also set the following variables by using --var_name=value
807 syntax:
808 · max_allowed_packet
810 The maximum size of the buffer for client/server communication. The
812 maximum is 1GB.
813 · net_buffer_length
815 The initial size of the buffer for client/server communication.
817 When creating multiple-row INSERT statements (as with the
818 --extended-insert or --opt option), mysqldump creates rows up to
819 net_buffer_length length. If you increase this variable, you should
820 also ensure that the net_buffer_length variable in the MySQL server
821 is at least this large.
822 A common use of mysqldump is for making a backup of an entire database:
824 shell> mysqldump db_name > backup-file.sql
826 You can load the dump file back into the server like this:
828 shell> mysql db_name < backup-file.sql
830 Or like this:
832 shell> mysql -e "source /path-to-backup/backup-file.sql" db_name
834 mysqldump is also very useful for populating databases by copying data
836 from one MySQL server to another:
837 shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
839 It is possible to dump several databases with one command:
841 shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql
843 To dump all databases, use the --all-databases option:
845 shell> mysqldump --all-databases > all_databases.sql
847 For InnoDB tables, mysqldump provides a way of making an online backup:
849 shell> mysqldump --all-databases --single-transaction > all_databases.sql
851 This backup acquires a global read lock on all tables (using FLUSH
853 TABLES WITH READ LOCK) at the beginning of the dump. As soon as this
854 lock has been acquired, the binary log coordinates are read and the
855 lock is released. If long updating statements are running when the
856 FLUSH statement is issued, the MySQL server may get stalled until those
857 statements finish. After that, the dump becomes lock free and does not
858 disturb reads and writes on the tables. If the update statements that
859 the MySQL server receives are short (in terms of execution time), the
860 initial lock period should not be noticeable, even with many updates.
861 For point-in-time recovery (also known as “roll-forward,” when you need
863 to restore an old backup and replay the changes that happened since
864 that backup), it is often useful to rotate the binary log (see
865 Section 5.2.4, “The Binary Log”) or at least know the binary log
866 coordinates to which the dump corresponds:
867 shell> mysqldump --all-databases --master-data=2 > all_databases.sql
869 Or:
871 shell> mysqldump --all-databases --flush-logs --master-data=2
873 > all_databases.sql
874 The --master-data and --single-transaction options can be used
876 simultaneously, which provides a convenient way to make an online
877 backup suitable for use prior to point-in-time recovery if tables are
878 stored using the InnoDB storage engine.
879 For more information on making backups, see Section 6.2, “Database
881 Backup Methods”, and Section 6.3, “Example Backup and Recovery
882 Strategy”.
883 If you encounter problems backing up views, please read the section
885 that covers restrictions on views which describes a workaround for
886 backing up views when this fails due to insufficient privileges. See
887 Section E.4, “Restrictions on Views”.
888
890 Copyright © 1997, 2011, Oracle and/or its affiliates. All rights
891 reserved.
892 This documentation is free software; you can redistribute it and/or
894 modify it only under the terms of the GNU General Public License as
895 published by the Free Software Foundation; version 2 of the License.
896 This documentation is distributed in the hope that it will be useful,
898 but WITHOUT ANY WARRANTY; without even the implied warranty of
899 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
900 General Public License for more details.
901 You should have received a copy of the GNU General Public License along
903 with the program; if not, write to the Free Software Foundation, Inc.,
904 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
905 http://www.gnu.org/licenses/.
906
909 For more information, please refer to the MySQL Reference Manual, which
910 may already be installed locally and which is also available online at
911 http://dev.mysql.com/doc/.
912
914 Oracle Corporation (http://dev.mysql.com/).
915MySQL 5.1 10/26/2011 MYSQLDUMP(1)