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

NOTES

975        1. Bug#30123
976           http://bugs.mysql.com/bug.php?id=30123
977

SEE ALSO

979       For more information, please refer to the MariaDB Knowledge Base,
980       available online at https://mariadb.com/kb/
981

AUTHOR

983       MariaDB Foundation (http://www.mariadb.org/).
984
985
986
987MariaDB 10.3                      9 May 2017                      MYSQLDUMP(1)
Impressum