配置sharding-jdbc的分库分表数据源，及默认读写分离数据源（可本地测试）

爱自己er

已于 2023-08-21 08:47:11 修改

阅读量1.5k

点赞数

文章标签： java

于 2023-08-09 17:30:11 首次发布

本文链接：https://blog.csdn.net/ttpxp01/article/details/132192168

版权

本文介绍了如何配置sharding-jdbc进行分库分表和读写分离，包括在本地创建多个数据库和表，设置触发器同步主库数据到读库，以及使用mybatis-plus进行数据操作。示例基于sharding-jdbc 5.1.2版本，详细配置可参考官方用户手册。

摘要由CSDN通过智能技术生成

sharding jdbc 涉及操作数据库的方方面面。此处不多做扩展，具体在使用时请对比不同的数据库管理框架与shardingsphere对照

因shardingsphere引入了mode概念，本地相同配置（Memory 、 Standalone）在5.1.2版本可以启动，5.2.1版本不能正常启动（SPI配置错误）。本文主要以5.1.2配置做示例

详细配置请见官方用户手册

sharding-jdbc引入

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
            <version>5.1.2</version>
        </dependency>

在本地建立6个数据库，分别做分库分表的参照库，和基础读写分离库

# 第一个 写库  ds0_master
CREATE DATABASE `db_ds0_master` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

# 第一个 读库  ds0_slave
CREATE DATABASE `db_ds0_slave` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

# 第二个 写库  ds1_master
CREATE DATABASE `db_ds1_master` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

# 第三个  读库  ds1_slave
CREATE DATABASE `db_ds1_slave` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

# 基础 写库  ds10_base_master
CREATE DATABASE `db_ds10_base_master` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

# 基础 读库  ds10_base_slave
CREATE DATABASE `db_ds10_base_slave` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

在4个库下分别创建相同的表（分库分片+读写分离）

# 在4个数据库上分别创建 2张表. 在4个数据库上的表结构完全相同
# use db_ds0_master;
# use db_ds0_slave;
# use db_ds1_master;
# use db_ds1_slave;
CREATE TABLE `cms_merchant_0` (
  `merchant_id` bigint(20) NOT NULL,
  `merchant_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`merchant_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
 
CREATE TABLE `cms_merchant_1` (
  `merchant_id` bigint(20) NOT NULL,
  `merchant_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`merchant_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

在2个主库上创建触发器，模拟主库数据同步至读库

# 删除触发器脚本
use db_ds0_master;
drop trigger sync_0_master_merchant_0_i;
drop trigger sync_0_master_merchant_0_u;
drop trigger sync_0_master_merchant_0_d;
drop trigger sync_0_master_merchant_1_i;
drop trigger sync_0_master_merchant_1_u;
drop trigger sync_0_master_merchant_1_d;

use db_ds1_master;
drop trigger sync_1_master_merchant_0_i;
drop trigger sync_1_master_merchant_0_u;
drop trigger sync_1_master_merchant_0_d;
drop trigger sync_1_master_merchant_1_i;
drop trigger sync_1_master_merchant_1_u;
drop trigger sync_1_master_merchant_1_d;

# 创建触发器脚本

use db_ds0_master;
CREATE TRIGGER sync_0_master_merchant_0_i after INSERT ON db_ds0_master.cms_merchant_0 FOR EACH ROW
BEGIN
INSERT INTO db_ds0_slave.cms_merchant_0 (merchant_id, merchant_name) VALUES (NEW.merchant_id, NEW.merchant_name);
END;

CREATE TRIGGER sync_0_master_merchant_0_u after update ON db_ds0_master.cms_merchant_0 FOR EACH ROW
BEGIN
update db_ds0_slave.cms_merchant_0 set merchant_name = NEW.merchant_name where merchant_id = NEW.merchant_id ;
END;

CREATE TRIGGER sync_0_master_merchant_0_d after delete ON db_ds0_master.cms_merchant_0 FOR EACH ROW
BEGIN
delete from db_ds0_slave.cms_merchant_0 where merchant_id = old.merchant_id;
END;


CREATE TRIGGER sync_0_master_merchant_1_i after INSERT ON db_ds0_master.cms_merchant_1 FOR EACH ROW
BEGIN
INSERT INTO db_ds0_slave.cms_merchant_1 (merchant_id, merchant_name) VALUES (NEW.merchant_id, NEW.merchant_name);
END;

CREATE TRIGGER sync_0_master_merchant_1_u after update ON db_ds0_master.cms_merchant_1 FOR EACH ROW
BEGIN
update db_ds0_slave.cms_merchant_1 set merchant_name = NEW.merchant_name where merchant_id = NEW.merchant_id ;
END;

CREATE TRIGGER sync_0_master_merchant_1_d after delete ON db_ds0_master.cms_merchant_1 FOR EACH ROW
BEGIN
delete from db_ds0_slave.cms_merchant_1 where merchant_id = old.merchant_id;
END;


use db_ds1_master;
CREATE TRIGGER sync_1_master_merchant_0_i after INSERT ON db_ds1_master.cms_merchant_0 FOR EACH ROW
BEGIN
INSERT INTO db_ds1_slave.cms_merchant_0 (merchant_id, merchant_name) VALUES (NEW.merchant_id, NEW.merchant_name);
END;

CREATE TRIGGER sync_1_master_merchant_0_u after update ON db_ds1_master.cms_merchant_0 FOR EACH ROW
BEGIN
update db_ds1_slave.cms_merchant_0 set merchant_name = NEW.merchant_name where merchant_id = NEW.merchant_id ;
END;

CREATE TRIGGER sync_1_master_merchant_0_d after delete ON db_ds1_master.cms_merchant_0 FOR EACH ROW
BEGIN
delete from db_ds1_slave.cms_merchant_0 where merchant_id = old.merchant_id;
END;


CREATE TRIGGER sync_1_master_merchant_1_i after INSERT ON db_ds1_master.cms_merchant_1 FOR EACH ROW
BEGIN
INSERT INTO db_ds1_slave.cms_merchant_1 (merchant_id, merchant_name) VALUES (NEW.merchant_id, NEW.merchant_name);
END;

CREATE TRIGGER sync_1_master_merchant_1_u after update ON db_ds1_master.cms_merchant_1 FOR EACH ROW
BEGIN
update db_ds1_slave.cms_merchant_1 set merchant_name = NEW.merchant_name where merchant_id = NEW.merchant_id ;
END;

CREATE TRIGGER sync_1_master_merchant_1_d after delete ON db_ds1_mast