30
30
The :program:`drizzledump` tool has several available options:
32
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*
34
Dumps all databases found on the server apart from *information_schema* and
35
*data_dictionary* in Drizzle and *information_schema*, *performance_schema*
38
38
.. option:: -f, --force
40
Continue even if we get an sql-error.
40
Continue even if we get an sql-error.
42
42
.. option:: -?, --help
44
Show a message with all the available options.
44
Show a message with all the available options.
46
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`.
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`.
52
52
.. 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
64
Only works with InnoDB.
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
64
Only works with InnoDB.
66
66
.. option:: --skip-opt
68
A shortcut for :option:`--skip-drop-table`, :option:`--skip-create`,
69
:option:`--skip-extended-insert` and :option:`--skip-disable-keys`
68
A shortcut for :option:`--skip-drop-table`, :option:`--skip-create`,
69
:option:`--skip-extended-insert` and :option:`--skip-disable-keys`
71
71
.. option:: --tables t1 t2 ...
73
Dump a list of tables.
73
Dump a list of tables.
75
75
.. option:: --show-progress-size rows (=10000)
77
Show progress of the dump every *rows* of the dump. Requires
77
Show progress of the dump every *rows* of the dump. Requires
80
80
.. option:: -v, --verbose
82
Sends various verbose information to stderr as the dump progresses.
82
Sends various verbose information to stderr as the dump progresses.
84
84
.. option:: --skip-create
86
Do not dump the CREATE TABLE / CREATE DATABASE statements.
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::
92
INSERT INTO `t1` VALUES (1,'hello');
93
INSERT INTO `t1` VALUES (2,'world');
92
INSERT INTO `t1` VALUES (1,'hello');
93
INSERT INTO `t1` VALUES (2,'world');
95
95
.. option:: --skip-dump-date
97
Do not display the date/time at the end of the dump.
97
Do not display the date/time at the end of the dump.
99
99
.. option:: --no-defaults
101
Do not attempt to read configuration from configuration files.
101
Do not attempt to read configuration from configuration files.
103
103
.. option:: --add-drop-database
105
Add `DROP DATABASE` statements before `CREATE DATABASE`.
105
Add `DROP DATABASE` statements before `CREATE DATABASE`.
107
107
.. 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`.
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
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.
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
118
.. option:: -K, --skip-disable-keys
120
Do not dump the statements `ALTER TABLE ... DISABLE KEYS` and
121
`ALTER TABLE ... ENABLE KEYS`
120
Do not dump the statements `ALTER TABLE ... DISABLE KEYS` and
121
`ALTER TABLE ... ENABLE KEYS`
123
123
.. 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.
125
Do not dump specified table, needs to be in the format `database.table`.
126
Can be specified multiple times for multiple tables.
128
128
.. option:: --insert-ignore
130
Add the `IGNORE` keyword into every `INSERT` statement.
130
Add the `IGNORE` keyword into every `INSERT` statement.
132
132
.. option:: --no-autocommit
134
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`
137
137
.. option:: -n, --no-create-db
139
Do not dump the `CREATE DATABASE` statements when using
140
:option:`--all-databases` or :option:`--databases`.
139
Do not dump the `CREATE DATABASE` statements when using
140
:option:`--all-databases` or :option:`--databases`.
142
142
.. option:: -t, --skip-create
144
Do not dump the `CREATE TABLE` statements.
144
Do not dump the `CREATE TABLE` statements.
146
146
.. option:: -d, --no-data
148
Do not dump the data itself, used to dump the schemas only.
148
Do not dump the data itself, used to dump the schemas only.
150
150
.. option:: --replace
152
Use `REPLACE INTO` statements instead of `INSERT INTO`
152
Use `REPLACE INTO` statements instead of `INSERT INTO`
154
154
.. 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
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
166
166
.. option:: --destination-host hostname (=localhost)
168
The hostname for the destination database. Requires
169
:option:`--destination-type` `= database`
168
The hostname for the destination database. Requires
169
:option:`--destination-type` `= database`
171
.. versionadded:: 2010-09-27
171
.. versionadded:: 2010-09-27
173
173
.. option:: --destination-port port (=3306)
175
The port number for the destination database. Requires
176
:option:`--destination-type` `= database`
175
The port number for the destination database. Requires
176
:option:`--destination-type` `= database`
178
.. versionadded:: 2010-09-27
178
.. versionadded:: 2010-09-27
180
180
.. option:: --destination-user username
182
The username for the destinations database. Requires
183
:option:`--destination-type` `= database`
182
The username for the destinations database. Requires
183
:option:`--destination-type` `= database`
185
.. versionadded:: 2010-09-27
185
.. versionadded:: 2010-09-27
187
187
.. option:: --destination-password password
189
The password for the destination database. Requires
190
:option:`--destination-type` `= database`
189
The password for the destination database. Requires
190
:option:`--destination-type` `= database`
192
.. versionadded:: 2010-09-27
192
.. versionadded:: 2010-09-27
194
194
.. 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
.. versionadded:: 2010-09-27
202
.. 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
196
The database for the destination database, for use when only dumping a
197
single database. Requires
198
:option:`--destination-type` `= database`
200
.. versionadded:: 2010-09-27
216
202
.. option:: -h, --host hostname (=localhost)
218
The hostname of the database server.
204
The hostname of the database server.
220
206
.. option:: -u, --user username
222
The username for the database server.
208
The username for the database server.
224
210
.. option:: -P, --password password
226
The password for the database server.
212
The password for the database server.
228
214
.. 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.
216
The port number of the database server. Defaults to 3306 for MySQL protocol
217
and 4427 for Drizzle protocol.
233
219
.. 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.
221
The protocol to use when connecting to the database server. Options are:
224
The standard MySQL protocol.
227
The Drizzle protocol.
243
229
Backups using Drizzledump
244
230
-------------------------
246
232
Backups of a database can be made very simply by running the following::
248
$ drizzledump --all-databases > dumpfile.sql
234
$ drizzledump --all-databases > dumpfile.sql
250
236
This can then be re-imported into drizzle at a later date using::
252
$ drizzle < dumpfile.sql
238
$ drizzle < dumpfile.sql
254
240
MySQL Migration using Drizzledump
255
241
---------------------------------
260
246
:program:`drizzledump` will automatically detect whether it is talking to a
261
247
MySQL or Drizzle database server. If it is connected to a MySQL server it will
262
248
automatically convert all the structures and data into a Drizzle compatible
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.
266
251
So, simply connecting to a MySQL server with :program:`drizzledump` as follows
267
252
will give you a Drizzle compatible output::
269
$ drizzledump --all-databases --host=mysql-host --port=3306 --user=mysql-user --password > dumpfile.sql
254
$ drizzledump --all-databases --host=mysql-host --user=mysql-user --password > dumpfile.sql
271
256
Additionally :program:`drizzledump` can now dump from MySQL and import directly
272
257
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.
259
$ drizzledump --all-databases --host=mysql-host --user=mysql-user --password --destination-type=database --desination-host=drizzle-host
283
261
When you migrate from MySQL to Drizzle, the following conversions are required:
286
* FullText -> drop it (with stderr warning)
287
* int unsigned -> bigint
297
* time -> int (of seconds)
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.
264
FullText -> drop it (with stderr warning)
265
int unsigned -> bigint
275
time -> int (of seconds)
278
date/datetime default 0000-00-00 -> default NULL *(Currently, ALL date columns have their DEFAULT set to NULL on migration)
279
date/datetime NOT NULL columns -> NULL
280
any date data containing 0000-00-00 -> NULL
b'\\ No newline at end of file'
added
removed
docs/clients/drizzledump.rst
