热门标签
热门文章
- 1华为c8817e怎么添加显示语言,华为C8817E卡刷教程详解
- 2基于Netty和SpringBoot实现一个轻量级RPC框架-Client端请求响应同步化处理_spring netty client
- 3Vite与Webpack打包内存溢出问题优雅处理方式_vite 打包内存溢出
- 4统计学习实战--决策树模型处理分类和回归问题C4.5，CART，RF（随机森林），XGBoost，LGBM_随机森林是lgbm么
- 5《安富莱嵌入式周报》第329期：圣诞前夕，各种软件井喷式更新，开源600Wh的UPS低压电源，各种插件类型介绍，ADI推出的六位半电压测量模块，手势音频调节_admx3652 cdsn
- 6springboot集成安全框架springsecurity
- 7从零开始创建Hi3516DV300在EMMC上的根文件系统_海思hi3516dv300 文件系统打包流程
- 8ubuntu16.04安装python3.6最新 踩坑无数的经验
- 9python判断素数（质数）：for-else循环的理解与示例应用_python使用for 或者for...else检查质数
- 10GateUpdate楚楚留香网关数据记录分析_bbe8b397ea7378665d19fb8fba646ba6ab23d248
当前位置: article > 正文
mysql高可用
作者：你好赵伟 | 2024-04-08 22:21:44
赞
踩
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%]
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
主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.
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
免密登录验证
[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
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
主、从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');
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
启动MySQL
[root@localhost ~]# systemctl start mysqld
- 1
主
修改MySQL配置文件
[root@localhost ~]# vim /etc/my.cnf
server-id=1
log-bin=mysql-bin # 开启二进制日志
- 1
- 2
- 3
从1
server-id=2
log-bin=mysql-bin #开启二进制日志
relay-log=relay-log-bin #中继日志
relay-log-purge=0 #防止从变成主删除中继日志
- 1
- 2
- 3
- 4
从2
server-id=3
log-bin=mysql-bin #开启二进制日志
relay-log=relay-log-bin #中继日志
relay-log-purge=0 #防止从变成主删除中继日志
- 1
- 2
- 3
- 4
重启MySQL
[root@localhost ~]# systemctl restart mysqld
- 1
主从复制 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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
从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:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
从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:
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
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"; }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
#赋予可执行权限
[root@localhost ~]# chmod a+x /usr/bin/master_ip_failover
- 1
检测节点之间的主从复制是否正常连接 （出现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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
检测节点之间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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
主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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
从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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
主 关闭主库 制造故障
[root@localhost ~]# systemctl stop mysql
- 1
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..
- 1
- 2
- 3
- 4
[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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
从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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
从1（新的master） 从2 查看日志 以下内容属于正常情况
从1
mysql> show slave status\G;
Empty set (0.00 sec)
ERROR:
No query specified
- 1
- 2
- 3
- 4
- 5
从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
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
验证
从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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
从2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hanhan |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.06 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
从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)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
从2
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
总结：本实验需要先安装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!
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
此错误通过检查[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
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/你好赵伟/article/detail/388584?site
推荐阅读
相关标签
