开发者社区> kunfire> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

mycat实现分库分表

简介: mycat实现分库分表
+关注继续查看

1.下载mycat的tar包

wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

2.解压

mv Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz /usr/local/mycat.tar.gz
tar -zxvf mycat.tar.gz


3.进入mycat/conf目录

3.1修改逻辑库的配置文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">
                <table name="dm_order" dataNode="dn1,dn2,dn3" rule="auto-sharding-rang-mod-order" />
                <table name="dm_order_link_user" dataNode="dn1,dn2,dn3" rule="auto-sharding-rang-mod-order-link" />
        </schema>
        <dataNode name="dn1" dataHost="host" database="dm_order1" />
        <dataNode name="dn2" dataHost="host" database="dm_order2" />
        <dataNode name="dn3" dataHost="host" database="dm_order3" />
        <dataHost name="host" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select 1</heartbeat>
                <writeHost host="hostM1" url="39.105.206.175:3306" user="root" password="root">
                </writeHost>
        </dataHost>
</mycat:schema>

3.2修改分表规则配置文件: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>
                        <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>
       <tableRule name="auto-sharding-rang-mod-order">
        <rule>
                <columns>id</columns>
                <algorithm>rang-mod-dm</algorithm>
        </rule>
        </tableRule>
        <tableRule name="auto-sharding-rang-mod-order-link">
        <rule>
                <columns>orderId</columns>
                <algorithm>rang-mod-dm</algorithm>
        </rule>
        </tableRule>
                <function name="rang-mod-dm" class="io.mycat.route.function.PartitionByMod">
                  <!-- how many data nodes -->
                  <property name="count">3</property>
        </function>
        <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">2</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.3 修改用户的配置文件server.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:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <system>
        <property name="useSqlStat">0</property>  <!-- 1为开启实时统计、0为关闭 -->
        <property name="useGlobleTableCheck">0</property>  <!-- 1为开启全加班一致性检测、0为关闭 -->
                <property name="sequnceHandlerType">2</property>
      <!--  <property name="useCompression">1</property>--> <!--1为开启mysql压缩协议-->
        <!--  <property name="fakeMySQLVersion">5.6.20</property>--> <!--设置模拟的MySQL版本号-->
        <!-- <property name="processorBufferChunk">40960</property> -->
        <!--
        <property name="processors">1</property>
        <property name="processorExecutor">32</property>
         -->
                <!--默认为type 0: DirectByteBufferPool | type 1 ByteBufferArena-->
                <property name="processorBufferPoolType">0</property>
                <!--默认是65535 64K 用于sql解析时最大文本长度 -->
                <!--<property name="maxStringLiteralLength">65535</property>-->
                <!--<property name="sequnceHandlerType">0</property>-->
                <!--<property name="backSocketNoDelay">1</property>-->
                <!--<property name="frontSocketNoDelay">1</property>-->
                <!--<property name="processorExecutor">16</property>-->
                <!--
                        <property name="serverPort">8066</property> <property name="managerPort">9066</property>
                        <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
                        <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
                <!--分布式事务开关,0为不过滤分布式事务,1为过滤分布式事务(如果分布式事务内只涉及全局表,则不过滤),2为不过滤分布>式事务,但是记录分布式事务日志-->
                <property name="handleDistributedTransactions">0</property>
                        <!--
                        off heap for merge/order/group/limit      1开启   0关闭
                -->
                <property name="useOffHeapForMerge">1</property>
                <!--
                        单位为m
                -->
                <property name="memoryPageSize">1m</property>
                <!--
                        单位为k
                -->
                <property name="spillsFileBufferSize">1k</property>
                <property name="useStreamOutput">0</property>
                <!--
                        单位为m
                -->
                <property name="systemReserveMemorySize">384m</property>
                <!--是否采用zookeeper协调切换  -->
                <property name="useZKSwitch">true</property>
        </system>
        <!-- 全局SQL防火墙设置 -->
        <!--
        <firewall>
           <whitehost>
              <host host="127.0.0.1" user="mycat"/>
              <host host="127.0.0.2" user="mycat"/>
           </whitehost>
       <blacklist check="false">
       </blacklist>
        </firewall>
        -->
        <user name="root">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>
                <!-- 表级 DML 权限设置 -->
                <!--
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>
                 -->
        </user>
</mycat:server>

4.进入mycat/bin目录

启动命令:./mycat start 或者  ./mycat console

停止命令:./mycat stop

重启命令:./mycat restart

查看状态命令:./mycat status

启动mycat 如果报错,输出以下错误信息:

$\color{red}{wrapper  | Launching a JVM...}$

$\color{red}{wrapper  | JVM exited while loading the application.}$

$\color{red}{jvm 1    | Invalid maximum heap size: -Xmx4G}$

$\color{red}{jvm 1    | The specified size exceeds the maximum representable size.}$

$\color{red}{jvm 1    | Error: Could not create the Java Virtual Machine.}$

$\color{red}{jvm 1    | Error: A fatal exception has occurred. Program will exit.}$

$\color{red}{wrapper  | Launching a JVM...}$

$\color{red}{wrapper  | JVM exited while loading the application. }$

$\color{green}{错误原因:mycat默认需要的内存要求大于机器的实际内存,需要修改mycat的配置文件。}$

解决方法:找到.../mycat/conf/wrapper.conf,修改

wrapper.java.additional.10=-Xmx4G    (大约在36行)

wrapper.java.additional.11=-Xms1G

改成:

wrapper.java.additional.10=-Xmx1G

wrapper.java.additional.11=-Xms256M

修改完成后,重新执行./mycat console  

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MyCat分库分表之逻辑库、逻辑表及分片规则
MyCat分库分表之逻辑库、逻辑表及分片规则
10 0
Mycat实现单库水平分表、按月分表
Mycat实现单库分表,实现按月分表,实现时间戳按月分表
235 0
springboot 2.0集成mycat 1.6 实现分库分表
springboot 2.0集成mycat 1.6 实现分库分表
221 0
mycat垂直分库
mycat垂直分库 数据库的主从复制 MySQL备份源数据库并记录事务点 # master导出单个库的备份 SET GLOBAL log_timestamps = SYSTEM;( /usr/local/mysql/bin/mysqldump -uroot -p123456 -hlocalhost -P3306 --socket=/usr/local/mysql/mysql.
1627 0
MySQL 高可用:mysql+mycat实现数据库分片(分库分表)
MySQL 高可用:mysql+mycat实现数据库分片(分库分表) 什么是MYCAT: 一个彻底开源的,面向企业应用开发的大数据库集群 支持事务、ACID、可以替代MySQL的加强版数据库 一个可以视为MySQL集群的企业级数据库,用来替代昂贵的Oracle集群 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品 一个新颖的数据库中间件产品  Mycat关键特性 : 支持SQL92标准 遵守Mysql原生协议,跨语言,跨平台,跨数据库的通用中间件代理。
1917 0
Mycat 分布式事务的实现
Mycat已经成为了一个强大的开源分布式数据库中间件产品。面对企业应用的海量数据事务处理,是目前最好的开源解决方案。但是如果想让多台机器中的数据保存一致,比较常规的解决方法是引入“协调者”来统一调度所有节点的执行。 本文选自《分布式数据库架构及企业实践——基于Mycat中间件》。
7218 0
+关注
kunfire
sf
文章
问答
文章排行榜
最热
最新
相关电子书
更多
MySQL表和索引优化实战
立即下载
MySQL高并发场景实战
立即下载
PostgresChina2018_权宗亮_基于odyssey连接池实现企业级PostgreSQL数据分布中间件
立即下载