Synchronizing a RDS instance with another RDS instance

Question

I have a database on an RDS Instance (master) that needs to be synchronized to another RDS instance (slave). I created a read-replica for the first one, but that is created as read_only so I cannot do any operations on it.

Basically, I'll need to create a few other databases on the slave instance as well so that needs to be non-read-only. I'll also need to setup sync to happen once daily.

I am using MySQL 5.6+.

Answer 1

DISCLAIMER : I haven't tried this myself

What you asking for is somewhat rare. Nevertheless, it is possible.

Here is a link to Looker.com to explain how.

Basically, you do the following:

• Create a read replica
• Create a Custom DB Parameter group
• Tweak the read_only setting in the Custom DB Parameter Group
• When replica has been created, ...
  • you modify the instance
  • set Parameter Group to the Custom DB Parameter Group

Once you have the accessibility, you can create the DBs directly in the Slave.

I would normally say "Give it a Try !!!" but I never did this before. So, try it out and tell us all if it worked.

• Kanuj Bhatnagar: Thanks for your quick response. The 'read_only' flag was one of the first things I tried. In the parameters group I couldn't find this parameter and I couldn't change it. Amazon's official documentation says that this flag can be changed too, but I couldn't see it in the parameter group.
• RolandoMySQLDBA: Please look at Steps 6-10 in looker.com/docs/admin/database-config/mysql-rds and try to see if you can duplicate those steps. Perhaps Amazon made it harder or impossible to do this, or maybe the link is older than Amazon's current policies. Try it out and tell us ...
• Kanuj Bhatnagar: I don't know why it didn't show up before. Anyway, that worked.. thanks!
• RolandoMySQLDBA: Actually, thank you. I learned today that you can bypass read_only on an RDS Slave and I have your question and your acceptance as proof. You get a +1 for your question. I will mark your question as one of my favorites.