Copying between databases

I have two identical databases. One is my development environment and the other is my production environment. Every few days I want to copy the data from my production DB back to my dev DB, both as a backup and also so that I am testing on valid data. Sometimes I reverse the process because I am copy new columns etc to the production environment.

I have been using the 'Export Database as SQL' then 'Load SQL File' in the other DB. This works but has some shortcomings..
- if I have added a column to a table, it doesn't get added in the backup DB because the table already exists.
- it is a two step process and I have to select which tables I want saved each time

Is there a better way?

What would be nice is a 'Copy Database' command, or the ability to drag and drop a table from one DB to another.

Maybe I could setup a query that did the copy? Not sure of the details.

use a batch file with call to the mysqldump utility, followed by mysql utility to load the dumped file. with the right parameters to mysqldumo (ie include DROP TABLE before CREATE TABLE in dump file) you will propagate structure changes along with the data.
heidisql is primarily an interactive client; for repetitive db tasks it is usually better to use the command line utilities.

use a batch file with call to the mysqldump utility, followed by mysql utility to load the dumped file.

Thanks for the reply. The two databases are on different machines (different continents actually!) so I am not sure these utilities will do it for me. Heidi is so useful I would prefer to be able to do EVERYTHING with it rather than having to add other utilities :-)

of course it is possible to use mysqldump/mysql with different servers:

mysqldump -h host1 -u user1 --ppwd1 --databases database1 database2... -r tempfile.sql
mysql -h host2 -u user2 -ppwd2 <tempfile.sql

really complicated, isnt it? to obtain them, get the current mysql server package; on windows, copy mysqldump.exe, mysql.exe from its bin\ directory, delete the rest.

I understand that you would prefer heidisql, but it just isn't possible. although heidisql can do a direct copy between different servers, it is not scriptable in any way, so that you would have to enter all options (source and target servers, databases, copy options) anew each time, as well as manually starting the transfer. like, interactive.

if I have added a column to a table, it doesn't get added in the backup DB because the table already exists.

You have to check the "Drop table" option. I believe the setting does not persist to prevent accidental deletions.

In any case, you must be aware that neither HeidiSQL not most MySQL clients (including mysqldump) are synchronisation tools. You simply can't perform automated deployment with them.

Thanks for the comments. I will try mysqldump.

although heidisql can do a direct copy between different servers, it is not scriptable in any way,

How do you do a direct copy apart from using the Save to SQL?

First, you have to have two sessions defined in heidisql's session manager, one for the surce server and one for the target server.

Open the source server session. From the menu, choose "Tools" / "Export database as SQL"; in it's options dialog, select databases to export in the tree on the left, then open the Output: dropdown. It should contain an entry "Server: name_of_target_session" at the botton. Select this; possibly modify Database: dropdown below it; finally click "Export".

Excellent. Thank you. I had been using the 'Export as SQL' feature but had not looked at the output options. Duh!