PolarDB-X Replica Read Account Configuration - Route Read Traffic to Follower Nodes - PolarDB

This topic describes how to configure a replica read account using the ALTER USER ... READ_STRATEGY statement to route read traffic such as temporary queries and bulk data extraction to Follower nodes, avoiding impact on Leader node online traffic for PolarDB-X clusters.

Background

DN (Data Node) in PolarDB-X implements high availability based on the Paxos majority replication protocol. Node roles include Leader, Follower, Logger, and Learner. For more information, see High availability (RPO=0).

In daily operations, activities such as DMS queries or bulk data extraction can drive up resource usage on the DN Leader, which in turn affects the stability of online workloads. By configuring a replica read account, you can route this kind of query and extraction traffic to Follower nodes, eliminating the impact on the Leader node and keeping online workloads running smoothly.

Warning

Core rule: Replica read only routes read requests; write requests are never sent to the replica. Only SELECT statements that do not explicitly start a transaction are routed to Follower nodes. All write operations (INSERT, UPDATE, DELETE, and so on), as well as queries inside explicit transactions (BEGIN ... COMMIT), are still executed on the Leader node. Configuring a replica read account does not change the routing behavior of write operations.
Important: The replica (Follower) plays a critical role in high availability failover and must replicate the Leader's logs in real time. Heavy use of the Follower can slow down log replication, delay failover, or even cause failover to fail. Therefore, a replica read account must not be used for stable online traffic and is only suitable for low-volume, ad hoc workloads such as queries and bulk data extraction.

Differences between replica read and read/write splitting

Before you start, understand the differences between replica read and read/write splitting so that you can choose the right option for your workload:

Comparison item	Replica read account	Read/Write splitting	Description
DN node used	Follower DN of the primary instance	Learner DN of a read-only instance	Replica read reuses the Follower of the primary instance directly; read/write splitting uses a separate read-only instance.
Participates in Paxos voting	Yes, participates in voting and decision-making	No, does not participate in voting or decision-making	Follower takes on failover responsibilities; Learner has no impact on high availability.
Impact on the system	Some impact; may affect failover	No impact	Heavy queries against the replica slow down log replication.
Applicable scenarios	Low-volume, ad hoc traffic such as temporary queries and bulk data extraction	Steady read traffic offloading	Choose based on traffic stability and volume.

Recommendation: If your workload requires steady read traffic offloading, use the read/write splitting solution. A replica read account is better suited for occasional, low-volume queries and bulk data extraction.

Version requirements

Only Enterprise Edition instances are supported.
Instance version requirements:
- Version 2.5.0: must be polardb-2.5.0_5.4.20-20260312 or later.
- Version 2.6.0: must be polardb-2.6.0_5.4.21-20260521 or later.
Note
- For information about the instance version naming rules, see Release notes.
- For information about how to view the version of an instance, see View and update the engine version of a PolarDB for Xscale instance.

Syntax

Use the following statement to configure a replica read strategy for a specified account:

ALTER USER 'user_name'@'hostname' READ_STRATEGY [follower | stale | none];

After the configuration takes effect, non-transactional SELECT queries issued by a connection that uses this account are routed to Follower nodes.

Read strategies

Replica read supports two read strategies. Choose one based on your workload's data consistency requirements:

Strategy	Keyword	Consistency	Description
Strong consistency	`follower`	Globally consistent	You can read the latest data committed on the primary instance, ensuring global query consistency. If the primary-replica lag is too large (commonly during DDL operations), the query returns an error.
Weak consistency	`stale`	Eventually consistent	You read the latest data currently available on the Follower. If there is any replication lag between the primary and the replica, the query does not wait and returns the available data immediately.
Disabled	`none`	—	Disables replica read and restores the default behavior.

Note

The difference between strong and weak consistency lies in whether the LSN (Log Sequence Number) is sent. In strong consistency mode, the Follower must catch up to the Leader's LSN before returning the query result, whereas in weak consistency mode, the data currently available on the Follower is returned immediately. Strong consistency is not guaranteed at the Read Committed (RC) isolation level or lower.

Considerations

Before you use replica read, make sure that you understand the following limitations:

Replica read only applies to reads; writes never reach the replica. Only SELECT statements that do not explicitly start a transaction are routed to the Follower. All write operations (INSERT, UPDATE, DELETE, and so on) and queries inside explicit transactions are still routed to the Leader.
Permission requirements: Only a privileged account or the account itself can configure a replica read strategy. Other accounts cannot configure it.
Account naming rules: user_name can only contain letters, digits, underscores (_), and dollar signs ($). Otherwise, an error is returned.
Scope: An account is defined at the cluster level, but the replica read attribute applies only to the primary instance.
Switching the read strategy requires resetting to none first: For an account that already has a replica read strategy enabled, you cannot switch directly from one strategy to another (for example, from follower directly to stale). Otherwise the server returns ERR_EXECUTOR: read strategy already set. First execute ALTER USER ... READ_STRATEGY none; to disable the current strategy, then set the new one.

Procedure

Step 1: Create a dedicated account

We recommend that you create the account in the console. For more information, see Create an account. You can also use a SQL statement to create the account:

CREATE USER 'newuser'@'%' IDENTIFIED BY 'mypassword';

Step 2: Configure account permissions

Grant permissions based on your business requirements. For query workloads, we recommend that you grant only read-only permissions:

GRANT SELECT ON *.* TO 'newuser'@'%';

Step 3: Configure the replica read strategy

Use a privileged account or the account itself to run one of the following statements to configure the replica read strategy.

Strong consistency example (recommended for query workloads that require data consistency):
```
ALTER USER 'newuser'@'%' READ_STRATEGY follower;
```
Weak consistency example (suitable for bulk data extraction workloads with higher tolerance for latency):
```
ALTER USER 'newuser'@'%' READ_STRATEGY stale;
```

Disable replica read example:

ALTER USER 'newuser'@'%' READ_STRATEGY none;

Step 4: Verify that the configuration takes effect

Use the following statement to view all replica read routing rules on the current instance (including both follower and stale strategies):

SHOW ROUTING_RULES;

In the result, the USER_NAME column shows accounts that have replica read enabled, and the ROUTING_TYPE column shows the current strategy (follower or stale). To list only the strong-consistency accounts, add a filter:

SHOW ROUTING_RULES WHERE ROUTING_TYPE = 'follower';

Best practice

Example 1: Dedicated account for DMS queries (strong consistency)

Use this configuration when operations staff query online data through DMS and need to see the latest data:

-- 1. Create the account (recommended to create it in the console and configure the corresponding permissions).
CREATE USER 'dms_reader'@'%' IDENTIFIED BY 'your_secure_password';
GRANT SELECT ON *.* TO 'dms_reader'@'%';

-- 2. Configure strong consistency replica read (run as a privileged account or as the dms_reader account).
ALTER USER 'dms_reader'@'%' READ_STRATEGY follower;

Example 2: Dedicated account for bulk data extraction (weak consistency)

Use this configuration when a big data team periodically extracts data and has low requirements for real-time freshness:

-- 1. Create the account (recommended to create it in the console).
CREATE USER 'bigdata_sync'@'%' IDENTIFIED BY 'your_secure_password';

-- 2. Grant read-only permissions only.
GRANT SELECT ON *.* TO 'bigdata_sync'@'%';

-- 3. Configure weak consistency replica read (run as a privileged account or as the bigdata_sync account).
ALTER USER 'bigdata_sync'@'%' READ_STRATEGY stale;