mysql主从复制基于binlog+position

封面图 • 2025-11-01 • MySQL

MySQL 主从复制是一种常见的高可用性和负载均衡解决方案。通过主从复制，可以将主服务器（Master）上的数据同步到一个或多个从服务器（Slave），从而实现读写分离、数据备份等功能。

主从复制通常基于两种机制：基于二进制日志（Binary Log，简称 Binlog）和基于 GTID（Global Transaction Identifier）。这里我们讨论的是基于 Binlog 和 Position 的主从复制机制。

### 基本原理

1. **Binlog（二进制日志）**：
   - MySQL 的 Binlog 是一种记录数据库更改事件的日志文件。
   - 当主服务器执行了某些操作（如 `INSERT`、`UPDATE` 或 `DELETE`），这些操作会被记录在 Binlog 中。
   - 从服务器通过读取主服务器的 Binlog 来获取这些更改，并应用到自己的数据库中。

2. **Position（位置）**：
   - 每次主服务器执行的 Binlog 操作都会有一个唯一的序列号（称为 Log Sequence Number, LSN）。
   - 从服务器会跟踪自己已经应用的 Binlog 位置（Position），以确保不会重复应用已处理过的更改。
   - 从服务器需要知道从哪个位置开始读取 Binlog 文件。

### 主从复制的配置步骤

#### 1. 配置主服务器

1. **启用 Binlog**：
   在主服务器的 `my.cnf` 配置文件中添加以下内容：
   ```ini
   [mysqld]
   server-id=1
   log_bin=/var/lib/mysql/mysql-bin.log
   binlog_format=ROW
   ```
   - `server-id`：每个服务器必须有唯一的 ID。
   - `log_bin`：指定 Binlog 文件的存储路径。
   - `binlog_format=ROW`：建议使用 ROW 格式，因为它能更精确地记录每一行的变化。

2. **创建用于复制的用户**：
   在主服务器上创建一个专门用于复制的用户，并赋予其复制权限：
   ```sql
   CREATE USER 'replica_user'@'%' IDENTIFIED BY 'password';
   GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
   FLUSH PRIVILEGES;
   ```

3. **锁定表并导出数据**：
   在主服务器上锁定所有表，以便导出数据：
   ```sql
   FLUSH TABLES WITH READ LOCK;
   ```
   然后导出数据：
   ```bash
   mysqldump --all-databases > /path/to/backup.sql
   ```
   导出完成后解锁表：
   ```sql
   UNLOCK TABLES;
   ```

4. **记录当前 Binlog 的位置**：
   在主服务器上执行以下命令，记录当前的 Binlog 文件名和位置：
   ```sql
   SHOW MASTER STATUS;
   ```
   输出示例：
   ```
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001 |      123 |              |                  |
   +------------------+----------+--------------+------------------+
   ```
   记录下 `File` 和 `Position` 的值。

5. **将备份数据传输到从服务器**：
   将刚刚导出的数据文件传输到从服务器。

#### 2. 配置从服务器

1. **安装 MySQL 并导入数据**：
   在从服务器上安装 MySQL，并将主服务器导出的数据导入：
   ```bash
   mysql < /path/to/backup.sql
   ```

2. **配置从服务器**：
   在从服务器的 `my.cnf` 文件中添加以下内容：
   ```ini
   [mysqld]
   server-id=2
   relay_log=/var/lib/mysql/mysql-relay-bin.log
   log_bin=/var/lib/mysql/mysql-bin.log
   binlog_format=ROW
   read_only=ON
   ```

3. **设置主服务器信息**：
   在从服务器上执行以下命令，配置主服务器的信息：
   ```sql
   CHANGE MASTER TO
   MASTER_HOST='主服务器IP',
   MASTER_USER='replica_user',
   MASTER_PASSWORD='password',
   MASTER_LOG_FILE='mysql-bin.000001',
   MASTER_LOG_POS=123;
   ```
   这里的 `MASTER_LOG_FILE` 和 `MASTER_LOG_POS` 是你在主服务器上记录的值。

4. **启动从服务器**：
   启动从服务器的复制进程：
   ```sql
   START SLAVE;
   ```

5. **检查从服务器状态**：
   查看从服务器的状态，确保复制正常运行：
   ```sql
   SHOW SLAVE STATUS\G
   ```
   关注以下两个字段：
   - `Slave_IO_Running`: 应为 `Yes`
   - `Slave_SQL_Running`: 应为 `Yes`

### 注意事项

- **网络延迟**：主从服务器之间的网络延迟可能会影响复制性能。
- **Binlog 文件大小**：如果 Binlog 文件过大，可能会占用大量磁盘空间。
- **延迟问题**：从服务器可能无法实时同步主服务器的数据，特别是在高并发情况下。
- **数据一致性**：确保主从服务器的数据一致，避免因网络问题导致的数据不一致。

通过以上步骤，你可以成功配置基于 Binlog 和 Position 的 MySQL 主从复制。