This topic describes how to verify the effect of read/write splitting for an ApsaraDB RDS for MySQL instance by executing the /*PROXY_INTERNAL*/show last route; statement, which is a hint. In this case, you must log on to the RDS instance by using the read/write splitting endpoint.
View the RDS instance to which an SQL statement is sent
Procedure
- Connect to an RDS instance by using its read/write splitting endpoint from your client. For more information, see Connect to an ApsaraDB RDS for MySQL instance.
- Execute the following statement to view the ID of the instance where the previous
SQL statement is executed:
/*PROXY_INTERNAL*/show last route;
Note- You can execute this statement only when your application connects to the read/write splitting endpoint of the RDS instance.
- If you use the mysql character terminal to connect to the RDS instance, you must include
the -c parameter. Otherwise, the system ignores comments and reports the following
error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'last route' at line 1
View results
In the command output, last_bkid
indicates the ID of the destination database for the previous SQL statement (the
one above the hint). This type of ID uniquely identifies an RDS instance. The following
figure shows further details.
select 1;
/*PROXY_INTERNAL*/show last route;select 1;
***100 executions***;
select 1;
/*PROXY_INTERNAL*/show last route;
The command output shows that SELECT Statement 101 is sent to another instance. (Assume that more than two read-only instances are available for the statements).
Verify that all write requests are sent to the primary instance
After the read/write splitting function is enabled for an RDS instance, write requests can only be sent to the primary instance because read-only instances can only process read requests. If a write request is sent to a read-only instance, the system re-sends the write request to the primary instance based on the error cause (read_only error).
Execute an INSERT statement and then execute the following statement to verify that the write request is sent to the primary instance:
/*PROXY_INTERNAL*/show last route;