利用ShardingSphere实现读写分离

1.准备工作

昨天已经搭建了MySQL的主从复制，然而MySQL自己无法实现读写分离，所以我们要借助MyCat或者ShardingSphere来实现读写分离。

那么我们首先需要安装好MySQL的主从复制环境

2.步骤

首先导入依赖，我这里用4.0.0版本的ShardingSphere

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--  DB: MyBatis Plus, Druid     -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.21</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.1</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

然后创建mapper、entity类

package com.zym.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
@Data
@TableName("user")
public class User {
    @TableId(type = IdType.AUTO)
    private int id;
    private String name;
}

package com.zym.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zym.entity.User;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UserMapper extends BaseMapper<User> {
}

配置文件：

spring:
  shardingsphere:
    datasource:
      names: master,slave
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.60.10:3306/test?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.60.10:3307/test?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: 123456
    masterslave:
      load-balance-algorithm-type: round_robin
      name: dataSource
      master-data-source-name: master
      slave-data-source-names: slave
    props:
      sql:
        show: true
    # 一个实体类对应两张表，覆盖
  main:
    allow-bean-definition-overriding: true

最后是测试类

package com.zym;
import com.zym.entity.User;
import com.zym.mapper.UserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.List;
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class ShardingSphereDemoApplicationTests {
    @Autowired
    private UserMapper userMapper;
    @Test
    public void insertUser() {
        User user = new User();
        user.setName("小王");
        int i = userMapper.insert(user);
        System.out.println(i == 1 ? "插入成功" : "插入失败");
    }
    @Test
    public void getUserList(){
        List<User> userList = userMapper.selectList(null);
        for (User user : userList) {
            System.out.println(user.toString());
        }
    }
}

然后我们来运行一下测试类，首先我们插入一条数据

可以看到这里是用master插入的

然后我们再查询一下用户列表

可以看到这里是用slave去查询的

那么这样我们的读写分离就配置好了，要注意的是，在真实环境中，从库应该建立只读用户，而不是直接使用root用户，这里为了演示就直接用了root用户，切勿在正式环境使用！！！

源码地址：https://gitee.com/zym213/sharding-sphere-demo