Provided by: mysql-utilities_1.6.4-1build1_all bug

NAME

       mysqldbcopy - Copy Database Objects Between Servers

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 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.

       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 example, --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 destination 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

           Create and execute a CHANGE MASTER statement to make the destination server a slave of
           the server specified in the --source option. 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

           Create and execute a CHANGE MASTER statement to make the destination server a slave
           connected to the same master as the server specified in the --source option. 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 after 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.

       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.

       The utility will also generate a warning if you copy databases on a GTID enabled server
       but use the --skip-gtid option.

       To make the most use of GTIDs, you should copy all of the databases on the server with the
       --all option.  OPTIONS.PP mysqldbcopy accepts the following command-line options:

       ·   --help

           Display a help message and exit.

       ·   --license

           Display license information and exit.

       ·   --character-set=<charset>

           Sets the client character set. The default is retrieved from the server variable
           character_set_client.

       ·   --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.

           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.

           ·   Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example :
               <login-path>[:<port>][:<socket>]

           ·   Use a configuration file (unencrypted, not visible) Note: available in
               release-1.5.0. Example : <configuration-file-path>[:<section>]

           ·   Specify the data on the command-line (unencrypted, visible). Example :
               <user>[:<passwd>]@<host>[:<port>][:<socket>]

       ·   --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 REGEXP matching.

           This option does not apply to grants.

               Note
               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 --regexp option, a warning is presented to suggest the user
               check the pattern for possible use with the --regexp option.

       ·   --drop-first

           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.

               Note
               Before MySQL Utilities 1.4.2, this option was named --force.

       ·   --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 snapshot (consistent read using a single transaction). The default is
           snapshot.

       ·   --multiprocess

           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.

       ·   --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 (make destination a slave
           of the source server) and slave (make destination a slave of the same master as the
           source - only works if the source server is a slave).

       ·   --rpl-user=<replication_user>

           The user and password for the replication user requirement in the form:
           <user>[:<password>] or <login-path>. E.g. rpl:passwd Default = None.

       ·   l --skip-gtid

           Skip creation and execution of GTID statements during the copy operation.

       ·   --all

           Copy all of the databases on the server.

       ·   --skip=<objects>

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

       ·   --source=<source>

           Connection information for the source server.

           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.

           ·   Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example :
               <login-path>[:<port>][:<socket>]

           ·   Use a configuration file (unencrypted, not visible) Note: available in
               release-1.5.0. Example : <configuration-file-path>[:<section>]

           ·   Specify the data on the command-line (unencrypted, visible). Example :
               <user>[:<passwd>]@<host>[:<port>][:<socket>]

       ·   --ssl-ca

           The path to a file that contains a list of trusted SSL CAs.

       ·   --ssl-cert

           The name of the SSL certificate file to use for establishing a secure connection.

       ·   --ssl-cert

           The name of the SSL key file to use for establishing a secure connection.

       ·   --ssl

           Specifies if the server connection requires use of SSL. If an encrypted connection
           cannot be established, the connection attempt fails. Default setting is 0 (SSL not
           required).

       ·   --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.PP 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.

       On the source to copy all objects from the database, the user must have these privileges:
       SELECT for tables, SHOW VIEW for views, EVENT for events and TRIGGER for triggers.
       Additionally, the SELECT privilege is also required for the mysql database.

       On the destination to copy all objects, the user must have these privileges: CREATE,
       ALTER, SELECT, INSERT, UPDATE, LOCK TABLES, DROP if --drop-first option is used, SUPER
       when binary logging is enabled, CREATE VIEW for views, CREATE ROUTINE, EXECUTE for
       procedures and functions, EVENT for events, TRIGGER for triggers and GRANT OPTION to copy
       grants. The SUPER privilege might also be required for some objects (views, procedures,
       functions, events and triggers), depending on their DEFINER value.

       Actual privileges required may differ from installation to installation depending on the
       security privileges present and whether the database 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.

       When copying data and including the GTID commands, you may encounter an error similar to
       "GTID_PURGED can only be set when GTID_EXECUTED is empty". This occurs because the
       destination server is not in a clean replication state. To alleviate this problem, you can
       issue a "RESET MASTER" command on the destination prior to executing the copy.

       Cloning databases that contain foreign key constraints does not change the constraint in
       the cloned table. For example, if table db1.t1 has a foreign key constraint on table
       db1.t2, when db1 is cloned to db2, table db2.t1 will have a foreign key constraint on
       db1.t2.

       The path to the MySQL client tools should be included in the PATH environment variable in
       order to use the authentication mechanism with login-paths. This will allow the utility to
       use the my_print_defaults tools which is required to read the login-path values from the
       login configuration file (.mylogin.cnf).

       If any database identifier specified as an argument contains special characters or is a
       reserved word, then it must be appropriately quoted with backticks (`). In turn, names
       quoted with backticks must also be quoted with single or double quotes depending on the
       operating system, i.e. (") in Windows or (') in non-Windows systems, in order for the
       utilities to read backtick quoted identifiers as a single argument. For example, to copy a
       database with the name weird`db.name with other:weird`db.name, the database pair must be
       specified using the following syntax (in non-Windows):
       '`weird``db.name`:`other:weird``db.name`'.

       Keep in mind that you can only take advantage of multiprocessing if your system has
       multiple CPUs available for concurrent execution. Also note that multiprocessing is
       applied at a different level according to the operating system where the mysqldbcopy
       utility is executed (due to python limitations). In particular, it is applied at the
       database level for Windows (i.e., different databases are concurrently copied) and at the
       table level for Non-Windows (POSIX) systems (i.e., different tables within the same
       database are concurrently copied).  EXAMPLES.PP 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:

           shell> 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:

           shell> 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:

           shell> 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 database to be copied as the source and the
       slave where the database needs to copied to as the destination:

           shell> 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.

       LIMITATIONS.PP When copying tables with blob fields, the copy operation will fail for any
       table with a blob field that is defined as NOT NULL. This is because the copy attempts to
       use a bulk insert technique to copy the data in two passes; one to copy the data without
       blob field data, and another to update the rows with the blob data. This has shown to be
       efficient for most use cases.

       However, if one or more tables have blob fields defined as NOT NULL, the two pass copy
       process will fail because the server will not permit inserting of null values for fields
       defined as NOT NULL on the first pass. Thus, the utility will check the tables in the copy
       for any blob fields defined as NOT NULL. If any are found, an error is thrown and the copy
       aborted.

       A workaround for this limitation is to alter the table(s) to remove the NOT NULL
       restriction on blob fields identified before the copy and restore the restriction after
       the copy. Similarly, any indexes that require NOT NULL on blob fields must be removed
       before the copy and recreated after the copy.  PERMISSIONS REQUIRED.PP The user must have
       SELECT, SHOW VIEW, EVENT and TRIGGER privileges for the database(s) on the source server.
       On the destination server, the user must have the following privileges for the copied
       database(s): CREATE, ALTER, SELECT, INSERT, UPDATE, LOCK TABLES, DROP if --drop-first
       option is used, and SUPER depending on the objects DEFINER value.

COPYRIGHT

       Copyright © 2006, 2016, Oracle and/or its affiliates. All rights reserved.

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

       This documentation is distributed in the hope that it will be useful, but WITHOUT ANY
       WARRANTY; without even the implied warranty of MERCHANTABILITY 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 the program;
       if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
       Boston, MA 02110-1301 USA or see http://www.gnu.org/licenses/.

SEE ALSO

       For more information, please refer to the MySQL Utilities and Fabric documentation, which
       is available online at http://dev.mysql.com/doc/index-utils-fabric.html

AUTHOR

       Oracle Corporation (http://dev.mysql.com/).