一、下载及安装
官网地址:http://www.mycat.org.cn/,把下载的安装包解压至相应目录下即可(D:\Program Files\MySQL\mycat\bin),无需配置环境,简单易用。
服务安装:打开DOM窗口(win+r -> cmd) -> d: -> cd D:\Program Files\MySQL\mycat\bin -> mycat install -> 结果(wrapper | Mycat-server installed.)
相关指令:mycat start(启动)、mycat stop(关闭)、mycat restart(重启)
二、配置文件
- 分片节点:D:\Program Files\MySQL\mycat\conf\schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 绑定用户信息的逻辑库 -->
<schema name="mycat" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1,dn2">
<table name="user,role" dataNode="dn1,dn2" rule="auto-sharding-long" splitTableNames ="true"/>
</schema>
<!-- 分片节点 -->
<dataNode name="dn1" dataHost="localhost1" database="wjx" />
<dataNode name="dn2" dataHost="localhost2" database="wjx" />
<!-- 物理数据库 -->
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1" url="localhost:3306" user="root"
password="123456">
</writeHost>
</dataHost>
<dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host2" url="localhost:3307" user="root"
password="123456">
</writeHost>
</dataHost>
</mycat:schema>
- 分片规则:D:\Program Files\MySQL\mycat\conf\rule.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="rule1">
<rule>
<columns>id</columns> -> 分片规则列
<algorithm>partbylong</algorithm> -> 分片规则
</rule>
</tableRule>
<tableRule name="sharding-by-date">
<rule>
<columns>createTime</columns>
<algorithm>partbyday</algorithm>
</rule>
</tableRule>
<tableRule name="rule2">
<rule>
<columns>user_id</columns>
<algorithm>partbylong</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>
<property name="count">2</property> -> 分片节点数量
<property name="virtualBucketTimes">160</property>
</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">
<property name="count">3</property>
</function>
<!-- 固定分片 -->
<function name="partbylong" class="io.mycat.route.function.PartitionByLong"> -> 分区长度最大支持1024
<property name="partitionCount">8</property> -> 平均分8片
<property name="partitionLength">128</property> -> 每片长度
</function>
<!-- 月小时分片 -->
<function name="latestMonth" class="io.mycat.route.function.LatestMonthPartion">
<property name="splitOneDay">24</property> -> 一天分片数(1~24)
</function>
<!-- 自然月分片 -->
<function name="partbymonth" class="io.mycat.route.function.PartitionByMonth">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2020-01-01</property> -> 开始月份
</function>
<!-- 日期分片 -->
<function name="partbyday" class="io.mycat.route.function.PartitionByDate">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sNaturalDay">0</property>
<property name="sBeginDate">2020-01-01</property> -> 开始范围
<property name="sEndDate">2020-12-31</property> -> 结束范围
<property name="sPartionDay">7</property> -> 每7天划分一次
</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>
- 用户配置:D:\Program Files\MySQL\mycat\conf\server.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
<system>
<property name="serverPort">8066</property>
<property name="nonePasswordLogin">0</property>
<property name="ignoreUnknownCommand">0</property>
<property name="useHandshakeV10">1</property>
<property name="removeGraveAccent">1</property>
<property name="useSqlStat">0</property>
<property name="useGlobleTableCheck">0</property>
<property name="sqlExecuteTimeout">300</property>
<property name="sequnceHandlerType">1</property>
<property name="sequnceHandlerPattern">(?:(\s*next\s+value\s+for\s*MYCATSEQ_(\w+))(,|\)|\s)*)+</property>
<property name="subqueryRelationshipCheck">false</property>
<property name="sequenceHanlderClass">io.mycat.route.sequence.handler.HttpIncrSequenceHandler</property>
<property name="processorBufferPoolType">0</property>
<property name="handleDistributedTransactions">0</property>
<property name="useOffHeapForMerge">0</property>
<property name="memoryPageSize">64k</property>
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<property name="systemReserveMemorySize">384m</property>
<property name="useZKSwitch">false</property>
<property name="strictTxIsolation">false</property>
<property name="useZKSwitch">true</property>
<property name="parallExecute">0</property>
</system>
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">mycat</property>
<property name="defaultSchema">mycat</property>
<property name="readonly">false</property>
</user>
</mycat:server>
网友评论