MySQL 基于日志点实现主从复制

发表于 阅读次数： 评论数： 本文字数： 3.1k 阅读时长 ≈ 3 分钟

大纲

前言

本文将介绍 MySQL 如何通过 binlog 日志点的方式实现主从复制。为了方便演示，使用 Docker 部署 MySQL；但在生产环境中，强烈建议在裸机或虚拟机上直接安装 MySQL，以获得更高、更稳定的性能。

MySQL 主从同步原理

主从同步的基本原理：

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

主从同步的具体步骤：

  • step1： master 将所有数据变更记录到 二进制日志（binlog） 中。
  • step2： 当 slave 执行 start slave 命令后，会创建一个 I/O 线程，用于连接 master 并请求其 binlog。
  • step3： slave 连接到 master 后，master 会创建一个 log dump 线程，负责将 binlog 内容发送给 slave。在读取 binlog 时会对其加锁，读取完成并发送给 slave 后再解锁。
  • step4： slave 的 I/O 线程接收来自 master 的 binlog 更新，并将其写入 中继日志（relay log）。
  • step5： slave 的 SQL 线程 读取 中继日志（relay log），将其中的事件解析并执行，以实现主从操作一致，最终确保数据一致性。

MySQL 主从同步配置

版本说明

软件版本说明
CentOS7.9可使用其他 Linux 发行版，比如 Debian、Ubuntu 等
Docker26.1.4
MySQL8.0.29

准备工作

配置网络服务

  • 配置 Linux 的网络服务，否则在外部远程连接 Docker 中的 MySQL 时，可能会出现连接失败的问题
1
2
3
# 在宿主机中，修改系统配置文件，在文件末尾追加以下一行内容
vim /usr/lib/sysctl.d/00-system.conf
net.ipv4.ip_forward=1
  • 重启网络服务（以 CentOS 为例）
1
2
# 重启网络服务
systemctl restart network

安装 Docker 服务

由于本文是基于 Docker 方式来安装 MySQL，因此需要提前安装并启动 Docker 服务，详细教程如下所示：

服务器规划

  • 使用 Docker 方式部署两台 MySQL 服务器，主服务器和从服务器的 IP 一致，但端口号不一致

服务器主从角色容器名称端口
主服务器主库 mysql-master3306
从服务器一从库 mysql-slave13307
从服务器二从库 mysql-slave23308

部署主服务器

step1：在 Docker 中创建并启动 MySQL 主服务器，端口是 3306

1
2
3
4
5
6
7
8
9
10
11
12
# 创建并启动 MySQL 容器（Docker 会自动在宿主机上创建不存在的目录）
docker run -d \
  -p 3306:3306 \
  -v /data/mysql/master/conf:/etc/mysql/conf.d \
  -v /data/mysql/master/data:/var/lib/mysql \
  -e MYSQL_ROOT_PASSWORD=123456 \
  --name mysql-master \
  --restart always \
  mysql:8.0.29

# 查看 MySQL 容器的运行状态
docker ps -a

step2：创建 MySQL 主服务器的配置文件

  • 默认情况下 MySQL 的 binlog 日志是自动开启的，可以通过如下配置定义一些可选配置项：
1
2
# 创建并编辑 MySQL 配置文件，添加以下配置内容
vim /data/mysql/master/conf/my.cnf
  • 配置如下内容：
1
2
3
4
5
6
7
8
9
10
11
12
[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 容器：
1
docker restart mysql-master

  • binlog 格式说明：

    • binlog_format=STATEMENT：记录的是主服务器执行的 写操作语句，性能较高；但由于依赖执行环境，诸如 NOW()、USER()、@@hostname 等与环境相关的函数或获取系统变量等操作可能会导致主从数据不一致的问题。
    • binlog_format=ROW（默认）：记录的是主服务器 执行写入后的实际数据行。在批量操作下性能略差，但能避免 NOW()、USER()、@@hostname 等与环境相关的函数或获取系统变量等操作导致的主从数据不一致问题。
    • binlog_format=MIXED：结合以上两种 binglog 格式。包含函数或与环境相关的操作时使用 ROW，其他情况使用 STATEMENT；但对系统变量的变化识别仍存在局限。

  • 配置项优先级说明：

    • 在 MySQL 5.7 及之后版本‌中，binlog-ignore-db 的优先级高于 binlog-do-db，即如果一个数据库同时出现在这两个配置中，它将被忽略。
    • 在 MySQL 5.6 及之前版本‌中，优先级相反，binlog-do-db 的优先级高于 binlog-ignore-db，即 binlog-do-db 的配置会覆盖 binlog-ignore-db，如下图所示。

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

1
2
3
4
5
6
7
8
# 进入容器内，其中环境变量 "env LANG=C.UTF-8" 用于避免容器内显示中文乱码的问题
docker exec -it mysql-master env LANG=C.UTF-8 /bin/bash

# 在容器内执行 MySQL 命令行
mysql -uroot -p

# 修改 MySQL 超级管理员用户的默认密码校验方式
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

step4：主服务器中创建 slave 用户

1
2
3
4
5
6
7
8
9
10
11
-- 创建 slave 用户
CREATE USER 'sync_slave'@'%';

-- 设置 slave 用户的密码
ALTER USER 'sync_slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

-- 授予复制权限给 slave 用户
GRANT REPLICATION SLAVE ON *.* TO 'sync_slave'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

step5：主服务器中查询 master 状态

1
SHOW MASTER STATUS;
  • 记录下 File 和 Position 的值，执行完此步骤后 不要再操作主服务器上的 MYSQL，防止主服务器的状态值发生变化

部署从服务器

特别注意

这里可以部署多台从服务器，如 slave1、slave2 ...，但从服务器数量并非越多越好，过多的从服务器会增加主服务器的负载。多台从服务器的部署流程基本相同，但需要注意修改 Docker 容器的端口映射以及 MySQL 服务器的 ID（server-id），以避免冲突。

从服务器一

step1：在 Docker 中创建并启动 MySQL 从服务器，端口是 3307

1
2
3
4
5
6
7
8
9
10
11
12
# 创建并启动 MySQL 容器（Docker 会自动在宿主机上创建不存在的目录）
docker run -d \
  -p 3307:3306 \
  -v /mysql/slave1/conf:/etc/mysql/conf.d \
  -v /mysql/slave1/data:/var/lib/mysql \
  -e MYSQL_ROOT_PASSWORD=123456 \
  --name mysql-slave1 \
  --restart always \
  mysql:8.0.29

# 查看 MySQL 容器的运行状态
docker ps -a

step2：创建 MySQL 从服务器的配置文件

1
2
# 创建并编辑 MySQL 配置文件，添加以下配置内容
vim /mysql/slave1/conf/my.cnf
  • 配置如下内容：
1
2
3
4
5
[mysqld]
# 服务器唯一 ID，每台服务器的 ID 必须不同，如果存在多台从服务器，需要注意修改 ID
server-id=2
# 中继日志名，默认是 xxxxxxxxxxxx-relay-bin
#relay-log=relay-bin
  • 重启 MySQL 容器：
1
docker restart mysql-slave1

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

1
2
3
4
5
6
7
8
# 进入容器内，其中环境变量 "env LANG=C.UTF-8" 用于避免容器内显示中文乱码的问题
docker exec -it mysql-slave1 env LANG=C.UTF-8 /bin/bash

# 在容器内执行 MySQL 命令行
mysql -uroot -p

# 修改 MySQL 超级管理员用户的默认密码校验方式
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

step4：在从服务器上配置主从关系

  • 在从服务器上，执行以下 SQL 操作
1
2
3
4
5
6
7
CHANGE MASTER TO
    MASTER_HOST='192.168.2.191',
    MASTER_PORT=3306,
    MASTER_USER='sync_slave',
    MASTER_PASSWORD='123456',
    MASTER_LOG_FILE='binlog.000003',
    MASTER_LOG_POS=1357;
参数说明
MASTER_HOST主服务器的 IP 地址或主机名，这里使用宿主机的 IP 地址（比如 192.168.2.191）
MASTER_PORT主服务器监听的端口号（默认为 3306），这里使用 Docker 的映射端口（比如 3306）
MASTER_USER从服务器用于连接主服务器的用户名
MASTER_PASSWORD从服务器用于连接主服务器的密码
MASTER_LOG_FILE从哪个 binlog 文件开始复制
MASTER_LOG_POS从 binlog 文件的哪个位置开始复制
  • 在从服务器上，查看主从关系的配置
1
show slave status \G;

从服务器二

step1：在 Docker 中创建并启动 MySQL 从服务器，端口是 3308

1
2
3
4
5
6
7
8
9
10
11
12
# 创建并启动 MySQL 容器（Docker 会自动在宿主机上创建不存在的目录）
docker run -d \
  -p 3308:3306 \
  -v /mysql/slave2/conf:/etc/mysql/conf.d \
  -v /mysql/slave2/data:/var/lib/mysql \
  -e MYSQL_ROOT_PASSWORD=123456 \
  --name mysql-slave2 \
  --restart always \
  mysql:8.0.29

# 查看 MySQL 容器的运行状态
docker ps -a

step2：创建 MySQL 从服务器的配置文件

1
2
# 创建并编辑 MySQL 配置文件，添加以下配置内容
vim /mysql/slave2/conf/my.cnf
  • 配置如下内容：
1
2
3
4
5
[mysqld]
# 服务器唯一 ID，每台服务器的 ID 必须不同，如果存在多台从服务器，需要注意修改 ID
server-id=3
# 中继日志名，默认是 xxxxxxxxxxxx-relay-bin
#relay-log=relay-bin
  • 重启 MySQL 容器：
1
docker restart mysql-slave2

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

1
2
3
4
5
6
7
8
# 进入容器内，其中环境变量 "env LANG=C.UTF-8" 用于避免容器内显示中文乱码的问题
docker exec -it mysql-slave2 env LANG=C.UTF-8 /bin/bash

# 在容器内执行 MySQL 命令行
mysql -uroot -p

# 修改 MySQL 超级管理员用户的默认密码校验方式
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

step4：在从服务器上配置主从关系

  • 在从服务器上，执行以下 SQL 操作
1
2
3
4
5
6
7
CHANGE MASTER TO
    MASTER_HOST='192.168.2.191',
    MASTER_PORT=3306,
    MASTER_USER='sync_slave',
    MASTER_PASSWORD='123456',
    MASTER_LOG_FILE='binlog.000003',
    MASTER_LOG_POS=1357;
参数说明
MASTER_HOST主服务器的 IP 地址或主机名，这里使用宿主机的 IP 地址（比如 192.168.2.191）
MASTER_PORT主服务器监听的端口号（默认为 3306），这里使用 Docker 的映射端口（比如 3306）
MASTER_USER从服务器用于连接主服务器的用户名
MASTER_PASSWORD从服务器用于连接主服务器的密码
MASTER_LOG_FILE从哪个 binlog 文件开始复制
MASTER_LOG_POS从 binlog 文件的哪个位置开始复制
  • 在从服务器上，查看主从关系的配置
1
2
# 查看从服务器的状态（不需要分号）
show slave status \G

启动主从同步

  • 在多台从服务器上，分别执行以下 MySQL 命令，启动从服务器的主从同步功能
1
2
3
4
5
# 启动主从同步
START SLAVE;

# 查看从服务器的同步状态（不需要分号）
SHOW SLAVE STATUS\G
  • 观察 MySQL 命令输出的内容，尤其是 Slave_IO_Running 与 Slave_SQL_Running 参数，如果这两个参数的值都是 Yes，则说明主从同步成功

验证主从同步

  • 在主服务器中，执行以下 SQL 语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 创建数据库
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('zhang3');
INSERT INTO t_user(uname) VALUES(@@hostname);
  • 在两台从服务器中，分别查看数据库、表和数据是否已经被同步过来
1
2
3
4
5
6
7
8
9
10
11
-- 查看所有数据库
SHOW DATABASES;

-- 切换数据库
USE db_user;

-- 查看所有表
SHOW TABLES;

-- 查询表数据
SELECT * FROM t_user;

停止和重置同步

  • 在需要的时候（比如，主从同步异常），可以执行以下 SQL 语句停止和重置主从同步：
1
2
3
4
5
6
7
8
-- 在从服务器上执行，作用说明：停止 I/O 线程和 SQL 线程的运行
STOP SLAVE;

-- 在从服务器上执行，作用说明：用于删除 Slave 数据库的中继日志（relay log）文件，并重新启用新的中继日志（relay log）文件
RESET SLAVE;

-- 在主服务器上执行，作用说明：删除所有的 binglog 日志文件，并将日志索引文件清空，重新开始所有新的日志文件；通常用于第一次搭建主从同步时，进行主服务器的 binlog 初始化工作
RESET MASTER;

MySQL 主从同步问题

常见问题一

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

  • 典型的错误信息，例如：
1
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'
  • 解决方案：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 在从服务器中，停止主从同步
STOP SLAVE;

-- 在主服务器中，查看 master 状态
SHOW MASTER STATUS;

-- 在主服务器中，刷新日志
FLUSH LOGS;

-- 在主服务器中，再次查看 master 状态（会发现 File 和 Position 发生了变化）
SHOW MASTER STATUS;

-- 在从服务器中，修改从服务器连接主服务器的 SQL，并重新执行该 SQL
CHANGE MASTER TO
    MASTER_HOST='192.168.2.191',
    MASTER_PORT=3306,
    MASTER_USER='sync_slave',
    MASTER_PASSWORD='123456',
    MASTER_LOG_FILE='binlog.000004',
    MASTER_LOG_POS=1487;

-- 在从服务器中，重新启动主从同步
START SLAVE;

常见问题二

  • Docker 创建并启动 MySQL 容器后，出现警告信息：WARNING: IPv4 forwarding is disabled. Networking will not work.

  • 该错误虽然不影响 MySQL 主从同步的搭建，但如果需要通过远程客户端连接 Docker 中的 MySQL，则会导致无法连接
1
2
# 在外部远程连接 Docker 中的 MySQL 会失败
mysql -h 192.168.2.191 -P 3306 -u root -p
  • 解决方案（以 CentOS 为例）：
1
2
3
4
5
6
7
8
9
10
11
# 关闭 Docker 服务
systemctl stop docker

# 临时关闭防火墙
systemctl stop firewalld

# 永久关闭防火墙
systemctl disable firewalld

# 启动 Docker 服务
systemctl stop docker