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 ***************************
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" ) {
print "Checking the Status of the script.. OK \n";
`ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
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";
}
#赋予可执行权限
[root@localhost ~]# chmod a+x /usr/bin/master_ip_failover
检测节点之间的主从复制是否正常连接 （出现ok表示无异常）
[root@localhost ~]# masterha_check_ssh --conf=/etc/mha/mha.cnf
Thu May 19 18:13:35 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May 19 18:13:35 2022 - [info] Reading application default configuration from /etc/mha/mha.cnf..
Thu May 19 18:13:35 2022 - [info] Reading server configuration from /etc/mha/mha.cnf..
Thu May 19 18:13:35 2022 - [info] Starting SSH connection tests..
Thu May 19 18:13:38 2022 - [debug]
Thu May 19 18:13:35 2022 - [debug] Connecting via SSH from root@192.168.217.11(192.168.217.11:22) to root@192.168.217.12(192.168.217.12:22)..
Thu May 19 18:13:36 2022 - [debug] ok.
Thu May 19 18:13:36 2022 - [debug] Connecting via SSH from root@192.168.217.11(192.168.217.11:22) to root@192.168.217.13(192.168.217.13:22)..
Thu May 19 18:13:38 2022 - [debug] ok.
Thu May 19 18:13:39 2022 - [debug]
Thu May 19 18:13:36 2022 - [debug] Connecting via SSH from root@192.168.217.12(192.168.217.12:22) to root@192.168.217.11(192.168.217.11:22)..
Thu May 19 18:13:37 2022 - [debug] ok.
Thu May 19 18:13:37 2022 - [debug] Connecting via SSH from root@192.168.217.12(192.168.217.12:22) to root@192.168.217.13(192.168.217.13:22)..
Thu May 19 18:13:38 2022 - [debug] ok.
Thu May 19 18:13:41 2022 - [debug]
Thu May 19 18:13:36 2022 - [debug] Connecting via SSH from root@192.168.217.13(192.168.217.13:22) to root@192.168.217.11(192.168.217.11:22)..
Thu May 19 18:13:37 2022 - [debug] ok.
Thu May 19 18:13:37 2022 - [debug] Connecting via SSH from root@192.168.217.13(192.168.217.13:22) to root@192.168.217.12(192.168.217.12:22)..
Thu May 19 18:13:40 2022 - [debug] ok.
Thu May 19 18:13:41 2022 - [info] All SSH connection tests passed successfully
检测节点之间ssh是否正常连接（出现MySQL Replication Health is OK. 表示成功）
root@localhost ~]# masterha_check_repl --conf=/etc/mha/mha.cnf
Thu May 19 18:14:37 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May 19 18:14:37 2022 - [info] Reading application default configuration from /etc/mha/mha.cnf..
Thu May 19 18:14:37 2022 - [info] Reading server configuration from /etc/mha/mha.cnf..
Thu May 19 18:14:37 2022 - [info] MHA::MasterMonitor version 0.56.
Thu May 19 18:14:38 2022 - [info] GTID failover mode = 0
Thu May 19 18:14:38 2022 - [info] Dead Servers:
Thu May 19 18:14:38 2022 - [info] Alive Servers:
Thu May 19 18:14:38 2022 - [info] 192.168.217.11(192.168.217.11:3306)
Thu May 19 18:14:38 2022 - [info] 192.168.217.12(192.168.217.12:3306)
Thu May 19 18:14:38 2022 - [info] 192.168.217.13(192.168.217.13:3306)
Thu May 19 18:14:38 2022 - [info] Alive Slaves:
Thu May 19 18:14:38 2022 - [info] 192.168.217.12(192.168.217.12:3306) Version=5.7.37-log (oldest major version between slaves) log-bin:enabled
Thu May 19 18:14:38 2022 - [info] Replicating from 192.168.217.11(192.168.217.11:3306)
Thu May 19 18:14:38 2022 - [info] Primary candidate for the new Master (candidate_master is set)
Thu May 19 18:14:38 2022 - [info] 192.168.217.13(192.168.217.13:3306) Version=5.7.37-log (oldest major version between slaves) log-bin:enabled
Thu May 19 18:14:38 2022 - [info] Replicating from 192.168.217.11(192.168.217.11:3306)
Thu May 19 18:14:38 2022 - [info] Not candidate for the new Master (no_master is set)
Thu May 19 18:14:38 2022 - [info] Current Alive Master: 192.168.217.11(192.168.217.11:3306)
Thu May 19 18:14:38 2022 - [info] Checking slave configurations..
Thu May 19 18:14:38 2022 - [info] Checking replication filtering settings..
Thu May 19 18:14:38 2022 - [info] binlog_do_db= , binlog_ignore_db=
Thu May 19 18:14:38 2022 - [info] Replication filtering check ok.
Thu May 19 18:14:38 2022 - [info] GTID (with auto-pos) is not supported
Thu May 19 18:14:38 2022 - [info] Starting SSH connection tests..
Thu May 19 18:14:41 2022 - [info] All SSH connection tests passed successfully.
Thu May 19 18:14:41 2022 - [info] Checking MHA Node version..
Thu May 19 18:14:42 2022 - [info] Version check ok.
Thu May 19 18:14:42 2022 - [info] Checking SSH publickey authentication settings on the current master..
Thu May 19 18:14:43 2022 - [info] HealthCheck: SSH to 192.168.217.11 is reachable.
Thu May 19 18:14:43 2022 - [info] Master MHA Node version is 0.56.
Thu May 19 18:14:43 2022 - [info] Checking recovery script configurations on 192.168.217.11(192.168.217.11:3306)..
Thu May 19 18:14:43 2022 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/var/tmp/save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000002
Thu May 19 18:14:43 2022 - [info] Connecting to root@192.168.217.11(192.168.217.11:22)..
Creating /var/tmp if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /var/lib/mysql, up to mysql-bin.000002
Thu May 19 18:14:44 2022 - [info] Binlog setting check done.
Thu May 19 18:14:44 2022 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Thu May 19 18:14:44 2022 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.217.12 --slave_ip=192.168.217.12 --slave_port=3306 --workdir=/var/tmp --target_version=5.7.37-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Thu May 19 18:14:44 2022 - [info] Connecting to root@192.168.217.12(192.168.217.12:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to relay-log-bin.000002
Temporary relay log file is /var/lib/mysql/relay-log-bin.000002
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 May 19 18:14:45 2022 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mha' --slave_host=192.168.217.13 --slave_ip=192.168.217.13 --slave_port=3306 --workdir=/var/tmp --target_version=5.7.37-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx
Thu May 19 18:14:45 2022 - [info] Connecting to root@192.168.217.13(192.168.217.13:22)..
Checking slave recovery environment settings..
Opening /var/lib/mysql/relay-log.info ... ok.
Relay log found at /var/lib/mysql, up to relay-log-bin.000002
Temporary relay log file is /var/lib/mysql/relay-log-bin.000002
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 May 19 18:14:45 2022 - [info] Slaves settings check done.
Thu May 19 18:14:45 2022 - [info]
192.168.217.11(192.168.217.11:3306) (current master)
+--192.168.217.12(192.168.217.12:3306)
+--192.168.217.13(192.168.217.13:3306)
Thu May 19 18:14:45 2022 - [info] Checking replication health on 192.168.217.12..
Thu May 19 18:14:45 2022 - [info] ok.
Thu May 19 18:14:45 2022 - [info] Checking replication health on 192.168.217.13..
Thu May 19 18:14:45 2022 - [info] ok.
Thu May 19 18:14:45 2022 - [info] Checking master_ip_failover_script status:
Thu May 19 18:14:45 2022 - [info] /usr/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.217.11 --orig_master_ip=192.168.217.11 --orig_master_port=3306
IN SCRIPT TEST====/usr/sbin/ifconfig ens33:1 down==/usr/sbin/ifconfig ens33:1 192.168.217.252/24===
Checking the Status of the script.. OK
Thu May 19 18:14:46 2022 - [info] OK.
Thu May 19 18:14:46 2022 - [warning] shutdown_script is not defined.
Thu May 19 18:14:46 2022 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
主1 查看漂移地址
[root@localhost ~]# ip addr
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:c2:15:cf brd ff:ff:ff:ff:ff:ff
inet 192.168.217.11/24 brd 192.168.217.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.217.252/24 brd 192.168.217.255 scope global secondary ens33:1 #漂移地址在此处
valid_lft forever preferred_lft forever
inet6 fe80::1e6f:d3ee:5554:1f34/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::ac8:77ad:9154:7983/64 scope link noprefixroute
valid_lft forever preferred_lft forever
从1 验证漂移地址
[root@localhost ~]# mysql -umha -p1234.Asd -h192.168.217.11
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 8
Server version: 5.7.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> exit
Bye
未登陆成功
[root@localhost ~]# mysql -umha -p1234.Asd -h192.168.217.252
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.217.252' (113)
[root@localhost ~]# mysql -uroot -p1234.Azx
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 14
Server version: 5.7.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> set GLOBAL read_only=1;
Query OK, 0 rows affected (0.00 sec)
在次验证成功
[root@localhost ~]# mysql -umha -p1234.Asd -h192.168.217.252
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 22
Server version: 5.7.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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> exit
主 关闭主库 制造故障
[root@localhost ~]# systemctl stop mysql
MHA
启动服务会阻塞终端
[root@localhost ~]# masterha_manager --conf=/etc/mha/mha.cnf
Thu May 19 18:24:16 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May 19 18:24:16 2022 - [info] Reading application default configuration from /etc/mha/mha.cnf..
Thu May 19 18:24:16 2022 - [info] Reading server configuration from /etc/mha/mha.cnf..
[root@localhost ~]# masterha_check_ssh --conf=/etc/mha/mha.cnf
Thu May 19 18:44:56 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May 19 18:44:56 2022 - [info] Reading application default configuration from /etc/mha/mha.cnf..
Thu May 19 18:44:56 2022 - [info] Reading server configuration from /etc/mha/mha.cnf..
Thu May 19 18:44:56 2022 - [info] Starting SSH connection tests..
Thu May 19 18:44:58 2022 - [debug]
Thu May 19 18:44:56 2022 - [debug] Connecting via SSH from root@192.168.217.11(192.168.217.11:22) to root@192.168.217.12(192.168.217.12:22)..
Thu May 19 18:44:57 2022 - [debug] ok.
Thu May 19 18:44:57 2022 - [debug] Connecting via SSH from root@192.168.217.11(192.168.217.11:22) to root@192.168.217.13(192.168.217.13:22)..
Thu May 19 18:44:58 2022 - [debug] ok.
Thu May 19 18:44:59 2022 - [debug]
Thu May 19 18:44:56 2022 - [debug] Connecting via SSH from root@192.168.217.12(192.168.217.12:22) to root@192.168.217.11(192.168.217.11:22)..
Thu May 19 18:44:57 2022 - [debug] ok.
Thu May 19 18:44:57 2022 - [debug] Connecting via SSH from root@192.168.217.12(192.168.217.12:22) to root@192.168.217.13(192.168.217.13:22)..
Thu May 19 18:44:59 2022 - [debug] ok.
Thu May 19 18:45:00 2022 - [debug]
Thu May 19 18:44:57 2022 - [debug] Connecting via SSH from root@192.168.217.13(192.168.217.13:22) to root@192.168.217.11(192.168.217.11:22)..
Thu May 19 18:44:58 2022 - [debug] ok.
Thu May 19 18:44:58 2022 - [debug] Connecting via SSH from root@192.168.217.13(192.168.217.13:22) to root@192.168.217.12(192.168.217.12:22)..
Thu May 19 18:44:59 2022 - [debug] ok.
Thu May 19 18:45:00 2022 - [info] All SSH connection tests passed successfully.
[root@localhost ~]# masterha_manager --conf=/etc/mha/mha.cnf
Thu May 19 18:48:36 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May 19 18:48:36 2022 - [info] Reading application default configuration from /etc/mha/mha.cnf..
Thu May 19 18:48:36 2022 - [info] Reading server configuration from /etc/mha/mha.cnf..
Creating /var/tmp if not exists.. ok.
Checking output directory is accessible or not..
ok. #此处显示主库已经切换到从库 从库可变成主库
Binlog found at /var/lib/mysql, up to mysql-bin.000002
Thu May 19 18:48:58 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu May 19 18:48:58 2022 - [info] Reading application default configuration from /etc/mha/mha.cnf..
Thu May 19 18:48:58 2022 - [info] Reading server configuration from /etc/mha/mha.cnf..
从1
查看从1 的网卡IP （此处查看需要一点时间，才能切换过来）
[root@localhost ~]# ip addr
ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:49:b3:a1 brd ff:ff:ff:ff:ff:ff
inet 192.168.217.12/24 brd 192.168.217.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.217.252/24 brd 192.168.217.255 scope global secondary ens33:1 #漂移IP已切换到从机
valid_lft forever preferred_lft forever
inet6 fe80::1e6f:d3ee:5554:1f34/64 scope link noprefixroute
valid_lft forever preferred_lft forever
从1（新的master） 从2 查看日志 以下内容属于正常情况
从1
mysql> show slave status\G;
Empty set (0.00 sec)
ERROR:
No query specified
从2
[root@localhost ~]# mysql -umha -p1234.Asd -h192.168.217.252
mysql> show slave status\G;
Empty set (0.00 sec)
ERROR:
No query specified
mysql> exit
[root@localhost ~]# mysql -uroot -p1234.Aqw
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.217.12 #新主库 已经是从1 ip
Master_User: mha
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: relay-log-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
验证
从1（新master）
[root@localhost ~]# mysql -uroot -p1234.Azx
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.29 sec)
mysql> create database hanhan;
Query OK, 1 row affected (0.33 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hanhan |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
从2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hanhan |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.06 sec)
从1（新master）
mysql> drop database hanhan;
Query OK, 0 rows affected (0.05 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
从2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
总结：本实验需要先安装MHA,后安装mysql，否责容易出错，
检测节点之间ssh是否正常连接 （此处容易报错 ）
[root@localhost ~]# masterha_check_repl --conf=/etc/mha/mha.cnf
……
Thu May 19 17:27:56 2022 - [info] /usr/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.217.11 --orig_master_ip=192.168.217.11 --orig_master_port=3306
Subroutine main redefined at /usr/bin/master_ip_failover line 62.
Thu May 19 17:27:56 2022 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln226] Failed to get master_ip_failover_script status with return code 255:0.
Thu May 19 17:27:56 2022 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations. at /usr/bin/masterha_check_repl line 48.
Thu May 19 17:27:56 2022 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Thu May 19 17:27:56 2022 - [info] Got exit code 1 (Not master dead).
MySQL Replication Health is NOT OK!
此错误通过检查[root@localhost ~]# vim /usr/bin/master_ip_failover 此配置文件解决
参考文章
https://blog.csdn.net/wzt888_/article/details/81639753?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522165283913216780357237067%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=165283913216780357237067&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2allsobaiduend~default-1-81639753-null-null.142v10pc_search_result_control_group,157v4control&utm_term=mha+mysql+%E9%AB%98%E5%8F%AF%E7%94%A8&spm=1018.2226.3001.4187
1891
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
