【Mysql运维篇】二、分库分表、读写分离

文章目录

参考资料1. 分库分表

1.1 介绍

1. 问题分析2. 拆分策略3. 实现技术 1.2 Mycat概述

1. 介绍2. 下载3. 安装4.目录结构5. 概念介绍 1.3 Mycat入门

1. 需求2. 环境准备3. 分片配置4. 启动服务5. 分片测试示例 1.4 Mycat配置

1. schema.xml2. rule.xml3. server.xml 1.5 Mycat分片

1. 垂直拆分2. 水平拆分3. 分片规则-范围4. 分片规则-取模5. 分片规则-一致性hash6. 分片规则-枚举7. 分片规则-应用指定8. 分片规则-固定分片hash算法9. 分片规则-字符串hash解析10. 分片规则-按（天）日期分片11. 分片规则-自然月 1.6 Mycat管理及监控

1. Mycat原理2. MyCat管理3. Mycat-eye 2. 读写分离

2.1 介绍2.2 一主一从

1. 原理2. 环境准备 2.3 一主一从读写分离

1. 配置2. 测试 2.4 双主双从

1. 介绍2. 准备3. 搭建4. 测试 2.5 双主双从读写分离

1. 配置

1. schema.xml2. user.xml 2. 测试 完结撒花

参考资料

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. 下载

下载地址：http://dl.mycat.org.cn/

3. 安装

Mycat是采用java语言开发的开源的数据库中间件，支持Windows和Linux运行环境，下面介绍MyCat的Linux中的环境搭建。我们需要在准备好的服务器中安装如下软件：

MySQLJDKMycat 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逻辑库中作为底层标签存在, 直接定义了具体的数据库实例、读写分离、心跳语句。
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)

1 

在Mycat监控界面配置服务地址

测试
配置好了之后，我们可以通过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

两台主库相互复制
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;

经过上述配置之后，双主双从的复制结构就已经搭建完成了。

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

配置逻辑库：

配置数据节点：

配置节点主机：

select user()

具体的对应情况如下：

2. user.xml

配置root用户也可以访问到逻辑库 ITCAST_RW2。

	123456
	SHOPPING,ITCAST,ITCAST_RW2

2. 测试

登录MyCat，测试查询及更新操作，判定是否能够进行读写分离，以及读写分离的策略是否正确。当主库挂掉一个之后，是否能够自动切换。 完结撒花