- 13.4 Manual Pages
- 13.4.1 MySQL Utilities Overview — Brief overview of command-line utilities
- 13.4.2 mysqlauditadmin — Allows users to perform maintenance action on the audit log
- 13.4.3 mysqlauditgrep — Allows users to search the current or an archived audit log
- 13.4.4 mysqldbcompare — Compare Two Databases and Identify Differences
- 13.4.5 mysqldbcopy — Copy Database Objects Between Servers
- 13.4.6 mysqldbexport — Export Object Definitions or Data from a Database
- 13.4.7 mysqldbimport — Import Object Definitions or Data into a Database
- 13.4.8 mysqldiff — Identify Differences Among Database Objects
- 13.4.9 mysqldiskusage — Show Database Disk Usage
- 13.4.10 mysqlfailover — Automatic replication health monitoring and failover
- 13.4.11 mysqlindexcheck — Identify Potentially Redundant Table Indexes
- 13.4.12 mysqlmetagrep — Search Database Object Definitions
- 13.4.13 mysqlprocgrep — Search Server Process Lists
- 13.4.14 mysqlreplicate — Set Up and Start Replication Between Two Servers
- 13.4.15 mysqlrpladmin — Administration utility for MySQL replication
- 13.4.16 mysqlrplcheck — Check Replication Prerequisites
- 13.4.17 mysqlrplshow — Show Slaves for Master Server
- 13.4.18 mysqlserverclone — Clone Existing Server to Create New Server
- 13.4.19 mysqlserverinfo — Display Common Diagnostic Information from a Server
- 13.4.20 mysqluc — Command line client for running MySQL Utilities
- 13.4.21 mysqluserclone — Clone Existing User to Create New User
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
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 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 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.
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
mysqldbcopy accepts the following command-line options:
--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
--regexpoption, patterns use REGEXP matching.This option does not apply to grants.
--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).
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 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 aleviate 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).
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 database 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.
User Comments
Add your own comment.