generating database with different name from mysqldump backup

Question

The database "db" is backuped in backup.sql. Is there a way to restore database from script with different from "db" name?

thank you in advance!

Solution 1

Sure, when you import it you do this right:

mysql -uuser -ppassword databasename < mydump.sql

You can put anything you want where I wrote databasename - as long as that database actually exists :)

Solution 2

This depends on how you created your MySQL dB dump file

for example, if you do

mysqldump -h localhost -u user mydb -pXXX > mydb.sql

There won't be any CREATE DATABASE statements in your sql dump file. But I think you can only backup one database.

If you create your mysql dump file with --database or --all-databases option for example

mysqldump -h localhost -u user --database mydb -pXXX > mydb.sql 
mysqldump -h localhost -u user --all-databases -pXXX > alldb.sql

then you will see CREATE DATABASE statement in your mysql dump file. If you want a different dB name, you will need to change it before DB restore.

Solution 3

If the name of the database is include the SQL file, I didn't find any other way than modify the SQL file.

My favorite command to do it :

    sed -i "s/\`old_db_name\`/\`new_db_name\`/g" my_sql_file.sql

Comments

[Eugeny89]

great solution! I'll try to do so!

[Eugeny89]

I'd made "mysql -uroot -p**** db < full-backup-db-20110324-0331.sql" then "mysql db -uroot -p****" and "show tables;". "Empty set" is outputed

[Henry]

@Eugeny89 - if you do a normal msyqldump into a sql file, verify there are sql statements in that file, go to your new server, create the new database CREATE DATABASE newdatabase; and then import is mysql newdatabase < dump.sql it will work. If there are no errors - were there errors?

[Eugeny89]

I guess I should try to drop the database and start from the begining

[James]

Try my suggestion. Might help.

[Henry]

That's just a comment, it doesn't have any effect as far as I know.

[James]

Yes it does. Open the .sql file, and change the database name inside to the name you want to use. Then restore the file. It will create a new database with the name you specify, containing the data from the original database.

[James]

The above requires you to read through the SQL file carefully... given that you didn't read my response properly perhaps you should try something else ^_^

[Hibou57]

This one works, but it may be dangerous if the database contains the same text in some other place for other purposes. This is a blind replacement, which may be OK, or not. A more precise replacement would be safer.

[Michael De Silva]

I like to use vim for this and go about with a :%s!old_name!new_name!gc where I get to approve what gets changed. Works like a charm. Not sure if this is feasible on say a 20-30GB export though!

[Ifedi Okonkwo]

I believe that this approach will work, provided mydump.sql does not contain CREATE DATABASE <original_db>; and/or USE <original_db>; In the latter scenario, these statements override the command line parameter, the imported queries are run on <original_db> and databasename` will receive no import.

[Ifedi Okonkwo]

@Hibou57: Good point. But that is one good reason for those (otherwise ugly) backticks around the db and table names. It should be very very rare to have ` oranges ` on the dump referring to anything but a database object. Elsewhere, the closest thing would probably be 'oranges' or "oranges".

[Ifedi Okonkwo]

Just to explain: The auto-formatting algorithm on SO wouldn't let me output "oranges" with backticks instead of quotes, since the backticks are read to stand for inline code boundary!