Mysql读写分离与分库分表

一、Mysql读写分离解决的问题二、mysql处理请求运行流程三、读写分离结构四、读写分离产生场景五、读写分离工具5.1 MyCat5.2 HAProxy5.3 Keepavlied 六、开始搭建MySql集群6.1 MyCat搭建6.1.1 读写分离+ 分库分表配置6.1.1.1 分库分表类型：6.1.1.2 安装MyCat6.1.1.3 编辑配置文件；6.1.1.4 启动mycat：6.1.1.5 登录mycat6.1.1.6 注意事项：6.1.1.7 常见bug：6.1.1.8 问题排查 6.2 HAProxy搭建6.2.1 安装HAProxy6.2.2 创建HAProxy组与用户6.2.3 修改配置文件6.2.3.1 配置日期文件6.2.3.2 配置f文件 6.2.4 启动proxy6.2.5 web访问6.2.6 集群 6.3 搭建Keepavlied6.3.1 安装Keepavlied6.3.2 修改配置6.3.3 集群配置6.3.4 Keepavlied操作（实现去中心化操作）6.3.5 检查是否配置成功 6.4 架构访问 七、MGR（MySQL Group Replication）模式

一、Mysql读写分离解决的问题

数据库有很多（读取与写入）混合请求时产生的并发问题。数据主备模式，保证数据的安全性

二、mysql处理请求运行流程

Sql请求 -> HAProxy虚拟IP -> HAProxy实例机 -> MyCat实例机 -> 数据库 -> 通过mysql内部机制实现主从同步

三、读写分离结构

数据库接口：主库（写库）-> 从库（读库）MyCat结构：

2.1 单体解决方案：

优点：解决读写分离分发问题

缺点：会出现MyCat宕机的问题。

2.2 MyCat集群解决方案

优点：防止MyCat宕机

缺点：HAProxy有宕机的风险

2.3. HAProxy搭建集群

优点：解决HAProxy宕机的问题。

缺点：有两个节点请求不知道该访问哪一个节点

2.4. 搭建Keepavlied监控工具，生成虚拟IP。

解决：HAProxy集群访问的问题（具体实现可翻到下面查看具体介绍），且真正实现mysql高可用

缺点：架构复杂，涉及的组件多，搭建需要较长的时间。

四、读写分离产生场景

100002次请求 = 100000 写 + 2读。读会涉及加锁解锁，因此读操作需要等待写操作完成。会降低性能，因此在主从分离的基础上再做读写分离，提升系统的并发性能。

五、读写分离工具

5.1 MyCat

作用

读写分离、分库分表

缺点

宕机将导致无法为读取 写入操作分配对应的数据库。

解决

为MyCat搭建集群。搭建集群需要考虑数据过来后哪一个节点去处理数据，所以为解决这个问题还需要再MyCat的上层搭建一套haproxy。

5.2 HAProxy

详细介绍以及配置

具体详细信息可访问此篇文章概述

HAProxy是一个使用C语言编写的自由及开放源代码软件，其提供高可用性、负载均衡，以及基于TCP和HTTP的应用程序代理,HAProxy特别适用于那些负载特大的web站点，这些站点通常又需要会话保持或七层处理。HAProxy运行在当前的硬件上，完全可以支持数以万计的并发连接。并且它的运行模式使得它可以很简单安全的整合进您当前的架构中， 同时可以保护你的web服务器不被暴露到网络上。宕机问题

去中心化、没有主节点，每个节点会有一个keepalived，它会给其他节点发送给心跳（感知对方是否还活着）。keepalived 还会维护一个虚拟ip，每个虚拟IP可以根据那个节点宕机的情况来自动选择存活的HAProxy节点。

5.3 Keepavlied

介绍：

keepalived是一个类似于layer3, 4 & 5交换机制的软件，也就是我们平时说的第3层、第4层和第5层交换。作用：

Keepalived的作用是检测服务器的状态，如果有一台web服务器宕机，或工作出现故障，Keepalived将检测到，并将有故障的服务器从系统中剔除，同时使用其他服务器代替该服务器的工作，当服务器工作正常后Keepalived自动将服务器加入到服务器群中，这些工作全部自动完成，不需要人工干涉，需要人工做的只是修复故障的服务器。原理：

Keepalived使用Layer3的方式工作式时，Keepalived会定期向服务器群中的服务器发送一个ICMP的数据包（既我们平时用的Ping程序）,如果发现某台服务的IP地址没有激活，Keepalived便报告这台服务器失效，并将它从服务器群中剔除，这种情况的典型例子是某台服务器被非法关机。Layer3的方式是以服务器的IP地址是否有效作为服务器工作正常与否的标准。

六、开始搭建MySql集群

方案一： 需要服务器6台机：

Mysql（主 + 从）： 2台机；

MyCat（主 + 从）：2台机；

HAProxy （集群 两个节点）：2台机；方案二： 需要4台机

Mysql / MyCat（主 + 从）： 2台机；

HAProxy （集群 两个节点）：2台机；

6.1 MyCat搭建

mycat集群结构

6.1.1 读写分离+ 分库分表配置

6.1.1.1 分库分表类型：

水平拆分：将一个业务逻辑拆分为多个小库（例如：用户模块：库1、库2、…库6）垂直拆分：按业务模块拆分 （例如：系统 【订单数据库、用户管理数据库】）

6.1.1.2 安装MyCat

更详细配置介绍：/qq_30889373/article/details/103007012

下载MyCat

.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-0105164103-linux.tar.gz上传文件、解压。

6.1.1.3 编辑配置文件；

server.xml（配置访问Mycat的用户，schemas代表逻辑库）

<user name="root" defaultAccount="true"><property name="password">root</property><!-- 逻辑库--><property name="schemas">mydb</property></user>

schema.xml（读写分离、分库分表）

互相发送心跳 此处发送的是 当前登录用户账号，例如：root。

感知其他节点的心跳，可配合switchType设置。

参数解析：

dataHost

balance:读请求的负载均衡

>0 ：不开启读写分离，所有的读操作 都发送到writehost中

1：全部的readhost和stand by writehost都参与 读操作的负责均衡

2：读请求 随机发送给readhost、writehost

3：读请求随机发送给writehost中的readhost （writehost不参与读请求） --推荐

writeType:写请求的负载均衡

>0：写请求先发送给schema.xml中的第一个writehost。当第一个writehost挂掉，再自动切换到 writehost中 。切换的记录 会被记录在 conf/dnindex.properties --推荐

1：写请求随机发送到所有的wirtehost中

switchType: 是否允许 “读操作”在readhost和writehost上自动切换（解决延迟问题：当从readhost中读取数据中 出现网络延迟等问题时，自动从writehost中读数据）

>-1：不许

1：默认，允许

2：根据“主从同步的状态” 自动选择是否 切换。

主从之间 会持续发送心跳。 当心跳检测机制发送了IO延迟，则readhost自动切换到writehost；

否则不切换。 必须将心跳设置 show slave status --推荐

rule.xml 配置

参数解析：

columns：表中的列名algorithm：指定的算法function：自定义的算法

3.1 name：算法的名称

3.2 class：算法的实现类

3.3 name=“count”：对应切分了几个片/节点（此处为2个）

6.1.1.4 启动mycat：

开启mycat>bin/mycat start关闭mycatbing/mycat stop查看状态bin/mycat status

6.1.1.5 登录mycat

命令：

mysql -uroot -proot -h192.168.2.129 -P8066

提示：

不能直接在mycat所在节点 登录：mysql -uroot -proot -P8066

需要借助一个 已经安装了mysql软件的 计算机上 远程操作mysql:

在windows上远程连接 bigdata02上的 Mycat :mysql -uroot -proot -h192.168.2.129 -P8066

(mydb)

向mycat中插入数据

6.1.1.6 注意事项：

操作Mycat: 和sql 92基本一致 ; 操作端口8066 ；使用mycat的方法 和mysql基本一致管理端口9066。

SQL92：语法严格的SQL

insert into student(id,name) values(1,zs) ;

sql99：语法较为宽松;

insert into student values(1,zs) ;

6.1.1.7 常见bug：

Invalid DataSource:0 ，常见解决方案：防火墙、IP、端口，权限问题：临时开放全部的权限

6.1.1.8 问题排查

如果无法正常实现mycat功能，调试：

日志：

mycat/logs ：

mycat.log：执行出错

wrapper.log：启动错误

6.2 HAProxy搭建

haproxy集群结构

6.2.1 安装HAProxy

查看可用的haproxy版本：yum list | grep haproxy

haproxy.x86_64

在线安装：

yum -y install haproxy.x86_64

6.2.2 创建HAProxy组与用户

设置： 用户名是haproxy

chown -R haproxy:haproxy /etc/haproxy/

6.2.3 修改配置文件

6.2.3.1 配置日期文件

vi /etc/rsyslog.conf

将以下2个指令的注释释放：

$ModLoad imudp

$UDPServerRun 514

设置日志文件的路径：

local2.* 日志的保存文件

6.2.3.2 配置f文件

6.2.4 启动proxy

启动并使用haproxy：

启动

systemctl stop haproxy.service

systemctl status haproxy.service

6.2.5 web访问

访问web界面的地址： /haproxy admin/admin

cannot bind socket ：网络

权限 setenforce 0

6.2.6 集群

以上配置完毕，再用同样的方法 增加另一个haproxy (bigdata05 )

6.3 搭建Keepavlied

概述：主要用于为HAProxy搭建集群做准备，能够实现HAProxy的心跳监控，并根据HAProxy存活情况生成虚拟IP。客户端可以直接访问 Keepavlied生成的虚拟IP。

6.3.1 安装Keepavlied

yum list | grep keepalived

yum -y install keepalived.x86_64

6.3.2 修改配置

文件/etc/keepalived/keepalived.conf

注意红色框处名称需要与服务器网卡名称一致，本次服务器网卡名：ens33。

追踪脚本：/etc/check_haproxy.sh

6.3.3 集群配置

用同样的办法，在bigdata05上配置第二个keepalived。

在bigdata05的Keepavlied配置文件中修改一下：

router_id :NodeB

priority :90

6.3.4 Keepavlied操作（实现去中心化操作）

启动：

systemctl start keepalived.service

重启：

systemctl restart keepalived.service

开机自启：

systemctl enable keepalived.service

停止：

systemctl stop keepalived.service

状态：

systemctl status keepalived.service

6.3.5 检查是否配置成功

分别在配置了keepalived的计算机上（bigdata04和bigdata05) 通过命令 查看ip情况。

ip a

如果bigdata04服务器中出现配置的虚拟ip则表示配置成功，并表示此服务器抢到了虚拟IP。

04服务拿到了虚拟IP，所以05服务器无虚拟IP

当04服务器挂掉后，此时ip就会被05拿到

当04服务器宕机重启后，04服务器将再次拿到虚拟IP（默认权重高于 05）。

6.4 架构访问

192.168.2.222：Keepavlied 虚拟IP

访问命令：mysql -uroot -proot -h192.168.2.222 -P8066访问路径

192.168.2.222 ->VIP (keepalived) -> haproxy ->MyCat1/MyCat2 ->MySQL主从…

七、MGR（MySQL Group Replication）模式

主从复制间容易发生复制延迟，尤其是在5.6以前的版本，以及当数据库实例中存在没有显式主键表时，很容易发生。主从复制节点间的数据一致性无法自行实现最终一致性。当主节点发生故障时，如果有多个从节点，无法自动从中选择合适的节点作为新的主节点。如果采用（增强）半同步复制，那么当有个从节点因为负载较高、网络延迟或其他意外因素使得事务无法及时确认时，也会反过来影响主节点的事务提交。