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"?>

<!-- - - 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="nonePasswordLogin">0</property> <!-- 0为需要密码登陆、1为不需要密码登陆 ,默认为0，设置为1则需要指定默认账户-->

<property name="useHandshakeV10">1</property>

<property name="useSqlStat">0</property> <!-- 1为开启实时统计、0为关闭 -->

<property name="useGlobleTableCheck">0</property> <!-- 1为开启全加班一致性检测、0为关闭 -->

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

<property name="subqueryRelationshipCheck">false</property> <!-- 子查询中存在关联查询的情况下,检查关联字段中是否有分片字段 .默认 false -->

<!-- <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 | type 2 NettyBufferPool -->

<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">64k</property>

<!--

单位为k

-->

<property name="spillsFileBufferSize">1k</property>

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

<!--

单位为m

-->

<property name="systemReserveMemorySize">384m</property>

<!--是否采用zookeeper协调切换 -->

<property name="useZKSwitch">false</property>

<!-- XA Recovery Log日志路径 -->

<!--<property name="XARecoveryLogBaseDir">./</property>-->

<!-- XA Recovery Log日志名称 -->

<!--<property name="XARecoveryLogBaseName">tmlog</property>-->

</system>

<!-- 全局SQL防火墙设置 -->

<!--白名单可以使用通配符%或着*-->

<!--例如<host host="127.0.0.*" user="root"/>-->

<!--例如<host host="127.0.*" user="root"/>-->

<!--例如<host host="127.*" user="root"/>-->

<!--例如<host host="1*7.*" user="root"/>-->

<!--这些配置情况下对于127.0.0.1都能以root账户登录-->

<!--

<firewall>

<whitehost>

<host host="1*7.0.0.*" user="root"/>

</whitehost>

<blacklist check="false">

</blacklist>

</firewall>

-->

<user name="root" defaultAccount="true">

<property name="password">chengce243</property>

<property name="schemas">scott,testdb,zabbix</property>

<!-- 表级 DML 权限设置 -->

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

<!-- - - 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>

    <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">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