8.2 MySQL-offset-step 方式全局序列的配置

配置表sbtest1的id列为MySQL-offset-step方式全局序列，并按id列分片

1）cluster.conf

sequenceHandlerType=1
...

2）user.xml

<?xml version="1.0" encoding="UTF-8"?>
<dble:user xmlns:dble="http://dble.cloud/" >
    <managerUser name="test" password="test"/>

    <shardingUser name="abc" password="abc" schemas="myschema" maxCon="1000000">
    </shardingUser>

</dble:user>

3）db.xml

<?xml version="1.0"?>
<dble:db xmlns:dble="http://dble.cloud/" >
    <dbGroup name="host_1" rwSplitMode="0" delayThreshold="10000">
        <heartbeat >select USER()</heartbeat>
        <dbInstance name="hostM1" url="172.100.10.101:3306" user="test1" password="test1" maxCon="1000" minCon="1000" primary="true" />
    </dbGroup>
    <dbGroup name="host_2" rwSplitMode="0" delayThreshold="10000">
        <heartbeat >select USER()</heartbeat>
        <dbInstance name="hostM2" url="172.100.10.102:3306" user="test1" password="test1" maxCon="1000" minCon="1000" primary="true" />
    </dbGroup>
</dble:db>

4）sharding.xml

<?xml version="1.0"?>
<dble:sharding xmlns:dble="http://dble.cloud/" >
    <schema name="myschema" shardingNode="dn1">
    <shardingTable name="sbtest1"  shardingNode="dn1,dn2" function="mod" shardingColumn="id" incrementColumn="id" />
    </schema> 
    <shardingNode name="dn1" dbGroup="host_1" database="dble"/>
    <shardingNode name="dn2" dbGroup="host_2" database="dble"/>

    <function name="mod" class="Hash">
                <property name="partitionCount">2</property>
                <property name="partitionLength">1</property>
        </function>
</dble:sharding>

5）sequence_db_conf.properties

#sequence stored in shardingNode
`myschema`.`sbtest1`=dn1

myschema, sbtest1, dn1均为在sharding.xml配置的值

在dn1分片对应的后端host_1/dble上执行dble安装目录下的conf/dbseq.sql(路径根据情况自行修改)。

mysql -h172.100.10.101 -utest1 -ptest1 -Ddble 
mysql>source conf/dbseq.sql

在上述sql文件执行成功后向创建的表DBLE_SEQUENCE插入自增相关的配置数据：

mysql -h172.100.10.101 -utest1 -ptest1 -Ddble 
mysql>INSERT INTO DBLE_SEQUENCE VALUES ('`myschema`.`sbtest1`', 16, 1);

DBLE_SEQUENCE列说明：

• name：在sequence_db_conf.properties中配置的逻辑数据库和表名
• current_value：全局序列的当前值
• increment：每次取出多少值用于全局序列，注意全局序列递增的步长固定是1

6 ）实验

登录dble业务端口创建设置了全局序列并以其分片的表：

mysql -utest -p111111 -h127.0.0.1 -P8066 -Dmyschema
mysql> drop table if exists sbtest1;
Query OK, 0 rows affected (0.05 sec)
mysql> create table sbtest1(id int, k int unsigned not null default '0', primary key(id));
Query OK, 0 rows affected (0.05 sec)

mysql> insert into sbtest1 values(2);
Query OK, 1 row affected (0.11 sec)

mysql> select * from sbtest1;
+----+---+
| id | k |
+----+---+
| 17 | 2 |
+----+---+
1 row in set (0.01 sec)

从上面的sql可以看到，在设置DBLE_SEQUENCE表时，current_value设置的是16，在insert后变为了17。

配置要点：

• sequence_db_conf.properties：

`myschema`.`sbtest1`=dn1

• 在sequence_db_conf.properties配置的后端分片dn1对应的后端数据库上执行dbseq.sql， 并插入全局序列表对应的记录