MySQL运维(二)MySQL分库分表概念及实战、读取分离详解

MySQL运维(二)MySQL分库分表详解、读取分离详解

1、MySQL分库分表相关概念

1.1 分库分表概念

1.1.1 分库的原因

分库:就是一个数据库分成多个数据库,部署到不同机器。

在这里插入图片描述

如果业务量剧增,数据库可能会出现性能瓶颈,这时候我们就需要考虑拆分数据库。从这几方面来看:

  • 磁盘存储

业务量剧增,MySQL单机磁盘容量会撑爆,拆成多个数据库,磁盘使用率大大降低。

  • 并发连接支撑

数据库连接是有限的。在高并发的场景下,大量请求访问数据库,MySQL单机是扛不住的!当前非常火的微服务架构出现,就是为了应对高并发。它把订单、用户、商品等不同模块,拆分成多个应用,并且把单个数据库也拆分成多个不同功能模块的数据库(订单库、用户库、商品库),以分担读写压力。

1.1.2 分表的原因

分表:就是一个数据表分成多个表。

在这里插入图片描述

数据量太大,SQL的查询就会变慢。如果一个查询SQL没命中索引,千百万数据量级别的表可能会拖垮整个数据库。

即使SQL命中了索引,如果表的数据量超过一千万的话,查询也是会明显变慢的。这是因为索引一般是B+树结构,数据千万级别的话,B+树的高度会增高,查询就变慢。

1.2 分库分表的类别

1.2.1 垂直分库

垂直分库,将原来一个单数据库的压力分担到不同的数据库,可以很好应对高并发场景。

在这里插入图片描述

1.2.2 水平分库

水平分库是指,将表的数据量切分到不同的数据库服务器上,每个服务器具有相同的库和表,只是表中的数据集合不一样。它可以有效的缓解单机单库的性能瓶颈和压力。

在这里插入图片描述

1.2.3 垂直分表

如果一个单表包含了几十列甚至上百列,管理起来很混乱,每次都select *的话,还占用IO资源。这时候,我们可以将一些不常用的、数据较大或者长度较长的列拆分到另外一张表。

在这里插入图片描述

1.2.4 水平分表

如果一个表的数据量太大,可以按照某种规则(如hash取模、range),把数据切分到多张表去。

在这里插入图片描述

1.3 分库分表的策略

分库分表策略一般有几种,使用与不同的场景:

  • range范围
  • hash取模
  • range+hash取模混合

1.3.1 range范围

range,即范围策略划分表。比如我们可以将表的主键,按照从0~1000万的划分为一个表,1000~2000万划分到另外一个表。如下图:

在这里插入图片描述

当然,有时候我们也可以按时间范围来划分,如不同年月的订单放到不同的表,它也是一种range的划分策略。

这种方案的优点:

  • 这种方案有利于扩容,不需要数据迁移。假设数据量增加到5千万,我们只需要水平增加一张表就好啦,之前0~4000万的数据,不需要迁移。

缺点:

  • 这种方案会有热点问题,因为订单id是一直在增大的,也就是说最近一段时间都是汇聚在一张表里面的。比如最近一个月的订单都在1000万~2000万之间,平时用户一般都查最近一个月的订单比较多,请求都打到order_1表啦,这就导致数据热点问题。

1.3.2 hash取模

hash取模策略:指定的路由key(一般是user_id、订单id作为key)对分表总数进行取模,把数据分散到各个表中。

在这里插入图片描述

这种方案的优点:

  • hash取模的方式,不会存在明显的热点问题。

缺点:

  • 如果一开始按照hash取模分成4个表了,未来某个时候,表数据量又到瓶颈了,需要扩容,这就比较棘手了。比如你从4张表,又扩容成8张表,那之前id=5的数据是在(5%4=1,即t_order_1),现在应该放到(5%8=5,即t_order_5),也就是说历史数据要做迁移了

1.3.3 range+hash取模混合

既然range存在热点数据问题,hash取模扩容迁移数据比较困难,我们可以综合两种方案一起,取之之长,弃之之短。

比较简单的做法就是,在拆分库的时候,我们可以先用range范围方案,比如订单id在04000万的区间,划分为订单库1;id在4000万8000万的数据,划分到订单库2,将来要扩容时,id在8000万~1.2亿的数据,划分到订单库3。然后订单库内,再用hash取模的策略,把不同订单划分到不同的表。

在这里插入图片描述

1.4 分库分表的时机

1.4.1 什么时候分表?

如果你的系统处于快速发展时期,如果每天的订单流水都新增几十万,并且,订单表的查询效率明变慢时,就需要规划分库分表了。一般B+树索引高度是2~3层最佳,如果数据量千万级别,可能高度就变4层了,数据量就会明显变慢了。一般500万数据就要考虑分表了。

1.4.2 什么时候分库

业务发展很快,还是多个服务共享一个单体数据库,数据库成为了性能瓶颈,就需要考虑分库了。比如订单、用户等,都可以抽取出来,新搞个应用(其实就是微服务思想),并且拆分数据库(订单库、用户库)。

1.5 分库分表产生的问题及分库分表中间件

1.5.1 事务问题

分库分表后,假设两个表在不同的数据库,那么本地事务已经无效啦,需要使用分布式事务了。

1.5.2 跨库关联

跨节点Join的问题:解决这一问题可以分两次查询实现

1.5.3 排序问题

跨节点的count,order by,group by以及聚合函数等问题:可以分别在各个节点上得到结果后在应用程序端进行合并。

1.5.4 分页问题

  • 方案1:在个节点查到对应结果后,在代码端汇聚再分页。
  • 方案2:把分页交给前端,前端传来pageSize和pageNo,在各个数据库节点都执行分页,然后汇聚总数量前端。这样缺点就是会造成空查,如果分页需要排序,也不好搞。

1.5.5 分布式ID

数据库被切分后,不能再依赖数据库自身的主键生成机制啦,最简单可以考虑UUID,或者使用雪花算法生成分布式ID。

1.5.6 分库分表中间件

目前流行的分库分表中间件比较多:

  • cobar
  • Mycat
  • Sharding-JDBC
  • Atlas
  • TDDL(淘宝)
  • vitess

2、分库分表中间件__Mycat使用详解

Mycat是开源的、活跃的、基于Java语言编写的MySQL数据库中间件。可以像使用mysql一样来使用mycat,对于开发人员来说根本感觉不到mycat的存在。
开发人员只需要连接MyCat即可,而具体底层用到几台数据库,每一台数据库服务器里面存储了什么数据,都无需关心。 具体的分库分表的策略,只需要在MyCat中配置即可。

在这里插入图片描述

2.1 Mycat的下载及安装

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

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

  • MySQL
  • JDK
  • Mycat
服务器安装软件说明
192.168.42.104JDK、MycatMyCat中间件服务器
192.168.42.104MySQL(端口3301)分片服务器
192.168.42.104MySQL(端口3302)分片服务器
192.168.42.104MySQL(端口3303)分片服务器

jdk安装过程略。。。

[root@centos04 apps]# java -version
java version "1.8.0_171"
Java(TM) SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot(TM) 64-Bit Server VM (build 25.171-b11, mixed mode)

使用docker在同一台服务器启动3个mysql实例

可以参考之前的文章,如下:

容器虚拟化技术Docker(二)mysql主从配置案例、redis集群搭建及扩容、缩容案例详解

docker run -p 3301:3306 --name mysql-01 \
-v /mydata/mysql-01/log:/var/log/mysql \
-v /mydata/mysql-01/data:/var/lib/mysql \
-v /mydata/mysql-01/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456  \
-d mysql:5.7


docker run -p 3302:3306 --name mysql-02 \
-v /mydata/mysql-02/log:/var/log/mysql \
-v /mydata/mysql-02/data:/var/lib/mysql \
-v /mydata/mysql-02/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456  \
-d mysql:5.7


docker run -p 3303:3306 --name mysql-03 \
-v /mydata/mysql-03/log:/var/log/mysql \
-v /mydata/mysql-03/data:/var/lib/mysql \
-v /mydata/mysql-03/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=123456  \
-d mysql:5.7

[root@centos04 apps]# docker ps
CONTAINER ID   IMAGE       COMMAND                  CREATED          STATUS          PORTS                                                  NAMES
1a7fb3926eec   mysql:5.7   "docker-entrypoint.s…"   6 seconds ago    Up 5 seconds    33060/tcp, 0.0.0.0:3303->3306/tcp, :::3303->3306/tcp   mysql-03
d3fe19fdce82   mysql:5.7   "docker-entrypoint.s…"   23 seconds ago   Up 22 seconds   33060/tcp, 0.0.0.0:3302->3306/tcp, :::3302->3306/tcp   mysql-02
6ef5e3163244   mysql:5.7   "docker-entrypoint.s…"   4 minutes ago    Up 4 minutes    33060/tcp, 0.0.0.0:3301->3306/tcp, :::3301->3306/tcp   mysql-01

使用客户端工具进行连接

在这里插入图片描述

2.1.1 上传Mycat压缩包到服务器

Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz 

2.1.2 解压MyCat的压缩包

tar -zxvf Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz -C /opt/apps/


[root@centos04 mycat]# ll
total 12
drwxr-xr-x. 2 root root  190 Jan 29 13:20 bin
drwxrwxrwx. 2 root root    6 Apr 15  2020 catlet
drwxrwxrwx. 4 root root 4096 Jan 29 13:20 conf
drwxr-xr-x. 2 root root 4096 Jan 29 13:20 lib
drwxrwxrwx. 2 root root    6 Aug 31  2021 logs
-rwxrwxrwx. 1 root root  227 Sep 13  2021 version.txt

# bin : 存放可执行文件,用于启动停止mycat
# conf:存放mycat的配置文件
# lib:存放mycat的项目依赖包(jar)
# logs:存放mycat的日志文件

2.1.3 mycat概念

MyCat的整体结构中,分为两个部分:上面的逻辑结构、下面的物理结构。

在MyCat的逻辑结构主要负责逻辑库、逻辑表、分片规则、分片节点等逻辑结构的处理,而具体的数据存储还是在物理结构,也就是数据库服务器中存储的。

在这里插入图片描述

2.2 Mycat入门(水平分表)

由于 tb_order 表中数据量很大,磁盘IO及容量都到达了瓶颈,现在需要对 tb_order 表进行数据分片,分为三个数据节点,每一个节点主机位于不同的服务器上, 具体的结构,参考下图

在这里插入图片描述

在上述3台mysql数据库中创建数据库 db01 。

在这里插入图片描述

2.2.1 配置

可以用notepad++安装NppFTP实现对linux上配置文件的更改。

  • 下载地址:https://github.com/ashkulz/NppFTP/releases
    选择对应版本解压,x86对应32位,x64对应64位。

  • 解压后将.dll文件复制到notepad++安装目录的plugins文件夹中

  • 重启Notepad++后即可使用(虚拟机连接:连接方式:SFTP 端口号:22)

也可以参考https://www.likecs.com/show-203660625.html#sc=400进行配置

schema.xml中配置

在schema.xml中配置逻辑库、逻辑表、数据节点、节点主机等相关信息。具体的配置如下:

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

	<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
		<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
	</schema>

	<dataNode name="dn1" dataHost="dhost1" database="db01" />
	<dataNode name="dn2" dataHost="dhost2" database="db01" />
	<dataNode name="dn3" dataHost="dhost3" database="db01" />

	<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<writeHost host="master" url="jdbc:mysql://192.168.42.104:3301?useSSL=false" user="root"  password="123456">
		</writeHost>

	</dataHost>
	
	
	<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<writeHost host="master" url="jdbc:mysql://192.168.42.104:3302?useSSL=false" user="root"  password="123456">
		</writeHost>

	</dataHost>
	
	
	<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<writeHost host="master" url="jdbc:mysql://192.168.42.104:3303?useSSL=false" user="root" password="123456">
		</writeHost>
	</dataHost>


</mycat:schema>

server.xm进行配置

需要在server.xml中配置用户名、密码,以及用户的访问权限信息,具体的配置如下:

	<user name="root" defaultAccount="true">
		<property name="password">123456</property>
		<property name="schemas">DB01</property>
		
		<!-- 表级 DML 权限设置 -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
	</user>

	<user name="user">
		<property name="password">user</property>
		<property name="schemas">DB01</property>
		<property name="readOnly">true</property>
	</user>

上述的配置表示,定义了两个用户 root 和 user ,这两个用户都可以访问 DB01 这个逻辑库,访问密码都是123456,但是root用户访问DB01逻辑库,既可以读,又可以写,但是 user用户访问DB01逻辑库是只读的。

2.2.2 测试

启动Mycat
[root@centos04 mycat]# bin/mycat start
Starting Mycat-server...

# 关闭
[root@centos04 mycat]# bin/mycat stop

Mycat启动之后,占用端口号 8066。

启动完毕之后,可以查看logs目录下的启动日志,查看Mycat是否启动完成。

[root@centos04 mycat]# tail -f logs/wrapper.log 
STATUS | wrapper  | 2023/01/29 17:56:51 | Launching a JVM...
INFO   | jvm 1    | 2023/01/29 17:56:51 | Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0
INFO   | jvm 1    | 2023/01/29 17:56:51 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2023/01/29 17:56:51 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2023/01/29 17:56:51 | 
INFO   | jvm 1    | 2023/01/29 17:56:51 | SLF4J: Class path contains multiple SLF4J bindings.
INFO   | jvm 1    | 2023/01/29 17:56:51 | SLF4J: Found binding in [jar:file:/opt/apps/mycat/lib/log4j-slf4j-impl-2.13.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
INFO   | jvm 1    | 2023/01/29 17:56:51 | SLF4J: Found binding in [jar:file:/opt/apps/mycat/lib/log4j-slf4j-impl-2.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
INFO   | jvm 1    | 2023/01/29 17:56:51 | SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
INFO   | jvm 1    | 2023/01/29 17:56:52 | MyCAT Server startup successfully. see logs in logs/mycat.log

# 出现MyCAT Server startup successfully.
测试
# 登录mycat
mysql -h 192.168.42.104 -P 8066 -uroot -p123456


# 然后就可以在MyCat中来创建表,并往表结构中插入数据,查看数据在MySQL中的分布情况。
mysql> show databases;
+----------+
| DATABASE |
+----------+
| DB01     |
+----------+
1 row in set (0.01 sec)

mysql> use DB01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables in DB01 |
+----------------+
| tb_order       |
+----------------+
1 row in set (0.00 sec)

# 在mycat上创建表
mysql> CREATE TABLE TB_ORDER (
    -> id BIGINT(20) NOT NULL,
    -> title VARCHAR(100) NOT NULL ,
    -> PRIMARY KEY (id)
    -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.06 sec)
 OK!

# 此时在物理库上,出现此表

在这里插入图片描述

# 插入测试数据
mysql> INSERT INTO TB_ORDER(id,title) VALUES(1,'goods1');
Query OK, 1 row affected (0.02 sec)
 OK!

mysql> INSERT INTO TB_ORDER(id,title) VALUES(2,'goods2');
Query OK, 1 row affected (0.00 sec)
 OK!

mysql> INSERT INTO TB_ORDER(id,title) VALUES(3,'goods3');
Query OK, 1 row affected (0.01 sec)
 OK!

mysql> 
mysql> INSERT INTO TB_ORDER(id,title) VALUES(5000000,'goods5000000');
Query OK, 1 row affected (0.00 sec)
 OK!

mysql> INSERT INTO TB_ORDER(id,title) VALUES(10000000,'goods10000000');
Query OK, 1 row affected (0.00 sec)
 OK!

mysql> INSERT INTO TB_ORDER(id,title) VALUES(10000001,'goods10000001');
Query OK, 1 row affected (0.00 sec)
 OK!

mysql> INSERT INTO TB_ORDER(id,title) VALUES(15000000,'goods15000000');
Query OK, 1 row affected (0.00 sec)
 OK!

mysql> INSERT INTO TB_ORDER(id,title) VALUES(15000001,'goods15000001');
ERROR 1064 (HY000): can't find any valid datanode :TB_ORDER -> ID -> 15000001

在这里插入图片描述

经过测试,我们发现,在往 TB_ORDER 表中插入数据时:

  • 如果id的值在1-500w之间,数据将会存储在第一个分片数据库中。
  • 如果id的值在500w-1000w之间,数据将会存储在第二个分片数据库中。
  • 如果id的值在1000w-1500w之间,数据将会存储在第三个分片数据库中。
  • 如果id的值超出1500w,在插入数据时,将会报错。

这是由逻辑表配置时的一个参数 rule 决定的,而这个参数配置的就是分片规则 。在rule.xml文件中找到auto-sharding-long及对应的function

	<tableRule name="auto-sharding-long">
		<rule>
			<columns>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>
	</function>

在conf目录下找到autopartition-long.txt文件,可以看到这里面规定了分片的规则。

# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2

2.3 Mycat配置详解

2.3.1 schema.xml

schema标签
	<schema name="DB01" checkSQLschema="true" sqlMaxLimit="100">
		<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
	</schema>

schema 标签用于定义 MyCat实例中的逻辑库 , 一个MyCat实例中, 可以有多个逻辑库 , 可以通过 schema 标签来划分不同的逻辑库。MyCat中的逻辑库的概念,等同于MySQL中的database概念, 需要操作某个逻辑库下的表时, 也需要切换逻辑库(use xxx)。

核心属性:

  • name:指定自定义的逻辑库库名

  • checkSQLschema:在SQL语句操作时指定了数据库名称,执行时是否自动去除;true:自动去除,false:不自动去除

  • sqlMaxLimit:如果未指定limit进行查询,列表查询模式查询多少条记录

table 标签定义了MyCat中逻辑库schema下的逻辑表 , 所有需要拆分的表都需要在table标签中定义 。

核心属性:

  • name:定义逻辑表表名,在该逻辑库下唯一

  • dataNode:定义逻辑表所属的dataNode,该属性需要与dataNode标签中name对应;多个dataNode逗号分隔

  • rule:分片规则的名字,分片规则名字是在rule.xml中定义的

  • primaryKey:逻辑表对应真实表的主键

  • type:逻辑表的类型,目前逻辑表只有全局表和普通表,如果未配置,就是普通表;全局表,配置为 global

datanode标签
<dataNode name="dn1" dataHost="dhost1" database="db01" />
<dataNode name="dn2" dataHost="dhost2" database="db01" />
<dataNode name="dn3" dataHost="dhost3" database="db01" />

核心属性:

  • name:定义数据节点名称

  • dataHost:数据库实例主机名称,引用自 dataHost 标签中name属性

  • database:定义分片所属数据库

datahost标签

该标签在MyCat逻辑库中作为底层标签存在, 直接定义了具体的数据库实例、读写分离、心跳语句。

	<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<writeHost host="master" url="jdbc:mysql://192.168.42.104:3301?useSSL=false" user="root"  password="123456">
		</writeHost>

	</dataHost>

核心属性:

  • name:唯一标识,供上层标签使用

  • maxCon/minCon:最大连接数/最小连接数

  • balance:负载均衡策略,取值 0,1,2,3(后面主从读写分离细讲)

  • writeType:写操作分发方式(0:写操作转发到第一个writeHost,第一个挂了,切换到第二个;1:写操作随机分发到配置的writeHost)

  • dbDriver:数据库驱动,支持 native、jdbc

2.3.2 rule.xml

rule.xml中定义所有拆分表的规则, 在使用过程中可以灵活的使用分片算法, 或者对同一个分片算法使用不同的参数, 它让分片过程可配置化。主要包含两类标签:tableRule、Function。

在这里插入图片描述

2.3.3 server.xml

在这里插入图片描述

在 privileges 下的schema标签中配置的dml属性配置的是逻辑库的权限。 在privileges的schema下的table标签的dml属性
中配置逻辑表的权限。

2.4 Mycat垂直分库

业务系统中, 涉及以下表结构 ,但是由于用户与订单每天都会产生大量的数据, 单台服务器的数据存储及处理能力是有限的, 可以对数据库表进行拆分, 原有的数据库表如下。

在这里插入图片描述

现在考虑将其进行垂直分库操作,将商品相关的表拆分到一个数据库服务器,订单表拆分的一个数据库
服务器,用户及省市区表拆分到一个服务器。最终结构如下:

在这里插入图片描述

2.4.1 配置

schema.xml

<schema name="SHOPPING" checkSQLschema="true" sqlMaxLimit="100">
		<table name="tb_goods_base" dataNode="dn1" primaryKey="id" />
		<table name="tb_goods_brand" dataNode="dn1" primaryKey="id" />
		<table name="tb_goods_cat" dataNode="dn1" primaryKey="id" />
		<table name="tb_goods_desc" dataNode="dn1" primaryKey="goods_id" />
		<table name="tb_goods_item" dataNode="dn1" primaryKey="id" />
		
		<table name="tb_order_item" dataNode="dn2" primaryKey="id" />
		<table name="tb_order_master" dataNode="dn2" primaryKey="order_id" />
		<table name="tb_order_pay_log" dataNode="dn2" primaryKey="out_trade_no" />
		
		<table name="tb_user" dataNode="dn3" primaryKey="id" />
		<table name="tb_user_address" dataNode="dn3" primaryKey="id" />
		<table name="tb_areas_provinces" dataNode="dn3" primaryKey="id"/>
		<table name="tb_areas_city" dataNode="dn3" primaryKey="id"/>
		<table name="tb_areas_region" dataNode="dn3" primaryKey="id"/>
	</schema>
	
	
	<dataNode name="dn1" dataHost="dhost1" database="shopping" />
	<dataNode name="dn2" dataHost="dhost2" database="shopping" />
	<dataNode name="dn3" dataHost="dhost3" database="shopping" />
	

	<dataHost name="dhost1" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<writeHost host="master" url="jdbc:mysql://192.168.42.104:3301?useSSL=false" user="root"  password="123456">

		</writeHost>

	</dataHost>
	
	
	<dataHost name="dhost2" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<writeHost host="master" url="jdbc:mysql://192.168.42.104:3302?useSSL=false" user="root"  password="123456">
		</writeHost>

	</dataHost>
	
	
	<dataHost name="dhost3" maxCon="1000" minCon="10" balance="0"
			  writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>

		<writeHost host="master" url="jdbc:mysql://192.168.42.104:3303?useSSL=false" user="root" password="123456">
		</writeHost>

	</dataHost>

server.xml

<user name="root" defaultAccount="true">
		<property name="password">123456</property>
		<property name="schemas">SHOPPING</property>
		
		<!-- 表级 DML 权限设置 -->
		<!-- 		
		<privileges check="false">
			<schema name="TESTDB" dml="0110" >
				<table name="tb01" dml="0000"></table>
				<table name="tb02" dml="1111"></table>
			</schema>
		</privileges>		
		 -->
	</user>

	<user name="user">
		<property name="password">user</property>
		<property name="schemas">SHOPPING</property>
		<property name="readOnly">true</property>
	</user>

2.4.2 测试

-- 执行建表语句
source /root/shopping-table.sql
source /root/shopping-insert.sql

在这里插入图片描述

-- 登录mycat 
-- 在MyCat的命令行中,当我们执行以下多表联查的SQL语句时,可以正常查询出数据。
select 
	ua.user_id, ua.contact, p.province, c.city, r.area , ua.address 
from
	tb_user_address ua ,tb_areas_city c , tb_areas_provinces p ,tb_areas_region r
where ua.province_id = p.provinceid and ua.city_id = c.cityid and ua.town_id =r.areaid ;


-- 查询每一笔订单及订单的收件地址信息(包含省、市、区)
SELECT 
	order_id , payment ,receiver, province , city , area 
FROM 
	tb_order_master o, tb_areas_provinces p , tb_areas_city c , tb_areas_region r 
WHERE
o.receiver_province = p.provinceid AND o.receiver_city = c.cityid AND o.receiver_region = r.areaid ;
-- 经过测试,我们看到,SQL语句执行报错。原因就是因为MyCat在执行该SQL语句时,需要往具体的数据库服务器中路由,而当前没有一个数据库服务器完全包含了订单以及省市区的表结构,造成SQL语句失败,报错

-- 对于这种情况应该如何解决呢???

对于省、市、区/县表tb_areas_provinces , tb_areas_city , tb_areas_region,是属于数据字典表,在多个业务模块中都可能会遇到,可以将其设置为全局表,利于业务操作。

修改schema.xml中的逻辑表的配置,修改 tb_areas_provinces、tb_areas_city、tb_areas_region 三个逻辑表,增加 type 属性,配置为global,就代表该表是全局表,就会在所涉及到的dataNode中创建给表。对于当前配置来说,也就意味着所有的节点中都有该表了。

<table name="tb_areas_provinces" dataNode="dn3" primaryKey="id" type="global"/>
<table name="tb_areas_city" dataNode="dn3" primaryKey="id" type="global"/>
<table name="tb_areas_region" dataNode="dn3" primaryKey="id" type="global"/>

在这里插入图片描述

2.5 Mycat分片规则

2.5.1 范围分片

根据指定的字段及其配置的范围与数据节点的对应情况, 来决定该数据属于哪一个分片。

在这里插入图片描述

schema.xml配置

schema.xml逻辑表配置:

<table name="TB_ORDER" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />

schema.xml数据节点配置:

<dataNode name="dn1" dataHost="dhost1" database="db01" />
<dataNode name="dn2" dataHost="dhost2" database="db01" />
<dataNode name="dn3" dataHost="dhost3" database="db01" />
rule.xml配置

rule.xml分片规则配置:

<tableRule name="auto-sharding-long">
    <rule>
        <!--columns标识将要分片的表字段-->
        <columns>id</columns>
        <algorithm>rang-long</algorithm>
    </rule>
</tableRule>


<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
    <!--mapFile对应的外部配置文件-->
    <property name="mapFile">autopartition-long.txt</property>
    <!--defaultNode枚举分片时,如果碰到不识别的枚举值, 就让它路由到默认节点 ; 如果没有默认值,碰到不识别的则报错 。-->
    <property name="defaultNode">0</property>
</function>

在rule.xml中配置分片规则时,关联了一个映射配置文件 autopartition-long.txt,该配置文件的配置如下:

# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2

含义:

  • 0-500万之间的值,存储在0号数据节点(数据节点的索引从0开始) ;

  • 500万-1000万之间的数据存储在1号数据节点 ;

  • 1000万-1500万的数据节点存储在2号节点 ;

该分片规则,主要是针对于数字类型的字段适用。 在MyCat的入门程序中,使用的就是该分片规则。

2.5.2 取模分片

根据指定的字段值与节点数量进行求模运算,根据运算结果, 来决定该数据属于哪一个分片。

在这里插入图片描述

schema.xml配置

schema.xml逻辑表配置:

<table name="tb_log" dataNode="dn4,dn5,dn6" primaryKey="id" rule="mod-long" />

schema.xml数据节点配置:

<dataNode name="dn4" dataHost="dhost1" database="yyds" />
<dataNode name="dn5" dataHost="dhost2" database="yyds" />
<dataNode name="dn6" dataHost="dhost3" database="yyds" />
rule.xml配置

rule.xml分片规则配置:

<tableRule name="mod-long">
    <rule>
        <columns>id</columns>
        <algorithm>mod-long</algorithm>
    </rule>
</tableRule>


<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
    <property name="count">3</property>
</function>

分片规则属性说明如下:

在这里插入图片描述

2.5.3 一致性hash分片

所谓一致性哈希,相同的哈希因子计算值总是被划分到相同的分区表中,不会因为分区节点的增加而改变原来数据的分区位置,有效的解决了分布式数据的拓容问题。

在这里插入图片描述

schema.xml配置

schema.xml逻辑表配置:

<!-- 一致性hash -->
<table name="tb_order" dataNode="dn4,dn5,dn6" rule="sharding-by-murmur" />

schema.xml数据节点配置:

<dataNode name="dn4" dataHost="dhost1" database="yyds" />
<dataNode name="dn5" dataHost="dhost2" database="yyds" />
<dataNode name="dn6" dataHost="dhost3" database="yyds" />
rule.xml配置

rule.xml分片规则配置:

<tableRule name="sharding-by-murmur">
    <rule>
        <columns>id</columns>
        <algorithm>murmur</algorithm>
    </rule>
</tableRule>


<function name="murmur" class="io.mycat.route.function.PartitionByMurmurHash">
    <property name="seed">0</property><!-- 默认是0 -->
    <property name="count">3</property>
    <property name="virtualBucketTimes">160</property>
</function>

2.5.4 枚举分片

通过在配置文件中配置可能的枚举值, 指定数据分布到不同数据节点上, 本规则适用于按照省份、性别、状态拆分数据等业务 。

在这里插入图片描述

schema.xml配置

schema.xml逻辑表配置:

<!-- 枚举 -->
<table name="tb_user" dataNode="dn4,dn5,dn6" rule="sharding-by-intfile-enumstatus"/>

schema.xml数据节点配置:

<dataNode name="dn4" dataHost="dhost1" database="yyds" />
<dataNode name="dn5" dataHost="dhost2" database="yyds" />
<dataNode name="dn6" dataHost="dhost3" database="yyds" />
rule.xml配置

rule.xml分片规则配置:

<tableRule name="sharding-by-intfile">
    <rule>
        <columns>sharding_id</columns>
        <algorithm>hash-int</algorithm>
    </rule>
</tableRule>

<!-- 多个枚举,可以自己增加 tableRule -->
<tableRule name="sharding-by-intfile-enumstatus">
    <rule>
        <columns>status</columns>
        <algorithm>hash-int</algorithm>
    </rule>
</tableRule>


<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
    <property name="defaultNode">2</property>
    <property name="mapFile">partition-hash-int.txt</property>
</function>

partition-hash-int.txt ,内容如下 :

1=0
2=1
3=2

2.5.5 应用指定分片

直接根据字符子串(必须是数字)计算分片号。

在这里插入图片描述

schema.xml配置

schema.xml逻辑表配置:

<!-- 应用指定算法 -->
<table name="tb_app" dataNode="dn4,dn5,dn6" rule="sharding-by-substring" />

schema.xml数据节点配置:

<dataNode name="dn4" dataHost="dhost1" database="yyds" />
<dataNode name="dn5" dataHost="dhost2" database="yyds" />
<dataNode name="dn6" dataHost="dhost3" database="yyds" />
rule.xml配置

rule.xml分片规则配置:

<tableRule name="sharding-by-substring">
    <rule>
        <columns>id</columns>
        <algorithm>sharding-by-substring</algorithm>
    </rule>
</tableRule>

<function name="sharding-by-substring" class="io.mycat.route.function.PartitionDirectBySubString">
    <property name="startIndex">0</property> <!-- zero-based -->
    <property name="size">2</property>
    <property name="partitionCount">3</property>
    <property name="defaultPartition">0</property>
</function>

在这里插入图片描述

2.5.6 固定分片hash算法

该算法类似于十进制的求模运算,但是为二进制的操作,例如,取 id 的二进制低 10 位 与1111111111 进行位 & 运算,位与运算最小值为 0000000000,最大值为1111111111,转换为十进制,也就是位于0-1023之间。

特点:

  • 如果是求模,连续的值,分别分配到各个不同的分片;但是此算法会将连续的值可能分配到相同的分片,降低事务处理的难度。

  • 可以均匀分配,也可以非均匀分配。

  • 分片字段必须为数字类型。

在这里插入图片描述

schema.xml配置

schema.xml逻辑表配置:

<!-- 固定分片hash算法 -->
<table name="tb_longhash" dataNode="dn4,dn5,dn6" rule="sharding-by-long-hash" />

schema.xml数据节点配置:

<dataNode name="dn4" dataHost="dhost1" database="yyds" />
<dataNode name="dn5" dataHost="dhost2" database="yyds" />
<dataNode name="dn6" dataHost="dhost3" database="yyds" />
rule.xml配置

rule.xml分片规则配置:

<tableRule name="sharding-by-long-hash">
    <rule>
        <columns>id</columns>
        <algorithm>sharding-by-long-hash</algorithm>
    </rule>
</tableRule>


<!-- 分片总长度为1024,count与length数组长度必须一致; -->
<function name="sharding-by-long-hash" class="io.mycat.route.function.PartitionByLong">
    <property name="partitionCount">2,1</property>
    <property name="partitionLength">256,512</property>
</function>

注意:

1) 分片长度 : 默认最大2^10 , 为 1024 ;
2) count, length的数组长度必须是一致的 ;
以上分为三个分区:0-255,256-511,512-1023

在这里插入图片描述

2.5.7 字符串hash解析算法分片

截取字符串中的指定位置的子字符串, 进行hash算法, 算出分片。

在这里插入图片描述

schema.xml配置

schema.xml逻辑表配置:

<!-- 字符串hash解析算法 -->
<table name="tb_strhash" dataNode="dn4,dn5" rule="sharding-by-stringhash" />

schema.xml数据节点配置:

<dataNode name="dn4" dataHost="dhost1" database="yyds" />
<dataNode name="dn5" dataHost="dhost2" database="yyds" />
rule.xml配置

rule.xml分片规则配置:

<tableRule name="sharding-by-stringhash">
    <rule>
        <columns>name</columns>
        <algorithm>sharding-by-stringhash</algorithm>
    </rule>
</tableRule>


<function name="sharding-by-stringhash" class="io.mycat.route.function.PartitionByString">
    <property name="partitionLength">512</property> <!-- zero-based -->
    <property name="partitionCount">2</property>
    <property name="hashSlice">0:2</property>
</function>

说明

在这里插入图片描述

在这里插入图片描述

2.5.8 按天分片

按照日期及对应的时间周期来分片。

在这里插入图片描述

schema.xml配置

schema.xml逻辑表配置:

<!-- 按天分片 -->
<table name="tb_datepart" dataNode="dn4,dn5,dn6" rule="sharding-by-date" />

schema.xml数据节点配置:

<dataNode name="dn4" dataHost="dhost1" database="yyds" />
<dataNode name="dn5" dataHost="dhost2" database="yyds" />
<dataNode name="dn6" dataHost="dhost3" database="yyds" />
rule.xml配置

rule.xml分片规则配置:

<tableRule name="sharding-by-date">
    <rule>
        <columns>create_time</columns>
        <algorithm>sharding-by-date</algorithm>
    </rule>
</tableRule>


<function name="sharding-by-date" class="io.mycat.route.function.PartitionByDate">
    <property name="dateFormat">yyyy-MM-dd</property>
    <property name="sBeginDate">2022-01-01</property>
    <property name="sEndDate">2022-01-30</property>
    <property name="sPartionDay">10</property>
</function>
<!--
从开始时间开始,每10天为一个分片,到达结束时间之后,会重复开始分片插入配置表的 dataNode 的分片,必须和分片规则数量一致,例如 2022-01-01 到 2022-12-31 ,每10天一个分片,一共需要37个分片。
-->

在这里插入图片描述

2.5.9 自然月分片

使用场景为按照月份来分片, 每个自然月为一个分片。

在这里插入图片描述

schema.xml配置

schema.xml逻辑表配置:

<!-- 按自然月分片 -->
<table name="tb_monthpart" dataNode="dn4,dn5,dn6" rule="sharding-by-month" />

schema.xml数据节点配置:

<dataNode name="dn4" dataHost="dhost1" database="yyds" />
<dataNode name="dn5" dataHost="dhost2" database="yyds" />
<dataNode name="dn6" dataHost="dhost3" database="yyds" />
rule.xml配置

rule.xml分片规则配置:

<tableRule name="sharding-by-month">
    <rule>
        <columns>create_time</columns>
        <algorithm>partbymonth</algorithm>
    </rule>
</tableRule>


<!--
从开始时间开始,一个月为一个分片,到达结束时间之后,会重复开始分片插入配置表的 dataNode 的分片,必须和分片规则数量一致,例如 2022-01-01 到 2022-12-31 ,一共需要12个分片。
-->

<function name="partbymonth" class="io.mycat.route.function.PartitionByMonth">
    <property name="dateFormat">yyyy-MM-dd</property>
    <property name="sBeginDate">2022-01-01</property>
    <property name="sEndDate">2022-03-31</property>
</function>

2.6 Mycat原理及监控

2.6.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 等。

Mycat默认开通2个端口,可以在server.xml中进行修改。

  • 8066 数据访问端口,即进行 DML 和 DDL 操作。
  • 9066 数据库管理端口,即 mycat 服务管理控制功能,用于管理mycat的整个集群状态
# 连接MyCat的管理控制台
mysql -h 192.168.42.104 -p 9066 -uroot -p123456

show @@help           查看Mycat管理工具帮助文档
show @@version        查看Mycat的版本
reload @@config       重新加载Mycat的配置文件
show @@datasource     查看Mycat的数据源信息
show @@datanode       查看MyCat现有的分片节点信息
show @@threadpool     查看Mycat的线程池信息
show @@sql            查看执行的SQL
show @@sql.sum        查看执行的SQL统计

2.6.2 Mycat监控

Mycat-web(Mycat-eye)是对mycat-server提供监控服务,功能不局限于对mycat-server使用。他通过JDBC连接对Mycat、Mysql监控,监控远程服务器(目前仅限于linux系统)的cpu、内存、网络、磁盘。Mycat-eye运行过程中需要依赖zookeeper,因此需要先安装zookeeper。

安装和监控可以参考: https://blog.51cto.com/u_15790101/5717943

3、利用Mycat实现读写分离

读写分离,简单地说是把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作,从数据库提供读操作,这样能有效地减轻单台数据库的压力。

在这里插入图片描述

3.1 Mycat实现一主一从读写分离

主从复制的搭建,可以参考:容器虚拟化技术Docker(二)mysql主从配置案例、redis集群搭建及扩容、缩容案例详解

MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件datahost标签的balance属性控制。

3.1.1 schema.xml配置

schema.xml逻辑表配置:

<!-- 配置逻辑库 -->
<schema name="DB02" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7">
</schema>


<dataNode name="dn7" dataHost="dhost7" database="db02" />



<!--
writeHost代表的是写操作对应的数据库,readHost代表的是读操作对应的数据库。 所以我们要想实现读写分离,就得配置writeHost关联的是主库,readHost关联的是从库。
而仅仅配置好了writeHost以及readHost还不能完成读写分离,还需要配置一个非常重要的负责均衡的参数 balance,几个参数值的含义如下图
所以,在一主一从模式的读写分离中,balance配置1或3都是可以完成读写分离的。
-->
<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.42.104:3307" user="root" password="123456" >
        <readHost host="slave1" url="jdbc:mysql://192.168.42.104:3308" user="root" password="123456" />
    </writeHost>
</dataHost>

在这里插入图片描述

3.1.2 server.xml 配置

配置root用户可以访问DB02逻辑库。

<user name="root" defaultAccount="true">
    <property name="password">123456</property>
    <property name="schemas">DB02</property>
</user>

注意:

当主节点Master宕机之后,业务系统就只能够读,而不能写入数据了。

3.2 Mycat实现双主双从读写分离

一个主机 Master1 用于处理所有写请求,它的从机 Slave1 和另一台主机 Master2 还有它的从机 Slave2 负责所有读请求。当 Master1 主机宕机后,Master2 主机负责写请求,Master1 、Master2 互为备机。

在这里插入图片描述

搭建过程比较麻烦,直接给出Mycat的配置。

<!--配置逻辑库-->
<schema name="DB03" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7">
</schema>


<!--配置数据节点-->
<dataNode name="dn7" dataHost="dhost7" database="db03" />


<!--配置节点主机-->
<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" user="root" password="1234" >
        <readHost host="slave1" url="jdbc:mysql://192.168.200.212:3306" user="root" password="1234" />
    </writeHost>
    
    <writeHost host="master2" url="jdbc:mysql://192.168.200.213:3306" user="root" password="1234" >
        <readHost host="slave2" url="jdbc:mysql://192.168.200.214:3306" user="root" password="123456" />
    </writeHost>
</dataHost>
# 相关参数解释
balance="1"
	代表全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且 M1 与 M2 互为主备),正常情况下,M2,S1,S2 都参与 select 语句的负载均衡 ;


writeType
	0 : 写操作都转发到第1台writeHost, writeHost1挂了, 会切换到writeHost2上;
	1 : 所有的写操作都随机地发送到配置的writeHost上 ;
	
switchType
	-1 : 不自动切换
	1 : 自动切换
  • 8
    点赞
  • 41
    收藏
    觉得还不错? 一键收藏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值