0


【分布式微服务云原生】windows+docker+mysql5.7.44一主一从主从复制

目录

摘要:
在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;

在从库查询测试表


本文转载自: https://blog.csdn.net/u010425839/article/details/142604728
版权归原作者 Dylanioucn 所有, 如有侵权,请联系我们删除。

“【分布式微服务云原生】windows+docker+mysql5.7.44一主一从主从复制”的评论:

还没有评论