4
4
The :program:`drizzledump` command line tool is used for backing up and
5
5
restoring logical backups of a Drizzle database, as well as for migrating
6
from *MySQL*. It is called like this::
8
$ drizzledump [OPTIONS] database [tables]
9
$ drizzledump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
10
$ drizzledump [OPTIONS] --all-databases [OPTIONS]
12
When connecting to a Drizzle server it will do a plain dump of the server. It
13
will, however, automatically detect when it is connected to a *MySQL* server and
14
will convert the tables and data into a Drizzle compatible format.
16
Any binary data in tables will be converted into hexadecimal output so that it
17
does not corrupt the dump file.
22
The :program:`drizzledump` tool has several available options:
24
.. option:: -A, --all-databases
26
Dumps all databases found on the server apart from *information_schema* and
27
*data_dictionary* in Drizzle and *information_schema*, *performance_schema*
30
.. option:: -c, --complete-insert
32
Dumps using complete insert statements. *CURRENTLY BROKEN*
34
.. option:: -C, --compress
36
Use client/server compression protocol.
38
.. option:: -F, --flush-logs
40
Flush the server logs before commencing with the dump. *CURRENTLY_BROKEN*
42
.. option:: -f, --force
44
Continue even if we get an sql-error. *CURRENTLY_BROKEN*
46
.. option:: -?, --help
48
Show a message with all the available options.
50
.. option:: -x, --lock-all-tables
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`.
56
.. option:: --single-transaction
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
68
Only works with InnoDB. Automatically disables :option:`--lock-tables`.
70
.. option:: --disable-opt
72
A shortcut for :option:`--skip-drop-table`, :option:`--skip-locks`,
73
:option:`--skip-create`, :option:`--slow`, :option:`--skip-extended-insert`
74
and :option:`--skip-disable-keys`
76
.. option:: --tables t1 t2 ...
78
Dump a list of tables.
80
.. option:: --show-progress-size rows (=10000)
82
Show progress of the dump every *rows* of the dump. Requires
85
.. option:: -v, --verbose
87
Sends various verbose information to stderr as the dump progresses.
89
.. option:: --skip-create
91
Do not dump the CREATE TABLE / CREATE DATABASE statements.
93
.. option:: --skip-extended-insert
95
Dump every row on an individual line. For example::
97
INSERT INTO `t1` VALUES (1,'hello');
98
INSERT INTO `t1` VALUES (2,'world');
100
.. option:: --skip-dump-date
102
Do not display the date/time at the end of the dump.
104
.. option:: --no-defaults
106
Do not attempt to read configuration from configuration files.
108
.. option:: --add-drop-database
110
Add `DROP DATABASE` statements before `CREATE DATABASE`.
112
.. option:: --compact
114
Gives a more compact output by disabling header/footer comments and enabling
115
:option:`--skip-add-drop-table`, :option:`--no-set-names`,
116
:option:`--skip-disable-keys` and :option:`--skip-add-locks`.
118
.. option:: -B, --databases
120
Dump several databases. The databases do not need to follow on after this
121
option, they can be anywhere in the command line.
123
.. option:: --insert-ignore
125
Add the `IGNORE` keyword into every `INSERT` statement.
127
.. option:: --no-autocommit
129
Make the dump of each table a single transaction by wrapping it in `COMMIT`
132
.. option:: -n, --no-create-db
134
Do not output the `CREATE DATABASE` statements when using
135
:option:`--all-databases` or :option:`--databases`.
137
.. option:: -d, --no-data
139
Do not dump the data itself, used to dump the schemas only.
143
Dump directly from database to destination rather than using a query buffer.
145
.. option:: --replace
147
Use `REPLACE INTO` statements instead of `INSERT INTO`
149
.. option:: --destination-type type (=stdout)
151
Destination of the data.
154
The default. Output to the command line
157
Connect to another database and pipe data to that.
159
.. versionadded:: 2010-09-27
161
.. option:: --destination-host hostname (=localhost)
163
The hostname for the destination database. Requires
164
:option:`--destination-type` `= database`
166
.. versionadded:: 2010-09-27
168
.. option:: --destination-port port (=3306)
170
The port number for the destination database. Requires
171
:option:`--destination-type` `= database`
173
.. versionadded:: 2010-09-27
175
.. option:: --destination-user username
177
The username for the destinations database. Requires
178
:option:`--destination-type` `= database`
180
.. versionadded:: 2010-09-27
182
.. option:: --destination-password password
184
The password for the destination database. Requires
185
:option:`--destination-type` `= database`
187
.. versionadded:: 2010-09-27
189
.. option:: --destination-database database
191
The database for the destination database, for use when only dumping a
192
single database. Requires
193
:option:`--destination-type` `= database`
195
.. versionadded:: 2010-09-27
197
.. option:: -h, --host hostname (=localhost)
199
The hostname of the database server.
201
.. option:: -u, --user username
203
The username for the database server.
205
.. option:: -P, --password password
207
The password for the database server.
209
.. option:: -p, --port port (=3306,4427)
211
The port number of the database server. Defaults to 3306 for MySQL protocol
212
and 4427 for Drizzle protocol.
214
.. option:: --protocol protocol (=mysql)
216
The protocol to use when connecting to the database server. Options are:
219
The standard MySQL protocol.
222
The Drizzle protocol.
224
Backups using Drizzledump
225
-------------------------
227
Backups of a database can be made very simply by running the following::
229
$ drizzledump --all-databases > dumpfile.sql
231
This can then be re-imported into drizzle at a later date using::
233
$ drizzle < dumpfile.sql
235
MySQL Migration using Drizzledump
236
---------------------------------
238
As of version 2010-09-27 there is the capability to migrate databases from
239
MySQL to Drizzle using :program:`drizzledump`.
241
:program:`drizzledump` will automatically detect whether it is talking to a
242
MySQL or Drizzle database server. If it is connected to a MySQL server it will
243
automatically convert all the structures and data into a Drizzle compatible
246
So, simply connecting to a MySQL server with :program:`drizzledump` as follows
247
will give you a Drizzle compatible output::
249
$ drizzledump --all-databases --host=mysql-host --user=mysql-user --password > dumpfile.sql
251
Additionally :program:`drizzledump` can now dump from MySQL and import directly
252
into a Drizzle server as follows::
254
$ drizzledump --all-databases --host=mysql-host --user=mysql-user --password --destination-type=database --desination-host=drizzle-host