Confused about AWS RDS read replica databases. Why can I edit rows?
Edit: I'm not trying to edit the read replica. I'm saying I did edit it and I'm confused on why I was able to.
I have a database in US-West. I made a read replica in Mumbai, so the users in India don't experience slowness. Out of curiosity, I tried to edit a row in the Mumbai read-replica database hoping to get a security error rejecting my write attempt (since after all, it is a READ replica). But the write operation was successful. Why is that? Shouldn't this be a read-only database?
I then went to the master database hoping the writing process would at least be synchronized, but my write execution didn't persist. The master database was now different than the place.
I also tried edited data in the master database, hoping it would replicate it to the slave database, but that failed as well.
Obviously, I'm not understanding something.
Answer
Take a look at this link from Amazon Web Service to get an idea:
How do I configure my Amazon RDS DB instance read replica to be modifiable?
Probably your read replica has the flag read_only = false
Modify the newly created parameter group and set the following parameter:
- In the navigation pane, choose Parameter Groups. The available DB parameter groups appear in a list.
- In the list, select the parameter group you want to modify.
- Choose Edit Parameters and set the following parameter to the specified value:
read_only = 0- Choose Save Changes.
I think you should read a little about Cross region read replicas and how they work.
Working with Read Replicas of MariaDB, MySQL, and PostgreSQL DB Instances
Read Replica lag is influenced by a number of factors including the load on both the primary and secondary instances, the amount of data being replicated, the number of replicas, if they are within the same region or cross-region, etc. Lag can stretch to seconds or minutes, though typically it is under one minute.
Reference: https://stackoverflow.com/a/44442233/1715121
Facts to remember about RDS Read Replica
- In Read Replica, a snapshot is taken of the primary database.
- Read replicas are available in Amazon RDS for MySQL, MariaDB, and PostgreSQL.
- Read replicas in Amazon RDS for MySQL, MariaDB, and PostgreSQL provide a complementary availability mechanism to Amazon RDS Multi-AZ Deployments
- All traffic between the source and destination database is encrypted for Read Replica’s.
- You need to enable backups before creating Read replica’s. This can be done by setting the backup retention period to a value other than 0
- Amazon RDS for MySQL, MariaDB and PostgreSQL currently allow you to create up to five Read Replicas for a given source DB Instance
- It is possible to create a read replica of another read replica. You can create a second-tier Read Replica from an existing first-tier Read Replica. By creating a second-tier Read Replica, you may be able to move some of the replication load from the master database instance to a first-tier Read Replica.
- Even though a read replica is updated from the source database, the target replica can still become out of sync due to various reasons.
- You can delete a read replica at any point in time.