从源服务器上拷贝一个数据库到另一个目标服务器上。源服务器和目标服务器可以同一台，也可以是不同台。数据库名字也可以相同或不相同。如果源服务器和目标服务器同一台，那么数据库名字必需不一样的，也就是同一个实例下，不能有相同的数据库名。
mysqldbcopy 接受一个或多个数据库对。格式为db_name:new_db_name。分别表示源和目标。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
默认情况下，复制所有对象(如表、视图、触发器、事件、存储过程、函数和数据库级别权限)和数据到目标。可以有选择性的复制，如只复制部分对象，不复制数据等等。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
要针对性的复制，可以使用--exclude选项来排除。格式如下：db.*obj* 。也可以使用搜索模式，如--exclude=db1.trig1 排除单个触发器， --exclude=trig_排除所有以trig开头的对象。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
默认情况下，目标上使用的存储引擎与源相同。如果目标上使用另一种存储引擎，可以使用--new-storage-engine 选项来指定。如果目标服务器支持指定的引擎，那么所有表使用该引擎。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
如果目标服务器不支持源服务器所用的存储引擎，可以使用--default-storage-engine选项来指定默认使用的存储引擎。 --new-storage-engine选项的优先级高于--default-storage-engine。如果这两个选项都指定，然而又不支持指定的存储引擎，那么默认的代替。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
默认情况下，复制操作是使用一致性快照来读取源数据库。要改变锁定模式，可以使用--locking选项来指定锁定类型值。值no-locks关闭锁，lock-all只使用表锁。默认是snapshot。此外，使用WRITE锁，在复制过程中将锁定目标表。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
从主或者从服务器复制还可以包含复制语句。--rpl选项指定文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
- master 创建并执行CHANGE MASTER 语句，将目标服务器作为--source选项指定的服务器的从。在复制数据之前，执行 STOP SLAVE 语句。在复制完成后执行 CHANGE MASTER 和 START SLAVE语句。
- slave 创建和执行 CHANGE MASTER 语句，使目标服务器成为与--source选项指定的服务器的同一个主服务器的从。仅仅在源服务器是从有用。
语句的执行先后顺序，可以将general_log打开，可以看到每步的执行过程。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
# 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
文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
# 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
文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
--repl-user选项指定复制的用户名和密码。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
如果要复制的数据库的服务器上启用了GTIDs（GTID_MODE = ON），如果只复制其中一部分数据库，将会有警告信息产生。这是因为GTID报表生成包括所有数据库的gtids，不仅仅是某个的。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
如果有启用GTID，但是使用了--skip-gtid也会收到警告。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
如果启用了GTID，最好是复制所有的数据库。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
选项：
--version show program's version number and exit
--help display a help message and exit
--license display program's license and exit
--source=SOURCE connection information for source server in the form:
<user>[:<password>]@<host>[:<port>][:<socket>] or
<login-path>[:<port>][:<socket>] or <config-
path>[<[group]>].
--destination=DESTINATION
connection information for destination server in the
form: <user>[:<password>]@<host>[:<port>][:<socket>]
or <login-path>[:<port>][:<socket>] or <config-
path>[<[group]>].
--character-set=CHARSET
sets the client character set. The default is
retrieved from the server variable
'character_set_client'.
-d, --drop-first drop the new database or object if it exists
-x EXCLUDE, --exclude=EXCLUDE
exclude one or more objects from the operation using
either a specific name (e.g. db1.t1), a LIKE pattern
(e.g. db1.t% or db%.%) or a REGEXP search pattern. To
use a REGEXP search pattern for all exclusions, you
must also specify the --regexp option. Repeat the
--exclude option for multiple exclusions.
-a, --all 所有数据库
--skip=SKIP_OBJECTS specify objects to skip in the operation in the form
of a comma-separated list (no spaces). Valid values =
tables, views, triggers, procedures, functions,
events, grants, data, create_db
-v, --verbose control how much information is displayed. e.g., -v =
verbose, -vv = more verbose, -vvv = debug
-q, --quiet turn off all messages for quiet execution.
--new-storage-engine=NEW_ENGINE
change all tables to use this storage engine if
storage engine exists on the destination.
--default-storage-engine=DEF_ENGINE
change all tables to use this storage engine if the
original storage engine does not exist on the
destination.
--locking=LOCKING choose the lock type for the operation: no-locks = do
not use any table locks, lock-all = use table locks
but no transaction and no consistent read, snaphot
(default): consistent read using a single transaction.
-G, --basic-regexp, --regexp
use 'REGEXP' operator to match pattern. Default is to
use 'LIKE'.
--rpl-user=RPL_USER the user and password for the replication user
requirement, in the form: <user>[:<password>] or
<login-path>. E.g. rpl:passwd
--rpl=RPL_MODE, --replication=RPL_MODE
include replication information. Choices: 'master' =
include the CHANGE MASTER command using the source
server as the master, 'slave' = include the CHANGE
MASTER command for the source server's master (only
works if the source server is a slave).
--ssl-ca=SSL_CA The path to a file that contains a list of trusted SSL
CAs.
--ssl-cert=SSL_CERT The name of the SSL certificate file to use for
establishing a secure connection.
--ssl-key=SSL_KEY The name of the SSL key file to use for establishing a
secure connection.
--skip-gtid skip creation and execution of GTID statements during
copy.
--multiprocess=MULTIPROCESS
use multiprocessing, number of processes to use for
concurrent execution. Special values: 0 (number of
processes equal to the CPUs detected) and 1 (default -
no concurrency).
必需提供连接参数和赋予要访问对象的适当权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
源服务器上要复制的数据库，所需要的权限有：SELECT、SHOW VIEW、EVENT、TRIGGER，同时需要对mysql库有SELECT权限。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
目标服务器上所需要的权限有：CREATE、 ALTER、 SELECT、 INSERT、 UPDATE、 LOCK TABLES，如果有使用--drop-first选项就需要 DROP权限。如果二进制日志启用就要SUPER权限。CREATE VIEW 、CREATE ROUTINE、 EXECUTE、EVENT、TRIGGER、GRANT OPTION 、SUPER。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
在同一个实例上复制，--rpl选项是无效的，将会产生一个错误。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
当复制数据和包含GTID命令，可能会遇到"GTID_PURGED can only be set when GTID_EXECUTED is empty"错误。产生的原因是目标服务器不是一个干净的复制状态。解决办法是在复制之前，先在目标服务器上执行 RESET MASTER 命令，清空复制状态。文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
实例
在同一个实例上复制ttlsa_com库文章源自运维生存时间-https://www.ttlsa.com/mysql/mysqldbcopy-copy-database-between-servers/
如果要复制的数据库并不是所有的表是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)
默认情况下是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)
上面，那样写有问题的。
使用正则排除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.
使用正则排除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.
权限
源服务器：SELECT, SHOW VIEW, EVENT ，TRIGGER
目标服务器：CREATE, ALTER, SELECT, INSERT, UPDATE, LOCK TABLES, DROP，SUPER权限取决于对象DEFINER的值。
我的微信
微信公众号
扫一扫关注运维生存时间公众号，获取最新技术文章~
评论