mysql restore to different database

Question

I back up my production database with the following command:

mysqldump -u root --opt --skip-extended-insert --databases my_production_db 

The resulting dump file has the following lines near the top:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `my_production_db` /*!40100 DEFAULT CHARACTER SET latin1 */; USE `my_production_db `; 

In order to restore the database to a different destination ie. my_debvelopment_db I have to open the dump file and edit the bits where the database is named.

Then I run:

mysql -u root  -p <password> < mydumpfile 

I have not figured out another way to do it.

As the database gets bigger this becomes impractical.

Am I missing something? Cant I somehow specify where I want to restore the database? Would I need a different backup command?

Answer 1

@minaz answer was good, but I want to append a little bit more.

The problem was caused by --databases keyword. If you omit the keyword, it will not contain any database creation contents.

So, Dump without --databases keyword.

mysqldump -u username -p database_name > dump.sql 

And restore it with the target database name.

mysql -u username -p target_database_name < dump.sql 

Also, there are several ways to do this. See the similar problem on here (dba.stackexchange).

Answer 2

If you drop the option --databases but still specify the database name, you will NOT get the create database statements. ie:

mysqldump -u root --opt --skip-extended-insert  my_production_db 

On your dev machine simply create any database you wish to restore to.