Reasons and Solution of Delay in Synchronization of RDS for MySQL Read-Only Instances
• A large transaction is executed on the Master. Wait until the large transaction is complete, and then the primary/standby replication can be restored. You can split the large transaction into small transactions and execute them in batches.
• A table without a primary key is updated. For a table without a primary key, add a primary key. For a table without a secondary index, add an index.
• DDL operations are performed. It is normal for the replication to be delayed during DDL operation. The primary/standby replication can be restored after the DDL is completed. It is recommended that you perform DDL operations during off-peak hours.
• The read-only instance waits for an MDL. Kill the long transaction that blocks DDL operations on the read-only instance, or commit the long transaction on the service side.
• The read-only instance specification is smaller than the primary instance. Expand the specifications of the read-only instance or disaster recovery instance on the DRS to match the specifications of the primary instance.
Does RDS for MySQL support read/write separation?
After the database proxy service is activated, if no read-only instance is ordered or the primary instance is a single instance, the read/write request is routed to the primary instance through the configuration of the read/write separation rules.
After the database proxy service is activated, if read-only instances exist or the primary instance is not a single instance, the read/write request is routed to each instance by weight through the configuration of the read/write separation rules.
Does RDS for MySQL support sharding and read/write separation?
Yes, RDS for MySQL supports sharding and read/write separation. Details are as follows.
The sharding function relies on the Distributed Relational Database Service (DRDS), which solves the issue of performance scaling during use.
Up to five read-only instances can be created for the RDS for MySQL. The primary instance and the read-only instance have separate IP connection addresses.
Can I request multiple read/write separation addresses for RDS for MySQL?
At present, RDS for MySQL only supports one read/write separation connection address.
After the database proxy service is activated, the read/write request is routed to each instance by weight through the configuration of the read/write separation rules.