Docker MySQL 單主從及分表函數(shù)的解決方案
更新時(shí)間：2025年10月28日 17:15:02 作者：小小的木頭人
本文介紹了MySQL單主從配置和按月分表兩種方案,本文結(jié)合實(shí)例代碼給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友參考下吧
一、MySQL 單主從
1.創(chuàng)建網(wǎng)絡(luò)
docker network create --driver bridge mysql-net
2.配置文件 my.cnf
注意文件權(quán)限改為 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節(jié)點(diǎn)不能一樣 binlog_format = ROW # STATEMENT、ROW、MIXED #log-bin = /home/mysql/mybilong/mysql-bin.log #開啟二進(jìn)制日志 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 #最大連接數(shù) 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: true4. 修改賬號(hào)認(rèn)證方式
ALTER USER 'rec'@'%' IDENTIFIED WITH mysql_native_password BY 'repassword';
5. 從節(jié)點(diǎn)執(zhí)行
docker exec -it slave01 /bin/bash # 進(jìn)入 slave01 容器
mysql -uroot -p # 登錄到 MySQL 數(shù)據(jù)庫(kù)
-- 步驟 1：停止從庫(kù)復(fù)制進(jìn)程
STOP SLAVE;
-- 步驟 2：重置從庫(kù)復(fù)制設(shè)置，清除之前的配置
RESET SLAVE ALL;
-- 步驟 3：應(yīng)用修正后的 CHANGE MASTER TO 命令
CHANGE MASTER TO
MASTER_HOST='192.168.34.18', # 主庫(kù)的 IP 地址
MASTER_USER='re', # 用于復(fù)制的用戶名
MASTER_PASSWORD='repassword', # 復(fù)制用戶的密碼
MASTER_PORT=3316, # 主庫(kù)端口
MASTER_LOG_FILE='binlog.000002', # 主庫(kù)的二進(jìn)制日志文件（可以通過 `show master status` 查詢）
MASTER_LOG_POS=157; # 主庫(kù)日志位置（通過 `show master status` 查詢）
-- 步驟 4：?jiǎn)?dòng)從庫(kù)復(fù)制進(jìn)程
START SLAVE;
-- 步驟 5：驗(yàn)證從庫(kù)狀態(tài)
SHOW SLAVE STATUS\G; # 查看從庫(kù)狀態(tài)信息
-- 另外，MySQL 8.0+ 的命令，用于啟動(dòng)復(fù)制進(jìn)程
start replica; # 啟動(dòng)復(fù)制
-- 查看復(fù)制狀態(tài)
show replica status; # 顯示復(fù)制狀態(tài)6. 數(shù)據(jù)同步測(cè)試
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 各節(jié)點(diǎn)，觀察同步數(shù)據(jù)是否有異常
二、按月分表（user_YYYYMM）
- 插入數(shù)據(jù)時(shí)，先判斷當(dāng)前月份是否存在對(duì)應(yīng)的表。
- 如果不存在，就新建
user_YYYYMM表。 - 查詢時(shí)根據(jù)時(shí)間范圍定位到對(duì)應(yīng)的表。
1?? 建立模板表
先建一個(gè)模板表（用來復(fù)制結(jié)構(gòu)）：
CREATE TABLE user_template (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);2?? 存儲(chǔ)過程：按月份自動(dòng)建表并插入
DELIMITER //
CREATE PROCEDURE insert_user(IN uname VARCHAR(50))
BEGIN
DECLARE tbl VARCHAR(20);
DECLARE sql_create TEXT;
DECLARE sql_insert TEXT;
-- 計(jì)算當(dāng)前月份表名，例如 user_202509
SET tbl = CONCAT('user_', DATE_FORMAT(NOW(), '%Y%m'));
-- 如果表不存在則創(chuàng)建
SET @sql_create = CONCAT('CREATE TABLE IF NOT EXISTS ', tbl, ' LIKE user_template');
PREPARE stmt FROM @sql_create;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 插入數(shù)據(jù)
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 ;調(diào)用：
CALL insert_user('Alice');
CALL insert_user('Bob');當(dāng)月份變化時(shí)（例如 2025-10），會(huì)自動(dòng)建 user_202510。
3?? 查詢數(shù)據(jù)
查詢時(shí)根據(jù)月份決定表名：
-- 查 2025年9月數(shù)據(jù) SELECT * FROM user_202509 WHERE id = 1; -- 查 2025年10月數(shù)據(jù) 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?? 動(dòng)態(tài)查詢（自動(dòng)拼接 SQL）\r
寫一個(gè)函數(shù)：給定時(shí)間 → 返回表名。
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;? 總結(jié)：
- 插入：存儲(chǔ)過程
insert_user，自動(dòng)建表 + 插入。 - 查詢：函數(shù)
get_user_table，根據(jù)日期算表名，然后拼接 SQL。 - 跨月查詢：用
UNION ALL，或應(yīng)用層拼接 SQL 執(zhí)行。
5?? 跨月查詢 + 自動(dòng)跳過不存在的表 + 動(dòng)態(tài) WHERE 條件 + 分頁(yè) (LIMIT/OFFSET)
核心思路：
- 拼接所有目標(biāo)表的
UNION ALLSQL - 外面再包一層子查詢，統(tǒng)一做
LIMIT / OFFSET
?? 存儲(chǔ)過程（支持分頁(yè)）
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'));
-- 子區(qū)間的起止日期
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;
-- 下一個(gè)月
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 ;? 使用示例
- 查 2025-09 到 2025-12，name=‘Alice’，分頁(yè) 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);?? 注意事項(xiàng)
- 為了讓分頁(yè)穩(wěn)定，在最外層加了
ORDER BY id，這樣分頁(yè)順序是全局統(tǒng)一的。
如果想按時(shí)間分頁(yè)，可以改成ORDER BY create_time。 - 結(jié)果里加了一個(gè)
source_table字段，可以看到數(shù)據(jù)來自哪個(gè)分表。
三、數(shù)據(jù)庫(kù)分片工具對(duì)比
| 工具名稱 | 開源/商業(yè) | 支持?jǐn)?shù)據(jù)庫(kù) | 分片方式 | 讀寫分離 | 分布式事務(wù) | 高可用性 | 云原生支持 | 社區(qū)活躍度 | 適用場(chǎng)景 | 備注 |
|---|---|---|---|---|---|---|---|---|---|---|
| MyCat | 開源 | MySQL | 水平分片、垂直分片 | 支持 | 不支持 | 支持主從切換 | 一般 | 中等 | 中小型 MySQL 分片項(xiàng)目 | 配置簡(jiǎn)單，MySQL 生態(tài)依賴強(qiáng) |
| ShardingSphere | 開源 | MySQL, PostgreSQL, SQL Server 等 | 水平分片、垂直分片 | 支持 | 支持（XA/BASE） | 支持 | 良好 | 高 | Java 應(yīng)用、跨數(shù)據(jù)庫(kù)場(chǎng)景 | 提供 JDBC、Proxy、Sidecar 三種模式 |
| Vitess | 開源 | MySQL | 水平分片、垂直分片 | 支持 | 部分支持 | 支持主從切換 | 優(yōu)秀（Kubernetes） | 高 | 大規(guī)模 MySQL、云原生 | YouTube 開發(fā)，動(dòng)態(tài)重新分片 |
| TiDB | 開源 | 兼容 MySQL 協(xié)議 | 自動(dòng)分片 | 支持 | 支持（ACID） | 支持 | 優(yōu)秀 | 高 | 高并發(fā)、強(qiáng)一致性 | 內(nèi)置分布式存儲(chǔ)，NewSQL 數(shù)據(jù)庫(kù) |
| Cobar | 開源 | MySQL | 水平分片、垂直分片 | 支持 | 不支持 | 支持主從切換 | 一般 | 低（已停止維護(hù)） | 阿里巴巴生態(tài) | 不建議新項(xiàng)目使用 |
| DRDS | 商業(yè)（阿里云） | MySQL | 自動(dòng)分片 | 支持 | 支持 | 支持 | 優(yōu)秀（云托管） | - | 云端快速部署 | 無需運(yùn)維，適合云用戶 |
| ProxySQL | 開源 | MySQL | 簡(jiǎn)單分片 | 支持 | 不支持 | 支持負(fù)載均衡 | 一般 | 中等 | 輕量級(jí) MySQL 分片/讀寫分離 | 高性能代理，配置靈活 |
| KingShard | 開源 | MySQL | 水平分片 | 支持 | 不支持 | 支持 | 一般 | 低 | 輕量級(jí) MySQL 分片 | Go 語言開發(fā)，部署簡(jiǎn)單 |
| Mango | 開源 | MySQL | 水平分片 | 支持 | 不支持 | 支持 | 一般 | 低 | 小型實(shí)驗(yàn)性項(xiàng)目 | 社區(qū)支持有限，文檔較少 |
說明
- 支持?jǐn)?shù)據(jù)庫(kù): 工具適用的數(shù)據(jù)庫(kù)類型。
- 分片方式: 支持的數(shù)據(jù)庫(kù)分片方式（如水平分片、垂直分片或自動(dòng)分片）。
- 讀寫分離: 是否支持讀寫分離功能。
- 分布式事務(wù): 是否支持分布式事務(wù)（如 XA、ACID 等）。
- 高可用性: 是否提供主從切換、負(fù)載均衡等高可用特性。
- 云原生支持: 是否適配云環(huán)境（如 Kubernetes、云托管）。
- 社區(qū)活躍度: 社區(qū)維護(hù)和更新的活躍程度。
- 適用場(chǎng)景: 工具最適合的應(yīng)用場(chǎng)景。
選擇建議
- 高性能、Java 生態(tài): 推薦 ShardingSphere（Sharding-JDBC）。
- 云原生、大規(guī)模 MySQL: 推薦 Vitess。
- 強(qiáng)一致性、高并發(fā): 推薦 TiDB。
- 云端托管、無運(yùn)維: 推薦 DRDS。
- 輕量級(jí)、簡(jiǎn)單部署: 推薦 ProxySQL 或 KingShard。
- 小型項(xiàng)目: Mango 可作為實(shí)驗(yàn)性選擇。
- 舊項(xiàng)目兼容: MyCat 或 Cobar（謹(jǐn)慎使用 Cobar，因已停止維護(hù)）。
到此這篇關(guān)于Docker MySQL 單主從及分表函數(shù)的解決方案的文章就介紹到這了,更多相關(guān)docker mysql分表函數(shù)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:
相關(guān)文章
本文詳細(xì)介紹了如何使用Docker部署Nginx,包括拉取鏡像、啟動(dòng)容器、配置掛載、修改配置文件、重新加載配置文件以及配置域名2025-11-11
詳解通過Docker搭建Mysql容器+Tomcat容器連接環(huán)境
本篇文章主要介紹了通過Docker搭建Mysql容器+Tomcat容器連接環(huán)境，具有一定的參考價(jià)值，有興趣的可以了解一下。2017-01-01
這篇文章主要介紹了docker容器存放目錄磁盤空間滿了的解決,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-07-07
下載docker-compose-Linux-x86_64并上傳至CentOS7的/usr/local/bin目錄,重命名為docker-compose,添加執(zhí)行權(quán)限,最后通過版本命令測(cè)試安裝是否成功2025-08-08
在使用 Docker 管理和部署應(yīng)用程序時(shí),了解容器的 IP 地址是非常重要的,每個(gè) Docker 容器都有其自己的 IP 地址,本文將介紹幾種常用的方法,幫助您輕松查找 Docker 容器的 IP 地址,需要的朋友可以參考下2024-07-07
這篇文章主要介紹了Docker 解決下載鏡像慢的問題的相關(guān)資料,這里提供了具體實(shí)現(xiàn)方法，需要的朋友可以參考下2016-11-11
Minio是一個(gè)高性能的面向?qū)ο蟠鎯?chǔ)服務(wù),使用Go語言編寫,支持跨平臺(tái),本文介紹了如何使用Docker安裝Minio,并通過配置用戶名密碼、創(chuàng)建Bucket和上傳圖片進(jìn)行簡(jiǎn)單的存儲(chǔ)操作2024-12-12
明明代碼邏輯沒問題,部署時(shí)卻頻頻報(bào)錯(cuò),排查半天發(fā)現(xiàn)是服務(wù)器磁盤被占滿了,下面我們就來看看解決思路,希望可以幫助大家快速釋放磁盤空間2025-10-10
這篇文章主要介紹了使用docker部署hadoop集群的詳細(xì)教程,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值需要的朋友可以參考下2020-09-09
樹莓派3B+安裝64位ubuntu系統(tǒng)和docker工具的操作步驟詳解
這篇文章主要介紹了樹莓派3B+安裝64位ubuntu系統(tǒng)和docker工具,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2020-09-09