1MYSQLDUMP(1)                MariaDB Database System               MYSQLDUMP(1)
2
3
4

NAME

6       mysqldump - a database backup program
7

SYNOPSIS

9       mysqldump [options] [db_name [tbl_name ...]]
10

DESCRIPTION

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
19       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
23       There are four general ways to invoke mysqldump:
24
25           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
30       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
33       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
37       To see a list of the options your version of mysqldump supports,
38       execute mysqldump --help.
39
40       Some mysqldump options are shorthand for groups of other options:
41
42       ·   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
48       ·   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
52       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
57       ·   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
63       ·   To reverse --opt for all features except index disabling and table
64           locking, use --skip-opt --disable-keys --lock-tables.
65
66       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
71       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
78       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
82       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
86       ·   --help, -?
87
88           Display a help message and exit.
89
90       ·   --add-drop-database
91
92           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
97       ·   --add-drop-table
98
99           Add a DROP TABLE statement before each CREATE TABLE statement.
100
101       ·   --add-drop-trigger
102
103           Add a DROP TRIGGER statement before each CREATE TRIGGER statement.
104
105       ·   --add-locks
106
107           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
111       ·   --all-databases, -A
112
113           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
117       ·   --all-tablespaces, -Y
118
119           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
124
125       ·   --allow-keywords
126
127           Allow creation of column names that are keywords. This works by
128           prefixing each column name with the table name.
129
130       ·   --apply-slave-statements
131
132           Adds 'STOP SLAVE' prior to 'CHANGE MASTER' and 'START SLAVE' to
133           bottom of dump.
134
135       ·   --character-sets-dir=path
136
137           The directory where character sets are installed.
138
139       ·   --comments, -i
140
141           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
146       ·   --compact
147
148           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
152       ·   --compatible=name
153
154           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
162           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
168
169       ·   --complete-insert, -c
170
171           Use complete INSERT statements that include column names.
172
173       ·   --compress, -C
174
175           Compress all information sent between the client and the server if
176           both support compression.
177
178       ·   --create-options, -a
179
180           Include all MariaDB-specific table options in the CREATE TABLE
181           statements. Use --skip-create-options to disable.
182
183       ·   --databases, -B
184
185           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
191       ·   --debug[=debug_options], -# [debug_options]
192
193           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
197       ·   --debug-check
198
199           Print some debugging information when the program exits.
200
201       ·   --debug-info
202
203           Print debugging information and memory and CPU usage statistics
204           when the program exits.
205
206       ·   --default-auth
207
208           Default authentication client-side plugin to use.
209
210       ·   --default-character-set=charset_name
211
212           Use charset_name as the default character set. If no character set
213           is specified, mysqldump uses utf8.
214
215
216       ·   --defaults-extra-file=filename
217
218           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
222       ·   --defaults-file=filename
223
224           Set filename as the file to read default options from, override
225           global defaults files.  Must be given as first option.
226
227       ·   --defaults-group-suffix=str,
228
229           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
234       ·   --delayed-insert
235
236           Write INSERT DELAYED statements rather than INSERT statements.
237
238       ·   --delete-master-logs
239
240           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
244       ·   --disable-keys, -K
245
246           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
252       ·   --dump-date
253
254           If the --comments option is given, mysqldump produces a comment at
255           the end of the dump of the following form:
256
257               -- Dump completed on DATE
258
259           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
265       ·   --dump-slave[=value]
266
267           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
282       ·   --events, -E
283
284           Include Event Scheduler events for the dumped databases in the
285           output.
286
287       ·   --extended-insert, -e
288
289           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
293       ·   --fields-terminated-by=..., --fields-enclosed-by=...,
294           --fields-optionally-enclosed-by=..., --fields-escaped-by=...
295
296           These options are used with the --tab option and have the same
297           meaning as the corresponding FIELDS clauses for LOAD DATA INFILE.
298
299       ·   --first-slave
300
301           Removed in MariaDB 5.5. Use --lock-all-tables instead.
302
303       ·   --flashback, -B
304
305           Support flashback mode.
306
307       ·   --flush-logs, -F
308
309           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
319       ·   --flush-privileges
320
321           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
326       ·   --force, -f
327
328           Continue even if an SQL error occurs during a table dump.
329
330           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
338       ·   --gtid
339
340           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
349       ·   --hex-blob
350
351           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
355       ·   --host=host_name, -h host_name
356
357           Dump data from the MariaDB server on the given host. The default
358           host is localhost.
359
360       ·   --ignore-table=db_name.tbl_name
361
362           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
367       ·   --include-master-host-port
368
369           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
372       ·   --insert-ignore
373
374           Write INSERT IGNORE statements rather than INSERT statements.
375
376       ·   --lines-terminated-by=...
377
378           This option is used with the --tab option and has the same meaning
379           as the corresponding LINES clause for LOAD DATA INFILE.
380
381       ·   --lock-all-tables, -x
382
383           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
387       ·   --lock-tables, -l
388
389           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
396           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
401           Use --skip-lock-tables to disable.
402
403       ·   --log-error=file_name
404
405           Log warnings and errors by appending them to the named file. The
406           default is to do no logging.
407
408       ·   --log-queries
409
410           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
414       ·   --master-data[=value]
415
416           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
424           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
431           This option requires the RELOAD privilege and the binary log must
432           be enabled.
433
434           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
439           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
443            1. Stop the slave´s SQL thread and get its current status:
444
445                   mysql> STOP SLAVE SQL_THREAD;
446                   mysql> SHOW SLAVE STATUS;
447
448            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
454            3. Dump the slave server:
455
456                   shell> mysqldump --master-data=2 --all-databases > dumpfile
457
458            4. Restart the slave:
459
460                   mysql> START SLAVE;
461
462            5. On the new slave, load the dump file:
463
464                   shell> mysql < dumpfile
465
466            6. On the new slave, set the replication coordinates to those of
467               the master server obtained earlier:
468
469                   mysql> CHANGE MASTER TO
470                       -> MASTER_LOG_FILE = ´file_name´, MASTER_LOG_POS = file_pos;
471
472               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
477       ·   --max-allowed-packet=length
478
479           Sets the maximum packet length to send to or receive from server.
480
481       ·   --net-buffer-length=length
482
483           Sets the buffer size for TCP/IP and socket communication.
484
485       ·   --no-autocommit
486
487           Enclose the INSERT statements for each dumped table within SET
488           autocommit = 0 and COMMIT statements.
489
490       ·   --no-create-db, -n
491
492           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
496       ·   --no-create-info, -t
497
498           Do not write CREATE TABLE statements that re-create each dumped
499           table.
500
501       ·   --no-data, -d
502
503           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
508       ·   --no-defaults
509
510           Do not read default options from any option file. This must be
511           given as the first argument.
512
513       ·   --no-set-names, -N
514
515           This has the same effect as --skip-set-charset.
516
517       ·   --opt
518
519           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
525           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
530       ·   --order-by-primary
531
532           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
537       ·   --password[=password], -p[password]
538
539           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
545           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
549       ·   --pipe, -W
550
551           On Windows, connect to the server via a named pipe. This option
552           applies only if the server supports named-pipe connections.
553
554       ·   --plugin-dir
555
556           Directory for client-side plugins.
557
558       ·   --port=port_num, -P port_num
559
560           The TCP/IP port number to use for the connection.
561
562       ·   --protocol={TCP|SOCKET|PIPE|MEMORY}
563
564           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
568       ·   --quick, -q
569
570           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
575       ·   --print-defaults
576
577           Print the program argument list and exit. This must be given as the
578           first argument.
579
580       ·   --quote-names, -Q
581
582           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
589       ·   --replace
590
591           Write REPLACE statements rather than INSERT statements.
592
593       ·   --result-file=file_name, -r file_name
594
595           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
601       ·   --routines, -R
602
603           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
612           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
618
619       ·   --set-charset
620
621           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
625       ·   --single-transaction
626
627           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
633           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
638           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
647           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
651           To dump large tables, you should combine the --single-transaction
652           option with --quick.
653
654       ·   --skip-add-drop-table
655
656           Disable the --add-drop-table option.
657
658       ·   --skip-add-locks
659
660           Disable the --add-locks option.
661
662       ·   --skip-comments
663
664           Disable the --comments option.
665
666       ·   --skip-compact
667
668           Disable the --compact option.
669
670       ·   --skip-disable-keys
671
672           Disable the --disable-keys option.
673
674       ·   --skip-extended-insert
675
676           Disable the --extended-insert option.
677
678       ·   --skip-opt
679
680           Disable the --opt option.
681
682       ·   --skip-quick
683
684           Disable the --quick option.
685
686       ·   --skip-quote-names
687
688           Disable the --quote-names option.
689
690       ·   --skip-set-charset
691
692           Disable the --set-charset option.
693
694       ·   --skip-triggers
695
696           Disable the --triggers option.
697
698       ·   --skip-tz-utc
699
700           Disable the --tz-utc option.
701
702       ·   --socket=path, -S path
703
704           For connections to localhost, the Unix socket file to use, or, on
705           Windows, the name of the named pipe to use.
706
707       ·   --ssl
708
709           Enable SSL for connection (automatically enabled with other flags).
710           Disable with --skip-ssl.
711
712       ·   --ssl-ca=name
713
714           CA file in PEM format (check OpenSSL docs, implies --ssl).
715
716       ·   --ssl-capath=name
717
718           CA directory (check OpenSSL docs, implies --ssl).
719
720       ·   --ssl-cert=name
721
722           X509 cert in PEM format (check OpenSSL docs, implies --ssl).
723
724       ·   --ssl-cipher=name
725
726           SSL cipher to use (check OpenSSL docs, implies --ssl).
727
728       ·   --ssl-key=name
729
730           X509 key in PEM format (check OpenSSL docs, implies --ssl).
731
732       ·   --ssl-crl=name
733
734           Certificate revocation list (check OpenSSL docs, implies --ssl).
735
736       ·   --ssl-crlpath=name
737
738           Certificate revocation list path (check OpenSSL docs, implies
739           --ssl).
740
741       ·   --ssl-verify-server-cert
742
743           Verify server's "Common Name" in its cert against hostname used
744           when connecting. This option is disabled by default.
745
746       ·   --system={all, users, plugins, udfs, servers, stats, timezones}
747
748           Dump the system tables in the mysql database in a logical form.
749           This option is an empty set by default.
750
751           One or more options can be listed in comma separated list.
752
753           The options here are:
754
755           ·   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
771           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
776           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
780           The --insert-into option will cause CREATE IF NOT EXIST forms of
781           SQL to generated if available.
782
783           For stats, and timezones, --replace and --insert-into have the
784           usual effects.
785
786           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
790           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
797       ·   --tab=path, -T path
798
799           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
805               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
815           Column values are converted to the character set specified by the
816           --default-character-set option.
817
818       ·   --tables
819
820           Override the --databases or -B option.  mysqldump regards all name
821           arguments following the option as table names.
822
823       ·   --triggers
824
825           Include triggers for each dumped table in the output. This option
826           is enabled by default; disable it with --skip-triggers.
827
828       ·   --tz-utc
829
830           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
840       ·   --user=user_name, -u user_name
841
842           The MariaDB user name to use when connecting to the server.
843
844       ·   --verbose, -v
845
846           Verbose mode. Print more information about what the program does.
847
848       ·   --version, -V
849
850           Display version information and exit.
851
852       ·   --where=´where_condition´, -w ´where_condition´
853
854           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
858           Examples:
859
860               --where="user=´jimf´"
861               -w"userid>1"
862               -w"userid<1"
863
864       ·   --xml, -X
865
866           Write dump output as well-formed XML.
867
868           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
873           ┌──────────────────────┬───────────────────────────────────────────┐
874Value:                │ 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
887           XML output from mysqldump includes the XML namespace, as shown
888           here:
889
890               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
928
929       You can also set the following variables by using --var_name=value
930       syntax:
931
932       ·   max_allowed_packet
933
934           The maximum size of the buffer for client/server communication. The
935           maximum is 1GB.
936
937       ·   net_buffer_length
938
939           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
946       A common use of mysqldump is for making a backup of an entire database:
947
948           shell> mysqldump db_name > backup-file.sql
949
950       You can load the dump file back into the server like this:
951
952           shell> mysql db_name < backup-file.sql
953
954       Or like this:
955
956           shell> mysql -e "source /path-to-backup/backup-file.sql" db_name
957
958       mysqldump is also very useful for populating databases by copying data
959       from one MariaDB server to another:
960
961           shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
962
963       It is possible to dump several databases with one command:
964
965           shell> mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql
966
967       To dump all databases, use the --all-databases option:
968
969           shell> mysqldump --all-databases > all_databases.sql
970
971       For InnoDB tables, mysqldump provides a way of making an online backup:
972
973           shell> mysqldump --all-databases --single-transaction > all_databases.sql
974
975       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
986       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
991           shell> mysqldump --all-databases --master-data=2 > all_databases.sql
992
993       Or:
994
995           shell> mysqldump --all-databases --flush-logs --master-data=2
996                         > all_databases.sql
997
998       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
1003       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
1011       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
1015       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
1020       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
1025

SEE ALSO

1027       For more information, please refer to the MariaDB Knowledge Base,
1028       available online at https://mariadb.com/kb/
1029

AUTHOR

1031       MariaDB Foundation (http://www.mariadb.org/).
1032
1033
1034
1035MariaDB 10.4                    24 October 2020                   MYSQLDUMP(1)
Impressum