'\" t
.\" Title: \fBmysqldbcopy\fR
.\" Author: [FIXME: author] [see http://docbook.sf.net/el/author]
.\" Generator: DocBook XSL Stylesheets v1.79.1
.\" Date: 08/01/2016
.\" Manual: MySQL Utilities
.\" Source: MySQL 1.6.3
.\" Language: English
.\"
.TH "\FBMYSQLDBCOPY\FR" "1" "08/01/2016" "MySQL 1\&.6\&.3" "MySQL Utilities"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
mysqldbcopy \- Copy Database Objects Between Servers
.SH "SYNOPSIS"
.HP \w'\fBmysqldbcopy\ [\fR\fB\fIoptions\fR\fR\fB]\ \fR\fB\fIdb_name\fR\fR\fB\ [\fR\fB\fI:new_db_name\fR\fR\fB]\fR\ 'u
\fBmysqldbcopy [\fR\fB\fIoptions\fR\fR\fB] \fR\fB\fIdb_name\fR\fR\fB [\fR\fB\fI:new_db_name\fR\fR\fB]\fR
.SH "DESCRIPTION"
.PP
This utility copies a database on a source server to a database on a destination server\&. If the source and destination servers are different, the database names can be the same or different\&. If the source and destination servers are the same, the database names must be different\&.
.PP
The utility accepts one or more database pairs on the command line\&. To name a database pair, use
\fIdb_name\fR:\fInew_db_name\fR
syntax to specify the source and destination names explicitly\&. If the source and destination database names are the same,
\fIdb_name\fR
can be used as shorthand for
\fIdb_name\fR:\fIdb_name\fR\&.
.PP
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\&.
.PP
To exclude specific objects by name, use the
\fB\-\-exclude\fR
option with a name in
\fIdb\fR\&.*obj* format, or you can supply a search pattern\&. For example,
\fB\-\-exclude=db1\&.trig1\fR
excludes the single trigger and
\fB\-\-exclude=trig_\fR
excludes all objects from all databases having a name that begins with
trig
and has a following character\&.
.PP
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
\fB\-\-new\-storage\-engine\fR
option\&. If the destination server supports the new engine, all tables use that engine\&.
.PP
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
\fB\-\-default\-storage\-engine\fR
option\&.
.PP
The
\fB\-\-new\-storage\-engine\fR
option takes precedence over
\fB\-\-default\-storage\-engine\fR
if both are given\&.
.PP
If the
\fB\-\-new\-storage\-engine\fR
or
\fB\-\-default\-storage\-engine\fR
option is given and the destination server does not support the specified storage engine, a warning is issued and the server\*(Aqs default storage engine setting is used instead\&.
.PP
By default, the operation uses a consistent snapshot to read the source databases\&. To change the locking mode, use the
\fB\-\-locking\fR
option with a locking type value\&. Use a value of
\fBno\-locks\fR
to turn off locking altogether or
\fBlock\-all\fR
to use only table locks\&. The default value is
\fBsnapshot\fR\&. Additionally, the utility uses WRITE locks to lock the destination tables during the copy\&.
.PP
You can include replication statements for copying data among a master and slave or between slaves\&. The
\fB\-\-rpl\fR
option permits you to select from the following replication statements to include in the export\&.
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBmaster\fR
.sp
Create and execute a
\fBCHANGE MASTER\fR
statement to make the destination server a slave of the server specified in the
\fB\-\-source\fR
option\&. This executes the appropriate STOP and START slave statements\&. The
\fBSTOP SLAVE\fR
statement is executed at the start of the copy and the
\fBCHANGE MASTER\fR
followed by the
\fBSTART SLAVE\fR
statements are executed after the copy\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\fBslave\fR
.sp
Create and execute a
\fBCHANGE MASTER\fR
statement to make the destination server a slave connected to the same master as the server specified in the
\fB\-\-source\fR
option\&. This executes the appropriate STOP and START slave statements\&. The STOP SLAVE statement is executed at the start of the copy and the
\fBCHANGE MASTER\fR
followed by the
\fBSTART SLAVE\fR
statements after the copy\&.
.RE
.PP
To include the replication user in the
\fBCHANGE MASTER\fR
statement, use the
\fB\-\-rpl\-user\fR
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\&.
.PP
If you attempt to copy databases on a server with GTIDs enabled (GTID_MODE = ON), a warning will be generated if the copy does not include all databases\&. This is because the GTID statements generated include the GTIDs for all databases and not only those databases in the export\&.
.PP
The utility will also generate a warning if you copy databases on a GTID enabled server but use the
\fB\-\-skip\-gtid \fR
option\&.
.PP
To make the most use of GTIDs, you should copy all of the databases on the server with the
\fB\-\-all\fR
option\&.
OPTIONS.PP
\fBmysqldbcopy\fR
accepts the following command\-line options:
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-help
.sp
Display a help message and exit\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-license
.sp
Display license information and exit\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-character\-set=
.sp
Sets the client character set\&. The default is retrieved from the server variable
character_set_client\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-default\-storage\-engine=
.sp
The engine to use for tables if the destination server does not support the original storage engine on the source server\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-destination=
.sp
Connection information for the destination server\&.
.sp
To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket\&. MySQL Utilities provides a number of ways to supply this information\&. All of the methods require specifying your choice via a command\-line option such as \-\-server, \-\-master, \-\-slave, etc\&. The methods include the following in order of most secure to least secure\&.
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use login\-paths from your
\&.mylogin\&.cnf
file (encrypted, not visible)\&. Example : <\fIlogin\-path\fR>[:<\fIport\fR>][:<\fIsocket\fR>]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Use a configuration file (unencrypted, not visible) Note: available in release\-1\&.5\&.0\&. Example : <\fIconfiguration\-file\-path\fR>[:<\fIsection\fR>]
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
Specify the data on the command\-line (unencrypted, visible)\&. Example : <\fIuser\fR>[:<\fIpasswd\fR>]@<\fIhost\fR>[:<\fIport\fR>][:<\fIsocket\fR>]
.RE
.sp
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-exclude=, \-x
.sp
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
\fBLIKE\fR
matching\&. With the
\fB\-\-regexp\fR
option, patterns use
\fBREGEXP\fR
matching\&.
.sp
This option does not apply to grants\&.
.if n \{\
.sp
.\}
.RS 4
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.ps +1
\fBNote\fR
.ps -1
.br
The utility will attempt to determine if the pattern supplied has any special characters (such as an asterisks), which may indicate that the pattern could be a REGEXP pattern\&. If there are special, non\-SQL LIKE pattern characters and the user has not specified the
\fB\-\-regexp\fR
option, a warning is presented to suggest the user check the pattern for possible use with the
\fB\-\-regexp\fR
option\&.
.sp .5v
.RE
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-drop\-first
.sp
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\&.
.if n \{\
.sp
.\}
.RS 4
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.ps +1
\fBNote\fR
.ps -1
.br
Before MySQL Utilities 1\&.4\&.2, this option was named
\-\-force\&.
.sp .5v
.RE
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-locking=
.sp
Choose the lock type for the operation\&. Permitted lock values are
\fBno\-locks\fR
(do not use any table locks),
\fBlock\-all\fR
(use table locks but no transaction and no consistent read), and
\fBsnapshot\fR
(consistent read using a single transaction)\&. The default is
\fBsnapshot\fR\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-multiprocess
.sp
Specify the number of processes to concurrently copy the specified databases\&. Special values: 0 (number of processes equal to the number of detected CPUs) and 1 (default \- no concurrency)\&. Multiprocessing works at the database level for Windows and at the table level for Non\-Windows (POSIX) systems\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-new\-storage\-engine=
.sp
The engine to use for all tables created on the destination server\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-quiet, \-q
.sp
Turn off all messages for quiet execution\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-regexp, \-\-basic\-regexp, \-G
.sp
Perform pattern matches using the
\fBREGEXP\fR
operator\&. The default is to use
\fBLIKE\fR
for matching\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-rpl=, \-\-replication=
.sp
Include replication information\&. Permitted values are
\fBmaster\fR
(make destination a slave of the source server) and
\fBslave\fR
(make destination a slave of the same master as the source \- only works if the source server is a slave)\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-rpl\-user=
.sp
The user and password for the replication user requirement in the form: <\fIuser\fR>[:<\fIpassword\fR>] or <\fIlogin\-path\fR>\&. E\&.g\&. rpl:passwd Default = None\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
l \-\-skip\-gtid
.sp
Skip creation and execution of GTID statements during the copy operation\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-all
.sp
Copy all of the databases on the server\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-skip=
.sp
Specify objects to skip in the operation as a comma\-separated list (no spaces)\&. Permitted values are
\fBCREATE_DB\fR,
\fBDATA\fR,
\fBEVENTS\fR,
\fBFUNCTIONS\fR,
\fBGRANTS\fR,
\fBPROCEDURES\fR,
\fBTABLES\fR,
\fBTRIGGERS\fR, and
\fBVIEWS\fR\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04'\(bu\h'+03'\c
.\}
.el \{\
.sp -1
.IP \(bu 2.3
.\}
\-\-source=