Export a database from one instance to another with new name

giordano2 posted 5 years ago in Import/Export
I have a local installation of MariaDB on a Windows XP.
I created an empty database db_y which I wanted to populate with the tables of the database db_x which I exported with HeidiSQL as a dump-file from a MySQL-instance.

When I imported the dump-file db_x.sql into the the MariaDB instance:

c:\ > mysql -u root -p -h localhost db_y < "X:/archive/db_x.sql"

I got the following:

- MariaDB-instance

db_y remains empty and db_x from the dump-file was added (db_x is the database name of the original database I exported).

How can export a database and import the database with a new name using HeidiSQL?
Thanks for help.
jfalch posted 5 years ago
did you use the "export database as sql" feature ? if so, did you select the "create database" option (upper right) ? if so, donĀ“t - it includes code into the .sql file to create a database on the target server with the exact same name as is has on the source server.

to get rid of this code if you already have a dump file and it is difficult and/or time-consuming to recreate, open the dump with a text editor (notepad will do).
.look for a line starting with CREATE DATABASE db_x and in it, change db_x to db_y .
.look for another line (usually directly following) starting with USE and in it, also change db_x to db_y .
save file and close editor. the modified dump should now import as db_y from the command line; however, you should then omit db_y from it ie
mysql -u root -p -h localhost <...
jfalch posted 5 years ago
NB if you do not edit the dumpfile, but recreate it, you must use -D db_y on the command line to tell mysql where to put the tables in the absence of CREATE / USE; ie

mysql -u root -p -h localhost -D db_y <...

note that db_y must already on the target server exist when importing in this case.
giordano2 posted 5 years ago
Hi jfalch,
Thanks a lot. Yes, I used the "export database as sql" feature. I wanted to avoid to open the file to change the database name. I did it now as you explained and I could load the dump-file into the database. Obviously, there is no way to avoid the insert of "Create/Use" by using the "export database as sql" feature, isn't it? If this is the case I will try to use mysqldump.
Thanks again.

jfalch posted 5 years ago
yes, there is a way: do not select the "create database" option in the upper right of the export dialog. use -D with mysql to define define the target databse in this case.

instead of using using a 2-step process and mysql.exe, you could define a second heidisql session for the target server, and then select this session in the "Output:" option of the export dialog, and select the target database below it in the "Database:" option. heidisql is fully capable of copying data from one server to another by itself.
giordano2 posted 5 years ago
Greate! I will try both options in one of the next days and tell my experience.
Thanks a lot.
giordano2 posted 4 years ago
Fantastic. Direct export of a database from one server to another is very comfortable with HeidiSQL. Here are the steps:
1. Create the database db_y in instance y
2. Click on dump icon (or right click). The instance y should be activated.
3. At "Output" option choose Database
4. At "Database" option choose db_y
5. Select on the left the instance x and database x
6. Export

Comment: When I choosed instance y instead x the database x was not available and instance x was not accessible (Error message 1045: Access denied for ...).

@jfalch: thanks a lot for your help

Please login to leave a reply, or register at first.