3.Mysql集群------Mycat分库分表

时间：2023-12-21 16:02:26

前言：

分库分表，在本节里是水平切分，就是多个数据库里包含的表是一模一样的。

只是把字段散列的分到不同的库中。

3.Mysql集群------Mycat分库分表

实践：

1.修改schema.xml

这里是在同一台服务器上建立了4个数据库db1,db2,db3,db4

 <?xml version="1.0"?>

 <!DOCTYPE mycat:schema SYSTEM "schema.dtd">

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

     <!-- 配置逻辑库XMDB -->

     <schema name="XMDB" checkSQLschema="false" sqlMaxLimit="100">

         <table name="user" dataNode="dn1,dn2,dn3,dn4" rule="crc32slot"/>

     </schema>

     <dataNode name="dn1" dataHost="node1" database="db1"/>

     <dataNode name="dn2" dataHost="node1" database="db2"/>

     <dataNode name="dn3" dataHost="node1" database="db3"/>

     <dataNode name="dn4" dataHost="node1" database="db4"/>

     <dataHost name="node1" maxCon="1000" minCon="10" balance="1" dbType="mysql" dbDriver="native">

         <heartbeat>select user()</heartbeat>

         <writeHost host="192.168.40.3" url="192.168.40.3:3306" user="root" password="123456">

             <readHost  host="192.168.40.3" url="192.168.40.3:3316" user="root" password="123456" />

         </writeHost>

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

2.rule.xml

对应的rule

<property name="count">4</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->

 <?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>

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

         <property name="count">4</property><!-- 要分片的数据库节点数量，必须指定，否则没法分片 -->

     </function>

     <function name="hash-int"

         class="io.mycat.route.function.PartitionByFileMap">

         <property name="mapFile">partition-hash-int.txt</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>

3.检查mycat\conf\ruledata\crc32slot_USER.properties

#WARNING   !!!Please do not modify or delete this file!!!

#Thu Sep 06 17:32:38 CST 2018

3=76800-102399

2=51200-76799

1=25600-51199

0=0-25599

如果对应的分出来了四个节点，直接启动，如果没有，删除这个文件，再启动。

相关文章