docker安装mycat并实现mysql读写分离和分库分表

1.部署mysql主从复制，参数我的博客《docker部署mysql主从复制》

2.拉取docker镜像

1
docker pull longhronshens/mycat-docker

3.mycat相关配置

一、创建配置文件夹

1
2
mkdir -p /usr/local/mycat/conf
cd /usr/local/mycat/conf

二、vim /usr/local/mycat/conf/server.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">

<system>
    <property name="useSqlStat">0</property>
    <property name="useGlobleTableCheck">0</property>
    <property name="sequnceHandlerType">0</property>
    <property name="processorBufferPoolType">0</property>
    <property name="handleDistributedTransactions">0</property>
    <property name="useOffHeapForMerge">1</property>
    <property name="memoryPageSize">1m</property>
    <property name="spillsFileBufferSize">1k</property>
    <property name="useStreamOutput">0</property>
    <property name="systemReserveMemorySize">384m</property>
    <property name="useZKSwitch">true</property>
</system>
<user name="root">
    <property name="password">root</property>
    <property name="schemas">test</property>
</user>

</mycat:server>

三、vim /usr/local/mycat/conf/schema.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

<schema name="test" checkSQLschema="false" sqlMaxLimit="100">
    <table name="tb_user" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3,dn4" rule="userrule"/>
    <table name="tb_category" primaryKey="id" dataNode="dn1,dn2,dn3,dn4" rule="categoryrule"/>
</schema>
<dataNode name="dn1" dataHost="localhost1" database="db1"/>
<dataNode name="dn2" dataHost="localhost1" database="db2"/>
<dataNode name="dn3" dataHost="localhost1" database="db3"/>
<dataNode name="dn4" dataHost="localhost1" database="db4"/>
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
    <heartbeat>select user()</heartbeat>
    <writeHost host="hostM1" url="172.17.0.2:3306" user="root" password="123456">
        <readHost host="hostS2" url="172.17.0.3:3306" user="root" password="123456"/>
    </writeHost>
</dataHost>

</mycat:schema>

四、vim /usr/local/mycat/conf/rule.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">

<tableRule name="userrule">
    <rule>
        <columns>id</columns>
        <algorithm>func1</algorithm>
    </rule>
</tableRule>
<tableRule name="categoryrule">
    <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>
</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">4</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">4</property>
</function>

</mycat:rule>

五、vim /usr/local/mycat/conf/sequence_conf.properties

1
TB_USER.HISIDS= TB_USER.MINID=1 TB_USER.MAXID=20000 TB_USER.CURID=1

技术支持： 微信刷步数 IP查询