1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
|
Drizzledump Backup Tool
=======================
Synopsis
--------
**drizzledump** [*OPTIONS*] *database* [*tables*]
**drizzledump** [*OPTIONS*] *--databases* [*OPTIONS*] *DB1* [*DB2* *DB3*...]
**drizzledump** [*OPTIONS*] *--all-databases* [*OPTIONS*]
Description
-----------
:program:`drizzledump` is used for backing up and
restoring logical backups of a Drizzle database, as well as for migrating
from *MySQL*.
When connecting to a Drizzle server it will do a plain dump of the server. It
will, however, automatically detect when it is connected to a *MySQL* server and
will convert the tables and data into a Drizzle compatible format.
Any binary data in tables will be converted into hexadecimal output so that it
does not corrupt the dump file.
Drizzledump options
-------------------
The :program:`drizzledump` tool has several available options:
.. option:: -A, --all-databases
Dumps all databases found on the server apart from *information_schema* and
*data_dictionary* in Drizzle and *information_schema*, *performance_schema*
and *mysql* in MySQL.
.. option:: -f, --force
Continue even if we get an sql-error.
.. option:: -?, --help
Show a message with all the available options.
.. option:: -x, --lock-all-tables
Locks all the tables for all databases with a global read lock. The lock is
released automatically when :program:`drizzledump` ends.
Turns on :option:`--single-transaction` and :option:`--lock-tables`.
.. option:: --single-transaction
Creates a consistent snapshot by dumping the tables in a single transaction.
During the snapshot no other connected client should use any of the
following as this will implicitly commit the transaction and prevent the
consistency::
ALTER TABLE
DROP TABLE
RENAME TABLE
TRUNCATE TABLE
Only works with InnoDB.
.. option:: --skip-opt
A shortcut for :option:`--skip-drop-table`, :option:`--skip-create`,
:option:`--skip-extended-insert` and :option:`--skip-disable-keys`
.. option:: --tables t1 t2 ...
Dump a list of tables.
.. option:: --show-progress-size rows (=10000)
Show progress of the dump every *rows* of the dump. Requires
:option:`--verbose`
.. option:: -v, --verbose
Sends various verbose information to stderr as the dump progresses.
.. option:: --skip-create
Do not dump the CREATE TABLE / CREATE DATABASE statements.
.. option:: --skip-extended-insert
Dump every row on an individual line. For example::
INSERT INTO `t1` VALUES (1,'hello');
INSERT INTO `t1` VALUES (2,'world');
.. option:: --skip-dump-date
Do not display the date/time at the end of the dump.
.. option:: --no-defaults
Do not attempt to read configuration from configuration files.
.. option:: --add-drop-database
Add `DROP DATABASE` statements before `CREATE DATABASE`.
.. option:: --compact
Gives a more compact output by disabling header/footer comments and enabling
:option:`--skip-add-drop-table`, :option:`--skip-disable-keys`
and :option:`--skip-add-locks`.
.. option:: -B, --databases
Dump several databases. The databases do not need to follow on after this
option, they can be anywhere in the command line.
.. option:: -K, --skip-disable-keys
Do not dump the statements `ALTER TABLE ... DISABLE KEYS` and
`ALTER TABLE ... ENABLE KEYS`
.. option:: --ignore-table table
Do not dump specified table, needs to be in the format `database.table`.
Can be specified multiple times for multiple tables.
.. option:: --insert-ignore
Add the `IGNORE` keyword into every `INSERT` statement.
.. option:: --no-autocommit
Make the dump of each table a single transaction by wrapping it in `COMMIT`
statements.
.. option:: -n, --no-create-db
Do not dump the `CREATE DATABASE` statements when using
:option:`--all-databases` or :option:`--databases`.
.. option:: -t, --skip-create
Do not dump the `CREATE TABLE` statements.
.. option:: -d, --no-data
Do not dump the data itself, used to dump the schemas only.
.. option:: --replace
Use `REPLACE INTO` statements instead of `INSERT INTO`
.. option:: --destination-type type (=stdout)
Destination of the data.
**stdout**
The default. Output to the command line
**database**
Connect to another database and pipe data to that.
.. versionadded:: 2010-09-27
.. option:: --destination-host hostname (=localhost)
The hostname for the destination database. Requires
:option:`--destination-type` `= database`
.. versionadded:: 2010-09-27
.. option:: --destination-port port (=3306)
The port number for the destination database. Requires
:option:`--destination-type` `= database`
.. versionadded:: 2010-09-27
.. option:: --destination-user username
The username for the destinations database. Requires
:option:`--destination-type` `= database`
.. versionadded:: 2010-09-27
.. option:: --destination-password password
The password for the destination database. Requires
:option:`--destination-type` `= database`
.. versionadded:: 2010-09-27
.. option:: --destination-database database
The database for the destination database, for use when only dumping a
single database. Requires
:option:`--destination-type` `= database`
.. versionadded:: 2010-09-27
.. option:: -h, --host hostname (=localhost)
The hostname of the database server.
.. option:: -u, --user username
The username for the database server.
.. option:: -P, --password password
The password for the database server.
.. option:: -p, --port port (=3306,4427)
The port number of the database server. Defaults to 3306 for MySQL protocol
and 4427 for Drizzle protocol.
.. option:: --protocol protocol (=mysql)
The protocol to use when connecting to the database server. Options are:
**mysql**
The standard MySQL protocol.
**drizzle**
The Drizzle protocol.
Backups using Drizzledump
-------------------------
Backups of a database can be made very simply by running the following::
$ drizzledump --all-databases > dumpfile.sql
This can then be re-imported into drizzle at a later date using::
$ drizzle < dumpfile.sql
MySQL Migration using Drizzledump
---------------------------------
As of version 2010-09-27 there is the capability to migrate databases from
MySQL to Drizzle using :program:`drizzledump`.
:program:`drizzledump` will automatically detect whether it is talking to a
MySQL or Drizzle database server. If it is connected to a MySQL server it will
automatically convert all the structures and data into a Drizzle compatible
format.
So, simply connecting to a MySQL server with :program:`drizzledump` as follows
will give you a Drizzle compatible output::
$ drizzledump --all-databases --host=mysql-host --user=mysql-user --password > dumpfile.sql
Additionally :program:`drizzledump` can now dump from MySQL and import directly
into a Drizzle server as follows::
$ drizzledump --all-databases --host=mysql-host --user=mysql-user --password --destination-type=database --desination-host=drizzle-host
When you migrate from MySQL to Drizzle, the following conversions are required:
MyISAM -> InnoDB
FullText -> drop it (with stderr warning)
int unsigned -> bigint
tinyint -> int
smallint -> int
mediumint -> int
tinytext -> text
mediumtext -> text
longtext -> text
tinyblob -> blob
mediumblob -> blob
longblob -> blob
time -> int (of seconds)
year -> int
set -> text
date/datetime default 0000-00-00 -> default NULL *(Currently, ALL date columns have their DEFAULT set to NULL on migration)
date/datetime NOT NULL columns -> NULL
any date data containing 0000-00-00 -> NULL
enum-> DEFAULT NULL
|