MySQL – How to Restore Database with Different Name

backupMySQLmysql-5mysqldumprestore

How can we restore mysql database with different name from mysqldump file.
I dont't want to open dump file and edit it. Any other better methods?

Best Answer

You can let mysqldump create the dump in such a way that it does not create or select the database.

EXAMPLE : You are dumping the database db1 and loading it into database db2

This will put in the CREATE DATABASE and the USE commands in the dump

mysqldump -u... -p... --routines --triggers --databases db1 > /root/db1.sql

This will not put in the CREATE DATABASE and the USE commands in the dump (this is what you want)

mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql

You can load it into another database (such as db2) in one of four(4) ways:

OPTION 1

$ mysqldump -u... -p... --routines --triggers db1 | mysql -u... -p... -A -Ddb2

OPTION 2

$ mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql
$ mysql -u... -p... -A -Ddb2 < /root/db1.sql

OPTION 3

$ mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql
$ mysql -u... -p... -A -Ddb2
mysql> source /root/db1.sql

OPTION 4

$ mysqldump -u... -p... --routines --triggers db1 > /root/db1.sql
$ mysql -u... -p... -A
mysql> use db2
mysql> source /root/db1.sql

Give it a Try !!!

Related Question