MySQL管理工具MySQL Utilities — 如何复制数据库(7)

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

复制整个实例的数据库：文章源自运维生存时间-https://www.ttlsa.com/mysql/how-to-copy-of-a-database-on-the-same-server/

# mysqldbcopy --source=instance_3306 --destination=instance_3307 --drop-first --all  -vvv
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Including all databases.
# Copying database test 
# Copying GRANTS from test
GRANT ALTER ON `test`.* TO ''@'%'
GRANT CREATE ON `test`.* TO ''@'%'
GRANT CREATE ROUTINE ON `test`.* TO ''@'%'
GRANT CREATE TEMPORARY TABLES ON `test`.* TO ''@'%'
GRANT CREATE VIEW ON `test`.* TO ''@'%'
GRANT DELETE ON `test`.* TO ''@'%'
GRANT DROP ON `test`.* TO ''@'%'
GRANT EVENT ON `test`.* TO ''@'%'
GRANT INDEX ON `test`.* TO ''@'%'
GRANT INSERT ON `test`.* TO ''@'%'
GRANT LOCK TABLES ON `test`.* TO ''@'%'
GRANT REFERENCES ON `test`.* TO ''@'%'
GRANT SELECT ON `test`.* TO ''@'%'
GRANT SHOW VIEW ON `test`.* TO ''@'%'
GRANT TRIGGER ON `test`.* TO ''@'%'
GRANT UPDATE ON `test`.* TO ''@'%'
# Copying database ttlsa_com 
# Dropping new object TABLE ttlsa_com.`data`
# WARNING: Unable to drop `data` from destination database (object may not exist): DROP TABLE `ttlsa_com`.`data`
# Copying TABLE ttlsa_com.data
CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` char(30) NOT NULL DEFAULT '',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
# Dropping new object TABLE ttlsa_com.`t_data`
# WARNING: Unable to drop `t_data` from destination database (object may not exist): DROP TABLE `ttlsa_com`.`t_data`
# Copying TABLE ttlsa_com.t_data
CREATE TABLE `t_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` char(30) NOT NULL DEFAULT '',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
# Copying database ttlsa_com_copy 
# Dropping new object TABLE ttlsa_com_copy.`data`
# WARNING: Unable to drop `data` from destination database (object may not exist): DROP TABLE `ttlsa_com_copy`.`data`
# Copying TABLE ttlsa_com_copy.data
CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` char(30) NOT NULL DEFAULT '',
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`value`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
# Copying data for TABLE ttlsa_com.data
# Getting indexes for ttlsa_com.data
# Copying data for TABLE ttlsa_com.t_data
# Copying data for TABLE ttlsa_com_copy.data
# Getting indexes for ttlsa_com_copy.data
#...done.
Time: 1.62 sec

复制整个数据库，不允许更改数据库名称。如果要重命名，必需每次指定。文章源自运维生存时间-https://www.ttlsa.com/mysql/how-to-copy-of-a-database-on-the-same-server/

可以设置的锁定类型有：文章源自运维生存时间-https://www.ttlsa.com/mysql/how-to-copy-of-a-database-on-the-same-server/

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

如果在复制过程中出现错误，复制中止了，目标数据库可能不一致了。在这种情况下，删除已复制的数据库，修复失败的原因，然后再次复制。文章源自运维生存时间-https://www.ttlsa.com/mysql/how-to-copy-of-a-database-on-the-same-server/ 文章源自运维生存时间-https://www.ttlsa.com/mysql/how-to-copy-of-a-database-on-the-same-server/