How do I configure AWS Aurora to separate write/read operations

2017-12-12 01:12:39

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 |

|____________|

  • 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

    2017-12-12 01:49:01
  • I just wanted to point out that AWS has updated and now have a cluster Read endpoint that does load balancing in case anyone runs into this from Google.

    https://aws.amazon.com/blogs/aws/new-reader-endpoint-for-amazon-aurora-load-balancing-higher-availability/

    2017-12-12 01:53:28