目录
摘要:
在Windows系统上通过Docker部署MySQL主从复制,以下是详细的步骤和命令,帮助你设置一主一从的MySQL复制环境。
1. 主库设置
步骤1:运行MySQL主库容器
docker run --name mysql-master
-v "D:\mysql\mysql-master\log:/var/log/mysql"
-v "D:\mysql\mysql-master\data:/var/lib/mysql"
-v "D:\mysql\mysql-master\conf\my.cnf:/etc/mysql/conf.d/my.cnf"
-e MYSQL_ROOT_PASSWORD=password@312
-p 3306:3306 -d mysql:5.7.44
注意:确保配置文件
D:\mysql\mysql-master\conf\my.cnf
的读写权限设置为只读,以避免启动容器时的权限错误。
步骤2:进入MySQL主库容器
docker exec -it mysql-master mysql -uroot -p
设置MySQL用户和权限,以便进行复制。
步骤3:配置MySQL主库
CREATEUSER'root'@'%' IDENTIFIED BY'password@312';ALTERUSER'root'@'%' IDENTIFIED BY'password@312';GRANTREPLICATION SLAVE ON*.*TO'root'@'%';
FLUSH PRIVILEGES;SHOW MASTER STATUS;
这些命令创建了一个新的用户,并授予了复制权限。
流程图:主库设置
#mermaid-svg-PdvRsi0dP8sHmmEW {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-PdvRsi0dP8sHmmEW .error-icon{fill:#552222;}#mermaid-svg-PdvRsi0dP8sHmmEW .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-PdvRsi0dP8sHmmEW .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-PdvRsi0dP8sHmmEW .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-PdvRsi0dP8sHmmEW .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-PdvRsi0dP8sHmmEW .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-PdvRsi0dP8sHmmEW .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-PdvRsi0dP8sHmmEW .marker{fill:#333333;stroke:#333333;}#mermaid-svg-PdvRsi0dP8sHmmEW .marker.cross{stroke:#333333;}#mermaid-svg-PdvRsi0dP8sHmmEW svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-PdvRsi0dP8sHmmEW .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-PdvRsi0dP8sHmmEW .cluster-label text{fill:#333;}#mermaid-svg-PdvRsi0dP8sHmmEW .cluster-label span{color:#333;}#mermaid-svg-PdvRsi0dP8sHmmEW .label text,#mermaid-svg-PdvRsi0dP8sHmmEW span{fill:#333;color:#333;}#mermaid-svg-PdvRsi0dP8sHmmEW .node rect,#mermaid-svg-PdvRsi0dP8sHmmEW .node circle,#mermaid-svg-PdvRsi0dP8sHmmEW .node ellipse,#mermaid-svg-PdvRsi0dP8sHmmEW .node polygon,#mermaid-svg-PdvRsi0dP8sHmmEW .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-PdvRsi0dP8sHmmEW .node .label{text-align:center;}#mermaid-svg-PdvRsi0dP8sHmmEW .node.clickable{cursor:pointer;}#mermaid-svg-PdvRsi0dP8sHmmEW .arrowheadPath{fill:#333333;}#mermaid-svg-PdvRsi0dP8sHmmEW .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-PdvRsi0dP8sHmmEW .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-PdvRsi0dP8sHmmEW .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-PdvRsi0dP8sHmmEW .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-PdvRsi0dP8sHmmEW .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-PdvRsi0dP8sHmmEW .cluster text{fill:#333;}#mermaid-svg-PdvRsi0dP8sHmmEW .cluster span{color:#333;}#mermaid-svg-PdvRsi0dP8sHmmEW div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-PdvRsi0dP8sHmmEW :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}
开始
运行主库容器
设置容器卷和端口映射
开始
进入MySQL容器
配置MySQL用户和权限
执行命令
CREATE USER
执行命令
GRANT REPLICATION SLAVE
执行命令
SHOW MASTER STATUS
步骤4:查看容器IP地址
docker inspect
-f '{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' mysql-master
记录主库容器的IP地址,以便从库使用。
2. 从库设置
步骤1:运行MySQL从库容器
docker run
--name mysql-slave
-v "D:\mysql\mysql-slave\log:/var/log/mysql"
-v "D:\mysql\mysql-slave\data:/var/lib/mysql"
-v "D:\mysql\mysql-slave\conf\my.cnf:/etc/mysql/conf.d/my.cnf"
-e MYSQL_ROOT_PASSWORD=password@312
-p 3307:3306 -d mysql:5.7.44
与主库设置类似,但端口映射为3307。
注意:确保配置文件
D:\mysql\mysql-slave\conf\my.cnf
的读写权限设置为只读。
步骤2:进入MySQL从库容器
docker exec -it mysql-slave mysql -uroot -p
与主库设置类似。配置从库以连接到主库。
步骤3:配置MySQL从库
CHANGE MASTER TO
MASTER_HOST='172.17.0.2',
MASTER_USER='root',
MASTER_PASSWORD='password@312',
MASTER_LOG_FILE='mysql-bin.000015',
MASTER_LOG_POS=154;START SLAVE;SHOW SLAVE STATUS;
这些命令配置从库以连接到主库,并启动复制。
流程图:从库设置
#mermaid-svg-SNsQrVu1oYeZhLKT {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-SNsQrVu1oYeZhLKT .error-icon{fill:#552222;}#mermaid-svg-SNsQrVu1oYeZhLKT .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-SNsQrVu1oYeZhLKT .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-SNsQrVu1oYeZhLKT .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-SNsQrVu1oYeZhLKT .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-SNsQrVu1oYeZhLKT .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-SNsQrVu1oYeZhLKT .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-SNsQrVu1oYeZhLKT .marker{fill:#333333;stroke:#333333;}#mermaid-svg-SNsQrVu1oYeZhLKT .marker.cross{stroke:#333333;}#mermaid-svg-SNsQrVu1oYeZhLKT svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-SNsQrVu1oYeZhLKT .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-SNsQrVu1oYeZhLKT .cluster-label text{fill:#333;}#mermaid-svg-SNsQrVu1oYeZhLKT .cluster-label span{color:#333;}#mermaid-svg-SNsQrVu1oYeZhLKT .label text,#mermaid-svg-SNsQrVu1oYeZhLKT span{fill:#333;color:#333;}#mermaid-svg-SNsQrVu1oYeZhLKT .node rect,#mermaid-svg-SNsQrVu1oYeZhLKT .node circle,#mermaid-svg-SNsQrVu1oYeZhLKT .node ellipse,#mermaid-svg-SNsQrVu1oYeZhLKT .node polygon,#mermaid-svg-SNsQrVu1oYeZhLKT .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-SNsQrVu1oYeZhLKT .node .label{text-align:center;}#mermaid-svg-SNsQrVu1oYeZhLKT .node.clickable{cursor:pointer;}#mermaid-svg-SNsQrVu1oYeZhLKT .arrowheadPath{fill:#333333;}#mermaid-svg-SNsQrVu1oYeZhLKT .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-SNsQrVu1oYeZhLKT .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-SNsQrVu1oYeZhLKT .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-SNsQrVu1oYeZhLKT .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-SNsQrVu1oYeZhLKT .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-SNsQrVu1oYeZhLKT .cluster text{fill:#333;}#mermaid-svg-SNsQrVu1oYeZhLKT .cluster span{color:#333;}#mermaid-svg-SNsQrVu1oYeZhLKT div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-SNsQrVu1oYeZhLKT :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}
开始
运行从库容器
设置容器卷和端口映射
开始
进入MySQL容器
配置MySQL从库
执行命令
CHANGE MASTER TO
执行命令
START SLAVE
执行命令
SHOW SLAVE STATUS
3. 验证主从复制
步骤1:在主库创建测试表
CREATETABLE`test`(`id`INTNOTNULLAUTO_INCREMENT,`data`TEXTNOTNULL,`created_at`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMP,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
在主库创建一个测试表。
步骤2:在从库检查数据同步
SELECT*FROM test;
在从库查询测试表,验证数据是否已同步。
流程图:验证主从复制
#mermaid-svg-dHPjcnnQdSFZhuSB {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-dHPjcnnQdSFZhuSB .error-icon{fill:#552222;}#mermaid-svg-dHPjcnnQdSFZhuSB .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-dHPjcnnQdSFZhuSB .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-dHPjcnnQdSFZhuSB .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-dHPjcnnQdSFZhuSB .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-dHPjcnnQdSFZhuSB .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-dHPjcnnQdSFZhuSB .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-dHPjcnnQdSFZhuSB .marker{fill:#333333;stroke:#333333;}#mermaid-svg-dHPjcnnQdSFZhuSB .marker.cross{stroke:#333333;}#mermaid-svg-dHPjcnnQdSFZhuSB svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-dHPjcnnQdSFZhuSB .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-dHPjcnnQdSFZhuSB .cluster-label text{fill:#333;}#mermaid-svg-dHPjcnnQdSFZhuSB .cluster-label span{color:#333;}#mermaid-svg-dHPjcnnQdSFZhuSB .label text,#mermaid-svg-dHPjcnnQdSFZhuSB span{fill:#333;color:#333;}#mermaid-svg-dHPjcnnQdSFZhuSB .node rect,#mermaid-svg-dHPjcnnQdSFZhuSB .node circle,#mermaid-svg-dHPjcnnQdSFZhuSB .node ellipse,#mermaid-svg-dHPjcnnQdSFZhuSB .node polygon,#mermaid-svg-dHPjcnnQdSFZhuSB .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-dHPjcnnQdSFZhuSB .node .label{text-align:center;}#mermaid-svg-dHPjcnnQdSFZhuSB .node.clickable{cursor:pointer;}#mermaid-svg-dHPjcnnQdSFZhuSB .arrowheadPath{fill:#333333;}#mermaid-svg-dHPjcnnQdSFZhuSB .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-dHPjcnnQdSFZhuSB .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-dHPjcnnQdSFZhuSB .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-dHPjcnnQdSFZhuSB .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-dHPjcnnQdSFZhuSB .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-dHPjcnnQdSFZhuSB .cluster text{fill:#333;}#mermaid-svg-dHPjcnnQdSFZhuSB .cluster span{color:#333;}#mermaid-svg-dHPjcnnQdSFZhuSB div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-dHPjcnnQdSFZhuSB :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;}
开始
在主库创建测试表
在从库检查数据同步
内容汇总表格
章节命令说明主库设置
docker run --name mysql-master ...
运行MySQL主库容器主库设置
docker exec -it mysql-master mysql -uroot -p
进入MySQL主库容器主库设置
CREATE USER 'root'@'%' IDENTIFIED BY 'password';
创建用户并授权主库设置
SHOW MASTER STATUS;
显示主库状态从库设置
docker run --name mysql-slave ...
运行MySQL从库容器从库设置
docker exec -it mysql-slave mysql -uroot -p
进入MySQL从库容器从库设置
CHANGE MASTER TO MASTER_HOST='IP', ...
配置从库连接到主库从库设置
START SLAVE;
启动从库复制从库设置
SHOW SLAVE STATUS;
显示从库状态验证主从复制
CREATE TABLE test ...
在主库创建测试表验证主从复制
SELECT * FROM test;
在从库查询测试表
版权归原作者 Dylanioucn 所有, 如有侵权,请联系我们删除。