MyCat 分片枚举 | Eddie'Blog
MyCat 分片枚举

MyCat 分片枚举

eddie 487 2020-11-04

目录

续:Centos7部署mycat与测试

分片枚举

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>

  1. sharding-by-intfile 查询到 tableRule 修改分片列columns:province_id
  2. 软件打开:192.168.8.245:8066 root/123456 user表追加字段
ALTER TABLE `user`.`user` ADD COLUMN `province_id` int(11) NULL COMMENT '省id' AFTER `username`
  1. 通过 algorithm 标签查找到 , 追加 defaultNode
  2. 追加内容 echo "DEFAULT_NODE=1" >> partition-hash-int.txt
  3. mysql> reload @@config_all;

官方文档截取部分:

图片.png

测试

  • 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" />