MYSQLDBCOPY(1)							  MySQL Utilities						    MYSQLDBCOPY(1)

NAME

       mysqldbcopy - Copy databases from one MySQL server to another

SYNOPSIS

       mysqldbcopy [options] db_name[:new_db_name]

DESCRIPTION

       This  utility copies a database on a source server to a database on a destination server. If the source and destination servers are differ-
       ent, the database names can be the same or different. If the source and destination servers are the same, the database names must  be  dif-
       ferent.

       The  utility accepts one or more database pairs on the command line. To name a database pair, use db_name:new_db_name syntax to specify the
       source and destination names explicitly. If the source and destination database names are the same, db_name can be used	as  shorthand  for
       db_name:db_name.

       By  default,  the operation copies all objects (tables, views, triggers, events, procedures, functions, and database-level grants) and data
       to the destination server.  There are options to turn off copying any or all of the objects as well as not copying the data.

       To exclude specific objects by name, use the --exclude option with a name in db.*obj* format, or you can supply a search pattern. For exam-
       ple,  --exclude=db1.trig1 excludes the single trigger and --exclude=trig_ excludes all objects from all databases having a name that begins
       with trig and has a following character.

       By default, the utility creates each table on the destination server using the same storage engine as the original table.  To override this
       and  specify the storage engine to use for all tables created on the destination server, use the --new-storage-engine option. If the desti-
       nation server supports the new engine, all tables use that engine.

       To specify the storage engine to use for tables for which the destination server does not support the original storage engine on the source
       server, use the --default-storage-engine option.

       The --new-storage-engine option takes precedence over --default-storage-engine if both are given.

       If  the	--new-storage-engine or --default-storage-engine option is given and the destination server does not support the specified storage
       engine, a warning is issued and the server's default storage engine setting is used instead.

       By default, the operation uses a consistent snapshot to read the source databases. To change the locking mode,  use  the  --locking  option
       with  a	locking type value.  Use a value of no-locks to turn off locking altogether or lock-all to use only table locks. The default value
       is snapshot. Additionally, the utility uses WRITE locks to lock the destination tables during the copy.

       You can include replication statements for copying data among a master and slave or between slaves. The --rpl option permits you to  select
       from the following replication statements to include in the export.

       master Include the CHANGE MASTER statement to start a new slave with the current server acting as the master. This executes the appropriate
	      STOP and START slave statements. The STOP SLAVE statement is executed at the start of the copy and the CHANGE MASTER followed by the
	      START SLAVE statements are executed after the copy.

       slave  Include  the CHANGE MASTER statement to start a new slave using the current server's master information. This executes the appropri-
	      ate STOP and START slave statements. The STOP SLAVE statement is executed at the start of the copy and the CHANGE MASTER followed by
	      the START SLAVE statements follow the copy.

       To  include the replication user in the CHANGE MASTER statement, use the --rpl-user option to specify the user and password. If this option
       is omitted, the utility attempts to identify the replication user. In the event that there are multiple candidates or the user  requires  a
       password, the utility aborts with an error.

OPTIONS

       mysqldbcopy accepts the following command-line options:

       --help Display a help message and exit.

       --default-storage-engine=<def_engine>
	      The engine to use for tables if the destination server does not support the original storage engine on the source server.

       --destination=<destination>
	      Connection information for the destination server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format, where <passwd> is optional
	      and either <port> or <socket> must be provided.

       --exclude=<exclude>, -x<exclude>
	      Exclude one or more objects from the operation using either a specific name such as db1.t1 or a search  pattern.	 Use  this  option
	      multiple	times to specify multiple exclusions. By default, patterns use LIKE matching.  With the --regexp option, patterns use REG-
	      EXP matching.

	      This option does not apply to grants.

       --force
	      Drop each database to be copied if exists before copying anything into it. Without this option, an error occurs if  you  attempt	to
	      copy objects into an existing database.

       --locking=<locking>
	      Choose  the  lock type for the operation. Permitted lock values are no-locks (do not use any table locks), lock-all (use table locks
	      but no transaction and no consistent read), and snaphot (consistent read using a single transaction). The default is snapshot.

       --new-storage-engine=<new_engine>
	      The engine to use for all tables created on the destination server.

       --quiet, -q
	      Turn off all messages for quiet execution.

       --regexp, --basic-regexp, -G
	      Perform pattern matches using the REGEXP operator. The default is to use LIKE for matching.

       --rpl=<dump_option>, --replication=<dump_option>
	      Include replication information. Permitted values are master (include the CHANGE MASTER statement using the  source  server  as  the
	      master), slave (include the CHANGE MASTER statement using the destination server's master information), and both (include the master
	      and slave options where applicable).

       --rpl-user=<user[:password]>
	      The user and password for the replication user requirement - e.g. rpl:passwd - default = rpl:rpl.

       --skip=<objects>
	      Specify objects to skip in the operation as a comma-separated list (no spaces). Permitted values are CREATE_DB, DATA, EVENTS,  FUNC-
	      TIONS, GRANTS, PROCEDURES, TABLES, TRIGGERS, and VIEWS.

       --source=<source>
	      Connection  information for the source server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format, where <passwd> is optional and
	      either <port> or <socket> must be provided.

       --threads
	      Use multiple threads for cross-server copy. The default is 1.

       --verbose, -v
	      Specify how much information to display. Use this option multiple times to increase the amount of information.  For  example,  -v  =
	      verbose, -vv = more verbose, -vvv = debug.

       --version
	      Display version information and exit.

NOTES

       You  must  provide  connection parameters (user, host, password, and so forth) for an account that has the appropriate privileges to access
       all objects in the operation.

       To copy all objects from a source, the user must have these privileges: SELECT and SHOW VIEW for the database, and  SELECT  for	the  mysql
       database.

       To  copy all objects to a destination, the user must have these privileges: CREATE for the database, SUPER (when binary logging is enabled)
       for procedures and functions, and GRANT OPTION to copy grants.

       Actual privileges required may differ from installation to installation depending on the security privileges present and whether the  data-
       base contains certain objects such as views or events and whether binary logging is enabled.

       The --new-storage-engine and --default-storage-engine options apply to all destination tables in the operation.

       Some option combinations may result in errors during the operation.  For example, eliminating tables but not views may result in an error a
       the view is copied.

       The --rpl option is not valid for copying databases on the same server. An error will be generated.

EXAMPLES

       The following example demonstrates how to use the utility to copy a database named util_test to a new database named util_test_copy on  the
       same server:

       $ mysqldbcopy 
	 --source=root:pass@localhost:3310:/test123/mysql.sock 
	 --destination=root:pass@localhost:3310:/test123/mysql.sock 
	 util_test:util_test_copy
       # Source on localhost: ... connected.
       # Destination on localhost: ... connected.
       # Copying database util_test renamed as util_test_copy
       # Copying TABLE util_test.t1
       # Copying table data.
       # Copying TABLE util_test.t2
       # Copying table data.
       # Copying TABLE util_test.t3
       # Copying table data.
       # Copying TABLE util_test.t4
       # Copying table data.
       # Copying VIEW util_test.v1
       # Copying TRIGGER util_test.trg
       # Copying PROCEDURE util_test.p1
       # Copying FUNCTION util_test.f1
       # Copying EVENT util_test.e1
       # Copying GRANTS from util_test
       #...done.

       If  the	database  to be copied does not contain only InnoDB tables and you want to ensure data integrity of the copied data by locking the
       tables during the read step, add a --locking=lock-all option to the command:

       $ mysqldbcopy 
	 --source=root:pass@localhost:3310:/test123/mysql.sock 
	 --destination=root:pass@localhost:3310:/test123/mysql.sock 
	 util_test:util_test_copy --locking=lock-all
       # Source on localhost: ... connected.
       # Destination on localhost: ... connected.
       # Copying database util_test renamed as util_test_copy
       # Copying TABLE util_test.t1
       # Copying table data.
       # Copying TABLE util_test.t2
       # Copying table data.
       # Copying TABLE util_test.t3
       # Copying table data.
       # Copying TABLE util_test.t4
       # Copying table data.
       # Copying VIEW util_test.v1
       # Copying TRIGGER util_test.trg
       # Copying PROCEDURE util_test.p1
       # Copying FUNCTION util_test.f1
       # Copying EVENT util_test.e1
       # Copying GRANTS from util_test
       #...done.

       To copy one or more databases from a master to a slave, you can use the following command to copy the databases.  Use  the  master  as  the
       source and the slave as the destination:

       $ mysqldbcopy --source=root@localhost:3310 
	 --destination=root@localhost:3311 test123 --rpl=master 
	 --rpl-user=rpl
       # Source on localhost: ... connected.
       # Destination on localhost: ... connected.
       # Source on localhost: ... connected.
       # Stopping slave
       # Copying database test123
       # Copying TABLE test123.t1
       # Copying data for TABLE test123.t1
       # Connecting to the current server as master
       # Starting slave
       #...done.

       To  copy a database from one slave to another attached to the same master, you can use the following command using the slave with the data-
       base to be copied as the source and the slave where the database needs to copied to as the destination:

       $ mysqldbcopy --source=root@localhost:3311 
	 --destination=root@localhost:3312 test123 --rpl=slave 
	 --rpl-user=rpl
       # Source on localhost: ... connected.
       # Destination on localhost: ... connected.
       # Source on localhost: ... connected.
       # Stopping slave
       # Copying database test123
       # Copying TABLE test123.t1
       # Copying data for TABLE test123.t1
       # Connecting to the current server's master
       # Starting slave
       #...done.

COPYRIGHT

       Copyright (c) 2010, 2012, Oracle and/or its affiliates. All rights reserved.

       This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as  published	by
       the Free Software Foundation; version 2 of the License.

       This  program  is  distributed  in  the	hope  that  it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MER-
       CHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more details.

       You should have received a copy of the GNU General Public License along with this program; if not, write to the Free  Software  Foundation,
       Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

AUTHOR

       MySQL Utilities Team

COPYRIGHT

       2010, Oracle and/or its affiliates. All rights reserved.

1.0.3								   May 09, 2012 						    MYSQLDBCOPY(1)