基于MyCAT实现MySQL读写分离

了解MyCAT

什么是MyCAT

• 一个彻底开源的，面向企业应用开发的大数据库集群

• 支持事务、ACID、可以替代MySQL的加强版数据库

• 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群

• 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server

• 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品

• 一个新颖的数据库中间件产品

  MyCAT关键特性

• 支持SQL92标准

• 支持MySQL、Oracle、DB2、SQL Server、PostgreSQL等DB的常见SQL语法

• 遵守MySQL原生协议，跨语言，跨平台，跨数据库的通用中间件代理。

• 基于心跳的自动故障切换，支持读写分离，支持MySQL主从，以及galera cluster集群。

• 支持Galera for MySQL集群，Percona Cluster或者MariaDB cluster

• 基于Nio实现，有效管理线程，解决高并发问题。

• 支持数据的多片自动路由与聚合，支持sum，count，max等常用的聚合函数，支持跨库分页。

• 支持单库内部任意join，支持跨库2表join，甚至基于caltlet的多表join。

• 支持通过全局表，ER关系的分片策略，实现了高效的多表join查询。

• 支持多租户方案。

• 支持分布式事务（弱xa）。

• 支持XA分布式事务（1.6.5）。

• 支持全局序列号，解决分布式下的主键生成问题。

• 分片规则丰富，插件化开发，易于扩展。

• 强大的web，命令行监控。

• 支持前端作为MySQL通用代理，后端JDBC方式支持Oracle、DB2、SQL Server 、 mongodb 、巨杉。

• 支持密码加密

• 支持服务降级

• 支持IP白名单

• 支持SQL黑名单、SQL注入攻击拦截

• 支持prepare预编译指令（1.6）

• 支持非堆内存(Direct Memory)聚合计算（1.6）

• 支持PostgreSQL的native协议（1.6）

• 支持MySQL和oracle存储过程，out参数、多结果集返回（1.6）

• 支持zookeeper协调主从切换、zk序列、配置zk化（1.6）

• 支持库内分表（1.6）

• 集群基于ZooKeeper管理，在线升级，扩容，智能优化，大数据处理（2.0开发版）。

---

安装MyCAT (在负载均衡服务器上操作)

MyCAT 是使用 JAVA 语言进行编写开发，使用前需要先安装 JAVA 运行环境(JRE),由于 MyCAT 中使用了JDK7 中的一些特性，所以要求必须在 JDK7 以上的版本上运行。

安装JDK

检查是否安装了JAVA

1	$ java -version

安装jdk包

创建目录

12	$ mkdir /usr/java/$ cd /usr/java/

最新版本的jdk8下载 (2017年12月26日)

1234	$ wget http://download.oracle.com/otn-pub/java/jdk/8u151-b12/e758a0de34e24606bca991d704f6dcbf/jdk-8u151-linux-x64.tar.gz$ tar -zxvf jdk-8u151-linux-x64.tar.gz$ ls #查看目录jdk1.8.0_151 jdk-8u151-linux-x64.tar.gz

配置环境

123456789101112131415

$ vim /etc/profile...# 在最后添加#set java environmentJAVA_HOME=/usr/java/jdk1.8.0_151JRE_HOME=$JAVA_HOME/jreCLASS_PATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$JRE_HOME/libPATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/binexport JAVA_HOME JRE_HOME CLASS_PATH PATH...$ source /etc/profile #刷新生效$ java -versionjava version "1.8.0_151"Java(TM) SE Runtime Environment (build 1.8.0_151-b26)Java HotSpot(TM) 64-Bit Server VM (build 25.151-b23, mixed mode)

安装MyCAT配置读写分离

MyCAT资源站点

创建目录

12	$ mkdir /usr/mycat/$ cd /usr/mycat/

下载解压

这里我安装MyCAT 1.6.5

本来安装的是1.6.0，但是用不成，后来提交了issues，维护者告知1.6.0不支持Mysql预处理。

123456

$ wget http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release-20171117203123-linux.tar.gz$ tar -zxvf Mycat-server-1.6.5-release-20171117203123-linux.tar.gz$ lsmycat Mycat-server-1.6.5-release-20171117203123-linux.tar.gz$ ls mycatbin catlet conf lib logs version.txt

配置环境

12345678910

$ vim /etc/profile...# 在最后添加#set mycatexport MYCAT_HOME=/usr/mycat/mycatexport PATH=${MYCAT_HOME}/bin:$PATH...$ source /etc/profile #刷新生效$ mycat -v #MyCAT没有这个命令,但是出现下面提示代表配置成功Usage: /usr/mycat/mycat/bin/mycat { console | start | stop | restart | status | dump }

配置读写分离

配置文件非常多，如果只是简单配置在不同的服务器上进行读写分离只需要配置两个文件server.xml 和 schema.xml,点此查看文档,了解更多。

Master创建用户和数据库

在Master服务器操作

1234567

$ mysql -u root -p// 创建一个拥有全部权限的用户mysql> GRANT ALL ON *.* TO 'proxy'@'192.168.0.100' IDENTIFIED BY 'msMlRbj38mRwcc8yDH9iOzJaA0OxXLlv';// 创建一个拥有只读权限的用户mysql> GRANT SELECT ON *.* TO 're_proxy'@'192.168.0.100' IDENTIFIED BY 'msMlRbj38mRwcc8yDH9iOzJaA0OxXLlv';mysql> FLUSH PRIVILEGES;mysql> create database test;

• 建议创建MySQL用户时不要轻易用%，最好是限制一下权限ip

• 如果你的主从复制没有限定数据库，那么你创建的用户会同步到从库。

回到负载均衡服务器继续操作

• 配置schema.xml

  1234567891011121314151617181920212223

  $ vim mycat/conf/schema.xml<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd"><mycat:schema xmlns:mycat="http://io.mycat/"> <!-- 定义MyCat的逻辑库 --> <schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="pcxNode"></schema> <!-- 定义MyCat的数据节点 --> <dataNode name="pcxNode" dataHost="dtHost" database="test" /> <!-- 定义数据主机dtHost，连接到MySQL读写分离集群 ,schema中的每一个dataHost中的host属性值必须唯一--> <!-- dataHost实际上配置就是后台的数据库集群，一个datahost代表一个数据库集群 --> <!-- balance="1"，全部的readHost与stand by writeHost参与select语句的负载均衡--> <!-- writeType="0"，所有写操作发送到配置的第一个writeHost，这里就是我们的hostmaster，第一个挂了切到还生存的第二个writeHost--> <dataHost name="blogdtHost" maxCon="500" minCon="20" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <!--心跳检测 --> <heartbeat>select user()</heartbeat> <!--配置后台数据库的IP地址和端口号，还有账号密码 --> <writeHost host="hostM2" url="192.168.0.123:3306" user="proxy" password="msMlRbj38mRwcc8yDH9iOzJaA0OxXLlv"> <readHost host="hostS2" url="192.168.0.124:3306" user="re_proxy" password="msMlRbj38mRwcc8yDH9iOzJaA0OxXLlv"/> </writeHost> </dataHost></mycat:schema>

配置server.xml

$ vim mycat/conf/server.xml

1234567891011121314151617181920

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mycat:server SYSTEM "server.dtd"><mycat:server xmlns:mycat="http://io.mycat/"><system> <property name="defaultSqlParser">druidparser</property> <property name="charset">utf8mb4</property></system> <!-- user 业务代码中用来访问Mycat的帐号 --><user name="my_user"> <!-- password 业务代码中用来访问Mycat的密码 --><property name="password">abcdefghijklmn</property> <!-- schemas 与schema.xml中的<schema></schema>的name属性相同 --><property name="schemas">test</property></user><user name="my_readly_user"><property name="password">abcdefghijklmn</property><property name="schemas">test</property><property name="readOnly">true</property></user></mycat:server>

开启MyCAT

1	$ mycat start

查看端口

12345

$ netstat -antlpActive Internet connections (servers and established)Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program nametcp 0 0 0.0.0.0:8066 0.0.0.0:* LISTEN 14054/javatcp 0 0 0.0.0.0:9066 0.0.0.0:* LISTEN 14054/java

• 9066 虚拟schema管理端口

• 8066 虚拟schema登陆端口

  登录MyCAT读写分离服务

  1	$ mysql -u my_user -p -h127.0.0.1 -P 8066

登录MyCAT管理端

1234	$ mysql -u my_user -p -h127.0.0.1 -P 9066mysql> show @@heartbeat;RS_CODE为1表示心跳正常mysql> show @@datasource; --查看读写分离的机器配置情况

MyCAT读写分离验证

更改日志输出模式

123	$ vim mycat/conf/log4j.xml修改<asyncRoot level="info" includeLocation="true">为 <asyncRoot level="debug" includeLocation="true">

测试

12345

$ mysql -uroot -p123456 -h127.0.0.1 -P 8066mysql> create table test (id bigint not null primary key,user varchar(100));mysql> insert into test(id,user) values(1,abc);mysql> insert into test(id,user) values(2,xyz);mysql> select * from test

查询观察日志的变化

1	tail -f mycat/logs/mycat.log