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 four 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 shell> mysqldump [options] --system={options}
29 If you do not name any tables following db_name or if you use the
31 --databases or --all-databases option, entire databases are dumped.
32 mysqldump does not dump the INFORMATION_SCHEMA or performance_schema
34 databases by default. To dump these, name them explicitly on the
35 command line, although you must also use the --skip-lock-tables option.
36 To see a list of the options your version of mysqldump supports,
38 execute mysqldump --help.
39 Some mysqldump options are shorthand for groups of other options:
41 • Use of --opt is the same as specifying --add-drop-table,
43 --add-locks, --create-options, --disable-keys, --extended-insert,
44 --lock-tables, --quick, and --set-charset. All of the options that
45 --opt stands for also are on by default because --opt is on by
46 default.
47 • Use of --compact is the same as specifying --skip-add-drop-table,
49 --skip-add-locks, --skip-comments, --skip-disable-keys, and
50 --skip-set-charset options.
51 To reverse the effect of a group option, uses its --skip-xxx form
53 (--skip-opt or --skip-compact). It is also possible to select only part
54 of the effect of a group option by following it with options that
55 enable or disable specific features. Here are some examples:
56 • To select the effect of --opt except for some features, use the
58 --skip option for each feature. To disable extended inserts and
59 memory buffering, use --opt --skip-extended-insert --skip-quick.
60 (Actually, --skip-extended-insert --skip-quick is sufficient
61 because --opt is on by default.)
62 • To reverse --opt for all features except index disabling and table
64 locking, use --skip-opt --disable-keys --lock-tables.
65 When you selectively enable or disable the effect of a group option,
67 order is important because options are processed first to last. For
68 example, --disable-keys --lock-tables --skip-opt would not have the
69 intended effect; it is the same as --skip-opt by itself.
70 mysqldump can retrieve and dump table contents row by row, or it can
72 retrieve the entire content from a table and buffer it in memory before
73 dumping it. Buffering in memory can be a problem if you are dumping
74 large tables. To dump tables row by row, use the --quick option (or
75 --opt, which enables --quick). The --opt option (and hence --quick) is
76 enabled by default, so to enable memory buffering, use --skip-quick.
77 If you are using a recent version of mysqldump to generate a dump to be
79 reloaded into a very old MySQL server, you should not use the --opt or
80 --extended-insert option. Use --skip-opt instead.
81 mysqldump supports the following options, which can be specified on the
83 command line or in the [mysqldump] and [client] option file groups.
84 mysqldump also supports the options for processing option file.
85 • --help, -?
87 Display a help message and exit.
89 • --add-drop-database
91 Add a DROP DATABASE statement before each CREATE DATABASE
93 statement. This option is typically used in conjunction with the
94 --all-databases or --databases option because no CREATE DATABASE
95 statements are written unless one of those options is specified.
96 • --add-drop-table
98 Add a DROP TABLE statement before each CREATE TABLE statement.
100 • --add-drop-trigger
102 Add a DROP TRIGGER statement before each CREATE TRIGGER statement.
104 • --add-locks
106 Surround each table dump with LOCK TABLES and UNLOCK TABLES
108 statements. This results in faster inserts when the dump file is
109 reloaded.
110 • --all-databases, -A
112 Dump all tables in all databases. This is the same as using the
114 --databases option and naming all the databases on the command
115 line.
116 • --all-tablespaces, -Y
118 Adds to a table dump all SQL statements needed to create any
120 tablespaces used by an NDBCLUSTER table. This information is not
121 otherwise included in the output from mysqldump. This option is
122 currently relevant only to MySQL Cluster tables.
123 • --allow-keywords
126 Allow creation of column names that are keywords. This works by
128 prefixing each column name with the table name.
129 • --apply-slave-statements
131 Adds 'STOP SLAVE' prior to 'CHANGE MASTER' and 'START SLAVE' to
133 bottom of dump.
134 • --character-sets-dir=path
136 The directory where character sets are installed.
138 • --comments, -i
140 Write additional information in the dump file such as program
142 version, server version, and host. This option is enabled by
143 default. To suppress this additional information, use
144 --skip-comments.
145 • --compact
147 Produce more compact output. This option enables the
149 --skip-add-drop-table, --skip-add-locks, --skip-comments,
150 --skip-disable-keys, and --skip-set-charset options.
151 • --compatible=name
153 Produce output that is more compatible with other database systems
155 or with older MySQL servers. The value of name can be ansi,
156 mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb,
157 no_key_options, no_table_options, or no_field_options. To use
158 several values, separate them by commas. These values have the same
159 meaning as the corresponding options for setting the server SQL
160 mode.
161 This option does not guarantee compatibility with other servers. It
163 only enables those SQL mode values that are currently available for
164 making dump output more compatible. For example,
165 --compatible=oracle does not map data types to Oracle types or use
166 Oracle comment syntax.
167 • --complete-insert, -c
170 Use complete INSERT statements that include column names.
172 • --compress, -C
174 Compress all information sent between the client and the server if
176 both support compression.
177 • --copy-s3-tables
179 By default S3 tables are ignored. With this option set, the result
181 file will contain a CREATE statement for a similar Aria table,
182 followed by the table data and ending with an ALTER TABLE xxx
183 ENGINE=S3.
184 • --create-options, -a
186 Include all MariaDB-specific table options in the CREATE TABLE
188 statements. Use --skip-create-options to disable.
189 • --databases, -B
191 Dump several databases. Normally, mysqldump treats the first name
193 argument on the command line as a database name and following names
194 as table names. With this option, it treats all name arguments as
195 database names. CREATE DATABASE and USE statements are included in
196 the output before each new database.
197 • --debug[=debug_options], -# [debug_options]
199 Write a debugging log. A typical debug_options string is
201 ´d:t:o,file_name´. The default value is
202 ´d:t:o,/tmp/mysqldump.trace´.
203 • --debug-check
205 Print some debugging information when the program exits.
207 • --debug-info
209 Print debugging information and memory and CPU usage statistics
211 when the program exits.
212 • --default-auth
214 Default authentication client-side plugin to use.
216 • --default-character-set=charset_name
218 Use charset_name as the default character set. If no character set
220 is specified, mysqldump uses utf8.
221 • --defaults-extra-file=filename
224 Set filename as the file to read default options from after the
226 global defaults files has been read. Must be given as first
227 option.
228 • --defaults-file=filename
230 Set filename as the file to read default options from, override
232 global defaults files. Must be given as first option.
233 • --defaults-group-suffix=str,
235 Also read groups with a suffix of str. For example, since mysqldump
237 normally reads the [client] and [mysqldump] groups,
238 --defaults-group-suffix=x would cause it to also read the groups
239 [mysqldump_x] and [client_x].
240 • --delayed-insert
242 Write INSERT DELAYED statements rather than INSERT statements.
244 • --delete-master-logs
246 On a master replication server, delete the binary logs by sending a
248 PURGE BINARY LOGS statement to the server after performing the dump
249 operation. This option automatically enables --master-data.
250 • --disable-keys, -K
252 For each table, surround the INSERT statements with /*!40000 ALTER
254 TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name
255 ENABLE KEYS */; statements. This makes loading the dump file faster
256 because the indexes are created after all rows are inserted. This
257 option is effective only for nonunique indexes of MyISAM tables.
258 • --dump-date
260 If the --comments option is given, mysqldump produces a comment at
262 the end of the dump of the following form:
263 -- Dump completed on DATE
265 However, the date causes dump files taken at different times to
267 appear to be different, even if the data are otherwise identical.
268 --dump-date and --skip-dump-date control whether the date is added
269 to the comment. The default is --dump-date (include the date in the
270 comment). --skip-dump-date suppresses date printing
271 • --dump-slave[=value]
273 Used for producing a dump file from a replication slave server that
275 can be used to set up another slave server with the same master.
276 Causes the binary log position and filename of the master to be
277 appended to the dumped data output. Setting the value to 1 (the
278 default) will print it as a CHANGE MASTER command in the dumped
279 data output; if set to 2, that command will be prefixed with a
280 comment symbol. This option will turn --lock-all-tables on, unless
281 --single-transaction is specified too (in which case a global read
282 lock is only taken a short time at the beginning of the dump -
283 don't forget to read about --single-transaction below). In all
284 cases any action on logs will happen at the exact moment of the
285 dump. Option automatically turns --lock-tables off. Using this
286 option causes mysqldump to stop the slave SQL thread before
287 beginning the dump, and restart it again after completion.
288 • --events, -E
290 Include Event Scheduler events for the dumped databases in the
292 output.
293 • --extended-insert, -e
295 Use multiple-row INSERT syntax that include several VALUES lists.
297 This results in a smaller dump file and speeds up inserts when the
298 file is reloaded.
299 • --fields-terminated-by=..., --fields-enclosed-by=...,
301 --fields-optionally-enclosed-by=..., --fields-escaped-by=...
302 These options are used with the --tab option and have the same
304 meaning as the corresponding FIELDS clauses for LOAD DATA INFILE.
305 • --first-slave
307 Removed in MariaDB 5.5. Use --lock-all-tables instead.
309 • --flashback, -B
311 Support flashback mode.
313 • --flush-logs, -F
315 Flush the MariaDB server log files before starting the dump. This
317 option requires the RELOAD privilege. If you use this option in
318 combination with the --all-databases option, the logs are flushed
319 for each database dumped. The exception is when using
320 --lock-all-tables or --master-data: In this case, the logs are
321 flushed only once, corresponding to the moment that all tables are
322 locked. If you want your dump and the log flush to happen at
323 exactly the same moment, you should use --flush-logs together with
324 either --lock-all-tables or --master-data.
325 • --flush-privileges
327 Send a FLUSH PRIVILEGES statement to the server after dumping the
329 mysql database. This option should be used any time the dump
330 contains the mysql database and any other database that depends on
331 the data in the mysql database for proper restoration.
332 • --force, -f
334 Continue even if an SQL error occurs during a table dump.
336 One use for this option is to cause mysqldump to continue executing
338 even when it encounters a view that has become invalid because the
339 definition refers to a table that has been dropped. Without
340 --force, mysqldump exits with an error message. With --force,
341 mysqldump prints the error message, but it also writes an SQL
342 comment containing the view definition to the dump output and
343 continues executing.
344 • --gtid
346 Available from MariaDB 10.0.13, and is used together with
348 --master-data and --dump-slave to more conveniently set up a new
349 GTID slave. It causes those options to output SQL statements that
350 configure the slave to use the global transaction ID to connect to
351 the master instead of old-style filename/offset positions. The old-
352 style positions are still included in comments when --gtid is used;
353 likewise the GTID position is included in comments even if --gtid
354 is not used.
355 • --hex-blob
357 Dump binary columns using hexadecimal notation (for example, ´abc´
359 becomes 0x616263). The affected data types are BINARY, VARBINARY,
360 the BLOB types, and BIT.
361 • --host=host_name, -h host_name
363 Dump data from the MariaDB server on the given host. The default
365 host is localhost.
366 • --ignore-table=db_name.tbl_name
368 Do not dump the given table, which must be specified using both the
370 database and table names. To ignore multiple tables, use this
371 option multiple times. This option also can be used to ignore
372 views.
373 • --include-master-host-port
375 Add the MASTER_HOST and MASTER_PORT options for the CHANGE MASTER
377 TO statement when using the --dump-slave option for a slave dump.
378 • --insert-ignore
380 Write INSERT IGNORE statements rather than INSERT statements.
382 • --lines-terminated-by=...
384 This option is used with the --tab option and has the same meaning
386 as the corresponding LINES clause for LOAD DATA INFILE.
387 • --lock-all-tables, -x
389 Lock all tables across all databases. This is achieved by acquiring
391 a global read lock for the duration of the whole dump. This option
392 automatically turns off --single-transaction and --lock-tables.
393 • --lock-tables, -l
395 For each dumped database, lock all tables to be dumped before
397 dumping them. The tables are locked with READ LOCAL to allow
398 concurrent inserts in the case of MyISAM tables. For transactional
399 tables such as InnoDB, --single-transaction is a much better option
400 than --lock-tables because it does not need to lock the tables at
401 all.
402 Because --lock-tables locks tables for each database separately,
404 this option does not guarantee that the tables in the dump file are
405 logically consistent between databases. Tables in different
406 databases may be dumped in completely different states.
407 Use --skip-lock-tables to disable.
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.
414 • --log-queries
416 When restoring the dump, the server will, if logging is turned on,
418 log the queries to the general and slow query log. Defaults to on;
419 use --skip-log-queries to disable.
420 • --master-data[=value]
422 Use this option to dump a master replication server to produce a
424 dump file that can be used to set up another server as a slave of
425 the master. It causes the dump output to include a CHANGE MASTER TO
426 statement that indicates the binary log coordinates (file name and
427 position) of the dumped server. These are the master server
428 coordinates from which the slave should start replicating after you
429 load the dump file into the slave.
430 If the option value is 2, the CHANGE MASTER TO statement is written
432 as an SQL comment, and thus is informative only; it has no effect
433 when the dump file is reloaded. If the option value is 1, the
434 statement is not written as a comment and takes effect when the
435 dump file is reloaded. If no option value is specified, the default
436 value is 1.
437 This option requires the RELOAD privilege and the binary log must
439 be enabled.
440 The --master-data option automatically turns off --lock-tables. It
442 also turns on --lock-all-tables, unless --single-transaction also
443 is specified. In all cases, any action on logs happens at the exact
444 moment of the dump.
445 It is also possible to set up a slave by dumping an existing slave
447 of the master. To do this, use the following procedure on the
448 existing slave:
449 1. Stop the slave´s SQL thread and get its current status:
451 mysql> STOP SLAVE SQL_THREAD;
453 mysql> SHOW SLAVE STATUS;
454 2. From the output of the SHOW SLAVE STATUS statement, the binary
456 log coordinates of the master server from which the new slave
457 should start replicating are the values of the
458 Relay_Master_Log_File and Exec_Master_Log_Pos fields. Denote
459 those values as file_name and file_pos.
460 3. Dump the slave server:
462 shell> mysqldump --master-data=2 --all-databases > dumpfile
464 4. Restart the slave:
466 mysql> START SLAVE;
468 5. On the new slave, load the dump file:
470 shell> mysql < dumpfile
472 6. On the new slave, set the replication coordinates to those of
474 the master server obtained earlier:
475 mysql> CHANGE MASTER TO
477 -> MASTER_LOG_FILE = ´file_name´, MASTER_LOG_POS = file_pos;
478 The CHANGE MASTER TO statement might also need other
480 parameters, such as MASTER_HOST to point the slave to the
481 correct master server host. Add any such parameters as
482 necessary.
483 • --max-allowed-packet=length
485 Sets the maximum packet length to send to or receive from server.
487 • --net-buffer-length=length
489 Sets the buffer size for TCP/IP and socket communication.
491 • --no-autocommit
493 Enclose the INSERT statements for each dumped table within SET
495 autocommit = 0 and COMMIT statements.
496 • --no-create-db, -n
498 This option suppresses the CREATE DATABASE statements that are
500 otherwise included in the output if the --databases or
501 --all-databases option is given.
502 • --no-create-info, -t
504 Do not write CREATE TABLE statements that re-create each dumped
506 table.
507 • --no-data, -d
509 Do not write any table row information (that is, do not dump table
511 contents). This is useful if you want to dump only the CREATE TABLE
512 statement for the table (for example, to create an empty copy of
513 the table by loading the dump file).
514 • --no-defaults
516 Do not read default options from any option file. This must be
518 given as the first argument.
519 • --no-set-names, -N
521 This has the same effect as --skip-set-charset.
523 • --opt
525 This option is shorthand. It is the same as specifying
527 --add-drop-table --add-locks --create-options --disable-keys
528 --extended-insert --lock-tables --quick --set-charset. It should
529 give you a fast dump operation and produce a dump file that can be
530 reloaded into a MariaDB server quickly.
531 The --opt option is enabled by default. Use --skip-opt to disable
533 it. See the discussion at the beginning of this section for
534 information about selectively enabling or disabling a subset of the
535 options affected by --opt.
536 • --order-by-primary
538 Dump each table´s rows sorted by its primary key, or by its first
540 unique index, if such an index exists. This is useful when dumping
541 a MyISAM table to be loaded into an InnoDB table, but will make the
542 dump operation take considerably longer.
543 • --password[=password], -p[password]
545 The password to use when connecting to the server. If you use the
547 short option form (-p), you cannot have a space between the option
548 and the password. If you omit the password value following the
549 --password or -p option on the command line, mysqldump prompts for
550 one.
551 Specifying a password on the command line should be considered
553 insecure. You can use an option file to avoid giving the password
554 on the command line.
555 • --pipe, -W
557 On Windows, connect to the server via a named pipe. This option
559 applies only if the server supports named-pipe connections.
560 • --plugin-dir
562 Directory for client-side plugins.
564 • --port=port_num, -P port_num
566 The TCP/IP port number to use for the connection.
568 • --protocol={TCP|SOCKET|PIPE|MEMORY}
570 The connection protocol to use for connecting to the server. It is
572 useful when the other connection parameters normally would cause a
573 protocol to be used other than the one you want.
574 • --quick, -q
576 This option is useful for dumping large tables. It forces mysqldump
578 to retrieve rows for a table from the server a row at a time rather
579 than retrieving the entire row set and buffering it in memory
580 before writing it out.
581 • --print-defaults
583 Print the program argument list and exit. This must be given as the
585 first argument.
586 • --quote-names, -Q
588 Quote identifiers (such as database, table, and column names)
590 within “`” characters. If the ANSI_QUOTES SQL mode is enabled,
591 identifiers are quoted within “"” characters. This option is
592 enabled by default. It can be disabled with --skip-quote-names, but
593 this option should be given after any option such as --compatible
594 that may enable --quote-names.
595 • --replace
597 Write REPLACE statements rather than INSERT statements.
599 • --result-file=file_name, -r file_name
601 Direct output to a given file. This option should be used on
603 Windows to prevent newline “\n” characters from being converted to
604 “\r\n” carriage return/newline sequences. The result file is
605 created and its previous contents overwritten, even if an error
606 occurs while generating the dump.
607 • --routines, -R
609 Included stored routines (procedures and functions) for the dumped
611 databases in the output. Use of this option requires the SELECT
612 privilege for the mysql.proc table. The output generated by using
613 --routines contains CREATE PROCEDURE and CREATE FUNCTION statements
614 to re-create the routines. However, these statements do not include
615 attributes such as the routine creation and modification
616 timestamps. This means that when the routines are reloaded, they
617 will be created with the timestamps equal to the reload time.
618 If you require routines to be re-created with their original
620 timestamp attributes, do not use --routines. Instead, dump and
621 reload the contents of the mysql.proc table directly, using a
622 MariaDB account that has appropriate privileges for the mysql
623 database.
624 • --set-charset
627 Add SET NAMES default_character_set to the output. This option is
629 enabled by default. To suppress the SET NAMES statement, use
630 --skip-set-charset.
631 • --single-transaction
633 This option sends a START TRANSACTION SQL statement to the server
635 before dumping data. It is useful only with transactional tables
636 such as InnoDB, because then it dumps the consistent state of the
637 database at the time when BEGIN was issued without blocking any
638 applications.
639 When using this option, you should keep in mind that only InnoDB
641 tables are dumped in a consistent state. For example, any MyISAM or
642 MEMORY tables dumped while using this option may still change
643 state.
644 While a --single-transaction dump is in process, to ensure a valid
646 dump file (correct table contents and binary log coordinates), no
647 other connection should use the following statements: ALTER TABLE,
648 CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A
649 consistent read is not isolated from those statements, so use of
650 them on a table to be dumped can cause the SELECT that is performed
651 by mysqldump to retrieve the table contents to obtain incorrect
652 contents or fail.
653 The --single-transaction option and the --lock-tables option are
655 mutually exclusive because LOCK TABLES causes any pending
656 transactions to be committed implicitly.
657 To dump large tables, you should combine the --single-transaction
659 option with --quick.
660 • --skip-add-drop-table
662 Disable the --add-drop-table option.
664 • --skip-add-locks
666 Disable the --add-locks option.
668 • --skip-comments
670 Disable the --comments option.
672 • --skip-compact
674 Disable the --compact option.
676 • --skip-disable-keys
678 Disable the --disable-keys option.
680 • --skip-extended-insert
682 Disable the --extended-insert option.
684 • --skip-opt
686 Disable the --opt option.
688 • --skip-quick
690 Disable the --quick option.
692 • --skip-quote-names
694 Disable the --quote-names option.
696 • --skip-set-charset
698 Disable the --set-charset option.
700 • --skip-triggers
702 Disable the --triggers option.
704 • --skip-tz-utc
706 Disable the --tz-utc option.
708 • --socket=path, -S path
710 For connections to localhost, the Unix socket file to use, or, on
712 Windows, the name of the named pipe to use.
713 • --ssl
715 Enable SSL for connection (automatically enabled with other flags).
717 Disable with --skip-ssl.
718 • --ssl-ca=name
720 CA file in PEM format (check OpenSSL docs, implies --ssl).
722 • --ssl-capath=name
724 CA directory (check OpenSSL docs, implies --ssl).
726 • --ssl-cert=name
728 X509 cert in PEM format (check OpenSSL docs, implies --ssl).
730 • --ssl-cipher=name
732 SSL cipher to use (check OpenSSL docs, implies --ssl).
734 • --ssl-key=name
736 X509 key in PEM format (check OpenSSL docs, implies --ssl).
738 • --ssl-crl=name
740 Certificate revocation list (check OpenSSL docs, implies --ssl).
742 • --ssl-crlpath=name
744 Certificate revocation list path (check OpenSSL docs, implies
746 --ssl).
747 • --ssl-verify-server-cert
749 Verify server's "Common Name" in its cert against hostname used
751 when connecting. This option is disabled by default.
752 • --system={all, users, plugins, udfs, servers, stats, timezones}
754 Dump the system tables in the mysql database in a logical form.
756 This option is an empty set by default.
757 One or more options can be listed in comma separated list.
759 The options here are:
761 • all - an alias to enabling all of the below options.
763 • users - the users, roles and their grants outputed as CREATE
764 USER, CREATE ROLE, GRANT, and SET DEFAULT ROLE (ALTER USER for
765 MySQL-8.0+).
766 • plugins - active plugins of the server outputed as INSTALL
767 PLUGIN.
768 • udfs - user define functions outputed as CREATE FUNCTION.
769 • servers - remote (federated) servers as CREATE SERVER.
770 • stats - statistics tables, InnoDB and Engine Independent Table
771 Statistics (EITS), are dumped as REPLACE INTO (or INSERT IGNORE
772 if --insert-ignore is specified) statements without
773 (re)creating tables.
774 • timezones - timezone related system tables dumped as REPLACE
775 INTO (or INSERT IGNORE if --insert-ignore is specified)
776 statements without (re)creating tables.
777 The format of the output is affected by --replace and
779 --insert-ignore. The --replace option will output CREATE OR REPLACE
780 forms of SQL, and also DROP IF EXISTS prior to CREATE, if a CREATE
781 OR REPLACE option isn't available.
782 With --system=user (or all), and --replace, SQL is generated to
784 generate an error if attempting to import the dump with a
785 connection user that is being replaced within the dump.
786 The --insert-ignore option will cause CREATE IF NOT EXIST forms of
788 SQL to generated if available.
789 For stats, and timezones, --replace and --insert-ignore have the
791 usual effects.
792 Enabling specific options here will cause the relevant tables in
794 the mysql database to be ignored when dumping the mysql database or
795 --all-databases.
796 To help in migrating from MySQL to MariaDB, this option is designed
798 to be able to dump system information from MySQL-5.7 and 8.0
799 servers. SQL generated is also experimentally compatible with
800 MySQL-5.7/8.0. Mappings of implementation specific grants/plugins
801 isn't always one-to-one however between MariaDB and MySQL and will
802 require manual changes.
803 • --tab=path, -T path
805 Produce tab-separated text-format data files. For each dumped
807 table, mysqldump creates a tbl_name.sql file that contains the
808 CREATE TABLE statement that creates the table, and the server
809 writes a tbl_name.txt file that contains its data. The option value
810 is the directory in which to write the files.
811 Note
813 This option should be used only when mysqldump is run on the
814 same machine as the mysqld server. You must have the FILE
815 privilege, and the server must have permission to write files
816 in the directory that you specify.
817 By default, the .txt data files are formatted using tab characters
818 between column values and a newline at the end of each line. The
819 format can be specified explicitly using the --fields-xxx and
820 --lines-terminated-by options.
821 Column values are converted to the character set specified by the
823 --default-character-set option.
824 • --tables
826 Override the --databases or -B option. mysqldump regards all name
828 arguments following the option as table names.
829 • --triggers
831 Include triggers for each dumped table in the output. This option
833 is enabled by default; disable it with --skip-triggers.
834 • --tz-utc
836 This option enables TIMESTAMP columns to be dumped and reloaded
838 between servers in different time zones. mysqldump sets its
839 connection time zone to UTC and adds SET TIME_ZONE=´+00:00´ to the
840 dump file. Without this option, TIMESTAMP columns are dumped and
841 reloaded in the time zones local to the source and destination
842 servers, which can cause the values to change if the servers are in
843 different time zones. --tz-utc also protects against changes due
844 to daylight saving time. --tz-utc is enabled by default. To
845 disable it, use --skip-tz-utc.
846 • --user=user_name, -u user_name
848 The MariaDB user name to use when connecting to the server.
850 • --verbose, -v
852 Verbose mode. Print more information about what the program does.
854 • --version, -V
856 Display version information and exit.
858 • --where=´where_condition´, -w ´where_condition´
860 Dump only rows selected by the given WHERE condition. Quotes around
862 the condition are mandatory if it contains spaces or other
863 characters that are special to your command interpreter.
864 Examples:
866 --where="user=´jimf´"
868 -w"userid>1"
869 -w"userid<1"
870 • --xml, -X
872 Write dump output as well-formed XML.
874 NULL, ´NULL´, and Empty Values: For a column named column_name, the
876 NULL value, an empty string, and the string value ´NULL´ are
877 distinguished from one another in the output generated by this
878 option as follows.
879 ┌──────────────────────┬───────────────────────────────────────────┐
881 │Value: │ XML Representation: │
882 ├──────────────────────┼───────────────────────────────────────────┤
883 │NULL (unknown value) │ <field name="column_name" xsi:nil="true" │
884 │ │ /> │
885 ├──────────────────────┼───────────────────────────────────────────┤
886 │´´ (empty string) │ <field name="column_name"></field> │
887 ├──────────────────────┼───────────────────────────────────────────┤
888 │´NULL´ (string value) │ <field name="column_name">NULL</field> │
889 └──────────────────────┴───────────────────────────────────────────┘
890 The output from the mysql client when run using the --xml option
891 also follows the preceding rules. (See the section called “MYSQL
892 OPTIONS”.)
893 XML output from mysqldump includes the XML namespace, as shown
895 here:
896 shell> mysqldump --xml -u root world City
898 <?xml version="1.0"?>
899 <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
900 <database name="world">
901 <table_structure name="City">
902 <field Field="ID" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" />
903 <field Field="Name" Type="char(35)" Null="NO" Key="" Default="" Extra="" />
904 <field Field="CountryCode" Type="char(3)" Null="NO" Key="" Default="" Extra="" />
905 <field Field="District" Type="char(20)" Null="NO" Key="" Default="" Extra="" />
906 <field Field="Population" Type="int(11)" Null="NO" Key="" Default="0" Extra="" />
907 <key Table="City" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="ID"
908 Collation="A" Cardinality="4079" Null="" Index_type="BTREE" Comment="" />
909 <options Name="City" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="4079"
910 Avg_row_length="67" Data_length="273293" Max_data_length="18858823439613951"
911 Index_length="43008" Data_free="0" Auto_increment="4080"
912 Create_time="2007-03-31 01:47:01" Update_time="2007-03-31 01:47:02"
913 Collation="latin1_swedish_ci" Create_options="" Comment="" />
914 </table_structure>
915 <table_data name="City">
916 <row>
917 <field name="ID">1</field>
918 <field name="Name">Kabul</field>
919 <field name="CountryCode">AFG</field>
920 <field name="District">Kabol</field>
921 <field name="Population">1780000</field>
922 </row>
923 ...
924 <row>
925 <field name="ID">4079</field>
926 <field name="Name">Rafah</field>
927 <field name="CountryCode">PSE</field>
928 <field name="District">Rafah</field>
929 <field name="Population">92020</field>
930 </row>
931 </table_data>
932 </database>
933 </mysqldump>
934 You can also set the following variables by using --var_name=value
937 syntax:
938 • max_allowed_packet
940 The maximum size of the buffer for client/server communication. The
942 maximum is 1GB.
943 • net_buffer_length
945 The initial size of the buffer for client/server communication.
947 When creating multiple-row INSERT statements (as with the
948 --extended-insert or --opt option), mysqldump creates rows up to
949 net_buffer_length length. If you increase this variable, you should
950 also ensure that the net_buffer_length variable in the MariaDB
951 server is at least this large.
952 A common use of mysqldump is for making a backup of an entire database:
954 shell> mysqldump db_name > backup-file.sql
956 You can load the dump file back into the server like this:
958 shell> mysql db_name < backup-file.sql
960 Or like this:
962 shell> mysql -e "source /path-to-backup/backup-file.sql" db_name
964 mysqldump is also very useful for populating databases by copying data
966 from one MariaDB server to another:
967 shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
969 It is possible to dump several databases with one command:
971 shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql
973 To dump all databases, use the --all-databases option:
975 shell> mysqldump --all-databases > all_databases.sql
977 For InnoDB tables, mysqldump provides a way of making an online backup:
979 shell> mysqldump --all-databases --single-transaction > all_databases.sql
981 This backup acquires a global read lock on all tables (using FLUSH
983 TABLES WITH READ LOCK) at the beginning of the dump. As soon as this
984 lock has been acquired, the binary log coordinates are read and the
985 lock is released. If long updating statements are running when the
986 FLUSH statement is issued, the MariaDB server may get stalled until
987 those statements finish. After that, the dump becomes lock free and
988 does not disturb reads and writes on the tables. If the update
989 statements that the MariaDB server receives are short (in terms of
990 execution time), the initial lock period should not be noticeable, even
991 with many updates.
992 For point-in-time recovery (also known as “roll-forward,” when you need
994 to restore an old backup and replay the changes that happened since
995 that backup), it is often useful to rotate the binary log or at least
996 know the binary log coordinates to which the dump corresponds:
997 shell> mysqldump --all-databases --master-data=2 > all_databases.sql
999 Or:
1001 shell> mysqldump --all-databases --flush-logs --master-data=2
1003 > all_databases.sql
1004 The --master-data and --single-transaction options can be used
1006 simultaneously, which provides a convenient way to make an online
1007 backup suitable for use prior to point-in-time recovery if tables are
1008 stored using the InnoDB storage engine.
1009 If you encounter problems backing up views, please read the section
1011 that covers restrictions on views which describes a workaround for
1012 backing up views when this fails due to insufficient privileges.
1013
1015 Copyright 2007-2008 MySQL AB, 2008-2010 Sun Microsystems, Inc.,
1016 2010-2020 MariaDB Foundation
1017 This documentation is free software; you can redistribute it and/or
1019 modify it only under the terms of the GNU General Public License as
1020 published by the Free Software Foundation; version 2 of the License.
1021 This documentation is distributed in the hope that it will be useful,
1023 but WITHOUT ANY WARRANTY; without even the implied warranty of
1024 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
1025 General Public License for more details.
1026 You should have received a copy of the GNU General Public License along
1028 with the program; if not, write to the Free Software Foundation, Inc.,
1029 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA or see
1030 http://www.gnu.org/licenses/.
1031
1034 For more information, please refer to the MariaDB Knowledge Base,
1035 available online at https://mariadb.com/kb/
1036
1038 MariaDB Foundation (http://www.mariadb.org/).
1039MariaDB 10.5 24 October 2020 MYSQLDUMP(1)