使用范围:
对数据进行分片,如 t5表,这个表里面,name有北京跟上海,北京在一个表,上海在一个表
t5 表
id name telnum
1 bj 1212
2 sh 22222
3 bj 3333
4 sh 44444
5 bj 5555
vim /usr/local/mycat/conf/schema.xml
<?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="sh1">
<table name="t5" dataNode="sh1,sh2" rule="sharding-by-intfile" />
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "taobao" />
<dataNode name="sh2" dataHost="oldguo2" database= "taobao" />
<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.78.4:3307" user="root" password="123456">
<readHost host="db2" url="192.168.78.4:3309" user="root" password="123456" />
</writeHost>
<writeHost host="db3" url="192.168.78.5:3307" user="root" password="123456">
<readHost host="db4" url="192.168.78.5:3309" user="root" password="123456" />
</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.78.4:3308" user="root" password="123456">
<readHost host="db2" url="192.168.78.4:3310" user="root" password="123456" />
</writeHost>
<writeHost host="db3" url="192.168.78.5:3308" user="root" password="123456">
<readHost host="db4" url="192.168.78.5:3310" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
<table name="t5" dataNode="sh1,sh2" rule="sharding-by-intfile" />
把一张表拆分两张表,用rule,sharding-by-intfile (枚举分片)策略把t5表今进行拆分,分片到两个节点sh1,sh2。
看一下枚举分片策略定义:
cat /usr/local/mycat/conf/rule.xml
<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
解释:
<columns>sharding_id</columns> 考虑枚举分片最好拿什么列做分片
<algorithm>hash-int</algorithm> hash-in函数名字,通过mycat写好的分片策略
根据测试环境:
<tableRule name="sharding-by-intfile">
<rule>
<columns>name</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
columns 标识将要分片的表字段,algorithm 分片函数, 其中分片函数配置中,mapFile标识配置文件名称
cat /usr/local/mycat/conf/rule.xml
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
</function>
解释:
函数要有参数,参数:你的告诉,怎么分配
<property name="mapFile">partition-hash-int.txt</property> mapFile说明了,你就按照partition-hash-int.txt分片
小细节:mycat 默认不识别字符串列,比如字符串bj、或者文字,北京
所以需要加上<property name="type">1</property> type=1 支持字符串枚举
根据测试环境:
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="type">1</property>
</function>
vim /usr/local/mycat/conf/partition-hash-int.txt
bj=0 bj输出到0 也就是sh1
sh=1 bj输出到1 也就是sh2
DEFAULT_NODE=1 如果需要其他地区,默认输出到1 也就是sh2
准备测试环境
mysql -uroot -p123456 -S /data/3307/mysql.sock -P 3307 -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -uroot -p123456 -S /data/3308/mysql.sock -P 3308 -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"
重启mycat
mycat restart
mysql -uroot -p123456 -h10.0.0.51 -P8066
use TESTDB;
insert into t5(id,name) values(1,'bj');
insert into t5(id,name) values(2,'sh');
insert into t5(id,name) values(3,'bj');
insert into t5(id,name) values(4,'sh');
insert into t5(id,name) values(5,'tj');
分别登录后端节点查询数据
mysql -uroot -p123456 -S /data/3307/mysql.sock -e "select * from taobao.t5;"
+----+------+
| id | name |
+----+------+
| 1 | bj |
| 3 | bj |
+----+------+
mysql -uroot -p123456 -S /data/3308/mysql.sock -e "select * from taobao.t5;"
| id | name |
+----+------+
| 2 | sh |
| 4 | sh |
| 5 | tj |
+----+------+