mysql import/export

Question

I am trying to create an automatic process which will synchronize the databases of two servers. One site is live, and I need the testing environment to sync up with the live site every so often (I am thinking a cron job for that).

How can I implement this?

Answer 1

You can keep the systems up to date with MySQL replication

http://dev.mysql.com/doc/refman/5.0/en/replication.html

You are basically looking at a Master-Slave configuration

If you'd like something a little simpler, you can use mysqldump to dump your database, then ssh to ship it over the wire, and mysql to load it in again.

mysqldump mydatabase | ssh -h the_test_server "mysql mytestdatabase"

You will have to purge mytestdatabase before doing the transfer, but if you are looking for a single command to 'synchronize' database, this will do it.