MySQL 读写分离的概述

MySQL 作为目前世界上使用最广泛的免费数据库，相信所有从事系统运维的工程师都一定接触过。但在实际的生产环境中，由单台 MySQL 作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。

因此，一般来说都是通过主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy/Amoeba）来提升数据库的并发负载能力，这样的方案来进行部署与实施的。

读写分离工作原理

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

数据内部交换过程：

为什么要读写分离

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

实现读写分离的方式

一般有两种方式实现

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

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

   常见的中间件程序

   

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

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

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

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

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

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

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

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

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

什么是 MyCAT

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

MyCat 服务安装与配置

MyCat 有提供编译好的安装包，支持 Windows、Linux、Mac、Solaris 等系统上安装与运行。官方下载主页 http://www.mycat.org.cn/

架构

192.168.1.63 mycat
192.168.1.64 主
192.168.1.65 从

Mycat 需要安装 JDK 1.7 或者以上版：
第一步：下载 jdk-8u191-linux-x64.tar.gz 文件

[root@xuegod63 local]# wget http://download.oracle.com/otn-pub/java/jdk/8u191-
b12/2787e4a523244c269598db4e85c51e0c/jdk-8u191-linux-x64.tar.gz 
#如果在Linux 上下载的程序包有问题，需要把链接复制到 Windows 上下载再上传到 Linux。

第二步：新建/usr/java 文件夹，将 jdk-8u191-linux-x64.tar.gz 解压到该目录中

[root@xuegod63 local]# mkdir /usr/java
[root@xuegod63 local]# tar xf jdk-8u191-linux-x64.tar.gz -C /usr/java/

第三步：配置环境变量 在/etc/profile 底部加入如下内容：

[root@xuegod63 local]# vim /etc/profile.d/java.sh #/etc/profile.d/目录下创建
java.sh 文件并定入如下内容
JAVA_HOME=/usr/java/jdk1.8.0_191
PATH=$JAVA_HOME/bin:$PATH 
CLASSPATH=$JAVA_HOME/jre/lib/ext:$JAVA_HOME/lib/tools.jar
export PATH JAVA_HOME CLASSPATH
[root@xuegod63 local]# source /etc/profile.d/java.sh #使环境变量生效
[root@xuegod63 local]# java -version#查看 java 版本
java version "1.8.0_191"
Java(TM) SE Runtime Environment (build 1.8.0_191-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.191-b12, mixed mode)

安装 mycat
Linux 下可以下载 Mycat-server-xxxxx.linux.tar.gz 解压在某个目录下，注意目录不能有空格，在 Linux(Unix)下，建议放在 usr/local/Mycat 目录下，如下：

[root@xuegod63 ~]# tar -xf Mycat-server-1.5-RELEASE-0301083012-linux.tar.gz -C 
/usr/local/
[root@xuegod63 local]# ls /usr/local/mycat/

下面修改 MyCAT 用户密码(仅供参考)：

[root@xuegod63 ~]# useradd mycat
[root@xuegod63 ~]# passwd mycat #修改时输入两次密码 成功

[root@xuegod63 ~]# chown -R mycat.mycat /usr/local/mycat #修改权限

目录解释如下：

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 生效。

lib 目录下主要存放 mycat 依赖的一些 jar 文件。

日志存放在 logs/mycat.log 中，每天一个文件，日志的配置是在 conf/log4j.xml 中，根据自己的需要，可以调整输出级别为 debug，在 debug 级别下，会输出更多的信息，方便排查问题。

MyCat 服务启动与启动设置

MyCAT 在 Linux 中部署启动时，首先需要在 Linux 系统的环境变量中配置 MYCAT_HOME，操作方式如下：

[root@xuegod63 local]# vim /etc/profile.d/mycat.sh #在/etc/profile.d 目录下创建
mycat.sh 文件，并写入如下。
MYCAT_HOME=/usr/local/mycat PATH=$MYCAT_HOME/bin:$PATH
[root@xuegod63 local]# source /etc/profile.d/mycat.sh #使环境变量生效

如果是在多台 Linux 系统中组建的 MyCAT 集群，那需要在 MyCAT Server 所在的服务器上配置对其他 IP 和主机名的映射，配置方式如下：

vi /etc/hosts 

例如：我有 3 台机器，配置如下：
IP 主机名：

192.168.1.63 xuegod63.cn 
192.168.1.64 xuegod64.cn 
192.168.1.65 xuegod65.cn

编辑完后，保存文件。

mycat 的用户账号和授权信息是在 conf/server.xml 文件中配置

[root@xuegod63 local]# vim /usr/local/mycat/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>
 <!--以下设置为应用访问帐号权限 -->
 34 行 <user name="root"> #定义管理员用户，也就是连接 Mycat 的用户名
 <property name="password">123456</property> #密码
 <property name="schemas">ha</property> #定义一个逻辑库，与schema 配置文件对应
 </user>
 <!--以下设置为应用只读帐号权限 -->
 <user name="user">
 <property name="password">user</property>
 <property name="schemas">ha</property>
 <property name="readOnly">true</property>
 </user>
</mycat:server>

编辑 MyCAT 的配置文件 schema.xml，关于 dataHost 的配置信息如下：

[root@xuegod63 local]# mv /usr/local/mycat/conf/schema.xml 
/usr/local/mycat/conf/schema.xml.bak #备份原文件。
[root@xuegod63 local]# vim /usr/local/mycat/conf/schema.xml 新建
<?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="xuegod64.cn" url="192.168.1.64:3306" user="mycat" 
password="123456">
 </writeHost>
 <writeHost host="xuegod65.cn" url="192.168.1.65:3306" user="mycat" 
password="123456">
 </writeHost>
 </dataHost>
</mycat:schema>

绿色 ha 为物理存在的数据库名
注解：
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 标签就是一个独立的数据分片.

dataHost 该标签定义了具体的数据库实例、读写分离配置和心跳语句

heartbeat 标签指明用于和后端数据库进行心跳检查的语句。

writeHost /readHost 这两个标签都指定后端数据库的相关配置，用于实例化后端连接池。唯一不同的是，writeHost 指定写实例、readHost 指定读实例。 在一个 dataHost 内可以定义多个writeHost 和 readHost。但是，如果 writeHost 指定的后端数据库宕机，那么这个 writeHost 绑定的所有 readHost 都将不可用。另一方面，由于这个 writeHost 宕机，系统会自动的检测到，并切换到备用的 writeHost 上去。这两个标签的属性相同

有两个参数需要注意，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 种：

1. switchType='-1' 表示不自动切换；
2. switchType='1' 默认值，表示自动切换；
3. switchType='2' 基于 MySQL 主从同步的状态决定是否切换,心跳语句为 show slave status；
4. switchType='3'基于 MySQL galary cluster 的切换机制（适合集群）（1.4.1），心跳语句为show status like 'wsrep%'。

经过以上两个步骤的配置，就可以到/usr/local/mycat/bin 目录下执行 ./mycat start，即可启动mycat 服务！

[root@xuegod63 local]# /usr/local/mycat/bin/mycat start
[root@xuegod63 ~]# cat /usr/local/mycat/logs/wrapper.log #查看日志，启动成功

配置 MySQL 主从

安装 mysql
上传 mysql-5.7.tar.gz 到 xuegod64

[root@xuegod64 ~]# scp mysql-5.7.tar.gz root@192.168.1.65:/root/
[root@xuegod64 ~]# tar xf mysql-5.7.tar.gz
[root@xuegod64 ~]# yum install -y ./mysql*.rpm

关闭密码强度审计插件

[root@xuegod64 ~]# systemctl start mysqld
[root@xuegod64 ~]# echo "validate-password=OFF">> /etc/my.cnf
[root@xuegod64 ~]# systemctl restart mysqld

修改 root 用户密码

[root@xuegod64 ~]# grep "password" /var/log/mysqld.log
[root@xuegod64 ~]# mysql -uroot -p'-NEjo1gbPllh'
mysql> set password for root@localhost = password('123456');
mysql> exit

另一台机器

[root@xuegod65 ~]# tar xf mysql-5.7.tar.gz
[root@xuegod65 ~]# yum install -y ./mysql*.rpm

关闭密码强度审计插件

[root@xuegod65 ~]# systemctl start mysqld
[root@xuegod65 ~]# echo "validate-password=OFF">> /etc/my.cnf
[root@xuegod65 ~]# systemctl restart mysqld

修改 root 用户密码

[root@xuegod65~]# grep "password" /var/log/mysqld.log
[root@xuegod65 ~]# mysql -uroot -p'%OrrfGwyM6tS'
mysql> set password for root@localhost = password('123456');
mysql> exit

创建数据库和测试数据

[root@xuegod64 ~]# mysql -uroot -p123456
mysql> create database ha;
mysql> use ha;
mysql> create table test(id int,name varchar(20));
mysql> insert into test values(1,'man');
[root@xuegod64 ~]# mysqldump -uroot -p123456 -B ha >HA.sql #可以导出数据库

将导出的数据库传给从服务器

[root@xuegod64 ~]# scp HA.sql root@192.168.1.65:/root/

xuegod65 从服务上操作：
导入数据库和表，用于实现读操作：

[root@xuegod65 ~]# mysql -uroot -p123456<HA.sql
[root@xuegod64 ~]# vim /etc/my.cnf
log-bin=mysql-bin-master #启用二进制日志
server-id=1 #本机数据库 ID 标示
binlog-do-db=ha #可以被从服务器复制的库, 二进制需要同步的数据库名
binlog-ignore-db=mysql #不可以被从服务器复制的库
[root@xuegod64 ~]# systemctl restart mysqld
[root@xuegod64 ~]# mysql -uroot -p'123456'

授权给 mycat 登陆数据库使用的帐号以及主从复制账号

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mycat'@"%" IDENTIFIED BY "123456"; 
mysql> grant replication slave on *.* to slave@"192.168.1.%" identified by "123456";
mysql> exit
[root@xuegod65 ~]# vim /etc/my.cnf
log-bin=mysql-bin-slave #启用二进制日志
server-id=2 #本机数据库 ID 标示
binlog-do-db=ha #可以被从服务器复制的库, 二进制需要同步的数据库名
binlog-ignore-db=mysql #不可以被从服务器复制的库
[root@xuegod65 ~]# systemctl restart mysqld
[root@xuegod65 ~]# mysql -uroot -p'123456'

授权给 mycat 登陆数据库使用的帐号以及主从复制账号

mysql> GRANT ALL PRIVILEGES ON *.* TO 'mycat'@"%" IDENTIFIED BY "123456";
mysql> grant replication slave on *.* to slave@"192.168.1.%" identified by "123456";
mysql> change master to 
master_host='192.168.1.64',master_user='slave',master_password='123456';
mysql> start slave; #启动 slave
mysql> show slave status\G #查看状态 ，有两个 yes 主从同步成功！
mysql> exit

模拟 slave 故障：从服务器挂掉了

[root@xuegod65 ~]# systemctl stop mysqld

在客户端上测试读写

[root@xuegod63 ~]# mysql -uroot -p123456 -h 192.168.1.63 -P8066 
mysql> use ha;
mysql> select * from test;

说明读操作的路由切换到 master 上了，对外没有任何影响！

[root@xuegod65 ~]# systemctl start mysqld

模拟 master 故障：主服务器挂掉了

[root@xuegod64 ~]# systemctl stop mysqld

在客户端上测试读写

[root@xuegod63 ~]# mysql -uroot -p123456 -h 192.168.1.63 -P8066
mysql> use ha;
mysql> create table test1(id int);
ERROR 1184 (HY000): 拒绝连接 #主数据库停止了，是无法写操作的，但不影响读.
mysql> select * from test;

[root@xuegod64 ~]# systemctl start mysqld

只读用户登录测试

mysql -u user -puser -h 192.168.1.201 -P8066
mysql> insert into test values(5,'feng');
ERROR 1495 (HY000): User readonly
mysql> select * from ha.test;
+------+--------+
| id | name |
+------+--------+
| 1 | cd |

实战节点宕机后自动切换 Slave 节点

配置 MM 模式主从复制环境
启用 65 为 64 的主

[root@xuegod64 ~]# mysql -uroot -p'123456'
mysql> change master to 
master_host='192.168.1.65',master_user='slave',master_password='123456';
mysql> start slave; #启动 slave
mysql> show slave status\G #查看状态 ，有两个 yes 主从同步成功！
mysql> exit

mycat 配置文件优化调整。

[root@xuegod63 ~]# vim /usr/local/mycat/conf/schema.xml
<?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="xuegod64.cn" url="192.168.1.64:3306" user="mycat" 
password="123456">
 </writeHost>
 <writeHost host="xuegod65.cn" url="192.168.1.65:3306" user="mycat" 
password="123456">
 </writeHost>
 </dataHost>
</mycat:schema>

我们前面配置 switchType="-1"关闭了自动切换，但是我们在主从环境下应该避免在从节点写入数据，否则会造成主从数据库数据不一致， 如果我们使用的是 MM 模式则主节点发生故障后会自动切换至备用节点，此时不影响数据写入。

[root@xuegod63 ~]# /usr/local/mycat/bin/mycat restart 
[root@xuegod64 ~]# systemctl stop mysqld
[root@xuegod63 ~]# mysql -uroot -p123456 -h 192.168.1.63 -P8066
MySQL [(none)]> insert into ha.test values(666,'mk');
MySQL [(none)]> select * from ha.test;
+------+---------+
| id | name |
+------+---------+
| 1 | man |
| 666 | mk |

停主节点：

[root@xuegod64 ~]# systemctl stop mysqld
Mycat 端继续插入
mysql> insert into test values(5,'feng');

恢复主节点

[root@xuegod64 ~]# systemctl start mysqld
mysql -uroot -p123456
select * from ha.test;

关于使用方面的建议，在一些 CMS（内容管理系统）系统中底层的数据库代码已经经过封装，此时使用 mycat 是不可取的，因为封装后的 sql 语句会和 mycat 不兼容，建议是程序开发时应考虑连接mysql 的方式和 mycat 是相互兼容的。或者选择其他中间件比如 mysql-proxy。

                                             想要获取原文档和学习视频加笔记
                                               添加我们小姐姐一 一获取哦！