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 · --create-options, -a
179 Include all MariaDB-specific table options in the CREATE TABLE
181 statements. Use --skip-create-options to disable.
182 · --databases, -B
184 Dump several databases. Normally, mysqldump treats the first name
186 argument on the command line as a database name and following names
187 as table names. With this option, it treats all name arguments as
188 database names. CREATE DATABASE and USE statements are included in
189 the output before each new database.
190 · --debug[=debug_options], -# [debug_options]
192 Write a debugging log. A typical debug_options string is
194 ´d:t:o,file_name´. The default value is
195 ´d:t:o,/tmp/mysqldump.trace´.
196 · --debug-check
198 Print some debugging information when the program exits.
200 · --debug-info
202 Print debugging information and memory and CPU usage statistics
204 when the program exits.
205 · --default-auth
207 Default authentication client-side plugin to use.
209 · --default-character-set=charset_name
211 Use charset_name as the default character set. If no character set
213 is specified, mysqldump uses utf8.
214 · --defaults-extra-file=filename
217 Set filename as the file to read default options from after the
219 global defaults files has been read. Must be given as first
220 option.
221 · --defaults-file=filename
223 Set filename as the file to read default options from, override
225 global defaults files. Must be given as first option.
226 · --defaults-group-suffix=str,
228 Also read groups with a suffix of str. For example, since mysqldump
230 normally reads the [client] and [mysqldump] groups,
231 --defaults-group-suffix=x would cause it to also read the groups
232 [mysqldump_x] and [client_x].
233 · --delayed-insert
235 Write INSERT DELAYED statements rather than INSERT statements.
237 · --delete-master-logs
239 On a master replication server, delete the binary logs by sending a
241 PURGE BINARY LOGS statement to the server after performing the dump
242 operation. This option automatically enables --master-data.
243 · --disable-keys, -K
245 For each table, surround the INSERT statements with /*!40000 ALTER
247 TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE tbl_name
248 ENABLE KEYS */; statements. This makes loading the dump file faster
249 because the indexes are created after all rows are inserted. This
250 option is effective only for nonunique indexes of MyISAM tables.
251 · --dump-date
253 If the --comments option is given, mysqldump produces a comment at
255 the end of the dump of the following form:
256 -- Dump completed on DATE
258 However, the date causes dump files taken at different times to
260 appear to be different, even if the data are otherwise identical.
261 --dump-date and --skip-dump-date control whether the date is added
262 to the comment. The default is --dump-date (include the date in the
263 comment). --skip-dump-date suppresses date printing
264 · --dump-slave[=value]
266 Used for producing a dump file from a replication slave server that
268 can be used to set up another slave server with the same master.
269 Causes the binary log position and filename of the master to be
270 appended to the dumped data output. Setting the value to 1 (the
271 default) will print it as a CHANGE MASTER command in the dumped
272 data output; if set to 2, that command will be prefixed with a
273 comment symbol. This option will turn --lock-all-tables on, unless
274 --single-transaction is specified too (in which case a global read
275 lock is only taken a short time at the beginning of the dump -
276 don't forget to read about --single-transaction below). In all
277 cases any action on logs will happen at the exact moment of the
278 dump. Option automatically turns --lock-tables off. Using this
279 option causes mysqldump to stop the slave SQL thread before
280 beginning the dump, and restart it again after completion.
281 · --events, -E
283 Include Event Scheduler events for the dumped databases in the
285 output.
286 · --extended-insert, -e
288 Use multiple-row INSERT syntax that include several VALUES lists.
290 This results in a smaller dump file and speeds up inserts when the
291 file is reloaded.
292 · --fields-terminated-by=..., --fields-enclosed-by=...,
294 --fields-optionally-enclosed-by=..., --fields-escaped-by=...
295 These options are used with the --tab option and have the same
297 meaning as the corresponding FIELDS clauses for LOAD DATA INFILE.
298 · --first-slave
300 Removed in MariaDB 5.5. Use --lock-all-tables instead.
302 · --flashback, -B
304 Support flashback mode.
306 · --flush-logs, -F
308 Flush the MariaDB server log files before starting the dump. This
310 option requires the RELOAD privilege. If you use this option in
311 combination with the --all-databases option, the logs are flushed
312 for each database dumped. The exception is when using
313 --lock-all-tables or --master-data: In this case, the logs are
314 flushed only once, corresponding to the moment that all tables are
315 locked. If you want your dump and the log flush to happen at
316 exactly the same moment, you should use --flush-logs together with
317 either --lock-all-tables or --master-data.
318 · --flush-privileges
320 Send a FLUSH PRIVILEGES statement to the server after dumping the
322 mysql database. This option should be used any time the dump
323 contains the mysql database and any other database that depends on
324 the data in the mysql database for proper restoration.
325 · --force, -f
327 Continue even if an SQL error occurs during a table dump.
329 One use for this option is to cause mysqldump to continue executing
331 even when it encounters a view that has become invalid because the
332 definition refers to a table that has been dropped. Without
333 --force, mysqldump exits with an error message. With --force,
334 mysqldump prints the error message, but it also writes an SQL
335 comment containing the view definition to the dump output and
336 continues executing.
337 · --gtid
339 Available from MariaDB 10.0.13, and is used together with
341 --master-data and --dump-slave to more conveniently set up a new
342 GTID slave. It causes those options to output SQL statements that
343 configure the slave to use the global transaction ID to connect to
344 the master instead of old-style filename/offset positions. The old-
345 style positions are still included in comments when --gtid is used;
346 likewise the GTID position is included in comments even if --gtid
347 is not used.
348 · --hex-blob
350 Dump binary columns using hexadecimal notation (for example, ´abc´
352 becomes 0x616263). The affected data types are BINARY, VARBINARY,
353 the BLOB types, and BIT.
354 · --host=host_name, -h host_name
356 Dump data from the MariaDB server on the given host. The default
358 host is localhost.
359 · --ignore-table=db_name.tbl_name
361 Do not dump the given table, which must be specified using both the
363 database and table names. To ignore multiple tables, use this
364 option multiple times. This option also can be used to ignore
365 views.
366 · --include-master-host-port
368 Add the MASTER_HOST and MASTER_PORT options for the CHANGE MASTER
370 TO statement when using the --dump-slave option for a slave dump.
371 · --insert-ignore
373 Write INSERT IGNORE statements rather than INSERT statements.
375 · --lines-terminated-by=...
377 This option is used with the --tab option and has the same meaning
379 as the corresponding LINES clause for LOAD DATA INFILE.
380 · --lock-all-tables, -x
382 Lock all tables across all databases. This is achieved by acquiring
384 a global read lock for the duration of the whole dump. This option
385 automatically turns off --single-transaction and --lock-tables.
386 · --lock-tables, -l
388 For each dumped database, lock all tables to be dumped before
390 dumping them. The tables are locked with READ LOCAL to allow
391 concurrent inserts in the case of MyISAM tables. For transactional
392 tables such as InnoDB, --single-transaction is a much better option
393 than --lock-tables because it does not need to lock the tables at
394 all.
395 Because --lock-tables locks tables for each database separately,
397 this option does not guarantee that the tables in the dump file are
398 logically consistent between databases. Tables in different
399 databases may be dumped in completely different states.
400 Use --skip-lock-tables to disable.
402 · --log-error=file_name
404 Log warnings and errors by appending them to the named file. The
406 default is to do no logging.
407 · --log-queries
409 When restoring the dump, the server will, if logging is turned on,
411 log the queries to the general and slow query log. Defaults to on;
412 use --skip-log-queries to disable.
413 · --master-data[=value]
415 Use this option to dump a master replication server to produce a
417 dump file that can be used to set up another server as a slave of
418 the master. It causes the dump output to include a CHANGE MASTER TO
419 statement that indicates the binary log coordinates (file name and
420 position) of the dumped server. These are the master server
421 coordinates from which the slave should start replicating after you
422 load the dump file into the slave.
423 If the option value is 2, the CHANGE MASTER TO statement is written
425 as an SQL comment, and thus is informative only; it has no effect
426 when the dump file is reloaded. If the option value is 1, the
427 statement is not written as a comment and takes effect when the
428 dump file is reloaded. If no option value is specified, the default
429 value is 1.
430 This option requires the RELOAD privilege and the binary log must
432 be enabled.
433 The --master-data option automatically turns off --lock-tables. It
435 also turns on --lock-all-tables, unless --single-transaction also
436 is specified. In all cases, any action on logs happens at the exact
437 moment of the dump.
438 It is also possible to set up a slave by dumping an existing slave
440 of the master. To do this, use the following procedure on the
441 existing slave:
442 1. Stop the slave´s SQL thread and get its current status:
444 mysql> STOP SLAVE SQL_THREAD;
446 mysql> SHOW SLAVE STATUS;
447 2. From the output of the SHOW SLAVE STATUS statement, the binary
449 log coordinates of the master server from which the new slave
450 should start replicating are the values of the
451 Relay_Master_Log_File and Exec_Master_Log_Pos fields. Denote
452 those values as file_name and file_pos.
453 3. Dump the slave server:
455 shell> mysqldump --master-data=2 --all-databases > dumpfile
457 4. Restart the slave:
459 mysql> START SLAVE;
461 5. On the new slave, load the dump file:
463 shell> mysql < dumpfile
465 6. On the new slave, set the replication coordinates to those of
467 the master server obtained earlier:
468 mysql> CHANGE MASTER TO
470 -> MASTER_LOG_FILE = ´file_name´, MASTER_LOG_POS = file_pos;
471 The CHANGE MASTER TO statement might also need other
473 parameters, such as MASTER_HOST to point the slave to the
474 correct master server host. Add any such parameters as
475 necessary.
476 · --max-allowed-packet=length
478 Sets the maximum packet length to send to or receive from server.
480 · --net-buffer-length=length
482 Sets the buffer size for TCP/IP and socket communication.
484 · --no-autocommit
486 Enclose the INSERT statements for each dumped table within SET
488 autocommit = 0 and COMMIT statements.
489 · --no-create-db, -n
491 This option suppresses the CREATE DATABASE statements that are
493 otherwise included in the output if the --databases or
494 --all-databases option is given.
495 · --no-create-info, -t
497 Do not write CREATE TABLE statements that re-create each dumped
499 table.
500 · --no-data, -d
502 Do not write any table row information (that is, do not dump table
504 contents). This is useful if you want to dump only the CREATE TABLE
505 statement for the table (for example, to create an empty copy of
506 the table by loading the dump file).
507 · --no-defaults
509 Do not read default options from any option file. This must be
511 given as the first argument.
512 · --no-set-names, -N
514 This has the same effect as --skip-set-charset.
516 · --opt
518 This option is shorthand. It is the same as specifying
520 --add-drop-table --add-locks --create-options --disable-keys
521 --extended-insert --lock-tables --quick --set-charset. It should
522 give you a fast dump operation and produce a dump file that can be
523 reloaded into a MariaDB server quickly.
524 The --opt option is enabled by default. Use --skip-opt to disable
526 it. See the discussion at the beginning of this section for
527 information about selectively enabling or disabling a subset of the
528 options affected by --opt.
529 · --order-by-primary
531 Dump each table´s rows sorted by its primary key, or by its first
533 unique index, if such an index exists. This is useful when dumping
534 a MyISAM table to be loaded into an InnoDB table, but will make the
535 dump operation take considerably longer.
536 · --password[=password], -p[password]
538 The password to use when connecting to the server. If you use the
540 short option form (-p), you cannot have a space between the option
541 and the password. If you omit the password value following the
542 --password or -p option on the command line, mysqldump prompts for
543 one.
544 Specifying a password on the command line should be considered
546 insecure. You can use an option file to avoid giving the password
547 on the command line.
548 · --pipe, -W
550 On Windows, connect to the server via a named pipe. This option
552 applies only if the server supports named-pipe connections.
553 · --plugin-dir
555 Directory for client-side plugins.
557 · --port=port_num, -P port_num
559 The TCP/IP port number to use for the connection.
561 · --protocol={TCP|SOCKET|PIPE|MEMORY}
563 The connection protocol to use for connecting to the server. It is
565 useful when the other connection parameters normally would cause a
566 protocol to be used other than the one you want.
567 · --quick, -q
569 This option is useful for dumping large tables. It forces mysqldump
571 to retrieve rows for a table from the server a row at a time rather
572 than retrieving the entire row set and buffering it in memory
573 before writing it out.
574 · --print-defaults
576 Print the program argument list and exit. This must be given as the
578 first argument.
579 · --quote-names, -Q
581 Quote identifiers (such as database, table, and column names)
583 within “`” characters. If the ANSI_QUOTES SQL mode is enabled,
584 identifiers are quoted within “"” characters. This option is
585 enabled by default. It can be disabled with --skip-quote-names, but
586 this option should be given after any option such as --compatible
587 that may enable --quote-names.
588 · --replace
590 Write REPLACE statements rather than INSERT statements.
592 · --result-file=file_name, -r file_name
594 Direct output to a given file. This option should be used on
596 Windows to prevent newline “\n” characters from being converted to
597 “\r\n” carriage return/newline sequences. The result file is
598 created and its previous contents overwritten, even if an error
599 occurs while generating the dump.
600 · --routines, -R
602 Included stored routines (procedures and functions) for the dumped
604 databases in the output. Use of this option requires the SELECT
605 privilege for the mysql.proc table. The output generated by using
606 --routines contains CREATE PROCEDURE and CREATE FUNCTION statements
607 to re-create the routines. However, these statements do not include
608 attributes such as the routine creation and modification
609 timestamps. This means that when the routines are reloaded, they
610 will be created with the timestamps equal to the reload time.
611 If you require routines to be re-created with their original
613 timestamp attributes, do not use --routines. Instead, dump and
614 reload the contents of the mysql.proc table directly, using a
615 MariaDB account that has appropriate privileges for the mysql
616 database.
617 · --set-charset
620 Add SET NAMES default_character_set to the output. This option is
622 enabled by default. To suppress the SET NAMES statement, use
623 --skip-set-charset.
624 · --single-transaction
626 This option sends a START TRANSACTION SQL statement to the server
628 before dumping data. It is useful only with transactional tables
629 such as InnoDB, because then it dumps the consistent state of the
630 database at the time when BEGIN was issued without blocking any
631 applications.
632 When using this option, you should keep in mind that only InnoDB
634 tables are dumped in a consistent state. For example, any MyISAM or
635 MEMORY tables dumped while using this option may still change
636 state.
637 While a --single-transaction dump is in process, to ensure a valid
639 dump file (correct table contents and binary log coordinates), no
640 other connection should use the following statements: ALTER TABLE,
641 CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A
642 consistent read is not isolated from those statements, so use of
643 them on a table to be dumped can cause the SELECT that is performed
644 by mysqldump to retrieve the table contents to obtain incorrect
645 contents or fail.
646 The --single-transaction option and the --lock-tables option are
648 mutually exclusive because LOCK TABLES causes any pending
649 transactions to be committed implicitly.
650 To dump large tables, you should combine the --single-transaction
652 option with --quick.
653 · --skip-add-drop-table
655 Disable the --add-drop-table option.
657 · --skip-add-locks
659 Disable the --add-locks option.
661 · --skip-comments
663 Disable the --comments option.
665 · --skip-compact
667 Disable the --compact option.
669 · --skip-disable-keys
671 Disable the --disable-keys option.
673 · --skip-extended-insert
675 Disable the --extended-insert option.
677 · --skip-opt
679 Disable the --opt option.
681 · --skip-quick
683 Disable the --quick option.
685 · --skip-quote-names
687 Disable the --quote-names option.
689 · --skip-set-charset
691 Disable the --set-charset option.
693 · --skip-triggers
695 Disable the --triggers option.
697 · --skip-tz-utc
699 Disable the --tz-utc option.
701 · --socket=path, -S path
703 For connections to localhost, the Unix socket file to use, or, on
705 Windows, the name of the named pipe to use.
706 · --ssl
708 Enable SSL for connection (automatically enabled with other flags).
710 Disable with --skip-ssl.
711 · --ssl-ca=name
713 CA file in PEM format (check OpenSSL docs, implies --ssl).
715 · --ssl-capath=name
717 CA directory (check OpenSSL docs, implies --ssl).
719 · --ssl-cert=name
721 X509 cert in PEM format (check OpenSSL docs, implies --ssl).
723 · --ssl-cipher=name
725 SSL cipher to use (check OpenSSL docs, implies --ssl).
727 · --ssl-key=name
729 X509 key in PEM format (check OpenSSL docs, implies --ssl).
731 · --ssl-crl=name
733 Certificate revocation list (check OpenSSL docs, implies --ssl).
735 · --ssl-crlpath=name
737 Certificate revocation list path (check OpenSSL docs, implies
739 --ssl).
740 · --ssl-verify-server-cert
742 Verify server's "Common Name" in its cert against hostname used
744 when connecting. This option is disabled by default.
745 · --system={all, users, plugins, udfs, servers, stats, timezones}
747 Dump the system tables in the mysql database in a logical form.
749 This option is an empty set by default.
750 One or more options can be listed in comma separated list.
752 The options here are:
754 · all - an alias to enabling all of the below options.
756 · users - the users, roles and their grants outputed as CREATE
757 USER, CREATE ROLE, GRANT, and SET DEFAULT ROLE (ALTER USER for
758 MySQL-8.0+).
759 · plugins - active plugins of the server outputed as INSTALL
760 PLUGIN.
761 · udfs - user define functions outputed as CREATE FUNCTION.
762 · servers - remote (federated) servers as CREATE SERVER.
763 · stats - statistics tables, InnoDB and Engine Independent Table
764 Statistics (EITS), are dumped as REPLACE INTO (or INSERT IGNORE
765 if --insert-into is specified) statements without (re)creating
766 tables.
767 · timezones - timezone related system tables dumped as REPLACE
768 INTO (or INSERT IGNORE if --insert-into is specified)
769 statements without (re)creating tables.
770 The format of the output is affected by --replace and
772 --insert-into. The --replace option will output CREATE OR REPLACE
773 forms of SQL, and also DROP IF EXISTS prior to CREATE, if a CREATE
774 OR REPLACE option isn't available.
775 With --system=user (or all), and --replace, SQL is generated to
777 generate an error if attempting to import the dump with a
778 connection user that is being replaced within the dump.
779 The --insert-into option will cause CREATE IF NOT EXIST forms of
781 SQL to generated if available.
782 For stats, and timezones, --replace and --insert-into have the
784 usual effects.
785 Enabling specific options here will cause the relevant tables in
787 the mysql database to be ignored when dumping the mysql database or
788 --all-databases.
789 To help in migrating from MySQL to MariaDB, this option is designed
791 to be able to dump system information from MySQL-5.7 and 8.0
792 servers. SQL generated is also experimentally compatible with
793 MySQL-5.7/8.0. Mappings of implementation specific grants/plugins
794 isn't always one-to-one however between MariaDB and MySQL and will
795 require manual changes.
796 · --tab=path, -T path
798 Produce tab-separated text-format data files. For each dumped
800 table, mysqldump creates a tbl_name.sql file that contains the
801 CREATE TABLE statement that creates the table, and the server
802 writes a tbl_name.txt file that contains its data. The option value
803 is the directory in which to write the files.
804 Note
806 This option should be used only when mysqldump is run on the
807 same machine as the mysqld server. You must have the FILE
808 privilege, and the server must have permission to write files
809 in the directory that you specify.
810 By default, the .txt data files are formatted using tab characters
811 between column values and a newline at the end of each line. The
812 format can be specified explicitly using the --fields-xxx and
813 --lines-terminated-by options.
814 Column values are converted to the character set specified by the
816 --default-character-set option.
817 · --tables
819 Override the --databases or -B option. mysqldump regards all name
821 arguments following the option as table names.
822 · --triggers
824 Include triggers for each dumped table in the output. This option
826 is enabled by default; disable it with --skip-triggers.
827 · --tz-utc
829 This option enables TIMESTAMP columns to be dumped and reloaded
831 between servers in different time zones. mysqldump sets its
832 connection time zone to UTC and adds SET TIME_ZONE=´+00:00´ to the
833 dump file. Without this option, TIMESTAMP columns are dumped and
834 reloaded in the time zones local to the source and destination
835 servers, which can cause the values to change if the servers are in
836 different time zones. --tz-utc also protects against changes due
837 to daylight saving time. --tz-utc is enabled by default. To
838 disable it, use --skip-tz-utc.
839 · --user=user_name, -u user_name
841 The MariaDB user name to use when connecting to the server.
843 · --verbose, -v
845 Verbose mode. Print more information about what the program does.
847 · --version, -V
849 Display version information and exit.
851 · --where=´where_condition´, -w ´where_condition´
853 Dump only rows selected by the given WHERE condition. Quotes around
855 the condition are mandatory if it contains spaces or other
856 characters that are special to your command interpreter.
857 Examples:
859 --where="user=´jimf´"
861 -w"userid>1"
862 -w"userid<1"
863 · --xml, -X
865 Write dump output as well-formed XML.
867 NULL, ´NULL´, and Empty Values: For a column named column_name, the
869 NULL value, an empty string, and the string value ´NULL´ are
870 distinguished from one another in the output generated by this
871 option as follows.
872 ┌──────────────────────┬───────────────────────────────────────────┐
874 │Value: │ XML Representation: │
875 ├──────────────────────┼───────────────────────────────────────────┤
876 │NULL (unknown value) │ <field name="column_name" xsi:nil="true" │
877 │ │ /> │
878 ├──────────────────────┼───────────────────────────────────────────┤
879 │´´ (empty string) │ <field name="column_name"></field> │
880 ├──────────────────────┼───────────────────────────────────────────┤
881 │´NULL´ (string value) │ <field name="column_name">NULL</field> │
882 └──────────────────────┴───────────────────────────────────────────┘
883 The output from the mysql client when run using the --xml option
884 also follows the preceding rules. (See the section called “MYSQL
885 OPTIONS”.)
886 XML output from mysqldump includes the XML namespace, as shown
888 here:
889 shell> mysqldump --xml -u root world City
891 <?xml version="1.0"?>
892 <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
893 <database name="world">
894 <table_structure name="City">
895 <field Field="ID" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" />
896 <field Field="Name" Type="char(35)" Null="NO" Key="" Default="" Extra="" />
897 <field Field="CountryCode" Type="char(3)" Null="NO" Key="" Default="" Extra="" />
898 <field Field="District" Type="char(20)" Null="NO" Key="" Default="" Extra="" />
899 <field Field="Population" Type="int(11)" Null="NO" Key="" Default="0" Extra="" />
900 <key Table="City" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="ID"
901 Collation="A" Cardinality="4079" Null="" Index_type="BTREE" Comment="" />
902 <options Name="City" Engine="MyISAM" Version="10" Row_format="Fixed" Rows="4079"
903 Avg_row_length="67" Data_length="273293" Max_data_length="18858823439613951"
904 Index_length="43008" Data_free="0" Auto_increment="4080"
905 Create_time="2007-03-31 01:47:01" Update_time="2007-03-31 01:47:02"
906 Collation="latin1_swedish_ci" Create_options="" Comment="" />
907 </table_structure>
908 <table_data name="City">
909 <row>
910 <field name="ID">1</field>
911 <field name="Name">Kabul</field>
912 <field name="CountryCode">AFG</field>
913 <field name="District">Kabol</field>
914 <field name="Population">1780000</field>
915 </row>
916 ...
917 <row>
918 <field name="ID">4079</field>
919 <field name="Name">Rafah</field>
920 <field name="CountryCode">PSE</field>
921 <field name="District">Rafah</field>
922 <field name="Population">92020</field>
923 </row>
924 </table_data>
925 </database>
926 </mysqldump>
927 You can also set the following variables by using --var_name=value
930 syntax:
931 · max_allowed_packet
933 The maximum size of the buffer for client/server communication. The
935 maximum is 1GB.
936 · net_buffer_length
938 The initial size of the buffer for client/server communication.
940 When creating multiple-row INSERT statements (as with the
941 --extended-insert or --opt option), mysqldump creates rows up to
942 net_buffer_length length. If you increase this variable, you should
943 also ensure that the net_buffer_length variable in the MariaDB
944 server is at least this large.
945 A common use of mysqldump is for making a backup of an entire database:
947 shell> mysqldump db_name > backup-file.sql
949 You can load the dump file back into the server like this:
951 shell> mysql db_name < backup-file.sql
953 Or like this:
955 shell> mysql -e "source /path-to-backup/backup-file.sql" db_name
957 mysqldump is also very useful for populating databases by copying data
959 from one MariaDB server to another:
960 shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
962 It is possible to dump several databases with one command:
964 shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql
966 To dump all databases, use the --all-databases option:
968 shell> mysqldump --all-databases > all_databases.sql
970 For InnoDB tables, mysqldump provides a way of making an online backup:
972 shell> mysqldump --all-databases --single-transaction > all_databases.sql
974 This backup acquires a global read lock on all tables (using FLUSH
976 TABLES WITH READ LOCK) at the beginning of the dump. As soon as this
977 lock has been acquired, the binary log coordinates are read and the
978 lock is released. If long updating statements are running when the
979 FLUSH statement is issued, the MariaDB server may get stalled until
980 those statements finish. After that, the dump becomes lock free and
981 does not disturb reads and writes on the tables. If the update
982 statements that the MariaDB server receives are short (in terms of
983 execution time), the initial lock period should not be noticeable, even
984 with many updates.
985 For point-in-time recovery (also known as “roll-forward,” when you need
987 to restore an old backup and replay the changes that happened since
988 that backup), it is often useful to rotate the binary log or at least
989 know the binary log coordinates to which the dump corresponds:
990 shell> mysqldump --all-databases --master-data=2 > all_databases.sql
992 Or:
994 shell> mysqldump --all-databases --flush-logs --master-data=2
996 > all_databases.sql
997 The --master-data and --single-transaction options can be used
999 simultaneously, which provides a convenient way to make an online
1000 backup suitable for use prior to point-in-time recovery if tables are
1001 stored using the InnoDB storage engine.
1002 If you encounter problems backing up views, please read the section
1004 that covers restrictions on views which describes a workaround for
1005 backing up views when this fails due to insufficient privileges.
1006
1008 Copyright 2007-2008 MySQL AB, 2008-2010 Sun Microsystems, Inc.,
1009 2010-2020 MariaDB Foundation
1010 This documentation is free software; you can redistribute it and/or
1012 modify it only under the terms of the GNU General Public License as
1013 published by the Free Software Foundation; version 2 of the License.
1014 This documentation is distributed in the hope that it will be useful,
1016 but WITHOUT ANY WARRANTY; without even the implied warranty of
1017 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
1018 General Public License for more details.
1019 You should have received a copy of the GNU General Public License along
1021 with the program; if not, write to the Free Software Foundation, Inc.,
1022 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA or see
1023 http://www.gnu.org/licenses/.
1024
1027 For more information, please refer to the MariaDB Knowledge Base,
1028 available online at https://mariadb.com/kb/
1029
1031 MariaDB Foundation (http://www.mariadb.org/).
1032MariaDB 10.4 24 October 2020 MYSQLDUMP(1)