MySQL ---主从复制

后端程序员Aska
577 阅读9分钟

MySQL 主从复制（Master-Slave Replication）是一种数据复制技术，用于在多个数据库服务器之间的数据同步。在主从复制架构中，一个服务器被设置为主服务器（Master），充当数据源，其他服务器被设置为从服务器（Slave），用来复制主服务器的数据。

1.主从复制优点

主从复制的主要优点有以下几个：

  1. 高可用性：通过将主数据库的数据复制到一个或多个从数据库，可以在主数据库故障时快速切换到从数据库，以实现系统的高可用性和容错能力，从而保证系统的持续可用性。
  2. 提高整体性能和吞吐量：通过将读请求分散到多个从服务器上进行处理，从而减轻了主服务器的负载压力，提高数据库系统的整体性能和吞吐量。主服务器主要负责写操作，而从服务器主要负责读操作，从而分担了主服务器的压力。
  3. 数据备份和恢复：通过主从同步，可以将主服务器上的数据异步复制到从服务器上，从而实现数据备份和灾难恢复的需求。在应对意外数据丢失、灾难恢复或误操作时，可以使用从服务器作为数据的备份源来进行数据恢复。

2.如何实现主从复制？

① 配置并重启主服务器

在主服务器的配置文件（my.cnf）中添加以下参数：

[mysqld] server-id = 1 # 设置服务器 ID，每个主服务器和从服务器都必须有唯一的 ID

log_bin = /var/log/mysql/mysql-bin.log # 开启二进制日志，记录数据修改操作

以上配置完成之后，重启 MySQL 服务器，因为重启了 MySQL 服务才能让配置生效。

② 创建用于主从复制的用户

登录到主服务器上，执行以下命令：


-- 替换为实际的用户名和密码
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';    
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';




这段代码是 MySQL 中用于创建一个用户，并授予该用户复制权限的 SQL 语句。

- **`CREATE USER 'repl'@'%' IDENTIFIED BY 'password';`**:
  - 创建一个名为 `repl` 的新用户。
  - `repl` 是用户名，`'%'` 表示这个用户可以从任意主机（IP 地址）连接到数据库。
  - `IDENTIFIED BY 'password'` 设置该用户的密码为 `'password'`，你可以将 `'password'` 替换为你想要的实际密码。

- **`GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';`**:
  - 授予 `repl` 用户复制从服务器（Replication Slave）的权限。
  - `REPLICATION SLAVE` 权限允许该用户读取主服务器的二进制日志（binary log），这是进行主从复制时必需的权限。
  - `*.*` 表示该权限适用于所有数据库和所有表。
  - `'repl'@'%'` 指定该用户的主机为任意主机。

### 总结：
这段代码的作用是创建一个名为 `repl` 的用户，并且授予该用户从任何主机连接的权限，以及进行 MySQL 主从复制的权限

③ 查看主服务器状态

在 MySQL 主服务器中，执行以下命令，记录下 File 和 Position 的值，后续用于配置从服务器：

SHOW MASTER STATUS;

④ 配置并重启从服务器

在从服务器的配置文件（my.cnf）中添加以下参数：

[mysqld]

server-id = 2 # 设置服务器 ID，每个主服务器和从服务器都必须有唯一的 ID

重启从服务器，让以上配置生效。

⑤ 在从服务器上设置主服务器信息


登录到从服务器的 MySQL 中，执行以下命令
（将 MASTER\_HOST、MASTER\_USER、MASTER\_PASSWORD、
MASTER\_LOG\_FILE 和 MASTER\_LOG\_POS 替换为对应的值）


CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl', 
MASTER_PASSWORD='password', MASTER_LOG_FILE='binlog_file', 
MASTER_LOG_POS=log_file_position;


这段 SQL 语句是 MySQL 中用来设置从服务器连接到主服务器并开始主从复制的命令。

### 语句解析：

**`CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='binlog_file', MASTER_LOG_POS=log_file_position;`**

- **`MASTER_HOST='master_ip'`**：
  - 这个参数指定主服务器的 IP 地址或者主机名。例如，`master_ip` 应该替换为主服务器的 IP 地址，指示从服务器连接到哪个主服务器。

- **`MASTER_USER='repl'`**：
  - 这个参数指定从服务器用来连接主服务器的用户名。这里的 `repl` 是在前面的 SQL 语句中创建的复制用户的用户名。

- **`MASTER_PASSWORD='password'`**：
  - 这个参数指定用于连接主服务器的密码。`password` 应该是你为 `repl` 用户设置的密码。

- **`MASTER_LOG_FILE='binlog_file'`**：
  - 这个参数指定主服务器上的二进制日志文件名。`binlog_file` 应该是主服务器的二进制日志文件的名称，通常是类似 `mysql-bin.000001` 这样的文件名。

- **`MASTER_LOG_POS=log_file_position`**：
  - 这个参数指定主服务器上的二进制日志的偏移位置。`log_file_position` 是日志文件中从服务器开始读取的偏移量，通常你可以通过在主服务器上执行 `SHOW MASTER STATUS;` 命令获得。

### 总结：
这条命令的作用是设置从服务器的复制参数，使从服务器可以从主服务器的指定二进制日志文件和偏移位置开始进行主从复制。通过这个命令，从服务器会连接到主服务器，并获取从主服务器的最新数据。

⑥ 启动从服务器的复制进程

执行以下命令启动从服务器的复制进程：

START SLAVE;

⑦ 检查从服务器的复制状态

- 执行以下命令，确保 Slave\_IO\_Running 和 Slave\_SQL\_Running 的值都为 "YES"：

SHOW SLAVE STATUS \G;

3.主从复制原理

MySQL 数据库的主从复制主要是基于 Binary Log（二进制文件，简称 bin log）实现的，它的实现流程如下： 它的主要执行流程如下：

  1. 主数据库接收到一个写操作（如 INSERT、UPDATE、DELETE）时，会将这个操作记录到二进制日志（Binary Log）中，将数据修改的操作按顺序记录下来。
  2. 从数据库 IO 线程会自动连接主服务，从二进制中读取同步数据，记录到中继日志（Relay Log）中。
  3. 从数据库的 SQL 线程会定期从中继日志中获取同步数据，写入到从数据库中。

4.Bin Log 日志格式

Binary Log 二级制日志，它总共有以下三种格式（不同的日志格式决定了不同的主从同步效果）：

  1. STATEMENT 格式（语句模式，出现在 MySQL 5.1 之前） ：在这种格式下，binlog 记录的是执行的 SQL 语句的文本。

    1. 优点：日志文件通常较小，复制效率较高。
    2. 缺点：在某些情况下，由于数据库环境的差异（如表结构、字符集等），在从服务器上重放这些 SQL 语句可能会导致不一致的结果。例如，获取当前时间的函数或存储过程等，可能会导致数据不一致。

  2. ROW 格式（行模式，诞生于 MySQL 5.1） ：在这种格式下，binlog 记录的是每一行数据更改的具体内容。

    1. 优点：能够精确地记录数据的变化，避免了 STATEMENT 格式中的环境依赖问题，提供了更强的一致性保证。
    2. 缺点：日志文件可能会比 STATEMENT 格式大，因为记录了每一行的详细变化。此外，ROW 格式的日志在进行大量数据更新时可能会导致更高的 I/O 开销。

  3. MIXED 格式（混合模式） ：在这种格式下，binlog 可以根据具体的 SQL 语句和操作自动选择使用 STATEMENT 或 ROW 格式。

    1. 优点：结合了 STATEMENT 和 ROW 格式的优点，能够在保证一致性的同时尽可能地优化日志大小和复制性能。
    2. 缺点：由于混合使用了两种格式，可能需要更复杂的管理和监控。在某些特定情况下，MIXED 格式可能无法达到最优的性能或一致性。

5.主从复制模式

MySQL 中主要有以下三种主从复制的模式，分别是异步复制和半同步复制和全同步。

  1. 异步复制：MySQL 主从复制中最常见和默认的模式。在异步复制模式中，主服务器将数据修改操作记录到二进制日志（Binary Log）中，并将日志传输给从服务器。从服务器接收到二进制日志后，会异步地应用这些日志进行数据复制。

    1. 优点：它的优点是及时响应给使用者，主服务器不会受到从服务器的影响而等待确认，可以提高主服务器的性能。
    2. 缺点：由于是异步复制，可能存在数据传输的延迟，且从服务器上的复制过程是不可靠的。如果主服务器故障，尚未应用到从服务器的数据可能会丢失。

  2. 半同步复制：半同步复制是 MySQL 主从复制中的一种增强模式。在半同步复制模式中，主服务器将数据修改操作记录到二进制日志，并等待至少一个从服务器确认已接收到并应用了这些日志后才继续执行后续操作。

    1. 优点：可以提供更高的数据一致性和可靠性，确保至少一个从服务器与主服务器保持同步。如果主服务器故障，已经确认接收并应用到从服务器的数据不会丢失。
    2. 缺点：由于半同步复制需要等待从服务器的确认，因此相对于异步复制，会增加一定的延迟，可能会影响主服务器的性能。

  3. 全同步复制：半同步复制是 MySQL 主从复制中的一种增强模式。在半同步复制模式中，主服务器将数据修改操作记录到二进制日志，并等待至少一个从服务器确认已接收到并应用了这些日志后才继续执行后续操作。

如果对数据一致性和可靠性要求较高，可以考虑使用半同步复制；如果对延迟和主服务器性能要求较高，可以继续使用异步复制，根据实际需求调整复制模式。

小结

MySQL 主从复制用于多个数据库服务器之间的数据同步，它可以提供高可用性、提高数据库整体性能和吞吐量，以及可以进行数据备份和数据库恢复。MySQL 主从复制是通过 bin log 实现的，主服务写入操作会同时添加到 bin log 中，而从数据库定期拉取主数据库的 bin log，然后将拉取的数据存放到自己的 relay log 中，之后再由单独 SQL 线程将数据写入到从数据库中，此时 MySQL 的主从同步就完成了。

avatar
文章
阅读
粉丝