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 mysqldump requires at least the SELECT privilege for dumped tables,
20 SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK
21 TABLES if the --single-transaction option is not used. Certain options
22 might require other privileges as noted in the option descriptions.
23 To reload a dump file, you must have the same privileges needed to
25 create each of the dumped objects by issuing CREATE statements
26 manually.
27 mysqldump output can include ALTER DATABASE statements that change the
29 database collation. These may be used when dumping stored programs to
30 preserve their character encodings. To reload a dump file containing
31 such statements, the ALTER privilege for the affected database is
32 required.
33 If you are performing a backup on the server and your tables all are
35 MyISAM tables, you can also use mysqlhotcopy for this purpose.
36 Syntax
37 There are in general three ways to use mysqldump—in order to dump a set
39 of one or more tables, a set of one or more complete databases, or an
40 entire MySQL server—as shown here:
41 shell> mysqldump [options] db_name [tbl_name ...]
43 shell> mysqldump [options] --databases db_name ...
44 shell> mysqldump [options] --all-databases
45 To dump entire databases, do not name any tables following db_name, or
47 use the --databases or --all-databases option.
48 mysqldump does not dump the INFORMATION_SCHEMA database by default. As
50 of MySQL 5.1.38, mysqldump dumps INFORMATION_SCHEMA if you name it
51 explicitly on the command line, although you must also use the
52 --skip-lock-tables option. Before 5.1.38, mysqldump silently ignores
53 INFORMATION_SCHEMA even if you name it explicitly on the command line.
54 Before MySQL 5.1.64, mysqldump does not dump the general_log or
56 slow_query_log tables for dumps of the mysql database. As of 5.1.64,
57 the dump includes statements to recreate those tables so that they are
58 not missing after reloading the dump file. Log table contents are not
59 dumped.
60 In MySQL Cluster NDB 7.1.7 and later, the ndbinfo information database
62 is ignored and not dumped by mysqldump.
63 To see a list of the options your version of mysqldump supports,
65 execute mysqldump --help.
66 Some mysqldump options are shorthand for groups of other options:
68 · Use of --opt is the same as specifying --add-drop-table,
70 --add-locks, --create-options, --disable-keys, --extended-insert,
71 --lock-tables, --quick, and --set-charset. All of the options that
72 --opt stands for also are on by default because --opt is on by
73 default.
74 · Use of --compact is the same as specifying --skip-add-drop-table,
76 --skip-add-locks, --skip-comments, --skip-disable-keys, and
77 --skip-set-charset options.
78 To reverse the effect of a group option, uses its --skip-xxx form
80 (--skip-opt or --skip-compact). It is also possible to select only part
81 of the effect of a group option by following it with options that
82 enable or disable specific features. Here are some examples:
83 · To select the effect of --opt except for some features, use the
85 --skip option for each feature. To disable extended inserts and
86 memory buffering, use --opt --skip-extended-insert --skip-quick.
87 (Actually, --skip-extended-insert --skip-quick is sufficient
88 because --opt is on by default.)
89 · To reverse --opt for all features except index disabling and table
91 locking, use --skip-opt --disable-keys --lock-tables.
92 When you selectively enable or disable the effect of a group option,
94 order is important because options are processed first to last. For
95 example, --disable-keys --lock-tables --skip-opt would not have the
96 intended effect; it is the same as --skip-opt by itself.
97 mysqldump can retrieve and dump table contents row by row, or it can
99 retrieve the entire content from a table and buffer it in memory before
100 dumping it. Buffering in memory can be a problem if you are dumping
101 large tables. To dump tables row by row, use the --quick option (or
102 --opt, which enables --quick). The --opt option (and hence --quick) is
103 enabled by default, so to enable memory buffering, use --skip-quick.
104 If you are using a recent version of mysqldump to generate a dump to be
106 reloaded into a very old MySQL server, you should not use the --opt or
107 --extended-insert option. Use --skip-opt instead.
108 Note
110 mysqldump from MySQL 5.1.21 cannot be used to create dumps from
111 MySQL server 5.1.20 and older. This issue is fixed in MySQL 5.1.22.
112 (Bug #30123)
113 mysqldump supports the following options, which can be specified on the
115 command line or in the [mysqldump] and [client] groups of an option
116 file. mysqldump also supports the options for processing option files
117 described at Section 4.2.3.4, “Command-Line Options that Affect Option-
118 File Handling”.
119 · --help, -?
121 Display a help message and exit.
123 · --add-drop-database
125 Add a DROP DATABASE statement before each CREATE DATABASE
127 statement. This option is typically used in conjunction with the
128 --all-databases or --databases option because no CREATE DATABASE
129 statements are written unless one of those options is specified.
130 · --add-drop-table
132 Add a DROP TABLE statement before each CREATE TABLE statement.
134 · --add-drop-trigger
136 Add a DROP TRIGGER statement before each CREATE TRIGGER statement.
138 Note
140 This option is supported only by mysqldump as supplied with
141 MySQL Cluster NDB 6.3.38, MySQL Cluster NDB 7.0.19, MySQL
142 Cluster NDB 7.1.8, and later MySQL Cluster releases. It is not
143 available when using MySQL 5.1.
144 · --add-locks
146 Surround each table dump with LOCK TABLES and UNLOCK TABLES
148 statements. This results in faster inserts when the dump file is
149 reloaded. See Section 8.3.2.1, “Speed of INSERT Statements”.
150 · --all-databases, -A
152 Dump all tables in all databases. This is the same as using the
154 --databases option and naming all the databases on the command
155 line.
156 · --all-tablespaces, -Y
158 Adds to a table dump all SQL statements needed to create any
160 tablespaces used by an NDBCLUSTER table. This information is not
161 otherwise included in the output from mysqldump. This option is
162 currently relevant only to MySQL Cluster tables.
163 This option was added in MySQL 5.1.6.
165 · --allow-keywords
167 Permit creation of column names that are keywords. This works by
169 prefixing each column name with the table name.
170 · --bind-address=ip_address
172 On a computer having multiple network interfaces, this option can
174 be used to select which interface is employed when connecting to
175 the MySQL server.
176 This option is supported only in the version of mysqldump that is
178 supplied with MySQL Cluster, beginning with MySQL Cluster NDB
179 6.3.4. It is not available in standard MySQL 5.1 releases.
180 · --character-sets-dir=path
182 The directory where character sets are installed. See Section 10.5,
184 “Character Set Configuration”.
185 · --comments, -i
187 Write additional information in the dump file such as program
189 version, server version, and host. This option is enabled by
190 default. To suppress this additional information, use
191 --skip-comments.
192 · --compact
194 Produce more compact output. This option enables the
196 --skip-add-drop-table, --skip-add-locks, --skip-comments,
197 --skip-disable-keys, and --skip-set-charset options.
198 Note
200 Prior to MySQL 5.1.21, this option did not create valid SQL if
201 the database dump contained views. The recreation of views
202 requires the creation and removal of temporary tables and this
203 option suppressed the removal of those temporary tables. As a
204 workaround, use --compact with the --add-drop-table option and
205 then manually adjust the dump file.
206 · --compatible=name
208 Produce output that is more compatible with other database systems
210 or with older MySQL servers. The value of name can be ansi,
211 mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb,
212 no_key_options, no_table_options, or no_field_options. To use
213 several values, separate them by commas. These values have the same
214 meaning as the corresponding options for setting the server SQL
215 mode. See Section 5.1.7, “Server SQL Modes”.
216 This option does not guarantee compatibility with other servers. It
218 only enables those SQL mode values that are currently available for
219 making dump output more compatible. For example,
220 --compatible=oracle does not map data types to Oracle types or use
221 Oracle comment syntax.
222 This option requires a server version of 4.1.0 or higher. With
224 older servers, it does nothing.
225 · --complete-insert, -c
227 Use complete INSERT statements that include column names.
229 · --compress, -C
231 Compress all information sent between the client and the server if
233 both support compression.
234 · --create-options
236 Include all MySQL-specific table options in the CREATE TABLE
238 statements.
239 · --databases, -B
241 Dump several databases. Normally, mysqldump treats the first name
243 argument on the command line as a database name and following names
244 as table names. With this option, it treats all name arguments as
245 database names. CREATE DATABASE and USE statements are included in
246 the output before each new database.
247 · --debug[=debug_options], -# [debug_options]
249 Write a debugging log. A typical debug_options string is
251 'd:t:o,file_name'. The default value is
252 'd:t:o,/tmp/mysqldump.trace'.
253 · --debug-check
255 Print some debugging information when the program exits. This
257 option was added in MySQL 5.1.21.
258 · --debug-info
260 Print debugging information and memory and CPU usage statistics
262 when the program exits. This option was added in MySQL 5.1.14.
263 · --default-character-set=charset_name
265 Use charset_name as the default character set. See Section 10.5,
267 “Character Set Configuration”. If no character set is specified,
268 mysqldump uses utf8, and earlier versions use latin1.
269 Prior to MySQL 5.1.38, this option has no effect for output data
271 files produced by using the --tab option. See the description for
272 that option.
273 · --delayed-insert
275 Write INSERT DELAYED statements rather than INSERT statements.
277 · --delete-master-logs
279 On a master replication server, delete the binary logs by sending a
281 PURGE BINARY LOGS statement to the server after performing the dump
282 operation. This option automatically enables --master-data.
283 · --disable-keys, -K
285 For each table, surround the INSERT statements with /*!40000 ALTER
287 TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name
288 ENABLE KEYS */; statements. This makes loading the dump file faster
289 because the indexes are created after all rows are inserted. This
290 option is effective only for nonunique indexes of MyISAM tables. It
291 has no effect for other tables.
292 · --dump-date
294 If the --comments option is given, mysqldump produces a comment at
296 the end of the dump of the following form:
297 -- Dump completed on DATE
299 However, the date causes dump files taken at different times to
301 appear to be different, even if the data are otherwise identical.
302 --dump-date and --skip-dump-date control whether the date is added
303 to the comment. The default is --dump-date (include the date in the
304 comment). --skip-dump-date suppresses date printing. This option
305 was added in MySQL 5.1.23.
306 · --events, -E
308 Include Event Scheduler events for the dumped databases in the
310 output. This option was added in MySQL 5.1.8.
311 · --extended-insert, -e
313 Use multiple-row INSERT syntax that include several VALUES lists.
315 This results in a smaller dump file and speeds up inserts when the
316 file is reloaded.
317 · --fields-terminated-by=..., --fields-enclosed-by=...,
319 --fields-optionally-enclosed-by=..., --fields-escaped-by=...
320 These options are used with the --tab option and have the same
322 meaning as the corresponding FIELDS clauses for LOAD DATA INFILE.
323 See Section 13.2.6, “LOAD DATA INFILE Syntax”.
324 · --first-slave
326 Deprecated. Use --lock-all-tables instead. --first-slave is
328 removed in MySQL 5.5.
329 · --flush-logs, -F
331 Flush the MySQL server log files before starting the dump. This
333 option requires the RELOAD privilege. If you use this option in
334 combination with the --all-databases option, the logs are flushed
335 for each database dumped. The exception is when using
336 --lock-all-tables or --master-data: In this case, the logs are
337 flushed only once, corresponding to the moment that all tables are
338 locked. If you want your dump and the log flush to happen at
339 exactly the same moment, you should use --flush-logs together with
340 either --lock-all-tables or --master-data.
341 · --flush-privileges
343 Add a FLUSH PRIVILEGES statement to the dump output after dumping
345 the mysql database. This option should be used any time the dump
346 contains the mysql database and any other database that depends on
347 the data in the mysql database for proper restoration. This option
348 was added in MySQL 5.1.12.
349 · --force, -f
351 Continue even if an SQL error occurs during a table dump.
353 One use for this option is to cause mysqldump to continue executing
355 even when it encounters a view that has become invalid because the
356 definition refers to a table that has been dropped. Without
357 --force, mysqldump exits with an error message. With --force,
358 mysqldump prints the error message, but it also writes an SQL
359 comment containing the view definition to the dump output and
360 continues executing.
361 · --host=host_name, -h host_name
363 Dump data from the MySQL server on the given host. The default host
365 is localhost.
366 · --hex-blob
368 Dump binary columns using hexadecimal notation (for example, 'abc'
370 becomes 0x616263). The affected data types are BINARY, VARBINARY,
371 the BLOB types, and BIT.
372 · --ignore-table=db_name.tbl_name
374 Do not dump the given table, which must be specified using both the
376 database and table names. To ignore multiple tables, use this
377 option multiple times. This option also can be used to ignore
378 views.
379 · --insert-ignore
381 Write INSERT IGNORE statements rather than INSERT statements.
383 · --lines-terminated-by=...
385 This option is used with the --tab option and has the same meaning
387 as the corresponding LINES clause for LOAD DATA INFILE. See
388 Section 13.2.6, “LOAD DATA INFILE Syntax”.
389 · --lock-all-tables, -x
391 Lock all tables across all databases. This is achieved by acquiring
393 a global read lock for the duration of the whole dump. This option
394 automatically turns off --single-transaction and --lock-tables.
395 · --lock-tables, -l
397 For each dumped database, lock all tables to be dumped before
399 dumping them. The tables are locked with READ LOCAL to permit
400 concurrent inserts in the case of MyISAM tables. For transactional
401 tables such as InnoDB, --single-transaction is a much better option
402 than --lock-tables because it does not need to lock the tables at
403 all.
404 Because --lock-tables locks tables for each database separately,
406 this option does not guarantee that the tables in the dump file are
407 logically consistent between databases. Tables in different
408 databases may be dumped in completely different states.
409 · --log-error=file_name
411 Log warnings and errors by appending them to the named file. The
413 default is to do no logging. This option was added in MySQL 5.1.18.
414 · --master-data[=value]
416 Use this option to dump a master replication server to produce a
418 dump file that can be used to set up another server as a slave of
419 the master. It causes the dump output to include a CHANGE MASTER TO
420 statement that indicates the binary log coordinates (file name and
421 position) of the dumped server. These are the master server
422 coordinates from which the slave should start replicating after you
423 load the dump file into the slave.
424 If the option value is 2, the CHANGE MASTER TO statement is written
426 as an SQL comment, and thus is informative only; it has no effect
427 when the dump file is reloaded. If the option value is 1, the
428 statement is not written as a comment and takes effect when the
429 dump file is reloaded. If no option value is specified, the default
430 value is 1.
431 This option requires the RELOAD privilege and the binary log must
433 be enabled.
434 The --master-data option automatically turns off --lock-tables. It
436 also turns on --lock-all-tables, unless --single-transaction also
437 is specified, in which case, a global read lock is acquired only
438 for a short time at the beginning of the dump (see the description
439 for --single-transaction). In all cases, any action on logs happens
440 at the exact moment of the dump.
441 It is also possible to set up a slave by dumping an existing slave
443 of the master. To do this, use the following procedure on the
444 existing slave:
445 1. Stop the slave's SQL thread and get its current status:
447 mysql> STOP SLAVE SQL_THREAD;
449 mysql> SHOW SLAVE STATUS;
450 2. From the output of the SHOW SLAVE STATUS statement, the binary
452 log coordinates of the master server from which the new slave
453 should start replicating are the values of the
454 Relay_Master_Log_File and Exec_Master_Log_Pos fields. Denote
455 those values as file_name and file_pos.
456 3. Dump the slave server:
458 shell> mysqldump --master-data=2 --all-databases > dumpfile
460 Using --master-data=2 works only if binary logging has been
462 enabled on the slave. Otherwise, mysqldump fails with the error
463 Binlogging on server not active. In this case you must handle
464 any locking issues in another manner, using one or more of
465 --add-locks, --lock-tables, --lock-all-tables, or
466 --single-transaction, as required by your application and
467 environment.
468 4. Restart the slave:
470 mysql> START SLAVE;
472 5. On the new slave, load the dump file:
474 shell> mysql < dumpfile
476 6. On the new slave, set the replication coordinates to those of
478 the master server obtained earlier:
479 mysql> CHANGE MASTER TO
481 -> MASTER_LOG_FILE = 'file_name', MASTER_LOG_POS = file_pos;
482 The CHANGE MASTER TO statement might also need other
484 parameters, such as MASTER_HOST to point the slave to the
485 correct master server host. Add any such parameters as
486 necessary.
487 · --no-autocommit
489 Enclose the INSERT statements for each dumped table within SET
491 autocommit = 0 and COMMIT statements.
492 · --no-create-db, -n
494 This option suppresses the CREATE DATABASE statements that are
496 otherwise included in the output if the --databases or
497 --all-databases option is given.
498 · --no-create-info, -t
500 Do not write CREATE TABLE statements that re-create each dumped
502 table.
503 Note
505 This option does not not exclude statements creating log file
506 groups or tablespaces from mysqldump output; in MySQL 5.1.14
507 and later, you can use the --no-tablespaces option for this
508 purpose.
509 · --no-data, -d
511 Do not write any table row information (that is, do not dump table
513 contents). This is useful if you want to dump only the CREATE TABLE
514 statement for the table (for example, to create an empty copy of
515 the table by loading the dump file).
516 · --no-set-names, -N
518 This has the same effect as --skip-set-charset.
520 · --no-tablespaces, -y
522 This option suppresses all CREATE LOGFILE GROUP and CREATE
524 TABLESPACE statements in the output of mysqldump.
525 This option was added in MySQL 5.1.14.
527 · --opt
529 This option is shorthand. It is the same as specifying
531 --add-drop-table --add-locks --create-options --disable-keys
532 --extended-insert --lock-tables --quick --set-charset. It should
533 give you a fast dump operation and produce a dump file that can be
534 reloaded into a MySQL server quickly.
535 The --opt option is enabled by default. Use --skip-opt to disable
537 it. See the discussion at the beginning of this section for
538 information about selectively enabling or disabling a subset of the
539 options affected by --opt.
540 · --order-by-primary
542 Dump each table's rows sorted by its primary key, or by its first
544 unique index, if such an index exists. This is useful when dumping
545 a MyISAM table to be loaded into an InnoDB table, but will make the
546 dump operation take considerably longer.
547 · --password[=password], -p[password]
549 The password to use when connecting to the server. If you use the
551 short option form (-p), you cannot have a space between the option
552 and the password. If you omit the password value following the
553 --password or -p option on the command line, mysqldump prompts for
554 one.
555 Specifying a password on the command line should be considered
557 insecure. See Section 6.1.2.1, “End-User Guidelines for Password
558 Security”. You can use an option file to avoid giving the password
559 on the command line.
560 · --pipe, -W
562 On Windows, connect to the server using a named pipe. This option
564 applies only if the server supports named-pipe connections.
565 · --port=port_num, -P port_num
567 The TCP/IP port number to use for the connection.
569 · --protocol={TCP|SOCKET|PIPE|MEMORY}
571 The connection protocol to use for connecting to the server. It is
573 useful when the other connection parameters normally would cause a
574 protocol to be used other than the one you want. For details on the
575 permissible values, see Section 4.2.2, “Connecting to the MySQL
576 Server”.
577 · --quick, -q
579 This option is useful for dumping large tables. It forces mysqldump
581 to retrieve rows for a table from the server a row at a time rather
582 than retrieving the entire row set and buffering it in memory
583 before writing it out.
584 · --quote-names, -Q
586 Quote identifiers (such as database, table, and column names)
588 within “`” characters. If the ANSI_QUOTES SQL mode is enabled,
589 identifiers are quoted within “"” characters. This option is
590 enabled by default. It can be disabled with --skip-quote-names, but
591 this option should be given after any option such as --compatible
592 that may enable --quote-names.
593 · --replace
595 Write REPLACE statements rather than INSERT statements. This option
597 was added in MySQL 5.1.3.
598 · --result-file=file_name, -r file_name
600 Direct output to a given file. This option should be used on
602 Windows to prevent newline “\n” characters from being converted to
603 “\r\n” carriage return/newline sequences. The result file is
604 created and its previous contents overwritten, even if an error
605 occurs while generating the dump.
606 · --routines, -R
608 Include stored routines (procedures and functions) for the dumped
610 databases in the output. Use of this option requires the SELECT
611 privilege for the mysql.proc table. The output generated by using
612 --routines contains CREATE PROCEDURE and CREATE FUNCTION statements
613 to re-create the routines. However, these statements do not include
614 attributes such as the routine creation and modification
615 timestamps. This means that when the routines are reloaded, they
616 will be created with the timestamps equal to the reload time.
617 If you require routines to be re-created with their original
619 timestamp attributes, do not use --routines. Instead, dump and
620 reload the contents of the mysql.proc table directly, using a MySQL
621 account that has appropriate privileges for the mysql database.
622 This option was added in MySQL 5.1.2. Before that, stored routines
624 are not dumped. Routine DEFINER values are not dumped until MySQL
625 5.1.8. This means that before 5.1.8, when routines are reloaded,
626 they will be created with the definer set to the reloading user. If
627 you require routines to be re-created with their original definer,
628 dump and load the contents of the mysql.proc table directly as
629 described earlier.
630 Prior to MySQL 5.1.62, this option had no effect when used together
632 with the --xml option. (Bug #11760384, Bug #52792)
633 · --set-charset
635 Add SET NAMES default_character_set to the output. This option is
637 enabled by default. To suppress the SET NAMES statement, use
638 --skip-set-charset.
639 · --single-transaction
641 This option sets the transaction isolation mode to REPEATABLE READ
643 and sends a START TRANSACTION SQL statement to the server before
644 dumping data. It is useful only with transactional tables such as
645 InnoDB, because then it dumps the consistent state of the database
646 at the time when START TRANSACTION was issued without blocking any
647 applications.
648 When using this option, you should keep in mind that only InnoDB
650 tables are dumped in a consistent state. For example, any MyISAM or
651 MEMORY tables dumped while using this option may still change
652 state.
653 While a --single-transaction dump is in process, to ensure a valid
655 dump file (correct table contents and binary log coordinates), no
656 other connection should use the following statements: ALTER TABLE,
657 CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A
658 consistent read is not isolated from those statements, so use of
659 them on a table to be dumped can cause the SELECT that is performed
660 by mysqldump to retrieve the table contents to obtain incorrect
661 contents or fail.
662 The --single-transaction option and the --lock-tables option are
664 mutually exclusive because LOCK TABLES causes any pending
665 transactions to be committed implicitly.
666 This option is not supported for MySQL Cluster tables; the results
668 cannot be guaranteed to be consistent due to the fact that the
669 NDBCLUSTER storage engine supports only the READ_COMMITTED
670 transaction isolation level. You should always use NDB backup and
671 restore instead.
672 To dump large tables, you should combine the --single-transaction
674 option with --quick.
675 · --skip-comments
677 See the description for the --comments option.
679 · --skip-opt
681 See the description for the --opt option.
683 · --socket=path, -S path
685 For connections to localhost, the Unix socket file to use, or, on
687 Windows, the name of the named pipe to use.
688 · --ssl*
690 Options that begin with --ssl specify whether to connect to the
692 server using SSL and indicate where to find SSL keys and
693 certificates. See Section 6.3.6.4, “SSL Command Options”.
694 · --tab=path, -T path
696 Produce tab-separated text-format data files. For each dumped
698 table, mysqldump creates a tbl_name.sql file that contains the
699 CREATE TABLE statement that creates the table, and the server
700 writes a tbl_name.txt file that contains its data. The option value
701 is the directory in which to write the files.
702 Note
704 This option should be used only when mysqldump is run on the
705 same machine as the mysqld server. You must have the FILE
706 privilege, and the server must have permission to write files
707 in the directory that you specify.
708 By default, the .txt data files are formatted using tab characters
709 between column values and a newline at the end of each line. The
710 format can be specified explicitly using the --fields-xxx and
711 --lines-terminated-by options.
712 As of MySQL 5.1.38, column values are converted to the character
714 set specified by the --default-character-set option. Prior to
715 5.1.38 or if no such option is present, values are dumped using the
716 binary character set. In effect, there is no character set
717 conversion. If a table contains columns in several character sets,
718 the output data file will as well and you may not be able to reload
719 the file correctly.
720 · --tables
722 Override the --databases or -B option. mysqldump regards all name
724 arguments following the option as table names.
725 · --triggers
727 Include triggers for each dumped table in the output. This option
729 is enabled by default; disable it with --skip-triggers.
730 · --tz-utc
732 This option enables TIMESTAMP columns to be dumped and reloaded
734 between servers in different time zones. mysqldump sets its
735 connection time zone to UTC and adds SET TIME_ZONE='+00:00' to the
736 dump file. Without this option, TIMESTAMP columns are dumped and
737 reloaded in the time zones local to the source and destination
738 servers, which can cause the values to change if the servers are in
739 different time zones. --tz-utc also protects against changes due
740 to daylight saving time. --tz-utc is enabled by default. To
741 disable it, use --skip-tz-utc. This option was added in MySQL
742 5.1.2.
743 · --user=user_name, -u user_name
745 The MySQL user name to use when connecting to the server.
747 · --verbose, -v
749 Verbose mode. Print more information about what the program does.
751 · --version, -V
753 Display version information and exit.
755 · --where='where_condition', -w 'where_condition'
757 Dump only rows selected by the given WHERE condition. Quotes around
759 the condition are mandatory if it contains spaces or other
760 characters that are special to your command interpreter.
761 Examples:
763 --where="user='jimf'"
765 -w"userid>1"
766 -w"userid<1"
767 · --xml, -X
769 Write dump output as well-formed XML.
771 NULL, 'NULL', and Empty Values: For a column named column_name, the
773 NULL value, an empty string, and the string value 'NULL' are
774 distinguished from one another in the output generated by this
775 option as follows.
776 ┌──────────────────────┬─────────────────────────────────┐
778 │Value: │ XML Representation: │
779 ├──────────────────────┼─────────────────────────────────┤
780 │NULL (unknown value) │ <field name="column_name" │
781 │ │ xsi:nil="true" /> │
782 ├──────────────────────┼─────────────────────────────────┤
783 │'' (empty string) │ <field │
784 │ │ name="column_name"></field> │
785 ├──────────────────────┼─────────────────────────────────┤
786 │'NULL' (string value) │ <field │
787 │ │ name="column_name">NULL</field> │
788 └──────────────────────┴─────────────────────────────────┘
789 Beginning with MySQL 5.1.12, the output from the mysql client when
790 run using the --xml option also follows the preceding rules. (See
791 the section called “MYSQL OPTIONS”.)
792 Beginning with MySQL 5.1.18, XML output from mysqldump includes the
794 XML namespace, as shown here:
795 shell> mysqldump --xml -u root world City
797 <?xml version="1.0"?>
798 <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
799 <database name="world">
800 <table_structure name="City">
801 <field Field="ID" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" />
802 <field Field="Name" Type="char(35)" Null="NO" Key="" Default="" Extra="" />
803 <field Field="CountryCode" Type="char(3)" Null="NO" Key="" Default="" Extra="" />
804 <field Field="District" Type="char(20)" Null="NO" Key="" Default="" Extra="" />
805 <field Field="Population" Type="int(11)" Null="NO" Key="" Default="0" Extra="" />
806 <key Table="City" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="ID"
807 Collation="A" Cardinality="4079" Null="" Index_type="BTREE" Comment="" />
808 <options Name="City" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="4079"
809 Avg_row_length="67" Data_length="273293" Max_data_length="18858823439613951"
810 Index_length="43008" Data_free="0" Auto_increment="4080"
811 Create_time="2007-03-31 01:47:01" Update_time="2007-03-31 01:47:02"
812 Collation="latin1_swedish_ci" Create_options="" Comment="" />
813 </table_structure>
814 <table_data name="City">
815 <row>
816 <field name="ID">1</field>
817 <field name="Name">Kabul</field>
818 <field name="CountryCode">AFG</field>
819 <field name="District">Kabol</field>
820 <field name="Population">1780000</field>
821 </row>
822 ...
823 <row>
824 <field name="ID">4079</field>
825 <field name="Name">Rafah</field>
826 <field name="CountryCode">PSE</field>
827 <field name="District">Rafah</field>
828 <field name="Population">92020</field>
829 </row>
830 </table_data>
831 </database>
832 </mysqldump>
833 Prior to MySQL 5.1.62, this option prevented the --routines option from
835 working correctly—that is, no stored routines, triggers, or events
836 could be dumped in XML format. (Bug #11760384, Bug #52792)
837 You can also set the following variables by using --var_name=value
839 syntax:
840 · max_allowed_packet
842 The maximum size of the buffer for client/server communication. The
844 default is 24MB, the maximum is 1GB.
845 · net_buffer_length
847 The initial size of the buffer for client/server communication.
849 When creating multiple-row INSERT statements (as with the
850 --extended-insert or --opt option), mysqldump creates rows up to
851 net_buffer_length length. If you increase this variable, you should
852 also ensure that the net_buffer_length variable in the MySQL server
853 is at least this large.
854 A common use of mysqldump is for making a backup of an entire database:
856 shell> mysqldump db_name > backup-file.sql
858 You can load the dump file back into the server like this:
860 shell> mysql db_name < backup-file.sql
862 Or like this:
864 shell> mysql -e "source /path-to-backup/backup-file.sql" db_name
866 mysqldump is also very useful for populating databases by copying data
868 from one MySQL server to another:
869 shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
871 It is possible to dump several databases with one command:
873 shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql
875 To dump all databases, use the --all-databases option:
877 shell> mysqldump --all-databases > all_databases.sql
879 For InnoDB tables, mysqldump provides a way of making an online backup:
881 shell> mysqldump --all-databases --single-transaction > all_databases.sql
883 This backup acquires a global read lock on all tables (using FLUSH
885 TABLES WITH READ LOCK) at the beginning of the dump. As soon as this
886 lock has been acquired, the binary log coordinates are read and the
887 lock is released. If long updating statements are running when the
888 FLUSH statement is issued, the MySQL server may get stalled until those
889 statements finish. After that, the dump becomes lock free and does not
890 disturb reads and writes on the tables. If the update statements that
891 the MySQL server receives are short (in terms of execution time), the
892 initial lock period should not be noticeable, even with many updates.
893 For point-in-time recovery (also known as “roll-forward,” when you need
895 to restore an old backup and replay the changes that happened since
896 that backup), it is often useful to rotate the binary log (see
897 Section 5.2.4, “The Binary Log”) or at least know the binary log
898 coordinates to which the dump corresponds:
899 shell> mysqldump --all-databases --master-data=2 > all_databases.sql
901 Or:
903 shell> mysqldump --all-databases --flush-logs --master-data=2
905 > all_databases.sql
906 The --master-data and --single-transaction options can be used
908 simultaneously, which provides a convenient way to make an online
909 backup suitable for use prior to point-in-time recovery if tables are
910 stored using the InnoDB storage engine.
911 For more information on making backups, see Section 7.2, “Database
913 Backup Methods”, and Section 7.3, “Example Backup and Recovery
914 Strategy”.
915 If you encounter problems backing up views, please read the section
917 that covers restrictions on views which describes a workaround for
918 backing up views when this fails due to insufficient privileges. See
919 Section E.4, “Restrictions on Views”.
920
922 Copyright © 1997, 2013, Oracle and/or its affiliates. All rights
923 reserved.
924 This documentation is free software; you can redistribute it and/or
926 modify it only under the terms of the GNU General Public License as
927 published by the Free Software Foundation; version 2 of the License.
928 This documentation is distributed in the hope that it will be useful,
930 but WITHOUT ANY WARRANTY; without even the implied warranty of
931 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
932 General Public License for more details.
933 You should have received a copy of the GNU General Public License along
935 with the program; if not, write to the Free Software Foundation, Inc.,
936 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
937 http://www.gnu.org/licenses/.
938
941 For more information, please refer to the MySQL Reference Manual, which
942 may already be installed locally and which is also available online at
943 http://dev.mysql.com/doc/.
944
946 Oracle Corporation (http://dev.mysql.com/).
947MySQL 5.1 11/04/2013 MYSQLDUMP(1)