Need to recreate database on local machine using .sql file

subra010 posted 5 years ago in Import/Export
Sorry if this is covered elsewhere...I am new to this.
I have a MYSQL database on a remote ec2 machine and I want to bring the data down to my local machine.
I need to do this because the external program populating the database unfortunately overwrites data rather than appending them. So getting the data down to my local machine is critical.
I have mysql installed locally (but no database set up).
I was hoping that if I exported the remote database to
* a large .sql file
* or exported the data table by table again as SQL file
either of these would work.
I have done both of these..
I created a new database locally through HeidiSQL but when I try to import the .sql file, I get a "table xxx does not exist " error.
I would appreciate any help.
thanks in advance
jfalch posted 5 years ago
depends on the options that you choose in heidisql´s "export database as sql" dialog when you create the sql file from the remote database. be sure to set both "Database(s) [x] Create" as well as "Table(s) [x] Create" on the top right side of the "SQL export" tab of the dialog, then it should work.

it is, however, not necessary to use an sql file as an intermediate step; you can export directly into your local database: choose "Database" as "Output" option in the same dialog, and the database to import into below it.
subra010 posted 5 years ago
Thanks..this really helped, I am now off and running and am really amazed at how slick HeidiSQL is!
I was able to download the .sql and set up a similar database locally. I am still unclear about the "database" as output option...my databases (remote on ec2) as well as local are named the same and I am concerned that I may overwrite the remote data with what is in my local copy..I am sure I will figure this out as I get more familiar...tks again. Great product, great community support..this is wonderful!
jfalch posted 5 years ago
about the "database" as output: the source of the copy operation is always the database (and tables) selected in the tree on the left side of the "table tools" dialog. You can see which server it belongs to by the parent node of this database in the tree (the session name).

The target depends on the "Output:" options. "Database" means the source data will be copied to the same server that the source is on; you can select the target database below "Output:" in the "Database:" list.

If you have more than one session defined in heidisql´s session manager, the "Database" entry in the "Output:" options list will be followed by the "other" session names (ie all except your current session). If you selewct one of these, heidisql will connect to this session in the background; when it has done so (usually takes a moment), you can then select a target database from those on that sessions´ server. When you have done so, "Export" will copy the selected source(s) from your current server to the selected target database on the "Output:" session´s server.

AFAIK there is not much of a possibility to accidentally overwrite something as long as you keep the above in mind:
.the source of the copy is always on the server of your current session (also shown in heidisql´s window title),
.the target is always on the server of the session selected in "Output:" (unless it is "Database", which means same server as source).

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