克隆 MySQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5551301/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Clone MySQL database
提问by Vinod HC
I have database on a server with 120 tables.
我在有 120 个表的服务器上有数据库。
I want to clone the whole database with a new db name and the copied data.
我想用新的数据库名称和复制的数据克隆整个数据库。
Is there an efficient way to do this?
有没有一种有效的方法来做到这一点？
回答by
$ mysqldump yourFirstDatabase -u user -ppassword > yourDatabase.sql
$ mysql yourSecondDatabase -u user -ppassword < yourDatabase.sql
回答by Kai Sternad
mysqldump -u <user> --password=<password> <DATABASE_NAME> | mysql -u <user> --password=<password> -h <hostname> <DATABASE_NAME_NEW>
回答by user570605
Like accepted answer but without .sql files:
喜欢接受的答案，但没有 .sql 文件：
mysqldump sourcedb -u <USERNAME> -p<PASS> | mysql destdb -u <USERNAME> -p<PASS>
回答by akos
In case you use phpMyAdmin
如果您使用 phpMyAdmin
- Select the database you wish to copy (by clicking on the database from the phpMyAdmin home screen).
- Once inside the database, select the Operations tab.
- Scroll down to the section where it says "Copy database to:"
- Type in the name of the new database.
- Select "structure and data" to copy everything. Alternately, you can select "Structure only" if you want the columns but not the data.
- Check the box "CREATE DATABASE before copying" to create a new database.
- Check the box "Add AUTO_INCREMENT value."
- Click on the Go button to proceed.
- 选择您要复制的数据库（通过从 phpMyAdmin 主屏幕单击数据库）。
- 进入数据库后，选择“操作”选项卡。
- 向下滚动到“将数据库复制到：”部分
- 输入新数据库的名称。
- 选择“结构和数据”以复制所有内容。或者，如果您想要列而不是数据，您可以选择“仅结构”。
- 选中“复制前创建数据库”框以创建新数据库。
- 选中“添加 AUTO_INCREMENT 值”框。
- 单击“开始”按钮继续。
回答by Alexey F
There is mysqldbcopytool from the MySQL Utilities package. http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldbcopy.html
MySQL Utilities 包中有一个mysqldbcopy工具。 http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldbcopy.html
回答by bristweb
If you want to make sure it is an exact clone, the receiving database needs to be entirely cleared / dropped. This way, the new db only has the tables in your import file and nothing else. Otherwise, your receiving database could retain tables that weren't specified in your import file.
ex from prior answers:
如果要确保它是精确克隆，则需要完全清除/删除接收数据库。这样，新数据库只有您的导入文件中的表，没有别的。否则，您的接收数据库可能会保留未在您的导入文件中指定的表。
来自先前的答案：
DB1 == tableA, tableB
DB2 == tableB, tableC
DB1 imported to -> DB2
DB2 == tableA, tableB, tableC //true clone should not contain tableC
the change is easy with --databasesand --add-drop-database(see mysql docs). This adds the drop statement to the sqldump so your new database will be an exact replica:
使用--databases和更改很容易--add-drop-database（请参阅 mysql 文档）。这会将 drop 语句添加到 sqldump 中，因此您的新数据库将是一个精确的副本：
$ mysqldump -h $ip -u $user -p$pass --databases $dbname --add-drop-database > $file.sql
$ mysql -h $ip $dbname -u $user -p$pass < $file.sql
of course replace the $ variables and as always, no space between password and -p. For extra security, strip the -p$pass from your command
当然替换 $ 变量，并且一如既往，密码和 -p 之间没有空格。为了提高安全性，请从您的命令中删除 -p$pass
回答by vse.fm team
$newdb = (date('Y')-1);
$mysqli->query("DROP DATABASE `".$newdb."`;");
$mysqli->query("CREATE DATABASE `".$newdb."`;");
$query = "
SELECT
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE 'rds'
";
$result = $mysqli->query($query)->fetch_all(MYSQLI_ASSOC);
foreach($result as $val) {
echo $val['TABLE_NAME'].PHP_EOL;
$mysqli->query("CREATE TABLE `".$newdb."`.`".$val['TABLE_NAME']."` LIKE rds.`".$val['TABLE_NAME']."`");
$mysqli->query("INSERT `".$newdb."`.`".$val['TABLE_NAME']."` SELECT * FROM rds.`".$val['TABLE_NAME']."`");
}
