Mysql学习笔记：ShardingSphere-Proxy分库分表+读写分离从零搭建指南
一、背景：为什么需要分布式数据库
单个 MySQL 实例在数据量达到千万级、QPS 达到数千时，会面临明显的瓶颈：
- 写入瓶颈：单库的写入能力有限，无法水平扩展
- 读取瓶颈：单库扛不住大量并发查询
- 存储瓶颈：单表数据量过大时，索引维护成本急剧上升
ShardingSphere 就是为解决这些问题而生——它是一套开源的分布式数据库中间件，提供分库分表、读写分离、数据加密、分布式事务等能力。
二、ShardingSphere 核心概念
ShardingSphere 提供两种部署模式：
| 模式 | 特点 | 适用场景 |
|---|---|---|
| JDBC | 嵌入应用内，轻量级 | 小型项目、单体应用 |
| Proxy | 独立服务，兼容 MySQL 协议 | 多应用共享、复杂架构 |
本文采用 Proxy 模式 部署。
分片策略
ShardingSphere 支持多种分片算法：
- MOD（取模）：
user_id % 2决定数据去哪个库 - HASH_MOD：哈希取模，分布更均匀
- INLINE：通过 Groovy 表达式自定义路由
- INTERVAL：按时间范围分片
本项目使用 INLINE 表达式实现双层分片：
user_id % 2 → 决定去哪个集群（分库）
order_id % 2 → 决定去集群内的哪张表（分表）
三、架构设计
客户端 → ShardingSphere-Proxy (22226)
│
┌───────┴───────┐
│ │
ds_0(偶数) ds_1(奇数)
│ │
┌───┼───┐ ┌───┼───┐
│ │ │ │ │ │
Master Slave1 Slave2 Master Slave1 Slave2
(22220)(22221)(22222) (22223)(22224)(22225)
分片规则：
user_id % 2 == 0→ Cluster A（ds_0）user_id % 2 == 1→ Cluster B（ds_1）order_id % 2 == 0→t_order_0order_id % 2 == 1→t_order_1
四、环境准备
| 工具 | 版本 |
|---|---|
| Docker Desktop | 最新版 |
| Docker Compose | V2 |
| MySQL | 8.0 |
| ShardingSphere-Proxy | 5.5.3 |
| OS | Windows 11 + WSL2 |
五、集群部署实战
5.1 容器规划
需要创建 8 个容器：
| 容器名 | 角色 | 端口映射 |
|---|---|---|
mysql_a_master |
Cluster A 主库 | 22220:3306 |
mysql_a_slave1 |
Cluster A 从库1 | 22221:3306 |
mysql_a_slave2 |
Cluster A 从库2 | 22222:3306 |
mysql_b_master |
Cluster B 主库 | 22223:3306 |
mysql_b_slave1 |
Cluster B 从库1 | 22224:3306 |
mysql_b_slave2 |
Cluster B 从库2 | 22225:3306 |
sharding-proxy |
统一入口 | 22226:3307 |
setup |
初始化（一次性） | - |
5.2 项目文件结构
在开始之前，先创建好目录结构：
sharding-lab/
├── docker-compose.yml
├── sharding-proxy/
│ ├── Dockerfile
│ ├── global.yaml
│ └── database-sharding.yaml
└── scripts/
└── init.sh
5.3 编写 Docker Compose
完整的 docker-compose.yml：
version: "3.8"
networks:
sharding-net:
driver: bridge
x-mysql: &mysql
image: mysql:8.0
environment:
MYSQL_ROOT_PASSWORD: root123
networks:
- sharding-net
services:
# ====================== Cluster A ======================
# ---- Master 参数说明 ----
# --server-id: 集群内唯一编号，主从节点必须不同
# --log-bin: 开启二进制日志，仅 Master 需要
# --gtid_mode: 启用 GTID 复制，简化主从位置管理
# --binlog-do-db: 只记录指定库的 binlog
mysql_a_master:
<<: *mysql
container_name: mysql_a_master
ports: ["22220:3306"]
command:
--server-id=1 --log-bin=mysql-bin --binlog-format=ROW
--gtid_mode=ON --enforce-gtid-consistency=ON
--binlog-do-db=sharding_db
# ---- Slave 参数说明 ----
# --server-id: 与 Master 不同即可，无大小顺序
# --relay-log: 接收 Master 的 binlog 后写入 relay log
# --skip-slave-start: 启动时不自动复制，由 init.sh 控制
# --gtid_mode: Slave 也要开启，才能使用 MASTER_AUTO_POSITION
mysql_a_slave1:
<<: *mysql
container_name: mysql_a_slave1
ports: ["22221:3306"]
command:
--server-id=2 --relay-log=relay-log --skip-slave-start=ON
--gtid_mode=ON --enforce-gtid-consistency=ON
mysql_a_slave2:
<<: *mysql
container_name: mysql_a_slave2
ports: ["22222:3306"]
command:
--server-id=3 --relay-log=relay-log --skip-slave-start=ON
--gtid_mode=ON --enforce-gtid-consistency=ON
# ====================== Cluster B ======================
mysql_b_master:
<<: *mysql
container_name: mysql_b_master
ports: ["22223:3306"]
command:
--server-id=4 --log-bin=mysql-bin --binlog-format=ROW
--gtid_mode=ON --enforce-gtid-consistency=ON
--binlog-do-db=sharding_db
mysql_b_slave1:
<<: *mysql
container_name: mysql_b_slave1
ports: ["22224:3306"]
command:
--server-id=5 --relay-log=relay-log --skip-slave-start=ON
--gtid_mode=ON --enforce-gtid-consistency=ON
mysql_b_slave2:
<<: *mysql
container_name: mysql_b_slave2
ports: ["22225:3306"]
command:
--server-id=6 --relay-log=relay-log --skip-slave-start=ON
--gtid_mode=ON --enforce-gtid-consistency=ON
# ====================== ShardingSphere-Proxy ======================
sharding-proxy:
build: ./sharding-proxy
container_name: sharding-proxy
ports: ["22226:3307"]
volumes:
- ./sharding-proxy/global.yaml:/opt/shardingsphere-proxy/conf/global.yaml
- ./sharding-proxy/database-sharding.yaml:/opt/shardingsphere-proxy/conf/database-sharding.yaml
depends_on:
- mysql_a_master
- mysql_a_slave1
- mysql_a_slave2
- mysql_b_master
- mysql_b_slave1
- mysql_b_slave2
networks: [sharding-net]
# ====================== 初始化 ======================
setup:
image: mysql:8.0
container_name: sharding-setup
entrypoint: [""]
command: ["bash", "/scripts/init.sh"]
depends_on:
- mysql_a_master
- mysql_a_slave1
- mysql_a_slave2
- mysql_b_master
- mysql_b_slave1
- mysql_b_slave2
volumes:
- ./scripts/init.sh:/scripts/init.sh
networks: [sharding-net]
Proxy 官方镜像不包含 MySQL JDBC 驱动，需通过 Dockerfile 额外添加：
FROM apache/shardingsphere-proxy:latest
ADD https://repo1.maven.org/maven2/com/mysql/mysql-connector-j/8.0.33/mysql-connector-j-8.0.33.jar \
/opt/shardingsphere-proxy/ext-lib/mysql-connector-j-8.0.33.jar
EXPOSE 3307
原因：ShardingSphere 官方镜像默认只包含 PostgreSQL 和 openGauss 驱动，MySQL 驱动需用户自行提供。详见官方文档。
5.4 配置 ShardingSphere-Proxy
Proxy 使用两个配置文件：
global.yaml — 认证与全局配置：
mode:
type: Standalone # 单机模式，无需注册中心
authority:
users:
- user: root@% # 用户@主机，% 表示任意主机
password: root123
privilege:
type: ALL_PERMITTED # 所有用户拥有全部权限
props:
proxy-frontend-port: 3307 # Proxy 监听端口
sql-show: true # 打印实际路由 SQL，调试用
database-sharding.yaml — 分片与读写分离规则：
databaseName: sharding_db # 逻辑数据库名，客户端连接用
dataSources:
# ===== Cluster A (ds_0) =====
ds_0_master: # A 集群写入节点
url: jdbc:mysql://mysql_a_master:3306/sharding_db?useSSL=false&allowPublicKeyRetrieval=true
username: root
password: root123
ds_0_slave1: # A 集群读节点 1
url: jdbc:mysql://mysql_a_slave1:3306/sharding_db?useSSL=false&allowPublicKeyRetrieval=true
username: root
password: root123
ds_0_slave2: # A 集群读节点 2
url: jdbc:mysql://mysql_a_slave2:3306/sharding_db?useSSL=false&allowPublicKeyRetrieval=true
username: root
password: root123
# ===== Cluster B (ds_1) =====
ds_1_master: # B 集群写入节点
url: jdbc:mysql://mysql_b_master:3306/sharding_db?useSSL=false&allowPublicKeyRetrieval=true
username: root
password: root123
ds_1_slave1: # B 集群读节点 1
url: jdbc:mysql://mysql_b_slave1:3306/sharding_db?useSSL=false&allowPublicKeyRetrieval=true
username: root
password: root123
ds_1_slave2: # B 集群读节点 2
url: jdbc:mysql://mysql_b_slave2:3306/sharding_db?useSSL=false&allowPublicKeyRetrieval=true
username: root
password: root123
rules:
# ===== 分片规则 =====
- !SHARDING
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1} # 2库 × 2表
databaseStrategy: # 分库策略
standard:
shardingColumn: user_id # 按 user_id 分库
shardingAlgorithmName: db_inline # 引用下方 db_inline 算法
tableStrategy: # 分表策略
standard:
shardingColumn: order_id # 按 order_id 分表
shardingAlgorithmName: table_inline # 引用下方 table_inline 算法
keyGenerateStrategy: # 分布式主键生成策略
column: order_id # 对哪列生成主键
keyGeneratorName: snowflake # 使用雪花算法
shardingAlgorithms:
db_inline:
type: INLINE # INLINE = Groovy 表达式路由
props:
algorithm-expression: ds_${user_id % 2} # 偶数→ds_0，奇数→ds_1
table_inline:
type: INLINE
props:
algorithm-expression: t_order_${order_id % 2} # 偶数→t_order_0,奇数→t_order_1
keyGenerators:
snowflake:
type: SNOWFLAKE # 雪花算法，生成分布式唯一 ID
# ===== 读写分离 =====
- !READWRITE_SPLITTING
dataSourceGroups:
ds_0: # Cluster A
writeDataSourceName: ds_0_master # 写操作→Master
readDataSourceNames: [ds_0_slave1, ds_0_slave2] # 读操作→轮询 Slave
loadBalancerName: round_robin # Slave 间轮询负载均衡
ds_1: # Cluster B
writeDataSourceName: ds_1_master # 写操作→Master
readDataSourceNames: [ds_1_slave1, ds_1_slave2] # 读操作→轮询 Slave
loadBalancerName: round_robin
loadBalancers:
round_robin:
type: ROUND_ROBIN # 轮询算法
5.5 初始化脚本
setup 容器会自动执行 scripts/init.sh，内容如下：
#!/bin/bash
set -e
PASS=root123
# 1. 等待全部 MySQL 就绪
for host in mysql_a_master mysql_a_slave1 mysql_a_slave2 \
mysql_b_master mysql_b_slave1 mysql_b_slave2; do
until mysql -h "$host" -uroot -p"$PASS" -e "SELECT 1" &>/dev/null; do
sleep 2
done
echo " ✓ $host"
done
# 2. 创建数据库（全部节点）
for host in mysql_a_master mysql_a_slave1 mysql_a_slave2 \
mysql_b_master mysql_b_slave1 mysql_b_slave2; do
mysql -h "$host" -uroot -p"$PASS" -e "CREATE DATABASE IF NOT EXISTS sharding_db;"
done
# 3. 创建复制用户（必须用 mysql_native_password）
for host in mysql_a_master mysql_b_master; do
mysql -h "$host" -uroot -p"$PASS" -e "
CREATE USER IF NOT EXISTS 'repl'@'%'
IDENTIFIED WITH mysql_native_password BY 'repl123';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;"
done
# 4. 设置 GTID 主从复制
SETUP_SLAVE() {
local slave=$1 master=$2
mysql -h "$slave" -uroot -p"$PASS" -e "
STOP SLAVE; RESET SLAVE ALL;
CHANGE MASTER TO MASTER_HOST='$master', MASTER_PORT=3306,
MASTER_USER='repl', MASTER_PASSWORD='repl123',
MASTER_AUTO_POSITION=1;
START SLAVE;"
IO=$(mysql -h "$slave" -uroot -p"$PASS" -e "SHOW SLAVE STATUS\G" \
| grep "Slave_IO_Running:" | awk '{print $2}')
SQL=$(mysql -h "$slave" -uroot -p"$PASS" -e "SHOW SLAVE STATUS\G" \
| grep "Slave_SQL_Running:" | awk '{print $2}')
echo " ✓ $slave → $master (IO:$IO SQL:$SQL)"
}
SETUP_SLAVE mysql_a_slave1 mysql_a_master
SETUP_SLAVE mysql_a_slave2 mysql_a_master
SETUP_SLAVE mysql_b_slave1 mysql_b_master
SETUP_SLAVE mysql_b_slave2 mysql_b_master
# 5. 创建分片物理表
for host in mysql_a_master mysql_b_master; do
mysql -h "$host" -uroot -p"$PASS" sharding_db -e "
CREATE TABLE IF NOT EXISTS t_order_0 (
order_id BIGINT NOT NULL, user_id INT NOT NULL,
product_name VARCHAR(100), status TINYINT DEFAULT 0,
PRIMARY KEY (order_id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS t_order_1 (
order_id BIGINT NOT NULL, user_id INT NOT NULL,
product_name VARCHAR(100), status TINYINT DEFAULT 0,
PRIMARY KEY (order_id)) ENGINE=InnoDB;"
done
echo "初始化完成！"
5.6 启动集群
在 sharding-lab/ 目录下执行启动命令：
cd sharding-lab
docker compose up -d --build
# 查看初始化进度
docker logs sharding-setup -f
# 预期输出（约 1-2 分钟后）
✓ mysql_a_master
✓ mysql_a_slave1
...
✓ mysql_b_slave2
✓ mysql_a_slave1 → mysql_a_master (IO:Yes SQL:Yes)
...
初始化完成！
# 确认 Proxy 启动成功
docker logs sharding-proxy | grep "started successfully"
# 输出: ShardingSphere-Proxy Standalone mode started successfully
六、功能验证
6.1 分片验证
-- 通过 Proxy 插入数据
INSERT INTO t_order (order_id, user_id, product_name, status) VALUES
(1, 1, '商品A', 1),
(2, 2, '商品B', 1),
(3, 3, '商品C', 1),
(4, 4, '商品D', 1);
-- 查询
SELECT * FROM t_order ORDER BY order_id;
分片结果：
直连 MySQL 查看物理表，数据分布符合预期：
-- Cluster A (ds_0): user_id 偶数的数据
mysql> SELECT order_id, user_id, product_name FROM t_order_0;
+----------+---------+--------------+
| order_id | user_id | product_name |
+----------+---------+--------------+
| 2 | 2 | 商品B |
| 4 | 4 | 商品D |
+----------+---------+--------------+
-- Cluster B (ds_1): user_id 奇数的数据
mysql> SELECT order_id, user_id, product_name FROM t_order_1;
+----------+---------+--------------+
| order_id | user_id | product_name |
+----------+---------+--------------+
| 1 | 1 | 商品A |
| 3 | 3 | 商品C |
+----------+---------+--------------+
| Cluster | user_id | 物理表 | 数据 |
|---|---|---|---|
| ds_0 | 偶数（2, 4） | t_order_0 |
商品B, 商品D |
| ds_1 | 奇数（1, 3） | t_order_1 |
商品A, 商品C |
6.2 读写分离验证
从 Proxy 日志可以看到 SQL 路由情况：
INSERT → ds_0_master, ds_1_master # 写操作走到 Master
SELECT → ds_0_slave1, ds_1_slave2... # 读操作走到 Slave
读写分离规则：
INSERT、UPDATE、DELETE→ 自动路由到 MasterSELECT→ 轮询分发到 Slave（round_robin 策略）- 某个 Slave 宕机 → 自动跳过，不影响服务
6.3 纵向扩展示例
当需要增加分片数时，只需修改配置：
actualDataNodes: ds_${0..2}.t_order_${0..3} # 3库 × 4表
db_inline:
algorithm-expression: ds_${user_id % 3} # 分3库
table_inline:
algorithm-expression: t_order_${order_id % 4} # 每库4表
七、踩坑记录
坑1：MySQL 驱动缺失
现象： No suitable driver
原因： ShardingSphere 官方镜像只内置了 PostgreSQL/openGauss 驱动，MySQL 驱动需要用户自行添加
解决： 通过 Dockerfile 将 mysql-connector-j-8.0.33.jar 下载到 /opt/shardingsphere-proxy/ext-lib/
坑2：配置文件命名变更
现象： 配置文件加载失败，认证配置失效
原因： 5.5.x 起改用 global.yaml + database-*.yaml，不再支持 server.yaml + config-*.yaml
解决： 使用正确文件名
坑3：复制用户认证方式
现象： Slave_IO_Running: Connecting，错误 Authentication requires secure connection
原因： MySQL 8.0 默认 caching_sha2_password，复制需要 SSL 或改用 mysql_native_password
解决： CREATE USER ... IDENTIFIED WITH mysql_native_password BY '...'
坑4：Public Key Retrieval 错误
现象： Proxy 启动时 Public Key Retrieval is not allowed
原因： JDBC 连接 MySQL 8.0 不带 SSL 时需要显式允许公钥检索
解决： JDBC URL 加参数 allowPublicKeyRetrieval=true
八、总结
通过本文的实战，你完成了以下目标：
| 目标 | 状态 |
|---|---|
| 理解分库分表原理 | ✅ |
| 搭建 2 集群 × 3 节点 MySQL 集群 | ✅ |
| 配置 GTID 主从复制 | ✅ |
| 部署 ShardingSphere-Proxy | ✅ |
| 实现分片（分库 + 分表） | ✅ |
| 实现读写分离 | ✅ |
| 了解常见坑及解决方案 | ✅ |
浙公网安备 33010602011771号