MySQL实现主从同步

博主：爽爽
发布时间：2025 年 03 月 31 日
99 次浏览
暂无评论
5817字数
分类：数据库

MySQL实现主从同步

MySQL主从同步是一种常见的数据备份及高可用的解决方案

通过主从同步（主从复制）可以实现将数据从一个MySQL主服务同步到另一个MySQL从服务

主从同步原理

基本原理：

slave会从master读取binlog来进行数据同步

具体步骤：

master将数据改变记录到二进制日志（binary log）中
当slave上执行 start slave命令之后，slave会创建一个 IO 线程用来连接master，请求master中的binlog
当slave连接master时，master会创建一个 log dump 线程，用于发送 binlog 的内容。在读取 binlog 的内容的操作中，会对主节点上的 binlog 加锁，当读取完成并发送给从服务器后解锁
IO 线程接收主节点 binlog dump 进程发来的更新之后，保存到中继日志（relay log）中
slave的SQL线程，读取relay log日志，并解析成具体操作，从而实现主从操作一致，最终数据一致

一主多从配置

服务器规划：使用docker方式创建，主从服务器IP一致，端口号不一致

主服务器：容器名mysql-master，端口3306
从服务器：容器名mysql-slave1，端口3307
从服务器：容器名mysql-slave2，端口3308

注意：如果此时防火墙是开启的，则先关闭防火墙，并重启docker，否则后续安装的MySQL无法启动

#关闭docker
systemctl stop docker
#关闭防火墙
systemctl stop firewalld
#启动docker
systemctl start docker

准备主服务器

在docker中创建并启动MySQL主服务器：端口3306

docker run -d \
-p 3306:3306 \
-v /mysql/master/conf:/etc/mysql/conf.d \
-v /mysql/master/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name mysql-master \
mysql:8.0.29

创建MySQL主服务器配置文件

默认情况下MySQL的binlog日志是自动开启的，可以通过如下配置定义一些可选配置

vim /mysql/master/conf/my.cnf

配置如下内容

[mysqld]
# 服务器唯一id，默认值1
server-id=1
# 设置日志格式，默认值ROW
binlog_format=STATEMENT
# 二进制日志名，默认binlog
# log-bin=binlog
# 设置需要复制的数据库，默认复制全部数据库
#binlog-do-db=mytestdb
# 设置不需要复制的数据库
#binlog-ignore-db=mysql
#binlog-ignore-db=infomation_schema

重启MySQL容器

docker restart mysql-master

binlog格式说明

binlog\_format=STATEMENT：日志记录的是主机数据库的写指令，性能高，但是now()之类的函数以及获取系统参数的操作会出现主从数据不同步的问题
binlog\_format=ROW（默认）：日志记录的是主机数据库的写后的数据，批量操作时性能较差，解决now()或者 user()或者 @@hostname 等操作在主从机器上不一致的问题
binlog\_format=MIXED：是以上两种level的混合使用，有函数用ROW，没函数用STATEMENT，但是无法识别系统变量

binlog-ignore-db和binlog-do-db的优先级问题
0.08703112216569037.png

使用命令行登录MySQL主服务器

#进入容器：env LANG=C.UTF-8 避免容器中显示中文乱码
docker exec -it mysql-master env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p
#修改默认密码校验方式
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

主机中创建slave用户

-- 创建slave用户
CREATE USER 'slave'@'%';
-- 设置密码
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
-- 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
-- 刷新权限
FLUSH PRIVILEGES;

主机中查询master状态

执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

SHOW MASTER STATUS;

记下File和Position的值。执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

准备从服务器

可以配置多台从机slave1、slave2...，这里以配置slave1为例

在docker中创建并启动MySQL从服务器：端口3307

docker run -d \
-p 3308:3306 \
-v /mysql/slave1/conf:/etc/mysql/conf.d \
-v /mysql/slave1/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name mysql-slave1 \
mysql:8.0.29

创建MySQL从服务器配置文件

vim /mysql/slave1/conf/my.cnf

配置如下内容：

[mysqld]
# 服务器唯一id，每台服务器的id必须不同，如果配置其他从机，注意修改id
server-id=2
# 中继日志名，默认xxxxxxxxxxxx-relay-bin
#relay-log=relay-bin

重启MySQL容器

docker restart mysql-slave1

使用命令行登录MySQL从服务器

#进入容器：
docker exec -it mysql-slave1 env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p
#修改默认密码校验方式
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

在从机上配置主从关系

在从机上执行以下SQL操作

XXX.XXX.XXX.XXX填写主机ip地址

CHANGE MASTER TO MASTER_HOST='XXX.XXX.XXX.XXX', 
MASTER_USER='slave',MASTER_PASSWORD='123456', MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=1357;

启动主从同步

启动从机的复制功能，执行SQL：

START SLAVE;
-- 查看状态（不需要分号）
SHOW SLAVE STATUS\G

两个关键进程：下面两个参数都是Yes，则说明主从配置成功！

实现主从同步

在主机中执行以下SQL，在从机中查看数据库、表和数据是否已经被同步

CREATE DATABASE db_user;
USE db_user;
CREATE TABLE t_user (
 id BIGINT AUTO_INCREMENT,
 uname VARCHAR(30),
 PRIMARY KEY (id)
);
INSERT INTO t_user(uname) VALUES('zhangsan');
INSERT INTO t_user(uname) VALUES(@@hostname);

停止和重置

需要的时候，可以使用如下SQL语句

-- 在从机上执行。功能说明：停止I/O 线程和SQL线程的操作。
stop slave; 

-- 在从机上执行。功能说明：用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件。
reset slave;

-- 在主机上执行。功能说明：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。
-- 用于第一次进行搭建主从库时，进行主库binlog初始化工作；
reset master;

常见问题

问题1

启动主从同步后，常见错误是Slave\_IO\_Running： No 或者 Connecting的情况，此时查看下方的 Last\_IO\_ERROR错误日志，根据日志中显示的错误信息在网上搜索解决方案即可

典型的错误例如：Last\_IO\_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size'

解决方案：

-- 在从机停止slave
SLAVE STOP;

-- 在主机查看mater状态
SHOW MASTER STATUS;
-- 在主机刷新日志
FLUSH LOGS;
-- 再次在主机查看mater状态（会发现File和Position发生了变化）
SHOW MASTER STATUS;
-- 修改从机连接主机的SQL，并重新连接即可

问题2

启动docker容器后提示 WARNING: IPv4 forwarding is disabled. Networking will not work.

此错误，虽然不影响主从同步的搭建，但是如果想从远程客户端通过以下方式连接docker中的MySQL则没法连接

C:\Users\administrator>mysql -h 192.168.100.201 -P 3306 -u root -p

解决方案：

#修改配置文件：
vim /usr/lib/sysctl.d/00-system.conf
#追加
net.ipv4.ip_forward=1
#接着重启网络
systemctl restart network

最后修改：2025 年 03 月 31 日

如果觉得我的文章对你有用，请随意赞赏

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

评论 *

私密评论

名称 *

🎲

邮箱 *

地址

今日已经过去小时

这周已经过去天

本月已经过去天

今年已经过去个月

MySQL实现主从同步

爽爽 • 2025 年 03 月 31 日

<h1>MySQL实现主从同步</h1>MySQL主从同步是一种常见的数据备份及高可用的解决方案通过主从同步（主从复制）可以实现将数据从一个MySQL主服务同步到另一个MySQL从服务<h2>主从同步原理</h2><img src="https://webwss.cn/usr/uploads/2025/03/1270424238.png" alt="image-20220714133617856.png" title="image-20220714133617856.png" style="">基本原理：slave会从master读取binlog来进行数据同步具体步骤：<ul><li>master将数据改变记录到二进制日志（binary log）中</li><li>当slave上执行 start slave命令之后，slave会创建一个 IO 线程用来连接master，请求master中的binlog</li><li>当slave连接master时，master会创建一个 log dump 线程，用于发送 binlog 的内容。在读取 binlog 的内容的操作中，会对主节点上的 binlog 加锁，当读取完成并发送给从服务器后解锁</li><li>IO 线程接收主节点 binlog dump 进程发来的更新之后，保存到 中继日志（relay log） 中</li><li>slave的SQL线程，读取relay log日志，并解析成具体操作，从而实现主从操作一致，最终数据一致</li></ul><h2>一主多从配置</h2>服务器规划：使用docker方式创建，主从服务器IP一致，端口号不一致 <img src="https://webwss.cn/usr/uploads/2025/03/3963348301.png" alt="image-20250331074321031.png" title="image-20250331074321031.png" style=""><ul><li>主服务器：容器名mysql-master，端口3306</li><li>从服务器：容器名mysql-slave1，端口3307</li><li>从服务器：容器名mysql-slave2，端口3308</li></ul>注意：如果此时防火墙是开启的，则先关闭防火墙，并重启docker，否则后续安装的MySQL无法启动<pre><code>#关闭docker
systemctl stop docker
#关闭防火墙
systemctl stop firewalld
#启动docker
systemctl start docker</code></pre><h3>准备主服务器</h3><ul><li>在docker中创建并启动MySQL主服务器：端口3306</li></ul><pre><code>docker run -d \
-p 3306:3306 \
-v /mysql/master/conf:/etc/mysql/conf.d \
-v /mysql/master/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name mysql-master \
mysql:8.0.29</code></pre><ul><li>创建MySQL主服务器配置文件</li></ul>默认情况下MySQL的binlog日志是自动开启的，可以通过如下配置定义一些可选配置<pre><code>vim /mysql/master/conf/my.cnf</code></pre>配置如下内容<pre><code>[mysqld]
# 服务器唯一id，默认值1
server-id=1
# 设置日志格式，默认值ROW
binlog_format=STATEMENT
# 二进制日志名，默认binlog
# log-bin=binlog
# 设置需要复制的数据库，默认复制全部数据库
#binlog-do-db=mytestdb
# 设置不需要复制的数据库
#binlog-ignore-db=mysql
#binlog-ignore-db=infomation_schema</code></pre>重启MySQL容器<pre><code>docker restart mysql-master</code></pre>binlog格式说明<ul><li>binlog\_format=STATEMENT：日志记录的是主机数据库的写指令，性能高，但是now()之类的函数以及获取系统参数的操作会出现主从数据不同步的问题</li><li>binlog\_format=ROW（默认）：日志记录的是主机数据库的写后的数据，批量操作时性能较差，解决now()或者 user()或者 @@hostname 等操作在主从机器上不一致的问题</li><li>binlog\_format=MIXED：是以上两种level的混合使用，有函数用ROW，没函数用STATEMENT，但是无法识别系统变量</li></ul>binlog-ignore-db和binlog-do-db的优先级问题 <img src="https://webwss.cn/usr/uploads/2025/03/3742957461.png" alt="0.08703112216569037.png" title="0.08703112216569037.png" style=""><ul><li>使用命令行登录MySQL主服务器</li></ul><pre><code>#进入容器：env LANG=C.UTF-8 避免容器中显示中文乱码
docker exec -it mysql-master env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p
#修改默认密码校验方式
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';</code></pre><ul><li>主机中创建slave用户</li></ul><pre><code>-- 创建slave用户
CREATE USER 'slave'@'%';
-- 设置密码
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
-- 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
-- 刷新权限
FLUSH PRIVILEGES;</code></pre><ul><li>主机中查询master状态</li></ul>执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化<pre><code>SHOW MASTER STATUS;</code></pre>记下File和Position的值。执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化 <img src="https://webwss.cn/usr/uploads/2025/03/3737217423.png" alt="image-20220804191852164.png" title="image-20220804191852164.png" style=""><h3>准备从服务器</h3>可以配置多台从机slave1、slave2...，这里以配置slave1为例<ul><li>在docker中创建并启动MySQL从服务器：端口3307</li></ul><pre><code>docker run -d \
-p 3308:3306 \
-v /mysql/slave1/conf:/etc/mysql/conf.d \
-v /mysql/slave1/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name mysql-slave1 \
mysql:8.0.29</code></pre><ul><li>创建MySQL从服务器配置文件</li></ul><pre><code>vim /mysql/slave1/conf/my.cnf</code></pre>配置如下内容：<pre><code>[mysqld]
# 服务器唯一id，每台服务器的id必须不同，如果配置其他从机，注意修改id
server-id=2
# 中继日志名，默认xxxxxxxxxxxx-relay-bin
#relay-log=relay-bin</code></pre>重启MySQL容器<pre><code>docker restart mysql-slave1</code></pre><ul><li>使用命令行登录MySQL从服务器</li></ul><pre><code>#进入容器：
docker exec -it mysql-slave1 env LANG=C.UTF-8 /bin/bash
#进入容器内的mysql命令行
mysql -uroot -p
#修改默认密码校验方式
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';</code></pre><ul><li>在从机上配置主从关系</li></ul>在从机上执行以下SQL操作XXX.XXX.XXX.XXX填写主机ip地址<pre><code>CHANGE MASTER TO MASTER_HOST='XXX.XXX.XXX.XXX', 
MASTER_USER='slave',MASTER_PASSWORD='123456', MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000003',MASTER_LOG_POS=1357;</code></pre><h3>启动主从同步</h3>启动从机的复制功能，执行SQL：<pre><code>START SLAVE;
-- 查看状态（不需要分号）
SHOW SLAVE STATUS\G</code></pre>两个关键进程：下面两个参数都是Yes，则说明主从配置成功！ <img src="https://webwss.cn/usr/uploads/2025/03/636010488.png" alt="image-20220715000533951.png" title="image-20220715000533951.png" style=""><h3>实现主从同步</h3>在主机中执行以下SQL，在从机中查看数据库、表和数据是否已经被同步<pre><code>CREATE DATABASE db_user;
USE db_user;
CREATE TABLE t_user (
 id BIGINT AUTO_INCREMENT,
 uname VARCHAR(30),
 PRIMARY KEY (id)
);
INSERT INTO t_user(uname) VALUES('zhangsan');
INSERT INTO t_user(uname) VALUES(@@hostname);</code></pre><h3>停止和重置</h3>需要的时候，可以使用如下SQL语句<pre><code>-- 在从机上执行。功能说明：停止I/O 线程和SQL线程的操作。
stop slave;

-- 在从机上执行。功能说明：用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件。
reset slave;

-- 在主机上执行。功能说明：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。
-- 用于第一次进行搭建主从库时，进行主库binlog初始化工作；
reset master;</code></pre><h3>常见问题</h3><h4>问题1</h4>启动主从同步后，常见错误是Slave\_IO\_Running： No 或者 Connecting的情况，此时查看下方的 Last\_IO\_ERROR错误日志，根据日志中显示的错误信息在网上搜索解决方案即可 <img src="https://webwss.cn/usr/uploads/2025/03/4286846408.png" alt="image-20220714235426120.png" title="image-20220714235426120.png" style="">典型的错误例如：Last\_IO\_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position &gt; file size'解决方案：<pre><code>-- 在从机停止slave
SLAVE STOP;

-- 在主机查看mater状态
SHOW MASTER STATUS;
-- 在主机刷新日志
FLUSH LOGS;
-- 再次在主机查看mater状态（会发现File和Position发生了变化）
SHOW MASTER STATUS;
-- 修改从机连接主机的SQL，并重新连接即可</code></pre><h4>问题2</h4>启动docker容器后提示 WARNING: IPv4 forwarding is disabled. Networking will not work.<img src="https://webwss.cn/usr/uploads/2025/03/963891186.png" alt="image-20220715004850504.png" title="image-20220715004850504.png" style="">此错误，虽然不影响主从同步的搭建，但是如果想从远程客户端通过以下方式连接docker中的MySQL则没法连接<pre><code>C:\Users\administrator&gt;mysql -h 192.168.100.201 -P 3306 -u root -p</code></pre>解决方案：<pre><code>#修改配置文件：
vim /usr/lib/sysctl.d/00-system.conf
#追加
net.ipv4.ip_forward=1
#接着重启网络
systemctl restart network</code></pre>

MySQL实现主从同步

MySQL实现主从同步

主从同步原理

一主多从配置

准备主服务器

准备从服务器

启动主从同步

实现主从同步

停止和重置

常见问题

问题1

问题2

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

微信小程序登录功能（手机号快速验证登录）

Redis实现短信登录

Vue中使用wangEditor富文本编辑器图片上传（含后端代码）

SpringBoot+MyBatisPlus实现多数据源动态切换

七牛云短信服务

Redis持久化机制

JavaScript实现防抖和节流

Redis实现短信登录

Vue中使用wangEditor富文本编辑器图片上传（含后端代码）

LeetCode面试经典150题——合并两个有序数组（88）

MySQL实现主从同步

MySQL实现主从同步

主从同步原理

一主多从配置

准备主服务器

准备从服务器

启动主从同步

实现主从同步

停止和重置

常见问题

问题1

问题2

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

MySQL实现主从同步

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款