MySQL replication

MySQL 主从复制是一种常见的高可用和读写分离解决方案，下面将详细介绍搭建步骤：

1 准备工作

1.1 环境要求

• 两台或以上 MySQL 服务器（主库和从库）

• 相同或兼容的 MySQL 版本（建议主从版本一致）

• 确保网络连通性

• 足够的磁盘空间

1.2 服务器规划

角色	主机名/IP	端口	说明
主库	master.example.com	3306	写入操作的主要服务器
从库	slave1.example.com	3306	读取操作或备份的服务器

2 主库配置

2.1 修改主库配置文件 (my.cnf/my.ini)

[mysqld]
server-id = 1               # 唯一ID，主库通常为1
log_bin = mysql-bin         # 启用二进制日志
binlog_format = ROW         # 推荐使用ROW格式
binlog_do_db = your_database # 可选：指定要复制的数据库
expire_logs_days = 10       # 二进制日志保留天数
sync_binlog = 1             # 每次事务提交都写入磁盘

2.2 重启 MySQL 服务

# Linux系统
sudo systemctl restart mysqld
 
# 或
sudo service mysql restart

2.3 创建复制用户

CREATE USER 'repl'@'%' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

2.4 获取主库状态

FLUSH TABLES WITH READ LOCK;  -- 锁定表，防止数据变化
SHOW MASTER STATUS;

记录输出结果（File 和 Position 值），例如：

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      107 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

2.5 解锁表（可选，如果需要继续写入）

UNLOCK TABLES;

3 从库配置

3.1 修改从库配置文件 (my.cnf/my.ini)

[mysqld]
server-id = 2               # 唯一ID，不能与主库相同
relay_log = mysql-relay-bin # 启用中继日志
log_bin = mysql-bin         # 可选：如果需要链式复制
read_only = 1               # 从库只读

3.2 重启 MySQL 服务

sudo systemctl restart mysqld

3.3 配置复制

CHANGE MASTER TO
MASTER_HOST='master.example.com',
MASTER_USER='repl',
MASTER_PASSWORD='your_password',
MASTER_LOG_FILE='mysql-bin.000003',  -- 主库状态中的File值
MASTER_LOG_POS=107,                  -- 主库状态中的Position值
MASTER_CONNECT_RETRY=10;             -- 连接失败重试间隔(秒)

3.4 启动复制

START SLAVE;

3.5 检查复制状态

SHOW SLAVE STATUS\G

关键检查项：

• Slave_IO_Running: Yes

• Slave_SQL_Running: Yes

• Seconds_Behind_Master: 0（或较小值）

Slave_IO_Running: Yes #这个是I/O线程状态，I/O线程负责从从库到主库读取binlog日志，并写入从库的中继日志，状态为Yes表示I/O线程工作正常

Slave_SQL_Running: Yes #这个是SQL线程状态，SQL线程负责读取中继日志（relay-log）中的数据并转换为SQL语句应用到从数据库中，状态为YES表示I/O线程工作正常

---

执行 stop slave 时:

Slave_IO_Running 和 Slave_SQL_Running 均会变为 No

---

可以仅停止其中的一个线程:

STOP SLAVE IO_THREAD;

STOP SLAVE SQL_THREAD;

4 验证复制

4.1 在主库创建测试数据

CREATE DATABASE test_replication;
USE test_replication;
CREATE TABLE test_table (id INT, name VARCHAR(50));
INSERT INTO test_table VALUES (1, 'Test Data');

4.2 在从库检查数据

USE test_replication;
SELECT * FROM test_table;

5 常见问题处理

5.1 复制错误处理

• 查看错误日志：SHOW SLAVE STATUS\G 中的 Last_Error

• 常见错误及解决：

  • 主键冲突：跳过错误事务（不推荐长期使用）

  • 网络中断：检查网络后自动恢复

  • 数据不一致：可能需要重新同步

5.2 跳过错误事务（谨慎使用）

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

5.3 重新同步数据

如果复制差异过大，可能需要：

1. 停止从库复制

2. 备份主库数据并恢复到从库

3. 重新配置复制（使用新的主库状态）

5.4 主库状态检查

5.4.1 show master status;

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                            |
+------------------+----------+--------------+------------------+----------------------------------------------+
| mysql-bin.000002 |  5202671 |              |                  | 5f3b8a59-4a9a-11f0-9639-faf60001371b:1-18995 |
+------------------+----------+--------------+------------------+----------------------------------------------+

5.4.2 show slave status;

常态下主库查询 slave status 应该返回空

mysql> show slave status;
Empty set (0.00 sec)

5.4.3 select @@read_only;

• 返回值为 0（或 OFF）表示非只读模式

• 返回值为 1（或 ON）表示只读模式

mysql> select @@read_only;
+-------------+
| @@read_only |
+-------------+
|           0 |
+-------------+

6 高级配置（可选）

6.1 GTID 复制（MySQL 5.6+）

# 主从库配置中添加
gtid_mode = ON
enforce_gtid_consistency = ON

6.2 半同步复制（提高数据安全性）

-- 主库安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
 
-- 从库安装插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

6.3 多源复制（一个从库复制多个主库）

CHANGE MASTER TO
MASTER_HOST='master1.example.com',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_AUTO_POSITION=1
FOR CHANNEL 'master1';

7 维护建议

1. 定期监控复制延迟

2. 定期检查主从数据一致性

3. 考虑使用工具如 pt-table-checksum 和 pt-table-sync 进行数据校验和修复

4. 制定故障切换预案

通过以上步骤，您可以成功搭建 MySQL 主从复制环境。根据实际需求，您还可以进一步配置过滤规则、优化复制性能或实现更复杂的高可用架构。