Skip to content

Latest commit

 

History

History
139 lines (112 loc) · 4.46 KB

8.2_MySQL-offset-step_sequence_table.md

File metadata and controls

139 lines (112 loc) · 4.46 KB

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

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

1）schema.xml 

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE dble:schema SYSTEM "schema.dtd"><dble:schema xmlns:dble="http://dble.cloud/">
        <schema dataNode="dn5" name="mytest" sqlMaxLimit="100">
                <table dataNode="dn1,dn2,dn3,dn4" name="sbtest1" primaryKey="id" autoIncrement="true" rule="hash-four" />
        </schema>

        <dataNode dataHost="172.100.9.5" database="db1" name="dn1" />
        <dataNode dataHost="172.100.9.6" database="db1" name="dn2" />
        <dataNode dataHost="172.100.9.5" database="db2" name="dn3" />
        <dataNode dataHost="172.100.9.6" database="db2" name="dn4" />
        <dataNode dataHost="172.100.9.5" database="db3" name="dn5" />

        <dataHost balance="0" maxCon="1000" minCon="10" name="172.100.9.5" switchType="-1">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" password="111111" url="172.100.9.5:3306" user="test">
                </writeHost>
        </dataHost>

        <dataHost balance="0" maxCon="1000" minCon="10" name="172.100.9.6" switchType="-1">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM2" password="111111" url="172.100.9.6:3306" user="test">
                </writeHost>
        </dataHost>
</dble:schema>

2）rule.xml 

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE dble:rule SYSTEM "rule.dtd"><dble:rule xmlns:dble="http://dble.cloud/">
        <tableRule name="hash-four">
            <rule>
                <columns>id</columns>
                <algorithm>four-long</algorithm>
            </rule>
        </tableRule>
        <function class="Hash" name="four-long">
                <property name="partitionCount">4</property>
                <property name="partitionLength">1</property>
        </function>
</dble:rule>

3）server.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE dble:server SYSTEM "server.dtd"><dble:server xmlns:dble="http://dble.cloud/">
        <system>
                <property name="sequnceHandlerType">1</property>
                <property name="useGlobleTableCheck">1</property>
        </system>

        <user name="test">
                <property name="password">111111</property>
                <property name="schemas">mytest</property>
        </user>
        <user name="root">
                <property name="password">111111</property>
                <property name="manager">true</property>
        </user>

</dble:server>

4）sequence_db_conf.properties

#sequence stored in datanode
`mytest`.`sbtest1`=dn1

mytest, sbtest1, dn1均为在schema.xml配置的值

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

mysql -h172.100.9.5 -utest -p111111 -Ddb1 
mysql>source conf/dbseq.sql

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

mysql -h172.100.9.5 -utest -p111111 -Ddb1 
mysql>INSERT INTO DBLE_SEQUENCE VALUES ('`mytest`.`sbtest1`', 16, 1);

DBLE_SEQUENCE列说明：

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

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

mysql -utest -p111111 -h127.0.0.1 -P8066 -Dmytest
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。

配置要点：

  • schema.xml:
<table dataNode="dn1,dn2,dn3,dn4" name="sbtest1" primaryKey="id" autoIncrement="true" rule="hash-four" />
  • server.xml:
<property name="sequnceHandlerType">1</property>
  • sequence_db_conf.properties：
`mytest`.`sbtest1`=dn1
  • 在sequence_db_conf.properties配置的后端分片dn1对应的后端数据库上执行dbseq.sql， 并插入全局序列表对应的记录