如果目标服务器不支持源服务器所用的存储引擎，可以使用--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 选项命令。如下所示：

从主拷贝到从，源服务器是主，目标服务器是从。如上所见。

从从拷贝到另一台服务器上，与从具有相同的主。如上所见。

不想拷贝ttlsa_com.data表：


# instance_3306
mysql> show tables;
+---------------------+
| Tables_in_ttlsa_com |
+---------------------+
| bbs_categories      |
| bbs_comments        |
| bbs_favorites       |
| bbs_forums          |
| bbs_links           |
| bbs_notifications   |
| bbs_page            |
| bbs_settings        |
| bbs_tags            |
| bbs_tags_relation   |
| bbs_user_follow     |
| bbs_user_groups     |
| bbs_users           |
| data                |
| t_data              |
+---------------------+
15 rows in set (0.00 sec)

# mysqldbcopy --source=instance_3306 --destination=instance_3307  -vvv --exclude=ttlsa_com.data  ttlsa_com                    
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database ttlsa_com 
# Copying TABLE ttlsa_com.bbs_categories
CREATE TABLE `bbs_categories` (
  `cid` smallint(5) NOT NULL AUTO_INCREMENT,
  `pid` smallint(5) NOT NULL DEFAULT '0',
  `cname` varchar(30) DEFAULT NULL COMMENT '分类名称',
  `content` varchar(255) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `ico` varchar(128) DEFAULT NULL,
  `master` varchar(100) NOT NULL,
  `permit` varchar(255) DEFAULT NULL,
  `listnum` mediumint(8) unsigned DEFAULT '0',
  `clevel` varchar(25) DEFAULT NULL,
  `cord` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`cid`,`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
# Copying TABLE ttlsa_com.bbs_comments
CREATE TABLE `bbs_comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) NOT NULL DEFAULT '0',
  `uid` int(11) NOT NULL DEFAULT '0',
  `content` text,
  `replytime` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`,`fid`,`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=371 DEFAULT CHARSET=utf8
# Copying TABLE ttlsa_com.bbs_favorites
CREATE TABLE `bbs_favorites` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `uid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `favorites` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `content` mediumtext NOT NULL,
  PRIMARY KEY (`id`,`uid`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
# Copying TABLE ttlsa_com.bbs_forums
CREATE TABLE `bbs_forums` (
  `fid` int(11) NOT NULL AUTO_INCREMENT,
  `cid` smallint(5) NOT NULL DEFAULT '0',
  `uid` mediumint(8) NOT NULL DEFAULT '0',
  `ruid` mediumint(8) DEFAULT NULL,
  `title` varchar(128) DEFAULT NULL,
  `keywords` varchar(255) DEFAULT NULL,
  `content` text,
  `addtime` int(10) DEFAULT NULL,
  `updatetime` int(10) DEFAULT NULL,
  `lastreply` int(10) DEFAULT NULL,
  `views` int(10) DEFAULT '0',
  `comments` smallint(8) DEFAULT '0',
  `favorites` int(10) unsigned DEFAULT '0',
  `closecomment` tinyint(1) DEFAULT NULL,
  `is_top` tinyint(1) NOT NULL DEFAULT '0',
  `is_hidden` tinyint(1) NOT NULL DEFAULT '0',
  `ord` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`fid`,`cid`,`uid`),
  KEY `updatetime` (`updatetime`),
  KEY `ord` (`ord`)
) ENGINE=MyISAM AUTO_INCREMENT=94 DEFAULT CHARSET=utf8
# Copying TABLE ttlsa_com.bbs_links
CREATE TABLE `bbs_links` (
  `id` smallint(6) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `url` varchar(200) DEFAULT NULL,
  `logo` varchar(200) DEFAULT NULL,
  `is_hidden` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
# Copying TABLE ttlsa_com.bbs_notifications
CREATE TABLE `bbs_notifications` (
  `nid` int(11) NOT NULL AUTO_INCREMENT,
  `fid` int(11) DEFAULT NULL,
  `suid` int(11) DEFAULT NULL,
  `nuid` int(11) NOT NULL DEFAULT '0',
  `ntype` tinyint(1) DEFAULT NULL,
  `ntime` int(10) DEFAULT NULL,
  PRIMARY KEY (`nid`,`nuid`)
) ENGINE=MyISAM AUTO_INCREMENT=444 DEFAULT CHARSET=utf8
# Copying TABLE ttlsa_com.bbs_page
CREATE TABLE `bbs_page` (
  `pid` tinyint(6) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  `content` text,
  `go_url` varchar(100) DEFAULT NULL,
  `add_time` int(10) DEFAULT NULL,
  `is_hidden` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
# Copying TABLE ttlsa_com.bbs_settings
CREATE TABLE `bbs_settings` (
  `id` tinyint(5) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL DEFAULT '',
  `value` text NOT NULL,
  `type` tinyint(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`title`,`type`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8
# Copying TABLE ttlsa_com.bbs_tags
CREATE TABLE `bbs_tags` (
  `tag_id` int(10) NOT NULL AUTO_INCREMENT,
  `tag_title` varchar(30) NOT NULL,
  `forums` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`tag_id`),
  UNIQUE KEY `tag_title` (`tag_title`)
) ENGINE=MyISAM AUTO_INCREMENT=185 DEFAULT CHARSET=utf8
# Copying TABLE ttlsa_com.bbs_tags_relation
CREATE TABLE `bbs_tags_relation` (
  `tag_id` int(10) NOT NULL DEFAULT '0',
  `fid` int(10) DEFAULT NULL,
  KEY `tag_id` (`tag_id`),
  KEY `fid` (`fid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
# Copying TABLE ttlsa_com.bbs_users
CREATE TABLE `bbs_users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `password` char(32) DEFAULT NULL,
  `openid` char(32) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `avatar` varchar(100) DEFAULT NULL,
  `homepage` varchar(50) DEFAULT NULL,
  `money` int(11) DEFAULT '100',
  `signature` text,
  `forums` int(11) DEFAULT '0',
  `replies` int(11) DEFAULT '0',
  `notices` smallint(5) DEFAULT '0',
  `follows` int(11) NOT NULL DEFAULT '0',
  `regtime` int(10) DEFAULT NULL,
  `lastlogin` int(10) DEFAULT NULL,
  `lastpost` int(10) DEFAULT NULL,
  `qq` varchar(20) DEFAULT NULL,
  `group_type` tinyint(3) NOT NULL DEFAULT '0',
  `gid` tinyint(3) NOT NULL DEFAULT '3',
  `ip` char(15) DEFAULT NULL,
  `location` varchar(128) DEFAULT NULL,
  `token` varchar(40) DEFAULT NULL,
  `introduction` text,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`uid`,`group_type`)
) ENGINE=MyISAM AUTO_INCREMENT=109 DEFAULT CHARSET=utf8
# Copying TABLE ttlsa_com.bbs_user_follow
CREATE TABLE `bbs_user_follow` (
  `follow_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(10) unsigned NOT NULL DEFAULT '0',
  `follow_uid` int(10) unsigned NOT NULL DEFAULT '0',
  `addtime` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`follow_id`,`uid`,`follow_uid`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
# Copying TABLE ttlsa_com.bbs_user_groups
CREATE TABLE `bbs_user_groups` (
  `gid` int(11) NOT NULL AUTO_INCREMENT,
  `group_type` tinyint(3) NOT NULL DEFAULT '0',
  `group_name` varchar(50) DEFAULT NULL,
  `usernum` int(11) NOT NULL,
  PRIMARY KEY (`gid`,`group_type`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
# Copying TABLE ttlsa_com.t_data
CREATE TABLE `t_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 DEFAULT CHARSET=latin1
# Copying data for TABLE ttlsa_com.bbs_categories
# Copying data for TABLE ttlsa_com.bbs_comments
# Copying data for TABLE ttlsa_com.bbs_favorites
# Copying data for TABLE ttlsa_com.bbs_forums
# Copying data for TABLE ttlsa_com.bbs_links
# Copying data for TABLE ttlsa_com.bbs_notifications
# Copying data for TABLE ttlsa_com.bbs_page
# Copying data for TABLE ttlsa_com.bbs_settings
# Copying data for TABLE ttlsa_com.bbs_tags
# Copying data for TABLE ttlsa_com.bbs_tags_relation
# Copying data for TABLE ttlsa_com.bbs_users
# Copying data for TABLE ttlsa_com.bbs_user_follow
# Copying data for TABLE ttlsa_com.bbs_user_groups
# Copying data for TABLE ttlsa_com.t_data
#...done.
Time: 1.32 sec

Database changed
mysql> use ttlsa_com
mysql> show tables;
+---------------------+
| Tables_in_ttlsa_com |
+---------------------+
| bbs_categories      |
| bbs_comments        |
| bbs_favorites       |
| bbs_forums          |
| bbs_links           |
| bbs_notifications   |
| bbs_page            |
| bbs_settings        |
| bbs_tags            |
| bbs_tags_relation   |
| bbs_user_follow     |
| bbs_user_groups     |
| bbs_users           |
| t_data              |
+---------------------+
14 rows in set (0.00 sec)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

# instance_3306

mysql> show tables;

+---------------------+

| Tables_in_ttlsa_com |

+---------------------+

| bbs_categories |

| bbs_comments |

| bbs_favorites |

| bbs_forums |

| bbs_links |

| bbs_notifications |

| bbs_page |

| bbs_settings |

| bbs_tags |

| bbs_tags_relation |

| bbs_user_follow |

| bbs_user_groups |

| bbs_users |

| data |

| t_data |

+---------------------+

15 rows in set (0.00 sec)

# mysqldbcopy --source=instance_3306 --destination=instance_3307 -vvv --exclude=ttlsa_com.data ttlsa_com

# Source on localhost: ... connected.

# Destination on localhost: ... connected.

# Copying database ttlsa_com

# Copying TABLE ttlsa_com.bbs_categories

CREATE TABLE `bbs_categories` (

`cid` smallint(5) NOT NULL AUTO_INCREMENT,

`pid` smallint(5) NOT NULL DEFAULT '0',

`cname` varchar(30) DEFAULT NULL COMMENT '分类名称',

`content` varchar(255) DEFAULT NULL,

`keywords` varchar(255) DEFAULT NULL,

`ico` varchar(128) DEFAULT NULL,

`master` varchar(100) NOT NULL,

`permit` varchar(255) DEFAULT NULL,

`listnum` mediumint(8) unsigned DEFAULT '0',

`clevel` varchar(25) DEFAULT NULL,

`cord` smallint(6) DEFAULT NULL,

PRIMARY KEY (`cid`,`pid`)

) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

# Copying TABLE ttlsa_com.bbs_comments

CREATE TABLE `bbs_comments` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`fid` int(11) NOT NULL DEFAULT '0',

`uid` int(11) NOT NULL DEFAULT '0',

`content` text,

`replytime` char(10) DEFAULT NULL,

PRIMARY KEY (`id`,`fid`,`uid`)

) ENGINE=MyISAM AUTO_INCREMENT=371 DEFAULT CHARSET=utf8

# Copying TABLE ttlsa_com.bbs_favorites

CREATE TABLE `bbs_favorites` (

`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

`uid` mediumint(8) unsigned NOT NULL DEFAULT '0',

`favorites` mediumint(8) unsigned NOT NULL DEFAULT '0',

`content` mediumtext NOT NULL,

PRIMARY KEY (`id`,`uid`),

KEY `uid` (`uid`)

) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

# Copying TABLE ttlsa_com.bbs_forums

CREATE TABLE `bbs_forums` (

`fid` int(11) NOT NULL AUTO_INCREMENT,

`cid` smallint(5) NOT NULL DEFAULT '0',

`uid` mediumint(8) NOT NULL DEFAULT '0',

`ruid` mediumint(8) DEFAULT NULL,

`title` varchar(128) DEFAULT NULL,

`keywords` varchar(255) DEFAULT NULL,

`content` text,

`addtime` int(10) DEFAULT NULL,

`updatetime` int(10) DEFAULT NULL,

`lastreply` int(10) DEFAULT NULL,

`views` int(10) DEFAULT '0',

`comments` smallint(8) DEFAULT '0',

`favorites` int(10) unsigned DEFAULT '0',

`closecomment` tinyint(1) DEFAULT NULL,

`is_top` tinyint(1) NOT NULL DEFAULT '0',

`is_hidden` tinyint(1) NOT NULL DEFAULT '0',

`ord` int(10) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`fid`,`cid`,`uid`),

KEY `updatetime` (`updatetime`),

KEY `ord` (`ord`)

) ENGINE=MyISAM AUTO_INCREMENT=94 DEFAULT CHARSET=utf8

# Copying TABLE ttlsa_com.bbs_links

CREATE TABLE `bbs_links` (

`id` smallint(6) NOT NULL AUTO_INCREMENT,

`name` varchar(100) DEFAULT NULL,

`url` varchar(200) DEFAULT NULL,

`logo` varchar(200) DEFAULT NULL,

`is_hidden` tinyint(1) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

# Copying TABLE ttlsa_com.bbs_notifications

CREATE TABLE `bbs_notifications` (

`nid` int(11) NOT NULL AUTO_INCREMENT,

`fid` int(11) DEFAULT NULL,

`suid` int(11) DEFAULT NULL,

`nuid` int(11) NOT NULL DEFAULT '0',

`ntype` tinyint(1) DEFAULT NULL,

`ntime` int(10) DEFAULT NULL,

PRIMARY KEY (`nid`,`nuid`)

) ENGINE=MyISAM AUTO_INCREMENT=444 DEFAULT CHARSET=utf8

# Copying TABLE ttlsa_com.bbs_page

CREATE TABLE `bbs_page` (

`pid` tinyint(6) NOT NULL AUTO_INCREMENT,

`title` varchar(100) DEFAULT NULL,

`content` text,

`go_url` varchar(100) DEFAULT NULL,

`add_time` int(10) DEFAULT NULL,

`is_hidden` tinyint(1) DEFAULT '0',

PRIMARY KEY (`pid`)

) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

# Copying TABLE ttlsa_com.bbs_settings

CREATE TABLE `bbs_settings` (

`id` tinyint(5) NOT NULL AUTO_INCREMENT,

`title` varchar(255) NOT NULL DEFAULT '',

`value` text NOT NULL,

`type` tinyint(3) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`,`title`,`type`)

) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=utf8

# Copying TABLE ttlsa_com.bbs_tags

CREATE TABLE `bbs_tags` (

`tag_id` int(10) NOT NULL AUTO_INCREMENT,

`tag_title` varchar(30) NOT NULL,

`forums` int(10) NOT NULL DEFAULT '0',

PRIMARY KEY (`tag_id`),

UNIQUE KEY `tag_title` (`tag_title`)

) ENGINE=MyISAM AUTO_INCREMENT=185 DEFAULT CHARSET=utf8

# Copying TABLE ttlsa_com.bbs_tags_relation

CREATE TABLE `bbs_tags_relation` (

`tag_id` int(10) NOT NULL DEFAULT '0',

`fid` int(10) DEFAULT NULL,

KEY `tag_id` (`tag_id`),

KEY `fid` (`fid`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

# Copying TABLE ttlsa_com.bbs_users

CREATE TABLE `bbs_users` (

`uid` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(20) DEFAULT NULL,

`password` char(32) DEFAULT NULL,

`openid` char(32) NOT NULL,

`email` varchar(50) DEFAULT NULL,

`avatar` varchar(100) DEFAULT NULL,

`homepage` varchar(50) DEFAULT NULL,

`money` int(11) DEFAULT '100',

`signature` text,

`forums` int(11) DEFAULT '0',

`replies` int(11) DEFAULT '0',

`notices` smallint(5) DEFAULT '0',

`follows` int(11) NOT NULL DEFAULT '0',

`regtime` int(10) DEFAULT NULL,

`lastlogin` int(10) DEFAULT NULL,

`lastpost` int(10) DEFAULT NULL,

`qq` varchar(20) DEFAULT NULL,

`group_type` tinyint(3) NOT NULL DEFAULT '0',

`gid` tinyint(3) NOT NULL DEFAULT '3',

`ip` char(15) DEFAULT NULL,

`location` varchar(128) DEFAULT NULL,

`token` varchar(40) DEFAULT NULL,

`introduction` text,

`is_active` tinyint(1) NOT NULL DEFAULT '1',

PRIMARY KEY (`uid`,`group_type`)

) ENGINE=MyISAM AUTO_INCREMENT=109 DEFAULT CHARSET=utf8

# Copying TABLE ttlsa_com.bbs_user_follow

CREATE TABLE `bbs_user_follow` (

`follow_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`uid` int(10) unsigned NOT NULL DEFAULT '0',

`follow_uid` int(10) unsigned NOT NULL DEFAULT '0',

`addtime` int(10) NOT NULL DEFAULT '0',

PRIMARY KEY (`follow_id`,`uid`,`follow_uid`)

) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

# Copying TABLE ttlsa_com.bbs_user_groups

CREATE TABLE `bbs_user_groups` (

`gid` int(11) NOT NULL AUTO_INCREMENT,

`group_type` tinyint(3) NOT NULL DEFAULT '0',

`group_name` varchar(50) DEFAULT NULL,

`usernum` int(11) NOT NULL,

PRIMARY KEY (`gid`,`group_type`)

) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

# Copying TABLE ttlsa_com.t_data

CREATE TABLE `t_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 DEFAULT CHARSET=latin1

# Copying data for TABLE ttlsa_com.bbs_categories

# Copying data for TABLE ttlsa_com.bbs_comments

# Copying data for TABLE ttlsa_com.bbs_favorites

# Copying data for TABLE ttlsa_com.bbs_forums

# Copying data for TABLE ttlsa_com.bbs_links

# Copying data for TABLE ttlsa_com.bbs_notifications

# Copying data for TABLE ttlsa_com.bbs_page

# Copying data for TABLE ttlsa_com.bbs_settings

# Copying data for TABLE ttlsa_com.bbs_tags

# Copying data for TABLE ttlsa_com.bbs_tags_relation

# Copying data for TABLE ttlsa_com.bbs_users

# Copying data for TABLE ttlsa_com.bbs_user_follow

# Copying data for TABLE ttlsa_com.bbs_user_groups

# Copying data for TABLE ttlsa_com.t_data

#...done.

Time: 1.32 sec

Database changed

mysql> use ttlsa_com

mysql> show tables;

+---------------------+

| Tables_in_ttlsa_com |

+---------------------+

| bbs_categories |

| bbs_comments |

| bbs_favorites |

| bbs_forums |

| bbs_links |

| bbs_notifications |

| bbs_page |

| bbs_settings |

| bbs_tags |

| bbs_tags_relation |

| bbs_user_follow |

| bbs_user_groups |

| bbs_users |

| t_data |

+---------------------+

14 rows in set (0.00 sec)

默认情况下是LIKE匹配。使用正则，需要指定--regexp选项，同时，正则表达式只需写对象的匹配模式，不必加上数据库名。


# mysqldbcopy  --source=instance_3306 --destination=instance_3307  -vvv --regexp --exclude=ttlsa_com\.b   ttlsa_com  --drop-first  这个是有问题的，也是like匹配，相当于%b%
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database ttlsa_com 
# Dropping new object TABLE ttlsa_com.`data`
# WARNING: Unable to drop `data` from destination database (object may not exist): DROP TABLE `ttlsa_com`.`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
# Dropping new object TABLE ttlsa_com.`t_data`
# WARNING: Unable to drop `t_data` from destination database (object may not exist): DROP TABLE `ttlsa_com`.`t_data`
# Copying TABLE ttlsa_com.t_data
CREATE TABLE `t_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 DEFAULT CHARSET=latin1
# Copying data for TABLE ttlsa_com.data
# Getting indexes for ttlsa_com.data
# Copying data for TABLE ttlsa_com.t_data
#...done.
Time: 0.71 sec

# instance_3307
mysql> show tables;
+---------------------+
| Tables_in_ttlsa_com |
+---------------------+
| data                |
| t_data              |
+---------------------+
2 rows in set (0.00 sec)

# mysqldbcopy --source=instance_3306 --destination=instance_3307 -vvv --regexp --exclude=ttlsa_com\.b ttlsa_com --drop-first 这个是有问题的，也是like匹配，相当于%b%

# Source on localhost: ... connected.

# Destination on localhost: ... connected.

# Copying database ttlsa_com

# Dropping new object TABLE ttlsa_com.`data`

# WARNING: Unable to drop `data` from destination database (object may not exist): DROP TABLE `ttlsa_com`.`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

# Dropping new object TABLE ttlsa_com.`t_data`

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

# Copying TABLE ttlsa_com.t_data

CREATE TABLE `t_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 DEFAULT CHARSET=latin1

# Copying data for TABLE ttlsa_com.data

# Getting indexes for ttlsa_com.data

# Copying data for TABLE ttlsa_com.t_data

#...done.

Time: 0.71 sec

# instance_3307

mysql> show tables;

+---------------------+

| Tables_in_ttlsa_com |

+---------------------+

| data |

| t_data |

+---------------------+

2 rows in set (0.00 sec)

上面，那样写有问题的。使用正则排除t开头的表


# mysqldbcopy --source=instance_3306 --destination=instance_3307  --regexp --exclude=^t   ttlsa_com   --drop-first   
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database ttlsa_com 
# Copying TABLE ttlsa_com.bbs_categories
# Copying TABLE ttlsa_com.bbs_comments
# Copying TABLE ttlsa_com.bbs_favorites
# Copying TABLE ttlsa_com.bbs_forums
# Copying TABLE ttlsa_com.bbs_links
# Copying TABLE ttlsa_com.bbs_notifications
# Copying TABLE ttlsa_com.bbs_page
# Copying TABLE ttlsa_com.bbs_settings
# Copying TABLE ttlsa_com.bbs_tags
# Copying TABLE ttlsa_com.bbs_tags_relation
# Copying TABLE ttlsa_com.bbs_users
# Copying TABLE ttlsa_com.bbs_user_follow
# Copying TABLE ttlsa_com.bbs_user_groups
# Copying TABLE ttlsa_com.data
# Copying data for TABLE ttlsa_com.bbs_categories
# Copying data for TABLE ttlsa_com.bbs_comments
# Copying data for TABLE ttlsa_com.bbs_favorites
# Copying data for TABLE ttlsa_com.bbs_forums
# Copying data for TABLE ttlsa_com.bbs_links
# Copying data for TABLE ttlsa_com.bbs_notifications
# Copying data for TABLE ttlsa_com.bbs_page
# Copying data for TABLE ttlsa_com.bbs_settings
# Copying data for TABLE ttlsa_com.bbs_tags
# Copying data for TABLE ttlsa_com.bbs_tags_relation
# Copying data for TABLE ttlsa_com.bbs_users
# Copying data for TABLE ttlsa_com.bbs_user_follow
# Copying data for TABLE ttlsa_com.bbs_user_groups
# Copying data for TABLE ttlsa_com.data
#...done.

# mysqldbcopy --source=instance_3306 --destination=instance_3307 --regexp --exclude=^t ttlsa_com --drop-first

# Source on localhost: ... connected.

# Destination on localhost: ... connected.

# Copying database ttlsa_com

# Copying TABLE ttlsa_com.bbs_categories

# Copying TABLE ttlsa_com.bbs_comments

# Copying TABLE ttlsa_com.bbs_favorites

# Copying TABLE ttlsa_com.bbs_forums

# Copying TABLE ttlsa_com.bbs_links

# Copying TABLE ttlsa_com.bbs_notifications

# Copying TABLE ttlsa_com.bbs_page

# Copying TABLE ttlsa_com.bbs_settings

# Copying TABLE ttlsa_com.bbs_tags

# Copying TABLE ttlsa_com.bbs_tags_relation

# Copying TABLE ttlsa_com.bbs_users

# Copying TABLE ttlsa_com.bbs_user_follow

# Copying TABLE ttlsa_com.bbs_user_groups

# Copying TABLE ttlsa_com.data

# Copying data for TABLE ttlsa_com.bbs_categories

# Copying data for TABLE ttlsa_com.bbs_comments

# Copying data for TABLE ttlsa_com.bbs_favorites

# Copying data for TABLE ttlsa_com.bbs_forums

# Copying data for TABLE ttlsa_com.bbs_links

# Copying data for TABLE ttlsa_com.bbs_notifications

# Copying data for TABLE ttlsa_com.bbs_page

# Copying data for TABLE ttlsa_com.bbs_settings

# Copying data for TABLE ttlsa_com.bbs_tags

# Copying data for TABLE ttlsa_com.bbs_tags_relation

# Copying data for TABLE ttlsa_com.bbs_users

# Copying data for TABLE ttlsa_com.bbs_user_follow

# Copying data for TABLE ttlsa_com.bbs_user_groups

# Copying data for TABLE ttlsa_com.data

#...done.

使用正则排除t或b开头的表


# mysqldbcopy --source=instance_3306 --destination=instance_3307  --regexp --exclude=^[tb]   ttlsa_com   --drop-first 
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database ttlsa_com 
# Copying TABLE ttlsa_com.data
# Copying data for TABLE ttlsa_com.data
#...done.
或者
# mysqldbcopy --source=instance_3306 --destination=instance_3307  --regexp --exclude=^t  --exclude=^b  ttlsa_com   --drop-first      
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database ttlsa_com 
# Copying TABLE ttlsa_com.data
# Copying data for TABLE ttlsa_com.data
#...done.

# mysqldbcopy --source=instance_3306 --destination=instance_3307 --regexp --exclude=^[tb] ttlsa_com --drop-first

# Source on localhost: ... connected.

# Destination on localhost: ... connected.

# Copying database ttlsa_com

# Copying TABLE ttlsa_com.data

# Copying data for TABLE ttlsa_com.data

#...done.

或者

# mysqldbcopy --source=instance_3306 --destination=instance_3307 --regexp --exclude=^t --exclude=^b ttlsa_com --drop-first

# Source on localhost: ... connected.

# Destination on localhost: ... connected.

# Copying database ttlsa_com

# Copying TABLE ttlsa_com.data

# Copying data for TABLE ttlsa_com.data

#...done.

本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

权限

源服务器：SELECT, SHOW VIEW, EVENT ，TRIGGER

目标服务器：CREATE, ALTER, SELECT, INSERT, UPDATE, LOCK TABLES, DROP，SUPER权限取决于对象DEFINER的值。

如何复制数据库

不管你是出于什么目的，复制数据库到另一个地方，mysqldbcopy将是最佳最高效的可选方案。

复制整个实例的数据库：

# mysqldbcopy --source=instance_3306 --destination=instance_3307 --drop-first --all  -vvv
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Including all databases.
# Copying database test 
# Copying GRANTS from test
GRANT ALTER ON `test`.* TO ''@'%'
GRANT CREATE ON `test`.* TO ''@'%'
GRANT CREATE ROUTINE ON `test`.* TO ''@'%'
GRANT CREATE TEMPORARY TABLES ON `test`.* TO ''@'%'
GRANT CREATE VIEW ON `test`.* TO ''@'%'
GRANT DELETE ON `test`.* TO ''@'%'
GRANT DROP ON `test`.* TO ''@'%'
GRANT EVENT ON `test`.* TO ''@'%'
GRANT INDEX ON `test`.* TO ''@'%'
GRANT INSERT ON `test`.* TO ''@'%'
GRANT LOCK TABLES ON `test`.* TO ''@'%'
GRANT REFERENCES ON `test`.* TO ''@'%'
GRANT SELECT ON `test`.* TO ''@'%'
GRANT SHOW VIEW ON `test`.* TO ''@'%'
GRANT TRIGGER ON `test`.* TO ''@'%'
GRANT UPDATE ON `test`.* TO ''@'%'
# Copying database ttlsa_com 
# Dropping new object TABLE ttlsa_com.`data`
# WARNING: Unable to drop `data` from destination database (object may not exist): DROP TABLE `ttlsa_com`.`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
# Dropping new object TABLE ttlsa_com.`t_data`
# WARNING: Unable to drop `t_data` from destination database (object may not exist): DROP TABLE `ttlsa_com`.`t_data`
# Copying TABLE ttlsa_com.t_data
CREATE TABLE `t_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 DEFAULT CHARSET=latin1
# Copying database ttlsa_com_copy 
# Dropping new object TABLE ttlsa_com_copy.`data`
# WARNING: Unable to drop `data` from destination database (object may not exist): DROP TABLE `ttlsa_com_copy`.`data`
# Copying TABLE ttlsa_com_copy.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
# Copying data for TABLE ttlsa_com.t_data
# Copying data for TABLE ttlsa_com_copy.data
# Getting indexes for ttlsa_com_copy.data
#...done.
Time: 1.62 sec

# mysqldbcopy --source=instance_3306 --destination=instance_3307 --drop-first --all -vvv

# Source on localhost: ... connected.

# Destination on localhost: ... connected.

# Including all databases.

# Copying database test

# Copying GRANTS from test

GRANT ALTER ON `test`.* TO ''@'%'

GRANT CREATE ON `test`.* TO ''@'%'

GRANT CREATE ROUTINE ON `test`.* TO ''@'%'

GRANT CREATE TEMPORARY TABLES ON `test`.* TO ''@'%'

GRANT CREATE VIEW ON `test`.* TO ''@'%'

GRANT DELETE ON `test`.* TO ''@'%'

GRANT DROP ON `test`.* TO ''@'%'

GRANT EVENT ON `test`.* TO ''@'%'

GRANT INDEX ON `test`.* TO ''@'%'

GRANT INSERT ON `test`.* TO ''@'%'

GRANT LOCK TABLES ON `test`.* TO ''@'%'

GRANT REFERENCES ON `test`.* TO ''@'%'

GRANT SELECT ON `test`.* TO ''@'%'

GRANT SHOW VIEW ON `test`.* TO ''@'%'

GRANT TRIGGER ON `test`.* TO ''@'%'

GRANT UPDATE ON `test`.* TO ''@'%'

# Copying database ttlsa_com

# Dropping new object TABLE ttlsa_com.`data`

# WARNING: Unable to drop `data` from destination database (object may not exist): DROP TABLE `ttlsa_com`.`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

# Dropping new object TABLE ttlsa_com.`t_data`

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

# Copying TABLE ttlsa_com.t_data

CREATE TABLE `t_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 DEFAULT CHARSET=latin1

# Copying database ttlsa_com_copy

# Dropping new object TABLE ttlsa_com_copy.`data`

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

# Copying TABLE ttlsa_com_copy.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

# Copying data for TABLE ttlsa_com.t_data

# Copying data for TABLE ttlsa_com_copy.data

# Getting indexes for ttlsa_com_copy.data

#...done.

Time: 1.62 sec

复制整个数据库，不允许更改数据库名称。如果要重命名，必需每次指定。

可以设置的锁定类型有：

no-locks 不使用任何表锁
lock-all 使用表锁，无事务和一致性读
snapshot 默认，单一事务的一致性读

如果在复制过程中出现错误，复制中止了，目标数据库可能不一致了。在这种情况下，删除已复制的数据库，修复失败的原因，然后再次复制。

我的示例

mysqldbcopy --source=root:lhr@192.168.66.35:3317 --destination=root:lhr@192.168.66.35:3318 lhrdb:lhrdb1 --drop-first

拷贝一个数据库，MySQL 5.7需要设置以下参数：

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

set global log_bin_trust_function_creators=on

[root@docker35 ~]# mysqldbcopy --source=root:lhr@192.168.66.35:3317 --destination=root:lhr@192.168.66.35:3317 lhrdb:lhrdb1 --drop-first
# Source on 192.168.66.35: ... connected.
# Destination on 192.168.66.35: ... connected.
# Copying database lhrdb renamed as lhrdb1
# Copying TABLE lhrdb.events_list
# Copying TABLE lhrdb.grade
# Copying TABLE lhrdb.innodb_table
# Copying TABLE lhrdb.isam_table
# Copying TABLE lhrdb.nba
# Copying TABLE lhrdb.pet
# Copying TABLE lhrdb.scores_tb
# Copying TABLE lhrdb.t
# Copying TABLE lhrdb.t1
# Copying TABLE lhrdb.temp
# Copying TABLE lhrdb.time
# Copying TABLE lhrdb.t_rowid
# Copying FUNCTION lhrdb.rand_string
# Copying PROCEDURE lhrdb.mock_innodb
# Copying PROCEDURE lhrdb.mock_isam
# Copying VIEW lhrdb.vw_ob_lhr
# Copying data for TABLE lhrdb.events_list
# Copying data for TABLE lhrdb.grade
# Copying data for TABLE lhrdb.innodb_table
# Copying data for TABLE lhrdb.isam_table
# Copying data for TABLE lhrdb.nba
# Copying data for TABLE lhrdb.pet
# Copying data for TABLE lhrdb.scores_tb
# Copying data for TABLE lhrdb.t
# Copying data for TABLE lhrdb.t1
# Copying data for TABLE lhrdb.temp
# Copying data for TABLE lhrdb.time
# Copying data for TABLE lhrdb.t_rowid
# Copying TRIGGER lhrdb.trig1
# Copying EVENT lhrdb.event_minute
#...done.

[root@docker35 ~]# mysqldbcopy --source=root:lhr@192.168.66.35:3317 --destination=root:lhr@192.168.66.35:3317 lhrdb:lhrdb1 --drop-first

# Source on 192.168.66.35: ... connected.

# Destination on 192.168.66.35: ... connected.

# Copying database lhrdb renamed as lhrdb1

# Copying TABLE lhrdb.events_list

# Copying TABLE lhrdb.grade

# Copying TABLE lhrdb.innodb_table

# Copying TABLE lhrdb.isam_table

# Copying TABLE lhrdb.nba

# Copying TABLE lhrdb.pet

# Copying TABLE lhrdb.scores_tb

# Copying TABLE lhrdb.t

# Copying TABLE lhrdb.t1

# Copying TABLE lhrdb.temp

# Copying TABLE lhrdb.time

# Copying TABLE lhrdb.t_rowid

# Copying FUNCTION lhrdb.rand_string

# Copying PROCEDURE lhrdb.mock_innodb

# Copying PROCEDURE lhrdb.mock_isam

# Copying VIEW lhrdb.vw_ob_lhr

# Copying data for TABLE lhrdb.events_list

# Copying data for TABLE lhrdb.grade

# Copying data for TABLE lhrdb.innodb_table

# Copying data for TABLE lhrdb.isam_table

# Copying data for TABLE lhrdb.nba

# Copying data for TABLE lhrdb.pet

# Copying data for TABLE lhrdb.scores_tb

# Copying data for TABLE lhrdb.t

# Copying data for TABLE lhrdb.t1

# Copying data for TABLE lhrdb.temp

# Copying data for TABLE lhrdb.time

# Copying data for TABLE lhrdb.t_rowid

# Copying TRIGGER lhrdb.trig1

# Copying EVENT lhrdb.event_minute

#...done.

打赏赞(1)

标签： MySQL mysql-utilities mysqldbcopy

小麦苗

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

发表回复取消回复

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

2023年 3月
一	二	三	四	五	六	日
	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	31

嘿，我是小麦，需要帮助随时找我哦

18509239930
个人微信
麦老师QQ聊天
个人邮箱
点击加入QQ群
个人微店
回到顶部

小麦苗

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

mysqldbcopy

实例

权限

如何复制数据库

我的示例

相关文章

您可能还喜欢...

发表回复取消回复

博客寄语

博客概况

搜标题

搜索本网站

标签云☁

博客日历

博客归档

博客分类

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

mysqldbcopy

实例

权限

如何复制数据库

我的示例

相关文章

您可能还喜欢...

MySQL主从复制延迟的原因及解决办法

MySQL高可用之Xenon介绍

MySQL安装与卸载

发表回复 取消回复

博客寄语

博客概况

搜标题

搜索本网站

标签云☁

博客日历

博客归档

博客分类

发表回复取消回复