MySQL入门学习Step2_MyCat分片方式补充

已于 2024-08-02 20:35:00 修改
阅读量20 收藏
点赞数
文章标签： mysql 学习 数据库
于 2023-03-26 19:43:21 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44297248/article/details/129783144
版权
枚举分片

实验失败（ERROR 1064 (HY000): can’t find any valid datanode :TB_USER -> STATUS -> 2）

  1. 介绍

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

  2. 配置


    schema.xml逻辑表配置：

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

    schema.xml数据节点配置：

    <dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

    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

    分片规则属性说明如下：

    属性描述
    columns标识将要分片的表字段
    algorithm指定分片函数与function的对应关系
    class指定该分片算法对应的类
    mapFile对应的外部配置文件
    type默认值为0 ; 0 表示Integer , 1 表示String
    defaultNode默认节点 ; 小于0 标识不设置默认节点 , 大于等于0代表设置默认节点 ;默认节点的使用:枚举分片时,如果碰到不识别的枚举值, 就让它路由到默认节点 ; 如果没有默认值,碰到不识别的则报错 。

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

    补充：模运算，大模小，结果为大除于小的余数；小模大，直接返回小值

  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);
应用指定算法

  1. 介绍

    运行阶段由应用自主决定路由到那个分片 , 直接根据字符子串（必须是数字）计算分片号。在这里插入图片描述

  2. 配置


    schema.xml逻辑表配置：

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

    schema.xml数据节点配置：

    <dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

    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>

    分片规则属性说明如下：

    属性描述
    columns标识将要分片的表字段
    algorithm指定分片函数与function的对应关系
    class指定该分片算法对应的类
    startIndex字符子串起始索引
    size字符长度
    partitionCount分区(分片)数量
    defaultPartition默认分片(在分片数量定义时, 字符标示的分片编号不在分片数量内时,使用默认分片)

    示例说明 :
    id=05-100000002 , 在此配置中代表根据id中从 startIndex=0，开始，截取siz=2位数字即05，05就是获取的分区，如果没找到对应的分片则默认分配到defaultPartition 。

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

    CREATE TABLE tb_app (
  id varchar(10) NOT NULL COMMENT 'ID',
  name varchar(200) DEFAULT NULL COMMENT '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into tb_app (id,name) values('0000001','Testx00001');
insert into tb_app (id,name) values('0100001','Test100001');
insert into tb_app (id,name) values('0100002','Test200001');
insert into tb_app (id,name) values('0200001','Test300001');
insert into tb_app (id,name) values('0200002','TesT400001');
固定分片hash算法

  1. 介绍

    该算法类似于十进制的求模运算，但是为二进制的操作，例如，取 id 的二进制低 10 位 与1111111111 进行位 & 运算，位与运算最小值为 0000000000，最大值为1111111111，转换为十进制，也就是位于0-1023之间。在这里插入图片描述
    特点：

    • 如果是求模，连续的值，分别分配到各个不同的分片；但是此算法会将连续的值可能分配到相同的分片，降低事务处理的难度。
    • 可以均匀分配，也可以非均匀分配。
    • 分片字段必须为数字类型。

  2. 配置

    在这里插入图片描述
    schema.xml逻辑表配置：

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

    schema.xml数据节点配置：

    <dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

    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>

    分片规则属性说明如下：

    属性描述
    columns标识将要分片的表字段
    algorithm指定分片函数与function的对应关系
    class指定该分片算法对应的类
    partitionCount分片个数数组，2,1表示定义前两个节点和后一个节点
    partitionLength分片范围数组与partitionCount对应，
    256,512表示前两个节点分片长度为256，后一个节点分片长度为512

    约束 :

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

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

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

    515 ＞ 512 存放在第三个分组（序号为2）

  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');

#存储数据到第二个分组
insert into tb_longhash (id, name, firstChar) values(269,'很多匹狼','L');
字符串hash解析算法

  1. 介绍

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

  2. 配置

    在这里插入图片描述
    schema.xml逻辑表配置：

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

    schema.xml数据节点配置：

    <dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />

    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><!-- 截取0、1、2三个下标所指定的字符 -->
</function>

    分片规则属性说明如下：

    属性描述
    columns标识将要分片的表字段
    algorithm指定分片函数与function的对应关系
    class指定该分片算法对应的类
    partitionLengthhash求模基数 ; length*count=1024 (出于性能考虑)
    partitionCount分区(分片)数量
    hashSlicehash运算位 , 根据子字符串的hash运算 ; 0 代表 str.length(), -1 代表 str.length()-1 , 大于0只代表数字自身 ; 可以理解为substring（start，end），start为0则只表示0

    示例说明 :

    在这里插入图片描述

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

    create table tb_strhash(
    name varchar(20) primary key,
    content varchar(100)
)engine=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO tb_strhash (name,content) VALUES('T1001', UUID());
INSERT INTO tb_strhash (name,content) VALUES('ROSE', UUID());
INSERT INTO tb_strhash (name,content) VALUES('JERRY', UUID());
INSERT INTO tb_strhash (name,content) VALUES('CRISTINA', UUID());
INSERT INTO tb_strhash (name,content) VALUES('TOMCAT', UUID());
按天分片算法

  1. 介绍

    按照日期及对应的时间周期来分片。在这里插入图片描述

  2. 配置
    schema.xml逻辑表配置：

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

    schema.xml数据节点配置：

    <dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

    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个分片。
-->

    分片规则属性说明如下：

    属性描述
    columns标识将要分片的表字段
    algorithm指定分片函数与function的对应关系
    class指定该分片算法对应的类
    dateFormat日期格式
    sBeginDate开始日期
    sEndDate结束日期，如果配置了结束日期，则代码数据到达了这个日期的分片后，会重复从开始分片插入
    sPartionDay分区天数，默认值 10 ，从开始日期算起，每隔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');
    • 2022-01-30会被存放在第三个节点，2022-01-31的数据会被存放到第一个节点
自然月分片

  1. 介绍

    使用场景为按照月份来分片, 每个自然月为一个分片。在这里插入图片描述

  2. 配置
    schema.xml逻辑表配置：

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

    schema.xml数据节点配置：

    <dataNode name="dn4" dataHost="dhost1" database="itcast" />
<dataNode name="dn5" dataHost="dhost2" database="itcast" />
<dataNode name="dn6" dataHost="dhost3" database="itcast" />

    rule.xml分片规则配置：

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

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

    分片规则属性说明如下：

    属性描述
    columns标识将要分片的表字段
    algorithm指定分片函数与function的对应关系
    class指定该分片算法对应的类
    dateFormat日期格式
    sBeginDate开始日期
    sEndDate结束日期，如果配置了结束日期，则代码数据到达了这个日期的分片后，会重复从开始分片插入

  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');
    • 1、4月份的数据会被存放在第一个数据节点，3月份的数据会被存放在第三个数据节点

MyCat管理及监控

MyCat原理

在MyCat中，当执行一条SQL语句时，MyCat需要进行SQL解析、分片分析、路由分析、读写分离分析等操作，最终经过一系列的分析决定将当前的SQL语句到底路由到那几个(或哪一个)节点数据库，数据库将数据执行完毕后，如果有返回的结果，则将结果返回给MyCat，最终还需要在MyCat中进行结果合并、聚合处理、排序处理、分页处理等操作，最终再将结果返回给客户端。

  • 一般的：在上面的这个例子中，如果查询语句有指定关于status字段（也就是设置的分片字段）的查询条件，路由会规则指定到特定的数据节点
    • 该情况描述同样适用与status的多查询
  • 特别的：如果没有指定status字段的查询条件，路由会使下面的所有分片节点都接收到这条查询语句

而在MyCat的使用过程中，MyCat官方也提供了一个管理监控平台MyCat-Web（MyCat-eye）。Mycat-web 是 Mycat 可视化运维的管理和监控平台，弥补了 Mycat 在监控上的空白。帮 Mycat分担统计任务和配置管理任务。Mycat-web 引入了 ZooKeeper 作为配置中心，可以管理多个节点。Mycat-web 主要管理和监控 Mycat 的流量、连接、活动线程和内存等，具备 IP 白名单、邮件告警等模块，还可以统计 SQL 并分析慢 SQL 和高频 SQL 等。为优化 SQL 提供依据。

MyCat管理

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

  • 8066 数据访问端口，即进行 DML 和 DDL 操作。
  • 9066 数据库管理端口，即 mycat 服务管理控制功能，用于管理mycat的整个集群状态

连接MyCat的管理控制台：

mysql -h 192.168.200.210 -P 9066 -uroot -p12345
命令含义
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统计

MyCat-eye

Mycat-web(Mycat-eye)是对mycat-server提供监控服务，功能不局限于对mycat-server使用。他通过JDBC连接对Mycat、Mysql监控，监控远程服务器(目前仅限于linux系统)的cpu、内存、网络、磁盘。

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

安装详见相关的安装文档

补充：**JDBC的全称是Java数据库连接(Java Database connect)，它是一套用于执行SQL语句的Java API。应用程序可通过这套API连接到关系数据库，并使用SQL语句来完成对数据库中数据的查询、更新和删除等操作。**应用程序使用JDBC访问数据库的方式如下图所示。
img

访问

http://192.168.56.128:8082/mycat

配置
  1. 开启MyCat的实时统计功能(server.xml)
<property name="useSqlStat">1</property> <!-- 1为开启实时统计、0为关闭 -->
  1. 在Mycat监控界面配置服务地址在这里插入图片描述
测试

配置好了之后，我们可以通过MyCat执行一系列的增删改查的测试，然后过一段时间之后，打开mycat-eye的管理界面，查看mycat-eye监控到的数据信息。

  • 可以使用性能监控、物理节点、SQL统计、SQL表分析、SQL监控、高频SQL等功能

读写分离

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

通过MyCat即可轻易实现上述功能，不仅可以支持MySQL，也可以支持Oracle和SQL Server。

一主一从

原理

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

在这里插入图片描述

准备

主机角色用户名密码
192.168.200.211masterroot1234
192.168.200.212slaveroot1234

备注：主从复制的搭建，可以参考前面笔记中 主从复制 小结分析的步骤操作。

schema.xml配置

<!-- 配置逻辑库；逻辑表可以不指定，会自动加载所在数据节点所在的数据库，然后读取这个数据库中所有的表结构来作为逻辑表 -->
<schema name="ITCAST_RW" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7">
</schema>

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

<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&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
    user="root" password="1234" > 
        <readHost host="slave1" url="jdbc:mysql://192.168.200.212:3306?
        useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
        user="root" password="1234" />
    </writeHost>
</dataHost>

<!-- 实践完成的用例 -->
<schema name="ITCAST_RW" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7">
</schema>

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

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

    <writeHost host="master" url="jdbc:mysql://192.168.56.134:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root"
               password="123321">
        <readHost host="slave" url="jdbc:mysql://192.168.56.133:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root"
                  password="123321">

        </readHost>
    </writeHost>
</dataHost>

测试

配置完毕MyCat后，重新启动MyCat。

user> bin/mycat stop
user> bin/mycat start

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

观察的办法是，由于先去已经配置过主从复制，从库的数据不会同步到主库的数据中去，更改从库数据，可以看出在mycat中查询出来的为从库的数据，插入语句操作的是主库（因为插入后主库和从库都存在数据）

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

在这里插入图片描述

双主双从

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

在这里插入图片描述

准备

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

编号IP预装软件角色
1192.168.200.210MyCat、MySQL MyCat中间件服务器
2192.168.200.211MySQLM1
3192.168.200.212MySQLS1
4192.168.200.213MySQLM2
5192.168.200.214MySQLS2

M1、S1和M2、S2分别建立主从同步关系

#关闭以上所有服务器的防火墙：
user> systemctl stop firewalld
user> systemctl disable firewalld

搭建

主库配置

  1. Master1(192.168.200.211)
    在这里插入图片描述

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

      #mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，默认为1
server-id=1 #设备1；server-id=3 #设备3 按各自的编号设置server-id 
#指定同步的数据库
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
#在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates

    2. 重启MySQL服务器

      user> systemctl restart mysqld

    3. 创建账户并授权

      #创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
mysql> CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
#为 'itcast'@'%' 用户分配主从复制权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';

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

      mysql> show master status ;

  2. Master2(192.168.200.211)
    在这里插入图片描述

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

      #mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，默认为1
server-id=1 #设备1；server-id=3 #设备3 按各自的编号设置server-id 
#指定同步的数据库
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
#在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates

    2. 重启MySQL服务器

      user> systemctl restart mysqld

    3. 创建账户并授权

      #创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
mysql> CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
#为 'itcast'@'%' 用户分配主从复制权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';

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

      mysql> show master status;
从库配置

  1. Slave1(192.168.200.212)
    在这里插入图片描述

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

      #mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 232-1，默认为1
server-id=4

    2. 重启MySQL服务器

      user> systemctl restart mysqld

  2. Slave2(192.168.200.214)
    在这里插入图片描述

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

      #mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 232-1，默认为1
server-id=4

    2. 重启MySQL服务器

      user> systemctl restart mysqld
从库关联主库

  1. 两台从库配置关联的主库
    在这里插入图片描述

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

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

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

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

      start slave;
show slave status \G;

      在这里插入图片描述

  2. 两台主库相互复制
    在这里插入图片描述

    Master2 复制 Master1，Master1 复制 Master2。

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

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

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

      start slave;
show slave status \G;

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

测试

分别在两台主库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操作，看看数据是否可以同步到另外的三台数据库中。

完成了上述双主双从的结构搭建之后，接下来，我们再来看看如何完成这种双主双从的读写分离。

双主双从读写分离

配置

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.56.134:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123321" >
        <readHost host="slave1" url="jdbc:mysql://192.168.56.133:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123321" />
    </writeHost>

    <writeHost host="master2" url="jdbc:mysql://192.168.56.137:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123321" >
        <readHost host="slave2" url="jdbc:mysql://192.168.56.136:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123321" />
    </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 : 自动切换

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

    <user name="root" defaultAccount="true">
    <property name="password">123321</property>
    <property name="schemas">SHOPPING,ITCAST,ITCAST_RW2</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>

测试

登录MyCat，测试查询及更新操作，判定是否能够进行读写分离，以及读写分离的策略是否正确。

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

  • 观察的办法是，由于先去已经配置过主从复制，从库的数据不会同步到主库的数据中去，更改从库数据，可以看出在mycat中查询出来的为从库的数据，插入语句操作的是主库（因为插入后主库和从库都存在数据）

  • 当主库挂掉一个之后，是否能够自动切换。

MySQL主从复制是基于二进制日志binlog实现的。master、slave

master数据一旦发生变更，数据变更写入到二进制日志，

  • slave节点会去读取master节点的二进制日志，
    • 让后把读取过来的二进制日志写入到自身的中继日志，
    • 然后再通过另外一组线程SQLThread去读取中继日志中的数据，
    • 然后反应到自身的变化。

这么一来，master一旦有数据变更，就会同步到slave

weixin_44297248
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL入门学习_Step2
weixin_44297248的博客
03-26 40
✨架构 MySQL5.5 版本开始，默认使用InnoDB存储引擎，它擅长事务处理，具有崩溃恢复特性，在日常开发中使用非常广泛。下面是InnoDB架构图，左侧为内存结构，右侧为磁盘结构。 内存结构（4个部分） 在左侧的内存结构中，主要分为这么四大块儿： Buffer Pool、Change Buffer、Adaptive Hash Index、Log Buffer。 接下来介绍一下这四个部分 Buffer Pool InnoDB存储引擎基于磁盘文件存储，访问物理硬盘和在内存中进行访问，速度相差很大，为了
MyCat2测试实战 -- 王者归来的故事
roykingw的专栏
06-22 3201
​ 关于MyCat，不用做过多介绍，曾经大名鼎鼎的分库分表中间件。诞生于2013年，从MyCat1.6版本之后，陷入了一段时间的沉寂。从2021年11月低开始，重新推出新版本的MyCat2，官网地址：http://www.mycat.org.cn/ 。​ 重要特性一目了然，独立，是他最大的标签。然后在他的Git仓库中，对产品有一个简单的介绍： ​ 立足于Sharding分库分表。简单、快速是MyCat2对自己最直白的声明。他的定位其实是代表了一整套基于MySQL的分布式数据处理系统，可以让MySQL拥有堪比
mysql学习笔记
sober30的博客
02-24 529
数据类型类型类型举例整数浮点定点数DECIMAL位类型BIG日期时间类型文本字符串枚举类型ENUM集合类型SET二进制字符串类型json类型JSON对象、JSON数组空间数据类型单值类型：GEOMETRY、POINT、LINESTRING、POLYGON;集合类型：MULTIPOINT、MULTILINESTRING、MULTPOLYGON、GEOMETRYCOLLECTION数据类型属性mysql关键字含义NULL数据列可包含NULL值。
ShardingSphere学习（超详细）
hyzsuccess的博客
11-02 3510
Apache ShardingSphere是一款开源的分布式数据库中间件组成的生态圈。它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar（规划中）这3款相互独立的产品组成。 他们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、容器、云原生等各种多样化的应用场景。
ShardingShpere5分库分表
qq_40003391的博客
04-06 757
官网： https://shardingsphere.apache.org/index_zh.html文档： https://shardingsphere.apache.org/document/5.1.1/cn/overview/ ApacheShardingSphere 由JDBCProxy和Sidecar（规划中）这 3 款既能够独立部署，又支持混合 部署配合使用的产品组成。
MySQL集群干货
Blog of Stevenux
12-07 1173
MySQL集群一.MySQL 主从复制1.1 主从复制概念1.1.1 主从复制原理1.1.2 二进制日志类型1.1.3 主从复制所涉及的线程1.2 主从复制涉及的变量1.2.1 Master1.2.2 Slave1.2.3 Best Practices1.3 监控和管理复制1.4 检查数据一致性二.MySQL 读写分离2.1 Mycat 中间件代理2.1.1 Mycat 介绍2.1.2 不同的角度...
MySQL这一章就够了(二)
Dusttang的博客
09-03 1162
redo log redo log通常是重做日志(物理日志)，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。 redo log不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo 中。具体 的落盘策略可以进行配置 。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的未入磁盘数据进行持久化这一特性。RedoLog是为了实现事务的持久
ShardingSphere入门到精通
weixin_56018532的博客
09-18 4713
ShardingSphere入门到精通
分库分表技术之ShardingJDBC --- step3
qq_45441466的博客
09-30 1023
1. ShardingJDBC 1.1 分库分表方式回顾 1) 分库分表的目的就是将我们的单库的数据控制在合理范围内,从而提高数据库的性能. 垂直拆分( 按照结构分 ) 垂直分表: 将一张宽表(字段很多的表), 按照字段的访问频次进行拆分,就是按照表单结构进行 拆 垂直分库: 根据不同的业务,将表进行分类, 拆分到不同的数据库. 这些库可以部署在不同的服 务器,分摊访问压力 水平拆分( 按照数据行分 ) 水平分库: 将一张表的数据 ( 按照数据行) 分到多个不同的数据库.每个库的表结构相
Mycat复制与高可用集群_herself36w_mycat_MYSQL_
10-02
Mycat是基于MySQL协议开发的数据库中间件，它将多台MySQL服务器虚拟成一个大型的分布式数据库系统，实现了读写分离、分库分表等功能，为大数据量、高并发的业务场景提供了有效的解决方案。 二、Mycat复制 1. 数据...
mycat分片和mysql的分区_MyCat分库分表入门
weixin_29213525的博客
01-20 722
1、分区对业务透明，分区只不过把存放数据的文件分成了许多小块，例如mysql中的一张表对应三个文件.MYD,MYI,frm。根据一定的规则把数据文件(MYD)和索引文件(MYI)进行了分割，分区后的表呢，还是一张表。分区可以把表分到不同的硬盘上，但不能分配到不同服务器上。优点：数据不存在多个副本，不必进行数据复制，性能更高。缺点：分区策略必须经过充分考虑，避免多个分区之间的数据存在关联关系，每个分...
mysql mycat 路由规则_Mycat分片规则详解
weixin_42653691的博客
01-19 373
1、分片枚举通过在配置文件中配置可能的枚举 id，自己配置分片，本规则适用于特定的场景，比如有些业务需要按照省份或区县来做保存，而全国省份区县固定的，这类业务使用本条规则，配置如下：user_idhash-intpartition-hash-int.txt00配置说明标签属性说明columns标识将要分片的表字段algorithm分片函数mapFile标识配置文件名称type默认值为 0，0 表示...
mysql分片建表语句_Mycat 进阶
weixin_32515445的博客
01-19 1410
Mycat 高可用目前Mycat 没有实现对多Mycat 集群的支持，可以暂时使用HAProxy 来做负载思路：HAProxy 对Mycat 进行负载。Keepalived 实现VIP。Mycat 注解注解的作用当关联的数据不在同一个节点的时候，Mycat 是无法实现跨库join 的。举例：如果直接在150 插入主表数据，151 插入明细表数据，此时关联查询无法查询出来。-- 150 节点插入IN...
Java项目: 基于SpringBoot+mysql房产销售系统 (含源码+数据库+开题报告+答辩PPT+毕业论文)
weixin_43860634的博客
09-03 605
Java项目: 基于SpringBoot+mysql房产销售系统 (含源码+数据库+开题报告+答辩PPT+毕业论文)
策略规划：在MySQL中实现数据恢复的全面指南
2401_85339615的博客
09-04 1082
数据恢复是数据库管理中至关重要的一环，它确保在发生数据丢失或损坏的情况下，能够迅速且准确地恢复数据。在MySQL中，实现有效的数据恢复策略规划需要综合考虑备份策略、备份类型、存储管理、故障转移机制以及恢复流程。本文将深入探讨如何在MySQL中进行数据恢复的策略规划，包括策略制定、技术实现和最佳实践。
MySQL数据备份的版本控制：策略、实践与自动化
2401_85341950的博客
09-04 910
数据备份的版本控制是指对数据库备份进行管理，以便可以追踪、访问和恢复到数据库的特定历史状态。数据恢复：在数据丢失或损坏的情况下快速恢复数据。审计和合规性：满足法规要求，提供数据访问和变更的历史记录。开发和测试：为开发和测试环境提供数据的特定版本。
【重学 MySQL】十四、显示表结构
最新发布
CSDN全栈领域优秀博主，阿里云博客专家，工信部权威认证物联网技术开发工程师，曾30分钟高分通过C语言二级，两天手撸代码完成EasyX飞机大战，靠接软件私活一个月换苹果电脑
09-06 644
以上就是在MySQL中显示表结构的几种常用方法。DESCRIBE或DESC命令和命令提 供了快速查看表列信息的简便方式，而查询数据库则提供了更详细、更灵活的信息获取方式。命令则适用于需要查看或复制表完整结构的情况。
go解析YAML文件多文档
xsw164711368的博客
09-03 1265
本文介绍了YAML文档的特点，并用代码演示了GO语言对YAML文档的处理，特别是描述了当YAML文件中存在多个YAML文档时如何处理的方法。
Mycat入门与实战：分片原理及配置指南
1. **数据分片**: 分片是Mycat的核心特性之一，它将一个大表分成多个小表，分散到不同的数据库服务器（MySQL实例）上，从而实现水平扩展。分片可以分为垂直分片和水平分片。垂直分片是根据列来切分，将关联性较弱的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值