聊一聊ShardingSphere的读写分离

什么是 ShardingSphere

Apache ShardingSphere 是一款分布式的数据库生态系统， 可以将任意数据库转换为分布式数据库，并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。

ShardingSphere-JDBC

ShardingSphere-JDBC 定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。

ShardingSphere-Proxy

ShardingSphere-Proxy 定位为透明化的数据库代理端，通过实现数据库二进制协议，对异构语言提供支持。

ShardingSphere支持很多功能，比如数据分片、分布式事务、读写分离、高可用等等。今天我们主要聊一聊ShardingSphere-JDBC的读写分离。
首先我们要进行MySQL的主从搭建，为了更好的学习我们搭建一主两从。在docker中通过不同的端口，进行MySQL的搭建。
主服务器的搭建：

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

//修改config文件夹下my.cnf文件
[mysqld]
server-id=1

-p docker的3306端口映射到宿主机3306端口；-v 是docker中文件夹映射到宿主机的文件夹；--name 是docker中Mysql的名称

在docker中创建MySQL后，执行下面代码查看是否成功

//进入容器，后面是避免中文乱码
docker exec -it yy-mysql-master env LANG=C.UTF-8 /bin/bash
//避免低版本客户端连接失败，8.0版本检验方式改变了
alter user 'root'@'%' identified with mysql_native_password by '123456';
//在主服务器中查询status,记录下面数据，在从服务器中有用
show master status;
 binlog.000003 |     1357 

主机中创建slave用户
create user 'yy_slave'@'%'
设置密码
alter user 'yy_slave'@'%' identified with mysql_native_password by '123456'
授予复制权限 
grant replication slave on *.* to 'yy_slave'@'%';
刷新
flush privileges;

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

//修改config文件夹下my.cnf文件
[mysqld]
server-id=2

alter user 'root'@'%' identified with mysql_native_password by '123456';

change master to master_host='IP地址',
master_user='yy_slave',master_password='123456',master_port=3306,
master_log_file='binlog.000003',master_log_pos=1357;

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

//修改config文件夹下my.cnf文件
[mysqld]
server-id=3

alter user 'root'@'%' identified with mysql_native_password by '123456';

change master to master_host='IP地址',
master_user='yy_slave',master_password='123456',master_port=3306,
master_log_file='binlog.000003',master_log_pos=1357;

为了更好的测试ShardingSphere的读写分离，我们创建SpringBoot的项目进行测试：
用到jar包：

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.1.1</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-test</artifactId>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
        </dependency>
    </dependencies>

application.properties文件

# 应用名称
spring.application.name=sharding-sph

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://IP地址:3306/db_user?serverTimezone=UTC&characterEncoding=UTF-8
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://IP地址:3307/db_user?serverTimezone=UTC&characterEncoding=UTF-8
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://IP地址:3308/db_user?serverTimezone=UTC&characterEncoding=UTF-8
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=123456

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=alg_round

# 负载均衡算法配置
spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_round.type=ROUND_ROBIN
spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_random.type=RANDOM
spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_weiht.type=WEIGHT
spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_weiht.props.slave1=1.0
spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_weiht.props.slave2=2.0

spring.shardingsphere.props.sql-show=true

@Data
@TableName("t_user")
public class User {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String uname;
}

@RestController
@RequestMapping("user")
public class UserController {

    @Autowired
    UserMapper userMapper;

    @RequestMapping("insert")
    public void insert(){
        User user = new User();
        user.setUname("yy");
        userMapper.insert(user);
    }

    @Transactional
    @RequestMapping("insertAndSelect")
    public void insert2(){
        User user = new User();
        user.setUname("铁锤");
        userMapper.insert(user);

        List<User> users = userMapper.selectList(null);
    }

    @RequestMapping("SelectAll")
    public void insert3(){
        List<User> users = userMapper.selectList(null);
        for(User user:users){
            System.out.println(user);
        }
    }
}

@Mapper
public interface UserMapper extends BaseMapper<User> {

}

最后查询数据库，我们可以发现从服务器能够及时同步主服务的数据，并且查询也能做到读写分离以及轮询（权重、随机）