Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

up vote 10 down vote accepted

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 !!!

share|improve this answer
    
Thanks RolandoMySQLDBA,,, It worked..:) –  Praveen Prasannan Dec 8 '11 at 13:16

I have done this once, long time ago.

When you export all your data, there is an option to set the database name in the begining of the file, something as: "use database x"

So, you can change this declaration.

share|improve this answer

Here is the shell script that will allow you to add suffix / affix to all the schema names on the fly.

http://snippets.dzone.com/posts/show/13749

share|improve this answer

I am a big fan of dump, edit and insert. but you do not have to open the text file (dump file) to change it (this is especially usefull when it is several milion lines long). if you want to dump the database MYDATABASE.

mysqldump MYDATABASE > mydump.sql

then use sed to replace the old database name with new one like this

sed -i 's/MYDATABASE/MYNEWDATABASE/g' mydump.sql

then you can simply create the new database and import it again, and it will create all tables with in the new databae MYNEWDATABASE'

mysqladmin create MYNEWDATABASE

mysql MYNEWDATABASE < mydump.sql
share|improve this answer
    
I think the question was about restoring an enitre database but with a different name, not just copying one table within an existing DB. –  Michael Green Jul 3 at 10:54
    
oh.. good point, have fixed. (not sure if the sed part if much help now, but I leave it in there, just in case someone need to change something in the dumpfile) –  user1201232 Jul 4 at 9:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.