7

Currently I know AWS RDS official documents can achieve the function of read-only copy for example:

The RDS main library is the production library, the main library has 3 tables (a, b, c), the main library uses a read-only copy to create a sub-library, then read-only copy promoted to a database instance, the official link is as follows:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ReadRepl.html

After upgrading a read replica to a DB instance(I call it new DB instance), what steps would be taken if I wanted this new DB instance to fulfill the following requirements:

The new database instance has 5 tables (a, b, c, d, e), of which a, b, c three tables to be consistent with the data of the three tables in the main library (equivalent to these three tables is the main Read-only copy of the library), and d, e two tables can be modified.

Improve this question
1
  • The body of your question doesn't seem to match the title. In MySQL Master/Slave replication, you can't modify tables on the slave (replica) without replication failing.
    – Michael - sqlbot
    Commented Feb 4, 2018 at 5:24

1 Answer 1

Reset to default
2

While an RDS instance is a read-replica of another RDS instance:

  • The replica is kept up-to-date with changes on the master. It will receive updates from the master via replication.
  • The replica is in read-only mode, only SELECT can be used.

After you promote an RDS read-replica to a master:

  • Replication from the old master stops. The data in the new master will be as it was when replication stopped. Updates on the old master will not replicate to the new master.
  • The replica is now in read-write mode, so SELECT, UPDATE, INSERT, and DELETE commands can be executed.

So, after you promote an RDS read-replica to a master, you can add your two new tables d and e. The data in tables a, b, and c will be consistent with the old master up until the promotion occurred. You can update the data in these tables on the new master.

Most importantly, any updates on the old master WILL NOT replicate to the new master. The connection between these two instances is forever severed.

UPDATE

If you need to keep tables a, b, and c up-to-date from the old master, while adding new tables d and e which can be modified, this is not possible with RDS.

Improve this answer
3
  • 1
    Thanks , @Matt Houser. If I install MySQL on an EC2 instance on AWS, there are 5 tables (a, b, c, d, e) for Mysql on EC2 instance, 3 tables (a, b, c) on EC2 and only three tables (a, b, c) on RDS keep data consistency, the other two tables (d, e) on EC2 can be modified, is this can be achieved? If it can be achieved, what is the procedure?
    – Andrew
    Commented Feb 4, 2018 at 11:38
  • There are two separate RDS database on AWS, after master-slave replication. Can the slave database be modified ?
    – Andrew
    Commented Feb 5, 2018 at 8:05
  • @andrew I don't know whether MySQL can do what you want itself. I know RDS cannot. RDS read-replicas are put into read-only mode, so they cannot be modified.
    – Matt Houser
    Commented Feb 5, 2018 at 14:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.