Mysql数据库之MHA高可用架构

最新推荐文章于 2024-09-08 10:40:56 发布
最新推荐文章于 2024-09-08 10:40:56 发布
阅读量283 收藏 1
点赞数
文章标签： 数据库 mysql 架构 服务器 负载均衡
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45680960/article/details/125237009
版权

Mysql数据库之MHA高可用架构

MHA（Master High Availability）是由日本人yoshinorim开发的一款成熟且开源的MySQL高可用程序，它实现了MySQL主从环境下MASTER宕机后能够自动进行单次故障转移的功能，其本身由perl语言编写，安装方便使用简单。

在 MySQL 故障切换过程中，MHA 能做到在 0~30 秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA 能在最大程度上保证数据的一致性，以达到真正意义上的高可用。

该软件由两部分组成：MHA Manager（管理节点）和 MHA Node（数据节点）。MHA Manager 可以单独部署在一台独立的机器上管理多个 master-slave 集群，也可以部署在一台 slave 节点上。MHA Node 运行在每台 MySQL 服务器上，MHA Manager 会定时探测集群中的 master 节点，当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master，然后将所有其他的 slave 重新指向新的 master。整个故障转移过程对应用程序完全透明。

在 MHA 自动故障切换过程中，MHA 试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过 ssh 访问，MHA 没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用 MySQL 5.5 的半同步复制，可以大大降低数据丢失的风险。MHA 可以与半同步复制结合起来。如果只有一个 slave 已经收到了最新的二进制日志，MHA 可以将最新的二进制日志应用于其他所有的 slave 服务器上，因此可以保证所有节点的数据一致性。

文章目录


在这里插入图片描述

MHA 的组成

  • MHA Node（数据节点）

MHA Node 运行在每台 MySQL 服务器上。

  • MHA Manager（管理节点）

MHA Manager 可以单独部署在一台独立的机器上，管理多个 master-slave 集群；也可以部署在一台 slave 节点上。

MHA Manager 会定时探测集群中的 master 节点。当 master 出现故障时，它可以自动将最新数据的 slave 提升为新的 master， 然后将所有其他的 slave 重新指向新的 master。整个故障转移过程对应用程序完全透明。

MHA 的特点

  • 自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据不丢失

  • 使用半同步复制，可以大大降低数据丢失的风险，如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性

  • 目前MHA支持一主多从架构，最少三台服务，即一主两从

MHA部署搭建

Master 节点服务器:192.168.237.128

MHA manager 节点服务器:192.168.237.129

Slave1 节点服务器:192.168.237.148

Slave2 节点服务器:192.168.237.138

master节点服务器(要安装mysql，我这里已经脚本安装好，具体安装过程见前几章)

[root@localhost opt]# systemctl stop firewalld
[root@localhost opt]# systemctl disable firewalld
[root@localhost opt]# setenforce 0
[root@localhost opt]# hostnamectl set-hostname Mysql1			#更改主机名，方便记忆
[root@localhost opt]# vim /etc/my.cnf							#修改配置文件
server-id = 1
log_bin = master-bin
log-slave-updates = true
:wq
[root@localhost opt]# systemctl restart mysqld
[root@localhost opt]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/				#设置软连接
[root@localhost opt]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/			#设置软连接
[root@localhost opt]# mysql -uroot -p											#登录
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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> grant replication slave on *.* to 'myslave'@'192.168.237.%' identified by '123';	#赋予权限
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.237.%' identified by 'manager';		#赋予权限
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'Mysql1' identified by 'manager';				#赋予权限
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'Mysql2' identified by 'manager';				#赋予权限
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'Mysql3' identified by 'manager';				#赋予权限
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> flush privileges;															#刷新权限
Query OK, 0 rows affected (0.01 sec)
				
mysql> show master status;								#在 Master 节点查看二进制文件和同步点
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |     1595 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

[root@localhost opt]# yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN		#安装依赖
[root@localhost opt]# tar zxvf mha4mysql-node-0.57.tar.gz
[root@localhost opt]# cd mha4mysql-node-0.57
[root@localhost mha4mysql-node-0.57]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI        ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::node

[root@localhost mha4mysql-node-0.57]# make && make install

Slave1 节点服务器

[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld
[root@localhost ~]# setenforce 0
[root@localhost ~]# hostnamectl set-hostname Mysql2
[root@localhost ~]# vim /etc/my.cnf
server-id = 2 						#三台服务器的 server-id 不能一样
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
:wq

[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@localhost ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
[root@localhost ~]# mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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> grant replication slave on *.* to 'myslave'@'192.168.237.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.237.%' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'Mysql1' identified by 'manager';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'Mysql2' identified by 'manager';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'Mysql3' identified by 'manager';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> change master to master_host='192.168.237.128',master_user='myslave',master_password='123',master_log_file='master-bin.000001',master_log_pos=1595;						 #设置master服务器
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G								#查看数据同步结果
 Slave_IO_Running: Yes									#确保 IO 和 SQL 线程都是 Yes，代表同步正常
 Slave_SQL_Running: Yes

mysql> set global read_only=1;							#开启只读
Query OK, 0 rows affected (0.00 sec)

[root@localhost opt]# yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN		#安装依赖
[root@localhost opt]# tar zxvf mha4mysql-node-0.57.tar.gz
[root@localhost opt]# cd mha4mysql-node-0.57
[root@localhost mha4mysql-node-0.57]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI        ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::node

[root@localhost mha4mysql-node-0.57]# make && make install

Slave2 节点服务器

[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld
[root@localhost ~]# setenforce 0
[root@localhost ~]# hostnamectl set-hostname Mysql3
[root@localhost ~]# vim /etc/my.cnf
server-id = 3 						#三台服务器的 server-id 不能一样
log_bin = master-bin
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
:wq

[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# ln -s /usr/local/mysql/bin/mysql /usr/sbin/
[root@localhost ~]# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/sbin/
[root@localhost ~]# mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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> grant replication slave on *.* to 'myslave'@'192.168.237.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.237.%' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'Mysql1' identified by 'manager';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'Mysql2' identified by 'manager';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'Mysql3' identified by 'manager';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> change master to master_host='192.168.237.128',master_user='myslave',master_password='123',master_log_file='master-bin.000001',master_log_pos=1595;						 #设置master服务器
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G								#查看数据同步结果
 Slave_IO_Running: Yes									#确保 IO 和 SQL 线程都是 Yes，代表同步正常
 Slave_SQL_Running: Yes

mysql> set global read_only=1;							#开启只读
Query OK, 0 rows affected (0.00 sec)

master节点服务器
mysql> create database wpc;
Query OK, 1 row affected (0.01 sec)

mysql> use wpc
Database changed
mysql> create table test(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(id) values (1);
Query OK, 1 row affected (0.01 sec)

Slave1和Slave2测试
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test_db            |
| wpc                |
+--------------------+
6 rows in set (0.01 sec)

mysql> select * from wpc.test;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

[root@localhost opt]# yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN		#安装依赖
[root@localhost opt]# tar zxvf mha4mysql-node-0.57.tar.gz
[root@localhost opt]# cd mha4mysql-node-0.57
[root@localhost mha4mysql-node-0.57]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI        ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::node

[root@localhost mha4mysql-node-0.57]# make && make install

MHA manager节点服务器
[root@localhost ~]# yum install epel-release --nogpgcheck -y
[root@localhost ~]# yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN

[root@localhost opt]# tar zxvf mha4mysql-node-0.57.tar.gz
[root@localhost opt]# cd mha4mysql-node-0.57
[root@localhost mha4mysql-node-0.57]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI        ...loaded. (1.627)
- DBD::mysql ...loaded. (4.023)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::node

[root@localhost mha4mysql-node-0.57]# make && make install

[root@localhost ~]#cd /opt
[root@localhost opt]#tar zxvf mha4mysql-manager-0.57.tar.gz
[root@localhost opt]#cd mha4mysql-manager-0.57
[root@localhost mha4mysql-manager-0.57]# perl Makefile.PL
*** Module::AutoInstall version 1.06
*** Checking for Perl dependencies...
[Core Features]
- DBI                   ...loaded. (1.627)
- DBD::mysql            ...loaded. (4.023)
- Time::HiRes           ...loaded. (1.9725)
- Config::Tiny          ...loaded. (2.14)
- Log::Dispatch         ...loaded. (2.41)
- Parallel::ForkManager ...loaded. (1.18)
- MHA::NodeConst        ...loaded. (0.57)
*** Module::AutoInstall configuration finished.
Checking if your kit is complete...
Looks good
Writing Makefile for mha4mysql::manager
[root@localhost ~]#make && make install

#manager 组件安装后在/usr/local/bin 下面会生成几个工具，主要包括以下几个：

工具说明
masterha_check_ssh检查 MHA 的 SSH 配置状况
masterha_check_repl检查 MySQL 复制状况
masterha_manger启动 manager的脚本
masterha_check_status检测当前 MHA 运行状态
masterha_master_monitor检测 master 是否宕机
masterha_master_switch控制故障转移（自动或者手动)
masterha_conf_host添加或删除配置的 server 信息
masterha_stop关闭manager

#node 组件安装后也会在/usr/local/bin 下面会生成几个脚本（这些工具通常由 MHAManager 的脚本触发，无需人为操作）主要如下：

工具说明
save_binary_logs保存和复制 master 的二进制日志
apply_diff_relay_logs识别差异的中继日志事件并将其差异的事件应用于其他的 slave
filter_mysqlbinlog去除不必要的 ROLLBACK 事件（MHA 已不再使用这个工具）
purge_relay_logs清除中继日志（不会阻塞 SQL 线程）
manager 节点上配置到所有数据库节点的无密码认证
[root@localhost ~]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Created directory '/root/.ssh'.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:IdvcUwkcXV3OlWsNAvpBtQgLNEDT72LOVUiTx7B2i+4 root@localhost.localdomain
The key's randomart image is:
+---[RSA 2048]----+
|   .+++ o===....=|
|     ..o+*+ooo.+o|
|      .o*++ +. .=|
|       *o*.+   o.|
|      ..S.=   .  |
|      o.o  .     |
|     + o.        |
|      o.         |
|        E        |
+----[SHA256]-----+
[root@localhost ~]# ssh-copy-id 192.168.237.128
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.237.128 (192.168.237.128)' can't be established.
ECDSA key fingerprint is SHA256:NNSJ3YVr1bObxD3BqkJD3cpJlVQOwnCdNEvhOv7qBJA.
ECDSA key fingerprint is MD5:48:48:73:23:80:f8:f0:3b:7b:5d:f8:b6:36:ba:db:f6.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.128's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.237.128'"
and check to make sure that only the key(s) you wanted were added.

[root@localhost ~]# ssh-copy-id 192.168.237.138
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.237.138 (192.168.237.138)' can't be established.
ECDSA key fingerprint is SHA256:f0A00lMrl1k5OMRihmFMJ3fMH04+r3n6brOeFAqZ2Tg.
ECDSA key fingerprint is MD5:b8:f6:fb:25:84:f8:f1:21:f7:43:9e:88:79:cb:8b:d2.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.138's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.237.138'"
and check to make sure that only the key(s) you wanted were added.

[root@localhost ~]# ssh-copy-id 192.168.237.148
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.237.148 (192.168.237.148)' can't be established.
ECDSA key fingerprint is SHA256:xFapEICWsZOjdlUyVM3guOQECszsnmJgrnA0yyDIgNk.
ECDSA key fingerprint is MD5:ce:2e:10:1c:e9:43:ce:35:8c:66:26:36:d6:11:08:76.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.148's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.237.148'"
and check to make sure that only the key(s) you wanted were added.

mysql1 上配置到数据库节点 mysql2 和 mysql3 的无密码认证

[root@localhost mha4mysql-node-0.57]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
ssh-copy-id 192.168.237.148. saved in ssh-copy-id 192.168.237.138
ssh-copy-id 192.168.237.148.pub.n ssh-copy-id 192.168.237.138
The key fingerprint is:
SHA256:k8bFOBGJ/ASGDYvCLFQeqZrvhz7DtoXld50G2naJc04 root@mysql1
The key's randomart image is:
+---[RSA 2048]----+
| ..oo=oooo       |
|+ .ooo+ o+       |
|ooo..  oo o      |
|.o     ..+       |
|..  .   S        |
|o  +   + = o     |
| o..o o * E      |
|  B... o B       |
| ++=      .      |
+----[SHA256]-----+

[root@localhost mha4mysql-node-0.57]# ssh-copy-id 192.168.237.138
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.237.138 (192.168.237.138)' can't be established.
ECDSA key fingerprint is SHA256:f0A00lMrl1k5OMRihmFMJ3fMH04+r3n6brOeFAqZ2Tg.
ECDSA key fingerprint is MD5:b8:f6:fb:25:84:f8:f1:21:f7:43:9e:88:79:cb:8b:d2.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.138's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.237.138'"
and check to make sure that only the key(s) you wanted were added.

The authenticity of host '192.168.237.148 (192.168.237.148)' can't be established.
ECDSA key fingerprint is SHA256:xFapEICWsZOjdlUyVM3guOQECszsnmJgrnA0yyDIgNk.
ECDSA key fingerprint is MD5:ce:2e:10:1c:e9:43:ce:35:8c:66:26:36:d6:11:08:76.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.148's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.237.148'"
and check to make sure that only the key(s) you wanted were added.
mysql2 上配置到数据库节点 mysql1 和 mysql3 的无密码认证

[root@localhost mha4mysql-node-0.57]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:MqWrzYRPdoQVFH8BAxR1wkQqWjcNfwC7Q5f1Sq81pIs root@mysql2
The key's randomart image is:
+---[RSA 2048]----+
|       oXXO.+    |
|         O.B o   |
|      o O * + o  |
|     o O + + =   |
|    . = S   o +  |
|     . = . . + . |
|    . = . E o    |
|     O .         |
|    . +          |
+----[SHA256]-----+

root@localhost mha4mysql-node-0.57]# ssh-copy-id 192.168.237.138
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.138's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.237.138'"
and check to make sure that only the key(s) you wanted were added.

[root@localhost mha4mysql-node-0.57]# ssh-copy-id 192.168.237.128
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.237.128 (192.168.237.128)' can't be established.
ECDSA key fingerprint is SHA256:NNSJ3YVr1bObxD3BqkJD3cpJlVQOwnCdNEvhOv7qBJA.
ECDSA key fingerprint is MD5:48:48:73:23:80:f8:f0:3b:7b:5d:f8:b6:36:ba:db:f6.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.128's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.237.128'"
and check to make sure that only the key(s) you wanted were added.

mysql3 上配置到数据库节点 mysql1 和 mysql2 的无密码认证
[root@localhost ~]# ssh-keygen -t rsa

Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:810MExFH7yLmTI8NL2olxtUqF5jULMZ2A6WcFnLRG4Q root@mysql3
The key's randomart image is:
+---[RSA 2048]----+
|        ..=X*+o  |
|         +E+Bo . |
|         +==o=  .|
|         .o ++.. |
|        S. .=o+ .|
|         o=*+O . |
|         ..=* +  |
|          .. .   |
|         ..      |
+----[SHA256]-----+
[root@localhost ~]# ssh-copy-id 192.168.237.128
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.237.128 (192.168.237.128)' can't be established.
ECDSA key fingerprint is SHA256:NNSJ3YVr1bObxD3BqkJD3cpJlVQOwnCdNEvhOv7qBJA.
ECDSA key fingerprint is MD5:48:48:73:23:80:f8:f0:3b:7b:5d:f8:b6:36:ba:db:f6.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.128's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.237.128'"
and check to make sure that only the key(s) you wanted were added.

[root@localhost ~]# ssh-copy-id 192.168.237.138
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '192.168.237.138 (192.168.237.138)' can't be established.
ECDSA key fingerprint is SHA256:f0A00lMrl1k5OMRihmFMJ3fMH04+r3n6brOeFAqZ2Tg.
ECDSA key fingerprint is MD5:b8:f6:fb:25:84:f8:f1:21:f7:43:9e:88:79:cb:8b:d2.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@192.168.237.138's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '192.168.237.138'"
and check to make sure that only the key(s) you wanted were added.

MHA manager节点服务器
[root@localhost ~]# cp -rp /opt/mha4mysql-manager-0.57/samples/scripts /usr/local/bin
[root@localhost ~]# ll /usr/local/bin/scripts/
总用量 32
-rwxr-xr-x. 1 nginx nginx  3648 5月  31 2015 master_ip_failover
-rwxr-xr-x. 1 nginx nginx  9870 5月  31 2015 master_ip_online_change
-rwxr-xr-x. 1 nginx nginx 11867 5月  31 2015 power_manager
-rwxr-xr-x. 1 nginx nginx  1360 5月  31 2015 send_report
工具说明
master_ip_failover自动切换时 VIP 管理的脚本
master_ip_online_change在线切换时 vip 的管理
power_manager故障发生后关闭主机的脚本
send_report因故障切换后发送报警的脚本
[root@localhost ~]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin
[root@localhost ~]# vim /usr/local/bin/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';

use Getopt::Long;

my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
#############################添加内容部分#########################################
my $vip = '192.168.237.200';									#指定vip的地址
my $brdc = '192.168.237.255';								#指定vip的广播地址
my $ifdev = 'ens33';										#指定vip绑定的网卡
my $key = '1';												#指定vip绑定的虚拟网卡序列号
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";		#代表此变量值为ifconfig ens33:1 192.168.237.200
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";		#代表此变量值为ifconfig ens33:1 192.168.237.200 down
my $exit_code = 0;											#指定退出状态码为0
#my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;";
#my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key";
##################################################################################
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);

exit &main();

sub main {

print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

if ( $command eq "stop" || $command eq "stopssh" ) {

my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {

my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
## A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}

sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
:wq

[root@localhost ~]# mkdir /etc/masterha
[root@localhost ~]# cp /opt/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/masterha
[root@localhost ~]# vim /etc/masterha/app1.cnf
[server default]
manager_log=/var/log/masterha/app1/manager.log　　　　　 　#manager日志
manager_workdir=/var/log/masterha/app1.log　　　　　　　 　#manager工作目录
master_binlog_dir=/usr/local/mysql/data/　　　　　　　 　　#master保存binlog的位置，这里的路径要与master里配置的binlog的路径一致，以便MHA能找到
master_ip_failover_script=/usr/local/bin/master_ip_failover　　#设置自动failover时候的切换脚本，也就是上面的那个脚本
master_ip_online_change_script=/usr/local/bin/master_ip_online_change　　#设置手动切换时候的切换脚本
password=manager			#设置mysql中root用户的密码，这个密码是前文中创建监控用户的那个密码
ping_interval=1				#设置监控主库，发送ping包的时间间隔，默认是3秒，尝试三次没有回应的时候自动进行failover
remote_workdir=/tmp			#设置远端mysql在发生切换时binlog的保存位置
repl_password=123			#设置复制用户的密码
repl_user=myslave			#设置复制用户的用户
report_script=/usr/local/send_report　　　　　#设置发生切换后发送的报警的脚本
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.237.148 -s 192.168.237.138	#指定检查的从服务器IP地址
shutdown_script=""			#设置故障发生后关闭故障主机脚本（该脚本的主要作用是关闭主机防止发生脑裂,这里没有使用）
ssh_user=root				#设置ssh的登录用户名
user=mha					#设置监控用户root

[server1]
hostname=192.168.237.128
port=3306

[server2]
hostname=192.168.237.148
port=3306
candidate_master=1
#设置为候选master，设置该参数以后，发生主从切换以后将会将此从库提升为主库，即使这个从库不是集群中最新的slave

check_repl_delay=0
#默认情况下如果一个slave落后master 超过100M的relay logs的话，MHA将不会选择该slave作为一个新的master， 
因为对于这个slave的恢复需要花费很长时间；通过设置check_repl_delay=0，MHA触发切换在选择一个新的master的
时候将会忽略复制延时，这个参数对于设置了candidate_master=1的主机非常有用，因为这个候选主在切换的过程中一定是新的master

[server3]
hostname=192.168.237.138
port=3306
:wq

master节点服务器
[root@localhost mha4mysql-node-0.57]# /sbin/ifconfig ens33:1 192.168.237.200/24

# manager 节点上测试 mysql 主从连接情况
[root@localhost mha4mysql-manager-0.57]# masterha_check_repl -conf=/etc/masterha/app1.cnf
Thu Jun  9 17:38:17 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun  9 17:38:17 2022 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Thu Jun  9 17:38:17 2022 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Thu Jun  9 17:38:17 2022 - [info] MHA::MasterMonitor version 0.57.
Thu Jun  9 17:38:18 2022 - [info] GTID failover mode = 0
Thu Jun  9 17:38:18 2022 - [info] Dead Servers:
Thu Jun  9 17:38:18 2022 - [info] Alive Servers:
Thu Jun  9 17:38:18 2022 - [info]   192.168.237.128(192.168.237.128:3306)
Thu Jun  9 17:38:18 2022 - [info]   192.168.237.148(192.168.237.148:3306)
Thu Jun  9 17:38:18 2022 - [info]   192.168.237.138(192.168.237.138:3306)
Thu Jun  9 17:38:18 2022 - [info] Alive Slaves:
Thu Jun  9 17:38:18 2022 - [info]   192.168.237.148(192.168.237.148:3306)  Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Thu Jun  9 17:38:18 2022 - [info]     Replicating from 192.168.237.128(192.168.237.128:3306)
Thu Jun  9 17:38:18 2022 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu Jun  9 17:38:18 2022 - [info]   192.168.237.138(192.168.237.138:3306)  Version=5.7.20-log (oldest major version between slaves) log-bin:enabled
Thu Jun  9 17:38:18 2022 - [info]     Replicating from 192.168.237.128(192.168.237.128:3306)
Thu Jun  9 17:38:18 2022 - [info] Current Alive Master: 192.168.237.128(192.168.237.128:3306)
Thu Jun  9 17:38:18 2022 - [info] Checking slave configurations..
Thu Jun  9 17:38:18 2022 - [info]  read_only=1 is not set on slave 192.168.237.148(192.168.237.148:3306).
Thu Jun  9 17:38:18 2022 - [warning]  relay_log_purge=0 is not set on slave 192.168.237.148(192.168.237.148:3306).
Thu Jun  9 17:38:18 2022 - [info]  read_only=1 is not set on slave 192.168.237.138(192.168.237.138:3306).
Thu Jun  9 17:38:18 2022 - [warning]  relay_log_purge=0 is not set on slave 192.168.237.138(192.168.237.138:3306).
Thu Jun  9 17:38:18 2022 - [info] Checking replication filtering settings..
Thu Jun  9 17:38:18 2022 - [info]  binlog_do_db= , binlog_ignore_db= 
Thu Jun  9 17:38:18 2022 - [info]  Replication filtering check ok.
Thu Jun  9 17:38:18 2022 - [info] GTID (with auto-pos) is not supported
Thu Jun  9 17:38:18 2022 - [info] Starting SSH connection tests..
Thu Jun  9 17:38:20 2022 - [info] All SSH connection tests passed successfully.
Thu Jun  9 17:38:20 2022 - [info] Checking MHA Node version..
Thu Jun  9 17:38:21 2022 - [info]  Version check ok.
Thu Jun  9 17:38:21 2022 - [info] Checking SSH publickey authentication settings on the current master..
Thu Jun  9 17:38:21 2022 - [info] HealthCheck: SSH to 192.168.237.128 is reachable.
Thu Jun  9 17:38:21 2022 - [info] Master MHA Node version is 0.57.
Thu Jun  9 17:38:21 2022 - [info] Checking recovery script configurations on 192.168.237.128(192.168.237.128:3306)..
Thu Jun  9 17:38:21 2022 - [info]   Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/usr/local/mysql/data --output_file=/tmp/save_binary_logs_test --manager_version=0.57 --start_file=master-bin.000001 
Thu Jun  9 17:38:21 2022 - [info]   Connecting to root@192.168.237.128(192.168.237.128:22).. 
  Creating /tmp if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /usr/local/mysql/data, up to master-bin.000001
Thu Jun  9 17:38:22 2022 - [info] Binlog setting check done.
Thu Jun  9 17:38:22 2022 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Thu Jun  9 17:38:22 2022 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.237.148 --slave_ip=192.168.237.148 --slave_port=3306 --workdir=/tmp --target_version=5.7.20-log --manager_version=0.57 --relay_log_info=/usr/local/mysql/data/relay-log.info  --relay_dir=/usr/local/mysql/data/  --slave_pass=xxx
Thu Jun  9 17:38:22 2022 - [info]   Connecting to root@192.168.237.148(192.168.237.148:22).. 
  Checking slave recovery environment settings..
    Opening /usr/local/mysql/data/relay-log.info ... ok.
    Relay log found at /usr/local/mysql/data, up to relay-log-bin.000004
    Temporary relay log file is /usr/local/mysql/data/relay-log-bin.000004
    Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Thu Jun  9 17:38:22 2022 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.237.138 --slave_ip=192.168.237.138 --slave_port=3306 --workdir=/tmp --target_version=5.7.20-log --manager_version=0.57 --relay_log_info=/usr/local/mysql/data/relay-log.info  --relay_dir=/usr/local/mysql/data/  --slave_pass=xxx
Thu Jun  9 17:38:22 2022 - [info]   Connecting to root@192.168.237.138(192.168.237.138:22).. 
  Checking slave recovery environment settings..
    Opening /usr/local/mysql/data/relay-log.info ... ok.
    Relay log found at /usr/local/mysql/data, up to relay-log-bin.000004
    Temporary relay log file is /usr/local/mysql/data/relay-log-bin.000004
    Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Thu Jun  9 17:38:22 2022 - [info] Slaves settings check done.
Thu Jun  9 17:38:22 2022 - [info] 
192.168.237.128(192.168.237.128:3306) (current master)
 +--192.168.237.148(192.168.237.148:3306)
 +--192.168.237.138(192.168.237.138:3306)

Thu Jun  9 17:38:22 2022 - [info] Checking replication health on 192.168.237.148..
Thu Jun  9 17:38:22 2022 - [info]  ok.
Thu Jun  9 17:38:22 2022 - [info] Checking replication health on 192.168.237.138..
Thu Jun  9 17:38:22 2022 - [info]  ok.
Thu Jun  9 17:38:22 2022 - [info] Checking master_ip_failover_script status:
Thu Jun  9 17:38:22 2022 - [info]   /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.237.128 --orig_master_ip=192.168.237.128 --orig_master_port=3306 


IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.237.200===

Checking the Status of the script.. OK 
Thu Jun  9 17:38:22 2022 - [info]  OK.
Thu Jun  9 17:38:22 2022 - [warning] shutdown_script is not defined.
Thu Jun  9 17:38:22 2022 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.

#后台启动
[root@localhost mha4mysql-manager-0.57]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &

[root@localhost mha4mysql-manager-0.57]# ps -ef|grep masterha
root      44915  15765  0 17:40 pts/1    00:00:00 perl /usr/local/bin/masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover

#查看MHA状态
[root@localhost mha4mysql-manager-0.57]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:44915) is running(0:PING_OK), master:192.168.237.128
#查看MHA日志
[root@localhost mha4mysql-manager-0.57]# cat /var/log/masterha/app1/manager.log | grep "current master"
Thu Jun  9 17:40:43 2022 - [info] Checking SSH publickey authentication settings on the current master..
192.168.237.128(192.168.237.128:3306) (current master)

master节点服务器
[root@localhost mha4mysql-node-0.57]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.237.128  netmask 255.255.255.0  broadcast 192.168.237.255
        inet6 fe80::af1c:3feb:e960:2d03  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:82:35:12  txqueuelen 1000  (Ethernet)
        RX packets 152784  bytes 192828666 (183.8 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 53618  bytes 10649554 (10.1 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.237.200  netmask 255.255.255.0  broadcast 192.168.237.255
        ether 00:0c:29:82:35:12  txqueuelen 1000  (Ethernet)

注：若要关闭 manager 服务，可以使用如下命令

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

模拟故障

MHA manager节点服务器

[root@localhost mha4mysql-manager-0.57]# tail -f /var/log/masterha/app1/manager.log

IN SCRIPT TEST====/sbin/ifconfig ens33:1 down==/sbin/ifconfig ens33:1 192.168.237.200===

Checking the Status of the script.. OK 
Thu Jun  9 17:40:44 2022 - [info]  OK.
Thu Jun  9 17:40:44 2022 - [warning] shutdown_script is not defined.
Thu Jun  9 17:40:44 2022 - [info] Set master ping interval 1 seconds.
Thu Jun  9 17:40:44 2022 - [info] Set secondary check script: /usr/local/bin/masterha_secondary_check -s 192.168.237.148 -s 192.168.237.138
Thu Jun  9 17:40:44 2022 - [info] Starting ping health check on 192.168.237.128(192.168.237.128:3306)..
Thu Jun  9 17:40:44 2022 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

master节点服务器

[root@localhost mha4mysql-node-0.57]# systemctl stop mysqld			#关闭mysqld
[root@localhost mha4mysql-node-0.57]# systemctl status mysqld		#检查mysqld状态
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since 四 2022-06-09 17:46:28 CST; 27s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 46910 ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 46892 ExecStartPre=/usr/local/mysql/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 46913 (code=exited, status=0/SUCCESS)

6月 09 16:23:33 mysql1 mysqld[46910]: 2022-06-09T08:23:33.817544Z 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
6月 09 16:23:33 mysql1 mysqld[46910]: 2022-06-09T08:23:33.817552Z 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.
6月 09 16:23:33 mysql1 mysqld[46910]: 2022-06-09T08:23:33.820068Z 0 [Note] Event Scheduler: Loaded 0 events
6月 09 16:23:33 mysql1 mysqld[46910]: 2022-06-09T08:23:33.820328Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
6月 09 16:23:33 mysql1 mysqld[46910]: Version: '5.7.20-log'  socket: '/usr/local/mysql/mysql.sock'  port: 3306  Source distribution
6月 09 16:23:33 mysql1 mysqld[46910]: 2022-06-09T08:23:33.820341Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. ...ip this check.
6月 09 16:23:33 mysql1 mysqld[46910]: 2022-06-09T08:23:33.820343Z 0 [Note] Beginning of list of non-natively partitioned tables
6月 09 16:23:33 mysql1 systemd[1]: Started MySQL Server.
6月 09 17:46:17 mysql1 systemd[1]: Stopping MySQL Server...
6月 09 17:46:28 mysql1 systemd[1]: Stopped MySQL Server.
Hint: Some lines were ellipsized, use -l to show in full.

MHA manager节点服务器

----- Failover Report -----

app1: MySQL Master failover 192.168.237.128(192.168.237.128:3306) to 192.168.237.148(192.168.237.148:3306) succeeded

Master 192.168.237.128(192.168.237.128:3306) is down!

Check MHA Manager logs at localhost.localdomain:/var/log/masterha/app1/manager.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.237.128(192.168.237.128:3306)
The latest slave 192.168.237.148(192.168.237.148:3306) has all relay logs for recovery.
Selected 192.168.237.148(192.168.237.148:3306) as a new master.
192.168.237.148(192.168.237.148:3306): OK: Applying all logs succeeded.
192.168.237.148(192.168.237.148:3306): OK: Activated master IP address.
192.168.237.138(192.168.237.138:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.237.138(192.168.237.138:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.237.148(192.168.237.148:3306)
192.168.237.148(192.168.237.148:3306): Resetting slave info succeeded.
Master failover to 192.168.237.148(192.168.237.148:3306) completed successfully.

#可以看到master服务器宕机，slave1顶替成为master服务器

故障切换备选主库的算法

  • 一般判断从库的是从（position/GTID）判断优劣，数据有差异，最接近于master的slave，成为备选主。

  • 数据一致的情况下，按照配置文件顺序，选择备选主库。

  • 设定有权重（candidate_master=1），按照权重强制指定备选主。

    ​ 默认情况下如果一个slave落后master 100M的relay logs的话，即使有权重，也会失效。

    ​ 如果check_repl_delay=0的话，即使落后很多日志，也强制选择其为备选主。

故障修复

原master节点服务器

[root@localhost mha4mysql-node-0.57]# systemctl restart mysqld
[root@localhost mha4mysql-node-0.57]# mysql -uroot -pabc123
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 3
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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> change master to master_host='192.168.237.148',master_user='myslave',master_password='123',master_log_file='master-bin.000002',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

现master节点服务器（原slave1）

#查看二进制文件和同步点
[root@localhost mha4mysql-node-0.57]# mysql -uroot -pabc123
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 34
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 |      154 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

原master节点服务器

#在原master服务器执行同步操作
mysql> change master to master_host='192.168.237.148',master_user='myslave',master_password='123',master_log_file='master-bin.000002',master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

manager节点服务器

#manager节点上修改配置文件app1.cnf
[root@localhost mha4mysql-manager-0.57]# vi /etc/masterha/app1.cnf
[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1
master_binlog_dir=/usr/local/mysql/data
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=manager
ping_interval=1
remote_workdir=/tmp
repl_password=123
repl_user=myslave
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.237.128 -s 192.168.237.138
shutdown_script=""
ssh_user=root
user=mha

[server1]
hostname=192.168.237.148
port=3306

[server2]
candidate_master=1
check_repl_delay=0
hostname=192.168.237.128
port=3306

[server3]
hostname=192.168.237.138
port=3306

:wq

原master节点服务器

#原master节点服务器设置为只读模式
set global read_only=1;

[root@localhost mha4mysql-manager-0.57]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &
[1] 45607											#后台启动

[root@localhost mha4mysql-manager-0.57]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 (pid:45607) is running(0:PING_OK), master:192.168.237.148		#检查当前master节点服务器是原slave1节点服务器

总结：配置文件的修改尤其麻烦，需要认真核对，配置文件出现问题，后期的检查会报错，并且提示信息不全面，比较难排错。

????9oker??
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MHA 功能及介绍
lgstudyvc的专栏
12-03 7405
概述 MHA是一位日本MySQL大牛用Perl写的一套MySQL故障切换方案，来保证数据库系统的高可用.在宕机的时间内（通常10—30秒内），完成故障切换，部署MHA，可避免主从一致性问题，节约购买新服务器的费用，不影响服务器性能，易安装，不改变现有部署。 还支持在线切换，从当前运行master切换到一个新的master上面，只需要很短的时间（0.5-2秒内），此时仅仅阻塞写操作，并不
Linux-MySQL数据库之 MHA高可用集群部署及故障切换
m0_56107775的博客
06-30 551
MHA（MasterHigh Availability）是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。 MHA 的出现就是解决MySQL 单点的问题。 MySQL故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作。 MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用。MHA Node 运行在每台 MySQL 服务器上。MHA Manager 可以单独部署在一台独立的机器上，管理多个 master-slave 集群；也可以部署在一台 slave 节点上。MH
MySQL(10)数据库实现高可用架构之MHA
qq_44135433的博客
11-03 180
文章目录 一、MySQL MHA介绍 MHA（MasterHigh Availability）是一款开源的 MySQL 的高可用程序，它为 MySQL 主从复制架构提供了 automating master failover 功能。MHA 在监控到 master 节点故障时，会提升其中拥有最新数据的 slave 节点成为新的master 节点，在此期间，MHA 会通过于其它从节点获取额外信息来避免一致性方面的问题。MHA 还提供了 master 节点的在线切换功能，即按需切换 master/slave 节点
mysql-MHA高可用及读写分离
weixin_44695383的博客
08-21 569
1. 主从复制架构演变介绍 1.1 基本结构 （1）一主一从 （2）一主多从 （3）多级主从 （4）双主 （5）循环复制 1.2 高级应用架构演变 1.2.1 高性能架构 (1) 读写分离架构(读性能较高) 代码级别 MySQL proxy (Atlas,mysql router,proxySQL(percona),maxscale)、amoeba(taobao) xx-dbp...
MySQL MHA 基础知识介绍
weixin_51614581的博客
02-19 679
MySQL MHAMySQL MHA1．什么是 MHA2．MHA 的组成3．MHA 的特点 MySQL MHA 1．什么是 MHA MHA（MasterHigh Availability）是一套优秀的MySQL高可用环境下故障切换和主从复制的软件。 MHA 的出现就是解决MySQL 单点的问题。 MySQL故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作。 MHA能在故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用。 2．MHA 的组成 MHA Node（数据节点）
MySQL高可用架构之MHA
weixin_42073629的博客
12-15 763
简介： MHA（MasterHigh Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本DeNA公司youshimaton（现就职于Facebook公司）开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。 该软件由两部分组成：MHA Manager（管理节点）和M.
MySQL 高可用之MHA 工作原理和架构，实现MHA高可用实战案例
qq_43555873的博客
11-06 820
MySQL 高可用解决方案 MHA 工作原理和架构 MHA集群架构 实现 MHA 实战案例
MySQL高可用架构MHA
01-09
MHA工作原理 部署MHA 1.配置四台主机ssh登录不用密码 ssh-keygen -t rsa ssh-copy-id 192.168.100.10（20.30.102） 2.上传安装包，解压编译安装 配置yum仓库 vim /etc/yum.repos.d/mhapath.repo ##进入yum仓库，...
MHA高可用架构搭建细节流程
08-26
本文将详细介绍MHA高可用架构搭建的细节流程，从搭建MySQL主从复制到MHA高可用实现故障转移。本文将涵盖MySQL主从复制的搭建、MHA高可用架构的搭建详细流程，并对相关的技术点进行了详细的解释。 一、环境和软件...
MySQL数据库实现高可用架构之MHA
BeliefWish的博客
11-08 201
目录 一、MySQL MHA介绍 1.1 什么是 MHA? 1.2 MHA 的组成 二、MySQL MHA搭建 2.1 MySQL MHA搭建的步骤（理论） 2.1.1 搭建步骤 2.1.2 故障模拟 2.1.3 故障修复步骤 2.2 MySQL MHA搭建具体操作步骤（实操） 2.2.1 搭建步骤 2.2.2 故障模拟 2.2.3 故障修复步骤 一、MySQL MHA介绍 MHA（MasterHigh Availability）是一款开源的 MySQL 的高可用程序，它为 My
MHA 架构使用原理
魏州青年的博客
09-03 1万+
  MHA 架构介绍： MHA（Master High Availability）目前在 MySQL 高可用方面是一个相对成熟的解决方案，它由日本人 youshimaton 开发，是一套优秀的作为 MySQL 高可用性环境下故障切换和主从提升的高可用软件。在 MySQL 故障切换过程中，MHA 能做到 0~30 秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA 能最大程度...
安装MHA踩过的坑
热门推荐
文档搬运工
04-29 1万+
MHA 0.58+Mysql5.7+redhat7.4 已测试通过。 之前在redhat6.10上安装，在家里电脑安装OK，一样的包，拿到公司安装，失败了。很郁闷。随后改成redhat7.4安装，安装成功，走了不少弯路。把坑写出来。 一些包的下载地方 ，在使用redhat6.10的时候，下载过，安装成功了，redhat7.4没有从这里下载直接光盘yum了。 http://rpm.pbone....
MHA高可用配置以及故障切换和实验中的报错和解决方法
weixin_51432789的博客
02-21 3382
目录一、概述1、MHA简介2、MHA的组成3、MHA特点二、搭建MySQL MHA实验1、实验环境2、实验目的3、实验步骤 一、概述 1、MHA简介 一套优秀的MySQL高可用环境下故障切换和主从复制的软件 MHA的出现就是解决MySQL单点的问题 MySQL故障过程中，MHA能做到0~30秒内自动完成故障切换 MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用 2、MHA的组成 MHA Manager（管理节点） MHA Manager 可以单独部署在一台独立的机器上，管理多个 mast
MHA masterha_check_ssh 报错
weixin_33758863的博客
11-20 2282
2019独角兽企业重金招聘Python工程师标准>>> ...
spring事务详细讲解-深入浅出
最新发布
小电玩
09-08 305
Spring 事务是本身就是对数据库的事务的支持，没有数据库的事务 Spring 是本身无法实现事务的。Spring只提供了统一事务管理接口，具体的实现还是由各数据库自己实现。在使用 Spring 事务时，可以通过注解或编程方式将需要进行事务管理的方法和代码块标记为事务性操作，当这些操作被执行时，Spring 会负责开启时根据当前环境中设置的隔离级别，调整数据库隔离级别。
数据同步是如何实现的？为什么需要数据同步？
oOBubbleX的博客
09-05 866
实时数据同步，确保数据的一致性和实时更新性，将有利于企业各部门之间高效协作，从而支持企业的业务运作和决策。本文将介绍数据同步概念、数据同步的步骤、数据同步的方式，从而加深对数据同步的认识。
mongodb在Java中条件分组聚合查询并且分页(时间戳，按日期分组，年月日...)
简单发点工作中用到的，或者记录下小知识点
09-03 1772
按时间 年月日条件分组，筛选数据，统计数据，聚合查询 打印出mongo的SQL语句
MySQL高可用架构MHA部署与配置详解
"MySQL高可用架构MHA通过使用MHA Manager实现主从切换，确保数据库服务的高可用性。" 在MySQL环境中，为了保障数据服务的持续性和稳定性，高可用架构(High Availability, HA)是非常重要的。MHA(MySQL High ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值