Mysql 实现读写分离(主从复制)

一、概念

系统读写操作比例为10:1，为了减轻数据库的读压力，将读写分开，主库用来写数据，多个从库用来读数据，为了保证数据读取的完整性，就要把主库的数据复制到从库（主从复制）

数据层面上来说是主从复制，业务上来说是读写分离

二、搭建主从复制

mysql版本：5.7
操作系统版本：centos7.6

这里以一台master，一台slave演示

1.修改配置文件

master节点添加以下内容

vim /etc/my.cnf

添加以下配置

server-id=1000

#开启binlog
log-bin=mysql-master-bin

#忽略哪些库
binlog-ignore-db=mysql

binlog_cache_size=1M

#主从复制的混合模式
binlog_format=mixed

slave节点配置文件添加以下内容

#同一局域网内 一定要唯一
server-id=1001


#开启binlog
log-bin=mysql-slave-bin


#中继日志文件
relay_log=mysql-relay-bin


##忽略哪些库
binlog-ignore-db=mysql


#如果需要同步函数和存储过程
log_bin_trust_function_creators=true


binlog_cache_size=1M


##主从复制的混合模式
binlog_format=mixed


#忽略主键重复的错误
slave_skip_errors=1062

2.在主库开启从库对主库的访问权限

grant replication slave,replication client on *.* to 'root'@'slave库ip' identified by '从库密码';

查看主库的binlog文件和复制的位置

show master status;

3.在从库配置连接主库的user，ip，密码，主库的binlog文件，复制的位置

change master to master_host='master库ip', master_user='root', master_password='密码', master_port=3306, master_log_file='mysql-master-bin.000001', master_log_pos=1884;

4.开启从库，查看从库状态

在从库执行 start slave; 开启从库
查看从库状态 show slave status\G;

如果两个都是yes，说明主从复制完成

报错可能原因
1:密码输错，导致从库没有访问权限
2:master的binlog文件名写错，positon复制位置写错

使用Sharding-jdbc结合springboot实现读写分离操作

1.创建项目，引入依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.4.5</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.levi</groupId>
    <artifactId>sharding-jdbc</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding-jdbc</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
        <sharding-sphere.version>4.0.0-RC1</sharding-sphere.version>
    </properties>
  <dependencies>
      <!-- 依赖web -->
      <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-web</artifactId>
      </dependency>
      <!-- 依赖mybatis和mysql驱动 -->
      <dependency>
          <groupId>org.mybatis.spring.boot</groupId>
          <artifactId>mybatis-spring-boot-starter</artifactId>
          <version>2.1.4</version>
      </dependency>
      <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <scope>runtime</scope>
      </dependency>
      <!--依赖lombok-->
      <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>
      <!--依赖sharding-->
      <dependency>
          <groupId>org.apache.shardingsphere</groupId>
          <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
          <version>${sharding-sphere.version}</version>
      </dependency>
      <dependency>
          <groupId>org.apache.shardingsphere</groupId>
          <artifactId>sharding-core-common</artifactId>
          <version>${sharding-sphere.version}</version>
      </dependency>
      <!--依赖数据源druid-->
      <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>druid-spring-boot-starter</artifactId>
          <version>1.1.21</version>
      </dependency>
      <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <scope>test</scope>
      </dependency>
  </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>2.3.4.RELEASE</version>
            </plugin>
        </plugins>
    </build>

</project>

mapper接口

@Repository
public interface UserMapper {
    @Insert("insert into user (nickname,password,age,sex,birthday) values(#{nickname},#{password},#{age},#{sex},#{birthday})")
    void addUser(User user);
    @Select("select * from user")
    List<User> findUsers();
}

yml文件（最重要）

server:
  port: 8085
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    # 参数配置，显示sql
    props:
      sql:
        show: true
    # 配置数据源
    datasource:
      # 给每个数据源取别名，下面的ds1,ds2,ds3任意取名字
      names: ds1,ds2,ds3
      # 给master-ds1每个数据源配置数据库连接信息
      ds1:
        # 配置druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://master库ip:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: 密码
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds2-slave
      ds2:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://slave库ip:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: 密码
        maxPoolSize: 100
        minPoolSize: 5
      # 配置ds3-slave
      ds3:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://slave库ip:3306/test?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT%2b8
        username: root
        password: 密码
        maxPoolSize: 100
        minPoolSize: 5
    # 配置默认数据源ds1
    sharding:
      # 默认数据源，主要用于写，注意一定要配置读写分离 ,注意：如果不配置，那么就会把三个节点都当做从slave节点，新增，修改和删除会出错。
      default-data-source-name: ds1
#     配置数据源的读写分离，但是数据库一定要做主从复制
    masterslave:
      # 配置主从名称，可以任意取名字
      name: ms
      # 配置主库master，负责数据的写入
      master-data-source-name: ds1
      # 配置从库slave节点
      slave-data-source-names: ds2,ds3
      # 配置slave节点的负载均衡均衡策略，采用轮询机制   默认为随机(random)
      load-balance-algorithm-type: round_robin
# 整合mybatis的配置XXXXX
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.levi.shardingjdbc.entity

controller

@RestController
public class UserController {

    @Resource
    private UserMapper userMapper;

    @GetMapping("addUser")
    public Object addUser() {
//        User user = new User("levi","123456",1,"1995-8-11");
        User user = new User();
        user.setNickname("levi");
        user.setPassword("123456");
        user.setAge(20);
        user.setSex(1);
        user.setBirthday(LocalDateTime.now());
        userMapper.addUser(user);
        return "ok";
    }


    @GetMapping("findUsers")
    public Object findUsers() {
        return userMapper.findUsers();
    }
}

启动项目，调用接口
可以发现插入都是走ds1主库，查询从ds2和ds3轮训

总结

1.主库要开通bin-log，通过监听bin-log文件，把变更发送给从库，从库把变更记录到本地的中继日志文件，然后执行对应sql，保证与主库数据一致
2.sharding-jdbc通过管理数据库连接，解析应用执行的sql，根据sql的操作类型决定时打到主库还是从库，来实现读写分离操作

补充：
主从复制延迟问题
延迟原因：

1. 网络传输需要时间，io操作
2. 从库sql线程执行的同时，如果遇到比较大的select语句锁表，那么执行sql要等待

解决方案：强制走主库
注解+aop实现

注解定义

@Retention(value = RetentionPolicy.RUNTIME)
@Target(value = ElementType.METHOD)
public @interface ToMasterDB {
}

定义切面

@Aspect
@Slf4j
@Component
public class ToMasterAspect {

    /**
     * 定义切入点  注解
     */
    @Pointcut("@annotation(com.levi.shardingjdbc.annotation.ToMasterDB)")
    public void joinPoint(){

    }

    @Around("joinPoint()")
    public Object around(ProceedingJoinPoint pjp) throws Throwable {
        HintManager.getInstance().setMasterRouteOnly();
        log.info("sharding-jdbc强制走主库");
        Object result = pjp.proceed();
        HintManager.clear(); //一定要clear掉，不然其他的select操作也会走主库
        return result;
    }
}

controller再加一个方法测试 可见该方法一直都是走主库查询

    @GetMapping("findByMaster")
    @ToMasterDB
    public Object findByMaster() {
        return userMapper.findUsres();
    }