Enabling Database Proxy for Read/Write Splitting

Keep in mind the following notes on database proxies:

• Before enabling a database proxy, both the primary instance and read replicas must be in the Available state.
• You have learned the regions and versions that support database proxies. For details, see Constraints on Database Proxy.

1. Log in to the management console.
2. Click in the upper left corner and select a region and a project.
3. Click in the upper left corner of the page and choose Database > Relational Database Service.
4. In the instance list, click the target instance name to go to the Basic Information page.
5. In the navigation pane on the left, choose Database Proxy.
6. On the displayed page, click Create Database Proxy.
7. On the displayed page, set the required parameters and click Next.

   Table 1 Parameter description

   Parameter	Description
   Proxy Name	The proxy name must start with a letter and consist of 4 to 64 characters. Only letters (case-sensitive), digits, hyphens (-), and underscores (_) are allowed.
   Role	Read and write: Read and write requests are split. Read only: The proxy is not connected to your primary instance and cannot receive write requests.
   Routing Policy	Weighted: You can change the weights of your DB instance and read replicas after Database Proxy is enabled. Load balancing: If selected, to balance the load among read replicas, read requests are automatically distributed to multiple read replicas based on the number of active connections. You can change the routing policy after the database proxy is created. For details, see Configuring the Delay Threshold and Routing Policy.
   Reads Allowed on Primary	This parameter is available only when Load balancing is selected. Yes: Read requests can be routed to both the primary instance and read replicas, which increases the load of the primary instance. Configure this parameter as required. No: Read requests are routed only to read replicas to offload read pressure from the primary instance.
   New Instance Class	Select specifications for the proxy instance based on service requirements. You can change the specifications after the proxy instance is created. For details, see Changing the Instance Class of a Proxy Instance. For details about performance metrics, see Table 2.
   Subnet	Select the subnet where the proxy is located.
   Proxy Nodes	Enter an integer from 2 to 8. You can change the nodes after the proxy instance is created. For details, see Changing the Number of Proxy Nodes. You are advised to set proxy nodes to the quantity of read replicas, with one proxy node for one read replica.
   Set Read Weight	This parameter is only available if Weighted is selected. Select the primary instance and read replicas to which you want to assign weights. Rules for configuring read weights Read requests are allocated proportionate to the read weight you configure. For example, if you associate two read replicas with the proxy and set their read weights to 100 and 200, all read requests forwarded to the proxy are automatically routed to the read replicas in the ratio of 1:2 (the read weights for unselected instances are 0). A read replica can be associated with more than one proxy. To balance traffic among the read replicas of your primary instance, set read weights for them based on the existing proxies' weights and on the amount of traffic routed to the read replicas. You can set read weights allocated to read replicas and the primary DB instance after Database Proxy is enabled. For details, see Configuring the Delay Threshold and Routing Policy.
   Select DB Instances for Load Balancing	This parameter is available only when Load balancing is selected. Select the DB instances for load balancing. After Load balancing is selected, the proxy forwards read requests to database nodes with fewer active connections. You can change the DB instances for load balancing after Database Proxy is enabled. For details, see Configuring the Delay Threshold and Routing Policy.

8. Confirm the database proxy configuration.
   • To modify the configuration, click Previous.
   • To submit the request, click Submit.

9. View and manage the proxy on the Database Proxy page.

   You can view the read/write splitting address on the Basic Information page. Connect your application to the DB instance using this address to split read and write requests.

   The read/write splitting address and the floating IP address of the DB instance are in the same VPC and subnet and are independent of each other.

Before using a database proxy to connect to an RDS for MySQL instance, ensure that the current database account has the permission to access the proxy address.

You can perform the following steps to check and grant an account the permission to access a proxy address.

1. Connect to your RDS instance by referring to Instance Connection.
2. Check whether host of your account contains a database proxy address.

   SELECT user,host FROM mysql.user;
   
   

   

   If the address is %, the host contains all CIDR blocks.

3. If the host does not contain the CIDR block where the database proxy is located, grant the remote access permission to the account.

   For example, if you want to connect to an RDS for MySQL instance from the IP address range starting with 192.168.0 as the root user, you can set host of the account to 192.168.% on the user management page of Data Admin Service (DAS).

Ensure that the inbound and outbound rules allow access of the read/write splitting address. The default port is 3306.

1. On the Instances page, click the target DB instance to go to the Basic Information page.
2. In the navigation pane, choose Connectivity & Security. In the Security Group Rules area, click the security group name to view the security group rules.
3. On the Inbound Rules tab page, allow access through port 3306.

   If there is no such rule, click Add Rule to add one.

1. Obtain the read/write splitting address and port by referring to 9.
2. Log in to an ECS.

   For details, see section "ECS Login" in Elastic Cloud Server User Guide.

3. Run the following command to connect to the RDS for MySQL instance through the read/write splitting address:

   mysql -h <hostIP> -P <port> -u <userName> -p
   
   

   Example:

   mysql -h 192.168.0.61 -P 3306 -u root -p
   
   

   Enter the password of the database account if the following information is displayed:

   Enter password:
   
   

   Table 2 Parameter description

   Parameter	Description
   <hostIP>	The read/write splitting address obtained in 1.
   <port>	The database port obtained in 1.
   <userName>	The username of the database administrator account. The default username is root.

   Note

   When you use a MySQL 8.0 client to access a database proxy, the error "auth user failed" may be displayed.

   Add --default-auth=mysql_native_password when connecting to the database.

You can run the show last route command to check the routing result after you perform a read operation.

The following uses a read operation as an example to describe how to check the routing result of read requests.

1. After connecting to your RDS for MySQL instance, perform a read operation.

   Example:

   select 1;
   
   

   Figure 1 Performing a read operation

   
   

2. Run the following command to check the routing result of the read operation in 1:

   show last route;
   
   

   Figure 2 Query result

   
   

   The IP address in the command output is the IP address of the node to which the read operation is routed. Read operations are routed to the primary node or read replicas based on the specified routing policy.

   Note

   Do not include show last route in service code or multi-statement requests.