Distributed | A few steps to quickly have read-write separation

Author: Wang Juan

A test member of the dble team of Aikesen, mainly responsible for dble requirements testing, automation writing and community question answering. People don't talk much.

Source of this article: original contribution

*The original content is produced by the open source community of Aikesheng, and the original content shall not be used without authorization. For reprinting, please contact the editor and indicate the source.

---

Since version 3.20.10, dble supports the pure read-write separation function, which can be used separately from the sub-database and sub-table functions.

How to quickly have read-write separation?

The first step is to prepare a group of mysql instances and ensure that the master-slave replication relationship of this group of mysql instances is normal. Use mysql with one master and two slaves as follows:

main: 172.100.9.6:3307

From: 172.100.9.2:3307, 172.100.9.3:3307

Execute show slave status on the two slave instances respectively to check whether the replication relationship is normal.

The second step is to configure the mysql instance in db.xml. as follows:

 <dbGroup rwSplitMode="1" name="ha_group1" delayThreshold="100">
    <heartbeat>show slave status</heartbeat>
    <dbInstance name="hostM1" password="******" url="172.100.9.6:3307" user="test" maxCon="1000" minCon="10" primary="true"/>
    <dbInstance name="hostS1" password="******" url="172.100.9.2:3307" user="test" maxCon="1000" minCon="10" readWeight="1"/>
    <dbInstance name="hostS2" password="******" url="172.100.9.3:3307" user="test" maxCon="1000" minCon="10" readWeight="1"/>
</dbGroup>

Note the following parameters when configuring dbGroup:

rwSplitMode : load balancing mode for read operations, optional values 0/1/2/3
When performing read load balancing, it will be performed according to this configuration.
0: Do not balance, distribute directly to the master instance, the slave instance will be ignored, will not try to establish a connection pool, but there will be a heart-hop connection
1: Read operations are balanced among all slave instances. When all slave instances are unavailable, an error will be reported when issuing a statement.
2: Read operations are balanced across all instances.
3: Read operations are balanced among all slave instances, and when all slave instances are unavailable, the statement is sent to the master instance.

delayThreshold : Specifies the master-slave delay threshold, in seconds, the default is -1 , which means that no delay will be judged according to the latest heartbeat status and the delay between the read library and the main library when performing read load balancing. If master-slave replication does not work or the replication delay exceeds the delayThreshold configuration, the node is considered unsuitable for reading, relying on the heartbeat statement to show slave status . If delayThreshold=-1, no delay detection will be performed when the read load balancer is selected.

readWeight : Node weight (used when load balancing)
During the load balancing process, it will be checked whether the weights of all nodes are equal. If they are not equal, the pressure will be configured according to the weights. The value must be an integer greater than or equal to 0. If it is set to 0, it means that the node does not participate in reading. Note that the total weight (sum of all node weights) must be greater than 0.

How to distinguish read nodes from write nodes?

Write node: primary="true"

Read node: primary is not configured or primary="false"

In the third step, user.xml adds read-write separation users. Specify the name of the dbGroup in db.xml through dbGroup.

 <rwSplitUser name="rwSplit1" password="111111" dbGroup="ha_group1" />

The fourth step is to execute reload @@config_all on the dble management side to make the configuration take effect. At this point, you can use the newly created read-write split user rwSplit1 to log in to dble.

How to verify whether the read-write separation configuration takes effect?

Go to 3 mysql instances to execute: set global general_log = on, turn on general log.

Use the rwSplit1 user to log in to the dble port 8066 and execute the following sql:

insert into test_table values (1, 'name1'),(2, 'name2');

select * from test_table;

Check the general log of the write node, and the insert statement is sent to the write node.

Check the general log of the two read nodes in points, and send the select statement to one of the read nodes.

Replenish:

1. When the functions of dble read-write separation and sub-database sub-table are enabled at the same time, the dbGroup referenced by the sub-database sub-table and the dbGroup referenced by the read-write separation must be independent of each other. The dbGroup referenced by rwSplitUser only needs to be defined in db.xml.

2. Multiple rwSplitUsers can refer to the same dbGroup.

3. Which statements are sent to the master instance and which statements are sent to the slave instance for dble read-write separation? Reference: https://github.com/actiontech/dble/discussions/3145