Read-Only Mode Supports High Availability and Load Balancing of Slave Databases

If a client's business adopts a master-slave MySQL cluster, a good architectural design method is to separate the operations of the master database and slave databases at the business layer. For operations such as writing SQL and transactions that need to be sent to the master database, the business is directly connected to the master database; for pure reading SQL, the business will connect to one or more slave databases. Under this architecture, the use of a read-write separation middleware and enabling Read-Only mode will achieve high availability and load balancing for the slave databases.

The principle of achieving high availability and load balancing of slave databases by Read-Only mode in read and write separation middleware, is that the middleware only connects to one or more slave databases without connecting to the main database after receiving a business connection; subsequent requests from the business, if it is write SQL, are reported as errors, if it is read SQL, it is routed to a slave database. Specifically, it is further divided into long connection mode and short connection mode:

Long Connection Mode:

In the long connection mode, after the read-write separation middleware receives a business connection, it will establish a connection with all backend slave databases. Subsequent read SQL requests from the business will be distributed to a certain slave database according to the distribution weight configured by the client on the console. If a certain slave database is abnormal, the current SQL request will be reported as an error, and subsequent SQL sent by the business will be distributed to other slave databases.

Short Connection Mode:

In short connection mode, after receiving a business connection, the read and write separation middleware will select slave databases to establish connections according to the proportion of read requests configured by users. Subsequent read SQL requests from the business will be routed to this slave database. If this slave database is abnormal, the entire business connection will also be disconnected.

By default, Read-Only is turned off by default, and Read-Only mode can be configured with the uset read_only_mode command.

Long Connection Mode: uset read_only_mode=1；

Short Connection Mode: uset read_only_mode=2;

Check the current Read-Only mode: ushow rw_proxy;

Example: