Mycat
一、Mycat入门概述
1、是什么
Mycat 是数据库中间件。
数据库中间件
中间件:是一类连接软件组件和应用的计算机软件,以便于软件各部件之间的沟通。
例子:Tomcat,web中间件。
数据库中间件:连接java应用程序和数据库
为什么要用Mycat?
- Java与数据库紧耦合。
- 高访问量高并发对数据库的压力。
- 读写请求数据不一致
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-beIXOp3L-1630978519487)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210806212457882.png)]
数据库中间件对比
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zwmaNBPg-1630978519492)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210806211758250.png)]
- Cobar属于阿里B2B事业群,始于2008年,在阿里服役3年多,接管3000+个MySQL数据库的schema,集群日处理在线SQL请求50亿次以上。由于Cobar发起人的离职,Cobar停止维护。
- Mycat是开源社区在阿里cobar基础上进行二次开发,解决了cobar存在的问题,并且加入了许多新的功能在其中。青出于蓝而胜于蓝。
- OneProxy基于MySQL官方的proxy思想利用c进行开发的,OneProxy是一款商业收费的中间件。舍弃了一些功能,专注在性能和稳定性上。
- kingshard由小团队用go语言开发,还需要发展,需要不断完善。
- Vitess是Youtube生产在使用,架构很复杂。不支持MySQL原生协议,使用需要大量改造成本。
- Atlas是360团队基于mysql proxy改写,功能还需完善,高并发下不稳定。
- MaxScale是mariadb(MySQL原作者维护的一个版本) 研发的中间件
- MySQLRoute是MySQL官方Oracle公司发布的中间件
Mycat的官网
http://www.mycat.io/
2、干什么
读写分离
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NaPOkraU-1630978519495)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210806212604018.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cCJyjQRq-1630978519497)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210806212126391.png)]
数据分片
垂直拆分(分库)、水平拆分(分表)、垂直+水平拆分(分库分表)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E7nsjryO-1630978519499)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210806212705129.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ecwBLVHh-1630978519501)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210806212149490.png)]
多数据源整合
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0JlIWDt0-1630978519502)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210806212210730.png)]
3、原理
Mycat 的原理中最重要的一个动词是“拦截”
,它拦截了用户发送过来的 SQL 语句,首先对 SQL 语句做了一些特定的分析:如分片分析、路由分析、读写分离分析、缓存分析等,然后将此 SQL 发往后端的真实数据库,并将返回的结果做适当的处理,最终再返回给用户。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qqBXwrpr-1630978519503)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210806212252464.png)]
这种方式把数据库的分布式从代码中解耦出来,程序员察觉不出来后台使用 Mycat 还是MySQL。
二、安装
1、安装
1、解压后即可使用
tar -zxvf Mycat-server-1.6.7.6-release-20210730131311-linux.tar.gz
解压缩文件拷贝到 linux 下 /usr/local/
cp -r mycat /usr/local/
2、三个配置文件
- schema.xml:定义逻辑库,表、分片节点等内容
- rule.xml:定义分片规则
- server.xml:定义用户以及系统相关变量,如端口等
2、启动
1、修改配置文件server.xml
这里首先你得有两台 mysql
vim server.xml
<!-- 配置 MyCat 用户名 密码 -->
<user name="mycat" defaultAccount="true">
<property name="password">sasa</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
</user>
vim schema.xml
删除标签间的表信息,标签只留一个,标签只留一个,
只留一对
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 相当与一个虚拟数据库,这个库中的表格映射了test库中的表 -->
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<!-- 数据节点 hadoop01 机器内的 test 数据库 -->
<dataNode name="dn1" dataHost="hadoop01" database="test" />
<dataHost name="hadoop01" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<!-- 心跳 -->
<heartbeat>select user()</heartbeat>
<!-- 读写分离 -->
<writeHost host="hostM1" url="hadoop01:3306" user="root" password="sasa">
<readHost host="hostS1" url="hadoop02:3306" user="root" password="sasa"></readHost>
</writeHost>
</dataHost>
</mycat:schema>
2、验证数据库访问情况
Mycat 作为数据库中间件要和数据库部署在不同机器上,所以要验证远程访问情况。
mysql -uroot -p123123 -h 192.168.140.128 -P 3306
mysql -uroot -p123123 -h 192.168.140.127 -P 3306
#如远程访问报错,请建对应用户
grant all privileges on *.* to root@'缺少的host' identified by '123123';
3、启动程序
①控制台启动 :去 mycat/bin 目录下执行 ./mycat console
②后台启动 :去 mycat/bin 目录下 ./mycat start
为了能第一时间看到启动日志,方便定位问题,我们选择①控制台启动。
4、启动时可能出现报错
如果操作系统是 CentOS6.8,可能会出现域名解析失败错误,如下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-03sYOE4N-1630978519506)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808140630369.png)]
可以按照以下步骤解决
-
用 vim 修改 /etc/hosts 文件,在 127.0.0.1 后面增加你的机器名
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Euqt5g8U-1630978519508)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808140654097.png)]
-
修改后重新启动网络服务
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0BU6wXPr-1630978519509)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808140710488.png)]
3、登录
登录后台管理窗口
此登录方式用于管理维护 Mycat
mysql -umycat -p123456 -P 9066 -h 192.168.140.128
#常用命令如下:
show database
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h4YshaSj-1630978519510)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808140858032.png)]
show @@help
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c1OXKnzl-1630978519511)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808140920790.png)]
登录数据窗口
此登录方式用于通过 Mycat 查询数据,我们选择这种方式访问 Mycat
mysql -umycat -p123456 -P 8066 -h 192.168.140.128
三、搭建读写分离
我们通过 Mycat 和 MySQL 的主从复制配合搭建数据库的读写分离,实现 MySQL 的高可用性。
我们将搭建:一主一从、双主双从两种读写分离模式。
1、搭建一主一从
一个主机用于处理所有写请求,一台从机负责所有读请求,架构图如下
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TYJU2rhB-1630978519513)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808141055090.png)]
搭建 MySQL 数据库主从复制
MySQL 主从复制原理
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-95xrxDVz-1630978519514)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808141159691.png)]
主机配置(host79)
修改配置文件:vim /etc/my.cnf
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT
binlog 日志三种格式
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ls1EuECE-1630978519515)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808141253915.png)]
从机配置(host80)
修改配置文件:vim /etc/my.cnf
#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay
主机、从机重启 MySQL 服务
主机从机都关闭防火墙
在主机上建立帐户并授权 slave
#在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';
#查询master的状态
show master status;
#记录下File和Position的值
#执行完此步骤后不要再操作主服务器MySQL,防止主服务器状态值变化
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XSFZv8Xw-1630978519516)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808141404083.png)]
在从机上配置需要复制的主机
#复制主机的命令
CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值; #启动从服务器复制功能
start slave;
#查看从服务器状态
show slave status\G;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Xhm9PrvF-1630978519517)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808141442513.png)]
#下面两个参数都是Yes,则说明主从配置成功!
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-b6u5MthX-1630978519518)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808141510752.png)]
主机新建库、新建表、insert 记录,从机复制
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hox1G5dY-1630978519519)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808141547867.png)]
如何停止从服务复制功能
stop slave;
如何重新配置主从
stop slave;
reset master;
修改Mycat的配置文件schema.xml
之前的配置已分配了读写主机,是否已实现读写分离?
验证读写分离
(1)在写主机插入:insert into mytbl values (1,@@hostname);
主从主机数据不一致了
(2)在Mycat里查询:select * from mytbl;
修改的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”,所有读请求随机的分发到 readhost 执行,writerHost 不负担读压力 单主双单中使用
为了能看到读写分离的效果,把balance设置成2,会在两个主机间切换查询
<dataHost name="host1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-f4VLDFWj-1630978519521)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808141821825.png)]
启动Mycat
验证读写分离
#(1)在写主机数据库表mytbl中插入带系统变量数据,造成主从数据不一致
INSERT INTO mytbl VALUES(2,@@hostname);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pJbz70QY-1630978519521)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808141916722.png)]
#(2)在Mycat里查询mytbl表,可以看到查询语句在主从两个主机间切换
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WyA4z30g-1630978519522)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808142010554.png)]
2、搭建双主双从
一个主机 m1 用于处理所有写请求,它的从机 s1 和另一台主机 m2 还有它的从机 s2 负责所有读请
求。当 m1 主机宕机后,m2 主机负责写请求,m1、m2 互为备机。架构图如下
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4XMtArXa-1630978519523)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808142248626.png)]
编号 | 角色 | IP 地址 | 机器名 |
---|---|---|---|
1 | Master1 | 192.168.140.128 | host79.atguigu |
2 | Slave1 | 192.168.140.127 | host80.atguigu |
3 | Master2 | 192.168.140.126 | host81.atguigu |
4\ | Slave2 | 192.168.140.125 | host82.atguigu |
搭建 MySQL 数据库主从复制(双主双从)
1、双主机配置
Master1配置
修改配置文件:vim /etc/my.cnf
#主服务器唯一ID
server-id=1
#启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量,指自增字段的起始值,其默认值是1,取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始,指字段一次递增多少,他的取值范围是1 .. 65535
auto-increment-offset=1
Master2配置
修改配置文件:vim /etc/my.cnf
#主服务器唯一ID
server-id=3 #启用二进制日志
log-bin=mysql-bin
# 设置不要复制的数据库(可设置多个)
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
#设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
#设置logbin格式
binlog_format=STATEMENT
# 在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
#表示自增长字段每次递增的量,指自增字段的起始值,其默认值是1,取值范围是1 .. 65535
auto-increment-increment=2
# 表示自增长字段从哪个数开始,指字段一次递增多少,他的取值范围是1 .. 65535
auto-increment-offset=2
2、双从机配置
Slave1配置
修改配置文件:vim /etc/my.cnf
#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay
Slave2配置
修改配置文件:vim /etc/my.cnf
#从服务器唯一ID
server-id=4 #启用中继日志
relay-log=mysql-relay
3、双主机、双从机重启 mysql 服务
4、主机从机都关闭防火墙
5、在两台主机上建立帐户并授权 slave
#在主机MySQL里执行授权命令
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123123';
#查询Master1的状态
show master status;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UE1KPrBX-1630978519525)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808142916369.png)]
#查询Master2的状态
show master status;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W7ZVdiRT-1630978519526)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808142935808.png)]
#分别记录下File和Position的值
#执行完此步骤后不要再操作主服务器MYSQL,防止主服务器状态值变化
6、在从机上配置需要复制的主机
Slava1 复制 Master1,Slava2 复制 Master2
#复制主机的命令
CHANGE MASTER TO MASTER_HOST='主机的IP地址',
MASTER_USER='slave',
MASTER_PASSWORD='123123',
MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;
Slava1的复制命令
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eBTRSvwx-1630978519527)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808143037288.png)]
Slava2的复制命令
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2v6JcKq9-1630978519528)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808143057458.png)]
#启动两台从服务器复制功能
start slave;
#查看从服务器状态
show slave status\G;
#Slava1的复制Master1
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-O561rS0l-1630978519529)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808143128155.png)]
#Slava2的复制Master2
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-neeqZiXg-1630978519530)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808143148113.png)]
#下面两个参数都是Yes,则说明主从配置成功!
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
7、两个主机互相复制
Master2 复制 Master1,Master1 复制 Master2
Master2的复制命令
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5cDDibHE-1630978519531)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808150258318.png)]
Master1的复制命令
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-taYUBAKZ-1630978519532)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808150313822.png)]
#启动两台主服务器复制功能
start slave;
#查看从服务器状态
show slave status\G;
Master2的复制Master1
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5p60Tooh-1630978519533)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808150406457.png)]
Master1的复制Master2
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R32ivB6X-1630978519534)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808150445545.png)]
#下面两个参数都是Yes,则说明主从配置成功!
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
8、Master1 主机新建库、新建表、insert 记录,Master2 和从机复制
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wtH0HH2m-1630978519535)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808150458616.png)]
9、如何停止从服务复制功能
stop slave;
10、如何重新配置主从
stop slave;
reset master;
修改 Mycat 的配置文件 schema.xml
修改的balance属性,通过此属性配置读写分离的类型
负载均衡类型,目前的取值有4 种:
- balance=“0”, 不开启读写分离机制,所有读操作都发送到当前可用的 writeHost 上。
- balance=“1”,全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且 M1 与 M2 互为主备),正常情况下,M2,S1,S2 都参与 select 语句的负载均衡。(双主双从使用)
- balance=“2”,所有读操作都随机的在 writeHost、readhost 上分发。
- balance=“3”,所有读请求随机的分发到 readhost 执行,writerHost 不负担读压力(单主单从使用)
为了双主双从读写分离balance设置为1
<dataNode name="dn1" dataHost="host1" database="testdb" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100" >
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.140.128:3306" user="root"
password="123123">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="192.168.140.127:3306" user="root"
password="123123" />
</writeHost>
<writeHost host="hostM2" url="192.168.140.126:3306" user="root"
password="123123">
<!-- can have multi read hosts -->
<readHost host="hostS2" url="192.168.140.125:3306" user="root"
password="123123" />
</writeHost>
</dataHost>
…#balance="1": 全部的readHost与stand by writeHost参与select语句的负载均衡。
#writeType="0": 所有写操作发送到配置的第一个writeHost,第一个挂了切到还生存的第二个
#writeType="1",所有写操作都随机的发送到配置的 writeHost,1.5 以后废弃不推荐
#writeHost,重新启动后以切换后的为准,切换记录在配置文件中:dnindex.properties 。 #switchType="1": 1 默认值,自动切换。
# -1 表示不自动切换
# 2 基于 MySQL 主从同步的状态决定是否切换。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bZOaF4nN-1630978519536)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808150939014.png)]
3、 启动 Mycat
4、 验证读写分离
#在写主机Master1数据库表mytbl中插入带系统变量数据,造成主从数据不一致
INSERT INTO mytbl VALUES(3,@@hostname);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pLGqoOIW-1630978519537)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808151108525.png)]
#在Mycat里查询mytbl表,可以看到查询语句在Master2(host81)、Slava1(host80)、Slava2(host82)
主从三个主机间切换
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eOzQaSgL-1630978519538)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808151126394.png)]
抗风险能力
停止数据库Master1
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Rdgp14YX-1630978519540)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808151226648.png)]
#在Mycat里插入数据依然成功,Master2自动切换为写主机
INSERT INTO mytbl VALUES(3,@@hostname);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W8SSB1t3-1630978519541)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808151245256.png)]
#启动数据库Master1
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WHG8ib4q-1630978519542)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808151314791.png)]``
#在Mycat里查询mytbl表,可以看到查询语句在Master1(host79)、Slava1(host80)、Slava2(host82)
主从三个主机间切换
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CKVJtMby-1630978519544)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210808151342113.png)]
Master1、Master2 互做备机,负责写的主机宕机,备机切换负责写操作,保证数据库读写分离高
可用性。
四、垂直拆分——分库
一个数据库由很多表的构成,每个表对应着不同的业务,垂直切分是指按照业务将表进行分类,分布到不同 的数据库上面,这样也就将数据或者说压力分担到不同的库上面,如下图:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WpRa3fUf-1630978519545)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809111425514.png)]
系统被切分成了,用户,订单交易,支付几个模块。
1、如何划分表
一个问题:在两台主机上的两个数据库中的表,能否关联查询?
答案:不可以关联查询。
分库的原则:有紧密关联关系的表应该在一个库里,相互没有关联关系的表可以分到不同的库里。
#客户表 rows:20万
CREATE TABLE customer(
id INT AUTO_INCREMENT,
NAME VARCHAR(200),
PRIMARY KEY(id)
);
#订单表 rows:600万
CREATE TABLE orders(
id INT AUTO_INCREMENT,
order_type INT,
customer_id INT,
amount DECIMAL(10,2),
PRIMARY KEY(id)
);
#订单详细表 rows:600万
CREATE TABLE orders_detail(
id INT AUTO_INCREMENT,
detail VARCHAR(2000),
order_id INT,
PRIMARY KEY(id)
);
#订单状态字典表 rows:20
CREATE TABLE dict_order_type(
id INT AUTO_INCREMENT,
order_type VARCHAR(200),
PRIMARY KEY(id)
);
以上四个表如何分库?客户表分在一个数据库,另外三张都需要关联查询,分在另外一个数据库。
2、实现分库
修改 schema 配置文件
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2" ></table>
</schema>
<dataNode name="dn1" dataHost="host1" database="orders" />
<dataNode name="dn2" dataHost="host2" database="orders" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.140.128:3306" user="root"
password="123123">
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM2" url="192.168.140.127:3306" user="root"
password="123123">
</writeHost>
</dataHost>
如下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Rx62vj1R-1630978519546)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809111638984.png)]
新增两个空白库
分库操作不是在原来的老数据库上进行操作,需要准备两台机器分别安装新的数据库
#在数据节点 dn1、dn2 上分别创建数据库 orders
CREATE DATABASE orders;
启动 Mycat
./mycat console
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GouElZRy-1630978519547)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809111726384.png)]
访问 Mycat 进行分库
#访问 Mycat
mysql -umycat -p123456 -h 192.168.140.128 -P 8066
#切换到 TESTDB
#创建 4 张表
#查看表信息,可以看到成功分库
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M8YqkZhf-1630978519548)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809111753589.png)]
五、水平拆分——分表
相对于垂直拆分,水平拆分不是将表做分类,而是按照某个字段的某种规则来分散到多个库之中,每个表中 包含一部分数据。简单来说,我们可以将数据的水平切分理解为是按照数据行的切分,就是将表中的某些行切分 到一个数据库,而另外的某些行又切分到其他的数据库中,如图:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XzUveNYe-1630978519549)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809111911612.png)]
1、实现分表
选择要拆分的表
MySQL 单表存储数据条数是有瓶颈的,单表达到 1000 万条数据就达到了瓶颈,会影响查询效率,需要进行水平拆分(分表)进行优化。
例如:例子中的 orders、orders_detail 都已经达到 600 万行数据,需要进行分表优化。
分表字段
以 orders 表为例,可以根据不同自字段进行分表
编号 | 分表字段 | 效果 |
---|---|---|
1 | id(主键、或创建时间) | 查询订单注重时效,历史订单被查询的次数少,如此分片会造成一个节点访问多,一个访问少,不平均。 |
2 | customer_id(客户 id) | 根据客户 id 去分,两个节点访问平均,一个客户的所 |
修改配置文件 schema.xml
#为 orders 表设置数据节点为 dn1、dn2,并指定分片规则为 mod_rule(自定义的名字)
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" ></table>
#如下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JsZlaWFW-1630978519550)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809112133682.png)]
修改配置文件 rule.xml
#在 rule 配置文件里新增分片规则 mod_rule,并指定规则适用字段为 customer_id, #还有选择分片算法 mod-long(对字段求模运算),customer_id 对两个节点求模,根据结果分片
#配置算法 mod-long 参数 count 为 2,两个节点
<tableRule name="mod_rule">
<rule>
<columns>customer_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
…
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
如下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qijcf4vb-1630978519551)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809112203155.png)]
在数据节点 dn2 上建 orders 表
重启 Mycat,让配置生效
访问 Mycat 实现分片
#在 mycat 里向 orders 表插入数据,INSERT 字段不能省略
INSERT INTO orders(id,order_type,customer_id,amount) VALUES (1,101,100,100100);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(2,101,100,100300);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(3,101,101,120000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(4,101,101,103000);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(5,102,101,100400);
INSERT INTO orders(id,order_type,customer_id,amount) VALUES(6,102,100,100020);
#在mycat、dn1、dn2中查看orders表数据,分表成功
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9ikQTLRm-1630978519552)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809112401265.png)]
2、Mycat 的分片 “join”
Orders 订单表已经进行分表操作了,和它关联的 orders_detail 订单详情表如何进行 join 查询。
我们要对 orders_detail 也要进行分片操作。Join 的原理如下图:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UDM6P6iP-1630978519553)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809112454030.png)]
ER 表
Mycat 借鉴了 NewSQL 领域的新秀 Foundation DB 的设计思路,Foundation DB 创新性的提出了 Table Group 的概念,其将子表的存储位置依赖于主表,并且物理上紧邻存放,因此彻底解决了JION 的效率和性能问 题,根据这一思路,提出了基于 E-R 关系的数据分片策略,子表的记录与所关联的父表记录存放在同一个数据分片上。
#修改 schema.xml 配置文件
…
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MECKuyyW-1630978519554)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809112539384.png)]
#在 dn2 创建 orders_detail 表 #重启 Mycat
#访问 Mycat 向 orders_detail 表插入数据
INSERT INTO orders_detail(id,detail,order_id) values(1,'detail1',1);
INSERT INTO orders_detail(id,detail,order_id) VALUES(2,'detail1',2);
INSERT INTO orders_detail(id,detail,order_id) VALUES(3,'detail1',3);
INSERT INTO orders_detail(id,detail,order_id) VALUES(4,'detail1',4);
INSERT INTO orders_detail(id,detail,order_id) VALUES(5,'detail1',5);
INSERT INTO orders_detail(id,detail,order_id) VALUES(6,'detail1',6);
#在mycat、dn1、dn2中运行两个表join语句
Select o.*,od.detail from orders o inner join orders_detail od on o.id=od.order_id;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QCe7gh9O-1630978519555)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809112604269.png)]
全局表
在分片的情况下,当业务表因为规模而进行分片以后,业务表与这些附属的字典表之间的关联,就成了比较 棘手的问题,考虑到字典表具有以下几个特性:
- 变动不频繁
- 数据量总体变化不大
- 数据规模不大,很少有超过数十万条记录
鉴于此,Mycat 定义了一种特殊的表,称之为“全局表”,全局表具有以下特性:
- 全局表的插入、更新操作会实时在所有节点上执行,保持各个分片的数据一致性
- 全局表的查询操作,只从一个节点获取
- 全局表可以跟任何一个表进行 JOIN 操作
将字典表或者符合字典表特性的一些表定义为全局表,则从另外一个方面,很好的解决了数据JOIN 的难题。通过全局表+基于 E-R 关系的分片策略,Mycat 可以满足 80%以上的企业应用开发
修改 schema.xml 配置文件
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" >
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>
<table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table>
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OOd54yvc-1630978519555)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809112754634.png)]
#在 dn2 创建 dict_order_type 表 #重启 Mycat
#访问 Mycat 向 dict_order_type 表插入数据
INSERT INTO dict_order_type(id,order_type) VALUES(101,'type1');
INSERT INTO dict_order_type(id,order_type) VALUES(102,'type2');
#在Mycat、dn1、dn2中查询表数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ce2BFwPd-1630978519556)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809112817486.png)]
3、常用分片规则
取模
此规则为对分片字段求摸运算。也是水平分表最常用规则。5.1 配置分表中,orders 表采用了此规则。
分片枚举
通过在配置文件中配置可能的枚举 id,自己配置分片,本规则适用于特定的场景,比如有些业务需要按照省份或区县来做保存,而全国省份区县固定的,这类业务使用本条规则。
1、修改schema.xml配置文件
<table name="orders_ware_info" dataNode="dn1,dn2" rule="sharding_by_intfile" ></table>
2、修改rule.xml配置文件
<tableRule name="sharding_by_intfile">
<rule>
<columns>areacode</columns>
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
…
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="type">1</property>
<property name="defaultNode">0</property>
</function>
# columns:分片字段,algorithm:分片函数
# mapFile:标识配置文件名称,type:0为int型、非0为String,
#defaultNode:默认节点:小于 0 表示不设置默认节点,大于等于 0 表示设置默认节点,
# 设置默认节点如果碰到不识别的枚举值,就让它路由到默认节点,如不设置不识别就报错
3、修改partition-hash-int.txt配置文件
110=0
120=1
4、重启 Mycat
5、访问Mycat创建表
订单归属区域信息表
CREATE TABLE orders_ware_info
(
`id` INT AUTO_INCREMENT comment '编号',
`order_id` INT comment '订单编号',
`address` VARCHAR(200) comment '地址',
`areacode` VARCHAR(20) comment '区域编号',
PRIMARY KEY(id)
);
6、插入数据
INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (1,1,'北京','110');
INSERT INTO orders_ware_info(id, order_id,address,areacode) VALUES (2,2,'天津','120');
7、查询Mycat、dn1、dn2可以看到数据分片效果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-98jHPdC6-1630978519557)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809113302205.png)]
范围约定
此分片适用于,提前规划好分片字段某个范围属于哪个分片。
1、修改schema.xml配置文件
<table name="payment_info" dataNode="dn1,dn2" rule="auto_sharding_long" ></table>
2、修改rule.xml配置文件
<tableRule name="auto_sharding_long">
<rule>
<columns>order_id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
…
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
<property name="defaultNode">0</property>
</function>
# columns:分片字段,algorithm:分片函数
# mapFile:标识配置文件名称
#defaultNode:默认节点:小于 0 表示不设置默认节点,大于等于 0 表示设置默认节点,
# 设置默认节点如果碰到不识别的枚举值,就让它路由到默认节点,如不设置不识别就报错
3、修改partition-hash-int.txt配置文件
0-102=0
103-200=1
4、重启 Mycat
5、访问Mycat创建表
支付信息表
CREATE TABLE payment_info
(
`id` INT AUTO_INCREMENT comment '编号',
`order_id` INT comment '订单编号',
`payment_status` INT comment '支付状态',
PRIMARY KEY(id)
);
6、插入数据
INSERT INTO payment_info (id,order_id,payment_status) VALUES (1,101,0);
INSERT INTO payment_info (id,order_id,payment_status) VALUES (2,102,1);
INSERT INTO payment_info (id,order_id ,payment_status) VALUES (3,103,0);
INSERT INTO payment_info (id,order_id,payment_status) VALUES (4,104,1);
7、查询Mycat、dn1、dn2可以看到数据分片效果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WWTf0hTC-1630978519558)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809113534185.png)]
按日期(天)分片
此规则为按天分片。设定时间格式、范围
1、修改schema.xml配置文件
<table name="login_info" dataNode="dn1,dn2" rule="sharding_by_date" ></table>
2、修改rule.xml配置文件
<tableRule name="sharding_by_date">
<rule>
<columns>login_date</columns>
<algorithm>shardingByDate</algorithm>
</rule>
</tableRule>
…
<function name="shardingByDate" class="io.mycat.route.function.PartitionByDate">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2019-01-01</property>
<property name="sEndDate">2019-01-04</property>
<property name="sPartionDay">2</property>
</function>
# columns:分片字段,algorithm:分片函数
#dateFormat :日期格式
#sBeginDate :开始日期
#sEndDate:结束日期,则代表数据达到了这个日期的分片后循环从开始分片插入
#sPartionDay :分区天数,即默认从开始日期算起,分隔 2 天一个分区
3、重启 Mycat
4、访问Mycat创建表
用户信息表
CREATE TABLE login_info
(
`id` INT AUTO_INCREMENT comment '编号',
`user_id` INT comment '用户编号',
`login_date` date comment '登录日期',
PRIMARY KEY(id)
);
5、插入数据
INSERT INTO login_info(id,user_id,login_date) VALUES (1,101,'2019-01-01');
INSERT INTO login_info(id,user_id,login_date) VALUES (2,102,'2019-01-02');
INSERT INTO login_info(id,user_id,login_date) VALUES (3,103,'2019-01-03');
INSERT INTO login_info(id,user_id,login_date) VALUES (4,104,'2019-01-04');
INSERT INTO login_info(id,user_id,login_date) VALUES (5,103,'2019-01-05');
INSERT INTO login_info(id,user_id,login_date) VALUES (6,104,'2019-01-06');
6、查询Mycat、dn1、dn2可以看到数据分片效果
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8c1ZQnL4-1630978519559)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809113755919.png)]
4、全局序列
在实现分库分表的情况下,数据库自增主键已无法保证自增主键的全局唯一。为此,Mycat 提供了全局 sequence,并且提供了包含本地配置和数据库配置等多种实现方式。
本地文件
此方式 Mycat 将 sequence 配置到文件中,当使用到 sequence 中的配置后,Mycat 会更下classpath 中的 sequence_conf.properties 文件中 sequence 当前的值。
- 优点:本地加载,读取速度较快
- 缺点:抗风险能力差,Mycat 所在主机宕机后,无法读取本地文件。
数据库方式
利用数据库一个表 来进行计数累加。但是并不是每次生成序列都读写数据库,这样效率太低。
Mycat 会预加载一部分号段到 Mycat 的内存中,这样大部分读写序列都是在内存中完成的。
如果内存中的号段用完了 Mycat 会再向数据库要一次。
问:那如果 Mycat 崩溃了 ,那内存中的序列岂不是都没了?
是的。如果是这样,那么 Mycat 启动后会向数据库申请新的号段,原有号段会弃用。
也就是说如果 Mycat 重启,那么损失是当前的号段没用完的号码,但是不会因此出现主键重复。
1、建库序列脚本
#在 dn1 上创建全局序列表
CREATE TABLE MYCAT_SEQUENCE (NAME VARCHAR(50) NOT NULL,current_value INT NOT
NULL,increment INT NOT NULL DEFAULT 100, PRIMARY KEY(NAME)) ENGINE=INNODB;
#创建全局序列所需函数
DELIMITER $$
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
DECLARE retval VARCHAR(64);
SET retval="-999999999,null";
SELECT CONCAT(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) INTO retval FROM
MYCAT_SEQUENCE WHERE NAME = seq_name;
RETURN retval;
END $$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER) RETURNS
VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = VALUE
WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER ;
DELIMITER $$
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$
DELIMITER ;
#初始化序列表记录
INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS', 400000,
100);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IjuRYdJO-1630978519561)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809114200246.png)]
2、修改 Mycat 配置
修改sequence_db_conf.properties
vim sequence_db_conf.properties
意思是 ORDERS这个序列在dn1这个节点上,具体dn1节点是哪台机子,请参考schema.xml
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lSwaQ3nP-1630978519562)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809114245935.png)]
修改server.xml
vim server.xml
全局序列类型:0-本地文件,1-数据库方式,2-时间戳方式。此处应该修改成1。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-99apZEii-1630978519563)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809114335119.png)]
重启Mycat
3、验证全局序列
登录 Mycat,插入数据
insert into orders(id,amount,customer_id,order_type) values(next value for
MYCATSEQ_ORDERS,1000,101,102);
查询数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VOArl3C0-1630978519564)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809114425893.png)]
重启Mycat后,再次插入数据,再查询
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TMsghiEe-1630978519565)(E:\笔记\Typora\狂神\image-20210809114442373.png)]
时间戳方式
全局序列ID= 64 位二进制 (42(毫秒)+5(机器 ID)+5(业务编码)+12(重复累加) 换算成十进制为 18 位数的long 类型,每毫秒可以并发 12 位二进制的累加。
- 优点:配置简单
- 缺点:18 位 ID 过长
自主生成全局序列
可在 java 项目里自己生成全局序列,如下:
-
根据业务逻辑组合
-
可以利用 redis 的单线程原子性 incr 来生成序列
但,自主生成需要单独在工程中用 java 代码实现,还是推荐使用 Mycat 自带全局序列。
六、基于 HA 机制的 Mycat 高可用
在实际项目中,Mycat 服务也需要考虑高可用性,如果 Mycat 所在服务器出现宕机,或 Mycat 服务故障,需要有备机提供服务,需要考虑 Mycat 集群。
1、高可用方案
我们可以使用 HAProxy + Keepalived 配合两台 Mycat 搭起 Mycat 集群,实现高可用性。HAProxy 实现了 MyCat 多节点的集群高可用和负载均衡,而 HAProxy 自身的高可用则可以通过 Keepalived 来实现。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UHraHThS-1630978519566)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809114726273.png)]
编号 | 角色 | IP 地址 | 机器名 |
---|---|---|---|
1 | Mycat1 | 192.168.140.128 | host79.atguigu |
2 | Mycat2 | 192.168.140.127 | host80.atguigu |
3 | HAProxy(master) | 192.168.140.126 | host81.atguigu |
4 | Keepalived(master) | 192.168.140.126 | host81.atguigu |
5 | HAProxy(backup) | 192.168.140.125 | host82.atguigu |
6 | Keepalived(backup) | 192.168.140.125 | host82.atguigu |
2、安装配置 HAProxy
安装 HAProxy
1、准备好HAProxy安装包,传到/opt目录下
2、解压到/usr/local/src
tar -zxvf haproxy-1.5.18.tar.gz -C /usr/local/src
3、进入解压后的目录,查看内核版本,进行编译
cd /usr/local/src/haproxy-1.5.18
uname -r
make TARGET=linux310 PREFIX=/usr/local/haproxy ARCH=x86_64
# ARGET=linux310,内核版本,使用uname -r查看内核,如:3.10.0-514.el7,此时该参数就为linux310; #ARCH=x86_64,系统位数;
#PREFIX=/usr/local/haprpxy #/usr/local/haprpxy,为haprpxy安装路径。
4、编译完成后,进行安装
make install PREFIX=/usr/local/haproxy
5、安装完成后,创建目录、创建HAProxy配置文件
mkdir -p /usr/data/haproxy/
vim /usr/local/haproxy/haproxy.conf
6、向配置文件中插入以下配置信息,并保存
global
log 127.0.0.1 local0
#log 127.0.0.1 local1 notice
#log loghost local0 info
maxconn 4096
chroot /usr/local/haproxy
pidfile /usr/data/haproxy/haproxy.pid
uid 99
gid 99
daemon
#debug
#quiet
defaults
log global
mode tcp
option abortonclose
option redispatch
retries 3
maxconn 2000
timeout connect 5000
timeout client 50000
timeout server 50000
listen proxy_status
bind :48066
mode tcp
balance roundrobin
server mycat_1 192.168.140.128:8066 check inter 10s
server mycat_2 192.168.140.127:8066 check inter 10s
frontend admin_stats
bind :7777
mode http
stats enable
option httplog
maxconn 10
stats refresh 30s
stats uri /admin
stats auth admin:123123
stats hide-version
stats admin if TRUE
启动验证
1、启动HAProxy
/usr/local/haproxy/sbin/haproxy -f /usr/local/haproxy/haproxy.conf
2、查看HAProxy进程
ps -ef|grep haproxy
3、打开浏览器访问
http://192.168.140.125:7777/admin
在弹出框输入用户名:admin密码:123123
如果Mycat主备机均已启动,则可以看到如下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-j4c7w1zk-1630978519567)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809115556765.png)]
4、验证负载均衡,通过HAProxy访问Mycat
mysql -umycat -p123456 -h 192.168.140.126 -P 48066
3、配置 Keepalived
安装 Keepalived
1、准备好Keepalived安装包,传到/opt目录下
2、解压到/usr/local/src
tar -zxvf keepalived-1.4.2.tar.gz -C /usr/local/src
3、安装依赖插件
yum install -y gcc openssl-devel popt-devel
#3进入解压后的目录,进行配置,进行编译
cd /usr/local/src/keepalived-1.4.2
./configure --prefix=/usr/local/keepalived
4、进行编译,完成后进行安装
make && make install
5、运行前配置
cp /usr/local/src/keepalived-1.4.2/keepalived/etc/init.d/keepalived /etc/init.d/
mkdir /etc/keepalived
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/src/keepalived-1.4.2/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
6、修改配置文件
vim /etc/keepalived/keepalived.conf
修改内容如下
! Configuration File for keepalived
global_defs {
notification_email {
xlcocoon@foxmail.com
}
notification_email_from keepalived@showjoy.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
#主机配MASTER,备机配BACKUP
state MASTER
#所在机器网卡
interface ens33
virtual_router_id 51
#数值越大优先级越高
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
#虚拟IP
192.168.140.200
} }
virtual_server 192.168.140.200 48066 {
delay_loop 6
lb_algo rr
lb_kind NAT
persistence_timeout 50
protocol TCP
real_server 192.168.140.125 48066 {
weight 1
TCP_CHECK {
connect_timeout 3
retry 3
delay_before_retry 3
} }
real_server 192.168.140.126 48600 {
weight 1
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
} }
启动验证
#1启动Keepalived
service keepalived start
#2登录验证
mysql -umycat -p123456 -h 192.168.140.200 -P 48066
4、测试高可用
测试步骤
#1关闭mycat
#2通过虚拟ip查询数据
mysql -umycat -p123456 -h 192.168.140.200 -P 48066
七、Mycat 安全设置
1、权限配置
user 标签权限控制
目前 Mycat 对于中间件的连接控制并没有做太复杂的控制,目前只做了中间件逻辑库级别的读写权限控制。是通过 server.xml 的 user 标签进行配置。
server.xml配置文件user部分
<user name="mycat">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
</user>
如下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dbkZ14JQ-1630978519568)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809134837042.png)]
配置说明
标签属性 | 说明 |
---|---|
name | 应用连接中间件逻辑库的用户名 |
password | 该用户对应的密码 |
TESTDB | 应用当前连接的逻辑库中所对应的逻辑表。schemas 中可以配置一个或多个 |
readOnly | 应用连接中间件逻辑库所具有的权限。true 为只读,false 为读写都有,默认为 false |
测试案例
测试案例一
- 使用user用户,权限为只读(readOnly:true)
- 验证是否可以查询出数据,验证是否可以写入数据
1、用user用户登录,运行命令如下:
mysql -uuser -puser -h 192.168.140.128 -P8066
2、切换到TESTDB数据库,查询orders表数据,如下:
use TESTDB
select * from orders;
3、可以查询到数据,如下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CImbuIKZ-1630978519569)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809135114183.png)]
4、执行插入数据sql,如下:
insert into orders(id,order_type,customer_id,amount) values(7,101,101,10000);
5、可看到运行结果,插入失败,只有只读权限,如下图:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mxSUy9YU-1630978519570)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809135137893.png)]
测试案例二
- 使用mycat用户,权限为可读写(readOnly:false)
- 验证是否可以查询出数据,验证是否可以写入数据
1、用mycat用户登录,运行命令如下:
mysql -umycat -p123456 -h 192.168.140.128 -P8066
2、切换到TESTDB数据库,查询orders表数据,如下:
use TESTDB
select * from orders;
3、可以查询到数据,如下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Rk1rcqgA-1630978519571)(E:\笔记\Typora\狂神\image-20210809135231160.png)]
4、执行插入数据sql,如下:
insert into orders(id,order_type,customer_id,amount) values(7,101,101,10000);
5、可看到运行结果,插入成功,如下图:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IbiFTPVz-1630978519572)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809135304937.png)]
privileges 标签权限控制
在 user 标签下的 privileges 标签可以对逻辑库(schema)、表(table)进行精细化的 DML 权限控制。
privileges 标签下的 check 属性,如为 true 开启权限检查,为 false 不开启,默认为 false。
由于 Mycat 一个用户的 schemas 属性可配置多个逻辑库(schema) ,所以 privileges 的下级节点 schema 节点同样可配置多个,对多库多表进行细粒度的 DML 权限控制。
#server.xml配置文件privileges部分
#配置orders表没有增删改查权限
<user name="mycat">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<!-- 表级 DML 权限设置 -->
<privileges check="true">
<schema name="TESTDB" dml="1111" >
<table name="orders" dml="0000"></table>
<!--<table name="tb02" dml="1111"></table>-->
</schema>
</privileges>
</user>
如下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-52EZr35N-1630978519574)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809135354994.png)]
配置说明
DML 权限 | 增加(insert) | 更新(update) | 查询(select) | 删除(select) |
---|---|---|---|---|
0000 | 禁止 | 禁止 | 禁止 | 禁止 |
0010 | 禁止 | 禁止 | 可以 | 禁止 |
1110 | 可以 | 禁止 | 禁止 | 禁止 |
1111 | 可以 | 可以 | 可以 | 可以 |
测试案例
测试案例一
- 使用mycat用户,privileges配置orders表权限为禁止增删改查(dml=“0000”)
- 验证是否可以查询出数据,验证是否可以写入数据
1、重启mycat,用mycat用户登录,运行命令如下:
mysql -umycat -p123456 -h 192.168.140.128 -P8066
2、切换到TESTDB数据库,查询orders表数据,如下:
use TESTDB
select * from orders;
3、禁止该用户查询数据,如下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1VIQYmRE-1630978519574)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809135619507.png)]
4、执行插入数据sql,如下:
insert into orders(id,order_type,customer_id,amount) values(8,101,101,10000);
5、可看到运行结果,禁止该用户插入数据,如下图:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CNfjYhN2-1630978519575)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809135626574.png)]
测试案例二
- 使用mycat用户,privileges配置orders表权限为可以增删改查(dml=“1111”)
- 验证是否可以查询出数据,验证是否可以写入数据
1、重启mycat,用mycat用户登录,运行命令如下:
mysql -umycat -p123456 -h 192.168.140.128 -P8066
2、切换到TESTDB数据库,查询orders表数据,如下:
use TESTDB
select * from orders;
3、可以查询到数据,如下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Wn7wGK0W-1630978519576)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809135745798.png)]
4、执行插入数据sql,如下:
insert into orders(id,order_type,customer_id,amount) values(8,101,101,10000);
5、可看到运行结果,插入成功,如下图:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0g2f4suL-1630978519577)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809135755191.png)]
6、执行删除数据sql,如下:
delete from orders where id in (7,8);
7、可看到运行结果,插删除成功,如下图:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tMDX6mrg-1630978519579)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809135923045.png)]
2、SQL 拦截
firewall 标签用来定义防火墙;firewall 下 whitehost 标签用来定义 IP 白名单 ,blacklist 用来定义SQL 黑名单。
白名单
可以通过设置白名单,实现某主机某用户可以访问 Mycat,而其他主机用户禁止访问。
设置白名单
server.xml配置文件firewall标签
配置只有192.168.140.128主机可以通过mycat用户访问
<firewall>
<whitehost>
<host host="192.168.140.128" user="mycat"/>
</whitehost>
</firewall>
如下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aufHQWL4-1630978519580)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809140113887.png)]
重启Mycat后,192.168.140.128主机使用mycat用户访问
mysql -umycat -p123456 -h 192.168.140.128 -P 8066
可以正常访问,如下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IVIbXkVP-1630978519581)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809140147818.png)]
在此主机换user用户访问,禁止访问
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7WuNK9gh-1630978519582)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809140202029.png)]
在192.168.140.127主机用mycat用户访问,禁止访问
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3CDKzYyB-1630978519583)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809140212320.png)]
黑名单
可以通过设置黑名单,实现 Mycat 对具体 SQL 操作的拦截,如增删改查等操作的拦截。
设置黑名单
server.xml配置文件firewall标签
配置禁止mycat用户进行删除操作
<firewall>
<whitehost>
<host host="192.168.140.128" user="mycat"/>
</whitehost>
<blacklist check="true">
<property name="deleteAllow">false</property>
</blacklist>
</firewall>
如下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KeqWkZzD-1630978519584)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809140317241.png)]
重启Mycat后,192.168.140.128主机使用mycat用户访问
mysql -umycat -p123456 -h 192.168.140.128 -P 8066
可以正常访问,如下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-G5LBn66P-1630978519585)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809140343459.png)]
切换TESTDB数据库后,执行删除数据语句
delete from orders where id=7;
运行后发现已禁止删除数据,如下图
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rGhca6As-1630978519586)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809140409694.png)]
可以设置的黑名单 SQL 拦截功能列表
配置项 | 缺省值 | 描述 |
---|---|---|
selelctAllow | true | 是否允许执行 SELECT 语句 |
deleteAllow | true | 是否允许执行 DELETE 语句 |
updateAllow | true | 是否允许执行 UPDATE 语句 |
insertAllow | true | 是否允许执行 INSERT 语句 |
createTableAllow | true | 是否允许创建表 |
setAllow | true | 是否允许使用 SET 语法 |
alterTableAllow | true | 是否允许执行 Alter Table 语句 |
dropTableAllow | true | 是否允许修改表 |
commitAllow | true | 是否允许执行 commit 操作 |
rollbackAllow | true | 是否允许执行 roll back 操作 |
八、Mycat 监控工具
1、Mycat-web 简介
Mycat-web 是 Mycat 可视化运维的管理和监控平台,弥补了 Mycat 在监控上的空白。帮 Mycat 分担统计任务和配置管理任务。Mycat-web 引入了 ZooKeeper 作为配置中心,可以管理多个节点。Mycat-web 主要管理和监控 Mycat 的流量、连接、活动线程和内存等,具备 IP 白名单、邮件告警等模块,还可以统计 SQL 并分析慢 SQL 和高频 SQL 等。为优化 SQL 提供依据。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JHgJ6cIc-1630978519587)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809140735978.png)]
2、Mycat-web 配置使用
ZooKeeper 安装
安装步骤如下:
1、下载安装包http://zookeeper.apache.org/
2、安装包拷贝到Linux系统/opt目录下,并解压
tar -zxvf zookeeper-3.4.11.tar.gz
3、进入ZooKeeper解压后的配置目录(conf),复制配置文件并改名
cp zoo_sample.cfg zoo.cfg
4、进入ZooKeeper的命令目录(bin),运行启动命令
./zkServer.sh start
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XuWlnFT8-1630978519589)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809140944807.png)]
5、ZooKeeper服务端口为2181,查看服务已经启动
netstat -ant | grep 2181
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Y4BoZ2v4-1630978519590)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809141003502.png)]
Mycat-web 安装
安装步骤如下:
1、下载安装包http://www.mycat.io/
2、安装包拷贝到Linux系统/opt目录下,并解压
tar -zxvf Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz
3、拷贝mycat-web文件夹到/usr/local目录下
cp -r mycat-web /usr/local
4、进入mycat-web的目录下运行启动命令
cd /usr/local/mycat-web/
./start.sh &
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZaSiPDKL-1630978519591)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809141204531.png)]
5、Mycat-web服务端口为8082,查看服务已经启动
netstat -ant | grep 8082
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gP8g3mf7-1630978519592)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809141214926.png)]
6、通过地址访问服务
http://192.168.140.127:8082/mycat/
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aNBYsXSw-1630978519593)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809141228555.png)]
Mycat-web 配置
安装步骤如下:
1、先在注册中心配置ZooKeeper地址,配置后刷新页面,可见
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Mu8OfK1F-1630978519594)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809141255487.png)]
2、新增Mycat监控实例
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ao2Qw1q9-1630978519595)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809141332089.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JhGakYQO-1630978519596)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809141348977.png)]
3、Mycat 性能监控指标
在 Mycat-web 上可以进行 Mycat 性能监控,例如:内存分享、流量分析、连接分析、活动线程分析等等。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-COsON1QM-1630978519597)(C:\Users\DELL\AppData\Roaming\Typora\typora-user-images\image-20210809141421705.png)]
Allow | true | 是否允许使用 SET 语法 |
| alterTableAllow | true | 是否允许执行 Alter Table 语句 |
| dropTableAllow | true | 是否允许修改表 |
| commitAllow | true | 是否允许执行 commit 操作 |
| rollbackAllow | true | 是否允许执行 roll back 操作 |
八、Mycat 监控工具
1、Mycat-web 简介
Mycat-web 是 Mycat 可视化运维的管理和监控平台,弥补了 Mycat 在监控上的空白。帮 Mycat 分担统计任务和配置管理任务。Mycat-web 引入了 ZooKeeper 作为配置中心,可以管理多个节点。Mycat-web 主要管理和监控 Mycat 的流量、连接、活动线程和内存等,具备 IP 白名单、邮件告警等模块,还可以统计 SQL 并分析慢 SQL 和高频 SQL 等。为优化 SQL 提供依据。
[外链图片转存中…(img-JHgJ6cIc-1630978519587)]
2、Mycat-web 配置使用
ZooKeeper 安装
安装步骤如下:
1、下载安装包http://zookeeper.apache.org/
2、安装包拷贝到Linux系统/opt目录下,并解压
tar -zxvf zookeeper-3.4.11.tar.gz
3、进入ZooKeeper解压后的配置目录(conf),复制配置文件并改名
cp zoo_sample.cfg zoo.cfg
4、进入ZooKeeper的命令目录(bin),运行启动命令
./zkServer.sh start
[外链图片转存中…(img-XuWlnFT8-1630978519589)]
5、ZooKeeper服务端口为2181,查看服务已经启动
netstat -ant | grep 2181
[外链图片转存中…(img-Y4BoZ2v4-1630978519590)]
Mycat-web 安装
安装步骤如下:
1、下载安装包http://www.mycat.io/
2、安装包拷贝到Linux系统/opt目录下,并解压
tar -zxvf Mycat-web-1.0-SNAPSHOT-20170102153329-linux.tar.gz
3、拷贝mycat-web文件夹到/usr/local目录下
cp -r mycat-web /usr/local
4、进入mycat-web的目录下运行启动命令
cd /usr/local/mycat-web/
./start.sh &
[外链图片转存中…(img-ZaSiPDKL-1630978519591)]
5、Mycat-web服务端口为8082,查看服务已经启动
netstat -ant | grep 8082
[外链图片转存中…(img-gP8g3mf7-1630978519592)]
6、通过地址访问服务
http://192.168.140.127:8082/mycat/
[外链图片转存中…(img-aNBYsXSw-1630978519593)]
Mycat-web 配置
安装步骤如下:
1、先在注册中心配置ZooKeeper地址,配置后刷新页面,可见
[外链图片转存中…(img-Mu8OfK1F-1630978519594)]
2、新增Mycat监控实例
[外链图片转存中…(img-ao2Qw1q9-1630978519595)]
[外链图片转存中…(img-JhGakYQO-1630978519596)]
3、Mycat 性能监控指标
在 Mycat-web 上可以进行 Mycat 性能监控,例如:内存分享、流量分析、连接分析、活动线程分析等等。
[外链图片转存中…(img-COsON1QM-1630978519597)]