I want to migrate my database instance from AWS RDS MySQL to Aurora, but I have a doubt about replication and how Aurora management the write/read operations.
I have my application and I want to separate the write operations from the reads. I want to create a Master instance only for write operations and other instance (Read Replica) only for read operations.
The problem is here, I read on AWS documentation that I need to do this separation on my Application and I think or I hope to find a way to do that and be transparent for my Application. I draw a simple schema do I have to do to get with Aurora (from AWS):
What AWS says do I have to do:
-----------------
| Application |
-----------------
| |
| writes |reads
| |
------------ ------------------
| Master | | Read Replica |
------------ ------------------
^ ^
|replication |
|____________|
What do I need:
I need the Master always keep with the write operations and it redirects the read to the Read Replica instance.
-----------------
| Application |
-----------------
|
|write/read
|
------------ Reads ------------------
| Master | <-------> | Read Replica |
------------ ------------------
^ ^
| replication |
|_____________________|
The replication is always running. But I want to separate the write and read process. So my summary:
The master instance detecte the different between writes/reads operations and all read operation will be manage by Read Replica. I need this solution because aurora offer a good features to improve my RDS, but the only problem is that I need to create a balance between write and read operations: The write operation be processed in the master and it send the read operation to the Read Replica. I don't want to define this process and make selection between them in my application code as Amazon propose.
Best Answer
AFAIK, you're right that AWS RDS Aurora (a MySQL 5.6 fork) does not support automatic or transparent read/write splitting: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Aurora.html
In order to do that in a way that's completely transparent to the application, you would need an intermediate proxy. Your application would then always connect to the proxy, the proxy would then have to do packet inspection to examine each incoming query to determine if it's read-write, which then gets forwarded on to the master, or read-only, which can then get forwarded to any of N replicas.
Be aware that this has some notable implications: 1. This means that the proxy needs to understand the MySQL protocol 2. It needs to inspect each packet (query) and determine if it's RW or RO 3. It then needs to forward the query to the appropriate backend MySQL instance 4. It likely needs to keep track of each connection, maintaining a map of front-end connections between your app and the proxy, and backend connections to the mysqld instances. The front-end connection would remain stable, but the backend connection could change for each query. 5. You can potentially have some state issues as a result. For example, when you start an explicit transaction, create temporary tables, or set session variables in your connection... those could get lost when (transparently) switching backends. 6. This will have an impact on SSL and other security measures, as you are explicitly using a MITM 7. All of this typically adds quite a bit of overhead, and you will typically see noticeable query latency because of it.
This is a feature that we hope to have in the MySQL Router (the replacement for the old MySQL Proxy: http://mysqlhighavailability.com/mysql-router-on-labs-the-newest-member-of-the-mysql-family/), but we do not yet. It takes a lot of time and effort to do it properly, so as to minimize the effects noted above. One such proxy that does support that today is ProxySQL: http://www.proxysql.com (See the "Read write split" section)
You can grab the source and start playing with it here: https://github.com/renecannao/proxysql
Good luck!