Spring boot基于docker-compose完成mysql读写分离

记录下整合流程

1、核心组件件版本

spring boot版本2.2.5.RELEASE
mysql版本5.7.24
druid-spring-boot-starter版本1.2.6
sharding-jdbc-spring-boot-starter版本4.1.1
mybatis-plus-boot-starter版本为3.3.2

2、mysql 读写分离环境的搭建

2.1、docker-compose所在目录如下

├── READM.md
├── cnf
│   ├── read
│   │   └── read.cnf
│   └── write
│       └── write.cnf
├── data
├── docker-compose.yml
└── initsql
    └── init.sql

read.cnf 的内容如下:
[mysqld]
#主数据库端ID号
server_id = 2
#开启二进制日志
log-bin = mysql-bin

write.cnf的内容如下:
[mysqld]
#主数据库端ID号
server_id = 1
#开启二进制日志
log-bin = mysql-bin
#需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
binlog-do-db = 要同步的数据库

2.2、docker-compose.yml配置

version: '3.1'

services:
  mysql_local_master:
    container_name: master
    image: mysql:5.7.24
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
    ports:
      - "13306:3306"
    volumes:
      - ./initsql:/tmp/
      - ./data/:/var/lib/mysql
      - ./cnf/write/:/etc/mysql/conf.d

  mysql_local_slave:
    container_name: slave
    image: mysql:5.7.24
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
    ports:
      - "13307:3306"
    volumes:
      - ./cnf/read/:/etc/mysql/conf.d
    depends_on:
      - mysql_local_master

2.3容器启动后执行命令

进入目录执行docker-compose up 启动容器, 容器启动成功后进行手动配置:

进入master容器执行如下命令

# slave账号进行主从的同步
grant replication slave on *.* to 'slave'@'%' identified by 'slave';
flush privileges;
show master status;

进入slave容器执行如下命令

# 重制主从
reset slave;
# 配置要同步的主库
change master to master_host= 'master', master_user='slave', master_password='slave', 
master_log_file='基于show master status的结果配置', master_log_pos= 基于show master status的结果配置
# 开始同步
start slave;
# 查看状态
show slave status;
    
# 添加read账号
grant select  on xm_program_exam.* to 'read'@'%' IDENTIFIED by 'read';
flush PRIVILEGES;    

截止到这里mysql主从环境搭建完毕

3、创建spring-boot项目进行完成主从的读写

3.1、核心pom文件如下:

        <!-- 数据库配置 -->
        <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.2</version>
        </dependency>
        <!-- 记录sql语句时使用 -->
        <dependency>
            <groupId>p6spy</groupId>
            <artifactId>p6spy</artifactId>
            <version>3.8.7</version>
        </dependency>
        <!-- 数据库线程池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.6</version>
        </dependency>
        <!-- 多数据源-读写分离 -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <!-- 常用基础组件 -->        
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- web服务 -->                
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

3.2、核心配置如下

spring:
  shardingsphere:
    datasource:
      names: master,slave
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.p6spy.engine.spy.P6SpyDriver
        url: jdbc:p6spy:mysql://127.0.0.1:13306/xm_program_exam?useUnicode=true&characterEncoding=UTF8&autoReconnect=true&zeroDateTimeBehavior=convertToNull
        username: root
        password: root
        max-active: 100
        initial-size: 10
        min-idle: 5
        max-wait: 6000
        time-between-eviction-runs-millis: 2000
        min-evictable-idle-time-millis: 600000
        max-evictable-idle-time-millis: 900000
        validation-query: "select 1"
        test-while-idle: true
        keep-alive: true
        phy-max-use-count: 500
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.p6spy.engine.spy.P6SpyDriver
        url: jdbc:p6spy:mysql://127.0.0.1:13307/xm_program_exam?useUnicode=true&characterEncoding=UTF8&autoReconnect=true&zeroDateTimeBehavior=convertToNull
        username: read
        password: read
        max-active: 100
        initial-size: 10
        min-idle: 5
        max-wait: 6000
        time-between-eviction-runs-millis: 2000
        min-evictable-idle-time-millis: 600000
        max-evictable-idle-time-millis: 900000
        validation-query: "select 1"
        test-while-idle: true
        keep-alive: true
        phy-max-use-count: 500

4、参考链接:

https://segmentfault.com/a/1190000023775512
https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE
https://hub.docker.com/_/mysql