當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql db 复制_MySQL管理工具MySQL Utilities — mysqldbcopy(6)

發布時間：2024/1/23 数据库 28 豆豆

生活随笔收集整理的這篇文章主要介紹了 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

# 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

# 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).

--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)

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)

# 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.

# 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.

權限

源服務器：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