mysql高可用

mysql高可用
高可用的方法有MHA 、MMM、NGR三种。本文主要实验为MHA为例。
实验环境
CentOS Linux release 7.6.1810 (Core)
mysql-5.7.37-1.el7.x86_64

三台mysql、一台MHA
主机 IP：192.168.217.11
从机1IP ：192.168.217.12
从机2IP： 192.168.217.13
HMA IP： 192.168.217.14

MHA（master high avaliability）是一套优秀的mysql高可用环境下故障切换和主从复制的软件，mysq故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作。MHA能在故障切换的过程中最大程度上保证数据的一致性，一道道真正意义上的高可用。

MHA工作原理
1 从宕机崩溃的master保存二进制日志事件（binlog events）
2 识别含有最新更新的slave
3 应用差异的中继日志（relay log）到其他的slave
4 应用从master保存的二进制日志事件（binlog events）
5 提升一个slave为新的master
6 使其他的slave连接新的master进行复制

准备安装包

mha4mysql-node-0.56-0.el6.noarch.rpm
mha4mysql-manager-0.56-0.el6.noarch.rpm
mysql-community-client-5.7.37-1.el7.x86_64.rpm
mysql-community-common-5.7.37-1.el7.x86_64.rpm
mysql-community-libs-5.7.37-1.el7.x86_64.rpm
mysql-community-server-5.7.37-1.el7.x86_64.rpm

三台mysql安装

mha4mysql-node-0.56-0.el6.noarch.rpm

MHA 安装

mha4mysql-node-0.56-0.el6.noarch.rpm
mha4mysql-manager-0.56-0.el6.noarch.rpm

为了实验顺利进行4台设备关闭防火墙

systemctl stop firewalld
netenforce 0

更新yum

wget -O /etc/yum.repos.d/CentOS-Base.repo
http://mirrors.aliyun.com/repo/Centos-7.repo wget -O
/etc/yum.repos.d/epel-7.repo
http://mirrors.aliyun.com/repo/epel-7.repo

主1 从1 从2

安装mha   客户端node节点
[root@localhost ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
错误：依赖检测失败：
	perl(DBD::mysql) 被 mha4mysql-node-0.56-0.el6.noarch 需要
	perl(DBI) 被 mha4mysql-node-0.56-0.el6.noarch 需要
安装依赖	
[root@localhost ~]# yum -y install perl-DBD-mysql perl-DBI  
[root@localhost ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
准备中...                          ################################# [100%]
正在升级/安装...
   1:mha4mysql-node-0.56-0.el6     ################################# [100%]

MHA
安装mha

安装依赖
[root@localhost ~]# yum -y install perl-DBD-mysql perl-DBI perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
[root@localhost ~]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
准备中...                          ################################# [100%]
正在升级/安装...
   1:mha4mysql-node-0.56-0.el6        ################################# [100%]
[root@localhost ~]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
准备中...                          ################################# [100%]
正在升级/安装...
   1:mha4mysql-manager-0.56-0.el6     ################################# [100%]

主1、 从1 、从2、MHA
设置免密登录
4台设备全部操作免密登录 ， 同样操作每台设备操作一次

[root@localhost ~]# ssh-keygen       #回车4次
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:4yhK/ul6PUymTnOIc7FLM85bgIRsf9o6GDXc/nGuwNU root@localhost.localdomain
The key's randomart image is:
+---[RSA 2048]----+
|                 |
|..               |
|.oo .            |
|...= .  .        |
|  o.=. .SE       |
| . .=*++ o       |
|  *.@X= =        |
| + X+X=. .       |
|  +B@. o.        |
+----[SHA256]-----+
ssh-copy-id root@192.168.217.12       #此处ip改其余三台主机   
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"    
The authenticity of host '192.168.217.12 (192.168.217.12)' can't be established.
ECDSA key fingerprint is SHA256:/Dmpz2tJIrSn3PbaMSpDLfHTKrpAGIYGOEAzou708rc.
ECDSA key fingerprint is MD5:f4:03:dc:66:88:b1:42:0d:16:a3:d9:30:ab:86:d2:1c.
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.217.12's password:      #输入root  登录密码

Number of key(s) added: 1

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

免密登录验证

[root@localhost ~]# ssh root@192.168.217.14
Last login: Thu May 19 07:16:20 2022 from 192.168.217.50
[root@localhost ~]# exit
登出
Connection to 192.168.217.14 closed.
[root@localhost ~]# ssh root@192.168.217.13
Last login: Wed May 18 23:35:03 2022 from 192.168.217.50
[root@localhost ~]# exit
登出
Connection to 192.168.217.13 closed.

主、从1、从2

安装MySQL
卸载nodeps mariadb-libs
[root@localhost ~]#  rpm -e --nodeps mariadb-libs

[root@localhost ~]# rpm -ivh mysql-community-common-5.7.37-1.el7.x86_64.rpm
警告：mysql-community-common-5.7.37-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-common-5.7.37-1.e################################# [100%]
[root@localhost ~]# rpm -ivh mysql-community-libs-5.7.37-1.el7.x86_64.rpm
警告：mysql-community-libs-5.7.37-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-libs-5.7.37-1.el7################################# [100%]
[root@localhost ~]# rpm -ivh mysql-community-client-5.7.37-1.el7.x86_64.rpm
警告：mysql-community-client-5.7.37-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-client-5.7.37-1.e################################# [100%]
[root@localhost ~]# rpm -ivh mysql-community-server-5.7.37-1.el7.x86_64.rpm
警告：mysql-community-server-5.7.37-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-server-5.7.37-1.e################################# [100%]
[root@localhost ~]# systemctl start mysqld
查看初始密码
[root@localhost ~]# cat /var/log/mysqld.log | grep password
2022-05-18T22:58:28.605893Z 1 [Note] A temporary password is generated for root@localhost: **)=Juxuadl9m2** #密码
[root@localhost ~]# mysql -uroot -p
Enter password: 
修改MySQL密码
mysql> set password=password('1234.Asd');

启动MySQL

[root@localhost ~]# systemctl start mysqld

主
修改MySQL配置文件

[root@localhost ~]# vim /etc/my.cnf
server-id=1
log-bin=mysql-bin    # 开启二进制日志

从1

server-id=2
log-bin=mysql-bin    #开启二进制日志
relay-log=relay-log-bin   #中继日志
relay-log-purge=0  #防止从变成主删除中继日志

从2

server-id=3
log-bin=mysql-bin    #开启二进制日志
relay-log=relay-log-bin   #中继日志
relay-log-purge=0   #防止从变成主删除中继日志

重启MySQL

[root@localhost ~]# systemctl restart mysqld

主从复制 mha授权
主

mysql> grant replication slave on *.* to 'mha'@'192.168.217.%' identified by '1234.Asd';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> show master status/G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/G' at line 1
mysql> show master status\G;
*************************** 1. row *******</