Sharding-JDBC分库分表之SpringBoot主从配置

Sharding-JDBC系列

1、Sharding-JDBC分库分表的基本使用

2、Sharding-JDBC分库分表之SpringBoot分片策略

3、Sharding-JDBC分库分表之SpringBoot主从配置

前言

在开发中，如果对数据库的读和写都在一个数据服务器中操作，面对日益增加的访问量，无论是安全性、高可用性，还是并发量都不能满足实际需求。

而大多数的系统对数据库的操作都是读多写少，此时可以将数据库拆分为主库和从库。主库主要负责处理事务性的增删改操作，从库负责处理查询操作，从而提升查询的性能。对于从库，可以使用主从复制的方式同步数据。

在项目中，使用读写分离提升数据库的并发负载能力。Sharding-JDBC提供一主多从的读写分离配置，可独立使用，也可配合分库分表使用。Sharding-JDBC读写分离是通过分析SQL语义，将读操作和写操作分别路由到主库和从库，提供了透明化的读写分离。

在同一个线程且同一个数据库连接内，如有写入操作，后面的读操作均从主库读取，用于保证数据一致性。

环境准备

在Window中安装Mysql8，配置主从Mysql。

2.1 my.ini配置

2.1.1 主Mysql的my.ini配置

# my.ini
[mysqld]
#skip-grant-tables# 设置3307端口
port=3307
# 设置mysql的安装目录
basedir=D:\\database\\master-slave\\mysql-8.0.33-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\\database\\master-slave\\mysql-8.0.33-winx64\\data\\
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
authentication_policy=mysql_native_password#开启日志
log_bin=mysql-bin#设置服务id，主从不能一样
server_id=2#设置需要同步的数据库
binlog_do_db=order_db#屏蔽系统库同步
binlog_ignore_db=mysql,information_schema,performance_schemasql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES#不区分大小写
lower_case_table_names=1[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

2.1.2 从Mysql的my.ini配置

# my.ini
[mysqld]
#skip-grant-tables# 设置3308端口
port=3308
# 设置mysql的安装目录
basedir=D:\\database\\master-slave\\mysql-8.0.33-winx64-slave
# 设置mysql数据库的数据的存放目录
datadir=D:\\database\\master-slave\\mysql-8.0.33-winx64-slave\\data\\
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
authentication_policy=mysql_native_password#开启日志
log_bin=mysql-bin#设置服务id，主从不能一样
server_id=3#设置需要同步的数据库
binlog_do_db=order_db#屏蔽系统库同步
binlog_ignore_db=mysql,information_schema,performance_schemasql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES#不区分大小写
lower_case_table_names=1[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

2.2 安装

1）安装：mysqld install mysql_slave --defaults-file="D:\database\master-slave\mysql-8.0.33-winx64-slave\my.ini"

2）初始化：mysqld --initialize --console

通过该命令，会打印初始化的日志信息，在日志中，包含mysql的root账号的默认密码。如：

[Note] [MY-010454] [Server] A temporary password is generated for root@localhost: -1xsq4zpD3+h

3）启动：net start mysql_slave

4）关闭：net stop mysql_slave

2.3 主从复制配置

1）主库配置

# 创建test用户，密码为123456
create user 'test'@'127.0.0.1' identified by '123456'# 给test用户授权，用于从库操作主库
grant replication slave on *.* to 'test'@'127.0.0.1'# 刷新权限
flush PRIVILEGES# 查看主库的状态
show master status

通过show master status命名，查看主库状态信息，记录File和Position的信息。

2）从库配置

# 查看从库状态
# mysql8之前
# show slave statusshow replica status# 与主库连接
# mysql 8之前
#change master to master_host='127.0.0.1',master_post=3307, master_user='test', master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=3268# mysql8 改为
CHANGE REPLICATION SOURCE TOSOURCE_HOST='127.0.0.1',SOURCE_PORT=3307,SOURCE_USER='test',SOURCE_PASSWORD='123456',SOURCE_LOG_FILE='mysql-bin.000002',SOURCE_LOG_POS=3268# 开启复制
start replica

以上的SOURCE_LOG_FILE和SOURCE_LOG_POS是通过上面show master status中显示的File和Position的值。

Sharding-JDBC主从实现

3.1 规则配置

# 主从复制
server:port: 8080#sharding-jdbc分片规则配置
spring:shardingsphere:datasource:names: order1,slave1 #数据源名称，有几个数据源就写几个名字order1:  # 主库type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3307/order_db?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=falseusername: rootpassword: 123456slave1: # 从库type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3308/order_db?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=falseusername: rootpassword: 123456sharding:# 主从库逻辑数据源定义master-slave-rules:order_ms: # 逻辑库master-data-source-name: order1  # 主库slave-data-source-names: slave1  # 从库，可配置多个#分表策略#按照id分表，id使用雪花算保证全局唯一，具体算法：tb_order是表前缀，拼接上：$->{id % 2}  的值。order_ms为逻辑库的名称tables:tb_order: #逻辑表actual-data-nodes: order_ms.tb_order_$->{1..2}  #order1：数据源名称；两个tb_order表，分别为tb_order_1和tb_order_2key-generator: # 指定主键生成策略column: order_idtype: SNOWFLAKEtable-strategy:inline:sharding-column: order_id   #分片键。对id进行分表algorithm-expression: tb_order_$->{order_id % 2 + 1}  #分片算法props:sql:show: true  # 是否打印sql

1）配置主从数据库；

2）通过master-slave-rules，配置逻辑库名称，并指定主库和从库；

3）根据项目需要确定是否配置分表策略，以上配置为按order_id分表，等被2整除的存放在tb_order_1，不能整除的存放在tb_order_2；