All Products
Search
Document Center

Readwrite splitting

Last Updated: Aug 17, 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 of several milliseconds. If you need to read changed data immediately after the primary ApsaraDB RDS for MySQL instance is changed, you must ensure that the SQL statement for reading data is routed to the primary ApsaraDB RDS for MySQL instance. To meet this demand, DRDS provides custom hints for read/write splitting, to route SQL statements to a specified primary or read-only ApsaraDB RDS for MySQL instance.

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 has 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.

Notes:

  • 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 DRDS hint may become invalid, because the DRDS hint is used in the form of a MySQL comment and the MySQL Command-Line Client deletes all comments before sending the SQL statement to the server for execution. For more information, see MySQL Client Options.

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 keyword 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 keyword in the SQL statement, this SQL statement is randomly routed to a read-only ApsaraDB RDS for MySQL instance based on the allocated weight.

Notes:

  • 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.