MySQL 主从同步（主从复制）配置指南

一、什么是MySQL主从复制？

MySQL主从复制是一种数据同步机制，通过让一台MySQL服务器（主库，Master）将数据变更复制到一台或多台MySQL服务器（从库，Slave），实现多节点数据一致性。

其核心价值在于：

• 高可用性：主库故障时，可快速切换到从库，避免服务中断；
• 读写分离：主库负责写操作（INSERT/UPDATE/DELETE），从库负责读操作（SELECT），减轻主库压力；
• 数据备份：从库可作为异地备份节点，避免主库数据丢失风险；
• 横向扩展：通过增加从库数量，提升读操作并发能力。

二、主从复制基本原理

MySQL主从同步基于二进制日志（binlog） 和中继日志（relay log） 实现，默认采用异步复制模式。

2.1 核心组件

2.2 同步流程（3步核心）

1. 主库记录变更：主库执行数据变更操作后，自动将操作记录到binlog中（需手动开启binlog功能）。
2. 从库接收日志：从库的I/O线程主动连接主库，请求读取binlog；主库返回binlog内容，I/O线程将其写入从库的relay log。
3. 从库执行变更：从库的SQL线程实时读取relay log，解析并执行其中的SQL语句，使从库数据与主库保持一致。

三、环境准备与操作步骤

本次操作基于 Ubuntu 22.04 LTS 系统，需两台独立服务器（主库+从库），具体信息如下：

3.1 步骤1：更新软件源（两台服务器均执行）

确保系统软件源最新，避免安装MySQL时出现依赖问题：

1
2

# 更新apt软件源索引
apt update

3.2 步骤2：安装MySQL（两台服务器均执行）

通过apt命令安装MySQL Server，默认会自动启动服务：

1
2

# 安装MySQL Server
apt install mysql-server -y

验证MySQL服务状态：执行 systemctl status mysql，显示“active (running)”即为正常。

3.3 步骤3：配置主库（192.168.40.187）

主库需开启binlog、设置唯一server-id，并指定需同步/忽略的数据库。

3.3.1 编辑MySQL配置文件

1
2

# 打开MySQL主配置文件（apt安装默认路径）
vim /etc/mysql/mysql.conf.d/mysqld.cnf

3.3.2 关键配置项（添加/修改以下内容）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 允许远程连接（0.0.0.0表示监听所有IP，生产环境可改为从库IP）
bind-address            = 0.0.0.0
mysqlx-bind-address     = 0.0.0.0

# 主库唯一标识（必须为正整数，与从库不重复，建议用IP尾段或自定义编号）
server-id              = 1

# 开启binlog，指定binlog日志文件路径（前缀为mysql-bin）
log_bin                = /var/log/mysql/mysql-bin.log

# binlog日志过期时间（2592000秒=30天，避免日志文件过大）
binlog_expire_logs_seconds     = 2592000

# 单个binlog文件最大大小（超过后自动切割）
max_binlog_size        = 100M

# 需要同步的数据库（仅同步blog库，多个库可重复配置此参数）
binlog_do_db           = blog

# 忽略同步的数据库（不同步test库，避免测试数据干扰）
binlog_ignore_db       = test

3.3.3 重启MySQL使配置生效

1

systemctl restart mysql

3.4 步骤4：配置从库（192.168.40.188）

从库需设置唯一server-id（与主库不同），其余binlog相关配置可与主库一致（若从库需作为其他从库的主库，需开启binlog）。

3.4.1 编辑MySQL配置文件

1

vim /etc/mysql/mysql.conf.d/mysqld.cnf

3.4.2 关键配置项（添加/修改以下内容）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 允许远程连接
bind-address            = 0.0.0.0
mysqlx-bind-address     = 0.0.0.0

# 从库唯一标识（必须≠主库的1，此处设为2）
server-id              = 2

# （可选）若从库需作为“级联主库”，需开启binlog；仅作为从库可注释
log_bin                = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds     = 2592000
max_binlog_size        = 100M

# 与主库保持一致：同步blog库，忽略test库
binlog_do_db           = blog
binlog_ignore_db       = test

3.4.3 重启MySQL使配置生效

1

systemctl restart mysql

3.5 步骤5：主库创建复制用户并授权

主库需创建一个专门用于从库同步的用户（仅授予REPLICATION SLAVE权限，最小权限原则）。

5.1 登录主库MySQL

1
2

# 登录MySQL（apt安装默认root无密码，直接回车即可）
mysql -uroot

5.2 执行授权SQL

1
2
3
4
5
6
7
8

-- 1. 创建复制用户：用户名Slave_188，允许从库IP（192.168.40.188）连接
CREATE USER 'Slave_188'@'192.168.40.188' IDENTIFIED WITH mysql_native_password BY '123456';

-- 2. 授予复制权限（仅允许读取binlog用于同步，无其他权限）
GRANT REPLICATION SLAVE ON *.* TO 'Slave_188'@'192.168.40.188';

-- 3. 刷新权限使配置生效
FLUSH PRIVILEGES;

3.6 步骤6：查看主库binlog状态（关键！）

从库需知道主库当前的binlog文件名（File）和位置（Position），才能从正确的节点开始同步。

在主库的MySQL终端中执行：

1
2

-- 查看主库binlog状态（记录File和Position值，后续从库配置需用）
SHOW MASTER STATUS;

示例结果说明：

• File: mysql-bin.000003（当前使用的binlog文件名）
• Position: 324（当前binlog的偏移量，从库需从此位置开始读取）
• 注意：此值需与主库实际输出一致，不可直接复制示例值！

3.7 步骤7：从库关联主库并启动同步

在从库的MySQL终端中，配置主库信息并启动同步线程。

7.1 登录从库MySQL

1

mysql -uroot

7.2 执行同步配置SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

-- 1. 停止现有同步（若之前配置过，避免冲突）
STOP SLAVE;

-- 2. 配置主库信息（替换为实际的File和Position值）
CHANGE MASTER TO
    MASTER_HOST = '192.168.40.187',          -- 主库IP
    MASTER_USER = 'Slave_188',               -- 主库创建的复制用户
    MASTER_PASSWORD = '123456',              -- 复制用户密码
    MASTER_LOG_FILE = 'mysql-bin.000003',    -- 主库SHOW MASTER STATUS中的File值
    MASTER_LOG_POS = 324,                    -- 主库SHOW MASTER STATUS中的Position值
    MASTER_PORT = 3306,                      -- 主库MySQL端口（默认3306）
    MASTER_CONNECT_RETRY = 60;               -- 连接失败重试间隔（秒）

-- 3. 启动从库同步线程
START SLAVE;

四、同步效果验证

配置完成后，需在从库验证同步线程状态，并测试数据同步是否正常。

4.1 验证从库同步线程状态

在从库的MySQL终端中执行：

1
2

-- 查看从库同步状态（\G表示纵向格式化输出，更易读）
SHOW SLAVE STATUS\G

关键参数验证（必须为Yes！）

• Slave_IO_Running: Yes：I/O线程正常（成功连接主库并读取binlog）；
• Slave_SQL_Running: Yes：SQL线程正常（成功执行中继日志中的SQL）。

4.2 测试数据同步

通过在主库创建数据库/表/数据，验证从库是否同步。

4.2.1 主库创建测试数据库

在主库的MySQL终端中执行：

1
2

-- 创建需同步的数据库blog（与主库binlog_do_db配置一致）
CREATE DATABASE IF NOT EXISTS blog;

4.2.2 从库验证同步结果

在从库的MySQL终端中执行：

1
2

-- 查看数据库列表，确认blog库已同步
SHOW DATABASES;

五、常见错误与解决方案

在配置过程中，可能遇到同步线程异常或连接失败，以下是两种典型错误的处理方式。

5.1 错误1：从库无法连接主库（Host不允许连接）

错误提示

1
2

Error connecting to source 'Slave_188@192.168.40.188:3306'. 
Message: Host '192.168.40.188' is not allowed to connect to this MySQL server

原因分析

• 主库复制用户授权的IP与从库实际IP不匹配；
• 主库bind-address配置为127.0.0.1（仅允许本地连接）；
• 服务器防火墙拦截了3306端口。

解决方案

1. 重新授权复制用户（确保IP正确）：

   1 2 3 4 5 6

   -- 先删除旧用户（若IP错误） DROP USER IF EXISTS 'Slave_188'@'192.168.40.188'; -- 重新创建并授权（IP需与从库一致） CREATE USER 'Slave_188'@'192.168.40.188' IDENTIFIED WITH mysql_native_password BY '123456'; GRANT REPLICATION SLAVE ON *.* TO 'Slave_188'@'192.168.40.188'; FLUSH PRIVILEGES;

2. 确认主库bind-address = 0.0.0.0，并重启MySQL；
3. 开放防火墙3306端口（Ubuntu用ufw）：

   1 2	ufw allow 3306/tcp ufw reload

5.2 错误2：事务不一致（SQL线程停止）

错误提示

1
2

Coordinator stopped because there were error(s) in the worker(s). 
Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000002, end_log_pos 1807.

原因分析

主库与从库数据存在差异（如从库已存在主库要创建的表），导致SQL线程执行事务失败。

解决方案（临时跳过单个事务）

适用于偶尔的非关键事务冲突，不建议频繁使用（需先排查数据不一致原因）：

1
2
3
4
5
6
7
8
9
10
11

-- 1. 停止从库同步
STOP SLAVE;

-- 2. 跳过1个事务（数值可根据冲突次数调整）
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

-- 3. 重启从库同步
START SLAVE;

-- 4. 验证同步状态
SHOW SLAVE STATUS\G

长期解决方案：

1. 同步前确保主从库数据一致（如通过mysqldump备份主库数据，导入从库）；
2. 避免在从库手动执行写操作（建议将从库设为只读：SET GLOBAL read_only = 1;）。

六、总结

MySQL主从复制是实现数据库高可用、读写分离的基础方案，核心在于正确配置binlog和中继日志，并确保从库能正常连接主库。关键步骤总结如下：

1. 环境准备：两台服务器安装MySQL，确保网络互通；
2. 主库配置：开启binlog、设置server-id、创建复制用户；
3. 从库配置：设置唯一server-id、关联主库binlog信息；
4. 状态验证：确保从库I/O和SQL线程均为Yes；
5. 错误处理：针对性解决连接失败、数据不一致等问题。

通过主从复制，可有效提升数据库的并发处理能力和数据安全性，是生产环境中常用的数据库架构方案。