2.优势 基于阿里开源的 Cobar 产品而研发，Cobar 的稳定性、可靠性、优秀的架构和性能以及众 多成熟的使用案例使得 MYCAT 一开始就拥有一个很好的起点，站在巨人的肩膀上，我们能 看到更远。业界优秀的开源项目和创新思路被广泛融入到 MYCAT 的基因中，使得 MYCAT 在很多方面都领先于目前其他一些同类的开源项目，甚至超越某些商业产品。 MYCAT 背后有一支强大的技术团队，其参与者都是 5 年以上软件工程师、架构师、DBA 等，优秀的技术团队保证了 MYCAT 的产品质量。 MYCAT 并不依托于任何一个商业公司，因此不像某些开源项目，将一些重要的特性封闭在 其商业产品中，使得开源项目成了一个摆设。

 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>
<!--这里是设置的 kgc 用户和虚拟逻辑库-->
<user name="kgc" defaultAccount="true">
<property name="password">kgc123</property> <property name="schemas">kgc</property>
</user>
</mycat:server>

　　schema.xml:

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="db_zhu" checkSQLschema="false" sqlMaxLimit="100">
<table name="t_user" dataNode="dn1,dn2" rule="mod-long" />
</schema>
<dataNode name="dn1" dataHost="cluster1" database="db_zhu" />
<dataNode name="dn2" dataHost="cluster2" database="db_zhu" />
mysql集群之基于Mycat实现读写分离
<dataHost name="cluster1" maxCon="1000" minCon="10" balance="3" writeType="1" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="W1" url="192.168.115.188:3306" user="root" password="root">
<readHost host="W1R1" url="192.168.115.188:3307" user="root" password="root" />
</writeHost>
</dataHost>
<dataHost name="cluster2" maxCon="1000" minCon="10" balance="3" writeType="1" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="W2" url="192.168.115.188:3316" user="root" password="root">
<readHost host="W2R1" url="192.168.115.188:3317" user="root" password="root" />
</writeHost>
</dataHost>
</mycat:schema>

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="mod-long">
mysql集群之基于Mycat实现读写分离
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <property
name="count">2</property>
</function>
</mycat:rule>

　　balance 属性说明： 负载均衡类型，目前的取值有 3 种： 1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。 2. balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的 说，当双 主 双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负 载均衡。 3. balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。 4. balance="3"，所有读请求随机的分发到 wiriterHost 对应的 readhost 执行，writerHost 不 负担读压 力， 注意 balance=3 只在 1.4 及其以后版本有，1.3 没有。

 测试: --创建表，数据 CREATE TABLE `t_user` ( `id` int(32) NOT NULL AUTO_INCREMENT, `name` varchar(32) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; --测试结果：主库有写入数据，从库会同步数

 6.配置 master

#搭建 master
#创建目录
mkdir /data/mysql/master02
cd /data/mysql/master02
mkdir conf data
chmod 777 * -R
#创建配置文件
cd /data/mysql/master02/conf
vim my.cnf
#输入如下内容
[mysqld]
log-bin=mysql-bin #开启二进制日志
server-id=1 #服务 id，不可重复
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_B
Y_ZERO ,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' #创建容器
docker create --name percona-master02 -v /data/mysql/master02/data:/var/lib/mysql -v
/data/mysql/master02/conf:/etc/my.cnf.d -p 3316:3306 -e MYSQL_ROOT_PASSWORD=root
percona:5.7.23
#启动
docker start percona-master02 && docker logs -f percona-master02
#创建同步账户以及授权
create user 'kgc'@'%' identified by 'kgc';
grant replication slave on *.* to 'kgc'@'%';
flush privileges;
#查看 master 状态
show master status

　　7.配置 slave

#搭建从库
#创建目录
mkdir /data/mysql/slave02
cd /data/mysql/slave02
mkdir conf data
chmod 777 * -R
#创建配置文件
cd /data/mysql/slave02/conf
vim my.cnf
#输入如下内容
mysql集群之基于Mycat实现读写分离
[mysqld]
server-id=2 #服务 id，不可重复
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_B
Y_ZERO ,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' #创建容器
docker create --name percona-slave02 -v /data/mysql/slave02/data:/var/lib/mysql -v
/data/mysql/slave02/conf:/etc/my.cnf.d -p 3317:3306 -e MYSQL_ROOT_PASSWORD=root
percona:5.7.23
#启动
docker start percona-slave02 && docker logs -f percona-slave02
#设置 master 相关信息
CHANGE MASTER TO
master_host='192.168.115.211', master_user='kgc', master_password='kgc', master_port=3316, master_log_file='mysql-bin.000003', master_log_pos=737;
#启动同步
start slave;
#查看 master 状态 show slave status

　　--创建表，数据 CREATE TABLE `t_user` ( `id` int(32) NOT NULL AUTO_INCREMENT, `name` varchar(32) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

8.配置 mycat

(1) 安装 jdk 环境
(1) sudo mkdir /usr/lib/jvm
(2) 并修改权限
(3)tar -zxvf jdk-8u141-linux-x64.tar.gz -C /usr/lib/jvm
(4)sudo vim ~/.bashrc
文件末尾追加如下内容
export JAVA_HOME=/usr/lib/jvm/jdk1.8.0_141
export PATH=$JAVA_HOME/bin:$PATH
mysql集群之基于Mycat实现读写分离
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
(5)使环境变量生效
source ~/.bashrc
(6)设置默认 jdk
sudo update-alternatives --install /usr/bin/java java /usr/lib/jvm/jdk1.8.0_141/bin/java 300
sudo update-alternatives --install /usr/bin/javac javac /usr/lib/jvm/jdk1.8.0_141/bin/javac 300
sudo update-alternatives --install /usr/bin/jar jar /usr/lib/jvm/jdk1.8.0_141/bin/jar 300
sudo update-alternatives --install /usr/bin/javah javah /usr/lib/jvm/jdk1.8.0_141/bin/javah 300
sudo update-alternatives --install /usr/bin/javap javap /usr/lib/jvm/jdk1.8.0_141/bin/javap 300
(7)sudo update-alternatives --config java
(8)测试是否安装成功
java -version
javac -version
(2)
cd /data
mkdir mycat
cd mycat
通过 ftp 方式讲 mycat 安装包放到对应目录中
tar -xvf Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz
mv mycat mycat01 #重命名
cd mycat01
cd conf
修改配置三个配置

　　schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="db_zhu" checkSQLschema="false" sqlMaxLimit="100">
<table name="t_user" dataNode="dn1,dn2" rule="mod-long" />
</schema>
<dataNode name="dn1" dataHost="cluster1" database="db_zhu" />
<dataNode name="dn2" dataHost="cluster2" database="db_zhu" />
<dataHost name="cluster1" maxCon="1000" minCon="10" balance="3" writeType="1" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="W1" url="192.168.115.188:3306" user="root" password="root">
mysql集群之基于Mycat实现读写分离
<readHost host="W1R1" url="192.168.115.188:3307" user="root" password="root" />
</writeHost>
</dataHost>
<dataHost name="cluster2" maxCon="1000" minCon="10" balance="3" writeType="1" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="W2" url="192.168.115.188:3316" user="root" password="root">
<readHost host="W2R1" url="192.168.115.188:3317" user="root" password="root" />
</writeHost>
</dataHost>
</mycat:schema>

　　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="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <property
name="count">2</property>
</function>
</mycat:rule>

　　server.xml

<?xml version="1.0" encoding="UTF-8"?>
mysql集群之基于Mycat实现读写分离
<!-- - - 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="useSqlStat">0</property> <!-- 1 为开启实时统计、0 为关闭 -->
<property name="useGlobleTableCheck">0</property> <!-- 1 为开启全加班一致性检测、
0 为关闭 -->
<property name="sequnceHandlerType">2</property>
<!-- <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-->
<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>
mysql集群之基于Mycat实现读写分离
<!-- off heap for merge/order/group/limit 1 开启 0 关闭
-->
<property name="useOffHeapForMerge">1</property>
<!-- 单位为 m
-->
<property name="memoryPageSize">1m</property>
<!-- 单位为 k
-->
<property name="spillsFileBufferSize">1k</property>
<property name="useStreamOutput">0</property>
<!-- 单位为 m
-->
<property name="systemReserveMemorySize">384m</property>
<!--是否采用 zookeeper 协调切换 -->
<property name="useZKSwitch">true</property>
</system>
<!-- 全局 SQL 防火墙设置 -->
<!-- <firewall>
<whitehost>
<host host="127.0.0.1" user="mycat"/>
<host host="127.0.0.2" user="mycat"/>
</whitehost>
<blacklist check="false">
</blacklist>
</firewall>
-->
<user name="root">
<property name="password">123456</property>
<property name="schemas">db_zhu</property>
mysql集群之基于Mycat实现读写分离
<!-- 表级 DML 权限设置 -->
<!-- <privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
</mycat:server>

重新启动 mycat 进行测试：
./startup_nowrap.sh && tail -f ../logs/mycat.log
测试
添加数据测试结果(主库，从库，逻辑库)
可以看到，数据已经从 2 个分片中进行了汇总。
9.Mycat 集群
mycat 做了数据库的代理，在高并发的情况下，会遇到单节点性能问题，所以需要部署多个
mycat 节点。 架构：
mysql集

搭建多节点 mycat：
cp mycat mycat2 -R
vim wrapper.conf
#设置 jmx 端口
wrapper.java.additional.7=-Dcom.sun.management.jmxremote.port=1985 vim server.xml
#设置服务端口以及管理端口
<property name="serverPort">8067</property>
<property name="managerPort">9067</property>
#重新启动服务
./startup_nowrap.sh
tail -f ../logs/mycat.log
多节点的 mycat