参考资料
https://www.bilibili.com/video/BV1Kr4y1i7ru?p=163
1. 分库分表
1.1 介绍
1. 问题分析
随着互联网及移动互联网的发展,应用系统的数据量也是成指数式增长,若采用单数据库进行数据存储,存在以下性能瓶颈:
- IO瓶颈:热点数据太多,数据库缓存不足,产生大量磁盘IO,效率较低。 请求数据太多,带宽不够,网络IO瓶颈。
- CPU瓶颈:排序、分组、连接查询、聚合统计等SQL会耗费大量的CPU资源,请求数太多,CPU出现瓶颈。
分库分表的中心思想都是将数据分散存储,使得单一数据库/表的数据量变小来缓解单一数据库的性能问题,从而达到提升数据库性能的目的
2. 拆分策略
-
垂直拆分
-
水平拆分
3. 实现技术
shardingJDBC
:基于AOP
原理,在应用程序中对本地执行的SQL进行拦截,解析、改写、路由处理。需要自行编码配置实现,只支持java
语言,性能较高。MyCat
:数据库分库分表中间件,不用调整代码即可实现分库分表,支持多种语言,性能不及前者。
1.2 Mycat概述
1. 介绍
Mycat
是开源的、活跃的、基于Java
语言编写的MySQL
数据库中间件。可以像使用mysql
一样来使用mycat
,对于开发人员来说根本感觉不到mycat
的存在。- 优势:
• 性能可靠稳定
• 强大的技术团队
• 体系完善
• 社区活跃
2. 下载
3. 安装
- Mycat是采用java语言开发的开源的数据库中间件,支持Windows和Linux运行环境,下面介绍MyCat的Linux中的环境搭建。我们需要在准备好的服务器中安装如下软件:
- MySQL
- JDK
- Mycat
4.目录结构
bin
:存放可执行文件,用于启动停止mycatconf
:存放mycat的配置文件lib
:存放mycat的项目依赖包(jar)logs
:存放mycat的日志文件
5. 概念介绍
1.3 Mycat入门
windows下的配置请参考博客。
1. 需求
由于 tb_order
表中数据量很大,磁盘IO及容量都到达了瓶颈,现在需要对 tb_order
表进行数据分片,分为三个数据节点,每一个节点主机位于不同的服务器上, 具体的结构,参考下图:
2. 环境准备
记得检查防火墙
3. 分片配置
-
配置
schema.xml
参考视频:
-
配置
server.xml
配置mycat的用户及用户的权限信息:
4. 启动服务
-
切换到
Mycat
的安装目录bin/
下,执行如下指令,启动Mycat:#启动 mycat start #停止 mycat stop
Mycat启动之后,默认占用端口号 8066
-
启动完毕之后,可以查看
logs
目录下的启动日志,查看Mycat
是否启动完成。tail -f logs/wrapper.log
5. 分片测试示例
-
通过如下指令,就可以连接并登陆MyCat。
mysql -h 192.168.200.210-P 8066-uroot-p123456
-h 后面跟IP,-P后面跟端口号,默认为8066,后面就是设置的用户名和密码
如果IP是localhost,则命令为:
mysql -h localhost -P 8066 -uroot -p5213
下面是我在windows下操作的界面:
-
然后就可以在
MyCat
中来创建表,并往表结构中插入数据,查看数据在MySQL
中的分布情况然后就可以在MyCat中来创建表,开往表结构中插大数据,查看数据在 CREATE TABLE TB_ORDER( id BIGINT(20) NOT NULL, title VARCHAR(100) NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB DEFAULT CHARSET=utf8; INSERT INTO TB_ORDER(id,title) VALUES(1,'goods1'); INSERT INTO TB_ORDER(id,title) VALUES(2,'goods2'); INSERT INTO TB_DRDER(id,title) VALUES(3,'goods3'): INSERT INTO TB_ORDER(id,title) VALUES(1000000,'goods1000000 '); INSERT INTO TB_ORDER(id,title) VALUES(10000000,'goods10000000');
1.4 Mycat配置
1. schema.xml
schema.xml
作为MyCat中最重要的配置文件之一 , 涵盖了MyCat的逻辑库 、 逻辑表 、 分片规则、分片节点及数据源的配置。
主要包含以下三组标签:
➢ schema
标签
➢ datanode
标签
➢ datahost
标签
-
schema
标签
schema
标签用于定义 MyCat实例中的逻辑库 , 一个MyCat实例中, 可以有多个逻辑库 , 可以通过 schema 标签来划分不同的逻辑库。- MyCat中的逻辑库的概念 , 等同于MySQL中的database概念 , 需要操作某个逻辑库下的表时, 也需要切换逻辑库(use xxx)。
table
标签定义了MyCat中逻辑库schema下的逻辑表 , 所有需要拆分的表都需要在table标签中定义 。
-
dataNode
标签
dataNode
标签中定义了MyCat中的数据节点, 也就是我们通常说的数据分片。一个dataNode
标签就是一个独立的数据分片。
-
dataHost
标签
- 该标签在MyCat逻辑库中作为底层标签存在, 直接定义了具体的数据库实例、读写分离、心跳语句。
- 该标签在MyCat逻辑库中作为底层标签存在, 直接定义了具体的数据库实例、读写分离、心跳语句。
2. rule.xml
rule.xml
中定义所有拆分表的规则, 在使用过程中可以灵活的使用分片算法, 或者对同一个分片算法使用不同的参数, 它让分片过程可配置化。主要包含两类标签: tableRule、 Function
。
3. server.xml
server.xml
配置文件包含了MyCat的系统配置信息,主要有两个重要的标签:system、user
。
-
system
标签
对应的系统配置项及其含义。
-
user
标签
1.5 Mycat分片
1. 垂直拆分
-
场景
在业务系统中, 涉及以下表结构 ,但是由于用户与订单每天都会产生大量的数据, 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分, 原有的数据库表如下。
相关数据资料请下载网盘资料,找到对应文件即可。
链接:https://pan.baidu.com/s/1k5HjER6xD4aSCVJ1hV5AFA
提取码:o0ac -
准备:分别在三台MySQL中创建数据库 shopping。
-
配置
-
在
mycat
的命令行中,通过source
指令导入表结构,以及对应的数据,查看数据分布情况。这两个sql文件为脚本文件。
-
查询用户的收件人及收件人地址信息(包含省、市、区)。
-
查询每一笔订单及订单的收件地址信息(包含省、市、区)。
-
全局表配置
对于省、市、区/县表tb_areas_provinces, tb_areas_city,tb_areas_region,
是属于数据字典表,在多个业务模块中都可能会遇到,可以将其设置为全局表,利于业务操作。
2. 水平拆分
-
场景
在业务系统中, 有一张表(日志表), 业务系统每天都会产生大量的日志数据 , 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分。 -
准备
分别在三台MySQL中创建数据库 itcast。
-
配置
-
测试
在mycat
的命令行中,执行如下SQL创建表、并插入数据,查看数据分布情况。CREATE TABLE tb_log ( id bigint(20) NOT NULL COMMENT 'ID', model_name varchar(200) DEFAULT NULL COMMENT '模块名', model_value varchar(200) DEFAULT NULL COMMENT '模块值', return_value varchar(200) DEFAULT NULL COMMENT '返回值', return_class varchar(200) DEFAULT NULL COMMENT '返回值类型', operate_user varchar(20) DEFAULT NULL COMMENT '操作用户', operate_time varchar(20) DEFAULT NULL COMMENT '操作时间', param_and_value varchar(500) DEFAULT NULL COMMENT '请求参数名及参数值', operate_class varchar(200) DEFAULT NULL COMMENT '操作类', operate method varchar(200) DEFAULT NULL COMMENT '操作方法', cost_time bigint(20) DEFAULT NULL COMMENT '执行方法耗时, 单位ms', Source int(1) DEFAULT NULL COMMENT '来源: 1 PC,2 Android,3 IOS', PRIMARY KEY (id) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time,source) VALUES('1','user','insert','success','java.lang.String','10001','2022-01-06 18:12:28','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','insert','10',1); INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time,source) VALUES('2','user','insert','success','java.lang.String','10001','2022-01-06 18:12:27','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','insert','23',1); INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time,source) VALUES('3','user','update','success','java.lang.String','10001','2022-01-06 18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','update','34',1); INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time,source) VALUES('4','user','update','success','java.lang.String','10001','2022-01-06 18:16:45','{\"age\":\"20\",\"name\":\"Tom\",\"gender\":\"1\"}','cn.itcast.controller.UserController','update','13',2); INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time,source) VALUES('5','user','insert','success','java.lang.String','10001','2022-01-06 18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.controller.UserController','insert','29',3); INSERT INTO tb_log (id, model_name, model_value, return_value, return_class, operate_user, operate_time, param_and_value, operate_class, operate_method, cost_time,source) VALUES('6','user','find','success','java.lang.String','10001','2022-01-06 18:30:31','{\"age\":\"200\",\"name\":\"TomCat\",\"gender\":\"0\"}','cn.itcast.controller.UserController','find','29',2);
3. 分片规则-范围
根据指定的字段及其配置的范围与数据节点的对应情况, 来决定该数据属于哪一个分片。
-
示例
4. 分片规则-取模
根据指定的字段值与节点数量进行求模运算,根据运算结果, 来决定该数据属于哪一个分片。
- 示例
5. 分片规则-一致性hash
所谓一致性哈希, 相同的哈希因子计算值总是被划分到相同的分区表中,不会因为分区节点的增加而改变原来数据的分区位置。
- 示例
6. 分片规则-枚举
通过在配置文件中配置可能的枚举值, 指定数据分布到不同数据节点上, 本规则适用于按照省份、性别、状态拆分数据等业务 。
- 示例
7. 分片规则-应用指定
运行阶段由应用自主决定路由到那个分片 , 直接根据字符子串(必须是数字)计算分片号。
-
示例
8. 分片规则-固定分片hash算法
该算法类似于十进制的求模运算,但是为二进制的操作,例如,取 id 的二进制低 10 位 与 1111111111 进行位 & 运算。
特点:
➢ 如果是求模,连续的值,分别分配到各个不同的分片;但是此算法会将连续的值可能分配到相同的分片,降低事务处理的难度。
➢ 可以均匀分配,也可以非均匀分配。
➢ 分片字段必须为数字类型。
9. 分片规则-字符串hash解析
截取字符串中的指定位置的子字符串, 进行hash算法, 算出分片。
10. 分片规则-按(天)日期分片
11. 分片规则-自然月
使用场景为按照月份来分片, 每个自然月为一个分片
1.6 Mycat管理及监控
1. Mycat原理
- 在MyCat中,当执行一条SQL语句时,MyCat需要进行SQL解析、分片分析、路由分析、读写分离分析等操作,最终经过一系列的分析决定将当前的SQL语句到底路由到那几个(或哪一个)节点数据库,数据库将数据执行完毕后,如果有返回的结果,则将结果返回给MyCat,最终还需要在MyCat中进行结果合并、聚合处理、排序处理、分页处理等操作,最终再将结果返回给客户端。而在MyCat的使用过程中,MyCat官方也提供了一个管理监控平台MyCat-Web(MyCat-eye)。
- Mycat-web 是 Mycat 可视化运维的管理和监控平台,弥补了 Mycat 在监控上的空白。帮 Mycat分担统计任务和配置管理任务。Mycat-web 引入了 ZooKeeper 作为配置中心,可以管理多个节点。Mycat-web 主要管理和监控 Mycat 的流量、连接、活动线程和内存等,具备 IP 白名单、邮件告警等模块,还可以统计 SQL 并分析慢 SQL 和高频 SQL 等。为优化 SQL 提供依据。
2. MyCat管理
Mycat默认开通2个端口,可以在server.xml
中进行修改。
8066
数据访问端口,即进行 DML 和 DDL 操作。9066
数据库管理端口,即 mycat 服务管理控制功能,用于管理mycat的整个集群状态
连接MyCat的管理控制台:
mysql -h 192.168.200.210 -p 9066 -uroot -p123456
3. Mycat-eye
-
介绍
Mycat-web
(Mycat-eye)是对mycat-server
提供监控服务,功能不局限于对mycat-server
使用。他通过JDBC
连接对Mycat
、Mysql
监控,监控远程服务器(目前仅限于linux系统)的cpu、内存、网络、磁盘。Mycat-eye
运行过程中需要依赖zookeeper
,因此需要先安装zookeeper
。 -
安装
1). zookeeper安装
2). Mycat-web安装 -
访问示例
http://192.168.200.210:8082/mycat
-
配置
- 开启MyCat的实时统计功能(server.xml)
<property name="useSqlStat">1</property> <!-- 1为开启实时统计、0为关闭 -->
- 在Mycat监控界面配置服务地址
- 开启MyCat的实时统计功能(server.xml)
-
测试
配置好了之后,我们可以通过MyCat执行一系列的增删改查的测试,然后过一段时间之后,打开mycat-eye
的管理界面,查看mycat-eye
监控到的数据信息。-
性能监控
-
物理节点
-
-
SQL统计
-
SQL表分析
-
SQL监控
-
高频SQL
-
2. 读写分离
2.1 介绍
读写分离,简单地说是把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作,从数据库提供读操作,这样能有效地减轻单台数据库的压力。
通过MyCat
即可轻易实现上述功能,不仅可以支持MySQL
,也可以支持Oracle
和SQL Server
。
2.2 一主一从
1. 原理
MySQL的主从复制,是基于二进制日志(binlog)实现的。
2. 环境准备
2.3 一主一从读写分离
MyCat
控制后台数据库的读写分离和负载均衡由schema.xml
文件datahost
标签的balance
属性控制。
1. 配置
writeHost
代表的是写操作对应的数据库,readHost
代表的是读操作对应的数据库。 所以我们要想实现读写分离,就得配置writeHost
关联的是主库,readHost
关联的是从库。而仅仅配置好了writeHost以及readHost还不能完成读写分离,还需要配置一个非常重要的负责均衡的参数 balance
,取值有4种,具体含义如下:
所以,在一主一从模式的读写分离中,balance
配置1或3都是可以完成读写分离的。
2. 测试
连接Mycat,并在Mycat中执行DML、 DQL查看是否能够进行读写分离。
然后观察,在执行增删改操作时,对应的主库及从库的数据变化。 在执行查询操作时,检查主库及从库对应的数据变化。
在测试中,我们可以发现当主节点Master宕机之后,业务系统就只能够读,而不能写入数据了。
那如何解决这个问题呢?这个时候我们就得通过另外一种主从复制结构来解决了,也就是我们接下来讲解的双主双从。
2.4 双主双从
1. 介绍
一个主机 Master1 用于处理所有写请求,它的从机 Slave1 和另一台主机 Master2 还有它的从机 Slave2 负责所有读请求。当 Master1主机宕机后, Master2 主机负责写请求, Master1 、 Master2 互为备机。架构图如下
2. 准备
我们需要准备5台服务器,具体的服务器及软件安装情况如下:
关闭以上所有服务器的防火墙:
systemctl stop firewalld
systemctl disable firewalld
3. 搭建
-
主库配置( Master1-192.168.200.211 )
- 修改配置文件
/etc/my.cnf
#mysql 服务ID,保证整个集群环境中唯一,取值范围:1 – 2^32-1,默认为1 server-id=1 #指定同步的数据库 binlog-do-db=db01 binlog-do-db=db02 binlog-do-db=db03 # 在作为从数据库的时候,有写入操作也要更新二进制日志文件 log-slave-updates
- 重启MySQL服务器
systemctl restart mysqld
- 修改配置文件
-
主库配置( Master2-192.168.200.213 )
- 修改配置文件
/etc/my.cnf
#mysql 服务ID,保证整个集群环境中唯一,取值范围:1 – 2^32-1,默认为1 server-id=3 #指定同步的数据库 binlog-do-db=db01 binlog-do-db=db02 binlog-do-db=db03 # 在作为从数据库的时候,有写入操作也要更新二进制日志文件 log-slave-updates
- 重启MySQL服务器
systemctl restart mysqld
- 修改配置文件
-
两台主库创建账户并授权
#创建itcast用户,并设置密码,该用户可在任意主机连接该MySQL服务 CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456'; #为 'itcast'@'%' 用户分配主从复制权限 GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';
通过指令,查看两台主库的二进制日志坐标
show master status ;
-
从库配置( Slave1-192.168.200.212 )
- 修改配置文件
/etc/my.cnf
#mysql 服务ID,保证整个集群环境中唯一,取值范围:1 – 232-1,默认为1 server-id=2
- 重新启动MySQL服务器
systemctl restart mysqld
- 修改配置文件
-
从库配置( Slave2-192.168.200.214 )
- 修改配置文件
/etc/my.cnf
#mysql 服务ID,保证整个集群环境中唯一,取值范围:1 – 232-1,默认为1 server-id=4
- 重新启动MySQL服务器
systemctl restart mysqld
- 修改配置文件
-
两台从库配置关联的主库
- 在 slave1(192.168.200.212)上执行
CHANGE MASTER TO MASTER_HOST='192.168.200.211', MASTER_USER='itcast', MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=663;
- 在 slave2(192.168.200.214)上执行
CHANGE MASTER TO MASTER_HOST='192.168.200.213', MASTER_USER='itcast', MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=663;
- 启动两台从库主从复制,查看从库状态
start slave; show slave status \G
- 在 slave1(192.168.200.212)上执行
-
两台主库相互复制
Master2 复制 Master1,Master1 复制 Master2。
- 在 Master1(192.168.200.211)上执行
CHANGE MASTER TO MASTER_HOST='192.168.200.213', MASTER_USER='itcast', MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=663;
- 在 Master2(192.168.200.213)上执行
CHANGE MASTER TO MASTER_HOST='192.168.200.211', MASTER_USER='itcast', MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=663;
- 启动两台从库主从复制,查看从库状态
start slave; show slave status \G;
- 在 Master1(192.168.200.211)上执行
经过上述配置之后,双主双从的复制结构就已经搭建完成了。
4. 测试
分别在两台主库Master1、Master2上执行DDL、DML语句,查看涉及到的数据库服务器的数据同步情况
create database db01;
use db01;
create table tb_user(
id int(11) not null,
name varchar(50) not null,
sex varchar(1),
primary key (id)
)engine=innodb default charset=utf8;
insert into tb_user(id,name,sex) values(1,'Tom','1');
insert into tb_user(id,name,sex) values(2,'Trigger','0');
insert into tb_user(id,name,sex) values(3,'Dawn','1');
insert into tb_user(id,name,sex) values(4,'Jack Ma','1');
insert into tb_user(id,name,sex) values(5,'Coco','0');
insert into tb_user(id,name,sex) values(6,'Jerry','1');
- 在Master1中执行DML、DDL操作,看看数据是否可以同步到另外的三台数据库中。
- 在Master2中执行DML、DDL操作,看看数据是否可以同步到另外的三台数据库中。
2.5 双主双从读写分离
1. 配置
MyCat
控制后台数据库的读写分离和负载均衡由schema.xml
文件datahost
标签的balance
属性控制,通过writeType
及switchType
来完成失败自动切换的。
1. schema.xml
-
配置逻辑库:
<schema name="ITCAST_RW2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7"> </schema>
-
配置数据节点:
<dataNode name="dn7" dataHost="dhost7" database="db01" />
-
配置节点主机:
<dataHost name="dhost7" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="master1" url="jdbc:mysql://192.168.200.211:3306? useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="1234" > <readHost host="slave1" url="jdbc:mysql://192.168.200.212:3306? useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="1234" /> </writeHost> <writeHost host="master2" url="jdbc:mysql://192.168.200.213:3306? useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="1234" > <readHost host="slave2" url="jdbc:mysql://192.168.200.214:3306? useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="1234" /> </writeHost> </dataHost>
具体的对应情况如下:
2. user.xml
配置root用户也可以访问到逻辑库 ITCAST_RW2。
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">SHOPPING,ITCAST,ITCAST_RW2</property>
</user>
2. 测试
- 登录MyCat,测试查询及更新操作,判定是否能够进行读写分离,以及读写分离的策略是否正确。
- 当主库挂掉一个之后,是否能够自动切换。