使用sharding-jdbc实现水平分库分表和读写分离

原创已于 2022-04-14 14:05:02 修改 · 1.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java

于 2022-04-14 00:04:54 首次发布

MySQL 同时被 3 个专栏收录

12 篇文章

订阅专栏

SpringBoot

7 篇文章

订阅专栏

sharding-jdbc

1 篇文章

订阅专栏

本文详细指导如何在四台虚拟机上搭建MySQL主从复制，并通过Sharding-JDBC配置数据源、分库分表策略，实现实时的读写分离和水平扩展，适合Spring Boot项目实践。

使用 Sharding-Jdbc 实现读写分离和水平分表

服务器准备

我们克隆四台虚拟机【可参考克隆虚拟机】。ip地址分别为：
1. 192.168.17.123
2. 192.168.17.124
3. 192.168.17.125
4. 192.168.17.126
在四台机器上分别按照好mysql。【可以现在一台服务器上按照好，然后克隆三个】。

克隆之后需要修改mysql服务的UUID。否则后面搭建主从复制时会出现问题。

# find / -name 'auto.cnf'
# vim /data/mysql/auto.cnf
[auto]
server-uuid=804f2ebe-3a1c-11e8-ab46-000c29133368 # 按照这个16进制格式，修改server-uuid，重启mysql即可

uuid可以使用SQL来生成。**select uuid()**;

搭建主从复制

在这里插入图片描述

**搭建方式参考 **主从复制

主机配置

要求主从所有配置项都配置在 /etc/mysql.cnf的【mysqld】节点下。且都是小写字母。

# ========================== [必须] ===============================
#[必须] 主服务器唯一ID
server-id=1

#[必须]启用二进制日志，指明路径。比如：自己本地的路径/log/mysqlbin
log-bin=ooyhao-bin

# ========================== [可选] ===============================

#[可选] 0(默认)表示读写(主机)，1表示只读(从机)
read-only=0

#[可选]设置日志文件保留的时长，单位是秒
binlog_expire_logs_seconds=6000

#[可选]控制单个二进制日志大小。此参数的最大和默认值都是1GB
max_binlog_size=200M

#[可选]设置不要复制的数据库
binlog-ignore-db=test

#[可选]设置需要复制的数据库，默认全部记录。比如：binlog-do-db=atguigu_master_slave
binlog-do-db=需要复制的主数据库名字

#[可选]设置binlog格式
binlog-format=STATEMENT

重启后台mysql服务，使配置生效。systemctl restart mysqld

**搭建方式参考 **主从复制

主机配置

要求主从所有配置项都配置在 /etc/mysql.cnf的【mysqld】节点下。且都是小写字母。

# ========================== [必须] ===============================
#[必须] 主服务器唯一ID
server-id=1

#[必须]启用二进制日志，指明路径。比如：自己本地的路径/log/mysqlbin
log-bin=ooyhao-bin

# ========================== [可选] ===============================

#[可选] 0(默认)表示读写(主机)，1表示只读(从机)
read-only=0

#[可选]设置日志文件保留的时长，单位是秒
binlog_expire_logs_seconds=6000

#[可选]控制单个二进制日志大小。此参数的最大和默认值都是1GB
max_binlog_size=200M

#[可选]设置不要复制的数据库
binlog-ignore-db=test

#[可选]设置需要复制的数据库，默认全部记录。比如：binlog-do-db=atguigu_master_slave
binlog-do-db=需要复制的主数据库名字

#[可选]设置binlog格式
binlog-format=STATEMENT

重启后台mysql服务，使配置生效。systemctl restart mysqld

从机配置

#[必须]从服务器唯一ID
server-id=2
#[可选]启用中继日志
relay-log=mysql-relay

重启后台mysql服务，使配置生效。systemctl restart mysqld

主机：建立账户并授权

#在主机MySQL里执行授权主从复制的命令
#5.5,5.7
GRANT REPLICATION SLAVE ON *.* TO 'db_sync'@'从机器数据库IP' IDENTIFIED BY 'abc123'; 


# MySQL8
CREATE USER 'db_sync'@'%' IDENTIFIED BY '123456'; 
GRANT REPLICATION SLAVE ON *.* TO 'db_sync'@'%'; 
#此语句必须执行。否则见下面。
ALTER USER 'db_sync'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

查询master状态

mysql> show master status;
+-------------------+----------+----------------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB         | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+----------------------+------------------+-------------------+
| ooyhao-bin.000001 |     1136 | atguigu_master_slave |                  |                   |
+-------------------+----------+----------------------+------------------+-------------------+
1 row in set (0.00 sec)

记录下File和Position的值

注意：执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化。

从机：配置需要复制的主机

解释
CHANGE MASTER TO MASTER_HOST=‘主机的IP地址’,MASTER_USER=‘主机用户名’,MASTER_PASSWORD=‘主机用户名的密码’,MASTER_LOG_FILE=‘mysql-bin.具体数字’,MASTER_LOG_POS=具体值;
示例
CHANGE MASTER TO MASTER_HOST=‘192.168.17.123’,MASTER_USER=‘db_sync’,MASTER_PASSWORD=‘123456’,MASTER_LOG_FILE=‘ooyhao-bin.000001’,MASTER_LOG_POS=1136;

mysql> CHANGE MASTER TO MASTER_HOST='192.168.17.123',MASTER_USER='slave1',MASTER_PASSWORD='123456',MASTER_LOG_FILE='ooyhao-bin.000001',MASTER_LOG_POS=1136; 
Query OK, 0 rows affected, 8 warnings (0.01 sec)

启动slave

mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

在从节点上执行SHOW SLAVE STATUS. 如果出现 Slave_IO_Running: Yes和 Slave_SQL_Running: Yes。表示启动成功。

项目搭建

需求说明

按照user_id 奇偶进行分库。按照order_id奇偶计算分表。

创建springboot项目

使用 springboot 启动器创建springboot项目

导入依赖

<dependencies>
      <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter</artifactId>
      </dependency>

      <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-test</artifactId>
          <scope>test</scope>
      </dependency>

      <!-- 导入sharding-jdbc依赖 -->
      <dependency>
          <groupId>org.apache.shardingsphere</groupId>
          <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
          <version>4.0.0-RC1</version>
      </dependency>

      <!-- 导入mysql依赖 -->
      <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
      </dependency>

      <!-- 导入Druid连接池依赖 -->
      <dependency>
          <groupId>com.alibaba</groupId>
          <artifactId>druid-spring-boot-starter</artifactId>
          <version>1.1.23</version>
      </dependency>

      <!-- 导入mybatis-plus依赖 -->
      <dependency>
          <groupId>com.baomidou</groupId>
          <artifactId>mybatis-plus-boot-starter</artifactId>
          <version>3.3.0</version>
      </dependency>

      <!-- 导入lombok依赖 -->
      <dependency>
          <groupId>org.projectlombok</groupId>
          <artifactId>lombok</artifactId>
      </dependency>

  </dependencies>

创建数据库和表

在两个主库中创建数据库和表，从库会自动同步；

 create database order_db;
 use order_db;
 
 create table tb_order_0(
		order_id bigint(20) primary key not null,
		sku_name varchar(128) not null,
		user_id bigint(20)
 );
 
  create table tb_order_1(
		order_id bigint(20) primary key not null,
		sku_name varchar(128) not null,
		user_id bigint(20)
 );

在这里插入图片描述

配置主从复制和读写分离

在【application.properties】中配置

# 允许beanDefinition覆盖  [一个实体类对应两张表，覆盖]
spring.main.allow-bean-definition-overriding=true

# ======================================= 数据源配置 ======================================

# 配置数据源的名称 主库为m1,m2 从库为 s1,s2
spring.shardingsphere.datasource.names=m0,m1,s0,s1

# 配置数据源具体内容 包含连接池、驱动、地址、用户名和密码
# 配置第 1 个主库数据源
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://192.168.17.123:3306/order_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m0.username=root
spring.shardingsphere.datasource.m0.password=root

# 配置第 2 个主库数据源
spring.shardingsphere.datasource.s0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s0.url=jdbc:mysql://192.168.17.124:3306/order_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.s0.username=root
spring.shardingsphere.datasource.s0.password=root

# 配置第 1 个从库数据源
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://192.168.17.125:3306/order_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

# 配置第 2 个从库数据源
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://192.168.17.126:3306/order_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.s1.username=root
spring.shardingsphere.datasource.s1.password=root

# ====================================== 配置读写分离 ======================================
# 配置主从服务  ds0为 user_db
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s0

spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=m1
spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names=s1

# ====================================== 分库分表策略 ======================================
# 按照 user_id 奇偶进行分库。按照order_id奇偶计算分表。

# 分表策略
# 表达式 `ds_$->{0..1}`枚举的数据源为读写分离配置的逻辑数据源名称
# 指定数据库分片规则，指定数据库表的分片规则
spring.shardingsphere.sharding.tables.order.actual-data-nodes=ds$->{0..1}.tb_order_$->{0..1}
spring.shardingsphere.sharding.tables.order.key-generator.column=order_id
# 分布式序列算法配置
spring.shardingsphere.sharding.tables.order.key-generator.type=SNOWFLAKE

# ===================================== 分库策略 ==============================================
# 指定表的分库策略 [分库字段为 user_id] user_id 为偶数则放到m0库，为奇数则放到m1库
spring.shardingsphere.sharding.tables.order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}

# 默认的分库策略
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{user_id % 2}

# ===================================== 分表策略 ==============================================

# 表达式 `ds_$->{user_id % 2}` 枚举的数据源为读写分离配置的逻辑数据源名称
spring.shardingsphere.sharding.tables.order.table-strategy.inline.sharding-column=order_id
spring.shardingsphere.sharding.tables.order.table-strategy.inline.algorithm-expression=tb_order_$->{order_id % 2}

# 打开sql输出日志
spring.shardingsphere.props.sql.show=true

Mapper和Entity

@Data
public class Order {
    private Long orderId;
    private String skuName;
    private Long userId;
}

@Mapper
public interface OrderMapper extends BaseMapper<Order> {}

测试

@SpringBootTest
class ShardingJdbcReadWriteApplicationTests {

    @Autowired
    private OrderMapper orderMapper;

    @Test
    void addOrder() {
        for (long i = 0; i < 1000; i++) {
            Order order = new Order();
            order.setUserId((long) new Random().nextInt(20));
            order.setSkuName("sku-i-"+i);
            orderMapper.insert(order);
        }
    }
}

结果：192.168.17.124是192.168.17.123的从节点，192.168.17.126是192.168.17.125的从节点。
192.168.17.123

mysql> select count(*) from tb_order_0;
+----------+
| count(*) |
+----------+
|      256 |
+----------+
1 row in set (0.11 sec)

mysql> select count(*) from tb_order_1;
+----------+
| count(*) |
+----------+
|      258 |
+----------+
1 row in set (0.06 sec)

192.168.17.124

mysql> select count(*) from tb_order_0;
+----------+
| count(*) |
+----------+
|      256 |
+----------+
1 row in set (0.08 sec)

mysql> select count(*) from tb_order_1;
+----------+
| count(*) |
+----------+
|      258 |
+----------+
1 row in set (0.06 sec)

192.168.17.125

mysql> select count(*) from tb_order_0;
+----------+
| count(*) |
+----------+
|      244 |
+----------+
1 row in set (0.06 sec)

mysql> select count(*) from tb_order_1;
+----------+
| count(*) |
+----------+
|      242 |
+----------+
1 row in set (0.08 sec)

192.168.17.126

mysql> select count(*) from tb_order_0;
+----------+
| count(*) |
+----------+
|      244 |
+----------+
1 row in set (0.09 sec)

mysql> select count(*) from tb_order_1;
+----------+
| count(*) |
+----------+
|      242 |
+----------+
1 row in set (0.08 sec)