How to Use a Proxy Instance to Enable Read/Write Splitting

1. Log in to the management console.
2. Click in the upper left corner and select a region and project.
3. Click in the upper left corner of the page and choose Databases > GaussDB(for MySQL).
4. On the Instances page, click the instance name to go to the Basic Information page.
5. In the navigation pane, choose Database Proxy.
6. Click Create Proxy Instance.
7. In the displayed dialog box, configure related parameters.

   Table 1 Parameter description

   Parameter	Description
   Proxy Instance Name	The name can consist of 4 to 64 characters and must start with a letter. Only letters (case-sensitive), digits, hyphens (-), and underscores (_) are allowed.
   Proxy Mode	You can select Read/Write or Read-only as required. Read/Write: All write requests are forwarded only to the primary node, and all read requests are forwarded to the selected nodes based on the read weights or active connections. The default read weight of a node is 100. Read-only: Write requests are not forwarded to any node. All read requests are forwarded to the selected read replicas based on the read weights or active connections. The read requests are not forwarded to the primary node, even if the primary node is selected. NOTE: In the read-only mode, only read requests are supported. If write requests are forwarded to the selected nodes, an error message is displayed. DDL, DML, and temporary table operations are not supported in the read-only mode.
   Consistency Level	The consistency level can be configured only when the kernel version of your GaussDB(for MySQL) instance is 2.0.28.1 or later. Value: Eventual consistency After a proxy instance is created, requests for SELECT operations are routed to different nodes based on their read weights. Because there is a replication latency between the primary node and each read replica and the replication latency varies for different read replicas, the result returned by each SELECT statement may be different when you repeatedly execute a SELECT statement within a session. In this case, only eventual consistency is ensured. To offload read requests from the primary node to read replicas, you can select eventual consistency. Session consistency To eliminate data inconsistencies caused by eventual consistency, session consistency is provided. Session consistency ensures the result returned by each SELECT statement in a session is the data that was updated after the last write request. To use session consistency, the kernel version of your proxy instance must be 2.7.4.0 or later.
   Routing Policy	Value: Weighted: Read requests are assigned to nodes based on the weights you specify. Load balancing: Read requests are assigned to nodes with fewer active connections. To use load balancing, the kernel version of your proxy instance must be 2.22.07.000 or later. For more information about routing policies, see Modifying the Routing Policy of a Proxy Instance.
   Proxy Instance Specifications	You can select the proxy instance specifications as needed. Kunpeng general computing-plus: 2 vCPUs | 4 GB, 4 vCPUs | 8 GB, and 8 vCPUs | 16 GB General-enhanced: 2 vCPUs | 4 GB, 4 vCPUs | 8 GB, and 8 vCPUs | 16 GB
   Subnet	To specify this parameter, submit an application by choosing Service Tickets > Create Service Ticket in the upper right corner of the management console. When creating a proxy instance, you can specify a subnet for the proxy instance. If the subnet where the GaussDB(for MySQL) instance is associated with is a secondary CIDR block, you can only select the same subnet as the GaussDB(for MySQL) instance for the proxy instance.
   Proxy Instance Nodes	You can enter an integer from 2 to 16. The default value is 2. Number of recommended proxy instance nodes = (Number of vCPUs of the primary node + Total number of vCPUs of all read replicas)/(4 x Number of vCPUs of the proxy instance), rounded up.
   Associate New Nodes	After Associate New Nodes is enabled, new read replicas will be automatically associated with the proxy instance.
   New Node Weight	If Routing Policy is Weighted, you need to set read weights of the new nodes. The default weight of a node is 100. Nodes with higher weights process more read requests.
   Database Nodes	You need to select the nodes to be associated with the proxy instance for processing read requests. If Routing Policy is Load balancing, you do not need to configure read weights for selected nodes. Read requests are forwarded to nodes with fewer active connections. If Routing Policy is Weighted, you need to configure read weights of the primary node and read replicas. Read requests are forwarded to nodes based on the weights you specify. For example, read weights assigned to one primary node and two read replicas are 100, 200, and 200, respectively. In the read/write mode, the primary node and two read replicas process read requests in the ratio of 1:2:2. The primary node processes 20% of read requests, and each read replica processes 40% of read requests. Write requests are automatically routed to the primary node. In the read-only mode, the read weight of the primary node does not take effect, and the two read replicas process 50% of read requests, respectively.

8. Click OK.
9. View the proxy instance and associated nodes.

   After the proxy instance creation is complete, you can view the created proxy instance on the Database Proxy page.

   Figure 1 Viewing the create proxy instance
   

   Click Basic Information in the navigation pane. Click in the upper right corner of the page and select View Instance Topology. In the instance topology, you can view the database nodes associated with the proxy instance. You can move the pointer to a node name to view its details.

   Figure 2 Viewing information about nodes associated with a proxy instance
   

Before using a proxy instance to connect to your GaussDB(for MySQL) instance, ensure that the current database account has the permissions to access the proxy address, or the proxy instance cannot connect to your GaussDB(for MySQL) instance.

You can perform the following steps to check the permissions and grant the account the permissions to access the proxy address.

1. Connect to your GaussDB(for MySQL) instance.

   For details, see Connecting to a DB Instance.

2. After the instance is connected, run the following SQL statement to check whether the host of the current database account contains a proxy address:

   SELECT user,host FROM mysql.user;

   

   To obtain the proxy address:

   Click the name of the GaussDB(for MySQL) instance to go to the Basic Information page. In the navigation pane, choose Database Proxy. In the proxy instance list, view the proxy address.

   Figure 3 Viewing a proxy address
   

3. If the host does not contain the CIDR block where the proxy instance is associated with, assign the remote access permissions to the host.

   For example, if you want to connect to the GaussDB(for MySQL) instance using 192.168.0 as user root, set Host to 192.168.% on the DAS user management page. For details, see Editing a User.

   Figure 4 Configuring a host IP address
   

You need to ensure that the inbound and outbound rules allow access from the proxy address. The default port number is 3306.

1. Log in to the management console.
2. Click in the upper left corner and select a region and project.
3. Click in the upper left corner of the page and choose Databases > GaussDB(for MySQL).
4. On the Instances page, click the instance name to go to the Basic Information page.
5. In the Network Information area, click the security group name in the Security Group field.
6. On the Inbound Rules tab, check whether access through port 3306 is allowed by default.
   Figure 5 Allowing access through port 3306
   

   If there is no such a rule, click Fast-Add Rule. In the displayed dialog box, select MySQL (3306) and click OK.

   Figure 6 Fast adding port 3306
   

1. View the proxy address and port on the GaussDB(for MySQL) console.

   Click the name of the GaussDB(for MySQL) instance to go to the Basic Information page. In the navigation pane, choose Database Proxy. In the proxy instance list, view the proxy address and port.

   Figure 7 Viewing a proxy address and port
   

2. Log in to an ECS.

   For details, see Elastic Cloud Server User Guide.

3. Run the following command to connect to the GaussDB(for MySQL) instance using the proxy address:

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

   Table 2 Parameter description

   Parameter	Description
   <hostIP>	Proxy address obtained in 1.
   <port>	Port obtained in 1.
   <userName>	Username, that is, the GaussDB(for MySQL) database administrator account. The default value is root.
   <password>	Password of the GaussDB(for MySQL) database administrator.

After each read operation is complete, you can run the show last route command to view the routing result of the read operation.

The following is an example.

1. After the GaussDB(for MySQL) instance is connected, perform a read operation.

   Example: select 1;

   

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

   show last route

   Figure 8 Viewing a query result
   
   

   Do not use show last route for service code or multi-statement execution.