一、业务场景介绍:把全国27个大的省市的销售中心按行政区划码分成27个库,每个库的订单和订单item再按年分表,最后我们通过sharding-poxy来实现uat、sit等环境的配置;我们把sharding-poxy的注册和配置信息注册到zookeeper上,最后通过sharding-ui提供的可视化界面来对sharding-poxy多套配置文件来进行实时管理。
二、基本环境搭建(docker compose、Zookeeper、Mysql、sharding-poxy、sharding-ui)
1.docker compose安装:首先docker compose依赖于docker(可以参考前面的文章前8小步进行安装:docker快速搭建测试数据库(Mysql、Oracle、SqlServer)及ftp文件服务器_Morik的博客-CSDN博客),以下是安装docker compose的命令
#安装 下载失败就多下两次试试
[root@localhost ~]# curl -L https://github.com/docker/compose/releases/download/1.29.2/docker-compose-`uname -s`-`uname -m` > /usr/local/bin/docker-compose
#给执行权限
[root@localhost ~]# sudo chmod +x /usr/local/bin/docker-compose
#查看版本
[root@localhost ~]# docker-compose --version
#打开数据库端口
[root@localhost ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
#打开zookeeper端口
[root@localhost ~]# firewall-cmd --zone=public --add-port=2181/tcp --permanent
#刷新防火墙
[root@localhost ~]# firewall-cmd --reload
#查看防火墙打开的端口列表
[root@localhost ~]# firewall-cmd --list-ports
2.通过docker compose快速搭建zookeeper和mysql
1)、新建sharding-poxy-env.yml文件并将以下配置拷贝到yml文件中(根据实际情况可以只要mysql和zookeeper的配置,nacos、seata、redis可以选择性的删除)
version: '3'
services:
mysql:
image: mysql:5.7
container_name: mysql
command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
restart: always
environment:
MYSQL_ROOT_PASSWORD: root
ports:
- 3306:3306
volumes:
- /mydata/mysql/data/db:/var/lib/mysql
- /mydata/mysql/data/conf:/etc/mysql/conf.d
- /mydata/mysql/log:/var/log/mysql
redis:
image: redis:5.0
container_name: redis
command: redis-server --appendonly yes
volumes:
- /mydata/redis/data:/data
ports:
- 6379:6379
nacos:
image: nacos/nacos-server:1.4.2
container_name: nacos
environment:
- MODE=standalone
volumes:
- /mydata/nacos/logs/:/home/nacos/logs
ports:
- "8848:8848"
zookeeper:
image: zookeeper:3.5
ports:
- 2181:2181
volumes:
- /mydata/zookeeper/data:/zk/data
- /mydata/zookeeper/conf:/zk/conf
seata-server:
image: seataio/seata-server:1.2.0
ports:
- "8091:8091"
2)、进入yml目录运行(docker-compose -f sharding-poxy-env.yml up -d)
3)、连接mysql并新建两个库再导入sql测试数据脚本(如果连接不上前几章有具体的安装配置mysql详情)
/*
Navicat Premium Data Transfer
Source Server : sd
Source Server Type : MySQL
Source Server Version : 50734
Source Host : 192.168.0.4:3306
Source Schema : a11000000
Target Server Type : MySQL
Target Server Version : 50734
File Encoding : 65001
Date: 19/07/2021 20:08:27
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_order_2019
-- ----------------------------
DROP TABLE IF EXISTS `t_order_2019`;
CREATE TABLE `t_order_2019` (
`order_id` int(11) NOT NULL,
`order_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`creat_time` datetime(0) NULL DEFAULT NULL,
`yearmonth` int(255) NULL DEFAULT NULL,
`dealerid` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_order_2019
-- ----------------------------
INSERT INTO `t_order_2019` VALUES (2, '8', '2021-07-07 22:08:47', 201902, 11000000);
-- ----------------------------
-- Table structure for t_order_2020
-- ----------------------------
DROP TABLE IF EXISTS `t_order_2020`;
CREATE TABLE `t_order_2020` (
`order_id` int(11) NOT NULL,
`order_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`creat_time` datetime(0) NULL DEFAULT NULL,
`yearmonth` int(255) NULL DEFAULT NULL,
`dealerid` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_order_2020
-- ----------------------------
INSERT INTO `t_order_2020` VALUES (2, '8', '2021-07-07 22:08:47', 202003, 11000000);
-- ----------------------------
-- Table structure for t_order_2021
-- ----------------------------
DROP TABLE IF EXISTS `t_order_2021`;
CREATE TABLE `t_order_2021` (
`order_id` int(11) NOT NULL,
`order_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`creat_time` datetime(0) NULL DEFAULT NULL,
`yearmonth` int(255) NULL DEFAULT NULL,
`dealerid` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_order_2021
-- ----------------------------
INSERT INTO `t_order_2021` VALUES (2, '8', '2021-07-07 22:08:47', 202101, 11000000);
-- ----------------------------
-- Table structure for t_order_item_202001
-- ----------------------------
DROP TABLE IF EXISTS `t_order_item_202001`;
CREATE TABLE `t_order_item_202001` (
`order_id` int(11) NOT NULL,
`order_item_id` int(11) NULL DEFAULT NULL,
`order_item_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`yearmonth` int(255) NULL DEFAULT NULL,
`dealerid` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Table structure for test1
-- ----------------------------
DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
`id` int(11) NOT NULL,
`sex123` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
/*
Navicat Premium Data Transfer
Source Server : sd
Source Server Type : MySQL
Source Server Version : 50734
Source Host : 192.168.0.4:3306
Source Schema : a50000000
Target Server Type : MySQL
Target Server Version : 50734
File Encoding : 65001
Date: 19/07/2021 20:08:39
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_order_2019
-- ----------------------------
DROP TABLE IF EXISTS `t_order_2019`;
CREATE TABLE `t_order_2019` (
`order_id` int(11) NOT NULL,
`order_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`creat_time` datetime(0) NULL DEFAULT NULL,
`yearmonth` int(255) NULL DEFAULT NULL,
`dealerid` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_order_2019
-- ----------------------------
INSERT INTO `t_order_2019` VALUES (4, '10', '2021-07-08 08:52:15', 201902, 50000000);
-- ----------------------------
-- Table structure for t_order_2020
-- ----------------------------
DROP TABLE IF EXISTS `t_order_2020`;
CREATE TABLE `t_order_2020` (
`order_id` int(11) NOT NULL,
`order_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`creat_time` datetime(0) NULL DEFAULT NULL,
`yearmonth` int(255) NULL DEFAULT NULL,
`dealerid` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_order_2020
-- ----------------------------
INSERT INTO `t_order_2020` VALUES (4, '10', '2021-07-08 08:52:15', 202002, 50000000);
-- ----------------------------
-- Table structure for t_order_2021
-- ----------------------------
DROP TABLE IF EXISTS `t_order_2021`;
CREATE TABLE `t_order_2021` (
`order_id` int(11) NOT NULL,
`order_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`creat_time` datetime(0) NULL DEFAULT NULL,
`yearmonth` int(255) NULL DEFAULT NULL,
`dealerid` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_order_2021
-- ----------------------------
INSERT INTO `t_order_2021` VALUES (4, '10', '2021-07-08 08:52:15', 202102, 50000000);
-- ----------------------------
-- Table structure for t_order_item_202102
-- ----------------------------
DROP TABLE IF EXISTS `t_order_item_202102`;
CREATE TABLE `t_order_item_202102` (
`order_id` int(11) NOT NULL,
`order_item_id` int(11) NULL DEFAULT NULL,
`order_item_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`yearmonth` int(255) NULL DEFAULT NULL,
`dealerid` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`order_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
三、源码、sharding-poxy、sharding-ui简单配置
1)、下载地址:下载 :: ShardingSphere
2)、补全sharding-poxy、sharding-ui 的lib文件夹下的.jar后缀,并拷贝连接mysql的jdbc驱动到poxy的lib目录下
3)、配置sharding-poxy的cofig下的server.yaml和config-sharding.yaml
#server.yaml配置参考
#config-sharding.yaml 配置参考
schemaName: sharding_db
dataSources:
a11000000:
url: jdbc:mysql://192.168.0.4:3306/a11000000?serverTimezone=UTC&useSSL=true&useUnicode=true&characterEncoding=UTF-8
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
a50000000:
url: jdbc:mysql://192.168.0.4:3306/a50000000?serverTimezone=UTC&useSSL=true&useUnicode=true&characterEncoding=UTF-8
username: root
password: root
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
shardingRule:
tables:
t_order:
actualDataNodes: a${[11000000,50000000]}.t_order_${2019..2021}
tableStrategy:
inline:
shardingColumn: yearmonth
algorithmExpression: t_order_${yearmonth[0..3]}
keyGenerator:
type: SNOWFLAKE
column: yearmonth
t_order_item:
actualDataNodes: a${[11000000,50000000]}.t_order_item_${2019..2021}
tableStrategy:
inline:
shardingColumn: yearmonth
algorithmExpression: t_order_item_${yearmonth[0..3]}
keyGenerator:
type: SNOWFLAKE
column: yearmonth
bindingTables:
- t_order,t_order_item
defaultDatabaseStrategy:
inline:
shardingColumn: dealerid
algorithmExpression: a${dealerid}
defaultTableStrategy:
none:
4)、连接测试下
5)、sharding-ui简单配置
#放开sharding-poxy的server.yaml注释并配置zookeeper
#启动sharding-ui添加注册中心、配置中心