ApsaraDB RDS:Configure persistent connections

Switchover

During a switchover, the database proxy for ApsaraDB RDS for MySQL maintains a persistent connection in three steps:

1. Start switchover: Block new connections and requests

   The proxy cannot preserve transactions. Therefore, it handles sessions differently based on their state:

   • For sessions with active transactions during the blocking period, the proxy forwards requests to the backend primary instance for execution.

   • For sessions that start new transactions during the blocking period, the proxy blocks the requests. The client then waits for a response from the server.

   • For sessions with transactions still active after the blocking period ends, the proxy terminates the connection. The backend database then rolls back the uncommitted transactions.

   

2. During switchover: Switch existing connections

   During the switchover, the state of existing connections is modified:

   • For connections that cannot be maintained, the proxy terminates the entire connection.

   • For connections that can be maintained, the proxy switches them to the new database instance.

   • The proxy clears connections to the original primary instance from the connection pool.

   

3. After switchover: Restore connections

   For connections that are successfully maintained, the proxy establishes connections to the new primary instance and restores the connection state.

   

Failover

A failover is an unplanned event where a system failure causes an ApsaraDB RDS instance to automatically promote a secondary instance to become the new primary.

The proxy caches the SQL statements that are being executed on the database or will be forwarded. When a database failure occurs, the backend connection between the proxy and the database is interrupted. After the proxy detects the failover, it does not immediately disconnect from the client. Instead, the proxy forwards failed read requests to an available database instance and restores the connection state.

Prerequisites

Procedure

1. Go to the ApsaraDB RDS Instances page. In the top navigation bar, select the region where the instance is located. Then, find the instance and click its ID.

2. In the left-side navigation pane, click Database Proxy.

3. In the Basic Information section, click Enable next to Persistent Connection.

   Note

   If the 'persistent connection' text is not displayed, it means your instance does not meet the requirements to enable persistent connections.

Prerequisites

• The database proxy is enabled.

• The persistent connection feature is enabled for the database proxy.

Procedure

1. Go to the ApsaraDB RDS Instances page. In the top navigation bar, select the region where the instance is located. Then, find the instance and click its ID.

2. In the left-side navigation pane, click Database Proxy.

3. Configure an access policy for the target database proxy endpoint based on your business requirements. For more information, see Configure an access policy for a database proxy endpoint. Set Read/Write Attributes to Read/Write (Read/Write Splitting).

4. Apply for an internal or public endpoint for the database proxy based on your business requirements. For more information, see Set a database proxy endpoint.

5. In your application, use the internal or public endpoint and port number of the database proxy to connect to the database.

6. When an O&M operation involving a switchover occurs on the database instance, the database proxy automatically maintains the connection. Your application's connection to the proxy is not interrupted when you use the database proxy endpoint.

Limitations

During a switchover, the persistent connection feature cannot maintain connections in the following scenarios:

• Connections with an incomplete result set, where the MySQL server has not finished sending all data.

• Connections with uncommitted transactions.

• Connections that have used a change user statement.

• Connections that have used a LOAD DATA statement.

• Connections with temporary tables.

• Connections used to subscribe to binary logs through the database proxy endpoint.

• Calls to the FOUND_ROWS(), ROW_COUNT(), and LAST_INSERT_ID() functions are not supported. Although these functions can be called, the accuracy of the returned results cannot be guaranteed. MySQL no longer recommends the use of SELECT FOUND_ROWS(). We recommend that you replace SELECT FOUND_ROWS() with SELECT COUNT(*) FROM tb1 for queries. For more information, see FOUND_ROWS().

Usage notes

• Because connections can reconnect, the thread ID of the current connection returned by the select connection_id() query may change.

• Because connections are re-established, the IP address and port displayed in show processlist or SQL Explorer may not match the client's actual IP address and port.

• If a connection has user-defined variables, it can be maintained, but the variables become invalid.