How to do a local mysql database replication on an online server?

How can I use a mysql database from a server on the other side of the world without lag?

• We have a MySQL server running in the same US region as the application servers.. It is running perfectly. We'd like to set up local application servers in Japan, however we want to use a centralized database for all application servers. What methods are there to combat the latency issues that would experienced connecting to mysql servers on the other side of the world.

• Answer:

  There's nothing you can do about the 100-200ms latency between the US and Japan.  Cf. http://www.dotcom-monitor.com/WebTools/network_latency.aspx  And that's assuming the network is working at all.  If your applications try to use a database in another region, you're asking for trouble.  Any network downtime means your applications can't function. So a better strategy is to host a copy of your database in Japan, so your application servers in Japan are always reading and writing data locally.  Then the problem is how to keep databases in sync across the WAN? Look into http://www.percona.com/software/percona-xtradb-cluster (PXC) for a good solution for wide-area multi-way synchronous replication.  It's open-source and free. If your applications use the database by writing a high volume of changes, or if individual transactions contain a lot of data, then PXC isn't so efficient.  But typical applications read data a lot more frequently than they write data, and transactions make small incremental changes (i.e. single row updates).  In the latter scenario, PXC is great. Disclaimer: I work for Percona.