mysql db 复制_MySQL管理工具MySQL Utilities — mysqldbcopy(6)
從源服務器上拷貝一個數據庫到另一個目標服務器上。源服務器和目標服務器可以同一臺，也可以是不同臺。數據庫名字也可以相同或不相同。如果源服務器和目標服務器同一臺，那么數據庫名字必需不一樣的，也就是同一個實例下，不能有相同的數據庫名。
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的值。
總結
以上是生活随笔為你收集整理的mysql db 复制_MySQL管理工具MySQL Utilities — mysqldbcopy(6)的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： mysql文件查看工具_OpenDBVi
- 下一篇： thinkphp项目mysql类关系_T