合 mysql-utilities工具之MySQL数据库拷贝工具mysqldbcopy

2022年8月27日

0 276 1

Tags： MySQL mysql-utilities mysqldbcopy

👉 本文共约2477个字，系统预计阅读时间或需10分钟。

mysqldbcopy
实例
权限
如何复制数据库
我的示例

mysqldbcopy

从源服务器上拷贝一个数据库到另一个目标服务器上。源服务器和目标服务器可以同一台，也可以是不同台。数据库名字也可以相同或不相同。如果源服务器和目标服务器同一台，那么数据库名字必需不一样的，也就是同一个实例下，不能有相同的数据库名。

mysqldbcopy 接受一个或多个数据库对。格式为db_name:new_db_name。分别表示源和目标。

默认情况下，复制所有对象(如表、视图、触发器、事件、存储过程、函数和数据库级别权限)和数据到目标。可以有选择性的复制，如只复制部分对象，不复制数据等等。

要针对性的复制，可以使用--exclude选项来排除。格式如下：db.obj 。也可以使用搜索模式，如--exclude=db1.trig1 排除单个触发器， --exclude=trig_排除所有以trig开头的对象。

默认情况下，目标上使用的存储引擎与源相同。如果目标上使用另一种存储引擎，可以使用--new-storage-engine 选项来指定。如果目标服务器支持指定的引擎，那么所有表使用该引擎。

如果目标服务器不支持源服务器所用的存储引擎，可以使用--default-storage-engine选项来指定默认使用的存储引擎。 --new-storage-engine选项的优先级高于--default-storage-engine。如果这两个选项都指定，然而又不支持指定的存储引擎，那么默认的代替。

默认情况下，复制操作是使用一致性快照来读取源数据库。要改变锁定模式，可以使用--locking选项来指定锁定类型值。值no-locks关闭锁，lock-all只使用表锁。默认是snapshot。此外，使用WRITE锁，在复制过程中将锁定目标表。

从主或者从服务器复制还可以包含复制语句。--rpl选项指定

master 创建并执行CHANGE MASTER 语句，将目标服务器作为--source选项指定的服务器的从。在复制数据之前，执行 STOP SLAVE 语句。在复制完成后执行 CHANGE MASTER 和 START SLAVE语句。
slave 创建和执行 CHANGE MASTER 语句，使目标服务器成为与--source选项指定的服务器的同一个主服务器的从。仅仅在源服务器是从有用。

语句的执行先后顺序，可以将general_log打开，可以看到每步的执行过程。

# mysqldbcopy --source=instance_3306 --destination=instance_3307  ttlsa_com:ttlsa_com_test --rpl=master --rpl-user=root -vvvvvv --drop-first
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# LOCK STRING: FLUSH TABLES WITH READ LOCK
# Copying database ttlsa_com renamed as ttlsa_com_test
# Dropping new object TABLE ttlsa_com_test.`data`
# WARNING: Unable to drop `data` from destination database (object may not exist): DROP TABLE `ttlsa_com_test`.`data`
# Copying TABLE ttlsa_com.data
CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` char(30) NOT NULL DEFAULT '',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
# Copying data for TABLE ttlsa_com.data
# Getting indexes for ttlsa_com.data
# UNLOCK STRING: UNLOCK TABLES
# Connecting to the current server as master
CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_USER = 'root', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'mysql-bin-3306.000002', MASTER_LOG_POS = 214;
#...done.
Time: 0.72 sec

# mysql_config_editor set  --login-path=instance_3308 --host=localhost --user=root --port=3308 --password 
Enter password: 
# mysqldbcopy --source=instance_3307 --destination=instance_3308 ttlsa_com:ttlsa_com_test --rpl=slave --rpl-user=root -vvvvvv --drop-first 
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Reading master information from a file.
# Copying database ttlsa_com renamed as ttlsa_com_test
# Dropping new object TABLE ttlsa_com_test.`data`
# WARNING: Unable to drop `data` from destination database (object may not exist): DROP TABLE `ttlsa_com_test`.`data`
# Copying TABLE ttlsa_com.data
CREATE TABLE `data` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `value` char(30) NOT NULL DEFAULT '',
 `count` int(11) DEFAULT NULL,
 PRIMARY KEY (`value`),
 KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
# Copying data for TABLE ttlsa_com.data
# Getting indexes for ttlsa_com.data
# Connecting to the current server's master
CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_USER = 'root', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_SSL = 1, MASTER_LOG_FILE = 'mysql-bin-3306.000002', MASTER_LOG_POS = 214;
#...done.
Time: 0.80 sec

# mysqldbcopy --source=instance_3306 --destination=instance_3307 ttlsa_com:ttlsa_com_test --rpl=master --rpl-user=root -vvvvvv --drop-first

# Source on localhost: ... connected.

# Destination on localhost: ... connected.

# LOCK STRING: FLUSH TABLES WITH READ LOCK

# Copying database ttlsa_com renamed as ttlsa_com_test

# Dropping new object TABLE ttlsa_com_test.`data`

# WARNING: Unable to drop `data` from destination database (object may not exist): DROP TABLE `ttlsa_com_test`.`data`

# Copying TABLE ttlsa_com.data

CREATE TABLE `data` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`value` char(30) NOT NULL DEFAULT '',

`count` int(11) DEFAULT NULL,

PRIMARY KEY (`value`),

KEY `id` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1

# Copying data for TABLE ttlsa_com.data

# Getting indexes for ttlsa_com.data

# UNLOCK STRING: UNLOCK TABLES

# Connecting to the current server as master

CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_USER = 'root', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_LOG_FILE = 'mysql-bin-3306.000002', MASTER_LOG_POS = 214;

#...done.

Time: 0.72 sec

# mysql_config_editor set --login-path=instance_3308 --host=localhost --user=root --port=3308 --password

Enter password:

# mysqldbcopy --source=instance_3307 --destination=instance_3308 ttlsa_com:ttlsa_com_test --rpl=slave --rpl-user=root -vvvvvv --drop-first

# Source on localhost: ... connected.

# Destination on localhost: ... connected.

# Reading master information from a file.

# Copying database ttlsa_com renamed as ttlsa_com_test

# Dropping new object TABLE ttlsa_com_test.`data`

# WARNING: Unable to drop `data` from destination database (object may not exist): DROP TABLE `ttlsa_com_test`.`data`

# Copying TABLE ttlsa_com.data

CREATE TABLE `data` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`value` char(30) NOT NULL DEFAULT '',

`count` int(11) DEFAULT NULL,

PRIMARY KEY (`value`),

KEY `id` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1

# Copying data for TABLE ttlsa_com.data

# Getting indexes for ttlsa_com.data

# Connecting to the current server's master

CHANGE MASTER TO MASTER_HOST = 'localhost', MASTER_USER = 'root', MASTER_PASSWORD = '', MASTER_PORT = 3306, MASTER_SSL = 1, MASTER_LOG_FILE = 'mysql-bin-3306.000002', MASTER_LOG_POS = 214;

#...done.

Time: 0.80 sec

--repl-user选项指定复制的用户名和密码。

如果要复制的数据库的服务器上启用了GTIDs（GTID_MODE = ON），如果只复制其中一部分数据库，将会有警告信息产生。这是因为GTID报表生成包括所有数据库的gtids，不仅仅是某个的。

如果有启用GTID，但是使用了--skip-gtid也会收到警告。

如果启用了GTID，最好是复制所有的数据库。


  --version             show program's version number and exit
  --help                display a help message and exit
  --license             display program's license and exit
  --source=SOURCE       connection information for source server in the form:
                        <user>[:<password>]@<host>[:<port>][:<socket>] or
                        <login-path>[:<port>][:<socket>] or <config-
                        path>[<[group]>].
  --destination=DESTINATION
                        connection information for destination server in the
                        form: <user>[:<password>]@<host>[:<port>][:<socket>]
                        or <login-path>[:<port>][:<socket>] or <config-
                        path>[<[group]>].
  --character-set=CHARSET
                        sets the client character set. The default is
                        retrieved from the server variable
                        'character_set_client'.
  -d, --drop-first      drop the new database or object if it exists
  -x EXCLUDE, --exclude=EXCLUDE
                        exclude one or more objects from the operation using
                        either a specific name (e.g. db1.t1), a LIKE pattern
                        (e.g. db1.t% or db%.%) or a REGEXP search pattern. To
                        use a REGEXP search pattern for all exclusions, you
                        must also specify the --regexp option. Repeat the
                        --exclude option for multiple exclusions.
  -a, --all             所有数据库
  --skip=SKIP_OBJECTS   specify objects to skip in the operation in the form
                        of a comma-separated list (no spaces). Valid values =
                        tables, views, triggers, procedures, functions,
                        events, grants, data, create_db
  -v, --verbose         control how much information is displayed. e.g., -v =
                        verbose, -vv = more verbose, -vvv = debug
  -q, --quiet           turn off all messages for quiet execution.
  --new-storage-engine=NEW_ENGINE
                        change all tables to use this storage engine if
                        storage engine exists on the destination.
  --default-storage-engine=DEF_ENGINE
                        change all tables to use this storage engine if the
                        original storage engine does not exist on the
                        destination.
  --locking=LOCKING     choose the lock type for the operation: no-locks = do
                        not use any table locks, lock-all = use table locks
                        but no transaction and no consistent read, snaphot
                        (default): consistent read using a single transaction.
  -G, --basic-regexp, --regexp
                        use 'REGEXP' operator to match pattern. Default is to
                        use 'LIKE'.
  --rpl-user=RPL_USER   the user and password for the replication user
                        requirement, in the form: <user>[:<password>] or
                        <login-path>. E.g. rpl:passwd
  --rpl=RPL_MODE, --replication=RPL_MODE
                        include replication information. Choices: 'master' =
                        include the CHANGE MASTER command using the source
                        server as the master, 'slave' = include the CHANGE
                        MASTER command for the source server's master (only
                        works if the source server is a slave).
  --ssl-ca=SSL_CA       The path to a file that contains a list of trusted SSL
                        CAs.
  --ssl-cert=SSL_CERT   The name of the SSL certificate file to use for
                        establishing a secure connection.
  --ssl-key=SSL_KEY     The name of the SSL key file to use for establishing a
                        secure connection.
  --skip-gtid           skip creation and execution of GTID statements during
                        copy.
  --multiprocess=MULTIPROCESS
                        use multiprocessing, number of processes to use for
                        concurrent execution. Special values: 0 (number of
                        processes equal to the CPUs detected) and 1 (default -
                        no concurrency).

--version show program's version number and exit

--help display a help message and exit

--license display program's license and exit

--source=SOURCE connection information for source server in the form:

<user>[:<password>]@<host>[:<port>][:<socket>] or

<login-path>[:<port>][:<socket>] or <config-

path>[<[group]>].

--destination=DESTINATION

connection information for destination server in the

form: <user>[:<password>]@<host>[:<port>][:<socket>]

or <login-path>[:<port>][:<socket>] or <config-

path>[<[group]>].

--character-set=CHARSET

sets the client character set. The default is

retrieved from the server variable

'character_set_client'.

-d, --drop-first drop the new database or object if it exists

-x EXCLUDE, --exclude=EXCLUDE

exclude one or more objects from the operation using

either a specific name (e.g. db1.t1), a LIKE pattern

(e.g. db1.t% or db%.%) or a REGEXP search pattern. To

use a REGEXP search pattern for all exclusions, you

must also specify the --regexp option. Repeat the

--exclude option for multiple exclusions.

-a, --all 所有数据库

--skip=SKIP_OBJECTS specify objects to skip in the operation in the form

of a comma-separated list (no spaces). Valid values =

tables, views, triggers, procedures, functions,

events, grants, data, create_db

-v, --verbose control how much information is displayed. e.g., -v =

verbose, -vv = more verbose, -vvv = debug

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

--new-storage-engine=NEW_ENGINE

change all tables to use this storage engine if

storage engine exists on the destination.

--default-storage-engine=DEF_ENGINE

change all tables to use this storage engine if the

original storage engine does not exist on the

destination.

--locking=LOCKING choose the lock type for the operation: no-locks = do

not use any table locks, lock-all = use table locks

but no transaction and no consistent read, snaphot

(default): consistent read using a single transaction.

-G, --basic-regexp, --regexp

use 'REGEXP' operator to match pattern. Default is to

use 'LIKE'.

--rpl-user=RPL_USER the user and password for the replication user

requirement, in the form: <user>[:<password>] or

<login-path>. E.g. rpl:passwd

--rpl=RPL_MODE, --replication=RPL_MODE

include replication information. Choices: 'master' =

include the CHANGE MASTER command using the source

server as the master, 'slave' = include the CHANGE

MASTER command for the source server's master (only

works if the source server is a slave).

--ssl-ca=SSL_CA The path to a file that contains a list of trusted SSL

CAs.

--ssl-cert=SSL_CERT The name of the SSL certificate file to use for

establishing a secure connection.

--ssl-key=SSL_KEY The name of the SSL key file to use for establishing a

secure connection.

--skip-gtid skip creation and execution of GTID statements during

copy.

--multiprocess=MULTIPROCESS

use multiprocessing, number of processes to use for

concurrent execution. Special values: 0 (number of

processes equal to the CPUs detected) and 1 (default -

no concurrency).

必需提供连接参数和赋予要访问对象的适当权限。

源服务器上要复制的数据库，所需要的权限有：SELECT、SHOW VIEW、EVENT、TRIGGER，同时需要对mysql库有SELECT权限。

目标服务器上所需要的权限有：CREATE、 ALTER、 SELECT、 INSERT、 UPDATE、 LOCK TABLES，如果有使用--drop-first选项就需要 DROP权限。如果二进制日志启用就要SUPER权限。CREATE VIEW 、CREATE ROUTINE、 EXECUTE、EVENT、TRIGGER、GRANT OPTION 、SUPER。

在同一个实例上复制，--rpl选项是无效的，将会产生一个错误。

当复制数据和包含GTID命令，可能会遇到"GTID_PURGED can only be set when GTID_EXECUTED is empty"错误。产生的原因是目标服务器不是一个干净的复制状态。解决办法是在复制之前，先在目标服务器上执行 RESET MASTER 命令，清空复制状态。

实例

在同一个实例上复制ttlsa_com库

如果要复制的数据库并不是所有的表是innodb引擎的，为了确保数据的一致性，在读取的过程中需要锁定表。可以使用 --locking=lock-all 选项命令。如下所示：

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

获取验证码：请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

打赏赞(1)

标签： MySQL mysql-utilities mysqldbcopy

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

发表回复取消回复

要发表评论，您必须先登录。

一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30

合 mysql-utilities工具之MySQL数据库拷贝工具mysqldbcopy

mysqldbcopy

实例

相关文章

您可能还喜欢...

发表回复取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

合 mysql-utilities工具之MySQL数据库拷贝工具mysqldbcopy

mysqldbcopy

实例

相关文章

您可能还喜欢...

小麦苗MySQL DBA培训（从基础到高可用）

MySQL权限丢失导致只能看见information_schema一个库的解决办法

MySQL误操作闪回恢复利器之my2sql

发表回复 取消回复

网站公告

网站寄语

本站其它工具

搜索本网站

标签云☁

网站日历

网站归档

网站分类

发表回复取消回复