All Products
Search
Document Center

Read-write splitting

Last Updated: Aug 12, 2020

DRDS provides read/write splitting that is transparent to the application layer. However, data synchronization between primary and read-only ApsaraDB RDS for MySQL instances has a delay in milliseconds. If you need to read changed data immediately after the primary ApsaraDB RDS for MySQL instance is changed, ensure that the SQL statement for reading data is routed to the primary ApsaraDB RDS for MySQL instance. To meet this requirement, DRDS provides a custom hint for read/write splitting, to route SQL statements to a specified primary or read-only ApsaraDB RDS for MySQL instance.

This topic is applicable to Distributed Relational Database Service (DRDS) 5.3 and later versions.

Limits

  • DRDS hints can be in the formats of /*+TDDL:hint_command*/ and /!+TDDL:hint_command*/.

  • In the MySQL Command-Line Client, if you need to execute an SQL statement that contains a DRDS hint in the format of /*+TDDL:hint_command*/, add the -c parameter to the logon command. Otherwise, the client may delete the DRDS hint, which is used in the form of a MySQL comment, before sending the SQL statement to the server for execution. In this case, the DRDS hint is invalidated. For more information, see MySQL Client Options.

Syntax

  1. /*+TDDL:
  2. master()
  3. | slave()
  4. */

With this custom hint, you can specify a primary or read-only ApsaraDB RDS for MySQL instance to execute an SQL statement. With the custom hint /*+TDDL:slave()*/, if a primary ApsaraDB RDS for MySQL instance is configured with multiple read-only ApsaraDB RDS for MySQL instances, the DRDS instance randomly selects a read-only ApsaraDB RDS for MySQL instance based on its weight, to execute the SQL statement.

Examples

  • Specify a primary ApsaraDB RDS for MySQL instance to execute an SQL statement:
  1. SELECT /*+TDDL:master()*/ * FROM table_name;

After the custom hint /*+TDDL:master()*/ is added behind the first key word in the SQL statement, this SQL statement is routed to the primary ApsaraDB RDS for MySQL instance for execution.

  • Specify a read-only ApsaraDB RDS for MySQL instance to execute an SQL statement:
  1. SELECT /*+TDDL:slave()*/ * FROM table_name;

After the custom hint /*+TDDL:slave()*/ is added behind the first key word in the SQL statement, this SQL statement is randomly routed to a read-only ApsaraDB RDS for MySQL instance based on the allocated weight.

  • The DRDS hint for read/write splitting only takes effect for the read-only SQL statements outside transactions. Write SQL statements outside transactions or SQL statements in transactions are still routed to the primary ApsaraDB RDS for MySQL instance for execution.
  • The DRDS hint /*+TDDL:slave()*/ allows you to route the SQL statement to a read-only ApsaraDB RDS for MySQL instance that is randomly selected based on the allocated weight. If no read-only ApsaraDB RDS for MySQL instance is available, no error is reported and the primary ApsaraDB RDS for MySQL instance is selected to execute the SQL statement.