Postgresql – Read-only replica of RDS Postrgres instance with modified schema

MyISAM tables are not guaranteed to be consistent with disk and, effectively, Amazon recommends to manually do the equivalent to a FLUSH TABLES WITH READ LOCK (you cannot do that in RDS, so you have to lock and flush each MyISAM table individually). So you are right in the fact that backups may not be doing correctly on your setup.

Creating a backup from a slave is a very common way of doing backups, because in your case, you do not care about blocking or affecting the performance of the slaves. But you are not really solving the problem, because MyISAM is not supported for read replicas, as it basically uses the same snapshoting mechanism, potentially inconsistent with non transactional databases.

If you really need hot & consistent backups, you need InnoDB. Otherwise, lock, flush & snapshot.

If you want to guarantee the consistency of a backup, though, setting a replica and seeing that it works is one of the typical easy ways to partially do it. So creating a replica and leaving it on for a while without replication breaking may mean that at least it has all the records and tables you used in your latest transactions (with no guarantee overall).

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!