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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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
This will not put in the CREATE DATABASE and the USE commands in the dump (this is what you want)
You can load it into another database (such as db2) in one of four(4) ways:
Give it a Try !!!
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.
Here is the shell script that will allow you to add suffix / affix to all the schema names on the fly.
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
then use sed to replace the old database name with new one like this
then you can simply create the new database and import it again, and it will create all tables with in the new databae MYNEWDATABASE'
If you do a dump using the following two rules:
If you do the following then mysqldump will create the SQL without referencing the database, then you can use the new database name at the end of your mysql command during import!
If the file is what you have in hand and you are manipulating it from a shell / console, I would use
Replacing the db name on lines that matches Create Database, Use and mysqldump comments