企业级Mysql 集群技术部署

目录

1.1部署mysql

1.1.1 安装依赖性：

1.1.2 下载并解压源码包

1.1.3 源码编译安装mysql

1.1.4 部署mysql

2.mysql的主从复制

2.1 配置masters

2.2配置slave

 2.3 延迟复制

2.4 慢查询日志

2.5并行复制

2.6 原理刨析

2. 7架构缺陷

3.半同步模式

3.1半同步模式原理

3.2 gtid模式

3.3.启用半同步模式

3.4.测试

4.mysql高可用之组复制 (MGR)

4.1 组复制流程

4.2 组复制单主和多主模式

4.3.实现mysql组复制

5.mysql-router（mysql路由）

6.mysql-mha

6.1.MHA概述

6.2 MHA部署实施

6.2.1 搭建一主两从架构

6.2.2安装MHA所需要的软件

6.2.3 MHA的故障切换

master未出现故障手动切换

master故障手动切换

恢复故障mysql节点

自动切换

6.2.4为MHA添加VIP功能

1.1部署mysql

1.1.1 安装依赖性：

yum install cmake gcc-c++ openssl-devel ncurses-devel.x86_64 libtirpc-devel-1.3.3-8.el9_4.x86_64.rpm rpcgen.x86_64 -y

1.1.2 下载并解压源码包

1.1.3 源码编译安装mysql

编码时间会很久，所以我没有截图

进入解压后的mysql目录

[root@mysql-node10 mysql-5.7.44]# cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ #指定安装路径
-DMYSQL_DATADIR=/data/mysql \ #指定数据目录
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \ #指定套接字文件
-DWITH_INNOBASE_STORAGE_ENGINE=1 \ #指定启用INNODB存储引擎，默认
用myisam
-DWITH_EXTRA_CHARSETS=all \ #扩展字符集
-DDEFAULT_CHARSET=utf8mb4 \ #指定默认字符集
-DDEFAULT_COLLATION=utf8mb4_unicode_ci \ #指定默认校验字符集
-DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/ #指定c++库依赖
[root@mysql-node10 mysql-5.7.44]# make -j2 #-j2 表示有几个
核心就跑几个进程
[root@mysql-node10 mysql-5.7.44# make install

当cmake出错后如果想重新检测，删除 mysql-5.7.44 中 CMakeCache.txt即可

1.1.4 部署mysql

#生成启动脚本

dnf install initscripts-10.11.6-1.el9.x86_64 -y

#生成数据目录

、

#生成数据目录

改环境变量

#修改配置文件

#数据库初始化建立mysql基本数据

#数据库安全初始化

测试：

2.mysql的主从复制

另开两台虚拟机作为从服务器一台172.25.250.20  一台172.25.250.30

直接从10上复制过来，无需编译

但是之前部署的操作全都要做

2.1 配置masters

#进入数据库配置用户权限

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'lee'; ##生成专门用来做复制的用
户，此用户是用于slave端做认证用
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; ##对这个用户进行授权
mysql> SHOW MASTER STATUS; ##查看master的状态

#查看二进制日志

cd /data/mysql/

2.2配置slave

测试：

主上

在slave中查看数据是否有同步过来

在slave阶段中默认情况下是开启了写功能的，但是建议关闭slave节点的写功能来保证数据一致性 vim /etc/mysql.cnf

[mysqld]

super_read_only=on

 2.3 延迟复制

一般只做一台就够了

node3

从20上把目录复制过来

延迟复制时用来控制sql线程的，和i/o线程无关
这个延迟复制不是i/o线程过段时间来复制，i/o是正常工作的
是日志已经保存在slave端了，那个sql要等多久进行回放
当master端误操作，可以在slave端进行数据备份

对node3进行从服务配置

测试

在master中写入数据后过了延迟时间才能被查询到

slave查看，暂时看不到

等一分钟后

2.4 慢查询日志

慢查询，顾名思义，执行很慢的查询
当执行SQL超过long_query_time参数设定的时间阈值（默认10s）时，就被认为是慢查询，这个
SQL语句就是需要优化的
慢查询被记录在慢查询日志里
慢查询日志默认是不开启的
如果需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。

这里可以看到是OFF

开启慢查询

变成了ON

慢查询日志

测试慢查询

2.5并行复制

node3

打开多线程回放，需要打开gtid

查看slave中的线程信息

默认情况下slave中使用的是sql单线程回放

在master中时多用户读写，如果使用sql单线程回放那么会造成组从延迟严重 开启MySQL的多线程回放可以解决上述问题

在slaves中设定

vim /etc/my.cnf

gtid_mode=ON
enforce-gtid-consistency=ON
slave-parallel-type=LOGICAL_CLOCK #基于组提交，
slave-parallel-workers=16 #开启线程数量
master_info_repository=TABLE #master信息在表中记录，默认记录
在/data/mysql//master.info
relay_log_info_repository=TABLE #回放日志信息在表中记录，默认记录
在/data/mysql/relay-log.info
relay_log_recovery=ON #日志回放恢复功能开启

重启mysql

检测

此时sql线程转化为协调线程，16个worker负责处理sql协调线程发送过来的处理请求

MySQL 组提交（Group commit）是一个性能优化特性，它允许在一个事务日志同步操作中将多个 事务的日志记录一起写入。这样做可以减少磁盘I/O的次数，从而提高数据库的整体性能。

2.6 原理刨析

三个线程
实际上主从同步的原理就是基于 binlog 进行数据同步的。在主从复制过程中，会基于3 个线程来操作，
一个主库线程，两个从库线程。

• 二进制日志转储线程（Binlog dump thread）是一个主库线程。当从库线程连接的时候，主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上加锁，读取完成之后，再将锁释放掉。
• 从库 I/O 线程会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。
• 从库 SQL 线程会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。

复制三步骤

步骤1：Master将写操作记录到二进制日志（binlog）

步骤2：Slave将Master的binary log events拷贝到它的中继日志（relay log）；

步骤3：Slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化 的，而且重启后从接入点开始复制。

具体操作

1.slaves端中设置了master端的ip，用户，日志，和日志的Position，通过这些信息取得master的认证及信息
2.master端在设定好binlog启动后会开启binlog dump的线程
3.master端的binlog dump把二进制的更新发送到slave端的
4.slave端开启两个线程，一个是I/O线程，一个是sql线程，

• i/o线程用于接收master端的二进制日志，此线程会在本地打开relaylog中继日志，并且保存到本地磁盘
• sql线程读取本地relog中继日志进行回

5.什么时候我们需要多个slave？
当读取的而操作远远高与写操作时。我们采用一主多从架构
数据库外层接入负载均衡层并搭配高可用机制
 

2. 7架构缺陷

主从架构采用的是异步机制
master更新完成后直接发送二进制日志到slave，但是slaves是否真正保存了数据master端不会检测
master端直接保存二进制日志到磁盘
当master端到slave端的网络出现问题时或者master端直接挂掉，二进制日志可能根本没有到达slave
master出现问题slave端接管master，这个过程中数据就丢失了
这样的问题出现就无法达到数据的强一致性，零数据丢失

异步存储就是存储和发送日志是分开的，不管日志发没发过去我都要写到磁盘里面

半同步主上写入数据后，产生日志发送给slave，slvae回复一个ack后，master才把数据写入到磁盘中，不管日志回放，所以不是完全同步

当后端的从服务器挂掉以后，在半同步模式下是无法进行数据写入的，这个时候为什么能够进行写入是因为半同步模式已经关闭并转换成了异步模式

3.半同步模式

3.1半同步模式原理

1.用户线程写入完成后master中的dump会把日志推送到slave端
2.slave中的io线程接收后保存到relaylog中继日志
3.保存完成后slave向master端返回ack
4.在未接受到slave的ack时master端时不做提交的，一直处于等待当收到ack后提交到存储引擎
5.在5.6版本中用到的时after_commit模式，after_commit模式时先提交在等待ack返回后输出ok
 

3.2 gtid模式

当为启用gtid时我们要考虑的问题

在master端的写入时多用户读写，在slave端的复制时单线程日志回放，所以slave端一定会延迟与master端
这种延迟在slave端的延迟可能会不一致，当master挂掉后slave接管，一般会挑选一个和master延迟日志最接近的充当新的master
那么为接管master的主机继续充当slave角色并会指向到新的master上，作为其slave
这时候按照之前的配置我们需要知道新的master上的pos的id，但是我们无法确定新的master和slave之间差多少

当激活GITD之后
当master出现问题后，slave2和master的数据最接近，会被作为新的master
slave1指向新的master，但是他不会去检测新的master的pos id，只需要继续读取自己gtid_next即可

设置gtid

#在master端和slave端开启gtid模式

master端

slave端

node3同样这样搞，和node2一样，但是要改id

然后停掉slave端

#开启slave端的gtid

开启gtid的操作两边都要做

3.3.启用半同步模式

在master端配置启用半同步模式

#打开半同步功能，#查看半同步功能状态

在slave端开启半同步功能

node3同样的步骤

3.4.测试

在master端写入数据

模拟故障

#在slave端

node2和3都执行这个命令

#在master端插入数据

#10秒超时

4.mysql高可用之组复制 (MGR)

MySQL Group Replication(简称 MGR )是 MySQL 官方于 2016 年 12 月推出的一个全新的高可用与高扩展的解决方案
组复制是 MySQL 5.7.17 版本出现的新特性，它提供了高可用、高扩展、高可靠的 MySQL 集群服务

MySQL 组复制分单主模式和多主模式，传统的mysql复制技术仅解决了数据同步的问题，
MGR 对属于同一组的服务器自动进行协调。对于要提交的事务，组成员必须就全局事务序列中给定事务的顺序达成一致

提交或回滚事务由每个服务器单独完成，但所有服务器都必须做出相同的决定
如果存在网络分区，导致成员无法达成事先定义的分割策略，则在解决此问题之前系统不会继续进行，
这是一种内置的自动裂脑保护机制

MGR由组通信系统( Group Communication System ，GCS ) 协议支持
该系统提供故障检测机制、组成员服务以及安全且有序的消息传递
 

4.1 组复制流程

首先我们将多个节点共同组成一个复制组，在执行读写（RW）事务的时候，需要通过一致性协议层
（Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应 Node 节点）的同意，大多数指的是同意的节点数量需要大于 （N/2+1），这样才可以进行提交，而不是原发起方一个说了算。而针对只读（RO）事务则不需要经过组内同意，直接 提交 即可

节点数量不能超过9台

4.2 组复制单主和多主模式

single-primary mode(单写或单主模式) 单写模式 group 内只有一台节点可写可读，其他节点只可以读。当主服务器失败时，会自动选择新的主 服务器，

multi-primary mode(多写或多主模式) 组内的所有机器都是 primary 节点，同时可以进行读写操作，并且数据是最终一致的。

4.3.实现mysql组复制

先把mysql全部停掉，为了保持数据强一致性

删掉原本的mysql配置

然后修改配置加参数

server-id=1 #配置server唯一标识号
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" #禁用指定存储引擎
gtid_mode=ON    #启用全局事件标识
enforce_gtid_consistency=ON #强制gtid一致
master_info_repository=TABLE #复制事件数据到表中而不记录在数据目录中
relay_log_info_repository=TABLE
binlog_checksum=NONE #禁止对二进制日志校验
log_slave_updates=ON #打开数据库中继，
 #当slave中sql线程读取日志后也会写入到自己的binlog中
log_bin=binlog #重新指定log名称 
binlog_format=ROW #使用行日志格式 
plugin_load_add='group_replication.so' #加载组复制插件
transaction_write_set_extraction=XXHASH64 #把每个事件编码为加密散列
group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" #通知插件正
式加入
 #或创建的组名
 #名称为
uuid格式

group_replication_start_on_boot=off #在server启动时不自动启动组复
制
group_replication_local_address="172.25.254.10:33061" #指定插件接受其他成员的信息端
口

group_replication_group_seeds="172.25.254.10:33061,172.25.254.20:33061，
172.25.254.30:33061"   #本地地址允许访问成员列表

group_replication_ip_whitelist="172.25.254.0/24,127.0.0.1/8" #主机白名单
#不随系统自启而启动，只在初始成员主机中手动开启，
#需要在两种情况下做设定：1.初始化建组时 2.关闭并重新启动整个组时

group_replication_bootstrap_group=off 
group_replication_single_primary_mode=OFF #使用多主模式

group_replication_enforce_update_everywhere_checks=ON #组同步中有任何改变
检测更新

group_replication_allow_local_disjoint_gtids_join=1 #放弃自己信息以
master事件为主
 

然后在初始化

如果初始化失败就删掉/data/mysql/*文件

启动mysql

如果启动失败 

就过滤出mysql的进程，把里面的进程都杀掉然后再删掉文件重新初始化就能重启了

初始化后登录进去更改密码

这个密码是初始化后最后一行给的密码

关掉日志功能再创建用户

要在三台主机上进行本地解析

编写本地解析会让组连接的更快

10配置好后复制给20，30

#在复制配置文件到myql-node20和mysql-node30

修改server-id和groupreplicationlocal_address

然后初始化

删掉原本的mysql数据

杀死进程

启动mysql

登陆进去msyql，初始化mysql密码

mysql配置

20和30都要做是一样的

测试：

在每个节点都可以完成读写

#在mysql-node10中

#在mysql-node20中

#mysql—node30中

5.mysql-router（mysql路由）

MySQL Router 是一个对应用程序透明的InnoDB Cluster连接路由服务，提供负载均衡、应用连接故障转移和客户端路 由。

利用路由器的连接路由特性，用户可以编写应用程序来连接到路由器，并令路由器使用相应的路由策略 来处理连接，使其连接到正确的MySQL数据库服务器

Mysql route的部署方式

为了图方便

我们直接在node1上做这个路由

安装mysql-router

停止mysql服务

vim /etc/mysqlrouter/mysqlrouter.conf

重启服务

查看7001端口发现开了

注意此时node1上是没有mysql服务的，这里的7001和7002是之前写的router

在20和30创建远程用户登录（之前进行高可用时已经做过了，这里直接使用就可）

如果没有就去20-30创建用户

#建立测试用户

mysql> CREATE USER lee@'%' IDENTIFIED BY 'lee';

mysql> GRANT ALL ON lee.* TO lee@'%';

测试

这里发现可以登录10上

查看id

发现已经登录到20上了

在登陆一次发现到了30上

mysql router 并不能限制数据库的读写，访问分流 

6.mysql-mha

6.1.MHA概述

为什么要用MHA？

Master的单点故障问题

什么是 MHA？

• MHA（Master High Availability）是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。
• MHA 的出现就是解决MySQL 单点的问题。
• MySQL故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作。
• MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用。

MHA 的组成

• MHA由两部分组成:MHAManager (管理节点) MHA Node (数据库节点),
• MHA Manager 可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台 
• slave 节点上。
• MHA Manager 会定时探测集群中的 master 节点。
• 当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master， 然后将所有其他的 
• slave 重新指向新的 master。

MHA 的特点

• 自动故障切换过程中，MHA从宕机的主服务器上保存二进制日志，最大程度的保证数据不丢失
• 使用半同步复制，可以大大降低数据丢失的风险，如果只有一个slave已经收到了最新的二进制日
• 志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数
• 据一致性
• 目前MHA支持一主多从架构，最少三台服务，即一主两从

故障切换备选主库的算法

1．一般判断从库的是从（position/GTID）判断优劣，数据有差异，最接近于master的slave，成为备选
主。
2．数据一致的情况下，按照配置文件顺序，选择备选主库。
3．设定有权重（candidate_master=1），按照权重强制指定备选主。
（1）默认情况下如果一个slave落后master 100M的relay logs的话，即使有权重，也会失效。
（2）如果check_repl_delay=0的话，即使落后很多日志，也强制选择其为备选主。

MHA工作原理

• 目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群必须最少有3台数据库服务器，
• 一主二从，即一台充当Master，台充当备用Master，另一台充当从库。
• MHA Node 运行在每台 MySQL 服务器上
• MHAManager 会定时探测集群中的master 节点
• 当master 出现故障时，它可以自动将最新数据的slave 提升为新的master
• 然后将所有其他的slave 重新指向新的master，VIP自动漂移到新的master。
• 整个故障转移过程对应用程序完全透明。

6.2 MHA部署实施

6.2.1 搭建一主两从架构

开一台rh7虚拟机作为mha

和之前的主从复制模式是架构是一样的，都是把10作为20和30的主服务器

删除掉mysql文件然后进行初始化

两个slvae中，前面步骤一样，

6.2.2安装MHA所需要的软件

然后mha中

放入软件包

mha与其他主机进行免密认证

先做解析不然很慢

yum install *.rpm -y

在10&20&20

把复制过来的文件下载

yum install /mnt/mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y

在10上创建远程用户登录

测试能否远程登录

mha

#生成配置文件

#编辑配置文件

vim /etc/masterha/app1.conf

检测能否免密登录正常

vim /etc/ssh/sshd_config

systemctl restart sshd

这个操作三台主机都做

检测能否免密登录正常

检测数据主从复制情况

masterhacheckrepl --conf=/etc/masterha/app1.cnf

6.2.3 MHA的故障切换

MHA的故障切换过程
共包括以下的步骤：
1.配置文件检查阶段，这个阶段会检查整个集群配置文件配置
2.宕机的master处理，这个阶段包括虚拟ip摘除操作，主机关机操作
3.复制dead master和最新slave相差的relay log，并保存到MHA Manger具体的目录下
4.识别含有最新更新的slave
5.应用从master保存的二进制日志事件（binlog events）
6.提升一个slave为新的master进行复制
7.使其他的slave连接新的master进行复制

master未出现故障手动切换

刚才两个检测都成功后需要在mha的配置文件里面加上远程用户登录

这个用户和密码是刚才创建的远程用户登录时的密码

也就是这个repl

#在master数据节点还在正常工作情况下

masterha_master_switch --conf=/etc/masterha/app1.conf --master_state=alive --new_master_host=172.25.250.20 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=100

出现如图就可以

检测：

master故障手动切换

先把主服务器切回10

#模拟master故障

停止10上的mysql

然后手动切换

 masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.conf --dead_master_host=172.25.250.10 --dead_master_port=3306 --new_master_host=172.25.250.20 --new_master_port=3306 --ignore_last_failover

输入两个yes后

出现成功切换到20上

恢复故障mysql节点

在20上修改主服务器

如果发现io读写是no

就重启一下slave

30同样如此

然后检测一主两从是否正常

自动切换

#删掉切换锁文件

#监控程序通过指定配置文件监控master状态，当master出问题后自动切换并退出避免重复做故障切换

执行完这个命令后会暂时卡住

我们停掉10的mysql

另开一个标签查看该目录下会出现一个日志

查看该日志

发现进行了自动切换

我们查看30发现确实切换到了20上

恢复故障节点

和之前的步骤一样

CHANGE MASTER TO MASTER_HOST='172.25.250.10',MASTER_USER='repl',MASTER_PASSWORD='lee', MASTER_AUTO_POSITION=1；

如果发现写入出现问题就重启一下就ok

清除锁文件

6.2.4为MHA添加VIP功能

上传脚本

修改脚本

vim /usr/local/bin/masteripfailover

vim /usr/local/bin/masteriponline_change

 vim app1.conf

10添加vip

监控

在50上查看日志

tail -f manager.log

然后切到10上把mysql停止查看50上的日志

有没有出现completed successfully.

然后我们切换到20上查看vip

这样就ok了