目錄主從庫搭建Compose FileMaster 配置Slave 配置主從配置創建分庫分表Order 1 庫Order 2 庫User 庫Sharding-JDBC 引入Sharding-JDBC 配置可選配置數據源配置主從復制配置數據節點配置Demo 程序

Sharding-JDBC 是無侵入式的 MySQL 分庫分表操作工具，所有庫表設置僅需要在配置文件中配置即可，無須修改任何代碼。本文寫了一個 Demo，使用的是 SpringBoot 框架，通過 Docker 進行 MySQL 實例管理，分庫分表結構如下圖，同時所有的庫都進行了主從復制：

主從庫搭建

Docker 項目結構：

docker├── docker-compose.yml├── master│ ├── data│ ├── log│ │ └── error.log│ ├── my.cnf│ └── mysql-files # Win 需要，Linux 不需要├── README.md└── slave ├── data ├── log │ └── error.log ├── my.cnf └── mysql-filesCompose File

version: ’3’networks: sharding-jdbc-demo: driver: bridge ipam: config:- subnet: 172.25.0.0/24services: master: image: mysql container_name: sharding-jdbc-demo-master ports: - '3307:3306' volumes: - './master/data:/var/lib/mysql' - './master/mysql-files:/var/lib/mysql-files' # win 下的 MySQL8 需要，Linux 不需要 - './master/log/error.log:/var/log/mysql/error.log' - './master/my.cnf:/etc/mysql/my.cnf' environment: MYSQL_ROOT_PASSWORD: 123456 entrypoint: bash -c 'chown -R mysql:mysql /var/log/mysql && chmod 644 /etc/mysql/my.cnf && exec /entrypoint.sh mysqld' restart: unless-stopped networks: sharding-jdbc-demo:ipv4_address: 172.25.0.101 slave: image: mysql container_name: sharding-jdbc-demo-slave ports: - '3308:3306' volumes: - './slave/data:/var/lib/mysql' - './slave/mysql-files:/var/lib/mysql-files' - './slave/log/error.log:/var/log/mysql/error.log' - './slave/my.cnf:/etc/mysql/my.cnf' environment: MYSQL_ROOT_PASSWORD: 123456 entrypoint: bash -c 'chown -R mysql:mysql /var/log/mysql && chmod 644 /etc/mysql/my.cnf && exec /entrypoint.sh mysqld' restart: unless-stopped networks: sharding-jdbc-demo:ipv4_address: 172.25.0.102Master 配置

[mysqld]pid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockdatadir = /var/lib/mysqllog-error= /var/log/mysql/error.logbind-address = 0.0.0.0secure-file-priv = NULLmax_connections = 16384character-set-server = utf8mb4collation-server = utf8mb4_general_ciinit_connect =’SET NAMES utf8mb4’skip-name-resolveserver_id = 1log-bin = mysql-binbinlog-do-db = db_order_1 # 復制 db_order_1binlog-do-db = db_order_2 # 復制 db_order_2binlog-do-db = db_user # 復制 db_userlog-slave-updatessync_binlog = 1auto_increment_offset = 1auto_increment_increment = 1expire_logs_days = 7log_bin_trust_function_creators = 1# Custom config should go here!includedir /etc/mysql/conf.d/Slave 配置

[mysqld]pid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockdatadir = /var/lib/mysqllog-error= /var/log/mysql/error.logbind-address = 0.0.0.0secure-file-priv = NULLmax_connections = 16384character-set-server = utf8mb4collation-server = utf8mb4_general_ciinit_connect =’SET NAMES utf8mb4’skip-name-resolveskip-host-cacheserver_id = 2log-bin = mysql-binlog-slave-updatessync_binlog = 0innodb_flush_log_at_trx_commit = 0 # 提交策略replicate-do-db = db_order_1# 復制 db_order_1replicate-do-db = db_order_2# 復制 db_order_2replicate-do-db = db_user # 復制 db_userslave-net-timeout = 60 # 重連時間log_bin_trust_function_creators = 1# Custom config should go here!includedir /etc/mysql/conf.d/主從配置

啟動容器 docker compose up -d;登錄 Master mysql -uroot -h 127.0.0.1 -P 3307 -p ;查看 master 狀態。

mysql> show master statusG*************************** 1. row ***************************File: mysql-bin.000004 # 記住 Bin log 當前文件名稱Position: 156 # 記住 Bin log 當前偏移量Binlog_Do_DB: db_order_1,db_order_2,db_user # 確認復制數據庫是否正確Binlog_Ignore_DB:Executed_Gtid_Set:

登錄 Slave mysql -uroot -h 127.0.0.1 -P 3308 -p設置 Master 連接，注意 host 與 port 是內網的地址和端口。

mysql> change master to master_host=’172.25.0.101’, master_user=’root’, master_password=’123456’, master_port=3306, master_log_file=’mysql-bin.000004’, master_log_pos=156;

啟動同步

mysql> start slave;

查看 Slave 狀態，若 Slave_IO 與 Slave_SQL 都在運行為 YES 即成功。

mysql> show slave statusG*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 172.25.0.101Master_User: rootMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000004Read_Master_Log_Pos: 156Relay_Log_File: d2a706a02933-relay-bin.000002Relay_Log_Pos: 324Relay_Master_Log_File: mysql-bin.000004Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: db_order_1,db_order_2,db_user創建分庫分表

登錄 Master，創建數據庫：

CREATE DATABASE db_order_1;CREATE DATABASE db_order_2;CREATE DATABASE db_user;

此時從庫也會創建數據庫，若沒有，則是主從配置失敗了。此時已完成垂直分庫和水平分庫。接下來創建數據表：

Order 1 庫

先 USE db_order_1;，再分別創建 t_dict 全局表、t_order_1 和 t_order_2 水平分表。

DROP TABLE IF EXISTS `t_dict`;CREATE TABLE `t_dict`( `id` int NOT NULL AUTO_INCREMENT, `type` int NOT NULL, `enum_value` int NOT NULL, `name` varchar(64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ’’, PRIMARY KEY (`id`)) ENGINE = InnoDB AUTO_INCREMENT = 7 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;LOCK TABLES `t_dict` WRITE;INSERT INTO `t_dict` VALUES (1,1,0,’未定義’),(2,1,1,’未付款’),(3,1,2,’已付款’),(4,1,3,’退款中’),(5,1,4,’已退款’),(6,1,5,’已完成’),(7,2,0,’未定義’),(8,2,1,’已創建’),(9,2,2,’已驗證’),(10,2,3,’已凍結’),(11,2,4,’已注銷’),(12,2,5,’已刪除’);UNLOCK TABLES;DROP TABLE IF EXISTS `t_order_1`;CREATE TABLE `t_order_1`( `id` bigint NOT NULL, `user_id` bigint NOT NULL, `price` decimal(10, 2) NOT NULL, `status` int NOT NULL DEFAULT ’1’, PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;DROP TABLE IF EXISTS `t_order_2`;CREATE TABLE `t_order_2`( `id` bigint NOT NULL, `user_id` bigint NOT NULL, `price` decimal(10, 2) NOT NULL, `status` int NOT NULL DEFAULT ’1’, PRIMARY KEY (`id`)) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;Order 2 庫

先 USE db_order_2;，再分別創建 t_dict 全局表、t_order_1 和 t_order_2 水平分表。所執行 SQL 與 db_order_1 一致。

User 庫

先 USE db_user;，再分別創建 t_dict 全局表和 t_user 表，此處就不進行水平或垂直分表了。垂直分表 sharding-jdbc 不會去處理，因為垂直分表之后就是異表異構了，執行 Join 操作就可以了，或者代碼進行多次查詢實現。

DROP TABLE IF EXISTS `t_dict`;CREATE TABLE `t_dict`( `id` int NOT NULL AUTO_INCREMENT, `type` int NOT NULL, `enum_value` int NOT NULL, `name` varchar(64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ’’, PRIMARY KEY (`id`)) ENGINE = InnoDB AUTO_INCREMENT = 7 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;LOCK TABLES `t_dict` WRITE;INSERT INTO `t_dict` VALUES (1,1,0,’未定義’),(2,1,1,’未付款’),(3,1,2,’已付款’),(4,1,3,’退款中’),(5,1,4,’已退款’),(6,1,5,’已完成’),(7,2,0,’未定義’),(8,2,1,’已創建’),(9,2,2,’已驗證’),(10,2,3,’已凍結’),(11,2,4,’已注銷’),(12,2,5,’已刪除’);UNLOCK TABLES;DROP TABLE IF EXISTS `t_user`;CREATE TABLE `t_user`( `id` bigint NOT NULL AUTO_INCREMENT, `name` varchar(128) COLLATE utf8mb4_general_ci NOT NULL DEFAULT ’’, `type` int NOT NULL DEFAULT ’1’, PRIMARY KEY (`id`)) ENGINE = InnoDB AUTO_INCREMENT = 1426999086541635586 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;Sharding-JDBC 引入

Sharding-JDBC maven 包：

<!-- Sharding-jdbc --><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>4.1.1</version></dependency>

本 Demo 其他用到的依賴，分別是 Junit 測試、Lombok、MyBatis Plus、Druid 連接池、MySQL 驅動、Java Faker 數據生成器：

<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.13.2</version><scope>test</scope></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><!-- MyBatis Plus --><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.4.3.1</version></dependency><!-- Druid --><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.2.6</version></dependency><!-- MySQL --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><!-- Sharding-jdbc --><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>4.1.1</version></dependency><!-- Data Faker --><dependency><groupId>com.github.javafaker</groupId><artifactId>javafaker</artifactId><version>1.0.2</version></dependency></dependencies>Sharding-JDBC 配置可選配置

啟用 SQL 打印：

spring.shardingsphere.props.sql.show = true數據源配置

總共有 t_order_1、t_order_2 和 t_user 三個庫，加上單主單從的復制，因此有 6 個數據庫，需要配置六個數據源：

# Datasource Definespring.shardingsphere.datasource.names = o1-master,o2-master,o1-slave,o2-slave,u-master,u-slave# datasource o1-masterspring.shardingsphere.datasource.o1-master.type = com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.o1-master.driver-class-name = com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.o1-master.url = jdbc:mysql://localhost:3307/db_order_1?useUnicode=truespring.shardingsphere.datasource.o1-master.username = rootspring.shardingsphere.datasource.o1-master.password = 123456# datasource o1-slavespring.shardingsphere.datasource.o1-slave.type = com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.o1-slave.driver-class-name = com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.o1-slave.url = jdbc:mysql://localhost:3308/db_order_1?useUnicode=truespring.shardingsphere.datasource.o1-slave.username = rootspring.shardingsphere.datasource.o1-slave.password = 123456# datasource o2-masterspring.shardingsphere.datasource.o2-master.type = com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.o2-master.driver-class-name = com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.o2-master.url = jdbc:mysql://localhost:3307/db_order_2?useUnicode=truespring.shardingsphere.datasource.o2-master.username = rootspring.shardingsphere.datasource.o2-master.password = 123456# datasource o2-slavespring.shardingsphere.datasource.o2-slave.type = com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.o2-slave.driver-class-name = com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.o2-slave.url = jdbc:mysql://localhost:3308/db_order_2?useUnicode=truespring.shardingsphere.datasource.o2-slave.username = rootspring.shardingsphere.datasource.o2-slave.password = 123456# datasource u-masterspring.shardingsphere.datasource.u-master.type = com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.u-master.driver-class-name = com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.u-master.url = jdbc:mysql://localhost:3307/db_user?useUnicode=truespring.shardingsphere.datasource.u-master.username = rootspring.shardingsphere.datasource.u-master.password = 123456# datasource u-slavespring.shardingsphere.datasource.u-slave.type = com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.u-slave.driver-class-name = com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.u-slave.url = jdbc:mysql://localhost:3308/db_user?useUnicode=truespring.shardingsphere.datasource.u-slave.username = rootspring.shardingsphere.datasource.u-slave.password = 123456主從復制配置

主從配置不需要聲明，在定義時會自動讀取 key 中的主從配置庫作為邏輯庫，如下面的 db-order-1。

# Replication Definespring.shardingsphere.sharding.master-slave-rules.db-order-1.master-data-source-name=o1-masterspring.shardingsphere.sharding.master-slave-rules.db-order-1.slave-data-source-names=o1-slavespring.shardingsphere.sharding.master-slave-rules.db-order-2.master-data-source-name=o2-masterspring.shardingsphere.sharding.master-slave-rules.db-order-2.slave-data-source-names=o2-slavespring.shardingsphere.sharding.master-slave-rules.db-user.master-data-source-name=u-masterspring.shardingsphere.sharding.master-slave-rules.db-user.slave-data-source-names=u-slave數據節點配置

數據節點，指的是每張數據表，由于存在分庫、分表、全局的不同類型，因此數據節點也有不同類型。注意，由于我們進行了主從復制，因此這里的數據庫不能直接填數據源的名稱，應該填在主從復制配置的 Key 中定義的名稱，如 db-user 而不是 u-master 或 u-slave。

全局表：

# BroadCast Tablespring.shardingsphere.sharding.broadcast-tables = t_dict

單庫單表：key-generator.column 設置主鍵列。key-generator.type 設置主鍵生成類型，這里使用雪花算法，其實沒必要因為不是分表的，但是不填也會默認使用這個。

# Data Node t_userspring.shardingsphere.sharding.tables.t_user.actual-data-nodes = db-user.t_userspring.shardingsphere.sharding.tables.t_user.key-generator.column = idspring.shardingsphere.sharding.tables.t_user.key-generator.type = SNOWFLAKE

分庫分表：在 actual-data-nodes 中使用 groovy 表達式進行設置。在 database-strategy 中設置切分方式，具體自查，暫沒時間寫。

# Data Node t_order, If there is not master-salve-replication, use datasource name like 'o$-master->{1..2}.t_order_$->{1..2}'spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = db-order-$->{1..2}.t_order_$->{1..2}spring.shardingsphere.sharding.tables.t_order.key-generator.column = idspring.shardingsphere.sharding.tables.t_order.key-generator.type = SNOWFLAKE# database sharding strategyspring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column = user_idspring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression = db-order-$->{user_id % 2 + 1}# table sharding strategyspring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column = idspring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{id % 2 + 1}Demo 程序

參見：zoharyips/sharding-jdbc-demo (github.com)

到此這篇關于Java使用Sharding-JDBC分庫分表進行操作的文章就介紹到這了,更多相關Java Sharding-JDBC分庫分表內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！