MySQL 主从复制与读写分离

主从复制的基础原理

在实际生产环境中，我通常通过为主服务器开启二进制日志，并在从服务器上配置其为主服务器的复制对象，以此实现主从复制的基础架构。主服务器在处理写请求的同时会将数据变更记录到二进制日志，从服务器定期从主服务器拉取并重放这些日志实现数据同步。这一机制不仅能将读写负载分摊到不同服务器，还能在故障时快速切换到从服务器，提升系统的可靠性和扩展性。

复制架构

在部署中，我会选用一台作为主服务器进行写操作，若干台作为从服务器负责读操作。通过将读请求分配给从服务器，主服务器压力得到显著降低。同时，如果主服务器发生故障，我可以迅速将某台从服务器晋升为新的主服务器，从而减少业务中断时间。

复制类型

在实际运维中，我通常会根据业务特性选择复制类型。MySQL 支持基于语句的复制与基于行的复制。基于语句的复制记录执行的 SQL 语句，适合逻辑简单的场景，但可能在高并发环境中出现不一致问题。基于行的复制记录被修改的行数据，在生产中我更多选择基于行的复制以确保数据一致性。可在主库配置文件中通过设置 binlog_format=ROW 来启用基于行的复制。

下表对比两种复制类型的常见特点

复制类型	特点	使用场景
基于语句	日志量小，易读性高，但在高并发下可能出现数据不一致	简单逻辑场景
基于行	数据一致性更高，但日志量较大	高并发、数据一致性要求高的生产环境

CHANGE MASTER TO MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS =  107;

配置主从复制

在配置主从复制时，我首先会在主服务器上启用二进制日志，并为每台服务器分配唯一的 server-id，随后在主服务器上创建一个专用复制用户，然后在从服务器上通过 CHANGE MASTER TO 指定主服务器的连接信息和日志位置，最后启动从服务器的复制进程。

主服务器配置

在主服务器的 my.cnf 中启用二进制日志，并指定唯一 server-id。完成后重启 MySQL 服务生效。

[mysqld]
server-id=1
log-bin=mysql-bin
binlog_format=ROW

创建复制用户

在主服务器上创建复制用户并赋予 REPLICATION SLAVE 权限，这样从服务器即可使用该用户连接主服务器。

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

从服务器配置

在从服务器上设置与主服务器不同的唯一 server-id，并为从服务器指定中继日志文件。启动 MySQL 后，在从服务器中指定主服务器信息、复制账号与对应的日志文件和位置，然后启动复制线程。

[mysqld]
server-id=2
relay-log=relay-log-bin

CHANGE MASTER TO
    MASTER_HOST='主服务器IP',
    MASTER_USER='replica_user',
    MASTER_PASSWORD='secure_password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=107;
START SLAVE;

读写分离实现

为提高整体性能，我在实际使用中会将写请求固定指向主服务器，并通过应用层代码将读请求分发给从服务器。在应用程序中维护两个独立的数据源连接，在业务逻辑中区分读写操作，从而实现读写分离。此举降低了主服务器的查询压力，并使查询性能在多从服务器间水平扩展。

import mysql.connector

master_db = mysql.connector.connect(
    host="主服务器IP",
    user="root",
    password="password",
    database="mydatabase"
)

slave_db = mysql.connector.connect(
    host="从服务器IP",
    user="root",
    password="password",
    database="mydatabase"
)

# 写操作直接走主库连接
cursor_master = master_db.cursor()
cursor_master.execute("INSERT INTO orders (product_id, quantity) VALUES (1, 10)")
master_db.commit()

# 读操作走从库连接
cursor_slave = slave_db.cursor()
cursor_slave.execute("SELECT * FROM orders WHERE product_id=1")
print(cursor_slave.fetchall())

在有多台从服务器时，我也常使用 HAProxy 这类负载均衡器，将读请求平均分配到不同从服务器，减少单台从服务器压力。

frontend mysql_front
    bind *:3306
    mode tcp
    default_backend mysql_back

backend mysql_back
    mode tcp
    balance roundrobin
    server slave1 从服务器IP:3306 check
    server slave2 从服务器IP:3306 check

复制监控与故障处理

在日常维护中，我经常通过 SHOW SLAVE STATUS\G 命令查看从服务器的复制状态。当出现复制中断或延迟过高的情况，我会先停止从服务器复制线程，检查中继日志与主服务器二进制日志的差异，然后通过手动同步丢失数据或跳过异常事件的方式恢复复制。为减少此类问题，我在部署时会保持主从服务器时钟同步，并尽量避免在主库执行非幂等操作。

SHOW SLAVE STATUS\G

最佳实践

在生产中我会让主从服务器保持相同的 MySQL 版本与配置，以降低因版本差异导致的复制异常风险。

在主服务器上定期备份数据，这样在主服务器出现故障并需要从从服务器切换时，手头有完整备份可以快速恢复。

监控复制延迟情况，如果延迟过高，我会考虑增加从服务器数量、优化查询结构或者使用更高性能的硬件。

在复杂查询多的环境中，为减少复制延迟，我会尽量将写操作集中在必要的关键操作上，减少长事务。

官方文档

-- 可以通过以下命令查看主从延迟秒数 SHOW SLAVE STATUS\G -- Seconds_Behind_Master 字段显示从库延迟秒数

-- 也可以使用 pt-heartbeat 工具更精确地监控复制延迟

主从复制的基础原理​

复制架构​

复制类型​

配置主从复制​

主服务器配置​

创建复制用户​

从服务器配置​

读写分离实现​

复制监控与故障处理​

最佳实践​