从源服务器上拷贝一个数据库到另一个目标服务器上。源服务器和目标服务器可以同一台,也可以是不同台。数据库名字也可以相同或不相同。如果源服务器和目标服务器同一台,那么数据库名字必需不一样的,也就是同一个实例下,不能有相同的数据库名。
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 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
CREATETABLE`data`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`value`char(30)NOTNULLDEFAULT'',
`count`int(11)DEFAULTNULL,
PRIMARYKEY(`value`),
KEY`id`(`id`)
)ENGINE=InnoDBAUTO_INCREMENT=7DEFAULTCHARSET=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
CHANGEMASTERTOMASTER_HOST='localhost',MASTER_USER='root',MASTER_PASSWORD='',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin-3306.000002',MASTER_LOG_POS=214;
#...done.
Time:0.72sec
# 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# mysql_config_editor set --login-path=instance_3308 --host=localhost --user=root --port=3308 --password
Enterpassword:
# 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
CREATETABLE`data`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`value`char(30)NOTNULLDEFAULT'',
`count`int(11)DEFAULTNULL,
PRIMARYKEY(`value`),
KEY`id`(`id`)
)ENGINE=InnoDBAUTO_INCREMENT=8DEFAULTCHARSET=latin1
# Copying data for TABLE ttlsa_com.data
# Getting indexes for ttlsa_com.data
# Connecting to the current server's master
CHANGEMASTERTOMASTER_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.80sec
--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:
[:]@[:][:] or
[:][:] or
path>[].
--destination=DESTINATION
connection information for destination server in the
form: [:]@[:][:]
or [:][:] or
path>[].
--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: [:] or
. 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).
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
--versionshowprogram's version number and exit
--help display a help message and exit
--license display program'slicenseandexit
--source=SOURCEconnectioninformationforsourceserverintheform:
[:]@[:][:]or
[:][:]or
path>[].
--destination=DESTINATION
connectioninformationfordestinationserverinthe
form:[:]@[:][:]
or[:][:]or
path>[].
--character-set=CHARSET
setstheclientcharacterset.Thedefaultis
retrievedfromtheservervariable
'character_set_client'.
-d,--drop-firstdropthenewdatabaseorobjectifitexists
-xEXCLUDE,--exclude=EXCLUDE
excludeoneormoreobjectsfromtheoperationusing
eitheraspecificname(e.g.db1.t1),aLIKEpattern
(e.g.db1.t%ordb%.%)oraREGEXPsearchpattern.To
useaREGEXPsearchpatternforallexclusions,you
mustalsospecifythe--regexpoption.Repeatthe
--excludeoptionformultipleexclusions.
-a,--all所有数据库
--skip=SKIP_OBJECTSspecifyobjectstoskipintheoperationintheform
ofacomma-separatedlist(nospaces).Validvalues=
tables,views,triggers,procedures,functions,
events,grants,data,create_db
-v,--verbosecontrolhowmuchinformationisdisplayed.e.g.,-v=
verbose,-vv=moreverbose,-vvv=debug
-q,--quietturnoffallmessagesforquietexecution.
--new-storage-engine=NEW_ENGINE
changealltablestousethisstorageengineif
storageengineexistsonthedestination.
--default-storage-engine=DEF_ENGINE
changealltablestousethisstorageengineifthe
originalstorageenginedoesnotexistonthe
destination.
--locking=LOCKINGchoosethelocktypefortheoperation:no-locks=do
notuseanytablelocks,lock-all=usetablelocks
butnotransactionandnoconsistentread,snaphot
(default):consistentreadusingasingletransaction.
-G,--basic-regexp,--regexp
use'REGEXP'operatortomatchpattern.Defaultisto
use'LIKE'.
--rpl-user=RPL_USERtheuserandpasswordforthereplicationuser
requirement,intheform:[:]or
.E.g.rpl:passwd
--rpl=RPL_MODE,--replication=RPL_MODE
includereplicationinformation.Choices:'master'=
includetheCHANGEMASTERcommandusingthesource
serverasthemaster,'slave'=includetheCHANGE
MASTERcommandforthesourceserver'smaster(only
worksifthesourceserverisaslave).
--ssl-ca=SSL_CAThepathtoafilethatcontainsalistoftrustedSSL
CAs.
--ssl-cert=SSL_CERTThenameoftheSSLcertificatefiletousefor
establishingasecureconnection.
--ssl-key=SSL_KEYThenameoftheSSLkeyfiletouseforestablishinga
secureconnection.
--skip-gtidskipcreationandexecutionofGTIDstatementsduring
copy.
--multiprocess=MULTIPROCESS
usemultiprocessing,numberofprocessestousefor
concurrentexecution.Specialvalues:0(numberof
processesequaltotheCPUsdetected)and1(default-
noconcurrency).
必需提供连接参数和赋予要访问对象的适当权限。
源服务器上要复制的数据库,所需要的权限有: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)
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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
# instance_3306
mysql>showtables;
+---------------------+
|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|
+---------------------+
15rowsinset(0.00sec)
# 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
CREATETABLE`bbs_categories`(
`cid`smallint(5)NOTNULLAUTO_INCREMENT,
`pid`smallint(5)NOTNULLDEFAULT'0',
`cname`varchar(30)DEFAULTNULLCOMMENT'分类名称',
`content`varchar(255)DEFAULTNULL,
`keywords`varchar(255)DEFAULTNULL,
`ico`varchar(128)DEFAULTNULL,
`master`varchar(100)NOTNULL,
`permit`varchar(255)DEFAULTNULL,
`listnum`mediumint(8)unsignedDEFAULT'0',
`clevel`varchar(25)DEFAULTNULL,
`cord`smallint(6)DEFAULTNULL,
PRIMARYKEY(`cid`,`pid`)
)ENGINE=MyISAMAUTO_INCREMENT=7DEFAULTCHARSET=utf8
# Copying TABLE ttlsa_com.bbs_comments
CREATETABLE`bbs_comments`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`fid`int(11)NOTNULLDEFAULT'0',
`uid`int(11)NOTNULLDEFAULT'0',
`content`text,
`replytime`char(10)DEFAULTNULL,
PRIMARYKEY(`id`,`fid`,`uid`)
)ENGINE=MyISAMAUTO_INCREMENT=371DEFAULTCHARSET=utf8
# Copying TABLE ttlsa_com.bbs_favorites
CREATETABLE`bbs_favorites`(
`id`mediumint(8)unsignedNOTNULLAUTO_INCREMENT,
`uid`mediumint(8)unsignedNOTNULLDEFAULT'0',
`favorites`mediumint(8)unsignedNOTNULLDEFAULT'0',
`content`mediumtextNOTNULL,
PRIMARYKEY(`id`,`uid`),
KEY`uid`(`uid`)
)ENGINE=MyISAMAUTO_INCREMENT=3DEFAULTCHARSET=utf8
# Copying TABLE ttlsa_com.bbs_forums
CREATETABLE`bbs_forums`(
`fid`int(11)NOTNULLAUTO_INCREMENT,
`cid`smallint(5)NOTNULLDEFAULT'0',
`uid`mediumint(8)NOTNULLDEFAULT'0',
`ruid`mediumint(8)DEFAULTNULL,
`title`varchar(128)DEFAULTNULL,
`keywords`varchar(255)DEFAULTNULL,
`content`text,
`addtime`int(10)DEFAULTNULL,
`updatetime`int(10)DEFAULTNULL,
`lastreply`int(10)DEFAULTNULL,
`views`int(10)DEFAULT'0',
`comments`smallint(8)DEFAULT'0',
`favorites`int(10)unsignedDEFAULT'0',
`closecomment`tinyint(1)DEFAULTNULL,
`is_top`tinyint(1)NOTNULLDEFAULT'0',
`is_hidden`tinyint(1)NOTNULLDEFAULT'0',
`ord`int(10)unsignedNOTNULLDEFAULT'0',
PRIMARYKEY(`fid`,`cid`,`uid`),
KEY`updatetime`(`updatetime`),
KEY`ord`(`ord`)
)ENGINE=MyISAMAUTO_INCREMENT=94DEFAULTCHARSET=utf8
# Copying TABLE ttlsa_com.bbs_links
CREATETABLE`bbs_links`(
`id`smallint(6)NOTNULLAUTO_INCREMENT,
`name`varchar(100)DEFAULTNULL,
`url`varchar(200)DEFAULTNULL,
`logo`varchar(200)DEFAULTNULL,
`is_hidden`tinyint(1)NOTNULLDEFAULT'0',
PRIMARYKEY(`id`)
)ENGINE=MyISAMAUTO_INCREMENT=2DEFAULTCHARSET=utf8
# Copying TABLE ttlsa_com.bbs_notifications
CREATETABLE`bbs_notifications`(
`nid`int(11)NOTNULLAUTO_INCREMENT,
`fid`int(11)DEFAULTNULL,
`suid`int(11)DEFAULTNULL,
`nuid`int(11)NOTNULLDEFAULT'0',
`ntype`tinyint(1)DEFAULTNULL,
`ntime`int(10)DEFAULTNULL,
PRIMARYKEY(`nid`,`nuid`)
)ENGINE=MyISAMAUTO_INCREMENT=444DEFAULTCHARSET=utf8
# Copying TABLE ttlsa_com.bbs_page
CREATETABLE`bbs_page`(
`pid`tinyint(6)NOTNULLAUTO_INCREMENT,
`title`varchar(100)DEFAULTNULL,
`content`text,
`go_url`varchar(100)DEFAULTNULL,
`add_time`int(10)DEFAULTNULL,
`is_hidden`tinyint(1)DEFAULT'0',
PRIMARYKEY(`pid`)
)ENGINE=MyISAMAUTO_INCREMENT=6DEFAULTCHARSET=utf8
# Copying TABLE ttlsa_com.bbs_settings
CREATETABLE`bbs_settings`(
`id`tinyint(5)NOTNULLAUTO_INCREMENT,
`title`varchar(255)NOTNULLDEFAULT'',
`value`textNOTNULL,
`type`tinyint(3)NOTNULLDEFAULT'0',
PRIMARYKEY(`id`,`title`,`type`)
)ENGINE=MyISAMAUTO_INCREMENT=14DEFAULTCHARSET=utf8
# Copying TABLE ttlsa_com.bbs_tags
CREATETABLE`bbs_tags`(
`tag_id`int(10)NOTNULLAUTO_INCREMENT,
`tag_title`varchar(30)NOTNULL,
`forums`int(10)NOTNULLDEFAULT'0',
PRIMARYKEY(`tag_id`),
UNIQUEKEY`tag_title`(`tag_title`)
)ENGINE=MyISAMAUTO_INCREMENT=185DEFAULTCHARSET=utf8
# Copying TABLE ttlsa_com.bbs_tags_relation
CREATETABLE`bbs_tags_relation`(
`tag_id`int(10)NOTNULLDEFAULT'0',
`fid`int(10)DEFAULTNULL,
KEY`tag_id`(`tag_id`),
KEY`fid`(`fid`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8
# Copying TABLE ttlsa_com.bbs_users
CREATETABLE`bbs_users`(
`uid`int(11)NOTNULLAUTO_INCREMENT,
`username`varchar(20)DEFAULTNULL,
`password`char(32)DEFAULTNULL,
`openid`char(32)NOTNULL,
`email`varchar(50)DEFAULTNULL,
`avatar`varchar(100)DEFAULTNULL,
`homepage`varchar(50)DEFAULTNULL,
`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)NOTNULLDEFAULT'0',
`regtime`int(10)DEFAULTNULL,
`lastlogin`int(10)DEFAULTNULL,
`lastpost`int(10)DEFAULTNULL,
`qq`varchar(20)DEFAULTNULL,
`group_type`tinyint(3)NOTNULLDEFAULT'0',
`gid`tinyint(3)NOTNULLDEFAULT'3',
`ip`char(15)DEFAULTNULL,
`location`varchar(128)DEFAULTNULL,
`token`varchar(40)DEFAULTNULL,
`introduction`text,
`is_active`tinyint(1)NOTNULLDEFAULT'1',
PRIMARYKEY(`uid`,`group_type`)
)ENGINE=MyISAMAUTO_INCREMENT=109DEFAULTCHARSET=utf8
# Copying TABLE ttlsa_com.bbs_user_follow
CREATETABLE`bbs_user_follow`(
`follow_id`int(10)unsignedNOTNULLAUTO_INCREMENT,
`uid`int(10)unsignedNOTNULLDEFAULT'0',
`follow_uid`int(10)unsignedNOTNULLDEFAULT'0',
`addtime`int(10)NOTNULLDEFAULT'0',
PRIMARYKEY(`follow_id`,`uid`,`follow_uid`)
)ENGINE=MyISAMAUTO_INCREMENT=9DEFAULTCHARSET=utf8
# Copying TABLE ttlsa_com.bbs_user_groups
CREATETABLE`bbs_user_groups`(
`gid`int(11)NOTNULLAUTO_INCREMENT,
`group_type`tinyint(3)NOTNULLDEFAULT'0',
`group_name`varchar(50)DEFAULTNULL,
`usernum`int(11)NOTNULL,
PRIMARYKEY(`gid`,`group_type`)
)ENGINE=MyISAMAUTO_INCREMENT=4DEFAULTCHARSET=utf8
# Copying TABLE ttlsa_com.t_data
CREATETABLE`t_data`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`value`char(30)NOTNULLDEFAULT'',
`count`int(11)DEFAULTNULL,
PRIMARYKEY(`value`),
KEY`id`(`id`)
)ENGINE=InnoDBDEFAULTCHARSET=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.32sec
Databasechanged
mysql>usettlsa_com
mysql>showtables;
+---------------------+
|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|
+---------------------+
14rowsinset(0.00sec)
默认情况下是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)
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
32
33
34
35
36
37
38
39
# 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
CREATETABLE`data`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`value`char(30)NOTNULLDEFAULT'',
`count`int(11)DEFAULTNULL,
PRIMARYKEY(`value`),
KEY`id`(`id`)
)ENGINE=InnoDBAUTO_INCREMENT=7DEFAULTCHARSET=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
CREATETABLE`t_data`(
`id`int(11)NOTNULLAUTO_INCREMENT,
`value`char(30)NOTNULLDEFAULT'',
`count`int(11)DEFAULTNULL,
PRIMARYKEY(`value`),
KEY`id`(`id`)
)ENGINE=InnoDBDEFAULTCHARSET=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.71sec
# instance_3307
mysql>showtables;
+---------------------+
|Tables_in_ttlsa_com|
+---------------------+
|data|
|t_data|
+---------------------+
2rowsinset(0.00sec)
上面,那样写有问题的。
使用正则排除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.
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
32
33
# 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.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 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.
权限
源服务器:SELECT, SHOW VIEW, EVENT ,TRIGGER
目标服务器:CREATE, ALTER, SELECT, INSERT, UPDATE, LOCK TABLES, DROP,SUPER权限取决于对象DEFINER的值。