blocks|key|1598644|text|使用MySQL实用程序|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1598645|MySQL实用程序包含nice工具mysqldbcopy，该工具默认情况下将包含所有相关对象(“表、视图、触发器、事件、过程、函数和数据库级授权”)的数据库和数据从一台DB服务器复制到相同或另一台DB服务器。有许多选项可用于自定义实际复制的内容。|offset|length|style|CODE|1598646|所以，为了回答操作员的问题：|1598647|mysqldbcopy+\
++++--source=root:your_password@localhost+\
++++--destination=root:your_password@localhost+\
++++sitedb1:sitedb2|code-block|syntax|javascript|1598648|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldbcopy.html^0|0|H|B|H|B|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@$D|Y|E|Z|F|G]]|9|@$D|10|E|11|1|12]]|A|$]]|$1|H|3|I|5|6|7|13|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|14|8|@]|9|@]|A|$M|N]]|$1|O|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

<h2>Using MySQL Utilities</h2>

The MySQL Utilities contain the nice tool <a href="http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldbcopy.html"><code>mysqldbcopy</code></a> which by default copies a DB including all related objects (“tables, views, triggers, events, procedures, functions, and database-level grants”) and data from one DB server to the same or to another DB server. There are lots of options available to customize what is actually copied.

So, to answer the OP’s question:

<pre class="lang-sh prettyprint-override"><code>mysqldbcopy \
 --source=root:your_password@localhost \
 --destination=root:your_password@localhost \
 sitedb1:sitedb2
</code></pre>

blocks|key|1598955|text|最好也是最简单的方法是在终端中输入这些命令，并将权限设置为root用户。对我来说很有效！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1598956|:~$>+mysqldump+-u+root+-p+db1+>+dump.sql
:~$>+mysqladmin+-u+root+-p+create+db2
:~$>+mysql+-u+root+-p+db2+<+dump.sql|code-block|syntax|javascript|1598957|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Best and easy way is to enter these commands in your terminal and set permissions to the root user. Works for me..! 

<pre><code>:~$&gt; mysqldump -u root -p db1 &gt; dump.sql
:~$&gt; mysqladmin -u root -p create db2
:~$&gt; mysql -u root -p db2 &lt; dump.sql
</code></pre>

blocks|key|1598414|text|mysqladmin+create+DB_name+-u+DB_user+--password=DB_pass+&&+\
++++++++mysqldump+-u+DB_user+--password=DB_pass+DB_name+%7C+\
++++++++mysql+++++-u+DB_user+--password=DB_pass+-h+DB_host+DB_name|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1598415|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre class="lang-sh prettyprint-override"><code>mysqladmin create DB_name -u DB_user --password=DB_pass &amp;&amp; \
 mysqldump -u DB_user --password=DB_pass DB_name | \
 mysql -u DB_user --password=DB_pass -h DB_host DB_name
</code></pre>

blocks|key|1598698|text|您需要从终端/命令提示符运行该命令。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1598699|mysqldump+-u+<user+name>+-p+<pwd>+<original+db>+%7C+mysql+-u+<user+name>+<pwd>+<new+db>|code-block|syntax|javascript|1598700|例如：mysqldump+-u+root+test_db1+%7C+mysql+-u+root+test_db2|offset|length|style|CODE|1598701|这会将根复制到test_db2，并授予对‘test_db1’@‘localhost’的访问权限|1598702|entityMap^0|0|0|3|1F|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

You need to run the command from terminal / command prompt. 

<pre><code>mysqldump -u &lt;user name&gt; -p &lt;pwd&gt; &lt;original db&gt; | mysql -u &lt;user name&gt; &lt;pwd&gt; &lt;new db&gt;
</code></pre>

e.g: <code>mysqldump -u root test_db1 | mysql -u root test_db2</code>

This copies test_db1 to test_db2 and grant the access to 'root'@'localhost'

blocks|key|1598400|text|您可以使用(在伪代码中)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1598401|FOREACH+tbl+IN+db_a:
++++CREATE+TABLE+db_b.tbl+LIKE+db_a.tbl;
++++INSERT+INTO+db_b.tbl+SELECT+*+FROM+db_a.tbl;|code-block|syntax|javascript|1598402|我不使用CREATE+TABLE的原因是...选择...语法是为了保留索引。当然，这只是复制表。视图和过程不会被复制，尽管它可以以相同的方式完成。|1598403|参见CREATE+TABLE。|offset|length|1598404|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/create-table.html^0|0|0|0|2|C|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

You could use (in pseudocode):

<pre><code>FOREACH tbl IN db_a:
 CREATE TABLE db_b.tbl LIKE db_a.tbl;
 INSERT INTO db_b.tbl SELECT * FROM db_a.tbl;
</code></pre>

The reason I'm not using the CREATE TABLE ... SELECT ... syntax is to preserve indices. Of course this only copies tables. Views and procedures are not copied, although it can be done in the same manner.

See <a href="http://dev.mysql.com/doc/refman/5.1/en/create-table.html" rel="noreferrer">CREATE TABLE</a>.

blocks|key|1383498|text|首先创建重复数据库：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1383499|CREATE+DATABASE+duplicateddb;|code-block|syntax|javascript|1383500|确保权限等都已就绪，并且：|1383501|mysqldump+-u+admin+-p+originaldb+%7C+mysql+-u+backup+-p+password+duplicateddb;|1383502|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

First create the duplicate database:

<pre><code>CREATE DATABASE duplicateddb;
</code></pre>

Make sure the permissions etc are all in place and:

<pre><code>mysqldump -u admin -p originaldb | mysql -u backup -p password duplicateddb;
</code></pre>

blocks|key|1599063|text|如果您安装了phpmyadmin，则执行此操作的简单方法|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1599064|转到您的数据库，选择“操作”选项卡，您可以看到“复制数据库到”块。使用它，您就可以复制数据库。|1599065|entityMap^0|6|A|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|M|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|N|8|@]|D|@]|E|$]]]|I|$]]

A simple way to do so if you installed <code>phpmyadmin</code>:
Go to your database, select &quot;operation&quot; tab, and you can see the &quot;copy database to&quot; block. Use it and you can copy the database.

blocks|key|1598901|text|您可以执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1598902|mysqldump+-u[username]+-p[password]+database_name_for_clone+
+%7C+mysql+-u[username]+-p[password]+new_database_name|code-block|syntax|javascript|1598903|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can do something like the following:

<pre><code>mysqldump -u[username] -p[password] database_name_for_clone 
 | mysql -u[username] -p[password] new_database_name
</code></pre>

blocks|key|1598545|text|此语句在MySQL+5.1.7中添加，但被发现是危险的，并在MySQL+5.1.23中删除。它的目的是支持升级5.1版本之前的数据库，以便使用5.1中实现的编码将数据库名称映射到数据库目录名称。但是，使用该语句可能会导致数据库内容丢失，这就是删除该语句的原因。在存在重命名数据库的早期版本中，请不要使用重命名数据库。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1598546|要执行使用新编码升级数据库名称的任务，请改用ALTER+database+db_name+UPGRADE+DIRECTORY+NAME：http://dev.mysql.com/doc/refman/5.1/en/alter-database.html|offset|length|1598547|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.1/en/alter-database.html^0|0|1W|1M|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23. It was intended to enable upgrading pre-5.1 databases to use the encoding implemented in 5.1 for mapping database names to database directory names. However, use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present.

To perform the task of upgrading database names with the new encoding, use ALTER DATABASE db_name UPGRADE DATA DIRECTORY NAME instead: <a href="http://dev.mysql.com/doc/refman/5.1/en/alter-database.html" rel="nofollow">http://dev.mysql.com/doc/refman/5.1/en/alter-database.html</a>

blocks|key|1598763|text|除了Greg's+answer之外，如果new_db_name还不存在，这是最简单、最快的方法：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1598764|echo+"create+database+new_db_name"+%7C+mysql+-u+<user>+-p+<pwd>+
mysqldump+-u+<user>+-p+<pwd>+db_name+%7C+mysql+-u+<user>+-p+<pwd>+new_db_name|code-block|syntax|javascript|1598765|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/675299^0|K|B|2|D|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@$9|V|A|W|1|X]]|E|$]]|$1|F|3|G|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|L|$M|$5|N|O|P|E|$Q|R]]]]

In addition to <a href="https://stackoverflow.com/a/675299">Greg's answer</a>, this is the easiest and fastest way if the <code>new_db_name</code> doesn't yet exist: 

<pre><code>echo "create database new_db_name" | mysql -u &lt;user&gt; -p &lt;pwd&gt; 
mysqldump -u &lt;user&gt; -p &lt;pwd&gt; db_name | mysql -u &lt;user&gt; -p &lt;pwd&gt; new_db_name
</code></pre>

blocks|key|1384153|text|如果您的原始数据库中有触发器，则可以通过在导入之前通过管道传输替换触发器来避免“触发器已存在”错误：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1384154|mysqldump+-u+olddbuser+-p+-d+olddbname+%7C+sed+"s/`olddbname`./`newdbname`./"+%7C+mysql+-u+newdbuser+-p+-D+newdbname|code-block|syntax|javascript|1384155|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you have triggers in your original database, you can avoid the "Trigger already exists" error by piping a replacement before the import:

<pre><code>mysqldump -u olddbuser -p -d olddbname | sed "s/`olddbname`./`newdbname`./" | mysql -u newdbuser -p -D newdbname
</code></pre>

blocks|key|1384214|text|使用MySQL工作台，您可以使用数据库>迁移向导将数据库复制到同一服务器实例或其他服务器实例。我相信它可以在服务器端工作，所以它应该是复制大型数据库的一个很好的解决方案。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1384215|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Using MySQL Workbench you can use Database &gt; Migration Wizard to copy database to the same or to the other server instance. I believe it works server-side so it should be a good solution for duplicating large databases.

blocks|key|1599194|text|您可以执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1599195|CREATE+DATABASE+copy_of_db;
create+table+copy_of_db.table+LIKE+source_db.table;

If+you+want+to+copy+data+too:
INSERT+INTO+copy_of_db.table+SELECT+*+FROM+source_db.table;|code-block|syntax|javascript|1599196|对所有表、函数、过程等重复上述步骤|1599197|(mysqldump是正确的方法，但在许多情况下，这是一种快速而肮脏的解决方案)|1599198|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

You can do:
<pre><code>CREATE DATABASE copy_of_db;
create table copy_of_db.table LIKE source_db.table;

If you want to copy data too:
INSERT INTO copy_of_db.table SELECT * FROM source_db.table;
</code></pre>
Repeat for all tables, functions, procedures, etc
(mysqldump is the proper way, but this is a quick and dirty solution useful in many cases)

I would like to write a script which copies my current database <code>sitedb1</code> to <code>sitedb2</code> on the same mysql database instance. I know I can dump the sitedb1 to a sql script:

<pre><code>mysqldump -u root -p sitedb1 &gt;~/db_name.sql
</code></pre>

and then import it to <code>sitedb2</code>. 
Is there an easier way, without dumping the first database to a sql file?

MySQL: Cloning a MySQL database on the same MySql instance

我想写一个脚本，将我当前的数据库sitedb1复制到同一个mysql数据库实例上的sitedb2。我知道我可以将sitedb1转储为sql脚本：mysqldump -u root -p sitedb1 >~/db_name.sql然后将其导入到sitedb2。有没有一种更简单的方法，不将第一个数据库转储到sql文件？

问MySQL:在同一MySQL实例上克隆MySql数据库
EN

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL:在同一MySQL实例上克隆MySql数据库EN

回答 13

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL:在同一MySQL实例上克隆MySql数据库
EN