MySQL 主从复制、主主复制

分类：MySQL| 发布时间：2019-11-16 20:40:00

本文主要描述了如何设置 MySQL 主从复制、主主复制，位点信息如何确定，等常见问题以及解决方案。

MySQL 主从复制

假设我们有如下两台主机：

hostA(192.168.1.100)
hostB(192.168.1.101)

两台机器上均安装了相同版本的 MySQL，现在我们需要进行配置使得 hostA 作为主机，hostB 作为从机，进行复制。 假设目前 hostA 和 hostB 的数据库是已经同步的，步骤如下：

两台机器都开启了 binlog，并且 hostA 和 hostB 的 server-id 不一样
在 hostA 进行以下操作

mysql> CREATE USER 'repl'@'192.168.1.101';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.101' IDENTIFIED BY '123456';

在 hostB 尝试使用 repl 用户进行登录，如果无法登录需要先排查原因

$ mysql -h 192.168.1.100 -urepl -p123456

在 hostA 上执行

mysql> show master status \G;
*************************** 1. row ***************************
    File: mysql-bin.000027
    Position: 2221
    Binlog_Do_DB:
    Binlog_Ignore_DB:

在 hostB 上执行以下命令，各个参数的含义为：
- MASTER_HOST 用于指定从哪个数据库进行复制
- MASTER_USER 和 MASTER_PASSWORD 用于指定登录到主数据库进行复制时使用的用户名和密码
- MASTER_LOG_FILE 和 MASTER_LOG_POS 用于指定从主数据库的哪个 binlog 的哪个偏移开始复制，由于我们在上面假设了数据库开始复制前两个机器的数据库状态是一致的，因此在这里我们指定了从主数据库的当前位点进行复制（也就是 show master 命令得到的 File 和 Position 信息）

mysql> CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl',
    MASTER_PASSWORD='123456',
    MASTER_LOG_FILE='mysql-bin.000027',
    MASTER_LOG_POS=2221;

在 hostB 上执行

mysql> START SLAVE;

确认配置是否成功

# hostB
mysql> show slave status \G;
*************************** 1. row ***************************
    Slave_IO_State: Waiting for master to send event
    Master_Host: 10.0.3.120
    Master_User: repl
    Master_Port: 3306
    ...
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

在 hostB 执行 show slave status 会看到 Slave_IO_Running 和 Slave_SQL_Running 的值都是 Yes，表示相关的线程都启动了。接下来在 hostA 执行:

mysql>select * from information_schema.processlist as p where p.command = 'Binlog Dump' \G;
*************************** 1. row ***************************
     ID: 24
   USER: repl
   HOST: mysql2:45786
     DB: NULL
COMMAND: Binlog Dump
   TIME: 3666
  STATE: Master has sent all binlog to slave; waiting for more updates
   INFO: NULL
1 row in set (0.00 sec)

至此 MySQL 的主从复制配置已经完成了。

在上面我们进行 MySQL 的主从复制配置的时候，假设了复制前 hostA 和 hostB 是已经同步的了，如果创建复制前 hostA 和 hostB 是不同步的该如何处理呢？

这时候，如果知道 hostB 当前状态对应于 hostA 的哪个位点，并且 hostA 上相关的 binlog 文件都在，则根据实际情况设置 MASTER_LOG_FILE 和 MASTER_LOG_POS 参数即可。如果不知道对应的位点信息，则可以先对 hostA 的数据库进行物理备份，然后恢复到 hostB 上，最后使用物理备份集中记录的位点信息进行同步。（也就是备份集中 xtrabackup_binlog_info 的信息）。

基于 GTID 的主从复制方案

在上面进行主从复制的配置的时候，从机需要从主机的哪个 binlog 的哪个位置开始复制，这个关键的信息是需要我们提供的。

从 MySQL 5.6 开始，MySQL 引入了 GTID。在开启 GTID 的情况下，可以让 MySQL 自动确定要复制的内容，而不用我们手动给出位点信息。

要使用 MySQL 的 Auto-Positioning 首先需要 hostA 和 hostB 都开启 GTID。开启方式很简单，只需要在 MySQL 的配置文件设置如下选项：

gtid_mode=on
enforce_gtid_consistency = on

在 hostA 和 hostB 都开启 GITD 模式后，hostB 可以使用如下命令启用 MySQL 的 Auto-Positioning 功能：

mysql> CHANGE MASTER TO
    MASTER_HOST='192.168.1.100',
    MASTER_USER='repl',
    MASTER_PASSWORD='123456',
    MASTER_AUTO_POSITION=1;

Auto-Positioning 工作原理：

当从节点启用了 GTID，并且 CHANGE MASTER TO 命令开启了 MASTER_AUTO_POSITION 选项，Auto-Positioning 就默认启用了。这时主节点必须是已经开启了 GTID 否则连接会失败。从节点和主节点进行握手连接时，从节点会将当前已经接收和提交（也就是 SELECT @@GLOBAL.gtid_executed; 和 SELECT RECEIVED_TRANSACTION_SET FROM performance_schema.replication_connection_status; 的并集）的 GTID 发送给主节点。

主节点会将所有未包含在从节点的 GTID 集合中的事务发送过去。这保证了主节点只用发送从节点还没接收或者提交的事务。

如果主节点需要发送的事务的 binlog 已经被删除掉，或者在 gtid_purged 系统变量中，主节点会返回 ER_MASTER_HAS_PURGED_REQUIRED_GTIDS 错误给从节点，此时复制不会开始。从节点无法自动解决由于部分需要的事务历史已经被主节点删除的问题。尝试不使用 MASTER_AUTO_POSITION 选项进行重连只会导致从节点丢失这部分事务。正确的方法应该是从其他地方复制这部分事务，或者使用物理备份恢复来产生一个更接近主节点的从节点。

如果这交换事务阶段发现从节点中已经接收或者提交了部分属于主节点，但主节点没有的 GTID，主节点会返回 ER_SLAVE_HAS_MORE_GTIDS_THAN_MASTER 错误。这通常是由于主节点没有设置 sync_binlog=1 选项，然后发生了断电或者系统崩溃的情况，并且丢失的事务未被同步到 binlog 中但是从节点已经接收到了。当主节点重启后，有客户端连进来并且提交了事务会导致主节点和从节点发生偏离，此时会发生主节点和从节点使用了相同的 GTID，但是分别代表了不同的事务。此时需要手动检查并判断主节点和从节点是否已经发生偏离。如果发生使用相同 GTID 来代表不同的事务的情况，你需要手动解决这个冲突。如果只是主节点丢失了事务，你可以暂时地将主节点设置为从节点，使得主节点能从其他节点进行复制，当有需要再将其设置回主节点。

主主复制

上面讲了如何进行主从复制的配置，接下来讲下如何进行主主复制的配置。还是以上面的 hostA 和 hostB 为例子，主主又称双主，顾名思义，就是 hostA 和 hostB 是对等关系会互相复制。

我们来简单回顾下主从是如何配置的：

hostA 创建 repl 帐号并且授予 REPLICATION SLAVE 权限
hostB 使用 CHANGE MASTER TO 来声明 hostA 为其主节点，并提供相关信息，然后使用 start slave 开始复制

而主主复制的情况下 hostA 和 hostB 是对等的，因此还需要添加如下步骤：

hostB 创建 repl 帐号并且授予 REPLICATION SLAVE 权限
hostA 使用 CHANGE MASTER TO 来声明 hostA 为其主节点，并提供相关信息，然后使用 start slave 开始复制

为了避免自增 ID 字段的冲突，主主复制的情况下，通常还会进行以下配置：

hostA 的配置文件修改如下选项：

auto_increment_increment=2         # 步进值
auto_increment_offset=1            # 起始值

hostB 的配置文件修改如下选项：

auto_increment_increment=2         # 步进值
auto_increment_offset=2            # 起始值

也就是说对自增 ID 字段，hostA 总是使用奇数而 hostB 总是使用使用偶数，从而避免了冲突。

双主多从

既然说了主从和双主的配置，这里再简单说说双主多从的配置。双主多从主要用于需要对数据库进行读写分离的情况，业务系统选用其中一台主机进行写操作，将读操作分摊到多个从机中，从而减少主机的压力。

假设，我们有如下机器：

hostA(192.168.1.100)
hostB(192.168.1.101)
hostC(192.168.1.102)
hostD(192.168.1.103)
hostE(192.168.1.104)

我们需要将 hostA 和 hostB 设为主机，hostC、hostD 和 hostE 作为从机。并且将 hostA 作为当前的主服务器。

我们需要进行如下配置：

首先确保每个机器的 server_id 都不一样。
将 hostA 和 hostB 配置为主主复制，由于默认情况下 MySQL 并不会将复制过来的数据写到 binlog。这会导致作为从机的主机时不会将另一台主机的数据复制给从机，这明显不是我们预期的。为了能将复制过来的数据写入 binlog 我们需要对 hostA 和 hostB 设置选项 log-slave-updates=ON。
在 hostC、hostD、hostE 执行 CHANGE MASTER TO 将 hostA 作为 MASTER

可以考虑将其中一个从库配置为延迟复制从库，方法是在 CHANGE MASTER TO 的时候加上参数 MASTER_DELAY=N，N为数字单位为秒。通过延迟复制从库，可以预防一些误删除操作，比如主库发生误删表的操作，如果没有延迟复制从库，那么错误会马上传递到所有的从库，导致数据难以恢复。如果你设置了一个 N=3600 的从库，那么当发生误删后，你可以查出误删操作对应的 GTID，到从库执行以下操作：

stop slave;
set gtid_next=gtid1;begin;commit;
CHANGE MASTER TO MASTER_DELAY=0;
start slave;

当这个延迟从库追上主库后，将数据导出来拿到主库导入，然后在延迟从库上执行以下操作：

stop slave;
CHANGE MASTER TO MASTER_DELAY=3600;
start slave;

当需要切换到 hostB 作为主服务器的时候，需要进行以下操作：

查看 hostB 的 show slave status; 的 Seconds_Behind_Master 小于一定数值后（比如 10 秒）进行下一步，否则继续等待
将 hostA 设置为只读 SET GLOBAL.read_only=ON;
等待 hostB 的 Seconds_Behind_Master 为 0，将 hostB 设为可读写，也就是 SET GLOBAL.read_only=OFF;
将业务流量切换到 hostB

一般来说，主主复制的主服务器切换是通过 HA 系统自动完成的。

小结

现在来回顾一下，本文主要讲述了以下要点：

主从复制如何设置查询与设置位点信息
如何启用 GTID Auto-Positioning
如何设置主主复制，以及需要配置 auto_increment_increment 和 auto_increment_offset 选项
如何进行主主多从的设置，通过 MASTER_DELAY 设置延迟复制从库，以及发生误删操作后，如何利用延迟复制从库进行数据的恢复
主主多从的情况下，如何进行主服务器的切换

凌云的博客

行胜于言