Docker MySQL 单主从及分表函数 - 实践

一、MySQL 单主从

1.创建网络

docker network create --driver bridge mysql-net

2.配置文件 my.cnf

注意文件权限改为 644，

[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
read-only = 0
server-id=1   # slave节点不能一样
binlog_format           = ROW # STATEMENT、ROW、MIXED
#log-bin                 = /home/mysql/mybilong/mysql-bin.log
#开启二进制日志
log-bin                 = /var/lib/mysql/mysql-bin
expire_logs_days        = 7
max_binlog_size         = 100m
binlog_cache_size       = 4m
max_binlog_cache_size   = 512m
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
lower_case_table_names = 1
default-time-zone = '+08:00'
#skip-grant-tables
#lower_case_table_name=1
#最大连接数
max_connections=1000
max_allowed_packet=10M
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#query_cache_type=1
#query_cache_size=125M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

3.docker-compose.yaml

# version: '3.9'
services:
mysql-master:
image: mysql:8.0.39
container_name: mysql-master
environment:
MYSQL_ROOT_PASSWORD: admin
MYSQL_DATABASE: mydatabase
MYSQL_USER: re
MYSQL_PASSWORD: repassword
ports:
- "3316:3306"
volumes:
- ./master/config:/etc/mysql/conf.d
- ./master/data:/var/lib/mysql
restart: always
networks:
- mysql-net
mysql-slave01:
image: mysql:8.0.39
container_name: mysql-slave01
environment:
MYSQL_ROOT_PASSWORD: admin
MYSQL_DATABASE: mydatabase
MYSQL_USER: re
MYSQL_PASSWORD: repassword
ports:
- "3317:3306"
volumes:
- ./slave01/config:/etc/mysql/conf.d
- ./slave01/data:/var/lib/mysql
restart: always
networks:
- mysql-net
networks:
mysql-net:
external: true

4. 修改账号认证方式

ALTER USER 'rec'@'%' IDENTIFIED WITH mysql_native_password BY 'repassword';

5. 从节点执行

docker exec -it slave01 /bin/bash  # 进入 slave01 容器
mysql -uroot -p  # 登录到 MySQL 数据库
-- 步骤 1：停止从库复制进程
STOP SLAVE;
-- 步骤 2：重置从库复制设置，清除之前的配置
RESET SLAVE ALL;
-- 步骤 3：应用修正后的 CHANGE MASTER TO 命令
CHANGE MASTER TO
MASTER_HOST='192.168.34.18',  # 主库的 IP 地址
MASTER_USER='re',  # 用于复制的用户名
MASTER_PASSWORD='repassword',  # 复制用户的密码
MASTER_PORT=3316,  # 主库端口
MASTER_LOG_FILE='binlog.000002',  # 主库的二进制日志文件（可以通过 `show master status` 查询）
MASTER_LOG_POS=157;  # 主库日志位置（通过 `show master status` 查询）
-- 步骤 4：启动从库复制进程
START SLAVE;
-- 步骤 5：验证从库状态
SHOW SLAVE STATUS\G;  # 查看从库状态信息
-- 另外，MySQL 8.0+ 的命令，用于启动复制进程
start replica;  # 启动复制
-- 查看复制状态
show replica status;  # 显示复制状态

6. 数据同步测试

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO users (username, email, password)
VALUES ('aaa', 'aaa@example.com', 'aaa'),
('bbb', 'bbb@example.com', 'bbb');

注意重启MySQL 各节点，观察同步数据是否有异常

二、按月分表（user_YYYYMM）

  • 插入数据时，先判断当前月份是否存在对应的表。
  • 如果不存在，就新建 user_YYYYMM 表。
  • 查询时根据时间范围定位到对应的表。

1️⃣ 建立模板表

先建一个模板表（用来复制结构）：

CREATE TABLE user_template (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2️⃣ 存储过程：按月份自动建表并插入

DELIMITER //
CREATE PROCEDURE insert_user(IN uname VARCHAR(50))
BEGIN
DECLARE tbl VARCHAR(20);
DECLARE sql_create TEXT;
DECLARE sql_insert TEXT;
-- 计算当前月份表名，例如 user_202509
SET tbl = CONCAT('user_', DATE_FORMAT(NOW(), '%Y%m'));
-- 如果表不存在则创建
SET @sql_create = CONCAT('CREATE TABLE IF NOT EXISTS ', tbl, ' LIKE user_template');
PREPARE stmt FROM @sql_create;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 插入数据
SET @sql_insert = CONCAT('INSERT INTO ', tbl, ' (name) VALUES (?)');
PREPARE stmt FROM @sql_insert;
SET @n = uname;
EXECUTE stmt USING @n;
DEALLOCATE PREPARE stmt;
END;
//
DELIMITER ;

调用：

CALL insert_user('Alice');
CALL insert_user('Bob');

当月份变化时（例如 2025-10），会自动建 user_202510。

3️⃣ 查询数据

查询时根据月份决定表名：

-- 查 2025年9月数据
SELECT * FROM user_202509 WHERE id = 1;
-- 查 2025年10月数据
SELECT * FROM user_202510 WHERE id = 5;

如果要跨月，可以用 UNION ALL：

SELECT * FROM user_202509 WHERE name='Alice'
UNION ALL
SELECT * FROM user_202510 WHERE name='Alice';

4️⃣ 动态查询（自动拼接 SQL）\r

写一个函数：给定时间 → 返回表名。

DELIMITER //
CREATE FUNCTION get_user_table(dt DATE) RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
RETURN CONCAT('user_', DATE_FORMAT(dt, '%Y%m'));
END;
//
DELIMITER ;

用法：

SET @tbl = get_user_table('2025-09-10');
SET @sql = CONCAT('SELECT * FROM ', @tbl, ' WHERE name="Alice"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

✅ 总结：

  • 插入：存储过程 insert_user，自动建表 + 插入。
  • 查询：函数 get_user_table，根据日期算表名，然后拼接 SQL。
  • 跨月查询：用 UNION ALL，或应用层拼接 SQL 执行。

5️⃣ 跨月查询 + 自动跳过不存在的表 + 动态 WHERE 条件 + 分页 (LIMIT/OFFSET)

核心思路：

  • 拼接所有目标表的 UNION ALL SQL
  • 外面再包一层子查询，统一做 LIMIT / OFFSET
存储过程（支持分页）
DELIMITER //
CREATE PROCEDURE query_user_range(
IN start_date DATE,
IN end_date DATE,
IN where_clause TEXT,
IN limit_num INT,
IN offset_num INT
)
BEGIN
DECLARE cur_date DATE;
DECLARE tbl VARCHAR(20);
DECLARE sql_all LONGTEXT DEFAULT '';
DECLARE sql_tmp TEXT;
DECLARE tbl_count INT;
DECLARE sql_page LONGTEXT;
DECLARE sub_start DATE;
DECLARE sub_end DATE;
-- 从起始月份第一天开始
SET cur_date = DATE_FORMAT(start_date, '%Y-%m-01');
WHILE cur_date <= end_date DO
-- 表名: user_YYYYMM
SET tbl = CONCAT('user_', DATE_FORMAT(cur_date, '%Y%m'));
-- 子区间的起止日期
SET sub_start = GREATEST(cur_date, start_date);
SET sub_end   = LEAST(LAST_DAY(cur_date), end_date);
-- 检查表是否存在
SELECT COUNT(*) INTO tbl_count
FROM information_schema.tables
WHERE table_schema = DATABASE()
AND table_name = tbl;
-- 如果表存在，拼接子查询
IF tbl_count > 0 THEN
SET sql_tmp = CONCAT(
'SELECT *, "', tbl, '" AS source_table FROM ', tbl,
' WHERE create_time >= ''', sub_start, '''',
' AND create_time <= ''', sub_end, ''''
);
IF where_clause IS NOT NULL AND where_clause <> '' THEN
SET sql_tmp = CONCAT(sql_tmp, ' AND ', where_clause);
END IF;
IF sql_all = '' THEN
SET sql_all = sql_tmp;
ELSE
SET sql_all = CONCAT(sql_all, ' UNION ALL ', sql_tmp);
END IF;
END IF;
-- 下一个月
SET cur_date = DATE_ADD(cur_date, INTERVAL 1 MONTH);
END WHILE;
-- 如果有拼好的 SQL
IF sql_all <> '' THEN
IF limit_num IS NULL OR limit_num = 0 THEN
SET @sql_page = CONCAT('SELECT * FROM (', sql_all, ') AS t ORDER BY id');
ELSE
SET @sql_page = CONCAT('SELECT * FROM (', sql_all, ') AS t ORDER BY id LIMIT ', limit_num, ' OFFSET ', offset_num);
END IF;
PREPARE stmt FROM @sql_page;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
ELSE
SELECT 'No data found (tables do not exist in given range)' AS msg;
END IF;
END;
//
DELIMITER ;
✅ 使用示例
  1. 查 2025-09 到 2025-12，name=‘Alice’，分页 10 条，从第 0 条开始：
-- 只筛选 id > 1
CALL query_user_range('2025-08-01', '2025-09-30', 'id > 1', 20, 0);
-- 筛选 name="Alice"
CALL query_user_range('2025-08-01', '2025-09-30', 'name="Alice"', 20, 0);
-- 组合条件
CALL query_user_range('2025-08-01', '2025-09-30', 'id > 1 AND name="Alice"', 20, 0);
⚠️ 注意事项
  1. 为了让分页稳定，在最外层加了 ORDER BY id，这样分页顺序是全局统一的。
    如果想按时间分页，可以改成 ORDER BY create_time。
  2. 结果里加了一个 source_table 字段，可以看到数据来自哪个分表。

三、数据库分片工具对比

工具名称开源/商业支持数据库分片方式读写分离分布式事务高可用性云原生支持社区活跃度适用场景备注
MyCat开源MySQL水平分片、垂直分片支持不支持支持主从切换一般中等中小型 MySQL 分片项目配置简单，MySQL 生态依赖强
ShardingSphere开源MySQL, PostgreSQL, SQL Server 等水平分片、垂直分片支持支持（XA/BASE）支持良好高Java 应用、跨数据库场景提供 JDBC、Proxy、Sidecar 三种模式
Vitess开源MySQL水平分片、垂直分片支持部分支持支持主从切换优秀（Kubernetes）高大规模 MySQL、云原生YouTube 开发，动态重新分片
TiDB开源兼容 MySQL 协议自动分片支持支持（ACID）支持优秀高高并发、强一致性内置分布式存储，NewSQL 数据库
Cobar开源MySQL水平分片、垂直分片支持不支持支持主从切换一般低（已停止维护）阿里巴巴生态不建议新项目使用
DRDS商业（阿里云）MySQL自动分片支持支持支持优秀（云托管）-云端快速部署无需运维，适合云用户
ProxySQL开源MySQL简单分片支持不支持支持负载均衡一般中等轻量级 MySQL 分片/读写分离高性能代理，配置灵活
KingShard开源MySQL水平分片支持不支持支持一般低轻量级 MySQL 分片Go 语言开发，部署简单
Mango开源MySQL水平分片支持不支持支持一般低小型实验性项目社区支持有限，文档较少

说明

  • 支持数据库: 工具适用的数据库类型。
  • 分片方式: 支持的数据库分片方式（如水平分片、垂直分片或自动分片）。
  • 读写分离: 是否支持读写分离功能。
  • 分布式事务: 是否支持分布式事务（如 XA、ACID 等）。
  • 高可用性: 是否提供主从切换、负载均衡等高可用特性。
  • 云原生支持: 是否适配云环境（如 Kubernetes、云托管）。
  • 社区活跃度: 社区维护和更新的活跃程度。
  • 适用场景: 工具最适合的应用场景。

选择建议

  • 高性能、Java 生态: 推荐 ShardingSphere（Sharding-JDBC）。
  • 云原生、大规模 MySQL: 推荐 Vitess。
  • 强一致性、高并发: 推荐 TiDB。
  • 云端托管、无运维: 推荐 DRDS。
  • 轻量级、简单部署: 推荐 ProxySQL 或 KingShard。
  • 小型项目: Mango 可作为实验性选择。
  • 旧项目兼容: MyCat 或 Cobar（谨慎使用 Cobar，因已停止维护）。
posted @ 2026-01-21 22:27  clnchanpin  阅读(2)  评论(0)    收藏  举报