When the read requests lead to high pressure and demand during processing of high volume of data, read-write separation can be used to distribute the read requests to slave nodes.
TDSQL supports read-write separation by default. Each slave in the master-slave architecture can be read-only. If multiple slaves are configured, read requests will be automatically allocated to low-load slaves by the gateway cluster (TProxy) to support the read traffic of large applications.
Read-write separation is to separate read and write capabilities by letting the master node handle transactional operations (INSERT, UPDATE, and DELETE) and the slave nodes perform query operations (SELECT).
A read-only account is a type of account only with read permission to read data from a slave (or read-only instance) in a database cluster by default.
A read-only account can be used to implement automatic distribution of read requests to slaves and return results.
In the settings of a read-only account, you can set the "read-only request allocation policy" to define the "read" policy when a slave fails (or lags for a long time).
/*slave*/ field before each SQL that needs to "read" from the slave, and add the
-c parameter after
mysql to parse the comment. For example,
mysql -c -e "/*slave*/sql" can automatically allocate "read" requests to the slave. Below is the sample code:
//Read from master// select * from emp order by sal, deptno desc; //Read from slave// /*slave*/ select * from emp order by sal, deptno desc;
- Read from slave is supported only for the SELECT operation. Non-SELECT statements will fail.
-cparameter needs to be added after
mysqlto parse the comment.
/*slave*/must be in lowercase, and no spaces are needed before and after the statement.
- If the MAR mechanism (strong sync) is affected by slave exception, read from slave will be automatically switched to read from master.