How can I set up a mirror site of PHP & MYSQL between two distant sites?

Question

I have a site which is located in US and I want to set up a mirror site in China, the site is using PHP + MYSQL and the network quality between two servers is terrible.

I tried set up a database-sync between two mysql databases but, there will always be conflicts due to the bad network quality (extremely high latency, packet loss, and sometimes cannot establish a connection at all).

Is there any better way to do the mirroring?

is the mirror going to need sync back the main server or can it be a simple slave? do you need up to the minute syncing if a slave is ok? — anthonysomerset, May 03 '11 at 08:47
the mirror needs to sync back, so conflicts are everywhere when network goes terrible. — Felix Yan, May 03 '11 at 09:18

score 1 · Answer 1 · answered May 03 '11 at 12:25

I tried set up a database-sync between two mysql databases but, there will always be conflicts due to the bad network quality

I assume you mean conflicting data updates.

Then the only practical solution is to partition the systems as much as possible - which rather undermines the availaiblity of the system, and will probably require code changes unless you can isolate them completely.

score 1 · Answer 2 · answered May 03 '11 at 14:35

If you really need to do this why don't you use MySQL multi master replication aka MySQL circular replication link

Given the bad network conditions there will be replication delays hence your selects would have inconsistent results but the replication should still work fine. Another feasible option might be to host in HK where you would get better network connectivity..

score 1 · Accepted Answer · answered Jul 31 '11 at 02:33

Many organizations deal with this challenge. There are a number of standard approaches:

Mirror the static portions of the site to the remote region so that the amount of data fetched from a distant server is minimized. This can be enabled by a CDN or by your own DNS-based load balancing system, e.g. F5 GTM for an images sub-domain, etc.
Parition the site by region with mostly unique content in each partition and some manual or automatic content duplication when needed (possibly enabled by the CMS.)
Use a network acceleration service like akamai to serve your centrally hosted content to remote regions on faster network. The service doesn't mirror your content, rather it provides optimized path between the user and you with higher reliability and lower latency.

In certain circumstances, you might have a dynamic site that isn't edited very often. Rather than continuous database replication, you might take a snapshot approach. This would involve making frequent backups, copying them to the remote server, and restoring them on a schedule.

How can I set up a mirror site of PHP & MYSQL between two distant sites?

3 Answers3