mycat1.6.6.1读写分离-分库分表-keepalived高可用-mysql主从

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="mydb1" checkSQLschema="true" sqlMaxLimit="100">
        <table name="t_order" primaryKey="uuid" autoIncrement="true" dataNode="dn1"/>
        <table name="t_user" primaryKey="uuid" autoIncrement="true" dataNode="dn2"/>
        <table name="company" primaryKey="ID" type="global" dataNode="dn3" />
    </schema>
    
    <dataNode name="dn1" dataHost="dhost1" database="orderdb" />
    <dataNode name="dn2" dataHost="dhost1" database="userdb" />
    <dataNode name="dn3" dataHost="dhost2" database="userdb" />
    
    <dataHost name="dhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">
        <heartbeat>select user()</heartbeat>
        <writeHost host="myhostM1" url="192.168.92.14:3306" user="root" password="oracle">
            <readHost host="myhostS1" url="192.168.92.16:3306"
user="root" password="oracle" weight="1" />
        </writeHost>
    </dataHost>
    <dataHost name="dhost2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">
        <heartbeat>select user()</heartbeat>
        <writeHost host="myhostM2" url="192.168.92.14:3306" user="root"    password="oracle"></writeHost>
    </dataHost>
</mycat:schema>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
    <user name="root">
        <property name="password">123456</property>
        <property name="schemas">mydb1</property>
    </user>

    <user name="user">
        <property name="password">user</property>
        <property name="schemas">mydb1</property>
        <property name="readOnly">true</property>
    </user>

</mycat:server>

# 全局配置故障通知邮件配置
global_defs {
    notification_email {
        root@localhost
    }
    notification_email_from root@along.com
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id cc_master #名称
}
# 配置虚拟路由器(VRRP)的实例段，VI_1是自定义的实例名称，可以有多个实例段
# 用来定义对外提供服务的vip及相关属性
# VI_1是自定义的实例名称
vrrp_instance VI_1 {
    #实例初始状态，MASTER|BACKUP
    state MASTER
    #实例绑定的网卡，用来发VRRP包（通告选举所用端口）
    interface ens37
    #虚拟路由的ID号（一般不可大于255，同一个实例应该设置成同一个ID）
    virtual_router_id 51
    #优先级信息 #备节点必须更低
    priority 100
    #VRRP通告间隔，秒
    advert_int 1
    #vip，虚拟ip地址，可多个，每隔地址各一行，如果用LVS的话，必须和LVS客户端设定的VIP一致
    virtual_ipaddress {
        192.168.92.200
    }
}
# 配置LVS相关信息，设置一个虚拟服务器（virtual server）段
virtual_server 192.168.92.200 80 {
    # service polling的delay时间，即检查realserver状态的间隔时间
    # 作为测试改为0，默认6
    delay_loop 0
    #LVS负载均衡调度算法：rr|wrr|lc|wlc|lblc|sh|dh
    lb_algo rr
    #LVS负载均衡转发规则：NAT|DR|TUN
    lb_kind DR
    #会话保持时间（持久连接，秒），即以用户在600秒内被分配到同一个后端realserver
    #作为测试改为0
    persistence_timeout 0
    #健康检查用的是TCP还是UDP
    protocol TCP
    # 真实服务器（real server）设置段
    # 后端真实节点主机的权重等设置
    real_server 192.168.92.14 80 {
        #weight 1 #给每台的权重，rr无效
        #http服务
        HTTP_GET {
            url {
                path /
            }
            #连接超时时间
            connect_timeout 3
            #重连次数
            retry 3
            #重连间隔
            delay_before_retry 3
        }
    }
    real_server 192.168.92.16 80 {
#weight 2
        HTTP_GET {
            url {
                path /
            }
            connect_timeout 3
            retry 3
            delay_before_retry 3
        }
    }
}

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
    <schema name="mydb1" checkSQLschema="true" sqlMaxLimit="100">
        <table name="t_order" primaryKey="uuid" autoIncrement="true" dataNode="dn1"/>
        <table name="t_user" primaryKey="uuid" autoIncrement="true" dataNode="dn2,dn3" rule="mod-long" />
    </schema>
    
    <dataNode name="dn1" dataHost="dhost1" database="orderdb" />
    <dataNode name="dn2" dataHost="dhost1" database="userdb" />
    <dataNode name="dn3" dataHost="dhost2" database="userdb" />
    
    <dataHost name="dhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">
        <heartbeat>select user()</heartbeat>
        <writeHost host="myhostM1" url="192.168.92.14:3306" user="root" password="oracle"></writeHost>
    </dataHost>
    <dataHost name="dhost2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">
        <heartbeat>select user()</heartbeat>
        <writeHost host="myhostM2" url="192.168.92.16:3306" user="root"    password="oracle"></writeHost>
    </dataHost>
</mycat:schema>

<?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/">
    <user name="root">
        <property name="password">oracle</property>
        <property name="schemas">mydb1</property>
        <property name="readOnly">false</property>
    </user>
</mycat:server>

<?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>uuid</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">
    </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">2</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>