Mysql主从配置 & shardingsphere 读写分离配置

Mysql 主从配置

本文采用docker 安装 ，mysql 版本5.7

实例名	映射端口	配置文件
master	3306->3306	/opt/mysql/master/my.cnf
slave01	3307->3306	/opt/mysql/slave01/my.cnf
slave02	3308->3306	/opt/mysql/slave02/my.cnf

master 配置文件

server-id=1
log_bin = /var/lib/mysql/mysql_bin
expire_logs_days = 30
max_binlog_size = 1024M
binlog_format = ROW
sync_binlog = 1

• 其中binlog_format 有 statement、row、mixed， 建议设置为 row、mixed，后面再说为什么。

• server-id 需要唯一

---

slave01 配置文件

其实就改了个server-id

# 服务ID
server-id=2
log_bin = /var/lib/mysql/mysql_bin
expire_logs_days = 30
max_binlog_size = 1024M
binlog_format = ROW
sync_binlog = 1

---

slave02 配置文件

# 服务ID
server-id=3
log_bin = /var/lib/mysql/mysql_bin
expire_logs_days = 30
max_binlog_size = 1024M
binlog_format = ROW
sync_binlog = 1

运行docker 实例

分别运行三个docker 实例

sudo docker run -d -p 3306:3306 --privileged=true -v /opt/mysql/master/my.cnf:/etc/mysql/my.cnf -v /opt/mysql/master/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 --name master  mysql:5.7

sudo docker run -d -p 3307:3306 --privileged=true -v /opt/mysql/slave01/my.cnf:/etc/mysql/my.cnf -v /opt/mysql/slave01/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 --name slave01 mysql:5.7

sudo docker run -d -p 3308:3306 --privileged=true -v /opt/mysql/slave02/my.cnf:/etc/mysql/my.cnf -v /opt/mysql/slave02/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 --name slave02 mysql:5.7

创建 replication账号

进入主库docker 实例，连接上mysql

docker exec -it master /bin/bash

mysql -uroot -p

创建账号并授权

create user slave@'%' identified by 'slave';

grant replication slave on *.* to slave@'%';

查看主库状态 show master status;

从库同步主库

分别进入从库docker 实例

连接mysql

mysql -uroot -p

连接后执行

change master to 
master_host = '你的ip',
master_port = 3306,
master_user = 'slave',
master_password = 'slave',
master_log_file = 'mysql_bin.000003',
master_log_pos = 597;

再start slave;

测试验证

在主库创建数据库rwdb, 创建表 mytable ,再插入一条数据

CREATE DATABASE  rwdb
  CHARACTER SET utf8;


  CREATE TABLE mytable (
    id INT UNSIGNED AUTO_INCREMENT,
    name VARCHAR(50),
     PRIMARY KEY ( `id` )
) ;
 

 insert into mytable(name) values(uuid())

---

再去从库查询

数据库、表、表数据都同步过来了

binlog_format

statement : binlog 存储的是操作的sql ,对于一些不确定的函数，比如UUID() , 主从同步会不一致，但是一般情况 statement 存储空间比 row 要小，比如 update table set xxx = 1 ,如果是格式再存储所有行数据。

row : 存储的是操作的行数据，因此不会出现主从数据不一致，但存储空间要比statement大。

mixed : 遇到不确定的函数 UUID(), now () 等采用 row 格式存储， 其他情况采用statement 格式存储

测试将主库 binlog_format = statement

重启主库

sudo docker restart master

再次在主库插入一条数据

主库表数据

从库数据查看，可以看出两边数据不一致。

shardingsphere 集 成

shardingsphere 可以很好的支持分库分表， 也可以配置读写分离，但读写分离仅支持一主多从。读写分离适用于读多写少场景。

简单说明使用Mybatis-plus + shardingsphere 读写分离配置

---

引入pom

<dependencies>
  <!-- SpringBoot Web -->
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
  </dependency>
  <!-- Lombok -->
  <dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <scope>provided</scope>
  </dependency>

  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.70</version>
  </dependency>

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


  <!-- for spring boot -->
  <!-- https://mvnrepository.com/artifact/io.shardingsphere/sharding-jdbc-spring-boot-starter -->
  <dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
  </dependency>

  <!--集成druid连接池-->


  <dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.23</version>
  </dependency>
  <dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.0</version>
  </dependency>

  <dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.5</version>
  </dependency>

</dependencies>

配置文件

spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.url=jdbc:mysql://xxxx:3306/rwdb?characterEncoding=UTF-8
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.url=jdbc:mysql://xxx:3307/rwdb?characterEncoding=UTF-8
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=123456

spring.shardingsphere.datasource.slave1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.url=jdbc:mysql://xxx:3308/rwdb?characterEncoding=UTF-8
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=123456

spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0,slave1




spring.shardingsphere.props.sql.show=true

mybatis-plus.mapper-locations=classpath:mapper/*Mapper.xml
mybatis-plus.type-aliases-package=com.example.domain
mybatis-plus.configuration.map-underscore-to-camel-case=true

配置Mapper扫描包

---

测试验证

@Data
public class HelloDO {
    
    private Long id;
    
    private String name;
}

HelloMapper helloMapper = context.getBean(HelloMapper.class);
HelloDO helloDO = new HelloDO();
helloDO.setName("测试2");
helloMapper.insert(helloDO);

System.out.println(helloDO);
HelloDO queryDO =  helloMapper.selectById(helloDO.getId());
System.out.println(queryDO);

结果插入语句在master 执行， 查询语句在从库执行了，符合我们预期。