当前位置：首页 > news >正文

分库分表之基于Shardingjdbc+docker+mysql主从架构实现读写分离（二）

news 2025/8/23 18:19:58

说明：如果实现了docker部署mysql并完成主从复制的话再继续，本篇文章主要说明springboot配置实现Shardingjdbc进行读写分离操作。

如果没实现docker部署mysql实现主从架构的话点击我

Shardingjdbc配置介绍（版本：5.3.2）

application.yml配置设置连接池全局属性

spring:datasource:driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriverurl: jdbc:shardingsphere:classpath:qiyu-db-sharding.yamlhikari:pool-name: qiyu-user-poolminimum-idle: 15idle-timeout: 60000maximum-pool-size: 300connection-init-sql: select 1connection-timeout: 4000max-lifetime: 60000

shardingjdbc读写分离配置（qiyu-db-sharding.yaml）

dataSources:user_master: ##新表，重建的分表dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.1.128:8808/qiyu_live_user?useUnicode=true&characterEncoding=utf8username: rootpassword: rootuser_slave0: ##新表，重建的分表dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.DriverjdbcUrl: jdbc:mysql://192.168.1.128:8809/qiyu_live_user?useUnicode=true&characterEncoding=utf8username: rootpassword: rootrules:- !READWRITE_SPLITTINGdataSources:#读写分离配置user_ds:staticStrategy:writeDataSourceName: user_masterreadDataSourceNames:- user_slave0- !SINGLE#  不分表分分库的默认数据源defaultDataSource: user_ds- !SHARDINGtables:t_user:actualDataNodes: user_ds.t_user_${(0..04).collect(){it.toString().padLeft(2,'0')}}tableStrategy:standard:#指定用于分表的列名。shardingColumn: user_idshardingAlgorithmName: t_user-inline#定义分表算法shardingAlgorithms:t_user-inline:type: INLINEprops:#根据"user_id"对5取模的结果将作为分表的后缀，范围为00到04，对应五个表，并在左侧填充0。algorithm-expression: t_user_${(user_id % 5).toString().padLeft(2,'0')}
props:sql-show: true

同时这个hikari的数据源有点小bug，加个配置类

package com.laoyang.provider.config;import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.ApplicationRunner;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;import javax.sql.DataSource;
import java.sql.Connection;/*** @author:Kevin* @create: 2023-07-29 21:30* @Description:*/
@Configuration
public class ShardingjdbcDatasourceAutoConnectionconfig {private static final Logger LOGGER = LoggerFactory.getLogger(ShardingjdbcDatasourceAutoConnectionconfig.class);@Beanpublic ApplicationRunner runner(DataSource dataSource) {return args -> {LOGGER.info("dataSource: {}",dataSource);//手动触发下连接池的连接创建Connection connection = dataSource.getConnection();};}
}

运行：出现下图说明配置成功。