How to copy a database using HeidiSQL?

Question

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 as a dump-file from a MySQL-instance (with HeidiSQL).

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

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

I got the following:

- MariaDB-inst
    +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).

What I have to do to get the desired database name?
I thought I could change the database name in the db_x.sql file but I didn't want to open such a large file.
Can I change the import command above in such a way that it change the database name?
I'm also interested in this kind of solution:

CREATE DATABASE y FROM DATABASE x

Is something like this possible?
In the net I find the solution RENAME DATABASE which was not recommended and ALTER DATABASE db_x UPGRADE DATA DIRECTORY NAME but sincerely, I preferred to create a new database with the new name. Thanks for any help.

Solution 1

Consider you have two databases: source_db and target_db. If you want to copy the database contents from source_db to target_db you should do as follow in HeidiSQL:

1. Right click on source_db then select: Export database as SQL.
2. Now change the value of Output and select Database.
3. A select box will appear, select target_db and that's all.

Solution 2

There is an easy way to transfer a database from one instance to another with HeidiSQL:

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

Solution 3

HeidiSQL's export dialog recently got a new option called "Max INSERT size". This controls the number of rows in bulk/multiple INSERT commands.

Also, there is a documentation for this export dialog.

Solution 4

Try MySQL Workbench. It's made by MySQL and I've found it excellent for backing up a database and restoring it under a different name.

http://dev.mysql.com/downloads/workbench/

View more solutions

Comments

[Andrew Barber]

@krshekhar Please do not use code formatting to highlight random words (like MariaDB or Windows XP) It is meant only for actual code.

[शेखर]

@AndrewBarber thanks for your comment I will keep in mind.

[Bojan Kovacevic]

i found your syntax of import strange, usually it goes: mysql -u username -p -h localhost database_name < file.sql, so with database name after -h command and not after -p

[ravnur]

Please make sure that table names in your dump doesn't have database name, i.e. it should be "tbl", not "db_x.tbl". If you have database name your should dump db_x once again

[giordano]

@BojanK Thanks for the response. The next dump import I will do like you suggest and inform if it has an effect.

[giordano]

@ravnur Thanks for the hint. I also suspect that this could be the reason. So, have to find out how to export a dump file with HeidiSQL without prefix name of the database.

[giordano]

@BojanKovacevic The problem remains with the syntax you suggested. Do you really think that the order has an effect?

[Bojan Kovacevic]

@giordano i am not sure,although to me it seems logical that you specify database name (db_y) after host name (localhost) and not after password. It is the way i always use and the way i saw on multiple tutorials. Though your problem can be elswhere. You sure path to sql file is correct?

[giordano]

@BojanKovacevic I solved this problem using HeidiSql (see anser below). I think that the reason simply that the dump-file contains the sql to create the database db_x. See also the answer of ravnur.

[giordano]

Thanks for answer. I used workbench but I changed to HeidiSQL. One problem with gui-tools is often to find the right click-order. Sometimes, it is not so intuitive as assumed. If I wouldn't find the solution with HeidiSQL I would have try with workbench.

[Nicolas Thery]

There is a trick to be able to select another server's database : Clic export from the DESTINATION server. Then on the left panel, change the checked database to select the SOURCE database. The list now contains the DESTINATIONS databases

[sanji]

Thanks Rubens Mariuzzo , this was helped me