TencentDB for MariaDB supports read/write separation by default. Each replica in the primary/replica architecture can be read-only. If multiple replicas are configured, read requests will be automatically assigned to low-load replicas by the gateway cluster (TProxy).
TencentDB for MariaDB supports global automatic read/write separation, i.e., all
SELECT requests will be sent to replicas with no modification of the business required. To use this feature, you need to submit a ticket for application and indicate "your account information, instance region, instance ID, request to enable automatic read/write separation, and consent to the global read/write separation risk statement".
Risk of global read/write separation
The replica naturally has data delay. In global automatic read/write separation, the system sends all
SELECT requests to the replica by default with no policy provided (for more information, please see read/write separation based on read-only accounts). In this case, the business may read uncontrollable historical data, leading to data distortion. Therefore, this feature is only recommended for business systems that have no requirements of data consistency.
By submitting a ticket to enable this feature, you acknowledge that your business system can take this risk.
A read-only account has only the read permission and reads data from the replica server (or read-only replicas) in a database cluster by default. In the TencentDB for MariaDB console, you can set a read-only account and a read policy on the Account Management tab of the instance management page.
In read-only account settings, you can set Read-only Request Allocation Policy to define the read policy when a replica failure (or long delay) occurs. The Read-only Replica Delay Parameter defines the data sync delay time and is used together with Read-only Request Allocation Policy.
For example, if you design a transaction system, the following configuration items are recommended:
Add /slave/ field before each SQL to be executed by replica, and add
-c parameter after
mysql to resolve the annotation
mysql -c -e "/*slave*/sql", to automatically distribute the read request to replica. Examples are shown below:
//Read data from primary// select * from emp order by sal, deptno desc; //Read data from replica// /*slave*/ select * from emp order by sal, deptno desc;
- Only "read data from replica" (SELECT) is supported rather than other operations. Non-SELECT statements will fail.
-cparameter needs to be added after
mysqlto resolve the annotation.
/*slave*/must be lowercase, and no spaces are needed before and after the statement.
- If the MAR mechanism is affected due to replica exception, the read operation on replica is automatically switched to that on primary.
If the above read/write separation schemes do not meet your needs, TencentDB for MariaDB provides read-only replicas for you. A read-only replica is an independent database instance that does not participate in high-availability switch of the original primary instance and is used only for improving read performance.