MySQL主从架构配合ShardingJdbc实现读写分离

目录

架构搭建

基于Docker去创建MySQL的主从架构

读写分离

pom.xml

<dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>${fdy-mysql.version}</version>
</dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>shardingsphere-jdbc-core</artifactId><version>${sharding.jdbc.version}</version><exclusions><exclusion><groupId>org.yaml</groupId><artifactId>snakeyaml</artifactId></exclusion></exclusions>
</dependency>
<dependency><groupId>org.yaml</groupId><artifactId>snakeyaml</artifactId><version>${snakeyaml.version}</version>
</dependency>

fdy-live-user-provider 模块

application.yml

spring:datasource:driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriverurl: jdbc:shardingsphere:classpath:fdy-db-sharding.yaml# 设置连接池的全局属性hikari:pool-name: fdy-user-poolminimum-idle: 15 # 初始化连接池maximum-pool-size: 300connection-init-sql: select 1connection-timeout: 4000max-lifetime: 60000logging:level:org.apache.shardingsphere: DEBUGcom.zaxxer.hikari: DEBUG

fdy-db-sharding.yaml

dataSources:user_master: ##新表，重建的分表dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driver  # 统一为 driverClassNamejdbcUrl: jdbc:mysql://192.168.56.101:8808/fdy_live_user?useUnicode=true&characterEncoding=utf8username: rootpassword: rootuser_slave0: ##新表，重建的分表dataSourceClassName: com.zaxxer.hikari.HikariDataSourcedriver-class-name: com.mysql.cj.jdbc.Driver  # 统一为 driverClassNamejdbcUrl: jdbc:mysql://192.168.56.101:8809/fdy_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

ShardingJdbcDatasourceAutoInitConnectionConfig.java

java\org\fdy\live\user\provider\config\ShardingJdbcDatasourceAutoInitConnectionConfig

package org.fdy.live.user.provider.config;import lombok.extern.slf4j.Slf4j;
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 lee* @description 解决hikari存在bug, 强制hikari进行数据源的初始化操作*/
@Slf4j
@Configuration
public class ShardingJdbcDatasourceAutoInitConnectionConfig {@Beanpublic ApplicationRunner runner(DataSource dataSource) {return args -> {log.info("dataSource: {}", dataSource);// 手动触发下连接池的连接创建Connection connection = dataSource.getConnection();
//            try (Connection connection = dataSource.getConnection()){}};}
}