I've been task to move our web servers to the cloud for all countries that use our services for localization.

The issue is, they must share the same data and therefore I need to set up some database architecture that reads/writes locally but is shared amongst other servers.

I found an article that is pretty much what I want to do. Example below.


However my DBA says this is not possible. So my question is, is this possible? And if so, how?

We are a LAMP shop (Redhat, Apache, PHP5, MySql 5.5).

Thank you.


Bill Karwin has already given the best answer that I can think of... but I think some additional explanation might be in order as to why the link you posted in the original question probably does not provide a sufficient solution.

Your DBA is, at least in one sense, correct. The problem is that the illustration is deceptive. The three distributed nodes in the middle look like they are database servers sending updates back to the master, which MySQL can't really offer. You'll notice that the 3 nodes are actually labeled as "web servers."

It is apparent from the configuration examples that all the MySQL slave instances are doing in this setup is providing the application with read-only access to local replicas of the master database, but the web (application) server is actually sending all of its writes back to the master database -- not through to the local MySQL instances.

That's what MySQL can do -- scale out your ability to read from asynchronous replicas. It is unlikely that you'd want the ability for applications to write to the database to be dependent on the single point of failure that the document includes. Yes, you could have multiple masters, but depending on how the application works, it's only safe to direct writes to one such master at a time because there's no provision in multi-master asynchronous (ordinary MySQL) replication to handle the case when conflicting data gets written simultaneously from two (or more) masters.

PXC and MariaDB in cluster configuration don't have that same problem because the cluster nodes don't operate asynchronously... although there's a different problem, since if one node gets isolated from the other two, it can't be written to because in the case of a network split, the server that is left in the minority won't accept writes (to avoid inconsistency). If each of them is isolated from all of the others, then none of them will work, because they are all in the minority and have no idea what has happened to their peers. That's not to say it's not a great solution -- it's just not a perfect one.

But there is no perfect solution.

If your data is such that the following is practical, there's a third approach. This would be a scenario where each site has its own set of uniquely-named schema+table sets (that is, table_schema + table_name is unique to each site, table names could be the same if database names are different) that are written to, but needs to be able to read the data from the other sites.

MariaDB multi-source replication could be configured such that you have a centralized server that is configured as a slave to all of the remote servers, and serves as master to all of the remote servers. Each replication event received by this central server would be written to the binlog and sent out to all of the remotes, which would execute it locally. The remotes would log their local changes, sending replication events back to the master for distribution to all of the other remotes... sort of a hub-and-spoke configuration. The central server would only consolidate the data, it wouldn't be used by an instance of the application... and the remote servers wouldn't necessarily even have to be MariaDB -- only the central one -- I have MySQL 5.5 and MariaDB 10.0 systems interconnected and replicating to each other (5.5 <-> 10.0) without issue in my operation. The current limit of "spoke" servers would be 64.

But again, this is only practical if each site needs to see, but not change, the data from the other sites, and/or access a common set of read-only data while updating a uniquely-named local set of tables. In this scenario, a site's isolation would only delay their access to the latest data from outside the local environment but would not impair their ability to write to the things they should be able to write.


Check out this blog: http://www.mysqlperformanceblog.com/2012/01/11/making-the-impossible-3-nodes-intercontinental-replication/

We will create 3 nodes Cluster with nodes on different continents (Europe, USA, Japan) and each node will accept write queries.

The solution used is Percona XtraDB Cluster.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.