Strategies for keeping two mySQL databases (in sep

Question

Possible Duplicate:
Which is the best way to bi-directionally synchronize dynamic data in real time using mysql

I need advice:

How do I keep 2 mySQL databases (same structure) synced up across 2 locations?

What am I doing:
I am creating an application for a business with two locations. They need to share/update information in a database BUT the proprietor wants it to be internal not external, as he is worried about being able to run in case of an internet outage.

My Strategy so far:
I've set up two servers (one in each location) Each running local Apache + MySQL. The webapp is NOT accessible over the internet, only locally. The mySQL is accessible over SSL via the internet (with a complex password).

The local webapp runs and interacts with local mySQL for reads. For writes, it writes locally then to the remote server (writing 2x).

I am concerned about creating duplicate/corrupted entries when record ids might be duplicated / different, etc... This is why I don't rely on record id, but rather on a separate unique number that I control (not auto-increment).

Bottom line... This is the best I could come up with after doing extensive research. Am I right? Or can someone offer advice?

Answer 1

You might be looking for distributed transactions. MySQL offers XA transaction support (see their documentation for more information).

Answer 2

This is called replication, and MySQL (like any mature database management system) has native support for it. See: http://dev.mysql.com/doc/refman/5.0/en/replication.html