文章目录

读写分离背景

MySQL主从配置

安装并启动从服务器

修改主从配置文件

创建主从同步账号

设置主从同步

ShardingSphere实现读写分离

实现方式

yml配置

测试代码

结果演示

MybatisPlus实现读写分离

实现方式

POM依赖

yml配置

打印数据源切面

测试代码

结果演示

两种读写分离对比

遇到问题

基于ShardingSphere+MybatisPlus实现读写分离

读写分离背景

面对日益增加的系统访问量，数据库的吞吐量面临着巨大瓶颈。 对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说，将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善。

通过一主多从的配置方式，可以将查询请求均匀的分散到多个数据副本，能够进一步的提升系统的处理能力。 使用多主多从的方式，不但能够提升系统的吞吐量，还能够提升系统的可用性，可以达到在任何一个数据库宕机，甚至磁盘物理损坏的情况下仍然不影响系统的正常运行。

MySQL主从配置

安装并启动从服务器

查找MySQL安装位置，并复制一份作为从服务器按住windows + R弹出运行框，输入services.msc回车，出现本地服务界面。搜索MYSQL，右键属性查看安装位置。从【mysql-5.7.31-winx64】复制一份【mysql-5.7.31-s1】，来作从服务器修改从服务器my.ini配置文件，需要修改的内容如下：

#端口修改为3307[client]port = 3307[mysqld]#设置3307端口port = 3307# 修改mysql安装目录basedir="C:\Program Files\MySQL\mysql-5.7.31-s1"# 修改mysql数据库的数据的存放目录datadir="C:\Program Files\MySQL\mysql-5.7.31-s1\data"

E:\开发工具\mysql-5.7.31-s1\bin>mysqld install mysqls1 --defaults-file="E:\开发工具\mysql-5.7.31-s1\my.ini"Service successfully installed.

修改主从配置文件

修改主服务器的配置文件

[mysqld]# 开启binlog日志log-bin=mysql-bin# 选择row模式binlog_format=row# 设置服务id，主从不能一样server-id=1# 设置需要同步的数据库binlog-do-db=test# 屏蔽系统库同步binlog-ignore-db=mysqlbinlog-ignore-db=information_schemabinlog-ignore-db=performance_schema

[mysqld]# 开启binlog日志log-bin=mysql-bin# 选择row模式binlog_format=row# 设置服务id，主从不能一样server-id=2# 设置需要同步的数据库replicate_wild_do_table=test.%# 屏蔽系统库同步replicate_wild_ignore_table=mysql.%replicate_wild_ignore_table=information_schema.%replicate_wild_ignore_table=performance_schema.%

创建主从同步账号

连接到主库，执行如下命令，创建主从同步账号

grant replication slave on *.* to 'data_sync'@'%' identified by '123456';flush privileges;

show master status;

设置主从同步

连接到从库，执行如下命令，配置主从同步

# 停止主从同步STOP SLAVE;# 修改从库指向主库，使用上一步记录的文件名和位点CHANGE MASTER TOmaster_host='localhost',master_user='data_sync',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=154;# 启动主从同步START SLAVE;

show slave status;

ShardingSphere实现读写分离

实现方式

读写分离则是根据 SQL 语义(SQLStatement)的分析，将读操作和写操作分别路由至主库与从库。大白话就是insert、update、delete路由到主库，select路由到从库。路由类：ReadwriteSplittingDataSourceRouter 如下图所示：

yml配置

###############################      读写分离配置##############################spring:  shardingsphere:    datasource:      names: master,slave      master:        type: com.zaxxer.hikari.HikariDataSource        driver-class-name: com.mysql.cj.jdbc.Driver        jdbc-url: jdbc:mysql://127.0.0.1:3306/test?useSSL=true&charset=utf8mb4&serverTimezone=Hongkong        username: root        password: 123456      slave:        type: com.zaxxer.hikari.HikariDataSource        driver-class-name: com.mysql.cj.jdbc.Driver        jdbc-url: jdbc:mysql://127.0.0.1:3307/test?useSSL=true&charset=utf8mb4&serverTimezone=Hongkong        username: root        password: 123456    rules:      readwrite-splitting:        dataSources:          # 读写分离逻辑数据源名称          ds0:            # 读写分离类型            type: STATIC            props:              # 写库数据源名称              write-data-source-name: master              # 读库数据源名称，多个从数据源用逗号              read-data-source-names: slave            # 负载均衡算法名称            loadBalancerName: ROUND_ROBIN        # 负载均衡算法配置        load-balancers:          simple_round_robin:            type: ROUND_ROBIN    props:      # 日志输出到控制台      sql-show: truemybatis-plus:  mapper-locations: classpath*:mapper/*.xml  type-aliases-package: com.hqg.study.example.entity  global-config:    db-config:      id-type: AUTO      field-strategy: NOT_NULL  configuration:    log-impl: org.apache.ibatis.logging.stdout.StdOutImplswagger:  enable: true

    /**     * 测试读写分离     */    @Test    public void testRW(){        Integer id = 6;        TOrder tOrder = new TOrder();        tOrder.setOrderId(id);        tOrder.setUserId(id);        tOrderMapper.insert(tOrder);        TOrder order = tOrderMapper.selectOne(new LambdaQueryWrapper<TOrder>().eq(TOrder::getOrderId, id));        System.out.println(JSON.toJSONString(order));    }}

Actual SQL: master ::: INSERT INTO t_order ( order_id,user_id ) VALUES ( ?,? ) ::: [6, 6]

Actual SQL: slave ::: SELECT order_id,user_id FROM t_order WHERE (order_id = ?) ::: [6]

MybatisPlus实现读写分离实现方式

通过DS注解手动配置来实现的，路由类：AbstractRoutingDataSource

POM依赖

<dependency>    <groupId>com.baomidou</groupId>    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>    <version>3.3.1</version></dependency>

###############################      MyBatis-plus读写分离配置##############################spring:  datasource:    dynamic:      primary: master #设置默认的数据源或者数据源组,默认值即为master      strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源      datasource:        master:          url: jdbc:mysql://127.0.0.1:3306/test?useSSL=true&charset=utf8mb4&serverTimezone=Hongkong          username: root          password: 123456          driver-class-name: com.mysql.cj.jdbc.Driver # 3.2.0开始支持SPI可省略此配置        slave:          url: jdbc:mysql://127.0.0.1:3307/test?useSSL=true&charset=utf8mb4&serverTimezone=Hongkong          username: root          password: 123456          driver-class-name: com.mysql.cj.jdbc.Drivermybatis-plus:  mapper-locations: classpath*:mapper/*.xml  type-aliases-package: com.hqg.study.example.entity  global-config:    db-config:      id-type: AUTO      field-strategy: NOT_NULL  configuration:    log-impl: org.apache.ibatis.logging.stdout.StdOutImplswagger:  enable: true

@Aspect@EnableAspectJAutoProxy(exposeProxy = true, proxyTargetClass = true)@Component@Slf4j@Order(1)public class MultiDataSourceAspect {    @Pointcut("execution(* com.example.shardingspheredb.service.impl.*.*(..)) ")    public void dataSource() {    }    @Around("dataSource()")    public Object printDataSource(ProceedingJoinPoint pjp) throws Throwable {        MethodSignature methodSignature = (MethodSignature)pjp.getSignature();        Method method = methodSignature == null ? null : methodSignature.getMethod();        log.warn("==================MultiDataSourceAspect-printDataSource====method:{}.{},datasource route:{}================="                , pjp.getTarget().getClass().getName()                , method == null ? "" : method.getName(), DynamicDataSourceContextHolder.peek());        Object result = pjp.proceed();        return result;    }}

@DS("slave")@Slf4j@Servicepublic class TOrderServiceImpl extends ServiceImpl<TOrderMapper, TOrder> implements TOrderService {    @Autowired    private TOrderMapper tOrderMapper;    @DS("master")    @Override    public Boolean insertOrder(TOrder tOrder) {        int result = baseMapper.insert(tOrder);        return result > 0 ? true : false;    }    @Override    public TOrder queryOrder(Integer orderId) {        TOrder order = tOrderMapper.selectOne(new LambdaQueryWrapper<TOrder>().eq(TOrder::getOrderId, orderId));        return order;    }}

    /**     * 测试MyBatisPlus读写分离     */    @Test    public void testMyBatisPlusRW(){        Integer id = 9;        TOrder tOrder = new TOrder();        tOrder.setOrderId(id);        tOrder.setUserId(id);        tOrderService.insertOrder(tOrder);        TOrder order = tOrderService.queryOrder(id);        System.out.println(JSON.toJSONString(order));    }

==================MultiDataSourceAspect-printDataSource====method:com.example.shardingspheredb.service.impl.TOrderServiceImpl.insertOrder,datasource route:master=================

==> Preparing: INSERT INTO t_order ( order_id, user_id ) VALUES ( ?, ? )

==================MultiDataSourceAspect-printDataSource====method:com.example.shardingspheredb.service.impl.TOrderServiceImpl.queryOrder,datasource route:slave=================

==> Preparing: SELECT order_id,user_id FROM t_order WHERE (order_id = ?)

两种读写分离对比

对比

shardingsphere

MyBatis-Plus

实现方式

SQLStatement类型实现

DS注解实现

优点

自动；可配合数据分片使用；事务内读写均走主库；基于Hint强制走主库

配置灵活；支持多主多从；

缺点

只能一主多从，使用不灵活

配置工作量比较大，可用切面解决

遇到问题

主从同步中的Slave_IO_Running为no，mysql Error_code: 1593

检查 data目录下auto.cnf 文件中uuid是否与另一台服务器 重复检查my.inf文件中server-id是否重复