如何在mysql数据库中复制模式

Question

目标

在mysql数据库中复制一个模式，而不转储sql文件并执行它们。

如果有一种方法可以用几个命令完成这项任务，那就太好了。

背景

有一些方法可以通过执行转储程序生成的sql来转储数据库并导入它。

但我想做的是执行一个类似copy schema abc to abc_bk的命令，并完成。

因为我必须通过phpmyadmin来完成，并且转储所有字段并重新导入它们太耗时了。

Answer 1

有一句话：

CREATE TABLE <name> LIKE <othername>;

但你必须一次一桌地做这件事。没有一条语句可以对架构中的所有表执行此操作。

您可以从INFORMATION_SCHEMA.tables:获得表格列表

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = <old-schema>;

然后，对于该查询返回的每个表，运行以下语句：

CREATE TABLE <new-schema>.<table-name> LIKE <old-schema>.<table-name>;

您必须编写一些代码来执行循环来执行此操作。

---

回复您的评论：

如果您也想复制数据，可以使用INSERT。。。选择。

INSERT INTO <new-schema>.<table-name> 
SELECT * FROM <old-schema>.<table-name>;

这将需要一段时间，与表中需要更新的行数和索引数成比例(即，每行的写入成本乘以索引数(。

请注意，当INSERT。。。SELECT正在运行。如果在执行此操作时需要继续写入旧表，我建议使用pt archiver或pt表同步来进行复制。

Answer 2

在我的案例中，我想在现有数据库的基础上创建一个新的数据库，用于集成测试。这基本上是比尔·卡文上面回答的一个充实的版本。

请注意，我的解决方案不会复制数据。如果您需要该功能，添加到脚本中应该不会太困难。

你可以在这里引用GitHub Gist。

以下是MySQL代码供快速参考：

-- this procedure needs to be run somewhere, so just select a database
USE `existing_db`;
DELIMITER $$
DROP PROCEDURE IF EXISTS `exec`$$
DROP PROCEDURE IF EXISTS `copy_schema`$$
-- simple procedure to allow statement strings to be built and executed easily
-- using `concat`
CREATE PROCEDURE exec(stmt_text TEXT)
BEGIN
SET @sql_text = stmt_text;
PREPARE stmt FROM @sql_text;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END $$
-- Copies the database `from_db` into a new database, `to_db`.
-- Be careful: This procedure DROPs `to_db` before copying data
CREATE PROCEDURE copy_schema(from_db VARCHAR(64), to_db VARCHAR(64))
BEGIN
-- declare variables to be used throughout the procedure
DECLARE curr_table_name VARCHAR(64) DEFAULT NULL;
DECLARE done TINYINT DEFAULT FALSE;
-- This is a cursor that will point to every row in
-- INFORMATION_SCHEMA.TABLES
-- Each row corresponds to a table in `from_db`, so this will effectively
-- give us a foreach loop through the table names
DECLARE table_cursor
CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = from_db;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- initialize `to_db` before attempting to copy data into it
CALL exec(concat('DROP DATABASE IF EXISTS ', to_db));
CALL exec(concat('CREATE DATABASE ', to_db));
OPEN table_cursor;
-- Loop through all table names
table_loop:
LOOP
FETCH NEXT FROM table_cursor INTO curr_table_name;
-- Break if no more results
IF done THEN
LEAVE table_loop;
ELSE
-- Call `CREATE TABLE` for each table, copying the schema from `from_db`
CALL exec(concat('CREATE TABLE ', to_db, '.', curr_table_name, ' LIKE ', from_db, '.', curr_table_name));
END IF;
END LOOP;
CLOSE table_cursor;
END $$
DELIMITER ;
-- EXAMPLE USAGE:
CALL copy_schema('existing_db', 'existing_db_copy');