CODEWITHSUNDEEP
mysqldatabaseheidisql
giordano
giordano

Reputation: 3152

How to copy a database using HeidiSQL?

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.

Upvotes: 24

Views: 39351

Answers (4)

Anse
Anse

Reputation: 1658

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.

Upvotes: 1

Rubens Mariuzzo
Rubens Mariuzzo

Reputation: 29241

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

Upvotes: 52

giordano
giordano

Reputation: 3152

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

Upvotes: 5

cja
cja

Reputation: 10026

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/

Upvotes: 1

Related Questions