1MYSQLDBCOPY(1) MySQL Utilities MYSQLDBCOPY(1)
2
6 mysqldbcopy - Copy databases from one MySQL server to another
7
9 mysqldbcopy --source=<user>[<passwd>]@<host>:[<port>][:<socket>]
10 --destination=<user>[<passwd>]@<host>:[<port>][:<socket>]
11 (<db_name>[:<new_name>])+ [--verbose | --quiet |
12 --skip=(TABLES,TRIGGERS,VIEWS,PROCEDURES,FUNCTIONS,
13 EVENTS,GRANTS,DATA,CREATE_DB)* | --help | --version |
14 --new-storage-engine=<engine> | --default-storage-engine=<engine> |
15 --threads=<num threads>] | --exclude=<name>[|,--exclude=<name>]
16
18 This utility permits a database administrator to copy a database from
19 one server (source) either to another server (destinaton) as the same
20 name or a different name or to the same server (destination) as the
21 same or as a different name (clone).
22 The operation copies all objects (tables, views, triggers, events, pro‐
24 cedures, functions, and database-level grants) to the destination
25 server. The utility will also copy all data. There are options to turn
26 off copying any or all of the objects as well as not copying the data.
27 You can exclude specific objects by name using the --exclude option
29 whereby you specify a name in the form of <db>.<object> or you can sup‐
30 ply a regex search pattern. For example, --exclude=db1.trig1 will
31 exclude the single trigger and --exclude=trig_ will exclude all objects
32 from all databases whose name begins with trig and has a following
33 character or digit.
34 To change the storage engine for all tables on the destination, specify
36 the new engine with the --new-storage-engine option. If the new engine
37 specified is available on the destination, all tables will be changed
38 to use the engine.
39 Similarly, you can specify a different default storage engine with the
41 --default-storage-engine option. If the engine specified is available
42 on the destination, any table that specifies a storage engine that is
43 not on the destination will use the new default engine. Note that this
44 overrides the default storage engine mechanism on the server.
45 If the option --default-storage-engine or --new-storage-engine is sup‐
47 plied and the storage engine specified does not exist, a warning shall
48 be issued and the default storage engine setting on the server shall be
49 used instead.
50 You must provide login information such as user, host, password, etc.
52 for a user that has the appropriate rights to access all objects in the
53 operation. See mysqldb-notes below for more details.
54
56 The following command line options are accepted by mysqldbcopy:
57 --version
59 show version number and exit
60 --help show the help page
62 --source <source>
64 connection information for source server in the form:
66 <user>:<password>@<host>:<port>:<socket> where <password> is
67 optional and either <port> or <socket> must be provided.
68 --destination <destination>
70 connection information for destination server in the form:
72 <user>:<password>@<host>:<port>:<socket> Where <password> is
73 optional and either <port> or <socket> must be provided.
74 --copy-dir <copy directory>
76 a path to use when copying data (stores temporary files) -
78 default = current directory
79 --skip <objects>
81 specify objects to skip in the operation in the form of a
83 comma-separated list (no spaces). Valid values = TABLES, VIEWS,
84 TRIGGERS, PROCEDURES, FUNCTIONS, EVENTS, GRANTS, DATA, CREATE_DB
85 --exclude=EXCLUDE, -x EXCLUDE
87 exclude one or more objects from the operation using either a
89 specific name such as db1.t1 or a REGEXP search pattern. Repeat
90 option for multiple exclusions.
91 --force, -f
93 drop the new database or object if it exists
95 --quiet, -q
97 turn off all messages for quiet execution
99 --verbose, -v
101 control how much information is displayed. For example, -v =
103 verbose, -vv = more verbose, -vvv = debug
104 --threads
106 use multiple threads for cross-server copy (default = 1)
107 --new-storage-engine=NEW_ENGINE
109 Change all tables to use this storage engine if storage engine
111 exists on the destination.
112 --default-storage-engine=DEF_ENGINE
114 Change all tables to use this storage engine if the original
116 storage engine does not exist on the destination.
117
119 The login user must have the appropriate permissions to create new
120 objects, read the old database, access (read) the mysql database, and
121 grant privileges.
122 To copy all objects from a source, the user must have SELECT and SHOW
124 VIEW privileges on the database as well as SELECT on the mysql data‐
125 base.
126 To copy all objects to a destination, the user must have CREATE for the
128 database as well as SUPER for procedures and functions (when binary
129 logging is enabled) and WITH GRANT OPTION to copy grants.
130 Actual privileges needed may differ from installation to installation
132 depending on the security privileges present and whether the database
133 contains certain objects such as views or events and whether binary
134 logging is turned on (hence the need for SUPER).
135 The --new-storage-engine and --default-storage-engine options apply to
137 all tables in the operation.
138 Some combinations of the options may result in errors during the opera‐
140 tion. For example, eliminating tables but not views may result in an
141 error when the view is copied.
142 The --exclude option does not apply to grants.
144
146 The following example demonstrates how to use the utility to copy a
147 database named 'util_test' to a new name 'util_test_copy' on the same
148 server.:
149 $ mysqldbcopy.py \\
151 --source=root:pass@localhost:3310:/test123/mysql.sock \\
152 --destination=root:pass@localhost:3310:/test123/mysql.sock \\
153 util_test:util_test_copy
154 # Source on localhost: ... connected.
155 # Destination on localhost: ... connected.
156 # Copying database util_test renamed as util_test_copy
157 # Copying TABLE util_test.t1
158 # Copying table data.
159 # Copying TABLE util_test.t2
160 # Copying table data.
161 # Copying TABLE util_test.t3
162 # Copying table data.
163 # Copying TABLE util_test.t4
164 # Copying table data.
165 # Copying VIEW util_test.v1
166 # Copying TRIGGER util_test.trg
167 # Copying PROCEDURE util_test.p1
168 # Copying FUNCTION util_test.f1
169 # Copying EVENT util_test.e1
170 # Copying GRANTS from util_test
171 #...done.
172
174 Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
175 This program is free software; you can redistribute it and/or modify it
177 under the terms of the GNU General Public License as published by the
178 Free Software Foundation; version 2 of the License.
179 This program is distributed in the hope that it will be useful, but
181 WITHOUT ANY WARRANTY; without even the implied warranty of MER‐
182 CHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
183 Public License for more details.
184 You should have received a copy of the GNU General Public License along
186 with this program; if not, write to the Free Software Foundation, Inc.,
187 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
188
190 MySQL Utilities Team
191
193 2010, Oracle and/or its affiliates. All rights reserved.
1941.0.1 September 23, 2011 MYSQLDBCOPY(1)