MySQL同步复制机制详解 - 摩柯技术社区

MySQL 同步复制机制概述

MySQL 的同步复制机制是一种用于数据备份、故障恢复以及读写分离的重要技术。它允许将一台 MySQL 服务器（主服务器，Master）的数据更改同步到一台或多台其他 MySQL 服务器（从服务器，Slave）。这种机制基于二进制日志（Binary Log）和中继日志（Relay Log）来实现数据的异步复制。

在同步复制过程中，主服务器记录所有的数据更改操作到二进制日志中。从服务器通过 I/O 线程连接到主服务器，读取二进制日志并将其写入到本地的中继日志。然后，从服务器的 SQL 线程读取中继日志，并在本地执行这些更改操作，从而使从服务器的数据与主服务器保持同步。

同步复制的工作原理

主服务器的操作
- 主服务器在执行数据修改语句（如 INSERT、UPDATE、DELETE 等）时，会将这些操作记录到二进制日志（binlog）中。二进制日志记录了数据库的所有更改操作，它是 MySQL 同步复制的基础。
- 主服务器维护一个二进制日志索引文件（binlog.index），用于记录当前使用的二进制日志文件列表。
从服务器的操作
- I/O 线程：从服务器启动时，I/O 线程会连接到主服务器。它向主服务器请求二进制日志的内容，并将接收到的日志数据写入到本地的中继日志（relay log）中。I/O 线程会记录当前读取到的主服务器二进制日志的位置，这个位置信息存储在从服务器的 master.info 文件中。
- SQL 线程：SQL 线程负责读取中继日志，并在从服务器上执行其中记录的数据库更改操作。这样，从服务器就可以将主服务器上的数据更改应用到本地，从而保持与主服务器的数据同步。SQL 线程会记录当前执行到的中继日志的位置，这个位置信息存储在从服务器的 relay - log.info 文件中。

配置 MySQL 同步复制

主服务器配置
- 编辑 MySQL 配置文件（通常是 /etc/my.cnf 或 /etc/mysql/my.cnf），添加或修改以下配置项：

[mysqld]
# 开启二进制日志
log - bin = /var/log/mysql/mysql - bin.log
# 服务器唯一标识，取值范围是 1 - 2^32 - 1
server - id = 1

重启 MySQL 服务使配置生效：

sudo systemctl restart mysql

创建用于复制的用户，并授予复制权限：

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

获取主服务器的二进制日志文件名和位置：

SHOW MASTER STATUS;

执行上述命令后，会得到类似如下结果：

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

这里的 File 就是二进制日志文件名，Position 就是二进制日志位置，后续配置从服务器时会用到。

从服务器配置
- 编辑 MySQL 配置文件，添加或修改以下配置项：

[mysqld]
# 服务器唯一标识，不能与主服务器相同
server - id = 2

重启 MySQL 服务：

sudo systemctl restart mysql

配置从服务器连接主服务器：

CHANGE MASTER TO
    MASTER_HOST ='master_host_ip',
    MASTER_USER ='replication_user',
    MASTER_PASSWORD = 'password',
    MASTER_LOG_FILE ='mysql - bin.000003',
    MASTER_LOG_POS = 154;

这里的 MASTER_HOST 是主服务器的 IP 地址，MASTER_LOG_FILE 和 MASTER_LOG_POS 是前面在主服务器上执行 SHOW MASTER STATUS 得到的结果。
启动从服务器复制：

START SLAVE;

检查从服务器复制状态：

SHOW SLAVE STATUS \G;

查看 Slave_IO_Running 和 Slave_SQL_Running 字段，若都为 Yes，且 Seconds_Behind_Master 字段显示的值为 0 或接近 0，则表示复制配置成功。

同步复制的类型

基于语句的复制（Statement - Based Replication，SBR）
- 在基于语句的复制中，主服务器将执行的 SQL 语句记录到二进制日志中。从服务器在应用中继日志时，会重新执行这些 SQL 语句。
- 优点：
  - 日志量小，因为只记录 SQL 语句，而不是实际的数据更改。这对于一些简单的数据库操作（如插入几条记录）可以大大减少网络传输和存储开销。
  - 兼容性好，对于一些不涉及复杂数据类型（如 BLOB 类型）的操作，几乎所有的 SQL 语句都能很好地复制。
- 缺点：
  - 存在潜在的一致性问题。例如，在主服务器上执行 INSERT INTO table (col1) VALUES (NOW()); 语句，主从服务器上的 NOW() 函数返回的时间可能不同，导致数据不一致。
  - 对于一些不确定的函数（如 RAND()）或存储过程中使用了不确定的操作，可能会在主从服务器上产生不同的结果。
基于行的复制（Row - Based Replication，RBR）
- 基于行的复制中，主服务器将每一行数据的更改记录到二进制日志中。从服务器在应用中继日志时，直接应用这些行数据的更改。
- 优点：
  - 数据一致性更好，因为记录的是实际的数据更改，避免了由于函数执行结果不同等原因导致的不一致问题。
  - 对于一些复杂的操作（如对 BLOB 类型数据的修改）能更好地支持。
- 缺点：
  - 日志量较大，因为要记录每一行数据的更改，相比于基于语句的复制，会占用更多的存储空间和网络带宽。
混合模式复制（Mixed - Based Replication，MBR）
- 混合模式复制结合了基于语句的复制和基于行的复制的优点。MySQL 会根据具体的 SQL 语句自动选择使用基于语句的复制还是基于行的复制。
- 一般情况下，对于能够保证主从一致性的 SQL 语句（如简单的 INSERT、UPDATE 等），使用基于语句的复制；对于可能导致不一致的语句（如包含不确定函数的语句），则使用基于行的复制。

同步复制中的常见问题及解决方法

主从延迟
- 原因：
  - 主服务器压力过大，导致二进制日志生成速度过快，从服务器的 I/O 线程和 SQL 线程无法及时跟上。例如，主服务器上有大量的并发写入操作，每秒产生大量的二进制日志记录。
  - 从服务器硬件性能不足，如 CPU、内存或磁盘 I/O 性能较差，导致 SQL 线程执行中继日志的速度较慢。
  - 网络延迟，从服务器与主服务器之间的网络不稳定，I/O 线程获取二进制日志数据时出现延迟。
- 解决方法：
  - 优化主服务器负载，例如通过缓存技术（如 Memcached 或 Redis）减少数据库的直接读写压力，合理设计数据库架构，避免不必要的复杂查询。
  - 提升从服务器硬件性能，增加 CPU 核心数、扩大内存或更换更快的磁盘（如 SSD）。
  - 检查网络连接，确保从服务器与主服务器之间的网络稳定。可以通过调整网络带宽、优化网络拓扑等方式来减少网络延迟。
复制中断
- 原因：
  - 主从服务器之间的网络故障，如网线松动、网络设备故障等，导致 I/O 线程无法连接到主服务器获取二进制日志。
  - 主服务器或从服务器的 MySQL 服务异常重启，可能会导致复制相关的配置信息丢失或损坏。
  - 主服务器上的二进制日志文件被误删除或损坏，从服务器无法继续从正确的位置读取日志。
- 解决方法：
  - 检查网络连接，修复网络故障后，在从服务器上重新启动复制：

START SLAVE;

 - 如果是 MySQL 服务重启导致的问题，重新配置主从复制（按照前面配置 MySQL 同步复制的步骤进行）。
 - 如果主服务器的二进制日志文件损坏或丢失，可以尝试从备份中恢复二进制日志，或者重新配置主从复制，从最新的备份点开始同步。

代码示例深入解析

示例场景
- 假设我们有一个简单的数据库 test_db，其中有一张表 employees，表结构如下：

CREATE DATABASE test_db;
USE test_db;
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

主服务器操作及日志记录
- 在主服务器上插入一条数据：

INSERT INTO employees (name, salary) VALUES ('John Doe', 5000.00);

查看主服务器的二进制日志（这里以简单文本查看为例，实际生产环境中可以使用 mysqlbinlog 工具），基于语句的复制下，二进制日志可能记录如下：

# at 123
#191001 10:00:00 server id 1  end_log_pos 180 CRC32 0x12345678  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1570096800/*!*/;
INSERT INTO employees (name, salary) VALUES ('John Doe', 5000.00)/*!*/;

这里记录了执行插入语句的时间戳、服务器 ID、语句内容等信息。
基于行的复制下，二进制日志记录会更详细，例如：

# at 123
#191001 10:00:00 server id 1  end_log_pos 250 CRC32 0x12345678  Table_map: `test_db`.`employees` mapped to number 100
# at 250
#191001 10:00:00 server id 1  end_log_pos 320 CRC32 0x12345678  Write_rows: table id 100 flags: STMT_END_F
### INSERT INTO `test_db`.`employees`
### SET
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2='John Doe' /* VARSTRING(100) meta=100 nullable=1 is_null=0 */
### @3=5000.00 /* DECIMAL(10,2) meta=28 nullable=1 is_null=0 */

这里先记录了表的映射信息，然后详细记录了插入行的数据。

从服务器的同步过程
- I/O 线程从主服务器获取二进制日志，写入中继日志。假设中继日志记录如下（基于语句的复制）：

# at 123
#191001 10:00:00 server id 1  end_log_pos 180 CRC32 0x12345678  Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1570096800/*!*/;
INSERT INTO employees (name, salary) VALUES ('John Doe', 5000.00)/*!*/;

SQL 线程读取中继日志并执行：

SET TIMESTAMP=1570096800;
INSERT INTO employees (name, salary) VALUES ('John Doe', 5000.00);

这样就完成了从服务器的数据同步。如果是基于行的复制，SQL 线程会根据中继日志中记录的行数据信息进行插入操作，同样实现数据同步。

同步复制在高可用架构中的应用

读写分离
- 在高并发的 Web 应用中，读操作往往远远多于写操作。通过 MySQL 同步复制，可以将主服务器用于处理写操作，而将从服务器用于处理读操作。这样可以有效减轻主服务器的负载，提高系统的整体性能。
- 例如，在 PHP 应用中，可以通过配置数据库连接来实现读写分离。假设使用 PDO 连接数据库：

<?php
// 写操作连接主服务器
$writePDO = new PDO('mysql:host=master_host;dbname=test_db', 'username', 'password');
// 读操作连接从服务器
$readPDO = new PDO('mysql:host=slave_host;dbname=test_db', 'username', 'password');

// 写操作示例
$stmt = $writePDO->prepare('INSERT INTO employees (name, salary) VALUES (:name, :salary)');
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->bindParam(':salary', $salary, PDO::PARAM_STR);
$name = 'Jane Smith';
$salary = 6000.00;
$stmt->execute();

// 读操作示例
$stmt = $readPDO->prepare('SELECT * FROM employees');
$stmt->execute();
$employees = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($employees as $employee) {
    echo $employee['name'].': '.$employee['salary'].'<br>';
}
?>

故障恢复
- 当主服务器出现故障时，可以将其中一台从服务器提升为主服务器，继续提供服务。例如，假设主服务器突然宕机，我们可以在从服务器上执行以下操作将其提升为主服务器：

STOP SLAVE;
RESET MASTER;

然后调整应用程序的数据库连接配置，将写操作指向新的主服务器。同时，可以启动新的从服务器，与新主服务器进行同步复制，以恢复系统的高可用性。

同步复制的性能优化

优化二进制日志记录
- 选择合适的复制类型：如前所述，根据应用场景选择合适的复制类型。如果应用中大多是简单的 SQL 语句且对一致性要求不是特别高，可以选择基于语句的复制以减少日志量；如果对数据一致性要求严格，涉及复杂数据类型或不确定函数的操作较多，则选择基于行的复制。
- 调整二进制日志写入策略：通过 sync_binlog 参数来控制二进制日志的写入频率。sync_binlog = 0 表示 MySQL 不主动将二进制日志刷入磁盘，由操作系统负责缓存和刷盘，这样性能最高，但在系统崩溃时可能会丢失部分二进制日志；sync_binlog = 1 表示每次事务提交时都将二进制日志刷入磁盘，保证数据不丢失，但会降低性能。一般可以根据实际情况选择一个合适的值，如 sync_binlog = 100，表示每 100 次事务提交将二进制日志刷入磁盘，在性能和数据安全性之间取得平衡。
优化从服务器性能
- 调整中继日志配置：通过 relay_log_space_limit 参数限制中继日志占用的空间。当达到限制时，从服务器会自动清理不再需要的中继日志，避免中继日志占用过多磁盘空间。
- 优化 SQL 线程执行：可以调整 slave_parallel_workers 参数，开启多线程复制。从 MySQL 5.6 开始支持多线程复制，该参数指定了用于并行执行中继日志的线程数。例如，设置 slave_parallel_workers = 4，可以同时使用 4 个线程执行中继日志，提高从服务器应用日志的速度，减少主从延迟。

不同 MySQL 版本对同步复制的改进

MySQL 5.6
- 引入了多线程复制（Multi - Threaded Slave，MTS），通过 slave_parallel_workers 参数可以指定从服务器用于并行执行中继日志的线程数。这大大提高了从服务器应用中继日志的速度，有效减少了主从延迟。
- 改进了基于行的复制性能，优化了二进制日志的记录方式，减少了日志量，同时提高了复制的效率。
MySQL 5.7
- 对多线程复制进行了进一步优化，采用了基于逻辑时钟的并行复制（Logical - Clock - Based Parallel Replication）。这种方式通过在二进制日志中添加逻辑时钟信息，使从服务器能够更有效地并行执行中继日志，进一步提高了复制性能。
- 增强了复制的安全性，例如在复制用户权限管理方面进行了改进，提高了系统的安全性和稳定性。
MySQL 8.0
- 改进了复制拓扑的管理，如支持更多类型的复制拓扑结构（如环形复制等），使复制架构更加灵活。
- 优化了元数据锁（Metadata Lock，MDL）的处理，在复制过程中减少了由于 MDL 锁导致的阻塞，提高了复制性能和系统的并发处理能力。

通过深入理解 MySQL 的同步复制机制，包括其工作原理、配置方法、常见问题解决以及在不同场景下的应用和优化，开发人员和数据库管理员可以更好地构建高可用、高性能的数据库系统，满足各种业务需求。无论是简单的小型应用，还是复杂的大型分布式系统，MySQL 同步复制都能为数据的备份、恢复和负载均衡提供强大的支持。同时，随着 MySQL 版本的不断更新，同步复制机制也在持续改进和优化，我们需要不断关注和学习新的特性，以充分发挥 MySQL 的优势。