MHA高可用数据集群（Master High Availability）

一、概述

二、原理

三、安装部署

一、概述

高可用数据集群（mariadb）由日本人研发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在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。整个故障转移过程对应用程序完全透明。

三、安装部署

1.环境准备四台服务器全部关闭防火墙

[root@node1 ~] setenforce 0
[root@node1 ~] systemctl stop firewalld

2.添加主机声明（四台主机都添加）

[root@node1 ~] vim /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6192.168.1.120 node1
192.168.1.121 node2
192.168.1.122 node3
192.168.1.12 node4

3.修改四台服务器的名字

[root@localhost ~] hostnamectl set-hostname node2
[root@localhost ~] bash

4.四台服务器-免密配置-生成密钥

[root@node1 ~] 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:ErAooqCLRt/9EiBhsZneOVHTpAreVm/ZrPIeeExTeGw root@node1
The key's randomart image is:
+---[RSA 2048]----+
|   .o  oo.       |
|   ++o. o.o      |
|+ o=o..o . E     |
|=.o.+.=.. B      |
|o. o.Bo S* o     |
|o.. o oo= o      |
|o. . . +.=       |
|.      .= .      |
|        o+       |
+----[SHA256]-----+

5.四台服务器配置免密设置-复制密钥，然后查看获得的四把密钥

[root@node1 ~] ssh-copy-id -i .ssh/id_rsa.pub root@node1
[root@node2 ~] ssh-copy-id -i .ssh/id_rsa.pub root@node1
[root@node3 ~] ssh-copy-id -i .ssh/id_rsa.pub root@node1
[root@node4 ~] ssh-copy-id -i .ssh/id_rsa.pub root@node1
[root@node1 ~] cat /root/.ssh/authorized_keys
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDiCNWmT9KvNA1Hw6oJ64eHDvBGZyxgkKzzGU80RcvqJ4bzzB7LQ4MDIWbxF4t2g20NGCtpNmUz0mlx6vl5hUzT/sxPIuMkZwCOc+edEh8uW8p4zjXhdQ4Xs1QKeIn4AnHXiX/VwHw2aJnPV654jXpOSNivn+FZogQbT4mHfRl8ks8kghA7NDDbWlyqI1viTsgz1A3fSI6oqLIZsBUvfPFFd40W3YsoqhdZs1fL222htCy//SgnMNIVBDkExVpVFVgMtAlL4vQ2cQ6LRJZfdKXqaTlikA7Of9U14/00Y75RbDozFZUMy09ofGYuEBXCfZJMIkNCE2GWf4G0qoKHntad root@node1root@node2root@node3root@node4

6.将authorized_keysau文件发送给node2/3/4

[root@node1 ~] scp .ssh/authorized_keys root@node2:.ssh/
The authenticity of host 'node2 (192.168.1.121)' can't be established.
ECDSA key fingerprint is SHA256:Vnn5SvqAXIMK33uJ9f798YPxFbNITXupp/fy79HcP9A.
ECDSA key fingerprint is MD5:a4:52:6b:6f:2e:38:b6:5f:ee:8b:a2:02:00:e8:81:22.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'node2,192.168.1.121' (ECDSA) to the list of known hosts.
root@node2's password: 
authorized_keys                                        100% 1568     1.4MB/s   00:00    [root@node1 ~] scp .ssh/authorized_keys root@node3:.ssh/
[root@node1 ~] scp .ssh/authorized_keys root@node4:.ssh/

7.测试是否免密登录

[root@node1 ~] ssh node2
Last login: Wed Jun 14 18:00:24 2023 from 192.168.1.118
[root@node2 ~] ssh node3
The authenticity of host 'node3 (192.168.1.122)' can't be established.
ECDSA key fingerprint is SHA256:Vnn5SvqAXIMK33uJ9f798YPxFbNITXupp/fy79HcP9A.
ECDSA key fingerprint is MD5:a4:52:6b:6f:2e:38:b6:5f:ee:8b:a2:02:00:e8:81:22.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'node3,192.168.1.122' (ECDSA) to the list of known hosts.
Last login: Wed Jun 14 18:00:32 2023 from 192.168.1.118
[root@node3 ~] ssh node4

8.master操作-主从复制配置

[root@node2 ~] yum -y install epel-release
[root@node2 ~] yum install -y perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-DBI perl-DBD-MySQL perl-Net-OpenSSH perl-JSON
[root@node2 ~] yum -y install mariadb mariadb-server
[root@node2 ~] rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm [root@node2 ~] vim /etc/my.cnfserver-id = 1          #[mysqld下]log-bin = master-logrelay-log = relay-logskip_name_resolve
[root@node2 ~] systemctl restart mariadb

mysql命令进入后

[root@node2 ~] mysql#授权用户给从库使用
MariaDB [(none)]> grant replication slave on *.* to ‘slave’@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)#授权用户给manager管理master使用
MariaDB [(none)]>  grant all on *.* to ‘mha’@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 |      517 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)MariaDB [(none)]>

9.进入node3服务器（slave1）

[root@node3 ~] yum -y install mariadb mariadb-server epel-release
[root@node3 ~] rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm 
[root@node3 ~] vim /etc/my.cnf
server-id = 2                
relay-log = relay-log         
log-bin = master-log         
read_only = ON               
relay_log_purge = 0           
skip_name_resolve              
log_slave_updates = 1
[root@node3 ~] systemctl restart mariadb.service
[root@node3 ~] mysqlMariaDB [(none)]> change master to master_host='192.168.1.122',master_user='slave',master_password='123',master_log_file='master-log.000003',master_log_pos=509;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show slave status \G;Slave_IO_Running: YesSlave_SQL_Running: Yes#授权用户给manager管理slave使用
MariaDB [(none)]> grant all on *.* to ‘slave’@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

10.进入node4（slave2）

[root@node4 ~] yum -y install mariadb mariadb-server epel-release
[root@node4 ~] rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm 
[root@node4~] vim /etc/my.cnf
server-id = 3               
relay-log = relay-log         
log-bin = master-log         
read_only = ON               
relay_log_purge = 0           
skip_name_resolve              
log_slave_updates = 1[root@node4 ~] mysqlMariaDB [(none)]> change master to master_host='192.168.174.131',master_user='slave',master_password='123',master_log_file='master-log.000003',master_log_pos=529;
Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> show slave status \G;#授权用户给manager管理slave使用
MariaDB [(none)]> grant all on *.* to ‘slave’@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

11. node1服务器（manager）操作

[root@node1 ~] yum -y install mariadb mariadb-server epel-release
[root@node1 ~] yum -y install mha4mysql-node-0.56-0.el6.noarch.rpm mha4mysql-manager-0.56-0.el6.noarch.rpm [root@node1 ~] mkdir /etc/mha_master[root@node1 ~] vim /etc/mha_master/mha.cnf
[server default]
user=mha
password=123
manager_workdir=/etc/mha_master/app1
manager_log=/etc/mha_master/manager.log
remote_workdir=/mydata/mha_master/app1
ssh_user=root
repl_user=slave
repl_password=123
ping_interval=1
[server1]
hostname=192.168.174.131
ssh_port=22
candidate_master=1
[server2]
hostname=192.168.174.132
ssh_port=22
candidate_master=1
[server3]
hostname=192.168.174.133
ssh_port=22
candidate_master=1

12.对各个节点进行检测

[root@node1 ta] masterha_check_ssh --conf=/etc/mha_master/mha.cnf
2:22)..
Thu Jun 15 16:46:20 2023 - [debug]   ok.
Thu Jun 15 16:46:20 2023 - [debug]  Connecting via SSH from root@192.168.1.12(192.168.1.12:22) to root@192.168.1.133(192.168.1.133:22)..
Thu Jun 15 16:46:20 2023 - [debug]   ok.
Thu Jun 15 16:46:21 2023 - [info] All SSH connection tests passed successfully.
#显示成功[root@node1 ta] masterha_check_repl --conf=/etc/mha_master/mha.cnf
Thu Jun 15 16:47:21 2023 - [warning] master_ip_failover_script is not defined.
Thu Jun 15 16:47:21 2023 - [warning] shutdown_script is not defined.
Thu Jun 15 16:47:21 2023 - [info] Got exit code 0 (Not master dead).MySQL Replication Health is OK.#ok 完成