数据库中间件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主机上，测试读写分离配置成功