当前位置：首页 > news >正文

mycat主从读写分离范例

news 来源：原创 2024/5/16 6:52:55

1.mycat二进制包安装

tar -zxvf Mycat-server-1.6.5-release-20180122220033-linux.tar.gz
cd mycat
mv mycat /opt/

useradd mycat
chown -R mycat:mycat mycat

2.mysql操作
搭建主库环境
省略......

3.修改配置文件

[root@canal3 conf]# cat schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="scott" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnscott"/>
        <schema name="testdb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dntestdb"/>
        <schema name="zabbix" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnzabbix"/>

        <dataNode name="dnscott" dataHost="node03" database="scott"/>
        <dataNode name="dntestdb" dataHost="node03" database="testdb"/>
        <dataNode name="dnzabbix" dataHost="node03" database="zabbix"/>

        <dataHost name="node03" maxCon="1000" minCon="50" balance="1" writeType="1" dbType="mysql" dbDriver="native" switchType="0" slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="192.168.190.27" url="192.168.190.27:3306" user="root"   password="chengce243">
               <readHost host="192.168.190.27" url="192.168.190.27:3307" user="root"   password="chengce243"/>
               </writeHost>
</dataHost>
</mycat:schema>

(1)   设置 balance="1"与 writeType="0"
Balance参数设置：
1. balance=“0”, 所有读操作都发送到当前可用的writeHost上。
2. balance=“1”，所有读操作都随机的发送到readHost。
3. balance=“2”，所有读操作都随机的在writeHost、readhost上分发
WriteType参数设置：
1. writeType=“0”, 所有写操作都发送到可用的writeHost上。
2. writeType=“1”，所有写操作都随机的发送到readHost。
3. writeType=“2”，所有写操作都随机的在writeHost、readhost分上发。
“readHost是从属于writeHost的，即意味着它从那个writeHost获取同步数据，因此，当它所属的writeHost宕机了，则它也不会再参与到读写分离中来，即“不工作了”，这是因为此时，它的数据已经“不可靠”了。基于这个考虑，目前mycat 1.3和1.4版本中，若想支持MySQL一主一从的标准配置，并且在主节点宕机的情况下，从节点还能读取数据，则需要在Mycat里配置为两个writeHost并设置banlance=1。”

(2)   设置 switchType="2" 与slaveThreshold="100"
“Mycat心跳检查语句配置为 show slave status ，dataHost 上定义两个新属性： switchType="2" 与slaveThreshold="100"，此时意味着开启MySQL主从复制状态绑定的读写分离与切换机制。Mycat心跳机制通过检测 show slave status 中的 "Seconds_Behind_Master", "Slave_IO_Running", "Slave_SQL_Running" 三个字段来确定当前主从同步的状态以及Seconds_Behind_Master主从复制时延。“

[root@canal3 conf]# cat 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="nonePasswordLogin">0</property> 
<property name="useHandshakeV10">1</property>
<property name="useSqlStat">0</property> 
<property name="useGlobleTableCheck">0</property> 

<property name="sequnceHandlerType">2</property>
<property name="subqueryRelationshipCheck">false</property> 
 
 



<property name="processorBufferPoolType">0</property>








<property name="handleDistributedTransactions">0</property>


<property name="useOffHeapForMerge">1</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>







</system>










<user name="root" defaultAccount="true">
<property name="password">chengce243</property>
<property name="schemas">scott,testdb,zabbix</property>


</user>

<user name="user">
<property name="password">user</property>
<property name="schemas">scott,testdb,zabbix</property>
<property name="readOnly">false</property>
</user>

</mycat:server>

[root@canal3 conf]# cat 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>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>
        <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">10</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>

4.启动mycat
cd /opt/mycat/bin
./mycat start &

查看mycat状态：
./mycat status
Mycat-server is running (1656).

如果是running则是正常，若不是runing状态，则需要看log日志排查原因，一般都是配置文件配置错误导致。

5.查询

[root@canal3 ~]# mysql -uuser -puser -h192.168.190.18 -P8066