shardingJDBC的读写分离

使用的数据库是mysql8.5

进行读写分离之前需要进行主从同步

本次从库是从主库复制而来的需要删除data中的auto.conf文件否则uuid相同会影响主从复制

删除以后不用自己重新创建，启动服务会自动创建文件

主库的my.ini

[mysqld]
#设置3306端口
port=3306
#设置mysql的安装目录
basedir=E:\\blb\mysql-8.0.25-winx64
#设置mysql数据库的数据的存放目录
datadir=E:\\blb\mysql-8.0.25-winx64\Data
#允许最大连接数
max_connections=200
#允许连接失败的次数。
max_connect_errors=10
#服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
#开启日志
log-bin=mysql-bin
#设置服务id，主从不能一致
server-id=1
#设置需要同步的数据库
binlog-do-db=user_db
#屏蔽系统库同步
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
#设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

从库的my.ini

[mysqld]
#设置3306端口
port=3307
#设置mysql的安装目录
basedir=E:\\blb\mysql-8.0.25-winx64s
#设置mysql数据库的数据的存放目录
datadir=E:\\blb\mysql-8.0.25-winx64s\Data-2
#允许最大连接数
max_connections=200
#允许连接失败的次数。
max_connect_errors=10
#服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
#开启日志
log-bin=mysql-bin
#设置服务id，主从不能一致
server-id=2
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
[mysql]
#设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
#设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

重启主库和从库

授权主从复制专用账号

进入master的数据库，为master创建复制用户

create user 'slaveuser'@'localhost' identified by 'ysp_password';

grant replication slave on *.* to 'slaveuser'@'localhost(此处填写从库主机ip我的两个都是本机)'

FLUSH PRIVILEGES;
查看master的状态
show master status;

进入从库

#切换至从库bin目录，登录从库
mysql ‐h localhost ‐P3307 ‐uroot ‐p 
#先停止同步
STOP SLAVE;
#修改从库指向到主库，使用上一步记录的文件名以及位点
CHANGE MASTER TO

MASTER_HOST= 'localhost',

MASTER_USER= 'slaveuser',

MASTER_PASSWORD= 'ysp_password',

MASTER_LOG_FILE='binlog.000003',

MASTER_LOG_POS=;
# MASTER_LOG_FILE='',#与主库File 保持一致

# MASTER_LOG_POS= , #与主库Position 保持一致

#启动同步
START SLAVE;
#查看从库状态Slave_IO_Runing和Slave_SQL_Runing都为Yes说明同步成功，如果不为Yes，请检查error_log，然后 排查相关异常。
show slave status\G

读写分离项目配置

#应用名称
spring.application.name=sharding-jdbc

mybatis-plus.configuration.map-underscore-to-camel-case=true

spring.main.allow-bean-definition-overriding=true

#ShardingJDBC配置
# 配置数据源 m1
spring.shardingsphere.datasource.names=m1,m2,m0,s1
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/order_db1?serverTimezone=UTC
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456
# 配置数据源m2
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/order_db2?serverTimezone=UTC
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=123456
# 配置数据源m0
spring.shardingsphere.datasource.m0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url=jdbc:mysql://localhost:3306/user_db?serverTimezone=UTC
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=123456
# 配置数据源s1
spring.shardingsphere.datasource.s1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s1.url=jdbc:mysql://localhost:3307/user_db?serverTimezone=UTC
spring.shardingsphere.datasource.s1.username=root
spring.shardingsphere.datasource.s1.password=123456
#配置存在的节点
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=m$->{1..2}.t_order_$->{1..2}
#配置主键生成方法为SNOWFLAKE
spring.shardingsphere.sharding.tables.t_order.key-generator.column=order_id
spring.shardingsphere.sharding.tables.t_order.key-generator.type=SNOWFLAKE
#配置分表策略
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_${order_id%2+1}
#配置分库策略
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=m$->{user_id%2+1}
#配置公共表
spring.shardingsphere.sharding.broadcast-tables=t_dict
# 读写分离配置
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=m0
spring.shardingsphere.masterslave.slave-data-source-names=s1
#打开sql输出日志
spring.shardingsphere.props.sql.show=true
logging.level.root=info
logging.level.com.hp.sharding-jdbc=debug
logging.level.druid.sql=debug

实体类

package com.hp.shardingjdbc.entity;

import lombok.Data;

@Data
public class User {
    private Long id;
    private String username;
    private String password;
}

mapper

package com.hp.shardingjdbc.mapper;

import com.hp.shardingjdbc.entity.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface UserMapper {

    @Insert("insert into tb_user(username,password) values(#{username},#{password})")
    void insertUser(@Param("username")String username,@Param("password")String password);

    @Select("select * from tb_user where id= #{id}")
    User selectUserById(@Param("id")int id);
}

数据库建表

USE user_db;
DROP TABLE tb_user;
CREATE TABLE tb_user(
id INT(10) PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
PASSWORD VARCHAR(20)
);

 @Autowired
    private UserMapper userMapper;
    @Test
    public void insertUser(){
            //userMapper.insertUser("zhangsan","xxxx");
        userMapper.insertUser("lisi","xxxx");
    }

    @Test
    public void selectUserById(){
        User user = userMapper.selectUserById(1);
        System.out.println(user);
    }

上面是测试类中的代码

 

测试类中的结果是 执行写入的语句结果都是放到了m0数据库

执行查询的数据都在s1数据库进行