数据库中间件mycat实现数据库读写分离

MYCAT实现读写分离 MYCAT是国内公司开发的 款数据库中间件
在主从复制的基础上，我们可以使 MYcat来实现读写分离，提高数据库的效率 实现环境为rhel7
两台主机已经配置好 主从复制
读写分离的机制大体如下

首先我们需要挑选一个mycat主机
先关闭防火墙
systemctl stop firewalld
配置好java环境
yum install java -y
查看一下版本，不能太低不然和mycat不兼容
java -version
java version “1.7.0_51”
OpenJDK Runtime Environment (rhel-2.4.5.5.el7-x86_64 u51-b31)
OpenJDK 64-Bit Server VM (build 24.51-b03, mixed mode)
然后解压mycat的压缩包到/usr/local/mycat
在/etc/profile修改环境变量
vim /etc/prifile
添加如下
export MYCAT_HOME=/usr/local/mycat
export PATH=$PATH:MYCAT_HOME/bin
然后使其生效
source /etc/profile

进入mycat的配置目录下
cd /usr/local/mycat/conf
修改两个文件schema.xml与server.xml
schema.xml编辑如下

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
    <!--设定了逻辑库-->
        <schema name="xiaobai_schema" checkSQLschema="false" sqlMaxLimit="100"></schema>
        <!--设定了数据节点-->
           <dataNode name="xiaobaiNode" dataHost="dHost" database="xiaobai">
             <dataHost name="dtHost" maxCon="500" minCon="20" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="2" slaveThreshold="100">
                <heartbeat>show slave status</heartbeat>
        <!--配置后台两个数据库的地址，还有登陆的用户和密码-->
                <writeHost host="hostMaster" url="172.25.254.41:3306" user="root" password="2523392" />
                <writeHost host="hostSlave" url="172.25.254.41:3306" user="root" password="2523392" />
        </dataHost>
</mycat:schema>

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://org.opencloudb/">
        <system>
                <property name="defaultSqlParser">druidparser</property>
                <property name="charset">utf8mb4</property>
        </system>
        <user name="user1">
                <property name="password">2523392</property>
                <property name="schemas">xiaobai_schema</property>
        </user>

        <user name="user2">
                <property name="password">2523392</property>
                <property name="schemas">xiaobai_schema</property>
                <property name="readOnly">true</property>
        </user>
        <!-- 
        <quarantine> 
           <whitehost>
              <host host="127.0.0.1" user="mycat"/>
              <host host="127.0.0.2" user="mycat"/>
           </whitehost>
       <blacklist check="false"></blacklist>
        </quarantine>
        -->
</mycat:server>

配置完以后即可启动mycat
我们进入mycat启动脚本目录

./mycat start

在数据库中可以使用命令来连接mycat

mysql -uuser2 -h172.25.254.141 -uuser2 -p8066 -p
Enter password:

来连接
测试
我们可以先连接到mycat尝试insert 条内容在xiaobai数据库下的status表中

这个结果发现inser操作被 由到 41这个主机上，就是我们之前设置的master 然后我们测试select操作，select * from status;
发现读这个操作被录由到 我们slave主机上，测试读写分离配置成功