On RDS can I create Tables in a Read Replica that are not present on the Master?

Question

We have a separate RDS Instance to handle session state tables, however found that the session DB load is very low. if we can convert the instance handling session as a Read Replica of the main DB, then we can use it for read-only tasks that are safe even with a large lag in the copy.

Has anyone done something like this on RDS (Is it possible and safe)? Should I watch out for any serious side effects? Any links or help in understanding this better would help.

http://aws.amazon.com/rds/faqs/#95 attempts to answer the question but am looking for more insights.

Answer 1

Yes, it is possible. I am using it with success using RDS, for a specific case of local cache.

You need to set the read_only parameter on your replica to 0. I've had to reboot my server in order for that parameter to work.

It's going to work nicely if use different table names, as RDS doesn't allow you to set: replicate-ignore-table parameter.

Remember there musn't be any data collision between master<>slave. If there is a statement which works ok on MASTER, but fails on SLAVE, then you've just broke your replication. That might happen e.g. when you've created table on SLAVE first then after some time you've added that table to MASTER. The CREATE statement will work clean on MASTER, but fail on SLAVE, as table already exist.

Assuming, you need to be really careful, allowing your application to write to SLAVE. If you forget / or make a mistake and start writing to read replica for some of your other data, in the end you might lose data or experience hard to debug issues.