Cloning a MySQL database on the same MySql instanc

问题:

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

mysqldump -u root -p sitedb1 >~/db_name.sql

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

回答1:

As the manual says in Copying Databases you can pipe the dump directly into the mysql client:

mysqldump db_name | mysql new_db_name

If you're using MyISAM you could copy the files, but I wouldn't recommend it. It's a bit dodgy.

Integrated from various good other answers

Both mysqldump and mysql commands accept options for setting connection details (and much more), like:

mysqldump -u <user name> --password=<pwd> <original db> | mysql -u <user name> -p <new db>

Also, if the new database is not existing yet, you have to create it beforehand (e.g. with echo "create database new_db_name" | mysql -u <dbuser> -p).

回答2:

Using MySQL Utilities

The MySQL Utilities contain the nice tool mysqldbcopy 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:

mysqldbcopy \
    --source=root:your_password@localhost \
    --destination=root:your_password@localhost \
    sitedb1:sitedb2

回答3:

$ mysqladmin create DB_name -u DB_user --password=DB_pass && \
    mysqldump -u DB_user --password=DB_pass DB_name | mysql -u DB_user --password=DB_pass -h DB_host DB_name

回答4:

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

mysqldump -u <user name> -p <pwd> <original db> | mysql -u <user name> <pwd> <new db>

e.g: mysqldump -u root test_db1 | mysql -u root test_db2

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

回答5:

You could use (in pseudocode):

FOREACH tbl IN db_a:
    CREATE TABLE db_b.tbl LIKE db_a.tbl;
    INSERT INTO db_b.tbl SELECT * FROM db_a.tbl;

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 CREATE TABLE.

回答6:

First create the duplicate database:

CREATE DATABASE duplicateddb;

Make sure the permissions etc are all in place and:

mysqldump -u admin -p originaldb | mysql -u backup -p password duplicateddb;

回答7:

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

:~$> mysqldump -u root -p db1 > dump.sql
:~$> mysqladmin -u root -p create db2
:~$> mysql -u root -p db2 < dump.sql

回答8:

You can do something like the following:

mysqldump -u[username] -p[password] database_name_for_clone 
 | mysql -u[username] -p[password] new_database_name

回答9:

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: http://dev.mysql.com/doc/refman/5.1/en/alter-database.html

回答10:

Its better to use mysqldbcopy command to copy database from one to another server or same server.

mysqldbcopy --source=root:root@localhost --destination=root:root@localhost database-name:database-name-clone

[MySQL]

回答11:

I don't think there is a method to do this. When PHPMyAdmin does this, it dumps the DB then re-inserts it under the new name.

回答12:

In addition to Greg's Answer.
the easiest and fastest way if the new_db_name doesn't yet exist.

echo "create database new_db_name" | mysql -u <user> -p <pwd> 
mysqldump -u <user> -p <pwd> db_name | mysql -u <user> -p <pwd> new_db_name

回答13:

A simple way to do so if you installed phpmyadmin:

Go to your database, select "operation" tab, and you can see the "copy database to" block. Use it and you can copy the database.

回答14:

Use 'mysqldbcopy' on the terminal. This case is nicely mentioned here. Example: Start the cmd prompt. Navigate to the bin folder of mySql Server. Fire the below query:

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldbcopy --source=root:root@localhost --destination=root:root@localhost master:master_clone

here I am trying to copy my 'master' db to 'master_clone' on localhost.