MySQL集群方案
时间：2022-09-04 00:00:00
文中的【// TODO】都是待完成的任务。
1. 简介
1). 集群的好处
① 高可用性：故障检测及迁移，多节点备份。
② 可伸缩性：新数据库节点方便，扩展方便。
③ 负载平衡：切换服务访问节点，分担单个节点数据库的压力。
2). 高可用架构需要考虑以下几个方面
① 如果数据库停机或意外中断，可以尽快恢复数据库的可用性，尽量减少停机时间，确保业务不会因数据库故障而中断。
② 用作备份、只读等功能的【非主节点】的数据应该和主节点的数据实时或者最终保持一致。
③ 当业务发生数据库切换时，切换前后的数据库内容应一致，不会因数据缺失或数据不一致而影响业务。
2.高可用性方案
从复制架构开始：① 主从复制(一主多从)；② MMM架构(双主多从)；③ MHA架构(多主多从)。
PS：由于目前不涉及读写分离的技术需求，目前只考虑双主模式。
2.1双主
由于是两个主节点，两个节点之间必然存在数据同步问题。
MySQL为我们提供：异步复制、半同步复制、全同步复制。
2.1.1 MySQL从复制原理出发
1).客户端在master上执行DDL、DML操作，记录数据变更 bin log(二进制日志)；
2).从库 slave 的I/O线程连接上 master，请阅读指定位置 position 日志内容；
3).master 收到从库 slave 请求后，指定位置 position 日志内容志内容和主库 bin log 从库中推送文件的名称和日志的位置 slave。 ① 通过 show master status 查看最新的 bin log 日志文件名称和位置等信息；
② 定位一个 logEvent 需要通过 binlog filename binlog position进行定位。
4).slave 的I/O线程接收到数据后，将接收到的日志内容依次写入 relay log(中继日志)文件的末尾，读取的主库 bin log 文件名和位置 position 记录到 master info 文件中，方便下次读的时候清楚的告诉。 master我需要从某个开始bin log日志的某个位置开始日志的未来内容。
5).slave 的 sql 线程检测到 relay log 内容更新后，读取日志并分析为可执行的 sql 重做句子。
2.1.2异步复制
MySQL 默认复制采用异步复制，主库在执行客户端提交的事务后会立即返回客户端，不管备库是否已接收处理。
可能出现的问题：当主库停机时，主库上提交的事务可能没有传输到仓库。异步复制会导致节点之间的数据不一致，即不一致。
2.1.3半同步复制
在执行客户端提交后，主库并没有立即返回客户端，而是等待至少一个从库中收到并写到 relay log 返回客户端的中才。
这种方案会对客户端造成一定的延迟，至少一个 TCP/IP 往返时间。
其实这个方案就是把原本只提交一个库的操作变成两个库。
优点：
① 不涉及双节点 master 停机后直接切换选主的问题。
② 结构简单，使用方便 MySQL 原生半同步复制是数据同步的基础。
缺点：无故障自动转移和负载平衡。
2.2 MHA架构
2.2.1简介
MHA（master high availability）是开源的 MySQL 目前在高可用程序中 MySQL 相对成熟的高可用解决方案。
MHA 以标准为基础 MySQL 复制(异步/半同步)，
MHA 由 管理节点（MHA Manager）和 数据节点（MHA Node）两部分组成。
MHA Manager 会定时探测集群中的 master 节点，当 master 节点出现故障时，它可以自动将拥有最新数据的 slave 节点提升为新的 master 节点。
MHA 还提供了 master 节点在线切换的功能，也就是说，可以按需切换 master/slave 节点。
2.2.2 MHA角色
MHA 服务有两种角色：MHA Manager（管理节点）、MHA Node（数据节点）。
MHA Manager：通常单独部署在一台机器上，用来管理多个 master/slave 集群（组），每个 master/slave 集群称作一个 application，用来管理统筹整个集群。
MHA Node：运行在每台 MySQL 服务器上（master/slave/manager），它通过监控具备解析和清理 logs 功能的脚本来加快故障转移。
2.2.3架构
目前 MHA 主要支持一主多从的架构，搭建 MHA 要求一个【复制集群】中必须【最少】有三台数据库服务器，一主二从，即一台 master，一台备用 master，另外一台从库。SO，至少需要三台服务器，再加上 MHA Manager 一台，可能就需要四台服务器。
工作流程如下：
MAH 的目的在于维护 MySQL replication 中 master 库的高可用性，其最大的特点是可以修复多个 slave 之间的差异日志，最终使所有 slave 保持数据一致。
| 1). 从宕机崩溃的 master 保存二进制日志事件（bin log events）。 2). 识别含有最新更新的 slave。 3). 应用差异的中继日志（relay log）到其他的 slave。 4). 把 master 保存的二进制日志事件（bin log events）应用到要提升为 master 节点的 slave。 5). 解除这个 slave 的只读模式，并提升为新 master。 6). 让其他的 slave 连接到新的 master 进行复制。 |
2.2.4优缺点
1).优点
① 可以监控多个集群
一个 MHA Manager 可以管理多个集群。
② 故障处理速度
在主从复制集群中，只要从库在复制上没有延迟，MHA 通常可以在数秒内实现故障切换。9-10秒内检测到 master 故障，可以在7-10秒关闭 master 以避免出现脑裂，几秒钟内，将差异中继日志（relay log）应用到新的 master 上，因此，总的宕机时间通常为 10-30 秒。恢复新的 master 后，MHA 并行恢复其余的 slave。
③ 无性能下降
MAH 适用于【异步或半同步复制】的 MySQL 复制。监控 master 时，MHA 仅仅是每隔几秒（默认是 3 秒）发送一个 ping 包，并不发送重查询，可以得到像原生 MySQL 复制一样的性能。
2).不足
① 只监控 master
MHA 只保证 master 的高可用，并没有监控 slave 的状态。加入某个 slave 出现复制中断、延迟增加等问题，都是不知道的。
② 没有集成虚拟 IP 的配置
在故障转移时，为了对外透明，需要使用虚拟 IP，MHA 没有实现 VIP，需要借助于 keepAlive。
③ 安全问题
MHA 要求所有服务器之间都配置 SSH 免登录。这样就存在一定的安全隐患，如果某台服务器出现了安全问题，那么就可能影响其他服务器。
3.MHA 搭建
3.1 数据同步
如果主从库的数据一模一样，那么这一步可以略过。因为在搭建主从复制之前需要保证主从库一致。
可以使用 Navicat 工具来保证多个MySQL服务保持一致，也可以使用其他方式，具体方法就各显神通吧。
3.2 MySQL主从搭建（异步复制）
【一主二从，异步复制】。
| 主库 |
107 |
| 从库 |
134、143 |
3.2.1 准备
3.2.1.1 主从库
【备注】：所有节点都要做如下配置（一劳永逸，目的是为了方便下面的MHA搭建）
3.2.1.1.1 配置
修改 MySQL 的配置文件（可通过命令 mysql --help | grep "my.cnf" 查看配置文件的位置）。
| # ① 开启 bin log（二进制日志） log_bin=/usr/local/mysql/logs/binlogs/mysql-bin # 一个事务刷盘一次（bin log 刷盘机制） sync_binlog=1 # bin log 的格式 binlog_format=ROW # ② 开启 relay log（中继日志） # 如果只是主从复制，仅在 slave 上配置即可；MHA 的话，就需要在所有 MySQL 节点上配置。 relay-log=/usr/local/mysql/logs/relaylogs/mysql-relay # 指定 slave 要复制主库上的哪个数据库，如果有多个的话，那就得配置多个 replicate-do_db，而不是用逗号把对应的数据库名给隔开。 replicate-do_db=ta_liquidation_sub_sh |
3.2.1.1.2 创建复制账号
| # ① 创建【复制】用户 # 如果只是主从复制，仅在 slave 上创建即可； # 如果是MHA 的话，就需要在所有 MySQL 节点上创建。 grant replication slave on *.* to repluser@"%" identified by "654321"; # ② 刷新 flush privileges; |
| # ③ 看看有没有创建成功 select host, user from mysql.user where user = 'repluser'; |
3.2.1.2主库
3.2.1.2.1 配置
1). 查看MySQL配置文件在服务器上的目录：mysql --help | grep "my.cnf"
| 107 |
# ① cluster id，一个集群中各个 MySQL 不能一样 server_id=1 |
3.2.1.2.2 设置
| # ① 重置 master reset master; # ② 查看 master 的 binlog 日志信息（当前最新的 binlog 文件名称、position 等） show master status; |
3.2.1.3从库
3.2.1.3.1 配置
| 134 |
# cluster id server_id=2 |
| 143 |
# cluster id server_id=3 |
3.2.1.3.2 设置主从关系
| # ① 停止 slave 进程 stop slave; |
|
| # ② 在【从服务器】上设置主从关系 change master to master_host = '192.168.73.107', master_user = 'repluser', master_password = '654321', master_log_file = 'mysql-bin.000001', master_log_pos = 154; |
master_host：主库 ip master_user：主库授权用户名 master_password：授权用户密码 master_log_file：主库日志文件 master_log_pos：主库日志偏移量 |
| # ③ 启动 slave 进程 start slave; |
|
| # ④ 查看 slave 状态 show slave status\G; |
|
| 这里只截取了一部分 slave 的状态信息。 注意看： ① Slave_IO_Running（IO 线程状态）：YES（开启）。 ② Slave_SQL_Running（SQL 线程状态）：YES（开启）。 有这两个就说明主从复制搭建成功。 |
|
3.2.2 验证
在107主库的 ta_liquidation_sub_sh 数据库上进行 DDL、DML 操作，对应的 134、143 这两个从库上就会进行同步。
3.3 MySQL主从搭建（半同步复制）
【一主二从，半同步复制】。
注意：该步操作是在【3.1 MySQL主从搭建（异步复制）】的基础上进行。
3.3.1 概念
这里我们再来回顾一下半同步复制。
1).异步复制的问题
在异步复制中，主库不会去验证 bin log 有没有成功复制到从库中。如果主库提交一个事务并写入 bin log 中后，当从库还没有从主库中得到 bin log 时，主库宕机了或者由于其他原因导致该事务的 bin log 丢失了，那么从库就不会得到这个事务，进而就造成了主从数据不一致。
2).半同步复制来解决
半同步复制，当主库每提交一个事务后，不会立即返回，而是等待【其中一个从库】接收到 bin log 并成功写入 relay-log 中才返回客户端。SO，这样就可以保证一个事务至少有两份日志，一份保存在主库的 bin log，另一份保存在其中一个从库的 relay-log 中，从而保证了数据的安全性和一致性。
3).半同步复制的问题
① slave 超时时，就会退化为异步复制
在半同步复制时，如果主库的一个事务提交成功了，在推送到从库的过程中，如果从库宕机了或者网络故障，导致从库并没有接收到这个事务的 bin log。此时，主库会等待一段时间（这个时间由 rpl_sync_master_timeout 的毫秒数决定），如果过了这个时间后还无法推送到从库的话，那么，MySQL就会从半同步复制切换到异步复制，当从库恢复正常连接到主库后，主库又会切换回半同步复制。
SO，半同步复制很大程度上取决于主从库之间的网络情况，往返时延越小决定了从库的实时性越好。
3.3.2 配置半同步复制
3.3.2.1 确认MySQL服务器是否支持动态增加插件
主从库都查看一下。如果不支持那就直接跳过当前章节。
| select @@have_dynamic_loading; |
|
| YES：支持。 |
|
3.3.2.2 在主从库上安装插件
1).查看是否可以安装相应的插件
MySQL的插件一般在安装目录/bin/plugin下，先看一下能不能安装。
1).【主库】的插件是semisync_master.so。
2).【从库】的插件是semisync_slave.so。
3). ① 如果是【主从复制】的话，那么就各自安装；
② 如果是 MHA 的话，那么所有的节点都需要安装【主库和从库】的插件。
2).安装插件
| # ① 先看看是不是已经安装了 select * from mysql.plugin; |
| # ② 安装 semisync_master.so 插件 install plugin rpl_semi_sync_master soname 'semisync_master.so'; |
| # ③ 在所有【从库】上安装 semisync_slave.so 插件 install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; |
【注】：系统重启的时候也会自动加载这些插件，SO，不必担心。
3.3.2.3 打开半同步复制semi-sync
半同步复制，默认情况下设置不打开。
| # ① 在MySQL-master上开启 master 的半同步复制 set global rpl_semi_sync_master_enabled = 1; # 查看 show variables like 'rpl_semi_sync_master_enabled'; |
|
|
| # ② 在MySQL-slave上开启 slave 的半同步复制 set global rpl_semi_sync_slave_enabled = 1; # 查看 show variables like 'rpl_semi_sync_slave_enabled'; |
|
|
3.3.2.4 重启从库的I/O线程
如果之前配置的是异步复制，那么在这里就需要重启一下从库的I/O线程。如果是全新的半同步复制就不需要这一步。
| # 停止 I/O 线程 stop slave io_thread; # 开启 I/O 线程 start slave io_thread; |
|
|
3.3.2.5 在【主库】上查看半同步复制的状态信息
| # 在【主库】上查看半同步复制的状态信息 show status like '%semi_sync%'; |
|
|
|
我们在这里需要关注三个值： ① Rpl_semi_sync_master_status 值为 ON，表示半同步复制目前处于打开状态。 ② Rpl_semi_sync_master_no_tx 这个值表示当前主库有多少个事务不是半同步模式下从库及时响应的。 ③ Rpl_semi_sync_master_yes_tx 这个值表示当前主库上有多少个事务是通过半同步复制到从库的。 综上所知，在日常的维护中，我们可以查看这三个值来判断半同步复制是否正常。 |
3.3.3 半同步复制方式
MySQL的半同步复制方式由参数【rpl_semi_sync_master_wait_point】控制。
支持两种不同的半同步复制方式：AFTER_SYNC（默认值）和AFTER_COMMIT。
1). AFTER_SYNC
复制方式：Master上的 binlog 日志复制到 slave 之后，master 再 commit。
所有在 master 上 commit 的事务都已经复制到了 slave；但是所有已经复制到 slave 的事务在 master 上不一定 commit（比如说，master 将日志复制到 slave 之后，在 commit 之前宕机了）。
2). AFTER_COMMIT
复制方式：Master 先 commit，之后再将 binlog 日志复制到 slave 上。
所有在 master 上 commit 的事务不一定复制到了 slave（比如，master commit 之后，还未来得及将 binlog 日志复制到 slave 就宕机了）；所有已经复制到 slave的事务在 master 上一定 commit 了。
该方式在 master 宕机的情况下，无法保证数据的一致性，所以，一般也不会使用该方式。
3.3.4 验证
验证方式及结果与【3.2.2验证】相同。主从复制完成后，再来在主库上看一下半同步复制的状态信息。
3.4 MHA 搭建
真正的大佬来了。
| MHA Node |
主库 |
107 |
| 从库 |
134、143 |
|
| MHA Manager |
104 |
|
3.4.1 MySQL 配置
在 MySQL 的 slave 上开启 bin log（二进制日志）。因为当发生自动故障迁移时，如果 slave 没有开启 bin log 的话，那么就无法将其提升为 master。开启方式直接参考【3.2.1.1.1主库配置】。
3.4.2 安装 MHA
因为 MHA Manager 与 MHA Node 都是用 Perl 开发的，所以我们需要安装 Perl。所有服务器上都要安装。
1).安装 epel 源
yum install -y epel-release
2).安装 perl 模块
① 安装方式一
yum -y install perl-DBD-MySQL
yum -y install perl-Config-Tiny
yum -y install perl-Log-Dispatch
yum -y install perl-Parallel-ForkManager
yum -y install perl-ExtUtils-CBuilder
yum -y install perl-ExtUtils-MakeMaker
yum -y install perl-CPAN
② 安装方式二
CPAN网站（The Comprehensive Perl Archive Network - www.cpan.org）
MetaCPAN网站（Search the CPAN - metacpan.org）
在这两个网站上搜索。
3.4.2.2 安装MHA软件包
在 GitHub 上下载 tar.gz 包
| GitHub地址 |
版本 |
| https://github.com/yoshinorim/mha4mysql-manager/releases |
0.58 |
| https://github.com/yoshinorim/mha4mysql-node/releases |
0.58 |
3.4.2.2.1 安装 mha4mysql-node
1).安装
所有节点（一个MHA Manager，三个MHA Node[一主两从]）都要安装。
| # I. 解压 tar -xzvf mha4mysql-node-v0.58.tar.gz |
| # II. 编译安装 ① perl Makefile.PL |
|
|
| ② make |
|
|
| ③ make install |
|
|
2).安装过程中遇到的问题
Q1：安装的第一步：perl Makefile.PL，可能会出现下面的错误提示。
A1：解决方案
原因：缺少 Module::Install
【注意】：下面的方法一和方法二都要执行，执行完之后还是不行的话就继续执行直至成功为止。一般情况下需要两三次执行才可成功。
方法一：简单用法：perl -MCPAN -e 'install Module::Install'
一路 yes + enter 就行。
方法二：复杂用法（用capn进行安装）：perl -MCPAN -e shell
① install local::lib ② o conf init：重新配置。 ③ reload cpan：更新。 ④ exit：退出。
3).node组件安装成功后会在目录 /usr/local/bin 下面生成以下几个工具
这些工具通常由 MHA Manager 的脚本触发，无需人为操作。
| 工具（脚本）名称 |
作用 |
| save_binary_logs |
保存和复制 master 的二进制日志 |
| apply_diff_relay_logs |
识别差异的中继日志事件，并将其差异的事件应用于其他的 slave |
| filter_mysqlbinlog |
去除不必要的 rollback 事件（MHA 已不再使用这个工具） |
| purge_relay_logs |
清除中继日志（不会阻塞 SQL 线程） |
|
|
|
3.4.2.2.2 安装 mha4mysql-manager
只在 MHA Manager 的服务器上安装即可，具体安装过程同【3.4.1.2.1安装 mha4mysql-node】，此处不再赘述。
Manager 组件安装成功后会在 /usr/local/bin 下面会生成以下几个工具：
| 工具（脚本）名称 |
作用 |
| masterha_check_repl |
检查 MySQL 复制状况 |
| masterha_check_ssh |
检查 MHA 的 SSH 配置状况 |
| masterha_check_status |
检测当前 MHA 的运行状态 |
| masterha_conf_host |
添加或删除配置的 server 信息 |
| masterha_manager |
启动 MHA |
| masterha_master_monitor |
检测 master 是否宕机 |
| masterha_master_switch |
控制故障转移（自动or手动） |
| masterha_secondary_check |
多种线路检测 master 是否存活（当 MHA Manager 检测到 master 不可用时 ，通过该脚本来进一步确认，降低误切的风险）。 |
| masterha_stop |
停止 MHA |
|
|
|
3.4.3 免密通信
3.4.3.1 免密通信配置
服务器之间免密登录，也叫 SSH 互信配置。所有节点（manager、node）都要执行。
ssh 互信配置的原理：在各自的服务器上存放目标主机的证书，当执行登录时自动完成认证，从而不需要输入任何密码。
1).所有节点都执行生成密钥操作
这里以 MHA Manager 服务器节点为例进行说明，其他的三台（一主二从）机器一样的操作。
| # ① 生成 RSA 公钥 ssh-keygen -t rsa（请一直回车，直至结束） |
| 执行过程：
执行结果：
|
| # ② 将公钥（/root/.ssh/id_rsa.pub）文件追加到认证文件（名为 authorized_key 文件）中。 # id_rsa.pub 文件位置在上一步就可以看出来。 cat id_rsa.pub >> authorized_keys |
|
|
2).在4台机器（一个MHA Manager，三个 MHA Node [即，一主二从]）中，随便挑一台执行如下操作
这里也以 MHA Manager 为例进行操作。
| # I. 在 MHA Manager 上接收 主从3台 MySQL 服务器上的密钥 # ① 将 master 上的认证文件发送到 MHA Manager scp 192.168.73.107:/root/.ssh/authorized_keys ./authorized_keys.107 # ② 将 slave1 上的认证文件发送到 MHA Manager scp 192.168.73.143:/root/.ssh/authorized_keys ./authorized_keys.143 # ③ 将 slave2 上的认证文件发送到 MHA Manager scp 192.168.73.134:/root/.ssh/authorized_keys ./authorized_keys.134 |
|
|
|
|
| # II. 在 MHA Manager 上执行合并密钥的命令 # 也就是把 master、slave1、slave2 上发送过来的三个文件合并到 MHA Manager 的认证文件中。 # ① 把 master 上的认证文件合并到 MHA Manager cat authorized_keys.107 >> authorized_keys # ② 把 slave1 上的认证文件合并到 MHA Manager cat authorized_keys.134 >> authorized_keys # ③ 把 slave2 上的认证文件合并到 MHA Manager cat authorized_keys.143 >> authorized_keys |
|
|
|
合并完成之后，可以看出来 MHA Manager 对应的认证文件【authorized_keys】的文件大小都变大了。 |
| # III. 在 MHA Manager 上将其他服务器节点的认证文件删掉 # 因为这里已经没有用了，就只留 MHA Manager 自身的一个认证文件就行了。 |
|
|
|
|
| # IV. 将 MHA Manager 上合并好的密钥文件发送给其他节点（一主二从3个节点，即 MHA Node） # ① 将 MHA Manager 上的密钥文件发送到 master scp authorized_keys 192.168.73.107:/root/.ssh/ # ② 将 MHA Manager 上的密钥文件发送到 slave1 scp authorized_keys 192.168.73.134:/root/.ssh/ # ③ 将 MHA Manager 上的密钥文件发送到 slave2 scp authorized_keys 192.168.73.143:/root/.ssh/ |
|
| 至此，四台服务器（一个 MHA Manager，三个 MHA Node[一主二从]）上的密钥文件就是一模一样的了。 |
|
3.4.3.2 修改主机名
分别在四台服务器（一个 MHA Manager，三个 MHA Node[一主两从]）上修改 IP 主机名：vim /etc/hosts
| 192.168.73.104 mha-manager 192.168.73.107 mha-node-master 192.168.73.143 mha-node-slave1 192.168.73.134 mha-node-slave2 |
|
|
3.4.3.3 验证
能够互相 ssh 且不要密码就算是成功了。
3.4.4 MHA Manager 配置
在 https://github.com/yoshinorim/mha4mysql-manager.git 下载源码，我们需要用到 /mha4mysql-manager/samples 下的文件。
3.4.4.1 MHA Manager 脚本
我们在【3.4.1.2.2安装 mha4mysql-manager】中可以得知，安装成功后，在/usr/local/bin下有了9个工具（脚本），除此之外，我们还需要自定义3个脚本。当然，这3个脚本可以在 /mha4mysql-manager/samples/script 下找到。
