41

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?

58

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

  • In case anyone else struggled with this, -Ddb2 should not have a hyphen in front of it. At least, I got a MySQL syntax error when I included it. – gwg Dec 10 '15 at 21:56
  • This doesn't seem to work. My dump doesn't have a CREATE or USE database, but it still has ALTER DATABASE ... CHARACTER SET ... in it which is breaking my import. Edit: Oracle is basically ignoring the bug. – mpen Jan 19 '18 at 18:45
5

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

Edit: Like some nice people in the comments section have pointed out, this can be dangerous, if some of the data is also changed by this above, so, to conctrete ways to avoid this is.

1) Grep for this in the dump, before you change it, like this.

cat mydump.sql | grep "MYDATABASE"

and

2) we can add some ` to make it safer like this:

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

if anyone else have concrete suggestions, I am happy to edit my answer in another 4 years.

  • 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 '14 at 10:54
  • 3
    the sed part is needed – Steve Buzonas Jan 22 '15 at 0:22
  • 10
    This command is a bad idea and I would down vote if I could. If you are going for string replacements, at least match the line CREATE TABLE and USE so you don't replace any other data. – bksunday Jul 20 '15 at 22:41
  • 1
    before running the sed command ( sed -i 's/MYDATABASE/MYNEWDATABASE/g' mydump.sql ) check all occurances of MYDATABASE with cat mydump.sql | grep "MYDATABASE" to make sure only the database name is changed, other data remains untouched. – Rafaf Tahsin Jun 28 at 6:43
  • 1
    sed -i 's/`MYDATABASE`/`MYNEWDATABASE`/g' mydump.sql # potentially safer steam edit. my database dump does not contain any USE or CREATE DATABASE statements, but the db name is in sql comments, views and triggers (MySQL 5.7). This does NOT replace the database name in the comments, but they have no effect on the resulting database. This worked well for me. – Jonathan Oct 7 at 2:20
3

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.

3

If the file is what you have in hand and you are manipulating it from a shell / console, I would use sed to do string replacements on lines starting with CREATE DABATASE, CREATE TABLE, USE and optionnally -- (mysqldump comments)

Replacing the db name on lines that matches Create Database, Create Table, Use and mysqldump comments

dbfile="yoursqldumpfile.sql";
dbname="current_db_name";
dbnewname="new_db_name";
dbnewfile="/tmp/$dbnewname.sql";
cat $dbfile | sed "/^CREATE DATABASE/ s=$dbname=$dbnewname=" | sed "/^CREATE TABLE/ s=$dbname=$dbnewname=" | sed "/^USE / s=$dbname=$dbnewname=" | sed "/^-- / s=$dbname=$dbnewname=" > $dbnewfile


Of course like many of the answers here mentions, it's easier if the mysqldump backup did not contain a CREATE DATABASE and a USE line. Meaning the options --all-databases, --databases or their short versions -A or -B were not used.

  • In MySQL 5.7, mysqldump includes the database name in triggers and views, even with --no-create-db and without --database or --databases. – Jonathan Oct 7 at 2:10
2

If you do a dump using the following two rules:

  1. Do not use options such as --databases, --database, and simply just put the database name at the end of the command without these options.
  2. Include the option --no-create-db

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!

mysqldump  --no-create-db old_db_name --single-transaction --compress --order-by-primary --host old_db_host -u old_db_user -pOld_db_password | mysql --host new_host -u new_user -pnewpassword new_db_name
  • In MySQL 5.7, mysqldump includes the database name in triggers and views, even with --no-create-db and without --database or --databases. – Jonathan Oct 7 at 2:09
1

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

  • The available link for code is dead. – Michael Minter May 11 '17 at 0:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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