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.

Eridanus

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.

Impatience

I am getting an error when executing a delete operation because the user only has select permission on all tables. When I'm granting delete permission to the user in the read replica I'm getting the error: Access denied for user... How to grant any permission in a read replica RDS instance? – Substantive

Answer 2

There's not a lot to add -- the only normal scenario that really makes sense on a pure read replica is things like adding a few indexes and the like if its used primarily for reporting or something else read-intensive.

If you're trying to pre-calculate a lot of data and otherwise modify what's on the read replica you need to be really careful you're not changing data -- if the read is no longer consistent then you're in trouble :)

If you're curious about what happens if you change data on the slave and the master tries to update it, you're already heading down the wrong path IMHO.

TL;DR Don't do it unless you really know what you're doing and you understand all the ramifications.

• And bluntly, MySQL replication can be quirky in my experience, so even knowing what is supposed to happen and what does happen if there's as the master tries to write updated data to slave you've also updated.... who knows.

Illstarred