MySQL: Cloning a MySQL database on the same MySql instance

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?

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](

[To see links please register here]

).

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:

[To see links please register here]

## Using MySQL Utilities ##

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

<!-- language: lang-sh -->

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


[1]:

[To see links please register here]

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'

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

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;

You can do something like the following:

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

In addition to [Greg's answer](

[To see links please register here]

), this is 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

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

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