Synchronising local and remote DB

Question

Hi everyone, I have a general question about DB synchronisation.

So, I'm developing a website locally (PHP + MySQL) and I would like to be able to synchronise at least the structure (and maybe the contents) of the two DB when one of the two is changed (normally I would change the local copy).

Right now what I'm doing is to use mysqldump to dump the modified tables and then import them in the remote DB or do it by hand if the changes are minimal. However I find this tedious and error-prone.

For the PHP I'm currently using Quanta+ which has the handy feature of finding files that have changed and just upload those. Is there something similar for MySQL?

Otherwise how do you keep your DBs synchronised?

Thanks nico

PS: I'm sorry if this was already asked, I saw other questions that deal with similar topics, but couldn't really find an answer.

Answer 1

If you want to continuously keep your DB instances synced - you should use a proper master-slave configuration as your DB landscape.

Check out http://dev.mysql.com/doc/refman/5.5/en/replication.html for more info on replication.

If replication is not available, you can use the binary logs (which record all operationso n the database) on one machine and run them on the second machine whenever you want to sync the instances.

Check out http://dev.mysql.com/doc/refman/5.5/en/binary-log.html

Again, you might be needing access to certain configuration files. If this option is irrelevant, then you might need to fall back to other methods which are much less reliable.

Answer 2

Try this one: MySQL Compare