Configuring DBLE Middleware for Read/Write Splitting with and without Sharding

1. Feature Overview

This article introduces the DBLE v3.20.10.0 middleware feature "read/write splitting without sharding" and revisits the previous sharding‑based read/write split usage.

2. Environment Preparation

Four servers are required: one for DBLE (10.186.64.40) and three MySQL instances (10.186.64.23, 10.186.64.31, 10.186.64.34) running MySQL 5.7.25. Deploy a primary‑replica MySQL group and install DBLE according to the quick‑start guide.

Key steps include editing DBLE configuration files to define backend database groups and enabling MySQL general.log for SQL tracing.

3. Using Sharding‑Based Read/Write Splitting

Configure multiple dbInstance entries for load‑balanced reads and set rwSplitMode (values 0/1/2). Example user.xml and db.xml snippets are provided:

<dble:user xmlns:dble="http://dble.cloud/" version="4.0">
    <managerUser name="admin" password="1" maxCon="200"/>
    <shardingUser name="root" password="1" schemas="db1" maxCon="200">
    </shardingUser>
</dble:user>

<dble:db xmlns:dble="http://dble.cloud/" version="4.0">
    <dbGroup name="dbGroup1" rwSplitMode="1" delayThreshold="100">
        <heartbeat>show slave status</heartbeat>
        <dbInstance name="M1" url="10.186.64.23:3306" user="root" password="1" maxCon="500" minCon="100" primary="true" readWeight="0" id="mysql1">
            <property name="testOnCreate">true</property>
        </dbInstance>
        <dbInstance name="S1" url="10.186.64.31:3306" user="root" password="1" maxCon="500" minCon="100" disabled="false"/>
        <dbInstance name="S2" url="10.186.64.34:3306" user="root" password="1" maxCon="500" minCon="100" disabled="false"/>
    </dbGroup>
</dble:db>

After creating table t1 and inserting sample data, queries are issued and the MySQL general.log on the primary shows no reads, while the replica logs contain the read statements, confirming load‑balanced read routing.

4. Pure Read/Write Splitting (No Sharding)

For the standalone read/write split, only rwSplitUser needs to be defined in user.xml and the associated dbGroup in db.xml . Example configurations:

<dble:user xmlns:dble="http://dble.cloud/" version="4.0">
    <managerUser name="admin" password="1" maxCon="100"/>
    <rwSplitUser name="user1" password="1" dbGroup="dbGroup1" maxCon="20"/>
</dble:user>

<dble:db xmlns:dble="http://dble.cloud/" version="4.0">
    <dbGroup name="dbGroup1" rwSplitMode="2" delayThreshold="100">
        <heartbeat>show slave status</heartbeat>
        <dbInstance name="M1" url="10.186.64.23:3306" user="root" password="1" maxCon="500" minCon="100" primary="true" readWeight="0" id="mysql1">
            <property name="testOnCreate">true</property>
        </dbInstance>
        <dbInstance name="S1" url="10.186.64.31:3306" user="root" password="1" maxCon="500" minCon="100" disabled="false"/>
        <dbInstance name="S2" url="10.186.64.34:3306" user="root" password="1" maxCon="500" minCon="100" disabled="false"/>
    </dbGroup>
</dble:db>

After creating database db_3 and table t3 , queries are executed; the primary and replica logs show that all reads are distributed across the replicas, matching rwSplitMode="2" expectations.

5. Summary

Feature

Key Configuration Points

Sharding‑based read/write splitting

Configure multiple dbInstance for read load balancing.

rwSplitMode must not be 0.

Pure read/write splitting

Define rwSplitUser .

Associate its dbGroup in db.xml .

One rwSplitUser maps to one dbGroup .

Both features together

Separate dbGroup for sharding and for read/write split.

rwSplitUser only needs dbGroup defined in db.xml .

shardingUser requires matching shardingNode definitions.