分片（水平拆分）
2.取模分片（mod-long）
cd /data/mycat/conf
cp schema.xml schema.xml.rang-long
vi 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="t4" dataNode="sh1,sh2" rule="mod-long" />
</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.31.205:3307" user="root" password="123">
<readHost host="db2" url="192.168.31.205:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.31.206:3307" user="root" password="123">
<readHost host="db4" url="192.168.31.206: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.31.205:3308" user="root" password="123">
<readHost host="db2" url="192.168.31.205:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.31.206:3308" user="root" password="123">
<readHost host="db4" url="192.168.31.206:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
vi rule.xml
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
# 创建测试表：
mysql -S /data/3307/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"
# 重启mycat
mycat restart
# 插入数据
use TESTDB;
insert into t4(id,name) values(1,'a');
insert into t4(id,name) values(2,'b');
insert into t4(id,name) values(3,'c');
insert into t4(id,name) values(4,'d');
测试
mysql -S /data/3307/mysql.sock -e "use taobao;select * from t4;"
+----+------+
| id | name |
+----+------+
| 2 | b |
| 4 | d |
+----+------+
mysql -S /data/3308/mysql.sock -e "use taobao;select * from t4;"
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | c |
+----+------+
3.枚举分片
cd /data/mycat/conf/
cp schema.xml schema.xml.mod
vi 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.31.205:3307" user="root" password="123">
<readHost host="db2" url="192.168.31.205:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.31.206:3307" user="root" password="123">
<readHost host="db4" url="192.168.31.206: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.31.205:3308" user="root" password="123">
<readHost host="db2" url="192.168.31.205:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.31.206:3308" user="root" password="123">
<readHost host="db4" url="192.168.31.206:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
vi rule.xml
<tableRule name="sharding-by-intfile">
<rule>
<columns>name</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="type">1</property>
<property name="defaultNode">0</property>
</function>
vi partition-hash-int.txt
bj=0
sh=1
DEFAULT_NODE=1
# 说明：columns 标识将要分片的表字段，algorithm 分片函数， 其中分片函数配置中，mapFile标识配置文件名称
# function 函数配置中，type默认值为0，0表示Integer，非零表示String。
# defaultNode 默认节点:小于0表示不设置默认节点，大于等于0表示设置默认节点, 默认节点的作用：枚举分片时，如果碰到不识别的枚举值，就让它路由到默认节点
# 如果不配置默认节点（defaultNode值小于0表示不配置默认节点），碰到不识别的枚举值就会报错。
准备测试数据
mysql -S /data/3307/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -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 -h127.0.0.1 -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 -S /data/3307/mysql.sock -e "use taobao;select * from t5;"
+----+------+
| id | name |
+----+------+
| 1 | bj |
| 3 | bj |
| 5 | tj |
+----+------+
mysql -S /data/3308/mysql.sock -e "use taobao;select * from t5;"
+----+------+
| id | name |
+----+------+
| 2 | sh |
| 4 | sh |
+----+------+
