MySql集群(1)-读写分离

目录

1.读写分离原理

2.MySQL主从同步

3.一主多从配置

1.配置主节点

2.配置从节点

3.ShardingSphere-JDBC

---

1.读写分离原理

简单概括：创建多个数据库，每个数据库只做单一功能，分别“读”操作或者“写“操作，并且通过日志进行同步数据操作。

详细原理：

1.主库负责处理事务的增删改操作，从库负责查询操作。能够有效避免数据更新的行锁，使得整个系统的查询得到极大的改善。

2.读写分离是根据SQL语义的分析，将读操作和写操作分别路由至主库与从库。

3.通过一主多仆的配置配置方式，将查询请求均匀的分散到数据副本，能够近一步提升系统的处理能力。

4.使用多主多仆的方式，更能提高吞吐量和可用性，达到任何一个数据库宕机，物理磁盘损害也能正常运行。

2.MySQL主从同步

基本原理：

简单概述：slave会从master读取binlog来进行数据同步 两日志（Binary log二进制日志，Relaylog log中继日志），三线程。

详细概述：一般增删改的操作会在主结点，从结点读取数据。当主节点发生变化的时候会，会写到二进制日志中，在二进制日志发生变化的时候会有log dump thread（日志转出线程）监听到并转发到从设备salve（在读取binlog日志时候会对主节点的日志进行加锁）。此时从结点会有IO线程进行接受并写入中继日志，中继日志会存入sql的执行语句，当发生变化时候会有SQL 线程进行去读中继日志中的执行语句，并执行写入salve的数据库中。

原理图：

3.一主多从配置

前提：该配置主要为1主节点，两个从节点。配置方式为docker。

1.配置主节点

docker容器启动：

docker run -d \
-p 3306:3306 \
-v /llkj/mysql/master/conf:/etc/mysql/conf.d \
-v /llkj/mysql/master/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name llkj-mysql-master \
--restart=always \
mysql:8.0.29

修改配置文件：

#修改配置文件
vim /llkj/mysql/master/conf/my.cnf

#修改配置文件
[mysqld]
# 服务器唯一id，默认值1
server-id=1
# 设置日志格式，默认值ROW
binlog_format=STATEMENT
# 二进制日志名，默认binlog
# log-bin=binlog
# 设置需要复制的数据库，默认复制全部数据库
#binlog-do-db=mytestdb1
#binlog-do-db=mytestdb2
# 设置不需要复制的数据库
#binlog-ignore-db=mysql
#binlog-ignore-db=infomation_schema

#重启docker容器
docker restart llkj-mysql-master

binlog格式说明：

• binlog_format=STATEMENT：日志记录的是主机数据库的写指令，性能高，但是now()之类的函数以及获取系统参数的操作会出现主从数据不同步的问题。

• binlog_format=ROW（默认）：日志记录的是主机数据库的写后的数据，批量操作时性能较差，解决now()或者 user()或者 @@hostname 等操作在主从机器上不一致的问题。

• binlog_format=MIXED：是以上两种level的混合使用，有函数用ROW，没函数用STATEMENT，但是无法识别系统变量

等登录服务器中，修改默认密码校验模式(必须修改)

#进入容器：env LANG=C.UTF-8 避免容器中显示中文乱码
docker exec -it llkj-mysql-master env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p
#修改默认密码校验方式 ; 必须执行
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

ps：必须修改密码校验方式(包括下面的子节点)否则在使用ShardingSphere-JDBC的时候回导致配置文件失败连接不到数据库，报java.sql.SQLNonTransientConnectionException: Public Key Retrieval is not allowed

创建slave用户

-- 创建slave用户
create user 'llkj_slave'@'%';
-- 设置密码
alter user'llkj_slave'@'%' identified with mysql_native_password BY '123456';
-- 授予复制权限
grant replication slave on *.* to 'llkj_slave'@'%';
-- 刷新权限
flush privileges;
--查看master状态
show master status;

 binlog为上面流程的二进制日志

2.配置从节点

slave1：
docker run -d \
-p 3307:3306 \
-v /llkj/mysql/slave1/conf:/etc/mysql/conf.d \
-v /llkj/mysql/slave1/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name llkj-mysql-slave1 \
--restart=always \
mysql:8.0.29

slave2：
docker run -d \
-p 3308:3306 \
-v /llkj/mysql/slave2/conf:/etc/mysql/conf.d \
-v /llkj/mysql/slave2/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name llkj-mysql-slave2 \
--restart=always \
mysql:8.0.29

修改配置文件
vim /llkj/mysql/slave1/conf/my.cnf
vim /llkj/mysql/slave2/conf/my.cnf

配置文件分别为以下内容

[mysqld]
# 服务器唯一id，每台服务器的id必须不同，如果配置其他从机，注意修改id
server-id=2
# 中继日志名，默认xxxxxxxxxxxx-relay-bin
#relay-log=relay-bin

[mysqld]
# 服务器唯一id，每台服务器的id必须不同，如果配置其他从机，注意修改id
server-id=3
# 中继日志名，默认xxxxxxxxxxxx-relay-bin
#relay-log=relay-bin

重启两结点

docker restart llkj-mysql-slave1
docker restart llkj-mysql-slave2

分别在两结点配置主从

ps:master_log_pos=0可以默认匹配主节点日志的端口

#slave1:
change master to master_host='ip为主结点的ip', 
master_user='llkj_slave',master_password='用户密码', master_port=3306,
master_log_file='注意连接二进制文件编码',master_log_pos=端口号; 

#slave2和1一样，如果按照本文上面的则为:
change master to master_host='ip为主结点的ip', 
master_user='llkj_slave',master_password='123456', master_port=3306,
master_log_file='binlog.000003',master_log_pos=1532;

启动主从同步

start slave;
-- 查看状态（不需要分号）
show slave status\G;

 这两个值为yes才行

停止和重置结点

-- 在从机上执行。功能说明：停止I/O 线程和SQL线程的操作。
stop slave; 

-- 在从机上执行。功能说明：用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件。
reset slave;

-- 在主机上执行。功能说明：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。
-- 用于第一次进行搭建主从库时，进行主库binlog初始化工作；
reset master;

测试

//在主结点进行创建
CREATE DATABASE db_user;
USE db_user;
CREATE TABLE t_user (
 id BIGINT AUTO_INCREMENT,
 uname VARCHAR(30),
 PRIMARY KEY (id)
);
INSERT INTO t_user(uname) VALUES('zhang3');
//因为记录的是查询语句，所以此结果会会根据不同的结点名字进行改变
INSERT INTO t_user(uname) VALUES(@@hostname);

如果在主库创建从库发生改变则表示搭建成功

3.ShardingSphere-JDBC

官网：https://shardingsphere.apache.org/document/5.1.1/cn/user-manual/shardingsphere-jdbc/spring-boot-starter/rules/readwrite-splitting/

定义：轻量级java框架，在java的jdbc层提供额外的服务，使用客户都安直接连数据库，jar包形式提供服务，无需额外部署和依赖，可以理解为增强版本的JDBC驱动，完全兼容JDBC和ORM框架。

# 应用名称
spring.application.name=sharging-jdbc-demo
# 开发环境设置
spring.profiles.active=dev
# 内存模式
spring.shardingsphere.mode.type=Memory

# 配置真实数据源
spring.shardingsphere.datasource.names=master,slave1,slave2

# 配置第 1 个数据源
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://192.168.200.130:3306/db_user
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

# 配置第 2 个数据源
spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.200.130:3307/db_user
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=123456

# 配置第 3 个数据源
spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://192.168.200.130:3308/db_user
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=123456

# 读写分离类型，如: Static，Dynamic
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.type=Static
# 写数据源名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.props.write-data-source-name=master
# 读数据源名称，多个从数据源用逗号分隔
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.props.read-data-source-names=slave1,slave2

# 负载均衡算法名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.load-balancer-name=sgg_round

# 负载均衡算法配置
# 负载均衡算法类型
spring.shardingsphere.rules.readwrite-splitting.load-balancers.sgg_round.type=ROUND_ROBIN
spring.shardingsphere.rules.readwrite-splitting.load-balancers.sgg_random.type=RANDOM
spring.shardingsphere.rules.readwrite-splitting.load-balancers.sgg_weight.type=WEIGHT
spring.shardingsphere.rules.readwrite-splitting.load-balancers.sgg_weight.props.slave1=1
spring.shardingsphere.rules.readwrite-splitting.load-balancers.sgg_weight.props.slave2=2

# 打印SQl
spring.shardingsphere.props.sql-show=true

测试成功：

示例代码地址GitHub - qing0149/ShardingSphere-JDBC-demo

giit:https://github.com/qing0149/ShardingSphere-JDBC-demo.git