目录
3.2.Node工具包 （通常由masterHA主机直接调用，无需人为执行）
四.mysql高可用之组复制 (MGR)
MySQL Group Replication(简称 MGR )是 MySQL 官方于 2016 年 12 月推出的一个全新的高可用与高扩 展的解决方案 组复制是 MySQL 5.7.17 版本出现的新特性，它提供了高可用、高扩展、高可靠的 MySQL 集群服务 MySQL 组复制分单主模式和多主模式，传统的mysql复制技术仅解决了数据同步的问题， MGR 对属于同一组的服务器自动进行协调。对于要提交的事务，组成员必须就全局事务序列中给定事务 的顺序达成一致 提交或回滚事务由每个服务器单独完成，但所有服务器都必须做出相同的决定 如果存在网络分区，导致成员无法达成事先定义的分割策略，则在解决此问题之前系统不会继续进行， 这是一种内置的自动裂脑保护机制 MGR由组通信系统( Group Communication System ，GCS ) 协议支持 该系统提供故障检测机制、组成员服务以及安全且有序的消息传递
1.组复制流程
首先我们将多个节点共同组成一个复制组，在执行读写（RW）事务的时候，需要通过一致性协议层 （Consensus 层）的同意，也就是读写事务想要进行提交，必须要经过组里“大多数人”（对应 Node 节 点）的同意，大多数指的是同意的节点数量需要大于 （N/2+1），这样才可以进行提交，而不是原发起 方一个说了算。而针对只读（RO）事务则不需要经过组内同意，直接 提交 即可。节点数量不能超过9台。
2.组复制单主和多主模式
single-primary mode(单写或单主模式) 单写模式 group 内只有一台节点可写可读，其他节点只可以读。当主服务器失败时，会自动选择新的主 服务器，
multi-primary mode(多写或多主模式) 组内的所有机器都是 primary 节点，同时可以进行读写操作，并且数据是最终一致的。
3.实现mysql组复制
帮助文档：https://dev.mysql.com/doc/refman/
#编辑主配置文件，高可用。
#在mysql-node1中
[root@node1 ~]# /etc/init.d/mysqld stop
[root@node1 ~]# ps aux | grep mysql
[root@node1 ~]# kill -9 641 #做之前需要停止mysql，不行就杀掉
[root@node1 ~]# rm -fr /data/mysql/*
[root@node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=10 #配置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" #主机白名单
group_replication_bootstrap_group=off #不随系统自启而启动，只在初始成员主机中手动开启，#需要在两种情况下做设定：1.初始化建组时 2.关闭并重新启动整个组时
group_replication_single_primary_mode=OFF #使用多主模式
group_replication_enforce_update_everywhere_checks=ON #组同步中有任何改变检测更新
group_replication_allow_local_disjoint_gtids_join=1 #放弃自己信息以master事件为主
[root@mysql-node10 ~]# mysqld --user=mysql --initialize
[root@mysql-node10 ~]# /etc/init.d/mysqld start
#配置sql
[root@mysql-node10 ~]# mysql -p初始化后生成的密码
mysql> alter user root@localhost identified by '123';
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY '123';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='123' FOR CHANNEL 'group_replication_recovery';
mysql> SET GLOBAL group_replication_bootstrap_group=ON; #用以指定初始成员，只在第一台主机中执行
mysql> START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| group_replication_applier | 1e8f2864-61e6-11ef-81b8-000c29253f97 | node1.timinglee.org | 3306 | ONLINE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
1 row in set (0.00 sec)
[root@node1 ~]# vim /etc/hosts
....
172.25.254.10 node1.timinglee.org
172.25.254.20 node2.timinglee.org
172.25.254.30 node3.timinglee.org #末尾添加域名，master端和slave端全都需要配置。
#在复制配置文件到node2和node3
[root@node1 ~]# scp /etc/my.cnf root@172.25.254.20:/etc/my.cnf
The authenticity of host '172.25.254.20 (172.25.254.20)' can't be established.
ECDSA key fingerprint is SHA256:xNL4iQezrb+rZHrAapKQrkzgy92t0wee8Zarzeai/RA.
ECDSA key fingerprint is MD5:cf:92:7e:8a:c1:94:00:74:e4:ca:1b:c4:f4:10:5a:cc.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.25.254.20' (ECDSA) to the list of known hosts.
root@172.25.254.20's password:
my.cnf 100% 914 280.4KB/s 00:00
[root@node1 ~]# scp /etc/my.cnf root@172.25.254.30:/etc/my.cnf
root@172.25.254.30's password:
my.cnf 100% 914 757.7KB/s 00:00
#修改node2和node3中的配置
[root@node2 & 3 ~]# rm -fr /data/mysql/*
[root@node2 & 3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
server-id=20 #在30上写3
....
group_replication_local_address="172.25.254.20:33061" #在30上要写30
....
[root@node2 & 3 ~]# mysqld --user=mysql --initialize
[root@node2 & 3 ~]# /etc/init.d/mysqld start
#配置sql
[root@node2 & 3 ~]# mysql -p初始化后生成的密码
mysql> alter user root@localhost identified by '123';
mysql> SET SQL_LOG_BIN=0;
mysql> CREATE USER rpl_user@'%' IDENTIFIED BY '123';
mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
mysql> FLUSH PRIVILEGES;
mysql> SET SQL_LOG_BIN=1;
mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='123' FOR CHANNEL
'group_replication_recovery';
mysql> START GROUP_REPLICATION;
mysql> SELECT * FROM performance_schema.replication_group_members; #node2端
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| group_replication_applier | 1e8f2864-61e6-11ef-81b8-000c29253f97 | node1.timinglee.org | 3306 | ONLINE |
| group_replication_applier | 97fb3ced-61e7-11ef-b8a4-000c29b11dff | node3.timinglee.org | 3306 | RECOVERING |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM performance_schema.replication_group_members; #node3端
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
| group_replication_applier | 1e8f2864-61e6-11ef-81b8-000c29253f97 | node1.timinglee.org | 3306 | ONLINE |
| group_replication_applier | 87469f8f-61e7-11ef-983e-000c29471fe3 | node2.timinglee.org | 3306 | RECOVERING |
| group_replication_applier | 97fb3ced-61e7-11ef-b8a4-000c29b11dff | node3.timinglee.org | 3306 | ONLINE |
+---------------------------+--------------------------------------+---------------------+-------------+--------------+
3 rows in set (0.00 sec)
测试：
#在每个节点都可以完成读写
#在node1中
[root@node1 ~]# mysql -p123
mysql> CREATE DATABASE 凹头曼;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE TABLE 凹头曼.userlist(
-> username VARCHAR(10) PRIMARY KEY NOT NULL,
-> password VARCHAR(50) NOT NULL
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO 凹头曼.userlist VALUES ('赛罗','111');
Query OK, 1 row affected (0.11 sec)
mysql> INSERT INTO 凹头曼.userlist VALUES ('迪迦','NB');
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO 凹头曼.userlist VALUES ('泰罗','6666');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO 凹头曼.userlist VALUES ('泰迦','555');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM 凹头曼.userlist;
+----------+----------+
| username | password |
+----------+----------+
| 泰罗 | 6666 |
| 泰迦 | 555 |
| 赛罗 | 111 |
| 迪迦 | NB |
+----------+----------+
4 rows in set (0.01 sec)
#在node2中
[root@node2 ~]# mysql -p
mysql> INSERT INTO 凹头曼.userlist VALUES ('亚克','1232');
mysql> SELECT * FROM 凹头曼.userlist;
+--------------+----------+
| username | password |
+--------------+----------+
| 亚克 | 1232 |
| 泰罗 | 6666 |
| 泰迦 | 555 |
| 罗布 | 333 |
| 赛罗 | 111 |
| 迪迦 | NB |
| 闪耀赛罗 | 9999 |
| 闪耀迪迦 | 8888 |
+--------------+----------+
8 rows in set (0.00 sec)
#mysql—node30中
[root@node3 ~]# mysql -p
mysql> INSERT INTO 凹头曼.userlist VALUES ('闪耀赛罗','9999');
mysql> select * from 凹头曼.userlist;
+--------------+----------+
| username | password |
+--------------+----------+
| 泰罗 | 6666 |
| 泰迦 | 555 |
| 赛罗 | 111 |
| 迪迦 | NB |
| 闪耀赛罗 | 9999 |
+--------------+----------+
5 rows in set (0.00 sec)
#注意：如果出现报错，可以先检查自己的主配置文件有没有出问题，其次看看防火墙关了没有，最后检查一下自己的本地解析写了没，还有记得关闭STOP GROUP_REPLICATION;再重启START GROUP_REPLICATION。五.mysql-router（mysql路由）
MySQL Router是一个对应用程序透明的InnoDB Cluster连接路由服务，提供负载均衡、应用连接故障转移和客户端路由。利用路由器的连接路由特性，用户可以编写应用程序来连接到路由器，并令路由器使用相应的路由策略来处理连接，使其连接到正确的MySQL数据库服务器。
1.Mysql route的部署方式
我们需要在所有的数据库主机之外再开一台主机mysql-router
#安装mysql-router
[root@node1 ~]# rpm -ivh mysql-router-community-8.4.0-1.el7.x86_64.rpm
#配置mysql-router
[root@node1 ~]# vim /etc/mysqlrouter/mysqlrouter.conf
[routing:ro]
bind_address = 0.0.0.0
bind_port = 7001
destinations = 172.25.254.10:3306,172.25.254.20:3306,172.25.254.30:3306
routing_strategy = round-robin
[routing:rw]
bind_address = 0.0.0.0
bind_port = 7002
destinations = 172.25.254.30:3306,172.25.254.20:3306,172.25.254.10:3306
routing_strategy = first-available
[root@node1 ~]# systemctl start mysqlrouter.service
[root@node1 ~]# netstat -antlupe | grep 7001 #不需要启动自身的mysql
[root@node1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
[root@node1 ~]# netstat -antlupe | grep mysql
tcp 0 0 0.0.0.0:7001 0.0.0.0:* LISTEN 998 58005 4563/mysqlrouter
tcp 0 0 0.0.0.0:7002 0.0.0.0:* LISTEN 998 58006 4563/mysqlrouter #kill -9 <uuid>杀掉进程
测试：
#建立测试用户
#slaves
[root@node2 & 3 ~]# mysql -p123
mysql> CREATE USER lee@'%' IDENTIFIED BY '123';
mysql> GRANT ALL ON lee.* TO lee@'%';
#查看调度效果
[root@node1 & 2 & 3 ~]# watch -n 1 lsof -i :3306
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
mysqld 37337 mysql 31u IPv6 88936 0t0 TCP *:mysql (LISTEN)
[root@node1 ~]# mysql -ulee -p123 -h 172.25.254.10 -P 7001
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.7.44 Source distribution
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 20 |
+-------------+
1 row in set (0.00 sec)六.mysql高可用之MHA
1.MHA架构图
2.为什么要用MHA？
Master的单点故障问题
3.MHA 的组成
MHA由两部分组成:MHAManager (管理节点) MHA Node (数据库节点), MHA Manager 可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave 节点上。 MHA Manager 会定时探测集群中的 master 节点。 当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master， 然后将所有其他的slave 重新指向新的 master。
4.什么是 MHA？
MHA（Master High Availability）是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。 MHA 的出现就是解决MySQL 单点的问题。 MySQL故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作。 MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用。
5.MHA 的特点
自动故障切换过程中，MHA从宕机的主服务器上保存二进制日志，最大程度的保证数据不丢失 使用半同步复制，可以大大降低数据丢失的风险，如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性 目前MHA支持一主多从架构，最少三台服务，即一主两从
6.故障切换备选主库的算法
1．一般判断从库的是从（position/GTID）判断优劣，数据有差异，最接近于master的slave，成为备选 主。 2．数据一致的情况下，按照配置文件顺序，选择备选主库。 3．设定有权重（candidate_master=1），按照权重强制指定备选主。 （1）默认情况下如果一个slave落后master 100M的relay logs的话，即使有权重，也会失效。 （2）如果check_repl_delay=0的话，即使落后很多日志，也强制选择其为备选主。
7.MHA工作原理
-
目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群必须最少有3台数据库服务器，一主二从，即一台充当Master，台充当备用Master，另一台充当从库。
-
MHA Node 运行在每台 MySQL 服务器上
-
MHAManager 会定时探测集群中的master 节点
-
当master 出现故障时，它可以自动将最新数据的slave 提升为新的master
-
然后将所有其他的slave 重新指向新的master，VIP自动漂移到新的master。
-
整个故障转移过程对应用程序完全透明。
七.MHA部署实施
1.搭建主两从架构
#添加一个主机node4：172.25.254.40
[root@node1 ~]# rsync -al /usr/local/mysql root@172.25.254.40:/usr/local
root@172.25.254.40's password:
[root@node4 ~]# yum install rsync -y #双方主机都需要有rsync才行。
[root@node4 ~]# useradd -s /sbin/nologin -M mysql
[root@node4 ~]# mkdir -p /data/mysql
[root@node4 ~]# chown mysql.mysql /data/mysql/
[root@node4 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@node4 ~]# vim ~/.bash_profile
[root@node4 ~]# source ~/.bash_profile
[root@node4 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=10
log-bin=mysql-bin
gtid_mode=ON
log_slave_updates=ON
enforce-gtid-consistency=ON
symbolic-links=0
-----------------------------------------------------------------------.
#环境准备，全部机子都需要做免密认证。
[root@node1 ~]# vim /etc/hosts
....
172.25.254.10 node1.timinglee.org
172.25.254.20 node2.timinglee.org
172.25.254.30 node3.timinglee.org
172.25.254.40 node4.timinglee.org #末尾添加域名，master端和slave端全都需要配置。
ssh-keygen
ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.10
ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.20
ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.30
vim /etc/ssh/sshd_config
...
UseDNS no
...
systemctl restart sshd #4个机子都需要安装。
scp /root/.ssh/id_rsa root@172.25.254.10://root/.ssh/
scp /root/.ssh/id_rsa root@172.25.254.20://root/.ssh/
scp /root/.ssh/id_rsa root@172.25.254.30://root/.ssh/
#在master节点中
[root@node1 ~]# /etc/init.d/mysqld stop
[root@node1 ~]# rm -fr /data/mysql/*
[root@node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=10
log-bin=mysql-bin
gtid_mode=ON
log_slave_updates=ON
enforce-gtid-consistency=ON
symbolic-links=0
[root@node1 ~]# mysqld --user mysql --initialize
[root@node1 ~]# /etc/init.d/mysqld start
[root@node1 ~]# mysql_secure_installation #输入初始密码，改新密码
[root@node1 ~]# mysql -p123
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123';
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.00 sec)
mysql> SHOW STATUS LIKE 'rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
mysql> CREATE USER root@'%' IDENTIFIED BY '123';
mysql> GRANT ALL ON *.* TO root@'%'; #允许mysql远程登录。
#在slave1和slave2中
[root@node2 & 3 ~]# /etc/init.d/mysqld stop
[root@node2 & 3 ~]# rm -fr /data/mysql/*
[root@node2 & 3 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
server-id=20 #node3记得改成30
log-bin=mysql-bin
gtid_mode=ON
log_slave_updates=ON
enforce-gtid-consistency=ON
symbolic-links=0
[root@node2 & 3 ~]# mysqld --user mysql --initialize
[root@node2 & 3 ~]# /etc/init.d/mysqld start
[root@node2 & 3 ~]# mysql_secure_installation #n;n;y;y;y;y.
[root@node2 & 3 ~]# mysql -p123
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',
MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1;
mysql> start slave;
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON |
+----------------------------+-------+
1 row in set (0.00 sec)2.安装MHA所需要的软件
#在MHA中将需要的软件包拖入。
[root@node4 ~]# unzip MHA-7.zip
[root@node4 ~]# cd MHA-7/
[root@node4 MHA-7]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm perl-Mail-Sender-0.8.23-
1.el7.noarch.rpm
mha4mysql-manager-0.58.tar.gz perl-Mail-Sendmail-0.79-
21.el7.noarch.rpm
mha4mysql-node-0.58-0.el7.centos.noarch.rpm perl-MIME-Lite-3.030-
1.el7.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm perl-MIME-Types-1.38-
2.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm perl-Net-Telnet-3.03-
19.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm perl-Parallel-ForkManager-1.18-
2.el7.noarch.rpm
[root@node4 MHA-7]# yum install *.rpm -y
[root@node4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.10:/mnt
mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35KB 3.1MB/s 00:00
[root@node4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.20:/mnt
mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35KB 3.7MB/s 00:00
[root@node4 MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.30:/mnt
mha4mysql-node-0.58-0.el7.centos.noarch.rpm 100% 35KB 17.8MB/s 00:00
#在sql-node中
[root@node1 ~]# yum install /mnt/mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
[root@node2 ~]# yum install /mnt/mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y
[root@node3 ~]# yum install /mnt/mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y3.在软件中包含的工具包介绍
3.1.Manager工具包主要包括以下几个工具：
masterha_check_ssh #检查MHA的SSH配置状况 masterha_check_repl #检查MySQL复制状况 masterha_manger #启动MHA masterha_check_status #检测当前MHA运行状态 masterha_master_monitor #检测master是否宕机 masterha_master_switch #控制故障转移（自动或者手动） masterha_conf_host #添加或删除配置的server信息
3.2.Node工具包 （通常由masterHA主机直接调用，无需人为执行）
save_binary_logs #保存和复制master的二进制日志 apply_diff_relay_logs #识别差异的中继日志事件并将其差异的事件应用于其他的slave filter_mysqlbinlog #去除不必要的ROLLBACK事件（MHA已不再使用这个工具） purge_relay_logs #清除中继日志（不会阻塞SQL线程）
4.配置MHA 的管理环境
#生成配置目录和配置文件
[root@node4 ~]# masterha_manager --help
Usage:
masterha_manager --global_conf=/etc/masterha_default.cnf #全局配置文件，记录
公共设定
--conf=/usr/local/masterha/conf/app1.cnf #不同管理配置文件，记录各自配置
See online reference
(http://code.google.com/p/mysql-master-ha/wiki/masterha_manager) for
details.
因为我们当前只有一套主从，所以我们只需要写一个配置文件即可
rpm包中没有为我们准备配置文件的模板
可以解压源码包后在samples中找到配置文件的模板文件
#生成配置文件
[root@node4 ~]# mkdir /etc/masterha
[root@node4 MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz
[root@node4 MHA-7]# cd mha4mysql-manager-0.58/samples/conf/
[root@node4 conf]# cat masterha_default.cnf app1.cnf > /etc/masterha/app1.cnf
#编辑配置文件
[root@node4 ~]# vim /etc/masterha/app1.cnf
#全局配置
[server default]
user=root #mysql管理员用户，因为需要做自动化配置
password=123 #mysql密码
ssh_user=root #ssh远程登陆用户
repl_user=repl #mysql主从复制中负责认证的用户
repl_password=123 #mysql主从复制中负责认证的用户密码
master_binlog_dir= /data/mysql #二进制日志目录
remote_workdir=/tmp #远程工作目录 #此参数使为了提供冗余检测，方式是mha主机网络自身的问题无法连接数据库节点，应为集群之外的主机
secondary_check_script= masterha_secondary_check -s 172.25.254.10 -s 172.25.254.11
ping_interval=3 #每隔3秒检测一次
#发生故障后调用的脚本，用来迁移vip
# master_ip_failover_script= /script/masterha/master_ip_failover
#电源管理脚本
# shutdown_script= /script/masterha/power_manager
#当发生故障后用此脚本发邮件或者告警通知
# report_script= /script/masterha/send_report
#在线切换时调用的vip迁移脚本，手动
#master_ip_online_change_script= /script/masterha/master_ip_online_change
[server default]
manager_workdir=/etc/masterha #mha工作目录
manager_log=/etc/masterha/manager.log #mha日志
[server1]
hostname=172.25.254.10
candidate_master=1 #可能作为master的主机
check_repl_delay=0 ##默认情况下如果一个slave落后master 100M的relay logs的话
#MHA将不会选择该slave作为一个新的master
#因为对于这个slave的恢复需要花费很长时间
#通过设置check_repl_delay=0
#MHA触发切换在选择一个新的master的时候将会忽略复制延时
#这个参数对于设置了candidate_master=1的主机非常有用
#因为这个候选主在切换的过程中一定是新的master
[server2]
hostname=172.25.254.20
candidate_master=1 #可能作为master的主机
check_repl_delay=0
[server3]
hostname=172.25.254.30
no_master=1 #不会作为master的主机5.检测配置
#检测网络及ssh免密
[root@node2 ~]# ip a a 172.25.254.11/24 dev eth0
[root@node2 ~]# ip a
[root@node4 conf]# masterha_check_ssh --conf=/etc/masterha/app1.cnf
Sat Aug 24 23:26:15 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Aug 24 23:26:15 2024 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Sat Aug 24 23:26:15 2024 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Sat Aug 24 23:26:15 2024 - [info] Starting SSH connection tests..
Sat Aug 24 23:26:16 2024 - [debug]
Sat Aug 24 23:26:15 2024 - [debug] Connecting via SSH from root@172.25.254.10(172.25.254.10:22) to root@172.25.254.20(172.25.254.20:22)..
Sat Aug 24 23:26:15 2024 - [debug] ok.
Sat Aug 24 23:26:15 2024 - [debug] Connecting via SSH from root@172.25.254.10(172.25.254.10:22) to root@172.25.254.30(172.25.254.30:22)..
Sat Aug 24 23:26:16 2024 - [debug] ok.
Sat Aug 24 23:26:16 2024 - [debug]
Sat Aug 24 23:26:15 2024 - [debug] Connecting via SSH from root@172.25.254.20(172.25.254.20:22) to root@172.25.254.10(172.25.254.10:22)..
Warning: Permanently added '172.25.254.10' (ECDSA) to the list of known hosts.
Sat Aug 24 23:26:16 2024 - [debug] ok.
Sat Aug 24 23:26:16 2024 - [debug] Connecting via SSH from root@172.25.254.20(172.25.254.20:22) to root@172.25.254.30(172.25.254.30:22)..
Sat Aug 24 23:26:16 2024 - [debug] ok.
Sat Aug 24 23:26:17 2024 - [debug]
Sat Aug 24 23:26:16 2024 - [debug] Connecting via SSH from root@172.25.254.30(172.25.254.30:22) to root@172.25.254.10(172.25.254.10:22)..
Warning: Permanently added '172.25.254.10' (ECDSA) to the list of known hosts.
Sat Aug 24 23:26:16 2024 - [debug] ok.
Sat Aug 24 23:26:16 2024 - [debug] Connecting via SSH from root@172.25.254.30(172.25.254.30:22) to root@172.25.254.20(172.25.254.20:22)..
Warning: Permanently added '172.25.254.20' (ECDSA) to the list of known hosts.
Sat Aug 24 23:26:17 2024 - [debug] ok.
Sat Aug 24 23:26:17 2024 - [info] All SSH connection tests passed successfully.
----------------------------------------------------------
#检测数据主从复制情况
#执行检测
[root@node4 conf]# masterha_check_repl --conf=/etc/masterha/app1.cnf
Sat Aug 24 23:26:48 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Aug 24 23:26:48 2024 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Sat Aug 24 23:26:48 2024 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Sat Aug 24 23:26:48 2024 - [info] MHA::MasterMonitor version 0.58.
Sat Aug 24 23:26:49 2024 - [info] GTID failover mode = 1
Sat Aug 24 23:26:49 2024 - [info] Dead Servers:
Sat Aug 24 23:26:49 2024 - [info] Alive Servers:
Sat Aug 24 23:26:49 2024 - [info] 172.25.254.10(172.25.254.10:3306)
Sat Aug 24 23:26:49 2024 - [info] 172.25.254.20(172.25.254.20:3306)
Sat Aug 24 23:26:49 2024 - [info] 172.25.254.30(172.25.254.30:3306)
Sat Aug 24 23:26:49 2024 - [info] Alive Slaves:
Sat Aug 24 23:26:49 2024 - [info] 172.25.254.20(172.25.254.20:3306) Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Sat Aug 24 23:26:49 2024 - [info] GTID ON
Sat Aug 24 23:26:49 2024 - [info] Replicating from 172.25.254.10(172.25.254.10:3306)
Sat Aug 24 23:26:49 2024 - [info] Primary candidate for the new Master (candidate_master is set)
Sat Aug 24 23:26:49 2024 - [info] 172.25.254.30(172.25.254.30:3306) Version=5.7.44-log (oldest major version between slaves) log-bin:enabled
Sat Aug 24 23:26:49 2024 - [info] GTID ON
Sat Aug 24 23:26:49 2024 - [info] Replicating from 172.25.254.10(172.25.254.10:3306)
Sat Aug 24 23:26:49 2024 - [info] Not candidate for the new Master (no_master is set)
Sat Aug 24 23:26:49 2024 - [info] Current Alive Master: 172.25.254.10(172.25.254.10:3306)
Sat Aug 24 23:26:49 2024 - [info] Checking slave configurations..
Sat Aug 24 23:26:49 2024 - [info] read_only=1 is not set on slave 172.25.254.20(172.25.254.20:3306).
Sat Aug 24 23:26:49 2024 - [info] read_only=1 is not set on slave 172.25.254.30(172.25.254.30:3306).
Sat Aug 24 23:26:49 2024 - [info] Checking replication filtering settings..
Sat Aug 24 23:26:49 2024 - [info] binlog_do_db= , binlog_ignore_db=
Sat Aug 24 23:26:49 2024 - [info] Replication filtering check ok.
Sat Aug 24 23:26:49 2024 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sat Aug 24 23:26:49 2024 - [info] Checking SSH publickey authentication settings on the current master..
Sat Aug 24 23:26:50 2024 - [info] HealthCheck: SSH to 172.25.254.10 is reachable.
Sat Aug 24 23:26:50 2024 - [info]
172.25.254.10(172.25.254.10:3306) (current master)
+--172.25.254.20(172.25.254.20:3306)
+--172.25.254.30(172.25.254.30:3306)
Sat Aug 24 23:26:50 2024 - [info] Checking replication health on 172.25.254.20..
Sat Aug 24 23:26:50 2024 - [info] ok.
Sat Aug 24 23:26:50 2024 - [info] Checking replication health on 172.25.254.30..
Sat Aug 24 23:26:50 2024 - [info] ok.
Sat Aug 24 23:26:50 2024 - [warning] master_ip_failover_script is not defined.
Sat Aug 24 23:26:50 2024 - [warning] shutdown_script is not defined.
Sat Aug 24 23:26:50 2024 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.6.MHA的故障切换
6.1MHA的故障切换过程
共包括以下的步骤： 1.配置文件检查阶段，这个阶段会检查整个集群配置文件配置 2.宕机的master处理，这 个阶段包括虚拟ip摘除操作，主机关机操作 3.复制dead master和最新slave相差的relay log，并保存到MHA Manger具体的目录下 4.识别含有最新更新的slave 5.应用从master保存的二进制日志事件（binlog events） 6.提升一个slave为新的master进行复制 7.使其他的slave连接新的master进行复制
6.2切换方式：master未出现故障手动切换
#在master数据节点还在正常工作情况下
[root@node4 ~]# masterha_master_switch \
--conf=/etc/masterha/app1.cnf \ #指定配置文件
--master_state=alive \ #指定master节点状态
--new_master_host=172.25.254.20 \ #指定新master节点
--new_master_port=3306 \ #执行新master节点端口
--orig_master_is_new_slave \ #原始master会变成新的slave
--running_updates_limit=10000 #切换的超时时间
...
172.25.254.20(172.25.254.20:3306) completed successfully.
检测
[root@node4 masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnf
...
MySQL Replication Health is OK.
[root@node3 ~]# mysql -p123
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.206.3master故障手动切换
#模拟master故障
[root@node2 ~]# /etc/init.d/mysqld stop
#在MHA-master中做故障切换
[root@node4 ~]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=192.168.56.12 --dead_master_port=3306 --new_master_host=192.168.56.11 --new_master_port=3306 --ignore_last_failover #表示忽略在/etc/masterha/目录中在切换过程中生成的锁文件
#masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.25.254.20 --dead_master_port=3306 --new_master_host=172.25.254.10 --new_master_port=3306 --ignore_last_failover
...
172.25.254.10(172.25.254.10:3306) completed successfully.6.4恢复故障mysql节点
[root@node2 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
[root@node2 ~]# mysql -p123
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.10', MASTER_USER='repl',
MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.10
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2457
Relay_Log_File: node2-relay-bin.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
#测试一主两从是否正常
[root@node4 masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnf
...
MySQL Replication Health is OK.
自动切换
[root@node4 ~]# rm -fr /etc/masterha/app1.failover.complete #删掉切换锁文件
#监控程序通过指定配置文件监控master状态，当master出问题后自动切换并退出避免重复做故障切换,#启动之前需要删掉。
[root@node4 masterha]# masterha_manager --conf=/etc/masterha/app1.cnf #启动之前需要有一台机子处于故障。
....
Master failover to 172.25.254.20(172.25.254.20:3306) completed successfully.
[root@node4 masterha]# cat /etc/masterha/manager.log
#恢复故障节点
[root@node1 ~]# /etc/init.d/mysqld start
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.20', MASTER_USER='repl',
MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.20
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 234
Relay_Log_File: node1-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#清除锁文件
[root@node4 masterha]# rm -rf app1.failover.complete manager.log7.为MHA添加VIP功能
#上传脚本
[root@node4 ~]# ls
master_ip_failover master_ip_online_change MHA-7 MHA-7.zip
[root@node4 ~]# cp master_ip_failover master_ip_online_change /usr/local/bin/
[root@node4 ~]# chmod +x /usr/local/bin/master_ip_*
#修改脚本在脚本中只需要修改下vip即可
[root@node4 ~]# vim /usr/local/bin/master_ip_failover
my $vip = '172.25.254.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
[root@node4 ~]# vim /usr/local/bin/master_ip_online_change
my $vip = '172.25.254.100/24';
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";
my $exit_code = 0;
[root@node4 masterha]# vim app1.cnf
[server default]
user=root
password=123
ssh_user=root
repl_user=repl
repl_password=123
master_binlog_dir= /data/mysql
remote_workdir=/tmp
secondary_check_script= masterha_secondary_check -s 172.25.254.10 -s 172.25.254.11
ping_interval=3
master_ip_failover_script= /usr/local/bin/master_ip_failover
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
master_ip_online_change_script= /usr/local/bin/master_ip_online_change #一定要注意路径，不要露写，^__^.
[server default]
manager_workdir=/etc/masterha
manager_log=/etc/masterha/manager.log
....
[root@node2 ~]# ip a a 172.25.254.100/24 dev eth0 #在master节点添加VIP
#测试一主两从是否正常
[root@node4 masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnf
...
MySQL Replication Health is OK.
#启动监控程序
[root@node4 masterha]# masterha_manager --conf=/etc/masterha/app1.cnf
Sun Aug 25 21:50:23 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Aug 25 21:50:23 2024 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Sun Aug 25 21:50:23 2024 - [info] Reading server configuration from /etc/masterha/app1.cnf..
[root@node4 masterha]# tail -f /etc/masterha/manager.log #实时看日志
...
Sun Aug 25 21:50:24 2024 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
#模拟故障
[root@node1 ~]# /etc/init.d/mysqld stop #关闭主节点服务
[root@node4 masterha]# tail -f /etc/masterha/manager.log
172.25.254.20(172.25.254.20:3306): Resetting slave info succeeded.
Master failover to 172.25.254.20(172.25.254.20:3306) completed successfully.
#出现的效果
#node3-slave端
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.20
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 234
Relay_Log_File: node3-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@node2 ~]# ip a
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:47:1f:e3 brd ff:ff:ff:ff:ff:ff
inet 172.25.254.20/24 brd 172.25.254.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet 172.25.254.11/32 scope global eth0
valid_lft forever preferred_lft forever
inet 172.25.254.100/24 scope global secondary eth0
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe47:1fe3/64 scope link
valid_lft forever preferred_lft forever
#恢复故障主机
[root@node mysql]# /etc/init.d/mysqld start
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.20', MASTER_USER='repl',
MASTER_PASSWORD='123', MASTER_AUTO_POSITION=1;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.254.20
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 234
Relay_Log_File: node1-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@node4 masterha]# rm -rf app1.failover.complete manager.log
#手动切换后查看vip变化
[root@node4 masterha]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.10 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
...
It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 172.25.254.20(172.25.254.20:3306)? (YES/no): yes
....
Starting master switch from 172.25.254.20(172.25.254.20:3306) to 172.25.254.10(172.25.254.10:3306)? (yes/NO): yes
....
Sun Aug 25 22:28:00 2024 - [info] 172.25.254.10: Resetting slave info succeeded.
Sun Aug 25 22:28:00 2024 - [info] Switching master to 172.25.254.10(172.25.254.10:3306) completed successfully.
[root@node1 ~]# ip a
....
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:25:3f:97 brd ff:ff:ff:ff:ff:ff
inet 172.25.254.10/24 brd 172.25.254.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet 172.25.254.100/24 scope global secondary eth0
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe25:3f97/64 scope link
valid_lft forever preferred_lft forevertips:
跟大家分享一下配置心得，有时候结果不出来，不要着急，先按照配置的步骤或者思路倒推，看看自己的步骤有没有出错，其次，看看防火墙，selinux有没有关，自己的命令有没有漏写，路径，有没有在对应的文件目录进行相关的操作（主要是涉及的相关复制传输的文件及环境，路径一定要对，回头在到对应的机子检查自己的文件和环境到底传输过来没），最后，去除低级错误后，再找不到就可以请教其他nb的人。
最后的最后，作者希望大家可以跟我一起相信光，向着光，一起闪闪发光，也祝愿大家工作顺利，身体健康，阖家欢乐。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
