原创 【ReactJs+springBoot项目——租房】第13章：MySQL主从复制+MyCat数据库中间件+HAProxy+PXC集群

分析目前系统架构中的数据库层存在的问题
分析MySQL数据库的集群方案
学习主从复制（读写分离）架构方案
掌握MyCat数据库中间件的使用
掌握HAProxy复制均衡的使用
掌握PXC集群的使用
多种集群架构的综合应用

1、系统架构存在的问题

在我们的系统架构中，DBserver方面我们只是使用了单节点服务，如果面对大并发，海量数据的存储，显然单节点的系统架构将存在很严重的问题，所以接下来，我们将实现MySQL的集群，来应对大并发、海量数据存储等问题。
2、MySQL数据库的集群方案
2.1、读写分离架构
2.1.1、说明
我们一般应用对数据库而言都是“读多写少”，也就说对数据库读取数据的压力比较大，有一个思路就是说采用数据库 集群的方案： 其中一个是主库，负责写入数据，我们称之为：写库； 其它都是从库，负责读取数据，我们称之为： 读库；

那么，对我们的要求是：
1.读库和写库的数据一致；
2.写数据必须写到写库；
3.读数据必须到读库；
2.1.2、架构

从该系统架构中，可以看出：
数据库从之前的单节点变为多节点提供服务主节点数据，同步到从节点数据
应用程序需要连接到2个数据库节点，并且在程序内部实现判断读写操作
这种架构存在2个问题：
应用程序需要连接到多个节点，对应用程序而言开发变得复杂这个问题，可以通过中间件解决

如果在程序内部实现，可使用Spring的AOP功能实现

主从之间的同步，是异步完成，也就意味着这是 弱一致性
可能会导致，数据写入主库后，应用程序读取从库获取不到数据，或者可能会丢失数据，对于数据安全性 要求比较高的应用是不合适的
该问题可以通过PXC集群解决
2.2、中间件
通过上面的架构，可以看出，应用程序会连接到多个节点，使得应用程序的复杂度会提升，可以通过中间件方式解 决，如下：

从架构中，可以看出：
应用程序只需要连接到中间件即可，无需连接多个数据库节点应用程序无需区分读写操作，对中间件直接进行读写操作即可
在中间件中进行区分读写操作，读发送到从节点，写发送到主节点
该架构也存在问题，中间件的性能成为了系统的瓶颈，那么架构可以改造成这样：

这样的话，中间件的可靠性得到了保证，但是也带来了新的问题，应用系统依然是需要连接到2个中间件，又为应用 系统带来了复杂度。
2.3、负载均衡
为了解决以上问题，我们将继续优化架构，在应用程序和中间件之间增加proxy代理，由代理来完成负载均衡的功能，应用程序只需要对接到proxy即可。·

至此，主从复制架构的高可用架构才算是搭建完成。
2.4、PXC集群架构
在前面的架构中，都是基于MySQL主从的架构，那么在主从架构中，弱一致性问题依然没有解决，如果在需要强一致性的需求中，显然这种架构是不能应对的，比如：交易数据。
PXC提供了读写强一致性的功能，可以保证数据在任何一个节点写入的同时可以同步到其它节点，也就意味着可以存 其它的任何节点进行读取操作，无延迟。
架构如下：

2.5、混合架构
在前面的PXC架构中，虽然可以实现了事务的强一致性，但是它是通过牺牲了性能换来的一致性，如果在某些业务场 景下，如果没有强一致性的需求，那么使用PXC就不合适了。所以，在我们的系统架构中，需要将这两种方式综合起 来，这样才是一个较为完善的架构。

3、搭建主从复制架构
使用的MySQL版本依然是衍生版Percona，版本为5.7.23。并且通过docker进行搭建服务。
3.1、主从复制原理

mysql主(称master)从(称slave)复制的原理：
master将数据改变记录到二进制日志(binary log)中,也即是配置文件log-bin指定的文件(这些记录叫做二进制日志事件，binary log events)
slave将master的binary log events拷贝到它的中继日志(relay log) slave重做中继日志中的事件,将改变反映它自己的数据(数据重演)

主从配置需要注意的地方
主DB server和从DB server数据库的版本一致主DB server和从DB server数据库数据一致
主DB server开启二进制日志,主DB server和从DB server的server_id都必须唯一
3.2、主库配置文件my.conf

3.3、在主库创建同步用户

3.4、从库配置文件my.conf

3.5、搭建主库

master状态：

3.6、搭建从库

可以进行创建数据库、表，插入数据等操作，可以发现实现了主从。
3.7、主从复制模式

在查看二进制日志相关参数内容中，会发现默认的模式为ROW，其实在MySQL中提供了有3种模式，基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed- based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

STATEMENT模式（SBR）
每一条会修改数据的sql语句会记录到binlog中。
优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。
缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

ROW模式（RBR）
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的 存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用
ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。
建议使用MIXED模式。

可以看到，设置已经生效。并且进行测试，同步功能正常。
4、MyCat中间件
4.1、简介

一个彻底开源的，面向企业应用开发的大数据库集群支持事务、ACID、可以替代MySQL的加强版数据库
一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群 一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server 结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
一个新颖的数据库中间件产品
4.2、优势
基于阿里开源的Cobar产品而研发，Cobar的稳定性、可靠性、优秀的架构和性能以及众多成熟的使用案例使得MYCAT一开始就拥有一个很好的起点，站在巨人的肩膀上，我们能看到更远。业界优秀的开源项目和创新思路被广泛融入到MYCAT的基因中，使得MYCAT在很多方面都领先于目前其他一些同类的开源项目，甚至超越某些商业产品。
MYCAT背后有一支强大的技术团队，其参与者都是5年以上资深软件工程师、架构师、DBA等，优秀的技术团队保证了MYCAT的产品质量。
MYCAT并不依托于任何一个商业公司，因此不像某些开源项目，将一些重要的特性封闭在其商业产品中，使得开源项目成了一个摆设。
4.3、关键特性
支持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开发版）。
4.4、读写分离
MySQL服务部署情况：

server.xml：

schema.xml：

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 没有。

rule.xml：

测试：

1--创建表
2CREATE TABLE `tb_ad` (
3`id` bigint(20) NOT NULL AUTO_INCREMENT,
4`type` int(10) DEFAULT NULL COMMENT '广告类型',
5`title` varchar(100) DEFAULT NULL COMMENT '描述',
6`url` varchar(200) DEFAULT NULL COMMENT '图片URL地址',
7`created` datetime DEFAULT NULL,
8`updated` datetime DEFAULT NULL,

9PRIMARY KEY (`id`)
10) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='广告表'; 11
12--测试插入数据
13INSERT INTO `tb_ad` (`id`, `type`, `title`, `url`, `created`, `updated`) VALUES ('1', '1', 'UniCity 万 科 天 空 之 城 ', 'http://itcast-haoke.oss-cn- qingdao.aliyuncs.com/images/2018/11/26/15432029097062227.jpg', '2018-11-26 11:28:49',
'2018-11-26 11:28:51');
14
15	--测试结果：主库有写入数据，从库会同步数据

4.5、数据分片·

MySQL集群1：

主机	端口	容器名称	角色
192.168.1.18	3306	percona-master01	master
192.168.1.18	3307	percona-slave01	slave
			
MySQL集群2：

主机	端口	容器名称	角色
192.168.1.18	3316	percona-master02	master
192.168.1.18	3317	percona-slave02	slave

4.5.1、配置master

4.5.2、配置slave

4.5.3、创建数据库以及表

4.5.4、配置MyCat
schema.xml：

rule.xml：

重新启动mycat进行测试：

1	./startup_nowrap.sh && tail -f ../logs/mycat.log

4.5.5、测试

INSERT INTO `tb_ad` (`id`, `type`, `title`, `url`, `created`, `updated`) VALUES ('UniCity万科天空之城', 'http://itcast-haoke.oss-cn-qingdao.aliyuncs.com/images/2018/11/26/15432029097062227.jpg', '2018-11-26 11:28:49','2018-11-26 11:28:51'); 

 INSERT INTO `tb_ad` (`id`, `type`, `title`, `url`, `created`, `updated`) VALUES ('2', '1', ' 天 和 尚 海 庭 前 ', 'http://itcast-haoke.oss-cn- qingdao.aliyuncs.com/images/2018/11/26/1543202958579877.jpg', '2018-11-26 11:29:27', '2018-11-26 11:29:29');
 
 INSERT INTO `tb_ad` (`id`, `type`, `title`, `url`, `created`, `updated`) VALUES ('3', '1', '[ 奉 贤 南 桥 ] 光 语 著 ', 'http://itcast-haoke.oss-cn- qingdao.aliyuncs.com/images/2018/11/26/15432029946721854.jpg', '2018-11-26 11:30:04', '2018-11-26 11:30:06');
 
INSERT INTO `tb_ad` (`id`, `type`, `title`, `url`, `created`, `updated`) VALUES ('4', '1', '[ 上 海 周 边 嘉 兴 ] 融 创 海 逸 长 洲 ', 'http://itcast-haoke.oss-cn- qingdao.aliyuncs.com/images/2018/11/26/15432030275359146.jpg', '2018-11-26 11:30:49', '2018-11-26 11:30:53');

结果：

查看mycat：
可以看到，数据已经从2个分片中进行了汇总。

4.6、mycat集群
mycat做了数据库的代理，在高并发的情况下，必然也会面临单节点性能问题，所以需要部署多个mycat节点。架构：

搭建多节点mycat：

多节点的mycat搭建完成。
5、负载均衡
在前面架构中，虽然对mycat做了集群，保障了mycat的可靠性，但是，应用程序需要连接到多个mycat，显然不是 很友好的，也就是说缺少负载均衡的组件，接下来我们来了解下HAProxy。

官网：http://www.haproxy.org/
关于并发性能，haproxy可以做到千万级的并发。（当然了，运行环境不同，测试结果也不相同的）
5.2、架构

5.3、部署安装HAProxy

编写配置文件：

启动容器：

1#启动容器
2docker restart haproxy && docker logs -f haproxy

5.4、测试
通过web界面进行测试：http://192.168.1.18:4001/dbs

从页面中，可以看出已经存在了2个mycat代理服务。通过mysql客户端进行测试：

6、PXC集群
6.1、简介
Percona XtraDB Cluster（简称PXC）是针对MySQL用户的高可用性和扩展性解决方案，基于Percona Server 。其包括了Write Set REPlication补丁，使用Galera 2.0库，这是一个针对事务性应用程序的同步多主机复制插件。

Percona Server是MySQL的改进版本，使用 XtraDB 存储引擎，在功能和性能上较 MySQL 有着很显著的提升，如提升了在高负载情况下的 InnoDB 的性能，为 DBA 提供了一些非常有用的性能诊断工具，另外有更多的参数和命令来控制服务器行为。
Percona XtraDB Cluster提供了：
同步复制，事务可以在所有节点上提交。多主机复制，你可以写到任何节点。
从（slave）服务器上的并行应用事件，真正的“并行复制”。 自动节点配置。
数据一致性，不再有未同步的从服务器。
官网：https://www.percona.com/software/mysql-database/percona-xtradb-cluster
图示：

6.2、架构

6.3、部署安装
接下来，我们部署安装三节点的PXC。

实施：

需要注意的是：先启动第一个节点，等到mysql客户端可以连接到服务后再启动其它节点。
6.4、测试

效果：

数据：

6.5、集群的说明
尽可能的控制PXC集群的规模，节点越多，数据同步速度越慢
所有PXC节点的硬件配置要一致，如果不一致，配置低的节点将拖慢数据同步速度
PXC集群只支持InnoDB引擎，不支持其他的存储引擎
6.6、PXC集群方案与Replication区别
PXC集群方案所有节点都是可读可写的，Replication从节点不能写入，因为主从同步是单向的，无法从slave节 点向master点同步。

PXC同步机制是同步进行的，这也是它能保证数据强一致性的根本原因，Replication同步机制是异步进行的， 它如果从节点停止同步，依然可以向主节点插入数据，正确返回，造成数据主从数据的不一致性。
PXC是用牺牲性能保证数据的一致性，Replication在性能上是高于PXC的。所以两者用途也不一致。PXC是用于 重要信息的存储，例如：订单、用户信息等。Replication用于一般信息的存储，能够容忍数据丢失，例如：购物车，用户行为日志等。
7、综合应用
前面学习了主从架构、Mycat中间件、HAProxy负载均衡、PXC集群架构，在实际的项目中，往往不单单是一种架 构，更多的使用的混合架构，下面我们将好客租房项目采用混合架构的方式进行完善数据库集群。
7.1、架构

说明：
HAProxy作为负载均衡器
部署了2个Mycat节点作为数据库中间件
部署了2个PXC集群节点，作为2个Mycat分片，每个PXC集群中有2个节点，作为数据的同步存储 部署了1个主从复制集群
房源数据保存到PXC分片中，其余数据保存到主从架构中
7.2、部署PXC集群

集群一：

节点	端口	容器名称	数据卷
node1	13306	pxc_node1	haoke-v1
node2	13307	pxc_node2	haoke-v2

集群二：

节点	端口	容器名称	数据卷
node3	13308	pxc_node3	haoke-v3
node4	13309	pxc_node4	haoke-v4

结果：

2个集群，4个节点，均启动成功。
分别在2个集群中创建haoke数据库以及房源数据表：

1CREATE TABLE `tb_house_resources` (
2`id` bigint(20) NOT NULL AUTO_INCREMENT,
3`title` varchar(100) DEFAULT NULL COMMENT '房源标题',
4`estate_id` bigint(20) DEFAULT NULL COMMENT '楼盘id',
5`building_num` varchar(5) DEFAULT NULL COMMENT '楼号（栋）',
6`building_unit` varchar(5) DEFAULT NULL COMMENT '单元号',
7`building_floor_num` varchar(5) DEFAULT NULL COMMENT '门牌号',
8`rent` int(10) DEFAULT NULL COMMENT '租金',
9`rent_method` tinyint(1) DEFAULT NULL COMMENT '租赁方式，1-整租，2-合租',
10	`payment_method` tinyint(1) DEFAULT NULL COMMENT '支付方式，1-付一押一，2-付三押一，3-付六押一，4-年付押一，5-其它',
11`house_type` varchar(255) DEFAULT NULL COMMENT '户型，如：2室1厅1卫',
12`covered_area` varchar(10) DEFAULT NULL COMMENT '建筑面积',
13`use_area` varchar(10) DEFAULT NULL COMMENT '使用面积',
14`floor` varchar(10) DEFAULT NULL COMMENT '楼层，如：8/26',
15`orientation` varchar(2) DEFAULT NULL COMMENT '朝向：东、南、西、北',
16`decoration` tinyint(1) DEFAULT NULL COMMENT '装修，1-精装，2-简装，3-毛坯',
17`facilities` varchar(50) DEFAULT NULL COMMENT '配套设施， 如：1,2,3',
18`pic` varchar(1000) DEFAULT NULL COMMENT '图片，最多5张',

19`house_desc` varchar(200) DEFAULT NULL COMMENT '描述',
20`contact` varchar(10) DEFAULT NULL COMMENT '联系人',
21`mobile` varchar(11) DEFAULT NULL COMMENT '手机号',
22`time` tinyint(1) DEFAULT NULL COMMENT '看房时间，1-上午，2-中午，3-下午，4-晚上，5-全天',
23`property_cost` varchar(10) DEFAULT NULL COMMENT '物业费',
24`created` datetime DEFAULT NULL,
25`updated` datetime DEFAULT NULL,
26PRIMARY KEY (`id`)
27) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='房源表'; 28

7.3、部署主从复制集群
master：

slave：

测试：

说明主从搭建成功。

创建haoke数据库以及创建广告表：

1CREATE TABLE `tb_ad` (
2`id` bigint(20) NOT NULL AUTO_INCREMENT,
3`type` int(10) DEFAULT NULL COMMENT '广告类型',
4`title` varchar(100) DEFAULT NULL COMMENT '描述',
5`url` varchar(200) DEFAULT NULL COMMENT '图片URL地址',
6`created` datetime DEFAULT NULL,
7`updated` datetime DEFAULT NULL,
8PRIMARY KEY (`id`)
9) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='广告表';

7.4、部署Mycat
7.4.1、节点一

server.xml：


schema.xml：


rule.xml：

设置端口以及启动：

测试：

测试插入数据：

1INSERT INTO `tb_house_resources` (`id`, `title`, `estate_id`, `building_num`,
`building_unit`, `building_floor_num`, `rent`, `rent_method`, `payment_method`,
`house_type`, `covered_area`, `use_area`, `floor`, `orientation`, `decoration`,
`facilities`, `pic`, `house_desc`, `contact`, `mobile`, `time`, `property_cost`,
`created`, `updated`) VALUES ('1', '东方曼哈顿 3室2厅 16000元', '1005', '2', '1', '1',
'1111', '1', '1', '1室1厅1卫1厨1阳台', '2', '2', '1/2', '南', '1', '1,2,3,8,9', NULL, '这
个经纪人很懒，没写核心卖点', '张三', '11111111111', '1', '11', '2018-11-16 01:16:00',
'2018-11-16 01:16:00');
2INSERT INTO `tb_house_resources` (`id`, `title`, `estate_id`, `building_num`,
`building_unit`, `building_floor_num`, `rent`, `rent_method`, `payment_method`,
`house_type`, `covered_area`, `use_area`, `floor`, `orientation`, `decoration`,
`facilities`, `pic`, `house_desc`, `contact`, `mobile`, `time`, `property_cost`,
`created`, `updated`) VALUES ('2', '康城 3室2厅1卫', '1002', '1', '2', '3', '2000', '1',
'2', '3室2厅1卫1厨2阳台', '100', '80', '2/20', '南', '1', '1,2,3,7,6', NULL, '拎包入住',
'张三', '18888888888', '5', '1.5', '2018-11-16 01:34:02', '2018-11-16 01:34:02');
3
4	INSERT INTO `tb_ad` (`id`, `type`, `title`, `url`, `created`, `updated`) VALUES ('1', '1', 'UniCity 万 科 天 空 之 城 ', 'http://itcast-haoke.oss-cn- qingdao.aliyuncs.com/images/2018/11/26/15432029097062227.jpg', '2018-11-26 11:28:49', '2018-11-26 11:28:51');
5

测试结果：

7.4.2、节点二

测试结果与节点一相同。
7.5、部署HAProxy
修改配置文件：

启动容器：

http://192.168.1.18:4001/dbs

至此，集群搭建完毕。