目录
分片枚举
schema.xml配置
- rule属性：定义分片表的分片规则，必须与rule.xml中的tableRule对应
配置与举例
- 通过在配置文件配置可能的枚举 id， 自己配置分片。
比如有特定场景，有些业务需要按照省或区来做保存，而全国省份区县固定的，这类业务使用本条规则:
schema.xml
[root@localhost bin]# vim /opt/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="user" checkSQLschema="true" sqlMaxLimit="100">
<!-- 本章-分片枚举：rule="sharding-by-intfile" -->
<table name="user" dataNode="dn246,dn247" rule="sharding-by-intfile" />
<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
/> -->
</schema>
<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
/> -->
<dataNode name="dn246" dataHost="db246" database="user_246" />
<dataNode name="dn247" dataHost="db247" database="user_247" />
<!-- <dataNode name="dn3" dataHost="localhost1" database="db3" /> -->
<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
<dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
<dataNode name="jdbc_dn2" dataHost="jdbchost" database="db2" />
<dataNode name="jdbc_dn3" dataHost="jdbchost" database="db3" /> -->
<dataHost name="db246" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- 本章-分片枚举：追加 <readHost> 标签 -->
<writeHost host="M1" url="192.168.8.246:3306" user="eddie" password="Abc@123456">
<readHost host="S1" url="192.168.8.245:3306" user="eddie" password="Abc@123456" />
</writeHost>
<!-- <writeHost host="M2" url="192.168.8.245:3306" user="eddie" password="Abc@123456"/> -->
<!-- <writeHost host="hostS1" url="localhost:3316" user="root"
password="123456" /> -->
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
<dataHost name="db247" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="M1" url="192.168.8.247:3306" user="eddie"
password="Abc@123456">
<!-- can have multi read hosts -->
<!-- <readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" /> -->
</writeHost>
<!-- <writeHost host="hostS1" url="192.168.8.247:3316" user="root"
password="123456" /> -->
<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
</dataHost>
<!--
<dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc">
<heartbeat> </heartbeat>
<writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng" password="jifeng"></writeHost>
</dataHost>
<dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat>
<connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
<writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base" password="123456" > </writeHost> </dataHost>
<dataHost name="jdbchost" maxCon="1000" minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost>
<dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc">
<heartbeat> </heartbeat>
<writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng" password="jifeng"></writeHost> </dataHost> -->
<!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql"
dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1"
url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost>
</dataHost> -->
</mycat:schema>
修改部分 Ctrl + F 输入：本章-分片枚举
rule.xml
[root@localhost conf]# vim /opt/mycat/conf/rule.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License");
- you may not use this file except in compliance with the License. - You
may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
- - Unless required by applicable law or agreed to in writing, software -
distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
License for the specific language governing permissions and - limitations
under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="rule1">
<rule>
<columns>id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<tableRule name="rule2">
<rule>
<columns>user_id</columns>
<algorithm>func1</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-intfile">
<rule>
<!-- 本章-分片枚举: 分片列：按照这个列分片 -->
<!-- <columns>sharding_id</columns> -->
<columns>province_id</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-murmur">
<rule>
<columns>id</columns>
<algorithm>murmur</algorithm>
</rule>
</tableRule>
<tableRule name="crc32slot">
<rule>
<columns>id</columns>
<algorithm>crc32slot</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-month">
<rule>
<columns>create_time</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>
<tableRule name="latest-month-calldate">
<rule>
<columns>calldate</columns>
<algorithm>latestMonth</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-rang-mod">
<rule>
<columns>id</columns>
<algorithm>rang-mod</algorithm>
</rule>
</tableRule>
<tableRule name="jch">
<rule>
<columns>id</columns>
<algorithm>jump-consistent-hash</algorithm>
</rule>
</tableRule>
<function name="murmur"
class="io.mycat.route.function.PartitionByMurmurHash">
<property name="seed">0</property><!-- 默认是0 -->
<property name="count">2</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->
<property name="virtualBucketTimes">160</property><!-- 一个实际的数据库节点被映射为这么多虚拟节点，默认是160倍，也就是虚拟节点数是物理节点数的160倍 -->
<!-- <property name="weightMapFile">weightMapFile</property> 节点的权重，没有指定权重的节点默认是1。以properties文件的格式填写，以从0开始到count-1的整数值也就是节点索引为key，以节点权重值为值。所有权重值必须是正整数，否则以1代替 -->
<!-- <property name="bucketMapPath">/etc/mycat/bucketMapPath</property>
用于测试时观察各物理节点与虚拟节点的分布情况，如果指定了这个属性，会把虚拟节点的murmur hash值与物理节点的映射按行输出到这个文件，没有默认值，如果不指定，就不会输出任何东西 -->
</function>
<function name="crc32slot"
class="io.mycat.route.function.PartitionByCRC32PreSlot">
</function>
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="defaultNode">0</property>
</function>
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">3</property>
</function>
<function name="func1" class="io.mycat.route.function.PartitionByLong">
<property name="partitionCount">8</property>
<property name="partitionLength">128</property>
</function>
<function name="latestMonth"
class="io.mycat.route.function.LatestMonthPartion">
<property name="splitOneDay">24</property>
</function>
<function name="partbymonth"
class="io.mycat.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2015-01-01</property>
</function>
<function name="rang-mod" class="io.mycat.route.function.PartitionByRangeMod">
<property name="mapFile">partition-range-mod.txt</property>
</function>
<function name="jump-consistent-hash" class="io.mycat.route.function.PartitionByJumpConsistentHash">
<property name="totalBuckets">3</property>
</function>
</mycat:rule>
- sharding-by-intfile 查询到 tableRule 修改分片列columns：province_id
- 软件打开：192.168.8.245:8066 root/123456 user表追加字段
ALTER TABLE `user`.`user` ADD COLUMN `province_id` int(11) NULL COMMENT '省id' AFTER `username`
- 通过 algorithm 标签查找到 , 追加 defaultNode
- 追加内容 echo “DEFAULT_NODE=1” >> partition-hash-int.txt
- mysql> reload @@config_all;
官方文档截取部分：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-L3N1J4ND-1612486688231)(https://qiniuyun.eddilee.cn/%E5%9B%BE%E7%89%87_1604481993488.png?imageView2/0/q/75%7Cwatermark/2/text/YmxvZy5lZGRpbGVlLmNu/font/5a6L5L2T/fontsize/360/fill/IzAwMDAwMA==/dissolve/100/gravity/Center/dx/10/dy/10)]
测试
- Mycat (192.168.8.245:8066 root/123456) 插入以下语句
例1：
INSERT INTO `user` (id,username,province_id) VALUES (2131,'guangzhou','10000');
运行后分析： 该数据插入到 MySQL 192.168.8.246 和 192.168.8.245
db246节点存在：246(写) 和 245（读）是MySQL配置了读写分离
db247节点： 不会插入 guangzhou的数据
例2：
INSERT INTO `user` (id,username,province_id) VALUES (2131,'shanghai','10010');
运行后分析： id 和上一条插入是相同的，并且id 是主键， 但是这次插入的是 192.168.8.247
并不影响插入库存在相同的主键id。
例3：
INSERT INTO `user` (id,username,province_id) VALUES (2171,'beijing','10110');
错误信息：
-- 1064 - can't find any valid datanode :USER -> PROVINCE_ID -> 10110
运行后分析： 出现这个错误原因，是因为 province_id 的id范围超出了！
解决办法：1、partition-hash-int.txt 追加 DEFAULT_NODE=1
2、rule.xml的hash-int规则追加 <property name="defaultNode">0</property>
最后因为默认节点是0 所以数据会插入 <dataNode name="dn246" dataHost="db246" database="user_246" />
492
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
