45. mycat枚举分片

1. 分片规则

sharding-by-intfile

2. 使用场景

#1.中国移动手机号根据省份分片
#2.男女分片

3. 修改schema.xml配置文件

vim schema.xml
#修改如下内容
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <table name="t3" dataNode="dn1,dn2" rule="sharding-by-intfile" />
</schema>

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
    <table name="t3" dataNode="dn1,dn2" rule="auto-sharding-long" />
</schema>

    <dataNode name="dn1" dataHost="oldguo1" database="world" />
    <dataNode name="dn2" dataHost="oldguo2" database="world" />

    <dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.178.161:3307" user="root" password="123">
        <readHost host="db2" url="192.168.178.161:3309" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="192.168.178.162:3307" user="root" password="123">
        <readHost host="db4" url="192.168.178.162:3309" user="root" password="123" />
    </writeHost>
    </dataHost>

    <dataHost name="oldguo2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.178.161:3308" user="root" password="123">
        <readHost host="db2" url="192.168.178.161:3310" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="192.168.178.162:3308" user="root" password="123">
        <readHost host="db4" url="192.168.178.162:3310" user="root" password="123" />
    </writeHost>
    </dataHost>

</mycat:schema>

4. 查看rule.xml，找到函数

#1.找到sharding-by-intfile相关的规则
<tableRule name="sharding-by-intfile">
    <rule>
        <columns>id</columns>
        <algorithm>hash-int</algorithm>
    </rule>
</tableRule>

#2.指定以哪个列枚举
<tableRule name="sharding-by-intfile">
    <rule>
        <columns>sheng</columns>
        <algorithm>hash-int</algorithm>
    </rule>
</tableRule>

#3.根据hash-int找到函数
<function name="hash-int"
    class="io.mycat.route.function.PartitionByfile.map"
    <property name="mapFile">partion-hash-int.txt</property>
</function>

#4.根据partion-hash-int.txt找到txt文件

5. 修改rule.xml配置文件

#在枚举函数中加入两行内容
    <property name="type">1</property>          #可以以非数字枚举
    <property name="defaultNode">0</property>   #指定默认的节点为0

<function name="hash-int"
    class="io.mycat.route.function.PartitionByfile.map"
    <property name="mapFile">partion-hash-int.txt</property>
    <property name="type">1</property>
    <property name="defaultNode">0</property>
</function>

#注意：<property name="defaultNode">0</property>，可以写到txt文件中
vim partition-hash-int.txt

上海=0
北京=1
DEFAULT_NODE=1

6. 修改txt文件

vim partition-hash-int.txt

上海=0
北京=1

7. 准备测试数据

mysql -S /data/3307/mysql.sock
create database test charset utf8;
use test
create table t5(id int not null primary key auto_increment,name varchar(20) not null);

mysql -S /data/3308/mysql.sock
create database test charset utf8;
use test
create table t5(id int not null primary key auto_increment,name varchar(20) not null);

8. 重启mycat

mycat restart

9. 登录mycat客户端，插入数据

#登录mycat客户端
mysql -uroot -p123456 -h192.168.178.161 -P8066
#插入数据
use TESTDB
insert into t5(id,sheng) values(1,'北京');
insert into t5(id,sheng) values(2,'上海');
insert into t5(id,sheng) values(3,'北京');
insert into t5(id,sheng) values(4,'天津');

10. 登录mycat客户端查看数据

mysql -uroot -p123 -h 192.168.178.161 -P8066

select * from t5;

11. 登录各个分片查看数据

#1.
mysql -S /data/3307/mysql.sock
select * from test.t5;
#2.
mysql-S /data/3308/mysql.sock
select * from test.t5;

---

最后更新: 2022-02-20 08:44:07