7
:program:`drizzledump` [:ref:`OPTIONS <drizzledump-options-label>`] *database* [*tables*]
9
:program:`drizzledump` [:ref:`OPTIONS <drizzledump-options-label>`] :option:`--databases` *DB1* [*DB2* *DB3*...]
11
:program:`drizzledump` [:ref:`OPTIONS <drizzledump-options-label>`] :option:`--all-databases`
7
**drizzledump** [*OPTIONS*] *database* [*tables*]
9
**drizzledump** [*OPTIONS*] *--databases* [*OPTIONS*] *DB1* [*DB2* *DB3*...]
11
**drizzledump** [*OPTIONS*] *--all-databases* [*OPTIONS*]
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:
29
27
Drizzledump options
30
28
-------------------
32
30
The :program:`drizzledump` tool has several available options:
34
.. option:: --all-databases, -A
36
Dumps all databases found on the server apart from ``information_schema`` and
37
``data_dictionary`` in Drizzle and ``information_schema``,
38
``performance_schema`` and ``mysql`` in MySQL.
40
.. option:: --force, -f
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
42
40
Continue even if we get an sql-error.
44
.. option:: --help, -?
42
.. option:: -?, --help
46
44
Show a message with all the available options.
48
.. option:: --lock-all-tables, -x
46
.. option:: -x, --lock-all-tables
50
48
Locks all the tables for all databases with a global read lock. The lock is
51
49
released automatically when :program:`drizzledump` ends.
81
77
Show progress of the dump every *rows* of the dump. Requires
82
78
:option:`--verbose`
84
.. option:: --verbose, -v
80
.. option:: -v, --verbose
86
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.
88
88
.. option:: --skip-extended-insert
90
Dump every row on an individual line. For example:
90
Dump every row on an individual line. For example::
94
92
INSERT INTO `t1` VALUES (1,'hello');
95
93
INSERT INTO `t1` VALUES (2,'world');
112
110
:option:`--skip-add-drop-table`, :option:`--skip-disable-keys`
113
111
and :option:`--skip-add-locks`.
115
.. option:: --databases, -B
113
.. option:: -B, --databases
117
115
Dump several databases. The databases do not need to follow on after this
118
116
option, they can be anywhere in the command line.
120
.. option:: --skip-disable-keys, -K
118
.. option:: -K, --skip-disable-keys
122
Do not dump the statements ``ALTER TABLE ... DISABLE KEYS`` and
123
``ALTER TABLE ... ENABLE KEYS``
120
Do not dump the statements `ALTER TABLE ... DISABLE KEYS` and
121
`ALTER TABLE ... ENABLE KEYS`
125
123
.. option:: --ignore-table table
127
Do not dump specified table, needs to be in the format ``database.table``.
125
Do not dump specified table, needs to be in the format `database.table`.
128
126
Can be specified multiple times for multiple tables.
130
128
.. option:: --insert-ignore
132
Add the ``IGNORE`` keyword into every ``INSERT`` statement.
130
Add the `IGNORE` keyword into every `INSERT` statement.
134
132
.. option:: --no-autocommit
136
Make the dump of each table a single transaction by wrapping it in ``COMMIT``
134
Make the dump of each table a single transaction by wrapping it in `COMMIT`
139
.. option:: --no-create-db, -n
137
.. option:: -n, --no-create-db
141
Do not dump the ``CREATE DATABASE`` statements when using
139
Do not dump the `CREATE DATABASE` statements when using
142
140
:option:`--all-databases` or :option:`--databases`.
144
.. option:: --skip-create, -t
146
Do not dump the ``CREATE TABLE`` statements.
148
.. option:: --no-data, -d
142
.. option:: -t, --skip-create
144
Do not dump the `CREATE TABLE` statements.
146
.. option:: -d, --no-data
150
148
Do not dump the data itself, used to dump the schemas only.
152
150
.. option:: --replace
154
Use ``REPLACE INTO`` statements instead of ``INSERT INTO``
152
Use `REPLACE INTO` statements instead of `INSERT INTO`
156
154
.. option:: --destination-type type (=stdout)
208
206
mangled data. This is because MySQL will convert the data to UTF8 on the way
209
207
out to drizzledump and you effectively get a double-conversion to UTF8.
211
This typically happens with PHP apps that do not use ``SET NAMES``.
209
This typically happens with PHP apps that do not use 'SET NAMES'.
213
211
In these cases setting this option will retrieve the data as you see it in your
216
214
.. versionadded:: 2011-01-31
218
.. option:: --host, -h hostname (=localhost)
216
.. option:: -h, --host hostname (=localhost)
220
218
The hostname of the database server.
222
.. option:: --user, -u username
220
.. option:: -u, --user username
224
222
The username for the database server.
226
.. option:: --password, -P password
224
.. option:: -P, --password password
228
226
The password for the database server.
230
.. option:: --port, -p port (=4427)
228
.. option:: -p, --port port (=3306,4427)
232
The port number of the database server.
230
The port number of the database server. Defaults to 3306 for MySQL protocol
231
and 4427 for Drizzle protocol.
234
233
.. option:: --protocol protocol (=mysql)
244
243
Backups using Drizzledump
245
244
-------------------------
247
Backups of a database can be made very simply by running the following:
251
$ drizzledump --all-databases > dumpfile.sql
253
This can then be re-imported into drizzle at a later date using:
257
$ drizzle < dumpfile.sql
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
259
254
MySQL Migration using Drizzledump
260
255
---------------------------------
265
260
:program:`drizzledump` will automatically detect whether it is talking to a
266
261
MySQL or Drizzle database server. If it is connected to a MySQL server it will
267
262
automatically convert all the structures and data into a Drizzle compatible
272
:program:`drizzledump` will by default try to connect via. port 4427 so to
273
connect to a MySQL server a port (such as 3306) must be specified.
263
format. It will, however, by default try to connect via. port 4427 so to
264
connect to a MySQL server a port must be specified.
275
266
So, simply connecting to a MySQL server with :program:`drizzledump` as follows
276
will give you a Drizzle compatible output:
280
$ drizzledump --all-databases --host=mysql-host --port=3306 --user=mysql-user --password > dumpfile.sql
267
will give you a Drizzle compatible output::
269
$ drizzledump --all-databases --host=mysql-host --port=3306 --user=mysql-user --password > dumpfile.sql
282
271
Additionally :program:`drizzledump` can now dump from MySQL and import directly
283
into a Drizzle server as follows:
287
$ drizzledump --all-databases --host=mysql-host --port=3306 --user=mysql-user --password --destination-type=database --desination-host=drizzle-host
291
Please take special note of :ref:`old-passwords-label` if you have connection
292
issues from :program:`drizzledump` to your MySQL server.
295
If you find your VARCHAR and TEXT data does not look correct in a drizzledump
296
output, it is likely that you have UTF8 data stored in a non-UTF8 table. In
297
which case please check the :option:`--my-data-is-mangled` option.
272
into a Drizzle server as follows::
274
$ drizzledump --all-databases --host=mysql-host --port=3306 --user=mysql-user --password --destination-type=database --desination-host=drizzle-host
276
Please take special note of :ref:`old-passwords-label` if you have connection
277
issues from :program:`drizzledump` to your MySQL server.
279
If you find your VARCHAR and TEXT data does not look correct in a drizzledump
280
output, it is likely that you have UTF8 data stored in a non-UTF8 table. In
281
which case please check the :option:`--my-data-is-mangled` option.
299
283
When you migrate from MySQL to Drizzle, the following conversions are required:
310
294
* tinyblob -> blob
311
295
* mediumblob -> blob
312
296
* longblob -> blob
297
* time -> int (of seconds)
315
* date/datetime default 0000-00-00 -> default NULL [2]_
316
* date/datetime NOT NULL columns -> NULL [2]_
317
* any date data containing 0000-00-00 -> NULL [2]_
318
* time -> int of the number of seconds [3]_
319
* enum-> DEFAULT NULL [4]_
321
.. rubric:: Footnotes
323
.. [1] There is currently no good alternative to SET, this is simply to preserve
324
the data in the column. There is a new alternative to SET to be included
327
.. [2] Currently, ALL date columns have their DEFAULT set to NULL on migration.
328
This is so that any rows with 0000-00-00 dates can convert to NULL.
330
.. [3] This prevents data loss since MySQL's TIME data type has a range of
331
-838:59:59 - 838:59:59, and Drizzle's TIME type has a range of
334
.. [4] This is so that empty entries such as '' will convert to NULL.
300
* date/datetime default 0000-00-00 -> default NULL (Currently, ALL date columns have their DEFAULT set to NULL on migration)
301
* date/datetime NOT NULL columns -> NULL
302
* any date data containing 0000-00-00 -> NULL
303
* TIME -> INT of the number of seconds*
304
* enum-> DEFAULT NULL
306
* This prevents data loss since MySQL's TIME data type has a range of -838:59:59 - 838:59:59, and Drizzle's TIME type has a range of 00:00:00 - 23:59:61.999999.
added
removed
docs/clients/drizzledump.rst
