Synchronizing local mysql database with remote one with a script or automatically?

Question

Is there a way to sync a MySQL database with a remote one either automatically or with a script? The data is not time sensitive, but I don't want to have to export it out of the local database and then import it into the remote one.

I am one Windows Vista and using XAMPP as my local stack.

Answer 1

Replication works quite good, and it's not hard to configure. The official doc (for 5.1) it's easy to follow.

Answer 2

If the database you are syncing from is a Windows machine and the remote machine with MySQL is in Linux, you could pipe the mysqldump straight through to the remote mysql database:

mysqldump -hlocalhost -uusername -ppassword --single-transaction --flush-privileges --routines --triggers --all-databases | mysql -A -hremoteIpAddress -uremoteusername -premotepassword

CAVEAT

You need to be careful doing this from local to remote

• The network could timeout if running a very long reindex phase (aka ALTER TABLE tblname ENABLE KEYS) of a MyISAM table. The dump will terminate prematurely. (Dumping to local text file alleviates this worry, which are currently doing)
• Make sure table names and database names do not present case sensitivity problems. This could rear its ugly head if importing InnoDB tables with foreign key constraints. To be safe, make sure every table is lowercase in the Windows machine (Check out lower_case_table_names, perhaps setting it to 1 on the Window installation of mysql)