Export a database from one instance to another with new name
| User, date | Message |
|---|---|
|
Written by giordano2
3 months ago Category: Import/Export 4 posts since Sun, 03 Mar 13 |
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_x +db_y 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. |
|
Written by jfalch
3 months ago 222 posts since Sat, 17 Oct 09 |
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 <... |
|
Written by jfalch
3 months ago 222 posts since Sat, 17 Oct 09 |
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. |
|
Written by giordano2
3 months ago 4 posts since Sun, 03 Mar 13 |
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. |
|
Written by jfalch
3 months ago 222 posts since Sat, 17 Oct 09 |
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. |
|
Written by giordano2
3 months ago 4 posts since Sun, 03 Mar 13 |
Greate! I will try both options in one of the next days and tell my experience. Thanks a lot. |
|
Written by giordano2
3 weeks ago 4 posts since Sun, 03 Mar 13 |
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 giordano |
|
Please login to leave a reply, or register at first. |