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).
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.