使用Mycat实现mysql的主从读写分离

一、 Mysql读写分离的概述
MySQl作为目前世界上使用最广泛的免费数据库，相信所有从事系统运维的工程师都一定接触过。但在实际的生产环境中，由单台MySQL作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。
因此，一般来说都是通过 主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy/Amoeba）来提升数据库的并发负载能力，这样的方案来进行部署与实施的。

1、 读写分离工作原理
基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

数据内部交换过程：

2、 为什么要读写分离
1）面对越来越大的访问压力，单台的服务器的性能成为瓶颈，需要分担负载
2）主从只负责各自的写和读，极大程度的缓解X锁和S锁争用
3）从库可配置myisam引擎，提升查询性能以及节约系统开销
4）增加冗余，提高可用性

3、 实现读写分离的方式
一般有两种方式实现：
应用程序层实现：
1）应用程序层实现，网站的程序实现（web中间件）
2）应用程序层实现指的是在应用程序内部及连接器中实现读写分离

优点：
（1） 应用程序内部实现读写分离，安装既可以使用；
（2） 减少一定部署难度；
（3） 访问压力在一定级别以下，性能很好。
缺点：
（1） 架构一旦调整，代码要跟着变；
（2） 难以实现高级应用，如自动分库，分表；
（3） 无法适用大型应用场景。

中间件层实现：
中间件层实现是指在外部中间件程序实现读写分离

4、 常见的中间件程序

1）Cobar：
阿里巴巴B2B开发的关系型分布式系统，管理将近3000个MySQL实例。 在阿里经受住了考验，后面由于作者的走开的原因cobar没有人维护 了，阿里也开发了tddl替代cobar。

2）MyCAT：
社区爱好者在阿里cobar基础上进行二次开发，解决了cobar当时存 在的一些问题，并且加入了许多新的功能在其中。目前MyCAT社区活 跃度很高，目前已经有一些公司在使用MyCAT。总体来说支持度比 较高，也会一直维护下去，

3）OneProxy：
数据库界大牛，前支付宝数据库团队领导楼总开发，基于mysql官方 的proxy思想利用c进行开发的，OneProxy是一款商业收费的中间件， 楼总舍去了一些功能点，专注在性能和稳定性上。有朋友测试过说在 高并发下很稳定。

4）Vitess：
这个中间件是Youtube生产在使用的，但是架构很复杂。 与以往中间件不同，使用Vitess应用改动比较大要 使用他提供语言的API接口，我们可以借鉴他其中的一些设计思想。

5）Kingshard：
Kingshard是前360Atlas中间件开发团队的陈菲利用业务时间 用go语言开发的，目前参与开发的人员有3个左右， 目前来看还不是成熟可以使用的产品，需要在不断完善。

6）Atlas：
360团队基于mysql proxy 把lua用C改写。原有版本是支持分表， 目前已经放出了分库分表版本。在网上看到一些朋友经常说在高并 发下会经常挂掉，如果大家要使用需要提前做好测试。

7）MaxScale与MySQL Route：
这两个中间件都算是官方的，MaxScale是mariadb (MySQL原作者维护的一个版本)研发的，目前版本不支持分库分表。MySQL Route是现在MySQL官方Oracle公司发布出来的一个中间件。

优点：
架构设计更灵活
可以在程序上实现一些高级控制，如：透明化水平拆分，failover,监控
可以依靠些技术手段提高mysql性能
对业务代码的影响小，同时也安全

缺点：
需要一定的开发运维团队的支持。

二、 什么是MyCAT
一个彻底开源的，面向企业应用开发的大数据库集群；
支持事务、ACID、可以替代MySQL的加强版数据库；
一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群；
一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server；
结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品；
一个新颖的数据库中间件产品。

三、 MyCat服务安装和配置
环境：
名称 主机名 ip地址
mycat xuegod120 192.168.2.120
主数据库 xuegod130 192.168.2.130
从数据库 xuegod140 192.168.2.140
1、 安装mycat
MyCat有提供编译好的安装包，支持Windows、Linux、Mac、Solaris等系统上安装与运行。 官方下载主页http://www.mycat.io
Linux下可以下载Mycat-server-xxxxx.linux.tar.gz 解压在某个目录下，注意目录不能有空格，在Linux(Unix)下，建议放在usr/local/Mycat目录下，如下：
[root@xuegod120 ~]# tar zxf Mycat-server-1.5-RELEASE-20160301083012-linux.tar.gz -C /usr/local/ #加压指定的目录必须存在
[root@xuegod120 ~]# ls /usr/local/
bin etc games include lib lib64 libexec mycat sbin share src
[root@xuegod120 ~]# ll /usr/local/mycat/
total 12
drwxr-xr-x 2 root root 190 Jun 14 14:36 bin
drwxrwxrwx 2 root root 6 Dec 13 2015 catlet
drwxrwxrwx 2 root root 4096 Jun 14 14:36 conf
drwxr-xr-x 2 root root 4096 Jun 14 14:36 lib
drwxrwxrwx 2 root root 6 Dec 13 2015 logs
-rwxrwxrwx 1 root root 217 Mar 1 2016 version.txt
bin 程序目录，存放了window版本和linux版本，除了提供封装成服务的版本之外，也提供了nowrap的shell脚本命令，方便大家选择和修改，进入到bin目录：
Linux下运行：./mycat console,首先要chmod +x *
注：mycat支持的命令{ console | start | stop | restart | status | dump }
conf目录下存放配置文件，server.xml是Mycat服务器参数调整和用户授权的配置文件，schema.xml是逻辑库定义和表以及分片定义的配置文件，rule.xml是分片规则的配置文件，分片规则的具体一些参数信息单独存放为文件，也在这个目录下，配置文件修改，需要重启Mycat或者通过9066端口reload。
lib目录下主要存放mycat依赖的一些jar文件。
日志存放在logs/mycat.log中，每天一个文件，日志的配置是在conf/log4j.xml中，根据自己的需要，可以调整输出级别为debug，在debug级别下，会输出更多的信息，方便排查问题。
注意：Linux下部署安装MySQL，默认不忽略表名大小写，需要手动到/etc/my.cnf 下配置 lower_case_table_names=1 使Linux环境下MySQL忽略表名大小写，否则使用MyCAT的时候会提示找不到表的错误！

2、 安装JDK1.7或以上版本
mycat运行在JDK的平台之上，所以需要安装
1） 网络下载JDK
wget http://download.oracle.com/otn-pub/java/jdk/8u191-b12/2787e4a523244c269598db4e85c51e0c/jdk-8u191-linux-x64.tar.gz
#可以直接将连接复制到Windos平台的浏览器下载之后，然后在上传到Linux平台

2） 解压缩
手动创建/usr/java/目录，存放JDK的安装文件
[root@xuegod120 ~]# mkdir /usr/java/
[root@xuegod120 ~]# tar -zxf jdk-8u191-linux-x64.tar.gz -C /usr/java/
[root@xuegod120 ~]# ll /usr/java/jdk1.8.0_191/
total 25976
drwxr-xr-x 2 10 143 4096 Oct 6 2018 bin
-r–r--r-- 1 10 143 3244 Oct 6 2018 COPYRIGHT
drwxr-xr-x 3 10 143 132 Oct 6 2018 include
-rw-r–r-- 1 10 143 5207154 Sep 12 2018 javafx-src.zip
drwxr-xr-x 5 10 143 185 Oct 6 2018 jre
drwxr-xr-x 5 10 143 245 Oct 6 2018 lib
-r–r--r-- 1 10 143 40 Oct 6 2018 LICENSE
drwxr-xr-x 4 10 143 47 Oct 6 2018 man
-r–r--r-- 1 10 143 159 Oct 6 2018 README.html
-rw-r–r-- 1 10 143 424 Oct 6 2018 release
-rw-r–r-- 1 10 143 21101479 Oct 6 2018 src.zip
-rw-r–r-- 1 10 143 108062 Sep 12 2018 THIRDPARTYLICENSEREADME-JAVAFX.txt
-r–r--r-- 1 10 143 155003 Oct 6 2018 THIRDPARTYLICENSEREADME.txt

3） 配置mycat和jdk的环境变量，在/etc/profile底部加上如下内容
JAVA_HOME=/usr/java/jdk1.8.0_191
PATH=$JAV{A}_{H}OME/bin:$PATH
CLASSPATH=$JAV{A}_{H}OME/jre/lib/ext:$JAVA_HOME/lib/tools.jarexport PATH JAVA_HOME CLASSPATH

MYCAT_HOME=/usr/local/mycat
PATH=$MYCA{T}_{H}OME/bin:$PATH

刷新配置文件
[root@xuegod120 ~]# source /etc/profile

3、 Mycat服务启动与启动设置
如果是在多台Linux系统中组建的MyCAT集群，那需要在MyCAT Server所在的服务器上配置对其他IP和主机名的映射，配置方式如下：
vim /etc/hosts
192.168.2.120 xuegod120.com
192.168.2.130 xuegod130.com
192.168.2.140 xuegod140.com

1） 修改mycat配置文件server.xml
mycat的用户账号和授权信息是在conf/server.xml文件中配置
[root@xuegod120 ~]# cd /usr/local/mycat/
[root@xuegod120 mycat]# cp conf/server.xml conf/server.xml.bak #备份一份配置文件
我们这里直接清空配置文件，
[root@xuegod120 mycat]# >conf/server.xml
重新写入以下内容：
[root@xuegod120 mycat]# vim conf/server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
        <system>
        <property name="defaultSqlParser">druidparser</property>
        </system>
         <!--以下设置为应用访问帐号权限 -->
        <user name="root">
                <property name="password">123456</property>
                <property name="schemas">ha</property>
        </user>
         <!--以下设置为应用只读帐号权限 -->
        <user name="user">
                <property name="password">user</property>
                <property name="schemas">ha</property>
                <property name="readOnly">true</property>
        </user>
</mycat:server>

2） 修改配置文件schema.xml，关于datahost的配置信息如下
[root@xuegod120 mycat]# cp conf/schema.xml conf/schema.xml.bak #备份配置文件
清空配置文件，直接写入如下内容：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
        <schema name="ha" checkSQLschema="false" sqlMaxLimit="100" dataNode='dn1'>
        </schema>
        <dataNode name="dn1" dataHost="dthost" database="ha"/>
        <dataHost name="dthost" maxCon="500" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="-1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="xuegod130.cn" url="192.168.2.130:3306" user="mycat" password="123456">
        </writeHost>        <writeHost host="xuegod140.cn" url="192.168.2.140:3306" user="mycat" password="123456"/>
        </dataHost>
</mycat:schema>

注解：
schema 标签用于定义 MyCat 实例中的逻辑库，name:后面就是逻辑库名MyCat 可以有多个逻辑库，每个逻辑库都有自己的相关配置。可以使用 schema 标签来划分这些不同的逻辑库。
checkSQLschema这个属性默认就是false，官方文档的意思就是是否去掉表前面的数据库的称，”select * from db1.testtable” ，设置为true就会去掉db1。但是如果db1的名称不是schema的名称，那么也不会被去掉，因此官方建议不要使用这种语法。同时默认设置为false。
sqlMaxLimit当该值设置为某个数值时。每条执行的 SQL 语句，如果没有加上 limit 语句，MyCat 也会自动的加上所对应的值。例如设置值为 100，执行”select * from test_table”,则效果为“selelct * from test_table limit 100”.
dataNode 标签定义了 MyCat 中的数据节点，也就是我们通常说所的数据分片。一个 dataNode 标签就是一个独立的数据分片.

属性 说明
Name 定义数据节点的名字，这个名字需要是唯一的
dataHost 该属性用于定义该分片属于哪个数据库实例
Database 该属性用于定义该分片属性哪个具体数据库实例上的具体库

dataHost该标签定义了具体的数据库实例、读写分离配置和心跳语句
标签 说明
name 唯一标识 dataHost 标签，供上层的标签使用
maxCon 指定每个读写实例连接池的最大连接。
minCon 指定每个读写实例连接池的最小连接，初始化连接池的大小。
balance 负载均衡类型，目前的取值有4 种：
“0”, 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。 “1”，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。
”2”，所有读操作都随机的在 writeHost、readhost 上分发。
”3”，所有读请求随机分发到 wiriterHost 对应的 readhost 执行。writerHost 不负担读压
writeType

1. writeType=”0”, 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .
2. writeType=”1”，所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐。默认0就好了！
   dbType 指定后端连接的数据库类型，目前支持二进制的 mysql 协议，还有其他使用 JDBC 连接的数据库。例如：mongodb、oracle、spark 等.
   dbDriver 指定连接后端数据库使用的 Driver，目前可选的值有 native 和 JDBC。使用 native 的话，因为这个值执行的是二进制的 mysql 协议，所以可以使用 mysql 和 maridb。其他类型的数据库则需要使用 JDBC 驱动来支持。
   switchType “-1” 表示不自动切换； “1” 默认值，自动切换； “2” 基于 MySQL 主从同步的状态决定是否切换心跳语句为 show slave status； “3” 基于 MySQL galary cluster 的切换机制（适合集群）（1.4.1）心跳语句为 show status like ‘wsrep%’.

heartbeat 标签指明用于和后端数据库进行心跳检查的语句。
writeHost /readHost这两个标签都指定后端数据库的相关配置，用于实例化后端连接池。唯一不同的是，writeHost 指定写实例、readHost 指定读实例。 在一个 dataHost 内可以定义多个 writeHost 和 readHost。但是，如果 writeHost 指定的后端数据库宕机，那么这个 writeHost 绑定的所有 readHost 都将不可用。另一方面，由于这个 writeHost 宕机，系统会自动的检测到，并切换到备用的 writeHost 上去。这两个标签的属性相同.
属性 说明
host 用于标识不同实例，一般 writeHost 我们使用M1，readHost 我们用S1。
url 后端实例连接地址。Native：地址：端口 JDBC：jdbc的url
password 后端存储实例需要的密码
user 后端存储实例需要的用户名字
weight 权重 配置在 readhost 中作为读节点的权重

有两个参数需要注意，balance和 switchType。其中，balance指的负载均衡类型，目前的取值有4种：

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不负担读压力。
   switchType指的是切换的模式，目前的取值也有4种：
5. switchType=’-1’ 表示不自动切换；
6. switchType=‘1’ 默认值，表示自动切换；
7. switchType=‘2’ 基于MySQL主从同步的状态决定是否切换,心跳语句为 show slave status；
8. switchType='3’基于MySQL galary cluster的切换机制（适合集群）（1.4.1），心跳语句为 show status like ‘wsrep%’。

3） 驱动mycat服务
如果没有增加环境变量的话，就使用绝对路径来启动服务
[root@xuegod63 local]# /usr/local/mycat/bin/mycat start
如果再/etc/profile/文件增加了环境变量，就直接使用mycat命令启动
[root@xuegod120 mycat]# mycat start
Starting Mycat-server…

4） 查看日志看mycat是否启动成功
错误记录：

这里无法启动mycat是因为本地的hosts文件里面定义的主机名和hostname文件定义的主机名不同，导致，修改后解决。
出现启动成功，表示mycat已经启动成功

4、 配置mysql主从
1） 配置xuegod130主服务器数据库
修改my.cnf配置文件
log-bin=mysql-bin-master
server-id=1
binlog-do-db=ha
binlog-ignore-db=mysql
保存并重启服务
systemctl restart mysqld

创建测试数据库ha
[root@xuegod130 ~]# mysql -uroot -p123456 -A
mysql> create database ha;
Query OK, 1 row affected (0.00 sec)
mysql> use ha;
Database changed
mysql> create table t1 (id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

导出数据库，将数据库传递到xuegod140从服务器数据库中使用，保证数据库的一致性
[root@xuegod130 ~]# mysqldump -uroot -p123456 -B ha > ha.sql
[root@xuegod130 ~]# scp ha.sql 192.168.2.140:/root/

增加用户，并授权登录数据库（这里创建的用户是给到mycat来登录数据库使用的）
mysql> grant all privileges on . to ‘mycat’@‘192.168.2.%’ identified by ‘123456’;
Query OK, 0 rows affected, 1 warning (0.00 sec)

授权slave用户，让slave主机可以到master读取日志记录
mysql> grant replication slave on . to slave@‘192.168.2.140’ identified by ‘123456’;
Query OK, 0 rows affected, 1 warning (0.01 sec)

刷新权限
mysql> flush privileges;
查看master的状态
mysql> show master status;
±------------------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±------------------------±---------±-------------±-----------------±------------------+
| mysql-bin-master.000001 | 1228 | ha | mysql | |
±------------------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)

2） 配置从服务器数据库xuegod140
先导入数据库
[root@xuegod140 ~]# mysql -uroot -p123456 < ha.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@xuegod140 ~]# mysql -uroot -p123456 -A
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| ha |
| mysql |
| performance_schema |
| sys |
±-------------------+
5 rows in set (0.11 sec)

修改my.cnf配置文件
server-id=2
保存并重启服务
[root@xuegod140 ~]# systemctl restart mysqld

创建mycat用户，用户mycat访问slave服务器
mysql> grant all privileges on . to ‘mycat’@‘192.168.2.%’ identified by ‘123456’;
Query OK, 0 rows affected, 1 warning (0.00 sec)

指定master服务器的IP地址、用户名和密码给到slave服务器
mysql> change master to master_host=‘192.168.2.130’,master_user=‘slave’,master_password=‘123456’;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

启动slave
mysql> start slave;
查看slave服务器的状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.130
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin-master.000001
Read_Master_Log_Pos: 1228
Relay_Log_File: xuegod140-relay-bin.000002
Relay_Log_Pos: 1455
Relay_Master_Log_File: mysql-bin-master.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

3） 在mycat服务器中登录xuegod130和140的数据库，测试能否登录（xuegod120）
[root@xuegod120 ~]# mysql -uroot -p123456 -h 192.168.2.120 -P8066 #这里使用mysql客户端来登录，加上jdk的8066端口来登录mycat，实现等mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.8-mycat-1.5-RELEASE-20160301083012 MyCat Server (OpenCloundDB)
#这里可看到我们登录的其实是mycat中间
Copyright © 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show databases; #使用mycat登录我们只能看到我们放行的ha库，其他看不到
±---------+
| DATABASE |
±---------+
| ha |
±---------+
1 row in set (0.00 sec)

mysql> use ha;
Database changed
mysql> select * from t1;
±-----±-----+
| id | name |
±-----±-----+
| 1 | AA |
| 2 | BB |
±-----±-----+
2 rows in set (0.12 sec)

4） 我们模拟从库xuegod140挂掉了，测试主库是否可以继续使用
[root@xuegod140 ~]# systemctl stop mysqld
mysql> insert into t1 values (3,‘CC’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
±-----±-----+
| id | name |
±-----±-----+
| 1 | AA |
| 3 | CC |
| 3 | CC |
| 3 | CC |
±-----±-----+
4 rows in set (0.00 sec)
从库挂掉了，我们同样可以查询和插入数据

5） 模拟主库挂掉
先启动从库的mysql服务
[root@xuegod140 ~]# systemctl start mysqld #启动从库
[root@xuegod130 ~]# systemctl stop mysqld #启动主库
mysql> select * from t1;
±-----±-----+
| id | name |
±-----±-----+
| 1 | AA |
| 2 | BB |
| 3 | CC |
| 3 | CC |
| 3 | CC |
±-----±-----+
5 rows in set (0.00 sec)

mysql> insert into t1 values (3,‘DD’);
ERROR 1184 (HY000): Connection refused
主库挂掉后，我们可以查询，但是无法写入。

6） 这里如果想实现，主库挂掉之后，从库可以写入数据，需要修改mycat的配置文件如下：
原：switchType="-1"
改为：switchType=“1” #表示根据心跳线语句，判定主库挂掉后，自动切换

解释：writeType=”0”, 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .

因为上文配置文件中writeType=”0”， switchType="-1" 表示第一个挂掉后，不进行自动切换
改为：writeType=”0”， switchType=“1” 表示第一个挂掉后，自动切换到第二个writetype的host主机