1MARIADB-DUMP(1) MariaDB Database System MARIADB-DUMP(1)
2
6 mariadb-dump - a database backup program (mysqldump is now a symlink to
7 mariadb-dump)
8
10 mysqldump [options] [db_name [tbl_name ...]]
11
13 The mysqldump client is a backup program originally written by Igor
14 Romanenko. It can be used to dump a database or a collection of
15 databases for backup or transfer to another SQL server (not necessarily
16 a MariaDB server). The dump typically contains SQL statements to create
17 the table, populate it, or both. However, mysqldump can also be used to
18 generate files in CSV, other delimited text, or XML format.
19 If you are doing a backup on the server and your tables all are MyISAM
21 tables, consider using the mysqlhotcopy instead because it can
22 accomplish faster backups and faster restores. See mysqlhotcopy(1).
23 There are four general ways to invoke mysqldump:
25 shell> mysqldump [options] db_name [tbl_name ...]
27 shell> mysqldump [options] --databases db_name ...
28 shell> mysqldump [options] --all-databases
29 shell> mysqldump [options] --system={options}
30 If you do not name any tables following db_name or if you use the
32 --databases or --all-databases option, entire databases are dumped.
33 mysqldump does not dump the INFORMATION_SCHEMA or performance_schema
35 databases by default. To dump these, name them explicitly on the
36 command line, although you must also use the --skip-lock-tables option.
37 To see a list of the options your version of mysqldump supports,
39 execute mysqldump --help.
40 Some mysqldump options are shorthand for groups of other options:
42 • Use of --opt is the same as specifying --add-drop-table,
44 --add-locks, --create-options, --disable-keys, --extended-insert,
45 --lock-tables, --quick, and --set-charset. All of the options that
46 --opt stands for also are on by default because --opt is on by
47 default.
48 • Use of --compact is the same as specifying --skip-add-drop-table,
50 --skip-add-locks, --skip-comments, --skip-disable-keys, and
51 --skip-set-charset options.
52 To reverse the effect of a group option, uses its --skip-xxx form
54 (--skip-opt or --skip-compact). It is also possible to select only part
55 of the effect of a group option by following it with options that
56 enable or disable specific features. Here are some examples:
57 • To select the effect of --opt except for some features, use the
59 --skip option for each feature. To disable extended inserts and
60 memory buffering, use --opt --skip-extended-insert --skip-quick.
61 (Actually, --skip-extended-insert --skip-quick is sufficient
62 because --opt is on by default.)
63 • To reverse --opt for all features except index disabling and table
65 locking, use --skip-opt --disable-keys --lock-tables.
66 When you selectively enable or disable the effect of a group option,
68 order is important because options are processed first to last. For
69 example, --disable-keys --lock-tables --skip-opt would not have the
70 intended effect; it is the same as --skip-opt by itself.
71 mysqldump can retrieve and dump table contents row by row, or it can
73 retrieve the entire content from a table and buffer it in memory before
74 dumping it. Buffering in memory can be a problem if you are dumping
75 large tables. To dump tables row by row, use the --quick option (or
76 --opt, which enables --quick). The --opt option (and hence --quick) is
77 enabled by default, so to enable memory buffering, use --skip-quick.
78 If you are using a recent version of mysqldump to generate a dump to be
80 reloaded into a very old MySQL server, you should not use the --opt or
81 --extended-insert option. Use --skip-opt instead.
82 mysqldump supports the following options, which can be specified on the
84 command line or in the [mysqldump] and [client] option file groups.
85 mysqldump also supports the options for processing option file.
86 • --help, -?
88 Display a help message and exit.
90 • --add-drop-database
92 Add a DROP DATABASE statement before each CREATE DATABASE
94 statement. This option is typically used in conjunction with the
95 --all-databases or --databases option because no CREATE DATABASE
96 statements are written unless one of those options is specified.
97 • --add-drop-table
99 Add a DROP TABLE statement before each CREATE TABLE statement.
101 • --add-drop-trigger
103 Add a DROP TRIGGER statement before each CREATE TRIGGER statement.
105 • --add-locks
107 Surround each table dump with LOCK TABLES and UNLOCK TABLES
109 statements. This results in faster inserts when the dump file is
110 reloaded.
111 • --all-databases, -A
113 Dump all tables in all databases. This is the same as using the
115 --databases option and naming all the databases on the command
116 line.
117 • --all-tablespaces, -Y
119 Adds to a table dump all SQL statements needed to create any
121 tablespaces used by an NDBCLUSTER table. This information is not
122 otherwise included in the output from mysqldump. This option is
123 currently relevant only to MySQL Cluster tables.
124 • --allow-keywords
127 Allow creation of column names that are keywords. This works by
129 prefixing each column name with the table name.
130 • --apply-slave-statements
132 Adds 'STOP SLAVE' prior to 'CHANGE MASTER' and 'START SLAVE' to
134 bottom of dump.
135 • --character-sets-dir=path
137 The directory where character sets are installed.
139 • --comments, -i
141 Write additional information in the dump file such as program
143 version, server version, and host. This option is enabled by
144 default. To suppress this additional information, use
145 --skip-comments.
146 • --compact
148 Produce more compact output. This option enables the
150 --skip-add-drop-table, --skip-add-locks, --skip-comments,
151 --skip-disable-keys, and --skip-set-charset options.
152 • --compatible=name
154 Produce output that is more compatible with other database systems
156 or with older MySQL servers. The value of name can be ansi,
157 mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb,
158 no_key_options, no_table_options, or no_field_options. To use
159 several values, separate them by commas. These values have the same
160 meaning as the corresponding options for setting the server SQL
161 mode.
162 This option does not guarantee compatibility with other servers. It
164 only enables those SQL mode values that are currently available for
165 making dump output more compatible. For example,
166 --compatible=oracle does not map data types to Oracle types or use
167 Oracle comment syntax.
168 • --complete-insert, -c
171 Use complete INSERT statements that include column names.
173 • --compress, -C
175 Compress all information sent between the client and the server if
177 both support compression.
178 • --copy-s3-tables
180 By default S3 tables are ignored. With this option set, the result
182 file will contain a CREATE statement for a similar Aria table,
183 followed by the table data and ending with an ALTER TABLE xxx
184 ENGINE=S3.
185 • --create-options, -a
187 Include all MariaDB-specific table options in the CREATE TABLE
189 statements. Use --skip-create-options to disable.
190 • --databases, -B
192 Dump several databases. Normally, mysqldump treats the first name
194 argument on the command line as a database name and following names
195 as table names. With this option, it treats all name arguments as
196 database names. CREATE DATABASE and USE statements are included in
197 the output before each new database.
198 • --debug[=debug_options], -# [debug_options]
200 Write a debugging log. A typical debug_options string is
202 ´d:t:o,file_name´. The default value is
203 ´d:t:o,/tmp/mysqldump.trace´.
204 • --debug-check
206 Print some debugging information when the program exits.
208 • --debug-info
210 Print debugging information and memory and CPU usage statistics
212 when the program exits.
213 • --default-auth
215 Default authentication client-side plugin to use.
217 • --default-character-set=charset_name
219 Use charset_name as the default character set. If no character set
221 is specified, mysqldump uses utf8.
222 • --defaults-extra-file=filename
225 Set filename as the file to read default options from after the
227 global defaults files has been read. Must be given as first
228 option.
229 • --defaults-file=filename
231 Set filename as the file to read default options from, override
233 global defaults files. Must be given as first option.
234 • --defaults-group-suffix=str,
236 Also read groups with a suffix of str. For example, since mysqldump
238 normally reads the [client] and [mysqldump] groups,
239 --defaults-group-suffix=x would cause it to also read the groups
240 [mysqldump_x] and [client_x].
241 • --delayed-insert
243 Write INSERT DELAYED statements rather than INSERT statements.
245 • --delete-master-logs
247 On a master replication server, delete the binary logs by sending a
249 PURGE BINARY LOGS statement to the server after performing the dump
250 operation. This option automatically enables --master-data.
251 • --disable-keys, -K
253 For each table, surround the INSERT statements with /*!40000 ALTER
255 TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name
256 ENABLE KEYS */; statements. This makes loading the dump file faster
257 because the indexes are created after all rows are inserted. This
258 option is effective only for nonunique indexes of MyISAM tables.
259 • --dump-date
261 If the --comments option is given, mysqldump produces a comment at
263 the end of the dump of the following form:
264 -- Dump completed on DATE
266 However, the date causes dump files taken at different times to
268 appear to be different, even if the data are otherwise identical.
269 --dump-date and --skip-dump-date control whether the date is added
270 to the comment. The default is --dump-date (include the date in the
271 comment). --skip-dump-date suppresses date printing
272 • --dump-slave[=value]
274 Used for producing a dump file from a replication slave server that
276 can be used to set up another slave server with the same master.
277 Causes the binary log position and filename of the master to be
278 appended to the dumped data output. Setting the value to 1 (the
279 default) will print it as a CHANGE MASTER command in the dumped
280 data output; if set to 2, that command will be prefixed with a
281 comment symbol. This option will turn --lock-all-tables on, unless
282 --single-transaction is specified too (in which case a global read
283 lock is only taken a short time at the beginning of the dump -
284 don't forget to read about --single-transaction below). In all
285 cases any action on logs will happen at the exact moment of the
286 dump. Option automatically turns --lock-tables off. Using this
287 option causes mysqldump to stop the slave SQL thread before
288 beginning the dump, and restart it again after completion.
289 • --events, -E
291 Include Event Scheduler events for the dumped databases in the
293 output.
294 • --extended-insert, -e
296 Use multiple-row INSERT syntax that include several VALUES lists.
298 This results in a smaller dump file and speeds up inserts when the
299 file is reloaded.
300 • --fields-terminated-by=..., --fields-enclosed-by=...,
302 --fields-optionally-enclosed-by=..., --fields-escaped-by=...
303 These options are used with the --tab option and have the same
305 meaning as the corresponding FIELDS clauses for LOAD DATA INFILE.
306 • --first-slave
308 Removed in MariaDB 5.5. Use --lock-all-tables instead.
310 • --flashback, -B
312 Support flashback mode.
314 • --flush-logs, -F
316 Flush the MariaDB server log files before starting the dump. This
318 option requires the RELOAD privilege. If you use this option in
319 combination with the --all-databases option, the logs are flushed
320 for each database dumped. The exception is when using
321 --lock-all-tables or --master-data: In this case, the logs are
322 flushed only once, corresponding to the moment that all tables are
323 locked. If you want your dump and the log flush to happen at
324 exactly the same moment, you should use --flush-logs together with
325 either --lock-all-tables or --master-data.
326 • --flush-privileges
328 Send a FLUSH PRIVILEGES statement to the server after dumping the
330 mysql database. This option should be used any time the dump
331 contains the mysql database and any other database that depends on
332 the data in the mysql database for proper restoration.
333 • --force, -f
335 Continue even if an SQL error occurs during a table dump.
337 One use for this option is to cause mysqldump to continue executing
339 even when it encounters a view that has become invalid because the
340 definition refers to a table that has been dropped. Without
341 --force, mysqldump exits with an error message. With --force,
342 mysqldump prints the error message, but it also writes an SQL
343 comment containing the view definition to the dump output and
344 continues executing.
345 • --gtid
347 Available from MariaDB 10.0.13, and is used together with
349 --master-data and --dump-slave to more conveniently set up a new
350 GTID slave. It causes those options to output SQL statements that
351 configure the slave to use the global transaction ID to connect to
352 the master instead of old-style filename/offset positions. The old-
353 style positions are still included in comments when --gtid is used;
354 likewise the GTID position is included in comments even if --gtid
355 is not used.
356 • --hex-blob
358 Dump binary columns using hexadecimal notation (for example, ´abc´
360 becomes 0x616263). The affected data types are BINARY, VARBINARY,
361 the BLOB types, and BIT.
362 • --host=host_name, -h host_name
364 Dump data from the MariaDB server on the given host. The default
366 host is localhost.
367 • --ignore-table=db_name.tbl_name
369 Do not dump the given table, which must be specified using both the
371 database and table names. To ignore multiple tables, use this
372 option multiple times. This option also can be used to ignore
373 views.
374 • --include-master-host-port
376 Add the MASTER_HOST and MASTER_PORT options for the CHANGE MASTER
378 TO statement when using the --dump-slave option for a slave dump.
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.
388 • --lock-all-tables, -x
390 Lock all tables across all databases. This is achieved by acquiring
392 a global read lock for the duration of the whole dump. This option
393 automatically turns off --single-transaction and --lock-tables.
394 • --lock-tables, -l
396 For each dumped database, lock all tables to be dumped before
398 dumping them. The tables are locked with READ LOCAL to allow
399 concurrent inserts in the case of MyISAM tables. For transactional
400 tables such as InnoDB, --single-transaction is a much better option
401 than --lock-tables because it does not need to lock the tables at
402 all.
403 Because --lock-tables locks tables for each database separately,
405 this option does not guarantee that the tables in the dump file are
406 logically consistent between databases. Tables in different
407 databases may be dumped in completely different states.
408 Use --skip-lock-tables to disable.
410 • --log-error=file_name
412 Log warnings and errors by appending them to the named file. The
414 default is to do no logging.
415 • --log-queries
417 When restoring the dump, the server will, if logging is turned on,
419 log the queries to the general and slow query log. Defaults to on;
420 use --skip-log-queries to disable.
421 • --master-data[=value]
423 Use this option to dump a master replication server to produce a
425 dump file that can be used to set up another server as a slave of
426 the master. It causes the dump output to include a CHANGE MASTER TO
427 statement that indicates the binary log coordinates (file name and
428 position) of the dumped server. These are the master server
429 coordinates from which the slave should start replicating after you
430 load the dump file into the slave.
431 If the option value is 2, the CHANGE MASTER TO statement is written
433 as an SQL comment, and thus is informative only; it has no effect
434 when the dump file is reloaded. If the option value is 1, the
435 statement is not written as a comment and takes effect when the
436 dump file is reloaded. If no option value is specified, the default
437 value is 1.
438 This option requires the RELOAD privilege and the binary log must
440 be enabled.
441 The --master-data option automatically turns off --lock-tables. It
443 also turns on --lock-all-tables, unless --single-transaction also
444 is specified. In all cases, any action on logs happens at the exact
445 moment of the dump.
446 It is also possible to set up a slave by dumping an existing slave
448 of the master. To do this, use the following procedure on the
449 existing slave:
450 1. Stop the slave´s SQL thread and get its current status:
452 mysql> STOP SLAVE SQL_THREAD;
454 mysql> SHOW SLAVE STATUS;
455 2. From the output of the SHOW SLAVE STATUS statement, the binary
457 log coordinates of the master server from which the new slave
458 should start replicating are the values of the
459 Relay_Master_Log_File and Exec_Master_Log_Pos fields. Denote
460 those values as file_name and file_pos.
461 3. Dump the slave server:
463 shell> mysqldump --master-data=2 --all-databases > dumpfile
465 4. Restart the slave:
467 mysql> START SLAVE;
469 5. On the new slave, load the dump file:
471 shell> mysql < dumpfile
473 6. On the new slave, set the replication coordinates to those of
475 the master server obtained earlier:
476 mysql> CHANGE MASTER TO
478 -> MASTER_LOG_FILE = ´file_name´, MASTER_LOG_POS = file_pos;
479 The CHANGE MASTER TO statement might also need other
481 parameters, such as MASTER_HOST to point the slave to the
482 correct master server host. Add any such parameters as
483 necessary.
484 • --max-allowed-packet=length
486 Sets the maximum packet length to send to or receive from server.
488 • --max-statement-time=seconds
490 Sets the maximum time any statement can run before being timed out
492 by the server. (Default value is 0 (no limit))
493 • --net-buffer-length=length
495 Sets the buffer size for TCP/IP and socket communication.
497 • --no-autocommit
499 Enclose the INSERT statements for each dumped table within SET
501 autocommit = 0 and COMMIT statements.
502 • --no-create-db, -n
504 This option suppresses the CREATE DATABASE statements that are
506 otherwise included in the output if the --databases or
507 --all-databases option is given.
508 • --no-create-info, -t
510 Do not write CREATE TABLE statements that re-create each dumped
512 table.
513 • --no-data, -d
515 Do not write any table row information (that is, do not dump table
517 contents). This is useful if you want to dump only the CREATE TABLE
518 statement for the table (for example, to create an empty copy of
519 the table by loading the dump file).
520 • --no-defaults
522 Do not read default options from any option file. This must be
524 given as the first argument.
525 • --no-set-names, -N
527 This has the same effect as --skip-set-charset.
529 • --opt
531 This option is shorthand. It is the same as specifying
533 --add-drop-table --add-locks --create-options --disable-keys
534 --extended-insert --lock-tables --quick --set-charset. It should
535 give you a fast dump operation and produce a dump file that can be
536 reloaded into a MariaDB server quickly.
537 The --opt option is enabled by default. Use --skip-opt to disable
539 it. See the discussion at the beginning of this section for
540 information about selectively enabling or disabling a subset of the
541 options affected by --opt.
542 • --order-by-primary
544 Dump each table´s rows sorted by its primary key, or by its first
546 unique index, if such an index exists. This is useful when dumping
547 a MyISAM table to be loaded into an InnoDB table, but will make the
548 dump operation take considerably longer.
549 • --password[=password], -p[password]
551 The password to use when connecting to the server. If you use the
553 short option form (-p), you cannot have a space between the option
554 and the password. If you omit the password value following the
555 --password or -p option on the command line, mysqldump prompts for
556 one.
557 Specifying a password on the command line should be considered
559 insecure. You can use an option file to avoid giving the password
560 on the command line.
561 • --pipe, -W
563 On Windows, connect to the server via a named pipe. This option
565 applies only if the server supports named-pipe connections.
566 • --plugin-dir
568 Directory for client-side plugins.
570 • --port=port_num, -P port_num
572 The TCP/IP port number to use for the connection.
574 • --protocol={TCP|SOCKET|PIPE|MEMORY}
576 The connection protocol to use for connecting to the server. It is
578 useful when the other connection parameters normally would cause a
579 protocol to be used other than the one you want.
580 • --quick, -q
582 This option is useful for dumping large tables. It forces mysqldump
584 to retrieve rows for a table from the server a row at a time rather
585 than retrieving the entire row set and buffering it in memory
586 before writing it out.
587 • --print-defaults
589 Print the program argument list and exit. This must be given as the
591 first argument.
592 • --quote-names, -Q
594 Quote identifiers (such as database, table, and column names)
596 within “`” characters. If the ANSI_QUOTES SQL mode is enabled,
597 identifiers are quoted within “"” characters. This option is
598 enabled by default. It can be disabled with --skip-quote-names, but
599 this option should be given after any option such as --compatible
600 that may enable --quote-names.
601 • --replace
603 Write REPLACE statements rather than INSERT statements.
605 • --result-file=file_name, -r file_name
607 Direct output to a given file. This option should be used on
609 Windows to prevent newline “\n” characters from being converted to
610 “\r\n” carriage return/newline sequences. The result file is
611 created and its previous contents overwritten, even if an error
612 occurs while generating the dump.
613 • --routines, -R
615 Included stored routines (procedures and functions) for the dumped
617 databases in the output. Use of this option requires the SELECT
618 privilege for the mysql.proc table. The output generated by using
619 --routines contains CREATE PROCEDURE and CREATE FUNCTION statements
620 to re-create the routines. However, these statements do not include
621 attributes such as the routine creation and modification
622 timestamps. This means that when the routines are reloaded, they
623 will be created with the timestamps equal to the reload time.
624 If you require routines to be re-created with their original
626 timestamp attributes, do not use --routines. Instead, dump and
627 reload the contents of the mysql.proc table directly, using a
628 MariaDB account that has appropriate privileges for the mysql
629 database.
630 • --set-charset
633 Add SET NAMES default_character_set to the output. This option is
635 enabled by default. To suppress the SET NAMES statement, use
636 --skip-set-charset.
637 • --single-transaction
639 This option sends a START TRANSACTION SQL statement to the server
641 before dumping data. It is useful only with transactional tables
642 such as InnoDB, because then it dumps the consistent state of the
643 database at the time when BEGIN was issued without blocking any
644 applications.
645 When using this option, you should keep in mind that only InnoDB
647 tables are dumped in a consistent state. For example, any MyISAM or
648 MEMORY tables dumped while using this option may still change
649 state.
650 While a --single-transaction dump is in process, to ensure a valid
652 dump file (correct table contents and binary log coordinates), no
653 other connection should use the following statements: ALTER TABLE,
654 CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A
655 consistent read is not isolated from those statements, so use of
656 them on a table to be dumped can cause the SELECT that is performed
657 by mysqldump to retrieve the table contents to obtain incorrect
658 contents or fail.
659 The --single-transaction option and the --lock-tables option are
661 mutually exclusive because LOCK TABLES causes any pending
662 transactions to be committed implicitly.
663 To dump large tables, you should combine the --single-transaction
665 option with --quick.
666 • --skip-add-drop-table
668 Disable the --add-drop-table option.
670 • --skip-add-locks
672 Disable the --add-locks option.
674 • --skip-comments
676 Disable the --comments option.
678 • --skip-compact
680 Disable the --compact option.
682 • --skip-disable-keys
684 Disable the --disable-keys option.
686 • --skip-extended-insert
688 Disable the --extended-insert option.
690 • --skip-opt
692 Disable the --opt option.
694 • --skip-quick
696 Disable the --quick option.
698 • --skip-quote-names
700 Disable the --quote-names option.
702 • --skip-set-charset
704 Disable the --set-charset option.
706 • --skip-triggers
708 Disable the --triggers option.
710 • --skip-tz-utc
712 Disable the --tz-utc option.
714 • --socket=path, -S path
716 For connections to localhost, the Unix socket file to use, or, on
718 Windows, the name of the named pipe to use.
719 • --ssl
721 Enable SSL for connection (automatically enabled with other flags).
723 Disable with --skip-ssl.
724 • --ssl-ca=name
726 CA file in PEM format (check OpenSSL docs, implies --ssl).
728 • --ssl-capath=name
730 CA directory (check OpenSSL docs, implies --ssl).
732 • --ssl-cert=name
734 X509 cert in PEM format (check OpenSSL docs, implies --ssl).
736 • --ssl-cipher=name
738 SSL cipher to use (check OpenSSL docs, implies --ssl).
740 • --ssl-key=name
742 X509 key in PEM format (check OpenSSL docs, implies --ssl).
744 • --ssl-crl=name
746 Certificate revocation list (check OpenSSL docs, implies --ssl).
748 • --ssl-crlpath=name
750 Certificate revocation list path (check OpenSSL docs, implies
752 --ssl).
753 • --ssl-verify-server-cert
755 Verify server's "Common Name" in its cert against hostname used
757 when connecting. This option is disabled by default.
758 • --system={all, users, plugins, udfs, servers, stats, timezones}
760 Dump the system tables in the mysql database in a logical form.
762 This option is an empty set by default.
763 One or more options can be listed in comma separated list.
765 The options here are:
767 • all - an alias to enabling all of the below options.
769 • users - the users, roles and their grants outputed as CREATE
770 USER, CREATE ROLE, GRANT, and SET DEFAULT ROLE (ALTER USER for
771 MySQL-8.0+).
772 • plugins - active plugins of the server outputed as INSTALL
773 PLUGIN.
774 • udfs - user define functions outputed as CREATE FUNCTION.
775 • servers - remote (federated) servers as CREATE SERVER.
776 • stats - statistics tables, InnoDB and Engine Independent Table
777 Statistics (EITS), are dumped as REPLACE INTO (or INSERT IGNORE
778 if --insert-ignore is specified) statements without
779 (re)creating tables.
780 • timezones - timezone related system tables dumped as REPLACE
781 INTO (or INSERT IGNORE if --insert-ignore is specified)
782 statements without (re)creating tables.
783 The format of the output is affected by --replace and
785 --insert-ignore. The --replace option will output CREATE OR REPLACE
786 forms of SQL, and also DROP IF EXISTS prior to CREATE, if a CREATE
787 OR REPLACE option isn't available.
788 With --system=user (or all), and --replace, SQL is generated to
790 generate an error if attempting to import the dump with a
791 connection user that is being replaced within the dump.
792 The --insert-ignore option will cause CREATE IF NOT EXIST forms of
794 SQL to generated if available.
795 For stats, and timezones, --replace and --insert-ignore have the
797 usual effects.
798 Enabling specific options here will cause the relevant tables in
800 the mysql database to be ignored when dumping the mysql database or
801 --all-databases.
802 To help in migrating from MySQL to MariaDB, this option is designed
804 to be able to dump system information from MySQL-5.7 and 8.0
805 servers. SQL generated is also experimentally compatible with
806 MySQL-5.7/8.0. Mappings of implementation specific grants/plugins
807 isn't always one-to-one however between MariaDB and MySQL and will
808 require manual changes.
809 • --tab=path, -T path
811 Produce tab-separated text-format data files. For each dumped
813 table, mysqldump creates a tbl_name.sql file that contains the
814 CREATE TABLE statement that creates the table, and the server
815 writes a tbl_name.txt file that contains its data. The option value
816 is the directory in which to write the files.
817 Note
819 This option should be used only when mysqldump is run on the
820 same machine as the mysqld server. You must have the FILE
821 privilege, and the server must have permission to write files
822 in the directory that you specify.
823 By default, the .txt data files are formatted using tab characters
824 between column values and a newline at the end of each line. The
825 format can be specified explicitly using the --fields-xxx and
826 --lines-terminated-by options.
827 Column values are converted to the character set specified by the
829 --default-character-set option.
830 • --tables
832 Override the --databases or -B option. mysqldump regards all name
834 arguments following the option as table names.
835 • --triggers
837 Include triggers for each dumped table in the output. This option
839 is enabled by default; disable it with --skip-triggers.
840 • --tz-utc
842 This option enables TIMESTAMP columns to be dumped and reloaded
844 between servers in different time zones. mysqldump sets its
845 connection time zone to UTC and adds SET TIME_ZONE=´+00:00´ to the
846 dump file. Without this option, TIMESTAMP columns are dumped and
847 reloaded in the time zones local to the source and destination
848 servers, which can cause the values to change if the servers are in
849 different time zones. --tz-utc also protects against changes due
850 to daylight saving time. --tz-utc is enabled by default. To
851 disable it, use --skip-tz-utc.
852 • --user=user_name, -u user_name
854 The MariaDB user name to use when connecting to the server.
856 • --verbose, -v
858 Verbose mode. Print more information about what the program does.
860 • --version, -V
862 Display version information and exit.
864 • --where=´where_condition´, -w ´where_condition´
866 Dump only rows selected by the given WHERE condition. Quotes around
868 the condition are mandatory if it contains spaces or other
869 characters that are special to your command interpreter.
870 Examples:
872 --where="user=´jimf´"
874 -w"userid>1"
875 -w"userid<1"
876 • --xml, -X
878 Write dump output as well-formed XML.
880 NULL, ´NULL´, and Empty Values: For a column named column_name, the
882 NULL value, an empty string, and the string value ´NULL´ are
883 distinguished from one another in the output generated by this
884 option as follows.
885 ┌──────────────────────┬───────────────────────────────────────────┐
887 │Value: │ XML Representation: │
888 ├──────────────────────┼───────────────────────────────────────────┤
889 │NULL (unknown value) │ <field name="column_name" xsi:nil="true" │
890 │ │ /> │
891 ├──────────────────────┼───────────────────────────────────────────┤
892 │´´ (empty string) │ <field name="column_name"></field> │
893 ├──────────────────────┼───────────────────────────────────────────┤
894 │´NULL´ (string value) │ <field name="column_name">NULL</field> │
895 └──────────────────────┴───────────────────────────────────────────┘
896 The output from the mysql client when run using the --xml option
897 also follows the preceding rules. (See the section called “MYSQL
898 OPTIONS”.)
899 XML output from mysqldump includes the XML namespace, as shown
901 here:
902 shell> mysqldump --xml -u root world City
904 <?xml version="1.0"?>
905 <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
906 <database name="world">
907 <table_structure name="City">
908 <field Field="ID" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" />
909 <field Field="Name" Type="char(35)" Null="NO" Key="" Default="" Extra="" />
910 <field Field="CountryCode" Type="char(3)" Null="NO" Key="" Default="" Extra="" />
911 <field Field="District" Type="char(20)" Null="NO" Key="" Default="" Extra="" />
912 <field Field="Population" Type="int(11)" Null="NO" Key="" Default="0" Extra="" />
913 <key Table="City" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="ID"
914 Collation="A" Cardinality="4079" Null="" Index_type="BTREE" Comment="" />
915 <options Name="City" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="4079"
916 Avg_row_length="67" Data_length="273293" Max_data_length="18858823439613951"
917 Index_length="43008" Data_free="0" Auto_increment="4080"
918 Create_time="2007-03-31 01:47:01" Update_time="2007-03-31 01:47:02"
919 Collation="latin1_swedish_ci" Create_options="" Comment="" />
920 </table_structure>
921 <table_data name="City">
922 <row>
923 <field name="ID">1</field>
924 <field name="Name">Kabul</field>
925 <field name="CountryCode">AFG</field>
926 <field name="District">Kabol</field>
927 <field name="Population">1780000</field>
928 </row>
929 ...
930 <row>
931 <field name="ID">4079</field>
932 <field name="Name">Rafah</field>
933 <field name="CountryCode">PSE</field>
934 <field name="District">Rafah</field>
935 <field name="Population">92020</field>
936 </row>
937 </table_data>
938 </database>
939 </mysqldump>
940 You can also set the following variables by using --var_name=value
943 syntax:
944 • max_allowed_packet
946 The maximum size of the buffer for client/server communication. The
948 maximum is 1GB.
949 • max_statement_time
951 A query that has taken more than max_statement_time seconds will be
953 aborted and the backup will fail. The argument will be treated as a
954 decimal value with microsecond precision. A value of 0 (default)
955 means no timeout. The maximum timeout is 31536000 seconds.
956 • net_buffer_length
958 The initial size of the buffer for client/server communication.
960 When creating multiple-row INSERT statements (as with the
961 --extended-insert or --opt option), mysqldump creates rows up to
962 net_buffer_length length. If you increase this variable, you should
963 also ensure that the net_buffer_length variable in the MariaDB
964 server is at least this large.
965 A common use of mysqldump is for making a backup of an entire database:
967 shell> mysqldump db_name > backup-file.sql
969 You can load the dump file back into the server like this:
971 shell> mysql db_name < backup-file.sql
973 Or like this:
975 shell> mysql -e "source /path-to-backup/backup-file.sql" db_name
977 mysqldump is also very useful for populating databases by copying data
979 from one MariaDB server to another:
980 shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
982 It is possible to dump several databases with one command:
984 shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql
986 To dump all databases, use the --all-databases option:
988 shell> mysqldump --all-databases > all_databases.sql
990 For InnoDB tables, mysqldump provides a way of making an online backup:
992 shell> mysqldump --all-databases --single-transaction > all_databases.sql
994 This backup acquires a global read lock on all tables (using FLUSH
996 TABLES WITH READ LOCK) at the beginning of the dump. As soon as this
997 lock has been acquired, the binary log coordinates are read and the
998 lock is released. If long updating statements are running when the
999 FLUSH statement is issued, the MariaDB server may get stalled until
1000 those statements finish. After that, the dump becomes lock free and
1001 does not disturb reads and writes on the tables. If the update
1002 statements that the MariaDB server receives are short (in terms of
1003 execution time), the initial lock period should not be noticeable, even
1004 with many updates.
1005 For point-in-time recovery (also known as “roll-forward,” when you need
1007 to restore an old backup and replay the changes that happened since
1008 that backup), it is often useful to rotate the binary log or at least
1009 know the binary log coordinates to which the dump corresponds:
1010 shell> mysqldump --all-databases --master-data=2 > all_databases.sql
1012 Or:
1014 shell> mysqldump --all-databases --flush-logs --master-data=2
1016 > all_databases.sql
1017 The --master-data and --single-transaction options can be used
1019 simultaneously, which provides a convenient way to make an online
1020 backup suitable for use prior to point-in-time recovery if tables are
1021 stored using the InnoDB storage engine.
1022 If you encounter problems backing up views, please read the section
1024 that covers restrictions on views which describes a workaround for
1025 backing up views when this fails due to insufficient privileges.
1026
1028 Copyright 2007-2008 MySQL AB, 2008-2010 Sun Microsystems, Inc.,
1029 2010-2020 MariaDB Foundation
1030 This documentation is free software; you can redistribute it and/or
1032 modify it only under the terms of the GNU General Public License as
1033 published by the Free Software Foundation; version 2 of the License.
1034 This documentation is distributed in the hope that it will be useful,
1036 but WITHOUT ANY WARRANTY; without even the implied warranty of
1037 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
1038 General Public License for more details.
1039 You should have received a copy of the GNU General Public License along
1041 with the program; if not, write to the Free Software Foundation, Inc.,
1042 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA or see
1043 http://www.gnu.org/licenses/.
1044
1047 For more information, please refer to the MariaDB Knowledge Base,
1048 available online at https://mariadb.com/kb/
1049
1051 MariaDB Foundation (http://www.mariadb.org/).
1052MariaDB 10.5 24 October 2020 MARIADB-DUMP(1)