How to synchronize two MYSQL database running on different server?

Question

I have a database on local computer and same on remote, I want to sync both server any suggestion on how to proceed?

Answer 1

Replication is not very hard to create.

Here's some good tutorials:

http://aciddrop.com/2008/01/10/step-by-step-how-to-setup-mysql-database-replication/

http://www.ghacks.net/2009/04/09/set-up-mysql-database-replication/

http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html

http://oreilly.com/catalog/hpmysql/chapter/ch07.pdf

http://www.lassosoft.com/Beginners-Guide-to-MySQL-Replication

Here some simple rules you will have to keep in mind (there's more of course but that is the main concept):

Setup 1 server (master) for writing data.
Setup 1 or more servers (slaves) for reading data.
This way, you will avoid errors.

For example: If your script insert into the same tables on both master and slave, you will have duplicate primary key conflict.

You can view the "slave" as a "backup" server which hold the same information as the master but cannot add data directly, only follow what the master server instructions.

NOTE: Of course you can read from the master and you can write to the slave but make sure you don't write to the same tables (master to slave and slave to master).

I would recommend to monitor your servers to make sure everything is fine.

Let me know if you need additional helphttp://stackoverflow.com/questions/7707859/mysql-database-sync-between-two-databases