mysql高可用杂谈

七七powerful

已于 2024-08-04 16:44:56 修改

阅读量851

点赞数 7

文章标签： mysql java jvm

于 2024-08-04 12:17:22 首次发布

本文链接：https://blog.csdn.net/qyq88888/article/details/140903782

版权

MYSQL支持单向、双向、链式级联、异步复制、半同步复制、GTID复制、多源复制、loss-less复制。

主从复制原理：

主服务器有一个工作线程I/O dump thread，从服务器有两个工作线程：一个I/O thread，另一个是SQL thread。

主库把外界接收到SQL请求记录到自己的binlog日志中，从库的I/O thread去请求主库的binlog日志，并将得到的binlog日志写到自己的Relay log（中继）文件中。然后在从库上重做应用中继日志中的SQL语句。主库通过I/O dump thread给从库I/O thread传送binlog日志。

异步复制原理：

异步复制是MYSQL默认的复制方式，其原理很简单，就是在主库写入binlog日志后即可成功返回客户端，无须等待binlog日志传递给从库的过程。

缺点：一旦主库发生宕机，就有可能出现丢失数据的情况。

半同步复制原理：

开启半同步复制功能，主从服务器必须同时安装半同步复制插件，确保从库接收完主库传递过来的binlog内容已经写入到自己的relay log里面，才会通知主库上面的等待线程该操作完毕。

如果等待超时，超过rpl_semi_sync_master_timeout参数设置的时间，则关闭半同步复制，并自动转换为异步复制模式，直到至少有1台从库通知主库已经接收到binlog信息为止。

AFTER_COMMIT

主库将每个事务写入binlog，并传递给从库，刷新到中继日志中，同时主库提交事务。之后主库开始等待从库的反馈，只有收到从库的回复之后，master才将commit OK的结果反馈给客户端。

AFTER_SYNC

mysql5.7默认方式。主库将每个事务写入binlog，并传递给从库，刷新到中继日志中，主库开始等待从库的反馈，接收到从库的回复之后，再提交事务并且返回“commit OK”结果给客户端。

在after_sync模式下，即使主库宕机，所有在主库上已经提交的事务都能保证已经同步到从库的中继日志中，不会丢失任何数据。

GTID复制原理

GTID又叫全局事务ID，是一个已提交事务的编号，并且是一个全局唯一的编号。GTID是由server_uuid和事务id组成的，即GTID=server_uuid:transaction_id。server_uuid是在数据库启动过程中自动生成的，每台机器的server-uuid不一样。uuid存放在数据目录的auto.cnf文件下。而transaction_id就是事务提交时由系统顺序分配的一个不会重复的序列号。

GTID存在的价值

（1）GTID使用master_auto_position=1代替了基于binlog和position号的主从复制的搭建方式，更便于主从复制的搭建。

（2）GTID可以知道事务长最开始是在哪个实例上提交的。

（3）GTID方便实现主从之间的failover，再也不用不断地去找position和binlog了。

GTID搭建需要使用的参数

主库参数配置：

gtid_mode=on

enforce_gtid_consistency=on

log_bin=on

binlog_format=row

从库参数配置：

gtid_mode=on

enforce_gtid_consistency=on

log_slave_updates=1

GTID使用的限制条件

GTID复制是针对事务来说的，一个事务只对应一个GTID。

（1）不能使用create table table_name select * from table_name;

（2）在一个事务中既包含事务表的操作，又包含非事务表。

（3）不支持CREATE TEMPORARY TABLE 或者 DROP TEMPORARY TABLE语句操作。

（4）使用GTID复制从库跳过错误时，不支持执行sql_slave_skip_counter参数的语法。

MYSQL的高可用集群架构

主要包括MHA、Keepalived+双主，PXC以及中间件ProxySQL等知识点。

MHA工作机制

MHA Manger管理节点可以单独部署在一台独立服务器上管理多个master-slave集群，也可以部署在一台slave上。

MHA Manager探测集群中的node节点，当发现master出现故障时，它可以自动将具有最新数据的slave提升为新的master，然后将所有其他slave导向新的master上。整个故障转移过程对应用程序是透明的。MHA node数据节点可以运行在每台MYSQL服务器上，它通过监控具备解析和清理logs功能的脚本来加快故障转移。

MHA工作原理

MHA的目的在于维持MYSQL Replication中master库的高可用性，其最大特点是可以修复多个slave之间的差异日志，最终使所有slave保持数据一致，然后从中选择一个充当新的master，并将其他slave指向它。当master出现故障时，可以通过对比slave之间I/O thread读取主库binlog的position号，选取最接近slave作为备选主库。

MHA优缺点：

优点：

（1）故障切换时，可以自行判断哪个从库与主库的数据最接近，然后切换到上面，可以减少数据的丢失，保证数据一致性。

（2）支持binlog server，可提高binlog的传送效率，进一步减少数据丢失的风险。

（3）结合增强半同步功能，确保故障切换时数据不丢失。

缺点：

（1）自动切换到脚本太简单了，而且比较老化，建议后续逐渐完善。

（2）搭建MHA架构，需要开启linux系统互信协议，所以对于系统安全性来说是个不小的考验。

Keepalived+双主架构

双master配合Keepalived这种MYSQL高可用架构设计也是基于主从复制的原理而搭建的。使用MYSQL双主复制技术+Keepalived是一种简单、便捷的解决方案，在高可用集群环境中，Keepalived使用vip，利用自带的服务监控功能和自定义脚本来实现MYSQL故障时自动切换。

Keepalived利用VRRP（虚拟冗余路由协议）协议可以实现MYSQL高可用集群方案，避免单点故障。两台互为主备的MYSQL服务器运行Keepalived，master会向backup节点发送广播信号，当backup节点接收不到master节点发生的VRRP包时，会认为master宕机，这时会根据VRRP的优先级来选举出一个backup来充当master，则这个master就会持有vip，从而保证了线上现有业务的正常运行，高可用完满地展现出来。

搭建必备条件

（1）两台mysql数据库互为主从模式

（2）keepalived参数中，两台机器的state都要采用backup这种状态，而且都是nopreempt这种非抢占模式，避免出现冲突，发生脑裂现象。

PXC集群介绍

基于Galera协议的MYSQL高可用集群架构。基于Galera的高可用方案主要有MariaDB Galera Cluster和Percona XtraDB cluster，简称PXC，目前PXC架构在生成环境中用的更多而且更成熟一些。是一种多主架构。

PXC原理

PXC最常用以下4个端口号：

3306--数据库对外服务的端口号。

4444--请求SST的端口（SST是指数据库一个备份全量文件的传输）

4567--组成员之间进行沟通的一个端口号

4568--用于传送IST（相对于SST来说的一个增量）

PXC操作流程

首先客户端发起一个事务，该事务先做本地执行，执行完成之后就要发起对事务的提交操作了。在提交之前需要将产生的复制写集广播出去，然后获取到一个全局的事务ID号，一并传送到另一个节点上面。

通过验证合并数据之后，发现没有冲突数据，执行apply_cb和commit_cb动作，否则就需要取消(discard)此次事务的操作。而单前server节点通过验证之后，执行提交操作，并返回OK。如果验证没有通过，则执行回滚。当然在生产中至少要有三个节点的集群环境，如果其中有一个节点没有验证通过，出现了数据冲突，那么此时采取的方式就是将出现不一致的节点踢出集群环境，而且它自己会执行shutdown命令，自动关机。

PXC架构优缺点

优点：

（1）实现MYSQL数据库集群架构的高可用性和数据的强一致性。

（2）完成了真正的多节点读写的集群方案。

（3）改善了传统意义上的主从复制延迟到问题，基本上达到了实时同步。

（4）新加入的节点可以自动部署，无需提供手动备份，维护起来很方便。

（5）由于是多节点写入，所以数据库故障切换很容易。

缺点：

（1）新加入到节点开销大，需要复制完整的数据。采用sst传输开销太大。

（2）任何更新事务都需要全局验证通过，才会在每个节点库上执行。集群性能受限于性能最差的节点，也就是经常说的短板效应。

（3）因为需要保证数据的一致性，所以这多节点并发写时，锁冲突问题比较严重。

（4）存在写扩大问题，所有的节点上都会发生写操作。

（5）只支持InnoDB存储引擎表

（6）没有表级别到锁定，执行DDL语句操作会把整个集群锁住，而且也kill不了。

（7）所有的表必须包含有主键，不然操作数据会报错。

ProxySQL

ProxySQL是MYSQL的一款中间件的产品，是灵活强大的MYSQL代理层，可以实现读写分离，支持query路由功能，支持动态指定某个SQL进行缓存，支持动态加载配置、故障切换和一些SQL的过滤功能。同类产品：DBproxy、MYCAT、OneProxy。

优点：

高可用性：ProxySQL 可以作为数据库代理层，提供高可用性和负载均衡。
灵活性：ProxySQL 允许你动态地调整路由，包括读写分离、负载均衡等。
监控和管理：ProxySQL 提供了丰富的监控和管理工具，可以实时查看数据库的状态。
配置简单：安装和配置 ProxySQL 相对简单，可以快速上手。

缺点：

学习曲线：ProxySQL 有一定的学习曲线，需要理解其配置和管理。
性能损耗：ProxySQL 作为代理层，会有性能损耗，但这可以通过硬件优化来部分减轻。
维护复杂度：ProxySQL 配置复杂，需要有专业知识来维护。

MGR（组复制）介绍

组复制的两种模式

单主模式：组复制具有自动选主功能，每次只有一个server成员接受更新;

多主模式：所有的server成员都可以同时接受更新;

组复制原理

组复制是一种可用于实现容错系统的技术，复制组是一个通过消息传递相互交互的server集群。通信层提供了原子消息和完全有序信息交付等保障机制，实现了基于复制协议的多主更新。

复制组由多个server成员构成，每个server成员可以独立的执行事务，单所有的读写事务只有在冲突检测成功后才会提交，只读事务则不需要冲突检测。因此，当一个读写事务准备提交的时候，会自动转组内进行原子性的广播，告知其它节点变更了什么内容、执行了什么事务。这种原子广播的方式，使得这个事务在每一个节点上都保持着同样顺序。这意味着每一个节点都以同样的顺序，接收到了同样的事务日志，所以每一个节点以同样的顺序重演了这些事务日志，最终整个组内保持了完全一致的状态。

组复制的限制

1). 数据必须放在InnoDB存储引擎, 禁用除InnoDB之外的其他存储引擎, 对应的配置项: disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
2). 每个表必须有主键或者有不允许空值的唯一键；对应的配置项：sql_require_primary_key=ON 或者 REQUIRE_TABLE_PRIMARY_KEY_CHECK=ON.
3). 稳定高新能的网络环境.网络对于组复的稳定性和性能至关重要，组内的每个服务器应该物理部署得尽可能靠近, 从MySQL 8.0.14开始, 支持IPv4/IPv6网络架构, 或者两种混合。
(阿里的Oceanbase提到的两地三中心的部署是否靠谱, 或者只是为了迎合金融行业的监管要求?)
4). 必须开二进制日志，对应配置项：log-bin[=log_file_name]
5). 二进制记录其来自其他成员的跟新, 对应配置项: log-slave-updates = ON
6). 二进制日志必须使用行模式, 对应配置项: binlog-format = ROW
7). 关闭二进制日志的校验(checksum), 对应配置项: binlog-checksum = NONE
8). 开启GTID, 对应配置项: gtid_mode = ON
9). 复制信息必须存放在数据库表内, 对应配置项: master_info_repository=TABLE 和 relay_log_info_repository=TABLE
10). 写集"write set"的哈希值使用XXHASH64算法, 对应配置项: transaction-write-set-extraction = XXHASH64
11). 设置binlog_transaction_dependency_tracking = WRITESET_SESSION可以提高组成员的性能
12). 每个组成员的default-table-encryption值必须一致；
13). 每个组成员的lower-case-table-names值必须一致, 需要设置为1(这是非默认值);
14). 开启多线程应用(Multithreaded Appliers), 对应配置项:
slave_parallel_workers = (线程数, 根据情况配置大于0, 最大1024)
slave_preserve_commit_order = 1 #确保应用事务的顺序与发起服务器一致
slave_parallel_type=LOGICAL_CLOCK

需要配置的my.cnf参数

## Group Replication Requirements
disabled_storage_engines               = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
sql_require_primary_key                = ON
log_slave_updates                      = ON  
log-bin                                = /data/mysql/mgr80/binlog/blog
log-bin-index                          = /data/mysql/mgr80/binlog/blog.index
binlog-format                          = ROW
binlog-checksum                        = NONE
server_id                              = 101  #每个成员必须设置不同
gtid_mode                              = ON
enforce_gtid_consistency               = ON
master_info_repository                 = TABLE 
relay_log_info_repository              = TABLE
transaction-write-set-extraction       = XXHASH64
binlog_transaction_dependency_tracking = WRITESET_SESSION
default-table-encryption               = OFF
lower_case_table_names                 = 1
slave_parallel_workers                 = 4
slave_parallel_type                    = LOGICAL_CLOCK
slave_preserve_commit_order            = ON
transaction-isolation                  = 'READ-COMMITTED'

## Group Replication Settings
plugin_load_add                                    = 'group_replication.so'
group_replication_group_name                       = "65e5f89b-770d-4936-b72f-c7a190d5884a" #可以通过uuidgen生成
group_replication_single_primary_mode              = OFF # OFF=muti-primary mode; ON=single-primary mode;
group_replication_enforce_update_everywhere_checks = ON  # if 'muti-primary mode' -> ON, if 'single-primary mode' -> OFF;
group_replication_start_on_boot                    = off
group_replication_bootstrap_group                  = off
group_replication_local_address                    = "192.168.55.10:33061" # 根据成员实际的IP不而不同
group_replication_group_seeds                      = "192.168.55.10:33061,192.168.55.20:33061,192.168.55.30:33061"
group_replication_ip_whitelist                     = "192.168.55.0/24,192.168.203.0/24" #设置白名单
group_replication_recovery_get_public_key          = ON