23

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.

Rubens Mariuzzo
  • 28,358
  • 27
  • 121
  • 148
giordano
  • 2,954
  • 7
  • 35
  • 57
  • @krshekhar Please do not use code formatting to highlight random words (like MariaDB or Windows XP) It is meant only for actual code. – Andrew Barber Feb 15 '13 at 09:05
  • @AndrewBarber thanks for your comment I will keep in mind. – शेखर Feb 15 '13 at 09:08
  • 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 – Bojan Kovacevic Feb 15 '13 at 09:10
  • 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 – ravnur Feb 15 '13 at 11:08
  • @BojanK Thanks for the response. The next dump import I will do like you suggest and inform if it has an effect. – giordano Feb 16 '13 at 10:11
  • @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 Feb 16 '13 at 10:14
  • @BojanKovacevic The problem remains with the syntax you suggested. Do you really think that the order has an effect? – giordano Sep 26 '13 at 05:30
  • @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? – Bojan Kovacevic Sep 27 '13 at 06:03
  • @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 Sep 27 '13 at 20:18

4 Answers4

50

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.

enter image description here

Rubens Mariuzzo
  • 28,358
  • 27
  • 121
  • 148
  • 2
    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 – Nicolas Thery Jan 15 '16 at 12:00
  • 1
    Thanks Rubens Mariuzzo , this was helped me – sanji Aug 21 '18 at 11:19
  • For some reason I expected it to be much harder than this lol. Cheers! – SpeedOfSpin Jul 13 '23 at 11:04
4

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
giordano
  • 2,954
  • 7
  • 35
  • 57
1

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/

cja
  • 9,512
  • 21
  • 75
  • 129
  • 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. – giordano Sep 26 '13 at 05:27
1

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.

Anse
  • 1,573
  • 12
  • 27