The AWS East Region outage showed all of us the importance of running our apps and databases across multiple Amazon regions (or multiple cloud providers). In this post, I’ll try to explain how to build a MySQL (or Amazon RDS) redundant site.
For simplicity, we create a passive redundant site. This means that the site is not used during normal operation and only comes into action when the primary site crashes. There are many reasons for choosing such an architecture – it’s easy to configure, simple to understand, and minimizes the risk of data collision. The downside is that you have hardware just sitting around doing nothing.
Still, it’s a common enough scenario. So what do we need to do to make it work?
We need to synchronize the database. This is done by means of database replication. Now, there are two options for database replication: synchronous and a-synchronous. Synchronous replication is great; it ensures that the backup database is identical to the primary database. However, it also means that each DML operation on the primary database must be executed on the backup, and the result is sent to the client only after the backup database has completed the operation (at least at commit level). This is very slow when the backup database is located in a remote geographical location (as a good friend of mine once said – sometimes the speed of light is just too slow).
A-synchronous replication is better for remote sites, but can result in data loss, depending on the “lag” between the primary database and the backup database.
We MySQL customers (RDS customers included) only have a- synchronous replication (and, starting from 5.5, semi- synchronous replication, which on most deployments will behave in the same way as a-synchronous replication).
After the replication is configured, the application needs to be updated to make sure that it recognizes database crashes, and will failover to the backup database.
There are several possibilities for crashes and their detection.
The first, and simplest, is if the entire region is down, which causes both the application servers and the databases to crash. In this case, the CDN, or a DNS load balancing technique, will failover to the backup region. This requires sys-ops work but is transparent to the application.
To read more, check out our original post here