1MYSQLDBCOPY(1) MySQL Utilities MYSQLDBCOPY(1)
2
6 mysqldbcopy - Copy Database Objects Between Servers
7
9 mysqldbcopy [options] db_name [:new_db_name]
10
12 This utility copies a database on a source server to a database on a
13 destination server. If the source and destination servers are
14 different, the database names can be the same or different. If the
15 source and destination servers are the same, the database names must be
16 different.
17 The utility accepts one or more database pairs on the command line. To
19 name a database pair, use db_name:new_db_name syntax to specify the
20 source and destination names explicitly. If the source and destination
21 database names are the same, db_name can be used as shorthand for
22 db_name:db_name.
23 By default, the operation copies all objects (tables, views, triggers,
25 events, procedures, functions, and database-level grants) and data to
26 the destination server. There are options to turn off copying any or
27 all of the objects as well as not copying the data.
28 To exclude specific objects by name, use the --exclude option with a
30 name in db.*obj* format, or you can supply a search pattern. For
31 example, --exclude=db1.trig1 excludes the single trigger and
32 --exclude=trig_ excludes all objects from all databases having a name
33 that begins with trig and has a following character.
34 By default, the utility creates each table on the destination server
36 using the same storage engine as the original table. To override this
37 and specify the storage engine to use for all tables created on the
38 destination server, use the --new-storage-engine option. If the
39 destination server supports the new engine, all tables use that engine.
40 To specify the storage engine to use for tables for which the
42 destination server does not support the original storage engine on the
43 source server, use the --default-storage-engine option.
44 The --new-storage-engine option takes precedence over
46 --default-storage-engine if both are given.
47 If the --new-storage-engine or --default-storage-engine option is given
49 and the destination server does not support the specified storage
50 engine, a warning is issued and the server's default storage engine
51 setting is used instead.
52 By default, the operation uses a consistent snapshot to read the source
54 databases. To change the locking mode, use the --locking option with a
55 locking type value. Use a value of no-locks to turn off locking
56 altogether or lock-all to use only table locks. The default value is
57 snapshot. Additionally, the utility uses WRITE locks to lock the
58 destination tables during the copy.
59 You can include replication statements for copying data among a master
61 and slave or between slaves. The --rpl option permits you to select
62 from the following replication statements to include in the export.
63 · master
65 Create and execute a CHANGE MASTER statement to make the
67 destination server a slave of the server specified in the --source
68 option. This executes the appropriate STOP and START slave
69 statements. The STOP SLAVE statement is executed at the start of
70 the copy and the CHANGE MASTER followed by the START SLAVE
71 statements are executed after the copy.
72 · slave
74 Create and execute a CHANGE MASTER statement to make the
76 destination server a slave connected to the same master as the
77 server specified in the --source option. This executes the
78 appropriate STOP and START slave statements. The STOP SLAVE
79 statement is executed at the start of the copy and the CHANGE
80 MASTER followed by the START SLAVE statements after the copy.
81 To include the replication user in the CHANGE MASTER statement, use the
83 --rpl-user option to specify the user and password. If this option is
84 omitted, the utility attempts to identify the replication user. In the
85 event that there are multiple candidates or the user requires a
86 password, the utility aborts with an error.
87 If you attempt to copy databases on a server with GTIDs enabled
89 (GTID_MODE = ON), a warning will be generated if the copy does not
90 include all databases. This is because the GTID statements generated
91 include the GTIDs for all databases and not only those databases in the
92 export.
93 The utility will also generate a warning if you copy databases on a
95 GTID enabled server but use the --skip-gtid option.
96 To make the most use of GTIDs, you should copy all of the databases on
98 the server with the --all option. OPTIONS.PP mysqldbcopy accepts the
99 following command-line options:
100 · --help
102 Display a help message and exit.
104 · --license
106 Display license information and exit.
108 · --character-set=<charset>
110 Sets the client character set. The default is retrieved from the
112 server variable character_set_client.
113 · --default-storage-engine=<def_engine>
115 The engine to use for tables if the destination server does not
117 support the original storage engine on the source server.
118 · --destination=<destination>
120 Connection information for the destination server.
122 To connect to a server, it is necessary to specify connection
124 parameters such as user name, host name, password, and either a
125 port or socket. MySQL Utilities provides a number of ways to
126 provide this information. All of the methods require specifying
127 your choice via a command-line option such as --server, --master,
128 --slave, etc. The methods include the following in order of most
129 secure to least secure.
130 · Use login-paths from your .mylogin.cnf file (encrypted, not
132 visible). Example : <login-path>[:<port>][:<socket>]
133 · Use a configuration file (unencrypted, not visible) Note:
135 available in release-1.5.0. Example :
136 <configuration-file-path>[:<section>]
137 · Specify the data on the command-line (unencrypted, visible).
139 Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]
140 · --exclude=<exclude>, -x<exclude>
143 Exclude one or more objects from the operation using either a
145 specific name such as db1.t1 or a search pattern. Use this option
146 multiple times to specify multiple exclusions. By default, patterns
147 use LIKE matching. With the --regexp option, patterns use REGEXP
148 matching.
149 This option does not apply to grants.
151 · --drop-first
153 Drop each database to be copied if exists before copying anything
155 into it. Without this option, an error occurs if you attempt to
156 copy objects into an existing database.
157 Note
159 Before MySQL Utilities 1.4.2, this option was named --force.
160 · --locking=<locking>
162 Choose the lock type for the operation. Permitted lock values are
164 no-locks (do not use any table locks), lock-all (use table locks
165 but no transaction and no consistent read), and snapshot
166 (consistent read using a single transaction). The default is
167 snapshot.
168 · --multiprocess
170 Specify the number of processes to concurrently copy the specified
172 databases. Special values: 0 (number of processes equal to the
173 number of detected CPUs) and 1 (default - no concurrency).
174 Multiprocessing works at the database level for Windows and at the
175 table level for Non-Windows (POSIX) systems.
176 · --new-storage-engine=<new_engine>
178 The engine to use for all tables created on the destination server.
180 · --quiet, -q
182 Turn off all messages for quiet execution.
184 · --regexp, --basic-regexp, -G
186 Perform pattern matches using the REGEXP operator. The default is
188 to use LIKE for matching.
189 · --rpl=<dump_option>, --replication=<dump_option>
191 Include replication information. Permitted values are master (make
193 destination a slave of the source server) and slave (make
194 destination a slave of the same master as the source - only works
195 if the source server is a slave).
196 · --rpl-user=<replication_user>
198 The user and password for the replication user requirement in the
200 form: <user>[:<password>] or <login-path>. E.g. rpl:passwd Default
201 = None.
202 · l --skip-gtid
204 Skip creation and execution of GTID statements during the copy
206 operation.
207 · --all
209 Copy all of the databases on the server.
211 · --skip=<objects>
213 Specify objects to skip in the operation as a comma-separated list
215 (no spaces). Permitted values are CREATE_DB, DATA, EVENTS,
216 FUNCTIONS, GRANTS, PROCEDURES, TABLES, TRIGGERS, and VIEWS.
217 · --source=<source>
219 Connection information for the source server.
221 To connect to a server, it is necessary to specify connection
223 parameters such as user name, host name, password, and either a
224 port or socket. MySQL Utilities provides a number of ways to
225 provide this information. All of the methods require specifying
226 your choice via a command-line option such as --server, --master,
227 --slave, etc. The methods include the following in order of most
228 secure to least secure.
229 · Use login-paths from your .mylogin.cnf file (encrypted, not
231 visible). Example : <login-path>[:<port>][:<socket>]
232 · Use a configuration file (unencrypted, not visible) Note:
234 available in release-1.5.0. Example :
235 <configuration-file-path>[:<section>]
236 · Specify the data on the command-line (unencrypted, visible).
238 Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]
239 · --ssl-ca
242 The path to a file that contains a list of trusted SSL CAs.
244 · --ssl-cert
246 The name of the SSL certificate file to use for establishing a
248 secure connection.
249 · --ssl-cert
251 The name of the SSL key file to use for establishing a secure
253 connection.
254 · --ssl
256 Specifies if the server connection requires use of SSL. If an
258 encrypted connection cannot be established, the connection attempt
259 fails. Default setting is 0 (SSL not required).
260 · --verbose, -v
262 Specify how much information to display. Use this option multiple
264 times to increase the amount of information. For example, -v =
265 verbose, -vv = more verbose, -vvv = debug.
266 · --version
268 Display version information and exit.
270 NOTES.PP You must provide connection parameters (user, host, password,
271 and so forth) for an account that has the appropriate privileges to
272 access all objects in the operation.
273 On the source to copy all objects from the database, the user must have
275 these privileges: SELECT for tables, SHOW VIEW for views, EVENT for
276 events and TRIGGER for triggers. Additionally, the SELECT privilege is
277 also required for the mysql database.
278 On the destination to copy all objects, the user must have these
280 privileges: CREATE, ALTER, SELECT, INSERT, UPDATE, LOCK TABLES, DROP if
281 --drop-first option is used, SUPER when binary logging is enabled,
282 CREATE VIEW for views, CREATE ROUTINE, EXECUTE for procedures and
283 functions, EVENT for events, TRIGGER for triggers and GRANT OPTION to
284 copy grants. The SUPER privilege might also be required for some
285 objects (views, procedures, functions, events and triggers), depending
286 on their DEFINER value.
287 Actual privileges required may differ from installation to installation
289 depending on the security privileges present and whether the database
290 contains certain objects such as views or events and whether binary
291 logging is enabled.
292 The --new-storage-engine and --default-storage-engine options apply to
294 all destination tables in the operation.
295 Some option combinations may result in errors during the operation. For
297 example, eliminating tables but not views may result in an error a the
298 view is copied.
299 The --rpl option is not valid for copying databases on the same server.
301 An error will be generated.
302 When copying data and including the GTID commands, you may encounter an
304 error similar to "GTID_PURGED can only be set when GTID_EXECUTED is
305 empty". This occurs because the destination server is not in a clean
306 replication state. To aleviate this problem, you can issue a "RESET
307 MASTER" command on the destination prior to executing the copy.
308 Cloning databases that contain foreign key constraints does not change
310 the constraint in the cloned table. For example, if table db1.t1 has a
311 foreign key constraint on table db1.t2, when db1 is cloned to db2,
312 table db2.t1 will have a foreign key constraint on db1.t2.
313 The path to the MySQL client tools should be included in the PATH
315 environment variable in order to use the authentication mechanism with
316 login-paths. This will allow the utility to use the my_print_defaults
317 tools which is required to read the login-path values from the login
318 configuration file (.mylogin.cnf).
319 If any database identifier specified as an argument contains special
321 characters or is a reserved word, then it must be appropriately quoted
322 with backticks (`). In turn, names quoted with backticks must also be
323 quoted with single or double quotes depending on the operating system,
324 i.e. (") in Windows or (') in non-Windows systems, in order for the
325 utilities to read backtick quoted identifiers as a single argument. For
326 example, to copy a database with the name weird`db.name with
327 other:weird`db.name, the database pair must be specified using the
328 following syntax (in non-Windows):
329 '`weird``db.name`:`other:weird``db.name`'.
330 Keep in mind that you can only take advantage of multiprocessing if
332 your system has multiple CPUs available for concurrent execution. Also
333 note that multiprocessing is applied at a different level according to
334 the operating system where the mysqldbcopy utility is executed (due to
335 python limitations). In particular, it is applied at the database level
336 for Windows (i.e., different databases are concurrently copied) and at
337 the table level for Non-Windows (POSIX) systems (i.e., different tables
338 within the same database are concurrently copied). EXAMPLES.PP The
339 following example demonstrates how to use the utility to copy a
340 database named util_test to a new database named util_test_copy on the
341 same server:
342 shell> mysqldbcopy \
344 --source=root:pass@localhost:3310:/test123/mysql.sock \
345 --destination=root:pass@localhost:3310:/test123/mysql.sock \
346 util_test:util_test_copy
347 # Source on localhost: ... connected.
348 # Destination on localhost: ... connected.
349 # Copying database util_test renamed as util_test_copy
350 # Copying TABLE util_test.t1
351 # Copying table data.
352 # Copying TABLE util_test.t2
353 # Copying table data.
354 # Copying TABLE util_test.t3
355 # Copying table data.
356 # Copying TABLE util_test.t4
357 # Copying table data.
358 # Copying VIEW util_test.v1
359 # Copying TRIGGER util_test.trg
360 # Copying PROCEDURE util_test.p1
361 # Copying FUNCTION util_test.f1
362 # Copying EVENT util_test.e1
363 # Copying GRANTS from util_test
364 #...done.
365 If the database to be copied does not contain only InnoDB tables and
367 you want to ensure data integrity of the copied data by locking the
368 tables during the read step, add a --locking=lock-all option to the
369 command:
370 shell> mysqldbcopy \
372 --source=root:pass@localhost:3310:/test123/mysql.sock \
373 --destination=root:pass@localhost:3310:/test123/mysql.sock \
374 util_test:util_test_copy --locking=lock-all
375 # Source on localhost: ... connected.
376 # Destination on localhost: ... connected.
377 # Copying database util_test renamed as util_test_copy
378 # Copying TABLE util_test.t1
379 # Copying table data.
380 # Copying TABLE util_test.t2
381 # Copying table data.
382 # Copying TABLE util_test.t3
383 # Copying table data.
384 # Copying TABLE util_test.t4
385 # Copying table data.
386 # Copying VIEW util_test.v1
387 # Copying TRIGGER util_test.trg
388 # Copying PROCEDURE util_test.p1
389 # Copying FUNCTION util_test.f1
390 # Copying EVENT util_test.e1
391 # Copying GRANTS from util_test
392 #...done.
393 To copy one or more databases from a master to a slave, you can use the
395 following command to copy the databases. Use the master as the source
396 and the slave as the destination:
397 shell> mysqldbcopy --source=root@localhost:3310 \
399 --destination=root@localhost:3311 test123 --rpl=master \
400 --rpl-user=rpl
401 # Source on localhost: ... connected.
402 # Destination on localhost: ... connected.
403 # Source on localhost: ... connected.
404 # Stopping slave
405 # Copying database test123
406 # Copying TABLE test123.t1
407 # Copying data for TABLE test123.t1
408 # Connecting to the current server as master
409 # Starting slave
410 #...done.
411 To copy a database from one slave to another attached to the same
413 master, you can use the following command using the slave with the
414 database to be copied as the source and the slave where the database
415 needs to copied to as the destination:
416 shell> mysqldbcopy --source=root@localhost:3311 \
418 --destination=root@localhost:3312 test123 --rpl=slave \
419 --rpl-user=rpl
420 # Source on localhost: ... connected.
421 # Destination on localhost: ... connected.
422 # Source on localhost: ... connected.
423 # Stopping slave
424 # Copying database test123
425 # Copying TABLE test123.t1
426 # Copying data for TABLE test123.t1
427 # Connecting to the current server's master
428 # Starting slave
429 #...done.
430 PERMISSIONS REQUIRED.PP The user must have SELECT, SHOW VIEW, EVENT and
432 TRIGGER privileges for the database(s) on the source server. On the
433 destination server, the user must have the following privileges for the
434 copied database(s): CREATE, ALTER, SELECT, INSERT, UPDATE, LOCK TABLES,
435 DROP if --drop-first option is used, and SUPER depending on the objects
436 DEFINER value.
437
439 Copyright © 2006, 2015, Oracle and/or its affiliates. All rights
440 reserved.
441 This documentation is free software; you can redistribute it and/or
443 modify it only under the terms of the GNU General Public License as
444 published by the Free Software Foundation; version 2 of the License.
445 This documentation is distributed in the hope that it will be useful,
447 but WITHOUT ANY WARRANTY; without even the implied warranty of
448 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
449 General Public License for more details.
450 You should have received a copy of the GNU General Public License along
452 with the program; if not, write to the Free Software Foundation, Inc.,
453 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA or see
454 http://www.gnu.org/licenses/.
455
458 For more information, please refer to the MySQL Utilities and Fabric
459 documentation, which is available online at
460 http://dev.mysql.com/doc/index-utils-fabric.html
461
463 Oracle Corporation (http://dev.mysql.com/).
464MySQL 1.5.6 09/15/2015 MYSQLDBCOPY(1)