mysql学习（五）

4.测试

4.1启动

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

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

4.2测试

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

3.4 MyCat配置

1.schema.xml

1.1schema标签

核心属性：

• name：指定自定义的逻辑库库名
• checkSQLschema：在SQL语句操作时指定了数据库名称，执行时是否自动去除；true：自动去 除，false：不自动去除
• sqlMaxLimit：如果未指定limit进行查询，列表查询模式查询多少条记录

2). schema 中的 table 定义逻辑表

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

义 。

核心属性：

• name：定义逻辑表表名，在该逻辑库下唯一
• dataNode：定义逻辑表所属的dataNode，该属性需要与dataNode标签中name对应；多个dataNode逗号分隔
• rule：分片规则的名字，分片规则名字是在rule.xml中定义的
• primaryKey：逻辑表对应真实表的主键
• type：逻辑表的类型，目前逻辑表只有全局表和普通表，如果未配置，就是普通表；全局表，配 置为 global

1.2datanode标签

• name：定义数据节点名称
• dataHost：数据库实例主机名称，引用自 dataHost 标签中name属性
• database：定义分片所属数据库

1.3datahost标签

• name：唯一标识，供上层标签使用
• maxCon/minCon：最大连接数/最小连接数
• balance：负载均衡策略，取值 0,1,2,3
• writeType：写操作分发方式（0：写操作转发到第一个writeHost，第一个挂了，切换到第二 个；1：写操作随机分发到配置的writeHost）
• dbDriver：数据库驱动，支持 native、jdbc

2.rule.xml

3.server.xml

主要配置 MyCat 中的系统配置信息，对应的系统配置项及其含义，如下：

2). user 标签

配置 MyCat 中的用户、访问密码，以及用户针对于逻辑库、逻辑表的权限信息，具体的权限描述方式及 配置说明如下：

在测试权限操作时，我们只需要将 privileges 标签的注释放开。 在 privileges 下的 schema

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

中配置逻辑表的权限。

3.5 MyCat分片

1.垂直拆分

1.1场景

在业务系统中 , 涉及以下表结构 , 但是由于用户与订单每天都会产生大量的数据 , 单台服务器的数据

存储及处理能力是有限的 , 可以对数据库表进行拆分 , 原有的数据库表如下。

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

1.2准备

准备三台服务器，IP地址如图所示：

并且在 192.168.200.210 ， 192.168.200.213, 192.168.200.214 上面创建数据库

shopping 。

1.3配置

1). schema.xml

name = "SHOPPING" checkSQLschema = "true" sqlMaxLimit = "100" >

2). server.xml

1.4测试

1). 上传测试SQL脚本到服务器的/root目录

将表结构及对应的测试数据导入之后，可以检查一下各个数据库服务器中的表结构分布情况。 检查是 否和我们准备工作中规划的服务器一致。

3). 查询用户的收件人及收件人地址信息 ( 包含省、市、区 ) 。

在 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 ;

4). 查询每一笔订单及订单的收件地址信息 ( 包含省、市、区 ) 。

实现该需求对应的 SQL 语句如下：

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 ;

但是现在存在一个问题，订单相关的表结构是在 192.168.200.213 数据库服务器中，而省市区的数

据库表是在 192.168.200.214 数据库服务器中。那么在 MyCat 中执行是否可以成功呢？

经过测试，我们看到， SQL 语句执行报错。原因就是因为 MyCat 在执行该 SQL 语句时，需要往具体的数 据库服务器中路由，而当前没有一个数据库服务器完全包含了订单以及省市区的表结构，造成SQL 语句 失败，报错。

对于上述的这种现象，我们如何来解决呢？ 下面我们介绍的全局表，就可以轻松解决这个问题。

1.5全局表

3). 检查每一个数据库服务器中的表及数据分布，看到三个节点中都有这三张全局表

4). 然后再次执行上面的多表联查的 SQL 语句

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 ;

是可以正常执行成功的。

5). 当在 MyCat 中更新全局表的时候，我们可以看到，所有分片节点中的数据都发生了变化，每个节

点的全局表数据时刻保持一致。

2.水平拆分

2.1场景

2.2准备

准备三台服务器，具体的结构如下：

并且，在三台数据库服务器中分表创建一个数据库itcast。​​​​​​​

2.3配置

1). schema.xml

2.4测试

3.分片规则

3.1范围分片

3.2取模分片

schema.xml 数据节点配置：

name = "dn4" dataHost = "dhost1" database = "itcast" />

name = "dn5" dataHost = "dhost2" database = "itcast" />

name = "dn6" dataHost = "dhost3" database = "itcast" />

rule.xml 分片规则配置：

name = "mod-long" >

id

mod-long

name = "mod-long" class = "io.mycat.route.function.PartitionByMod" >

name = "count" > 3

分片规则属性说明如下：

该分片规则，主要是针对于数字类型的字段适用。 在前面水平拆分的演示中，我们选择的就是取模分 片。

3). 测试

配置完毕后，重新启动 MyCat ，然后在 mycat 的命令行中，执行如下 SQL 创建表、并插入数据，查看数 据分布情况。

3.3一致性hash分片

schema.xml 数据节点配置：

name = "dn4" dataHost = "dhost1" database = "itcast" />

name = "dn5" dataHost = "dhost2" database = "itcast" />

name = "dn6" dataHost = "dhost3" database = "itcast" />

rule.xml 中分片规则配置：

name = "sharding-by-murmur" >

id

murmur

name = "murmur" class = "io.mycat.route.function.PartitionByMurmurHash" >

name = "seed" > 0

name = "count" > 3

name = "virtualBucketTimes" > 160

分片规则属性含义：

3). 测试

3.4枚举分片

schema.xml 数据节点配置：

name = "dn4" dataHost = "dhost1" database = "itcast" />

name = "dn5" dataHost = "dhost2" database = "itcast" />

name = "dn6" dataHost = "dhost3" database = "itcast" />

rule.xml 中分片规则配置：

name = "sharding-by-intfile" >

sharding_id

hash-int

name = "sharding-by-intfile-enumstatus" >

status

hash-int

name = "hash-int" class = "io.mycat.route.function.PartitionByFileMap" >

name = "defaultNode" > 2

name = "mapFile" > partition-hash-int.txt

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

1 = 0

2 = 1

3 = 2

分片规则属性含义：

3). 测试

配置完毕后，重新启动 MyCat ，然后在 mycat 的命令行中，执行如下 SQL 创建表、并插入数据，查看数 据分布情况。

CREATE TABLE tb_user (

id bigint ( 20 ) NOT NULL COMMENT 'ID' ,

username varchar ( 200 ) DEFAULT NULL COMMENT ' 姓名 ' ,

status int ( 2 ) DEFAULT '1' COMMENT '1: 未启用 , 2: 已启用 , 3: 已关闭 ' ,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into tb_user (id,username ,status) values ( 1 , 'Tom' , 1 );

insert into tb_user (id,username ,status) values ( 2 , 'Cat' , 2 );

insert into tb_user (id,username ,status) values ( 3 , 'Rose' , 3 );

insert into tb_user (id,username ,status) values ( 4 , 'Coco' , 2 );

insert into tb_user (id,username ,status) values ( 5 , 'Lily' , 1 );

insert into tb_user (id,username ,status) values ( 6 , 'Tom' , 1 );

insert into tb_user (id,username ,status) values ( 7 , 'Cat' , 2 );

insert into tb_user (id,username ,status) values ( 8 , 'Rose' , 3 );

insert into tb_user (id,username ,status) values ( 9 , 'Coco' , 2 );

insert into tb_user (id,username ,status) values ( 10 , 'Lily' , 1 );

3.5应用指定算法

3.6固定分片hash算法

schema.xml 数据节点配置：

name = "dn4" dataHost = "dhost1" database = "itcast" />

name = "dn5" dataHost = "dhost2" database = "itcast" />

name = "dn6" dataHost = "dhost3" database = "itcast" />

rule.xml 中分片规则配置：

name = "sharding-by-long-hash" >

id

sharding-by-long-hash

name = "sharding-by-long-hash"

class = "io.mycat.route.function.PartitionByLong" >

name = "partitionCount" > 2,1

name = "partitionLength" > 256,512

分片规则属性含义：

约束 :

1). 分片长度 : 默认最大 2^10 , 为 1024 ;

2). count, length 的数组长度必须是一致的 ;

以上分为三个分区 :0-255,256-511,512-1023

示例说明 :

3). 测试

配置完毕后，重新启动 MyCat ，然后在 mycat 的命令行中，执行如下 SQL 创建表、并插入数据，查看数 据分布情况。

CREATE TABLE tb_longhash (

id int ( 11 ) NOT NULL COMMENT 'ID' ,

name varchar ( 200 ) DEFAULT NULL COMMENT ' 名称 ' ,

firstChar char ( 1 ) COMMENT ' 首字母 ' ,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into tb_longhash (id,name,firstChar) values ( 1 , ' 七匹狼 ' , 'Q' );

insert into tb_longhash (id,name,firstChar) values ( 2 , ' 八匹狼 ' , 'B' );

insert into tb_longhash (id,name,firstChar) values ( 3 , ' 九匹狼 ' , 'J' );

insert into tb_longhash (id,name,firstChar) values ( 4 , ' 十匹狼 ' , 'S' );

insert into tb_longhash (id,name,firstChar) values ( 5 , ' 六匹狼 ' , 'L' );

insert into tb_longhash (id,name,firstChar) values ( 6 , ' 五匹狼 ' , 'W' );

insert into tb_longhash (id,name,firstChar) values ( 7 , ' 四匹狼 ' , 'S' );

insert into tb_longhash (id,name,firstChar) values ( 8 , ' 三匹狼 ' , 'S' );

insert into tb_longhash (id,name,firstChar) values ( 9 , ' 两匹狼 ' , 'L' );

3.7字符串hash解析算法

schema.xml 数据节点配置：

name = "dn4" dataHost = "dhost1" database = "itcast" />

name = "dn5" dataHost = "dhost2" database = "itcast" />

rule.xml 中分片规则配置：

name = "sharding-by-stringhash" >

name

sharding-by-stringhash

name = "sharding-by-stringhash"

class = "io.mycat.route.function.PartitionByString" >

name = "partitionLength" > 512

name = "partitionCount" > 2

name = "hashSlice" > 0:2

分片规则属性含义：

3.8按天分片算法

schema.xml 数据节点配置：

name = "dn4" dataHost = "dhost1" database = "itcast" />

name = "dn5" dataHost = "dhost2" database = "itcast" />

name = "dn6" dataHost = "dhost3" database = "itcast" />

rule.xml 中分片规则配置：

name = "sharding-by-date" >

create_time

sharding-by-date

name = "sharding-by-date"

class = "io.mycat.route.function.PartitionByDate" >

name = "dateFormat" > yyyy-MM-dd

name = "sBeginDate" > 2022-01-01

name = "sEndDate" > 2022-01-30

name = "sPartionDay" > 10

分片规则属性含义：

3). 测试

配置完毕后，重新启动 MyCat ，然后在 mycat 的命令行中，执行如下 SQL 创建表、并插入数据，查看数 据分布情况。

create table tb_datepart(

id bigint not null comment 'ID' primary key,

name varchar ( 100 ) null comment ' 姓名 ' ,

create_time date null

);

insert into tb_datepart(id,name ,create_time) values ( 1 , 'Tom' , '2022-01-01' );

insert into tb_datepart(id,name ,create_time) values ( 2 , 'Cat' , '2022-01-10' );

insert into tb_datepart(id,name ,create_time) values ( 3 , 'Rose' , '2022-01-11' );

insert into tb_datepart(id,name ,create_time) values ( 4 , 'Coco' , '2022-01-20' );

insert into tb_datepart(id,name ,create_time) values ( 5 , 'Rose2' , '2022-01-21' );

insert into tb_datepart(id,name ,create_time) values ( 6 , 'Coco2' , '2022-01-30' );

insert into tb_datepart(id,name ,create_time) values ( 7 , 'Coco3' , '2022-01-31' );

3.9自然月分片

schema.xml 数据节点配置：

name = "dn4" dataHost = "dhost1" database = "itcast" />

name = "dn5" dataHost = "dhost2" database = "itcast" />

name = "dn6" dataHost = "dhost3" database = "itcast" />

rule.xml 中分片规则配置：

name = "sharding-by-month" >

create_time

partbymonth

name = "partbymonth" class = "io.mycat.route.function.PartitionByMonth" >

name = "dateFormat" > yyyy-MM-dd

name = "sBeginDate" > 2022-01-01

name = "sEndDate" > 2022-03-31

分片规则属性含义：

3). 测试

配置完毕后，重新启动 MyCat ，然后在 mycat 的命令行中，执行如下 SQL 创建表、并插入数据，查看数 据分布情况。

create table tb_monthpart(

id bigint not null comment 'ID' primary key,

name varchar ( 100 ) null comment ' 姓名 ' ,

create_time date null

);

insert into tb_monthpart(id,name ,create_time) values ( 1 , 'Tom' , '2022-01-01' );

insert into tb_monthpart(id,name ,create_time) values ( 2 , 'Cat' , '2022-01-10' );

insert into tb_monthpart(id,name ,create_time) values ( 3 , 'Rose' , '2022-01-31' );

insert into tb_monthpart(id,name ,create_time) values ( 4 , 'Coco' , '2022-02-20' );

insert into tb_monthpart(id,name ,create_time) values ( 5 , 'Rose2' , '2022-02-25' );

insert into tb_monthpart(id,name ,create_time) values ( 6 , 'Coco2' , '2022-03-10' );

insert into tb_monthpart(id,name ,create_time) values ( 7 , 'Coco3' , '2022-03-31' );

insert into tb_monthpart(id,name ,create_time) values ( 8 , 'Coco4' , '2022-04-10' );

insert into tb_monthpart(id,name ,create_time) values ( 9 , 'Coco5' , '2022-04-30' );

3.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

3.1介绍

Mycat-web(Mycat-eye) 是对 mycat-server 提供监控服务，功能不局限于对 mycat-server 使

用。他通过 JDBC 连接对 Mycat 、 Mysql 监控，监控远程服务器 ( 目前仅限于 linux 系统 ) 的 cpu 、内

存、网络、磁盘。

Mycat-eye 运行过程中需要依赖 zookeeper ，因此需要先安装 zookeeper 。

3.2安装

1). zookeeper 安装

2). Mycat-web 安装

3.3访问

http://192.168.200.210:8082/mycat

3.4配置

1). 开启MyCat的实时统计功能(server.xml)

name="useSqlStat">1

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

3.5测试

配置好了之后，我们可以通过 MyCat 执行一系列的增删改查的测试，然后过一段时间之后，打开

mycat-eye 的管理界面，查看 mycat-eye 监控到的数据信息。

A. 性能监控

B. 物理节点

C. SQL 统计

D. SQL 表分析

E. SQL 监控

F. 高频 SQL

四、读写分离

4.1介绍

4.2一主一从

1.原理

MySQL的主从复制，是基于二进制日志（binlog）实现的。

2.准备

4.3一主一从读写分离

1.schema.xml配置

上述配置的具体关联对应情况如下：

2.server.xml配置

配置root用户可以访问SHOPPING、ITCAST 以及 ITCAST_RW逻辑库。

3.测试

然后观察，在执行增删改操作时，对应的主库及从库的数据变化。 在执行查询操作时，检查主库及从 库对应的数据变化。

在测试中，我们可以发现当主节点 Master 宕机之后，业务系统就只能够读，而不能写入数据了。 

那如何解决这个问题呢？这个时候我们就得通过另外一种主从复制结构来解决了，也就是我们接下来讲 解的双主双从。

4.4双主双从

1.介绍

2.准备

我们需要准备5台服务器，具体的服务器及软件安装情况如下：

3.搭建

3.1主库配置

1). Master1(192.168.200.211)

A. 修改配置文件 /etc/my.cnf

B. 重启MySQL服务器

systemctl restart mysqld

通过指令，查看两台主库的二进制日志坐标

show master status ;

2). Master2(192.168.200.213)

A. 修改配置文件 /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

B. 重启MySQL服务器

systemctl restart mysqld

C. 创建账户并授权

# 创建 itcast 用户，并设置密码，该用户可在任意主机连接该 MySQL 服务

CREATE USER 'itcast' @ '%' IDENTIFIED WITH mysql_native_password BY 'Root@123456'

;

# 为 'itcast' @ '%' 用户分配主从复制权限

GRANT REPLICATION SLAVE ON *.* TO 'itcast' @ '%' ;

通过指令，查看两台主库的二进制日志坐标

show master status ;

3.2从库配置

1). Slave1(192.168.200.212)

A. 修改配置文件 /etc/my.cnf

B. 重启MySQL服务器

systemctl restart mysqld

2). Slave2(192.168.200.214)

A. 修改配置文件 /etc/my.cnf

B. 重启MySQL服务器

systemctl restart mysqld

3.3从库关联主库

1). 两台从库配置关联的主库

需要注意slave1对应的是master1，slave2对应的是master2。

B. 在 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 ;

C. 启动两台从库主从复制，查看从库状态

start slave;

show slave status \G;

2). 两台主库相互复制

Master2 复制 Master1 ， Master1 复制 Master2 。

A. 在 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 ;

B. 在 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 ;

C. 启动两台从库主从复制，查看从库状态

start slave;

show slave status \G;

经过上述的三步配置之后，双主双从的复制结构就已经搭建完成了。 接下来，我们可以来测试验证一 下。

4.测试

4.5双主双从读写分离

1.配置

配置节点主机：

name = "dhost7" maxCon = "1000" minCon = "10" balance = "1" writeType = "0"

dbType = "mysql" dbDriver = "jdbc" switchType = "1" slaveThreshold = "100" >

select user()

host = "master1" url = "jdbc:mysql://192.168.200.211:3306?

useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"

user = "root" password = "1234" >

host = "slave1" url = "jdbc:mysql://192.168.200.212:3306?

useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"

user = "root" password = "1234" />

host = "master2" url = "jdbc:mysql://192.168.200.213:3306?

useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"

user = "root" password = "1234" >

host = "slave2" url = "jdbc:mysql://192.168.200.214:3306?

useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8"

user = "root" password = "1234" />

具体的对应情况如下：

​​​​​​​

属性说明：

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 : 自动切换

2). user.xml

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

name = "root" defaultAccount = "true" >

name = "password" > 123456

name = "schemas" > SHOPPING,ITCAST,ITCAST_RW2

2.测试