• MySQL之双主双从读写分离

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

    准备

    我们需要准备 4 台服务器，具体的服务器及软件安装情况如下：
    编号
    IP
    预装软件
    角色
    1
    192.168.2.3
    MyCat 、 MySQL
    MyCat 中间件服务器、M1
    2
    192.168.2.4
    MySQL
    S1
    3
    192.168.2.5
    MySQL
    M2
    4
    192.168.2.6
    MySQL
    S2
    关闭以上所有服务器的防火墙：
    systemctl stop firewalld
    systemctl disable firewalld

    搭建双主从

    主库配置

    1.Master1(192.168.2.3) 

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

    1. #mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，默认为1
    2. server-id=1
    3. #指定同步的数据库
    4. binlog-do-db=db01
    5. binlog-do-db=db02
    6. binlog-do-db=db03
    7. # 在作为从数据库的时候，有写入操作也要更新二进制日志文件
    8. log-slave-updates
    重启 MySQL 服务器 
    systemctl restart mysqld
    创建账户并授权 
    1. #创建mytest用户，并设置密码，该用户可在任意主机连接该MySQL服务
    2. CREATE USER 'mytest'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
    3. #为 'mytest'@'%' 用户分配主从复制权限
    4. GRANT REPLICATION SLAVE ON *.* TO 'mytest'@'%';
    通过指令，查看两台主库的二进制日志坐标 
    show master status ;

    2.Master2(192.168.2.5)

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

    1. #mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，默认为1
    2. server-id=3
    3. #指定同步的数据库
    4. binlog-do-db=db01
    5. binlog-do-db=db02
    6. binlog-do-db=db03
    7. # 在作为从数据库的时候，有写入操作也要更新二进制日志文件
    8. log-slave-updates
    重启 MySQL 服务器 
    systemctl restart mysqld
    创建账户并授权 
    1. #创建mytest用户，并设置密码，该用户可在任意主机连接该MySQL服务
    2. CREATE USER 'mytest'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
    3. #为 'mytest'@'%' 用户分配主从复制权限
    4. GRANT REPLICATION SLAVE ON *.* TO 'mytest'@'%';
    通过指令，查看两台主库的二进制日志坐标 
    show master status ;

    从库配置

    1.Slave1(192.168.2.4)

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

    1. #mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 232-1，默认为1
    2. server-id=2
    重新启动 MySQL 服务器 
    systemctl restart mysqld
    创建账户并授权 
    1. #创建mytest用户，并设置密码，该用户可在任意主机连接该MySQL服务
    2. CREATE USER 'mytest'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
    3. #为 'mytest'@'%' 用户分配主从复制权限
    4. GRANT REPLICATION SLAVE ON *.* TO 'mytest'@'%';

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

    show master status ;

    2.Slave2(192.168.2.6)

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

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

     重新启动MySQL服务器 

    systemctl restart mysqld

    从库关联主库

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

    在 slave1(192.168.2.4)上执行 

    1. CHANGE MASTER TO MASTER_HOST='192.168.2.3', MASTER_USER='mytest',
    2. MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000010',
    3. MASTER_LOG_POS=157;
    在 slave2(192.168.2.6) 上执行 
    1. CHANGE MASTER TO MASTER_HOST='192.168.2.5', MASTER_USER='mytest',
    2. MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000005',
    3. MASTER_LOG_POS=585;
    启动两台从库主从复制，查看从库状态 
    1. start slave;
    2. show slave status \G;

     

    两台主库相互复制

     在 Master1(192.168.2.3)上执行 

    1. CHANGE MASTER TO MASTER_HOST='192.168.2.5', MASTER_USER='mytest',
    2. MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000005',
    3. MASTER_LOG_POS=585;
    在 Master2(192.168.2.5) 上执行 
    1. CHANGE MASTER TO MASTER_HOST='192.168.2.3', MASTER_USER='mytest',
    2. MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000010',
    3. MASTER_LOG_POS=453;
    启动两台从库主从复制，查看从库状态 
    1. start slave;
    2. show slave status \G;

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

    测试

    分别在两台主库 Master1 、 Master2 上执行 DDL 、 DML 语句，查看涉及到的数据库服务器的数据同步情况。 
    1. create database db01;
    2. use db01;
    3. create table tb_user(
    4. id int(11) not null primary key ,
    5. name varchar(50) not null,
    6. sex varchar(1)
    7. )engine=innodb default charset=utf8mb4;
    8.  
    9. insert into tb_user(id,name,sex) values(1,'Tom','1');
    10. insert into tb_user(id,name,sex) values(2,'Trigger','0');
    11. insert into tb_user(id,name,sex) values(3,'Dawn','1');
    12. insert into tb_user(id,name,sex) values(4,'Jack Ma','1');
    13. insert into tb_user(id,name,sex) values(5,'Coco','0');
    14. insert into tb_user(id,name,sex) values(6,'Jerry','1');
    • 在Master1中执行DML、DDL操作，看看数据是否可以同步到另外的三台数据库中。
    • 在Master2中执行DML、DDL操作，看看数据是否可以同步到另外的三台数据库中。

    双主双从读写分离 

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

    配置

    MyCat 控制后台数据库的读写分离和负载均衡由 schema.xml 文件 datahost 标签的 balance 属性控制，通过writeType 及 switchType 来完成失败自动切换的。

    schema.xml

    配置逻辑库： 
    1. <schema name="TEST_RW2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7">
    2. </schema>
    配置数据节点： 
    <dataNode name="dn7" dataHost="dhost7" database="db01" />
    配置节点主机： 
    1. <dataHost name="dhost7" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
    2. 		<heartbeat>select user()</heartbeat>
    3. 		<writeHost host="master1" url="jdbc:mysql://192.168.2.3:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="newPwd520@" >
    4. 			<readHost host="slave1" url="jdbc:mysql://192.168.2.4:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="newPwd520@" />
    5. 		</writeHost>
    6. 		<writeHost host="master2" url="jdbc:mysql://192.168.2.5:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="newPwd520@" >
    7. 			<readHost host="slave2" url="jdbc:mysql://192.168.2.6:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="newPwd520@" />
    8. 		</writeHost>
    9. 	</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 : 自动切换

    user.xml

    配置 root 用户也可以访问到逻辑库 TEST _RW2 。 
    1. <user name="root" defaultAccount="true">
    2. <property name="password">123456</property>
    3. <property name="schemas">TEST_RW2</property>
    4. <!-- 表级 DML 权限设置 -->
    5. <!--
    6. <privileges check="true">
    7. <schema name="DB01" dml="0110" >
    8. <table name="TB_ORDER" dml="1110"></table>
    9. </schema>
    10. </privileges>
    11. -->
    12. </user>

    重启MyCat

    bin/mycat restart

    测试

    登录 MyCat ，测试查询及更新操作，判定是否能够进行读写分离，以及读写分离的策略是否正确。
    当主库挂掉一个之后，是否能够自动切换。
    先在mycat插入数据 
    insert into tb_user(id,name,sex) values(4,'Jack Ma','1');

    插入数据成功

    在全部mysql也插入数据成功

     

    然后停掉master1

    systemctl stop mysqld

     再次在mycat插入数据

    insert into tb_user(id,name,sex) values(5,'Coco','0');

    还是成功

    除了master，其他mysql也插入数据成功

  • 相关阅读:
    学习笔记 Cloud 代码
    基于石墨烯的光电探测传感器研究
    进程间通信-信号概述
    C语言笔记
    【STL】用哈希表（桶）封装出unordered_set和unordered_map
    linux 安装 Anaconda3
    492.构造矩形
    【Azure K8S | AKS】在不丢失文件/不影响POD运行的情况下增加PVC的大小
    施耐德AV71变频器和S7300PLC的DP通讯
    信息系统项目管理师必背核心考点（二十七）关键路径法（CPM）
  • 原文地址：https://blog.csdn.net/qq_63431773/article/details/133815174