MySQL主从复制

本篇笔记实际使用的是MariaDB数据库，但配置起来与MySQL配置方法无异，可正常套用：

主从搭建

MySQL的复制默认的异步的，主从复制至少需要两个MySQL服务，这些服务可以分布在不同服务器上，也可以在同一个台服务器上。测试环境使用虚拟机复制一台虚拟主机出来。

确保主从库安装了相同版本的数据库。因为主从库的角色可能会互换，同时减少出问题的概率，所以在可能的情况下推荐安装最新的稳定版本。
在主库上，设置一个复制使用的账户，并授予REPLICATION SLAVE权限。这里创建一个复制用户rep1，允许IP为192.168..0.33的主机进行连接。

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repl'@192.168.0.33 IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.203 sec)

修改主数据库文件的配置文件my.cnf，开启BINLOG，并设置server-id的值。这两个参数的修改需要重启MySQL服务才会生效。

log-bin=mysql-bin
server-id   = 1

在主库上设置读锁定有效，这个操作是为了确保没有数据库操作，以便获得一个一致性的快照：

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.106 sec)

然后得到主库上当前的二进制日志名和偏移量。这个操作的目的是为了在从数据库启动后从这个点开始进行数据恢复。

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000016 |      537 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

现在主数据库服务器已经停止更新操作，需要生成主数据库的备份数据，把备份下来的全部数据文件复制到从服务器上。（说白了就是这个时候主库和从库的数据要一样，由于我是直接复制的整个系统镜像，就不需要指这一步操作了）
主数据库备份完毕后，可以恢复写操作，剩下的操作只需要在从库上执行：

MariaDB [(none)]> UNLOCK TABLES;
Query OK, 0 rows affected (0.000 sec)

将主数据库备份的数据恢复到从数据库上。（意思就是把主库刚刚停止写入后的那个数据拿过来放在从库上，让主从库的数据一致）
修改从数据库的配置文件my.cnf，设置server-id参数，注意值是唯一的，不能与主库相同，如果有多个从数据库服务器，每一个从数据库服务器必须有自己唯一的server-id值。（如果使用了GTID模式，同时删除datadir目录下的auto.cnf，避免与主库使用相同的UUID）

server-id   = 2

在从库上使用--skip-slave-start选项启动从数据库，这样不会立即启动从数据库的复制进程，方便对数据库上的服务进行进一步配置：
对从数据库服务器作相应的配置，指定复制使用的用户、主服务器 IP、端口、以及开始执行复制的日志文件和位置等信息，写法如下：

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.22',
    -> MASTER_PORT=3306,
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl',
    -> MASTER_LOG_FILE='mysql-bin.000016',
    -> MASTER_LOG_POS=537;
Query OK, 0 rows affected (0.125 sec)

在从库上启动slave线程

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)

这是slave 上执行show processlist命令将看到以下的进程

MariaDB [test]> show processlist\G;
*************************** 1. row ***************************
      Id: 1
    User: system user
    Host: 
      db: NULL
 Command: Daemon
    Time: NULL
   State: InnoDB purge coordinator
    Info: NULL
Progress: 0.000
*************************** 2. row ***************************
      Id: 2
    User: system user
    Host: 
      db: NULL
 Command: Daemon
    Time: NULL
   State: InnoDB purge worker
    Info: NULL
Progress: 0.000
......

这表明slave已经连接上master，开始接受并执行日志。主从复制服务配置完成。

MySQL主从异步复制是最常见和最简单的复制场景。数据的完整性完全依赖于主库的BINLOG的不丢失，只要主库的BINLOG不丢失，就算主库宕机了，我们还可以通过BINLOG把丢失部分的数据手动的同步到从库上去。

遇到的问题

配置完从库之后，主库修改数据并没有同步过来，经过排查，发现问题出在两个地方：

主库的服务器没有开放数据库端口，从库无法从外部访问主库：

设置Linux系统开放指定端口，允许外部可以访问。

注意：如果是云服务器的话，只能开内网端口，不要开云服务器的防火墙端口，端口暴露给公网，风险很大。

Slave_SQL_Running配置问题：

通过使用命令show slave status\G;发现，Slave_SQL_Running的配置项为NO，需要修改为YES，步骤如下：

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.000 sec)

此时再次通过命令查看配置项正常，修改主库数据，从库同步也正常了。

auto.conf冲突：

备注：这个问题使用MySQL配置时可能会需要解决，本次配置使用MariaDB没有这个文件，不需要处理。

如果和我一样是直接复制了一个系统环境（如虚拟机复制，云服务器备份镜像等方法弄的从库服务器），那么数据目录的auto.cnf文件理论上是一样的，会和主库冲突，解决办法是删除掉从库上面的auto.cnf文件。

同步延迟问题

由于主从复制的原理是将主库的语句通过读取 binlog 的方式在从库上执行，所以当出现高并发的场景时，从库的数据更新会有延迟。刚写入主库的数据紧接着在从库查询是查不到的，需要经过十几毫秒，甚至几百毫秒才可以查询到。

对于延迟问题可以使用下面的方式来处理

网络方面： 保证主从库之间的网络稳定，以及延迟较小
硬件方面： 从库使用更好的配置，提高随机读写的性能
并行复制： 在 MySQL 5.7 中，新增了多线程复制技术，解决了 master 同一个 schema 下数据发生变更，从库不能处理并发的问题。
事务方面： 事务操作尽量在主表读写，而非事务操作在从表读取，减少因延迟导致数据不一致的影响。

半同步复制

前面所说的都是异步复制，从库和主库之间难免会有一些延迟，这样会有一个隐患，当主库执行完一个事务并提交成功，而从库还未读取到主库上的 binlog 日志时，主库因为意外宕机，导致主库上该 binlog 丢失，此时从库会损失这个事务，从而造成主从不一致。为了解决这个问题 MySQL 5.5 引入了半同步机制。

传统半同步

在异步复制时，主库提交操作并写入 binlog 日志后即可返回数据操作成功，无需等待 binlog 日志传送给从库。

而半同步复制时，保证主库上的 binlog 日志，至少被一个从库所接受并成功写入中继日志后，主库才返回操作成功给客户端。

半同步保证了事务在成功提交之后，至少有两份日志记录。一份在主库的 binlog 日志上，另一份在从库的 Relay Log 上，从而保证了数据的完整性。

增强半同步

传统的半同步有一个问题，就是当主库提交操作之后，实际上主库的数据已经发生了更改，只是还没有返回给客户端，等待从库写入 Relay Log，如果这个时候主库宕机，从库还没有写入 Relay Log 就会发生数据不一致的问题，而增强半同步就是为了解决这个问题的。

增强半同步在主库的提交逻辑上做了微调，即主库写数据到 binlog 后，等待从库应答 ACK，直到至少一个从库写入了 Relay Log，并将数据落盘，然后返回给主库消息，通知主库可以执行 commit 操作，主库开始提交数据到事务引擎层，这个时候主库的数据才被修改。

在半同步复制模式下，假如在传送 BINLOG 日志到从库时，从库宕机或网络故障导致没有 BINLOG 日志没有及时的传送在从库上，此时主库会等待一段时间（由 rpl_semi_sync_master_timeout 设置的毫秒数决定），如果 BINLOG 日志在这段时间内都无法成功的发送到从库上，则 MySQL 自动调整复制模式为异步模式，事务正常返回提交给客户端。

MySQL主从搭建的笔记大概就写完了，这只是最简单的一种主从配置方法，其他的方法，以后的笔记会出。

更新日志：2021-09-26 更新半同步和延迟问题

参考资料：《深入浅出MySQL：数据库开发、优化与管理维护（第三版）》

Catalog