1mysqldump Ver 10.16 Distrib 10.2.12-MariaDB, for Win64 (AMD64)
2Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
3
4Dumping structure and contents of MySQL databases and tables.
5Usage: mysqldump [OPTIONS] database [tables]
6OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
7OR mysqldump [OPTIONS] --all-databases [OPTIONS]
8
9Default options are read from the following files in the given order:
10C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf C:\Program Files\MariaDB 10.2\my.ini C:\Program Files\MariaDB 10.2\my.cnf C:\Program Files\MariaDB 10.2\data\my.ini C:\Program Files\MariaDB 10.2\data\my.cnf
11The following groups are read: mysqldump client client-server client-mariadb
12The following options may be given as the first argument:
13--print-defaults Print the program argument list and exit.
14--no-defaults Don't read default options from any option file.
15--defaults-file=# Only read default options from the given file #.
16--defaults-extra-file=# Read this file after the global files are read.
17
18 -A, --all-databases Dump all the databases. This will be same as --databases
19 with all databases selected.
20 -Y, --all-tablespaces
21 Dump all the tablespaces.
22 -y, --no-tablespaces
23 Do not dump any tablespace information.
24 --add-drop-database Add a DROP DATABASE before each create.
25 --add-drop-table Add a DROP TABLE before each create.
26 (Defaults to on; use --skip-add-drop-table to disable.)
27 --add-drop-trigger Add a DROP TRIGGER before each create.
28 --add-locks Add locks around INSERT statements.
29 (Defaults to on; use --skip-add-locks to disable.)
30 --allow-keywords Allow creation of column names that are keywords.
31 --apply-slave-statements
32 Adds 'STOP SLAVE' prior to 'CHANGE MASTER' and 'START
33 SLAVE' to bottom of dump.
34 --character-sets-dir=name
35 Directory for character set files.
36 -i, --comments Write additional information.
37 (Defaults to on; use --skip-comments to disable.)
38 --compatible=name Change the dump to be compatible with a given mode. By
39 default tables are dumped in a format optimized for
40 MySQL. Legal modes are: ansi, mysql323, mysql40,
41 postgresql, oracle, mssql, db2, maxdb, no_key_options,
42 no_table_options, no_field_options. One can use several
43 modes separated by commas. Note: Requires MySQL server
44 version 4.1.0 or higher. This option is ignored with
45 earlier server versions.
46 --compact Give less verbose output (useful for debugging). Disables
47 structure comments and header/footer constructs. Enables
48 options --skip-add-drop-table --skip-add-locks
49 --skip-comments --skip-disable-keys --skip-set-charset.
50 -c, --complete-insert
51 Use complete insert statements.
52 -C, --compress Use compression in server/client protocol.
53 -a, --create-options
54 Include all MySQL specific create options.
55 (Defaults to on; use --skip-create-options to disable.)
56 -B, --databases Dump several databases. Note the difference in usage; in
57 this case no tables are given. All name arguments are
58 regarded as database names. 'USE db_name;' will be
59 included in the output.
60 -#, --debug[=#] This is a non-debug version. Catch this and exit.
61 --debug-check Check memory and open file usage at exit.
62 --debug-info Print some debug info at exit.
63 --default-character-set=name
64 Set the default character set.
65 --delayed-insert Insert rows with INSERT DELAYED.
66 --delete-master-logs
67 Delete logs on master after backup. This automatically
68 enables --master-data.
69 -K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
70 '/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
71 in the output.
72 (Defaults to on; use --skip-disable-keys to disable.)
73 --dump-slave[=#] This causes the binary log position and filename of the
74 master to be appended to the dumped data output. Setting
75 the value to 1, will printit as a CHANGE MASTER command
76 in the dumped data output; if equal to 2, that command
77 will be prefixed with a comment symbol. This option will
78 turn --lock-all-tables on, unless --single-transaction is
79 specified too (in which case a global read lock is only
80 taken a short time at the beginning of the dump - don't
81 forget to read about --single-transaction below). In all
82 cases any action on logs will happen at the exact moment
83 of the dump.Option automatically turns --lock-tables off.
84 -E, --events Dump events.
85 -e, --extended-insert
86 Use multiple-row INSERT syntax that include several
87 VALUES lists.
88 (Defaults to on; use --skip-extended-insert to disable.)
89 --fields-terminated-by=name
90 Fields in the output file are terminated by the given
91 string.
92 --fields-enclosed-by=name
93 Fields in the output file are enclosed by the given
94 character.
95 --fields-optionally-enclosed-by=name
96 Fields in the output file are optionally enclosed by the
97 given character.
98 --fields-escaped-by=name
99 Fields in the output file are escaped by the given
100 character.
101 -F, --flush-logs Flush logs file in server before starting dump. Note that
102 if you dump many databases at once (using the option
103 --databases= or --all-databases), the logs will be
104 flushed for each database dumped. The exception is when
105 using --lock-all-tables or --master-data: in this case
106 the logs will be flushed only once, corresponding to the
107 moment all tables are locked. So if you want your dump
108 and the log flush to happen at the same exact moment you
109 should use --lock-all-tables or --master-data with
110 --flush-logs.
111 --flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
112 database. This option should be used any time the dump
113 contains the mysql database and any other database that
114 depends on the data in the mysql database for proper
115 restore.
116 -f, --force Continue even if we get an SQL error.
117 --gtid Used together with --master-data=1 or --dump-slave=1.When
118 enabled, the output from those options will set the GTID
119 position instead of the binlog file and offset; the
120 file/offset will appear only as a comment. When disabled,
121 the GTID position will still appear in the output, but
122 only commented.
123 -?, --help Display this help message and exit.
124 --hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in
125 hexadecimal format.
126 -h, --host=name Connect to host.
127 --ignore-table=name Do not dump the specified table. To specify more than one
128 table to ignore, use the directive multiple times, once
129 for each table. Each table must be specified with both
130 database and table names, e.g.,
131 --ignore-table=database.table.
132 --include-master-host-port
133 Adds 'MASTER_HOST=<host>, MASTER_PORT=<port>' to 'CHANGE
134 MASTER TO..' in dump produced with --dump-slave.
135 --insert-ignore Insert rows with INSERT IGNORE.
136 --lines-terminated-by=name
137 Lines in the output file are terminated by the given
138 string.
139 -x, --lock-all-tables
140 Locks all tables across all databases. This is achieved
141 by taking a global read lock for the duration of the
142 whole dump. Automatically turns --single-transaction and
143 --lock-tables off.
144 -l, --lock-tables Lock all tables for read.
145 (Defaults to on; use --skip-lock-tables to disable.)
146 --log-error=name Append warnings and errors to given file.
147 --log-queries When restoring the dump, the server will, if logging
148 turned on, log the queries to the general and slow query
149 log.
150 (Defaults to on; use --skip-log-queries to disable.)
151 --master-data[=#] This causes the binary log position and filename to be
152 appended to the output. If equal to 1, will print it as a
153 CHANGE MASTER command; if equal to 2, that command will
154 be prefixed with a comment symbol. This option will turn
155 --lock-all-tables on, unless --single-transaction is
156 specified too (on servers before MariaDB 5.3 this will
157 still take a global read lock for a short time at the
158 beginning of the dump; don't forget to read about
159 --single-transaction below). In all cases, any action on
160 logs will happen at the exact moment of the dump. Option
161 automatically turns --lock-tables off.
162 --max-allowed-packet=#
163 The maximum packet length to send to or receive from
164 server.
165 --net-buffer-length=#
166 The buffer size for TCP/IP and socket communication.
167 --no-autocommit Wrap tables with autocommit/commit statements.
168 -n, --no-create-db Suppress the CREATE DATABASE ... IF EXISTS statement that
169 normally is output for each dumped database if
170 --all-databases or --databases is given.
171 -t, --no-create-info
172 Don't write table creation info.
173 -d, --no-data No row information.
174 --no-data-med No row information for engines that Manage External Data
175 (MRG_MyISAM, MRG_ISAM, CONNECT, OQGRAPH, SPIDER, VP,
176 FEDERATED).
177 (Defaults to on; use --skip-no-data-med to disable.)
178 -N, --no-set-names Same as --skip-set-charset.
179 --opt Same as --add-drop-table, --add-locks, --create-options,
180 --quick, --extended-insert, --lock-tables, --set-charset,
181 and --disable-keys. Enabled by default, disable with
182 --skip-opt.
183 --order-by-primary Sorts each table's rows by primary key, or first unique
184 key, if such a key exists. Useful when dumping a MyISAM
185 table to be loaded into an InnoDB table, but will make
186 the dump itself take considerably longer.
187 -p, --password[=name]
188 Password to use when connecting to server. If password is
189 not given it's solicited on the tty.
190 -W, --pipe Use named pipes to connect to server.
191 -P, --port=# Port number to use for connection.
192 --protocol=name The protocol to use for connection (tcp, socket, pipe,
193 memory).
194 -q, --quick Don't buffer query, dump directly to stdout.
195 (Defaults to on; use --skip-quick to disable.)
196 -Q, --quote-names Quote table and column names with backticks (`).
197 (Defaults to on; use --skip-quote-names to disable.)
198 --replace Use REPLACE INTO instead of INSERT INTO.
199 -r, --result-file=name
200 Direct output to a given file. This option should be used
201 in systems (e.g., DOS, Windows) that use carriage-return
202 linefeed pairs (\r\n) to separate text lines. This option
203 ensures that only a single newline is used.
204 -R, --routines Dump stored routines (functions and procedures).
205 --set-charset Add 'SET NAMES default_character_set' to the output.
206 (Defaults to on; use --skip-set-charset to disable.)
207 --shared-memory-base-name=name
208 Base name of shared memory.
209 --single-transaction
210 Creates a consistent snapshot by dumping all tables in a
211 single transaction. Works ONLY for tables stored in
212 storage engines which support multiversioning (currently
213 only InnoDB does); the dump is NOT guaranteed to be
214 consistent for other storage engines. While a
215 --single-transaction dump is in process, to ensure a
216 valid dump file (correct table contents and binary log
217 position), no other connection should use the following
218 statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
219 TRUNCATE TABLE, as consistent snapshot is not isolated
220 from them. Option automatically turns off --lock-tables.
221 --dump-date Put a dump date to the end of the output.
222 (Defaults to on; use --skip-dump-date to disable.)
223 --skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
224 --create-options, --quick, --extended-insert,
225 --lock-tables, --set-charset, and --disable-keys.
226 -S, --socket=name The socket file to use for connection.
227 --ssl Enable SSL for connection (automatically enabled with
228 other flags).
229 --ssl-ca=name CA file in PEM format (check OpenSSL docs, implies
230 --ssl).
231 --ssl-capath=name CA directory (check OpenSSL docs, implies --ssl).
232 --ssl-cert=name X509 cert in PEM format (implies --ssl).
233 --ssl-cipher=name SSL cipher to use (implies --ssl).
234 --ssl-key=name X509 key in PEM format (implies --ssl).
235 --ssl-crl=name Certificate revocation list (implies --ssl).
236 --ssl-crlpath=name Certificate revocation list path (implies --ssl).
237 --ssl-verify-server-cert
238 Verify server's "Common Name" in its cert against
239 hostname used when connecting. This option is disabled by
240 default.
241 -T, --tab=name Create tab-separated textfile for each table to given
242 path. (Create .sql and .txt files.) NOTE: This only works
243 if mysqldump is run on the same machine as the mysqld
244 server.
245 --tables Overrides option --databases (-B).
246 --triggers Dump triggers for each dumped table.
247 (Defaults to on; use --skip-triggers to disable.)
248 --tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
249 TIMESTAMP data when a server has data in different time
250 zones or data is being moved between servers with
251 different time zones.
252 (Defaults to on; use --skip-tz-utc to disable.)
253 -u, --user=name User for login if not current user.
254 -v, --verbose Print info about the various stages.
255 -V, --version Output version information and exit.
256 -w, --where=name Dump only selected records. Quotes are mandatory.
257 -X, --xml Dump a database as well formed XML.
258 --plugin-dir=name Directory for client-side plugins.
259 --default-auth=name Default authentication client-side plugin to use.
260
261Variables (--variable-name=value)
262and boolean options {FALSE|TRUE} Value (after reading options)
263--------------------------------- ----------------------------------------
264all-databases FALSE
265all-tablespaces FALSE
266no-tablespaces FALSE
267add-drop-database FALSE
268add-drop-table TRUE
269add-drop-trigger FALSE
270add-locks TRUE
271allow-keywords FALSE
272apply-slave-statements FALSE
273character-sets-dir (No default value)
274comments TRUE
275compatible (No default value)
276compact FALSE
277complete-insert FALSE
278compress FALSE
279create-options TRUE
280databases FALSE
281debug-check FALSE
282debug-info FALSE
283default-character-set utf8
284delayed-insert FALSE
285delete-master-logs FALSE
286disable-keys TRUE
287dump-slave 0
288events FALSE
289extended-insert TRUE
290fields-terminated-by (No default value)
291fields-enclosed-by (No default value)
292fields-optionally-enclosed-by (No default value)
293fields-escaped-by (No default value)
294flush-logs FALSE
295flush-privileges FALSE
296force FALSE
297gtid FALSE
298hex-blob FALSE
299host (No default value)
300include-master-host-port FALSE
301insert-ignore FALSE
302lines-terminated-by (No default value)
303lock-all-tables FALSE
304lock-tables TRUE
305log-error (No default value)
306log-queries TRUE
307master-data 0
308max-allowed-packet 25165824
309net-buffer-length 1046528
310no-autocommit FALSE
311no-create-db FALSE
312no-create-info FALSE
313no-data FALSE
314no-data-med TRUE
315order-by-primary FALSE
316port 3306
317quick TRUE
318quote-names TRUE
319replace FALSE
320routines FALSE
321set-charset TRUE
322shared-memory-base-name (No default value)
323single-transaction FALSE
324dump-date TRUE
325socket (No default value)
326ssl FALSE
327ssl-ca (No default value)
328ssl-capath (No default value)
329ssl-cert (No default value)
330ssl-cipher (No default value)
331ssl-key (No default value)
332ssl-crl (No default value)
333ssl-crlpath (No default value)
334ssl-verify-server-cert FALSE
335tab (No default value)
336triggers TRUE
337tz-utc TRUE
338user (No default value)
339verbose FALSE
340where (No default value)
341plugin-dir C:/Program Files/MariaDB 10.2/lib/plugin
342default-auth (No default value)