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 ######################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################################## [%] 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 ***************************
File: mysql-bin.000002
Position: 891
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
从1
mysql> grant replication slave on *.* to 'mha'@'192.168.217.%' identified by '1234.Azx';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on *.* to 'mha'@'192.168.217.%' identified by '1234.Azx';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.217.11',master_user='mha',master_password='1234.Asd',master_log_file='mysql-bin.000002',master_log_pos=891;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.217.11
Master_User: mha
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 891
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
从2
mysql> grant replication slave on *.* to 'mha'@'192.168.217.%' identified by '1234.Aqw';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all on *.* to 'mha'@'192.168.217.%' identified by '1234.Aqw';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.217.11',master_user='mha',master_password='1234.Asd',master_log_file='mysql-bin.000002',master_log_pos=891;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.217.11
Master_User: mha
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 891
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
MHA
[root@localhost ~]# mkdir /etc/mha
[root@localhost ~]# vim /etc/mha/mha.cnf 添加以下内容
[server default]
user=mha
password=1234.Asd
ssh_user=root
repl_user=mha
repl_password=1234.Asd
ping_interval=1
master_ip_failover_script=/usr/bin/master_ip_failover
manager_workdir=/var/mha
manager_log=/var/mha/manager.log
[server1]
hostname=192.168.217.11 #mysql主库ip
ssh_port=22
master_binlog_dir=/var/lib/mysql
[server2]
hostname=192.168.217.12 #mysql从1ip
ssh_port=22
candidate_master=1 #允许从为主库
master_binlog_dir=/var/lib/mysql
[server3]
hostname=192.168.217.13 #mysql从2 ip
ssh_port=22
no_master=1 #不允许为主库
master_binlog_dir=/var/lib/mysql
[root@localhost ~]# vim /usr/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.217.252/24'; #漂移IP地址 同网段
my $key = "1";
my $ssh_start_vip = "/usr/sbin/ifconfig ens33:$key $vip"; #网卡名为本主机网卡名
my $ssh_stop_vip = "/usr/sbin/ifconfig ens33:$key down"; #网卡名为本主机网卡名
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" ) {