24
24
Any binary data in tables will be converted into hexadecimal output so that it
25
25
does not corrupt the dump file.
27
.. _drizzledump-options-label:
27
29
Drizzledump options
28
30
-------------------
30
32
The :program:`drizzledump` tool has several available options:
32
.. option:: -A, --all-databases
34
Dumps all databases found on the server apart from *information_schema* and
35
*data_dictionary* in Drizzle and *information_schema*, *performance_schema*
38
.. option:: -f, --force
40
Continue even if we get an sql-error.
42
.. option:: -?, --help
44
Show a message with all the available options.
46
.. option:: -x, --lock-all-tables
48
Locks all the tables for all databases with a global read lock. The lock is
49
released automatically when :program:`drizzledump` ends.
50
Turns on :option:`--single-transaction` and :option:`--lock-tables`.
34
.. program:: drizzledump
36
.. option:: --all-databases, -A
38
Dumps all databases found on the server apart from ``information_schema`` and
39
``data_dictionary`` in Drizzle and ``information_schema``,
40
``performance_schema`` and ``mysql`` in MySQL.
42
.. option:: --force, -f
44
Continue even if we get an sql-error.
46
.. option:: --help, -?
48
Show a message with all the available options.
50
.. option:: --lock-all-tables, -x
52
Locks all the tables for all databases with a global read lock. The lock is
53
released automatically when :program:`drizzledump` ends.
54
Turns on :option:`--single-transaction` and :option:`--lock-tables`.
52
56
.. option:: --single-transaction
54
Creates a consistent snapshot by dumping the tables in a single transaction.
55
During the snapshot no other connected client should use any of the
56
following as this will implicitly commit the transaction and prevent the
58
Creates a consistent snapshot by dumping the tables in a single transaction.
59
During the snapshot no other connected client should use any of the
60
following as this will implicitly commit the transaction and prevent the
64
Only works with InnoDB.
70
Only works with InnoDB.
66
72
.. option:: --skip-opt
68
A shortcut for :option:`--skip-drop-table`, :option:`--skip-create`,
69
:option:`--skip-extended-insert` and :option:`--skip-disable-keys`
74
A shortcut for :option:`--skip-drop-table`, :option:`--skip-create`,
75
:option:`--skip-extended-insert` and :option:`--skip-disable-keys`
71
77
.. option:: --tables t1 t2 ...
73
Dump a list of tables.
79
Dump a list of tables.
75
81
.. option:: --show-progress-size rows (=10000)
77
Show progress of the dump every *rows* of the dump. Requires
80
.. option:: -v, --verbose
82
Sends various verbose information to stderr as the dump progresses.
84
.. option:: --skip-create
86
Do not dump the CREATE TABLE / CREATE DATABASE statements.
83
Show progress of the dump every *rows* of the dump. Requires
86
.. option:: --verbose, -v
88
Sends various verbose information to stderr as the dump progresses.
88
90
.. option:: --skip-extended-insert
90
Dump every row on an individual line. For example::
92
Dump every row on an individual line. For example:
92
96
INSERT INTO `t1` VALUES (1,'hello');
93
97
INSERT INTO `t1` VALUES (2,'world');
95
99
.. option:: --skip-dump-date
97
Do not display the date/time at the end of the dump.
101
Do not display the date/time at the end of the dump.
99
103
.. option:: --no-defaults
101
Do not attempt to read configuration from configuration files.
105
Do not attempt to read configuration from configuration files.
103
107
.. option:: --add-drop-database
105
Add `DROP DATABASE` statements before `CREATE DATABASE`.
109
Add ``DROP DATABASE`` statements before ``CREATE DATABASE``.
107
111
.. option:: --compact
109
Gives a more compact output by disabling header/footer comments and enabling
110
:option:`--skip-add-drop-table`, :option:`--skip-disable-keys`
111
and :option:`--skip-add-locks`.
113
.. option:: -B, --databases
115
Dump several databases. The databases do not need to follow on after this
116
option, they can be anywhere in the command line.
118
.. option:: -K, --skip-disable-keys
120
Do not dump the statements `ALTER TABLE ... DISABLE KEYS` and
121
`ALTER TABLE ... ENABLE KEYS`
113
Gives a more compact output by disabling header/footer comments and enabling
114
:option:`--skip-add-drop-table`, :option:`--skip-disable-keys`
115
and :option:`--skip-add-locks`.
117
.. option:: --databases, -B
119
Dump several databases. The databases do not need to follow on after this
120
option, they can be anywhere in the command line.
122
.. option:: --skip-disable-keys, -K
124
Do not dump the statements ``ALTER TABLE ... DISABLE KEYS`` and
125
``ALTER TABLE ... ENABLE KEYS``
123
127
.. option:: --ignore-table table
125
Do not dump specified table, needs to be in the format `database.table`.
126
Can be specified multiple times for multiple tables.
129
Do not dump specified table, needs to be in the format ``database.table``.
130
Can be specified multiple times for multiple tables.
128
132
.. option:: --insert-ignore
130
Add the `IGNORE` keyword into every `INSERT` statement.
134
Add the ``IGNORE`` keyword into every ``INSERT`` statement.
132
136
.. option:: --no-autocommit
134
Make the dump of each table a single transaction by wrapping it in `COMMIT`
137
.. option:: -n, --no-create-db
139
Do not dump the `CREATE DATABASE` statements when using
140
:option:`--all-databases` or :option:`--databases`.
142
.. option:: -t, --skip-create
144
Do not dump the `CREATE TABLE` statements.
146
.. option:: -d, --no-data
148
Do not dump the data itself, used to dump the schemas only.
138
Make the dump of each table a single transaction by wrapping it in ``COMMIT``
141
.. option:: --no-create-db, -n
143
Do not dump the ``CREATE DATABASE`` statements when using
144
:option:`--all-databases` or :option:`--databases`.
146
.. option:: --skip-create, -t
148
Do not dump the ``CREATE TABLE`` statements.
150
.. option:: --no-data, -d
152
Do not dump the data itself, used to dump the schemas only.
150
154
.. option:: --replace
152
Use `REPLACE INTO` statements instead of `INSERT INTO`
156
Use ``REPLACE INTO`` statements instead of ``INSERT INTO``
154
158
.. option:: --destination-type type (=stdout)
156
Destination of the data.
159
The default. Output to the command line
162
Connect to another database and pipe data to that.
164
.. versionadded:: 2010-09-27
160
Destination of the data.
163
The default. Output to the command line
166
Connect to another database and pipe data to that.
168
.. versionadded:: Drizzle7 2010-09-27
166
170
.. option:: --destination-host hostname (=localhost)
168
The hostname for the destination database. Requires
169
:option:`--destination-type` `= database`
172
The hostname for the destination database. Requires
173
:option:`--destination-type` `= database`
171
.. versionadded:: 2010-09-27
175
.. versionadded:: Drizzle7 2010-09-27
173
177
.. option:: --destination-port port (=3306)
175
The port number for the destination database. Requires
176
:option:`--destination-type` `= database`
179
The port number for the destination database. Requires
180
:option:`--destination-type` `= database`
178
.. versionadded:: 2010-09-27
182
.. versionadded:: Drizzle7 2010-09-27
180
184
.. option:: --destination-user username
182
The username for the destinations database. Requires
183
:option:`--destination-type` `= database`
186
The username for the destinations database. Requires
187
:option:`--destination-type` `= database`
185
.. versionadded:: 2010-09-27
189
.. versionadded:: Drizzle7 2010-09-27
187
191
.. option:: --destination-password password
189
The password for the destination database. Requires
190
:option:`--destination-type` `= database`
193
The password for the destination database. Requires
194
:option:`--destination-type` `= database`
192
.. versionadded:: 2010-09-27
196
.. versionadded:: Drizzle7 2010-09-27
194
198
.. option:: --destination-database database
196
The database for the destination database, for use when only dumping a
197
single database. Requires
198
:option:`--destination-type` `= database`
200
The database for the destination database, for use when only dumping a
201
single database. Requires
202
:option:`--destination-type` `= database`
200
.. versionadded:: 2010-09-27
204
.. versionadded:: Drizzle7 2010-09-27
202
206
.. option:: --my-data-is-mangled
204
If your data is UTF8 but has been stored in a latin1 table using a latin1
205
connection then corruption is likely and drizzledump by default will retrieve
206
mangled data. This is because MySQL will convert the data to UTF8 on the way
207
out to drizzledump and you effectively get a double-conversion to UTF8.
209
This typically happens with PHP apps that do not use 'SET NAMES'.
211
In these cases setting this option will retrieve the data as you see it in your
214
.. versionadded:: 2011-01-31
216
.. option:: -h, --host hostname (=localhost)
218
The hostname of the database server.
220
.. option:: -u, --user username
222
The username for the database server.
224
.. option:: -P, --password password
226
The password for the database server.
228
.. option:: -p, --port port (=3306,4427)
230
The port number of the database server. Defaults to 3306 for MySQL protocol
231
and 4427 for Drizzle protocol.
208
If your data is UTF8 but has been stored in a latin1 table using a latin1
209
connection then corruption is likely and drizzledump by default will retrieve
210
mangled data. This is because MySQL will convert the data to UTF8 on the way
211
out to drizzledump and you effectively get a double-conversion to UTF8.
213
This typically happens with PHP apps that do not use ``SET NAMES``.
215
In these cases setting this option will retrieve the data as you see it in
218
.. versionadded:: Drizzle7 2011-01-31
220
.. option:: --host, -h hostname (=localhost)
222
The hostname of the database server.
224
.. option:: --user, -u username
226
The username for the database server.
228
.. option:: --password, -P password
230
The password for the database server.
232
.. option:: --port, -p port (=4427)
234
The port number of the database server.
233
236
.. option:: --protocol protocol (=mysql)
235
The protocol to use when connecting to the database server. Options are:
238
The standard MySQL protocol.
241
The Drizzle protocol.
238
The protocol to use when connecting to the database server. Options are:
241
The standard MySQL protocol.
244
The Drizzle protocol.
243
246
Backups using Drizzledump
244
247
-------------------------
246
Backups of a database can be made very simply by running the following::
248
$ drizzledump --all-databases > dumpfile.sql
250
This can then be re-imported into drizzle at a later date using::
252
$ drizzle < dumpfile.sql
249
Backups of a database can be made very simply by running the following:
253
$ drizzledump --all-databases > dumpfile.sql
255
This can then be re-imported into drizzle at a later date using:
259
$ drizzle < dumpfile.sql
261
.. _drizzledump-migration-label:
254
263
MySQL Migration using Drizzledump
255
264
---------------------------------
added
removed
docs/clients/drizzledump.rst
