1MYSQLDUMP(1) MariaDB 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 MariaDB 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 or performance_schema
33 databases by default. To dump these, name them explicitly on the
34 command line, although you must also use the --skip-lock-tables option.
35 To see a list of the options your version of mysqldump supports,
37 execute mysqldump --help.
38 Some mysqldump options are shorthand for groups of other options:
40 · Use of --opt is the same as specifying --add-drop-table,
42 --add-locks, --create-options, --disable-keys, --extended-insert,
43 --lock-tables, --quick, and --set-charset. All of the options that
44 --opt stands for also are on by default because --opt is on by
45 default.
46 · Use of --compact is the same as specifying --skip-add-drop-table,
48 --skip-add-locks, --skip-comments, --skip-disable-keys, and
49 --skip-set-charset options.
50 To reverse the effect of a group option, uses its --skip-xxx form
52 (--skip-opt or --skip-compact). It is also possible to select only part
53 of the effect of a group option by following it with options that
54 enable or disable specific features. Here are some examples:
55 · To select the effect of --opt except for some features, use the
57 --skip option for each feature. To disable extended inserts and
58 memory buffering, use --opt --skip-extended-insert --skip-quick.
59 (Actually, --skip-extended-insert --skip-quick is sufficient
60 because --opt is on by default.)
61 · To reverse --opt for all features except index disabling and table
63 locking, use --skip-opt --disable-keys --lock-tables.
64 When you selectively enable or disable the effect of a group option,
66 order is important because options are processed first to last. For
67 example, --disable-keys --lock-tables --skip-opt would not have the
68 intended effect; it is the same as --skip-opt by itself.
69 mysqldump can retrieve and dump table contents row by row, or it can
71 retrieve the entire content from a table and buffer it in memory before
72 dumping it. Buffering in memory can be a problem if you are dumping
73 large tables. To dump tables row by row, use the --quick option (or
74 --opt, which enables --quick). The --opt option (and hence --quick) is
75 enabled by default, so to enable memory buffering, use --skip-quick.
76 If you are using a recent version of mysqldump to generate a dump to be
78 reloaded into a very old MySQL server, you should not use the --opt or
79 --extended-insert option. Use --skip-opt instead.
80 mysqldump supports the following options, which can be specified on the
82 command line or in the [mysqldump] and [client] option file groups.
83 mysqldump also supports the options for processing option file.
84 · --help, -?
86 Display a help message and exit.
88 · --add-drop-database
90 Add a DROP DATABASE statement before each CREATE DATABASE
92 statement. This option is typically used in conjunction with the
93 --all-databases or --databases option because no CREATE DATABASE
94 statements are written unless one of those options is specified.
95 · --add-drop-table
97 Add a DROP TABLE statement before each CREATE TABLE statement.
99 · --add-drop-trigger
101 Add a DROP TRIGGER statement before each CREATE TRIGGER statement.
103 · --add-locks
105 Surround each table dump with LOCK TABLES and UNLOCK TABLES
107 statements. This results in faster inserts when the dump file is
108 reloaded.
109 · --all-databases, -A
111 Dump all tables in all databases. This is the same as using the
113 --databases option and naming all the databases on the command
114 line.
115 · --all-tablespaces, -Y
117 Adds to a table dump all SQL statements needed to create any
119 tablespaces used by an NDBCLUSTER table. This information is not
120 otherwise included in the output from mysqldump. This option is
121 currently relevant only to MySQL Cluster tables.
122 · --allow-keywords
125 Allow creation of column names that are keywords. This works by
127 prefixing each column name with the table name.
128 · --apply-slave-statements
130 Adds 'STOP SLAVE' prior to 'CHANGE MASTER' and 'START SLAVE' to
132 bottom of dump.
133 · --character-sets-dir=path
135 The directory where character sets are installed.
137 · --comments, -i
139 Write additional information in the dump file such as program
141 version, server version, and host. This option is enabled by
142 default. To suppress this additional information, use
143 --skip-comments.
144 · --compact
146 Produce more compact output. This option enables the
148 --skip-add-drop-table, --skip-add-locks, --skip-comments,
149 --skip-disable-keys, and --skip-set-charset options.
150 · --compatible=name
152 Produce output that is more compatible with other database systems
154 or with older MySQL servers. The value of name can be ansi,
155 mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb,
156 no_key_options, no_table_options, or no_field_options. To use
157 several values, separate them by commas. These values have the same
158 meaning as the corresponding options for setting the server SQL
159 mode.
160 This option does not guarantee compatibility with other servers. It
162 only enables those SQL mode values that are currently available for
163 making dump output more compatible. For example,
164 --compatible=oracle does not map data types to Oracle types or use
165 Oracle comment syntax.
166 · --complete-insert, -c
169 Use complete INSERT statements that include column names.
171 · --compress, -C
173 Compress all information sent between the client and the server if
175 both support compression.
176 · --create-options, -a
178 Include all MariaDB-specific table options in the CREATE TABLE
180 statements. Use --skip-create-options to disable.
181 · --databases, -B
183 Dump several databases. Normally, mysqldump treats the first name
185 argument on the command line as a database name and following names
186 as table names. With this option, it treats all name arguments as
187 database names. CREATE DATABASE and USE statements are included in
188 the output before each new database.
189 · --debug[=debug_options], -# [debug_options]
191 Write a debugging log. A typical debug_options string is
193 ´d:t:o,file_name´. The default value is
194 ´d:t:o,/tmp/mysqldump.trace´.
195 · --debug-check
197 Print some debugging information when the program exits.
199 · --debug-info
201 Print debugging information and memory and CPU usage statistics
203 when the program exits.
204 · --default-auth
206 Default authentication client-side plugin to use.
208 · --default-character-set=charset_name
210 Use charset_name as the default character set. If no character set
212 is specified, mysqldump uses utf8.
213 · --defaults-extra-file=filename
216 Set filename as the file to read default options from after the
218 global defaults files has been read. Must be given as first
219 option.
220 · --defaults-file=filename
222 Set filename as the file to read default options from, override
224 global defaults files. Must be given as first option.
225 · --defaults-group-suffix=str,
227 Also read groups with a suffix of str. For example, since mysqldump
229 normally reads the [client] and [mysqldump] groups,
230 --defaults-group-suffix=x would cause it to also read the groups
231 [mysqldump_x] and [client_x].
232 · --delayed-insert
234 Write INSERT DELAYED statements rather than INSERT statements.
236 · --delete-master-logs
238 On a master replication server, delete the binary logs by sending a
240 PURGE BINARY LOGS statement to the server after performing the dump
241 operation. This option automatically enables --master-data.
242 · --disable-keys, -K
244 For each table, surround the INSERT statements with /*!40000 ALTER
246 TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name
247 ENABLE KEYS */; statements. This makes loading the dump file faster
248 because the indexes are created after all rows are inserted. This
249 option is effective only for nonunique indexes of MyISAM tables.
250 · --dump-date
252 If the --comments option is given, mysqldump produces a comment at
254 the end of the dump of the following form:
255 -- Dump completed on DATE
257 However, the date causes dump files taken at different times to
259 appear to be different, even if the data are otherwise identical.
260 --dump-date and --skip-dump-date control whether the date is added
261 to the comment. The default is --dump-date (include the date in the
262 comment). --skip-dump-date suppresses date printing
263 · --dump-slave[=value]
265 Used for producing a dump file from a replication slave server that
267 can be used to set up another slave server with the same master.
268 Causes the binary log position and filename of the master to be
269 appended to the dumped data output. Setting the value to 1 (the
270 default) will print it as a CHANGE MASTER command in the dumped
271 data output; if set to 2, that command will be prefixed with a
272 comment symbol. This option will turn --lock-all-tables on, unless
273 --single-transaction is specified too (in which case a global read
274 lock is only taken a short time at the beginning of the dump -
275 don't forget to read about --single-transaction below). In all
276 cases any action on logs will happen at the exact moment of the
277 dump. Option automatically turns --lock-tables off. Using this
278 option causes mysqldump to stop the slave SQL thread before
279 beginning the dump, and restart it again after completion.
280 · --events, -E
282 Include Event Scheduler events for the dumped databases in the
284 output.
285 · --extended-insert, -e
287 Use multiple-row INSERT syntax that include several VALUES lists.
289 This results in a smaller dump file and speeds up inserts when the
290 file is reloaded.
291 · --fields-terminated-by=..., --fields-enclosed-by=...,
293 --fields-optionally-enclosed-by=..., --fields-escaped-by=...
294 These options are used with the --tab option and have the same
296 meaning as the corresponding FIELDS clauses for LOAD DATA INFILE.
297 · --first-slave
299 Removed in MariaDB 5.5. Use --lock-all-tables instead.
301 · --flashback, -B
303 Support flashback mode.
305 · --flush-logs, -F
307 Flush the MariaDB 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.
324 · --force, -f
326 Continue even if an SQL error occurs during a table dump.
328 One use for this option is to cause mysqldump to continue executing
330 even when it encounters a view that has become invalid because the
331 definition refers to a table that has been dropped. Without
332 --force, mysqldump exits with an error message. With --force,
333 mysqldump prints the error message, but it also writes an SQL
334 comment containing the view definition to the dump output and
335 continues executing.
336 · --gtid
338 Available from MariaDB 10.0.13, and is used together with
340 --master-data and --dump-slave to more conveniently set up a new
341 GTID slave. It causes those options to output SQL statements that
342 configure the slave to use the global transaction ID to connect to
343 the master instead of old-style filename/offset positions. The old-
344 style positions are still included in comments when --gtid is used;
345 likewise the GTID position is included in comments even if --gtid
346 is not used.
347 · --hex-blob
349 Dump binary columns using hexadecimal notation (for example, ´abc´
351 becomes 0x616263). The affected data types are BINARY, VARBINARY,
352 the BLOB types, and BIT.
353 · --host=host_name, -h host_name
355 Dump data from the MariaDB server on the given host. The default
357 host is localhost.
358 · --ignore-table=db_name.tbl_name
360 Do not dump the given table, which must be specified using both the
362 database and table names. To ignore multiple tables, use this
363 option multiple times. This option also can be used to ignore
364 views.
365 · --include-master-host-port
367 Add the MASTER_HOST and MASTER_PORT options for the CHANGE MASTER
369 TO statement when using the --dump-slave option for a slave dump.
370 · --insert-ignore
372 Write INSERT IGNORE statements rather than INSERT statements.
374 · --lines-terminated-by=...
376 This option is used with the --tab option and has the same meaning
378 as the corresponding LINES clause for LOAD DATA INFILE.
379 · --lock-all-tables, -x
381 Lock all tables across all databases. This is achieved by acquiring
383 a global read lock for the duration of the whole dump. This option
384 automatically turns off --single-transaction and --lock-tables.
385 · --lock-tables, -l
387 For each dumped database, lock all tables to be dumped before
389 dumping them. The tables are locked with READ LOCAL to allow
390 concurrent inserts in the case of MyISAM tables. For transactional
391 tables such as InnoDB, --single-transaction is a much better option
392 than --lock-tables because it does not need to lock the tables at
393 all.
394 Because --lock-tables locks tables for each database separately,
396 this option does not guarantee that the tables in the dump file are
397 logically consistent between databases. Tables in different
398 databases may be dumped in completely different states.
399 Use --skip-lock-tables to disable.
401 · --log-error=file_name
403 Log warnings and errors by appending them to the named file. The
405 default is to do no logging.
406 · --log-queries
408 When restoring the dump, the server will, if logging is turned on,
410 log the queries to the general and slow query log. Defaults to on;
411 use --skip-log-queries to disable.
412 · --master-data[=value]
414 Use this option to dump a master replication server to produce a
416 dump file that can be used to set up another server as a slave of
417 the master. It causes the dump output to include a CHANGE MASTER TO
418 statement that indicates the binary log coordinates (file name and
419 position) of the dumped server. These are the master server
420 coordinates from which the slave should start replicating after you
421 load the dump file into the slave.
422 If the option value is 2, the CHANGE MASTER TO statement is written
424 as an SQL comment, and thus is informative only; it has no effect
425 when the dump file is reloaded. If the option value is 1, the
426 statement is not written as a comment and takes effect when the
427 dump file is reloaded. If no option value is specified, the default
428 value is 1.
429 This option requires the RELOAD privilege and the binary log must
431 be enabled.
432 The --master-data option automatically turns off --lock-tables. It
434 also turns on --lock-all-tables, unless --single-transaction also
435 is specified. In all cases, any action on logs happens at the exact
436 moment of the dump.
437 It is also possible to set up a slave by dumping an existing slave
439 of the master. To do this, use the following procedure on the
440 existing slave:
441 1. Stop the slave´s SQL thread and get its current status:
443 mysql> STOP SLAVE SQL_THREAD;
445 mysql> SHOW SLAVE STATUS;
446 2. From the output of the SHOW SLAVE STATUS statement, the binary
448 log coordinates of the master server from which the new slave
449 should start replicating are the values of the
450 Relay_Master_Log_File and Exec_Master_Log_Pos fields. Denote
451 those values as file_name and file_pos.
452 3. Dump the slave server:
454 shell> mysqldump --master-data=2 --all-databases > dumpfile
456 4. Restart the slave:
458 mysql> START SLAVE;
460 5. On the new slave, load the dump file:
462 shell> mysql < dumpfile
464 6. On the new slave, set the replication coordinates to those of
466 the master server obtained earlier:
467 mysql> CHANGE MASTER TO
469 -> MASTER_LOG_FILE = ´file_name´, MASTER_LOG_POS = file_pos;
470 The CHANGE MASTER TO statement might also need other
472 parameters, such as MASTER_HOST to point the slave to the
473 correct master server host. Add any such parameters as
474 necessary.
475 · --max-allowed-packet=length
477 Sets the maximum packet length to send to or receive from server.
479 · --net-buffer-length=length
481 Sets the buffer size for TCP/IP and socket communication.
483 · --no-autocommit
485 Enclose the INSERT statements for each dumped table within SET
487 autocommit = 0 and COMMIT statements.
488 · --no-create-db, -n
490 This option suppresses the CREATE DATABASE statements that are
492 otherwise included in the output if the --databases or
493 --all-databases option is given.
494 · --no-create-info, -t
496 Do not write CREATE TABLE statements that re-create each dumped
498 table.
499 · --no-data, -d
501 Do not write any table row information (that is, do not dump table
503 contents). This is useful if you want to dump only the CREATE TABLE
504 statement for the table (for example, to create an empty copy of
505 the table by loading the dump file).
506 · --no-defaults
508 Do not read default options from any option file. This must be
510 given as the first argument.
511 · --no-set-names, -N
513 This has the same effect as --skip-set-charset.
515 · --opt
517 This option is shorthand. It is the same as specifying
519 --add-drop-table --add-locks --create-options --disable-keys
520 --extended-insert --lock-tables --quick --set-charset. It should
521 give you a fast dump operation and produce a dump file that can be
522 reloaded into a MariaDB server quickly.
523 The --opt option is enabled by default. Use --skip-opt to disable
525 it. See the discussion at the beginning of this section for
526 information about selectively enabling or disabling a subset of the
527 options affected by --opt.
528 · --order-by-primary
530 Dump each table´s rows sorted by its primary key, or by its first
532 unique index, if such an index exists. This is useful when dumping
533 a MyISAM table to be loaded into an InnoDB table, but will make the
534 dump operation take considerably longer.
535 · --password[=password], -p[password]
537 The password to use when connecting to the server. If you use the
539 short option form (-p), you cannot have a space between the option
540 and the password. If you omit the password value following the
541 --password or -p option on the command line, mysqldump prompts for
542 one.
543 Specifying a password on the command line should be considered
545 insecure. You can use an option file to avoid giving the password
546 on the command line.
547 · --pipe, -W
549 On Windows, connect to the server via a named pipe. This option
551 applies only if the server supports named-pipe connections.
552 · --plugin-dir
554 Directory for client-side plugins.
556 · --port=port_num, -P port_num
558 The TCP/IP port number to use for the connection.
560 · --protocol={TCP|SOCKET|PIPE|MEMORY}
562 The connection protocol to use for connecting to the server. It is
564 useful when the other connection parameters normally would cause a
565 protocol to be used other than the one you want.
566 · --quick, -q
568 This option is useful for dumping large tables. It forces mysqldump
570 to retrieve rows for a table from the server a row at a time rather
571 than retrieving the entire row set and buffering it in memory
572 before writing it out.
573 · --print-defaults
575 Print the program argument list and exit. This must be given as the
577 first argument.
578 · --quote-names, -Q
580 Quote identifiers (such as database, table, and column names)
582 within “`” characters. If the ANSI_QUOTES SQL mode is enabled,
583 identifiers are quoted within “"” characters. This option is
584 enabled by default. It can be disabled with --skip-quote-names, but
585 this option should be given after any option such as --compatible
586 that may enable --quote-names.
587 · --replace
589 Write REPLACE statements rather than INSERT statements.
591 · --result-file=file_name, -r file_name
593 Direct output to a given file. This option should be used on
595 Windows to prevent newline “\n” characters from being converted to
596 “\r\n” carriage return/newline sequences. The result file is
597 created and its previous contents overwritten, even if an error
598 occurs while generating the dump.
599 · --routines, -R
601 Included stored routines (procedures and functions) for the dumped
603 databases in the output. Use of this option requires the SELECT
604 privilege for the mysql.proc table. The output generated by using
605 --routines contains CREATE PROCEDURE and CREATE FUNCTION statements
606 to re-create the routines. However, these statements do not include
607 attributes such as the routine creation and modification
608 timestamps. This means that when the routines are reloaded, they
609 will be created with the timestamps equal to the reload time.
610 If you require routines to be re-created with their original
612 timestamp attributes, do not use --routines. Instead, dump and
613 reload the contents of the mysql.proc table directly, using a
614 MariaDB account that has appropriate privileges for the mysql
615 database.
616 · --set-charset
619 Add SET NAMES default_character_set to the output. This option is
621 enabled by default. To suppress the SET NAMES statement, use
622 --skip-set-charset.
623 · --single-transaction
625 This option sends a START TRANSACTION SQL statement to the server
627 before dumping data. It is useful only with transactional tables
628 such as InnoDB, because then it dumps the consistent state of the
629 database at the time when BEGIN was issued without blocking any
630 applications.
631 When using this option, you should keep in mind that only InnoDB
633 tables are dumped in a consistent state. For example, any MyISAM or
634 MEMORY tables dumped while using this option may still change
635 state.
636 While a --single-transaction dump is in process, to ensure a valid
638 dump file (correct table contents and binary log coordinates), no
639 other connection should use the following statements: ALTER TABLE,
640 CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A
641 consistent read is not isolated from those statements, so use of
642 them on a table to be dumped can cause the SELECT that is performed
643 by mysqldump to retrieve the table contents to obtain incorrect
644 contents or fail.
645 The --single-transaction option and the --lock-tables option are
647 mutually exclusive because LOCK TABLES causes any pending
648 transactions to be committed implicitly.
649 To dump large tables, you should combine the --single-transaction
651 option with --quick.
652 · --skip-add-drop-table
654 Disable the --add-drop-table option.
656 · --skip-add-locks
658 Disable the --add-locks option.
660 · --skip-comments
662 Disable the --comments option.
664 · --skip-compact
666 Disable the --compact option.
668 · --skip-disable-keys
670 Disable the --disable-keys option.
672 · --skip-extended-insert
674 Disable the --extended-insert option.
676 · --skip-opt
678 Disable the --opt option.
680 · --skip-quick
682 Disable the --quick option.
684 · --skip-quote-names
686 Disable the --quote-names option.
688 · --skip-set-charset
690 Disable the --set-charset option.
692 · --skip-triggers
694 Disable the --triggers option.
696 · --skip-tz-utc
698 Disable the --tz-utc option.
700 · --socket=path, -S path
702 For connections to localhost, the Unix socket file to use, or, on
704 Windows, the name of the named pipe to use.
705 · --ssl
707 Enable SSL for connection (automatically enabled with other flags).
709 Disable with --skip-ssl.
710 · --ssl-ca=name
712 CA file in PEM format (check OpenSSL docs, implies --ssl).
714 · --ssl-capath=name
716 CA directory (check OpenSSL docs, implies --ssl).
718 · --ssl-cert=name
720 X509 cert in PEM format (check OpenSSL docs, implies --ssl).
722 · --ssl-cipher=name
724 SSL cipher to use (check OpenSSL docs, implies --ssl).
726 · --ssl-key=name
728 X509 key in PEM format (check OpenSSL docs, implies --ssl).
730 · --ssl-crl=name
732 Certificate revocation list (check OpenSSL docs, implies --ssl).
734 · --ssl-crlpath=name
736 Certificate revocation list path (check OpenSSL docs, implies
738 --ssl).
739 · --ssl-verify-server-cert
741 Verify server's "Common Name" in its cert against hostname used
743 when connecting. This option is disabled by default.
744 · --tab=path, -T path
746 Produce tab-separated text-format data files. For each dumped
748 table, mysqldump creates a tbl_name.sql file that contains the
749 CREATE TABLE statement that creates the table, and the server
750 writes a tbl_name.txt file that contains its data. The option value
751 is the directory in which to write the files.
752 Note
754 This option should be used only when mysqldump is run on the
755 same machine as the mysqld server. You must have the FILE
756 privilege, and the server must have permission to write files
757 in the directory that you specify.
758 By default, the .txt data files are formatted using tab characters
759 between column values and a newline at the end of each line. The
760 format can be specified explicitly using the --fields-xxx and
761 --lines-terminated-by options.
762 Column values are converted to the character set specified by the
764 --default-character-set option.
765 · --tables
767 Override the --databases or -B option. mysqldump regards all name
769 arguments following the option as table names.
770 · --triggers
772 Include triggers for each dumped table in the output. This option
774 is enabled by default; disable it with --skip-triggers.
775 · --tz-utc
777 This option enables TIMESTAMP columns to be dumped and reloaded
779 between servers in different time zones. mysqldump sets its
780 connection time zone to UTC and adds SET TIME_ZONE=´+00:00´ to the
781 dump file. Without this option, TIMESTAMP columns are dumped and
782 reloaded in the time zones local to the source and destination
783 servers, which can cause the values to change if the servers are in
784 different time zones. --tz-utc also protects against changes due
785 to daylight saving time. --tz-utc is enabled by default. To
786 disable it, use --skip-tz-utc.
787 · --user=user_name, -u user_name
789 The MariaDB user name to use when connecting to the server.
791 · --verbose, -v
793 Verbose mode. Print more information about what the program does.
795 · --version, -V
797 Display version information and exit.
799 · --where=´where_condition´, -w ´where_condition´
801 Dump only rows selected by the given WHERE condition. Quotes around
803 the condition are mandatory if it contains spaces or other
804 characters that are special to your command interpreter.
805 Examples:
807 --where="user=´jimf´"
809 -w"userid>1"
810 -w"userid<1"
811 · --xml, -X
813 Write dump output as well-formed XML.
815 NULL, ´NULL´, and Empty Values: For a column named column_name, the
817 NULL value, an empty string, and the string value ´NULL´ are
818 distinguished from one another in the output generated by this
819 option as follows.
820 ┌──────────────────────┬───────────────────────────────────────────┐
822 │Value: │ XML Representation: │
823 ├──────────────────────┼───────────────────────────────────────────┤
824 │NULL (unknown value) │ <field name="column_name" xsi:nil="true" │
825 │ │ /> │
826 ├──────────────────────┼───────────────────────────────────────────┤
827 │´´ (empty string) │ <field name="column_name"></field> │
828 ├──────────────────────┼───────────────────────────────────────────┤
829 │´NULL´ (string value) │ <field name="column_name">NULL</field> │
830 └──────────────────────┴───────────────────────────────────────────┘
831 The output from the mysql client when run using the --xml option
832 also follows the preceding rules. (See the section called “MYSQL
833 OPTIONS”.)
834 XML output from mysqldump includes the XML namespace, as shown
836 here:
837 shell> mysqldump --xml -u root world City
839 <?xml version="1.0"?>
840 <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
841 <database name="world">
842 <table_structure name="City">
843 <field Field="ID" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" />
844 <field Field="Name" Type="char(35)" Null="NO" Key="" Default="" Extra="" />
845 <field Field="CountryCode" Type="char(3)" Null="NO" Key="" Default="" Extra="" />
846 <field Field="District" Type="char(20)" Null="NO" Key="" Default="" Extra="" />
847 <field Field="Population" Type="int(11)" Null="NO" Key="" Default="0" Extra="" />
848 <key Table="City" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="ID"
849 Collation="A" Cardinality="4079" Null="" Index_type="BTREE" Comment="" />
850 <options Name="City" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="4079"
851 Avg_row_length="67" Data_length="273293" Max_data_length="18858823439613951"
852 Index_length="43008" Data_free="0" Auto_increment="4080"
853 Create_time="2007-03-31 01:47:01" Update_time="2007-03-31 01:47:02"
854 Collation="latin1_swedish_ci" Create_options="" Comment="" />
855 </table_structure>
856 <table_data name="City">
857 <row>
858 <field name="ID">1</field>
859 <field name="Name">Kabul</field>
860 <field name="CountryCode">AFG</field>
861 <field name="District">Kabol</field>
862 <field name="Population">1780000</field>
863 </row>
864 ...
865 <row>
866 <field name="ID">4079</field>
867 <field name="Name">Rafah</field>
868 <field name="CountryCode">PSE</field>
869 <field name="District">Rafah</field>
870 <field name="Population">92020</field>
871 </row>
872 </table_data>
873 </database>
874 </mysqldump>
875 You can also set the following variables by using --var_name=value
878 syntax:
879 · max_allowed_packet
881 The maximum size of the buffer for client/server communication. The
883 maximum is 1GB.
884 · net_buffer_length
886 The initial size of the buffer for client/server communication.
888 When creating multiple-row INSERT statements (as with the
889 --extended-insert or --opt option), mysqldump creates rows up to
890 net_buffer_length length. If you increase this variable, you should
891 also ensure that the net_buffer_length variable in the MariaDB
892 server is at least this large.
893 A common use of mysqldump is for making a backup of an entire database:
895 shell> mysqldump db_name > backup-file.sql
897 You can load the dump file back into the server like this:
899 shell> mysql db_name < backup-file.sql
901 Or like this:
903 shell> mysql -e "source /path-to-backup/backup-file.sql" db_name
905 mysqldump is also very useful for populating databases by copying data
907 from one MariaDB server to another:
908 shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
910 It is possible to dump several databases with one command:
912 shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql
914 To dump all databases, use the --all-databases option:
916 shell> mysqldump --all-databases > all_databases.sql
918 For InnoDB tables, mysqldump provides a way of making an online backup:
920 shell> mysqldump --all-databases --single-transaction > all_databases.sql
922 This backup acquires a global read lock on all tables (using FLUSH
924 TABLES WITH READ LOCK) at the beginning of the dump. As soon as this
925 lock has been acquired, the binary log coordinates are read and the
926 lock is released. If long updating statements are running when the
927 FLUSH statement is issued, the MariaDB server may get stalled until
928 those statements finish. After that, the dump becomes lock free and
929 does not disturb reads and writes on the tables. If the update
930 statements that the MariaDB server receives are short (in terms of
931 execution time), the initial lock period should not be noticeable, even
932 with many updates.
933 For point-in-time recovery (also known as “roll-forward,” when you need
935 to restore an old backup and replay the changes that happened since
936 that backup), it is often useful to rotate the binary log or at least
937 know the binary log coordinates to which the dump corresponds:
938 shell> mysqldump --all-databases --master-data=2 > all_databases.sql
940 Or:
942 shell> mysqldump --all-databases --flush-logs --master-data=2
944 > all_databases.sql
945 The --master-data and --single-transaction options can be used
947 simultaneously, which provides a convenient way to make an online
948 backup suitable for use prior to point-in-time recovery if tables are
949 stored using the InnoDB storage engine.
950 If you encounter problems backing up views, please read the section
952 that covers restrictions on views which describes a workaround for
953 backing up views when this fails due to insufficient privileges.
954
956 Copyright 2007-2008 MySQL AB, 2008-2010 Sun Microsystems, Inc.,
957 2010-2015 MariaDB Foundation
958 This documentation is free software; you can redistribute it and/or
960 modify it only under the terms of the GNU General Public License as
961 published by the Free Software Foundation; version 2 of the License.
962 This documentation is distributed in the hope that it will be useful,
964 but WITHOUT ANY WARRANTY; without even the implied warranty of
965 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
966 General Public License for more details.
967 You should have received a copy of the GNU General Public License along
969 with the program; if not, write to the Free Software Foundation, Inc.,
970 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA or see
971 http://www.gnu.org/licenses/.
972
975 1. Bug#30123
976 http://bugs.mysql.com/bug.php?id=30123
977
979 For more information, please refer to the MariaDB Knowledge Base,
980 available online at https://mariadb.com/kb/
981
983 MariaDB Foundation (http://www.mariadb.org/).
984MariaDB 10.4 28 March 2019 MYSQLDUMP(1)