MHA

2020 年 12 月 9 日
筆記
MHA集群配置

一、項目規劃

主機 ip

主庫(master) 192.168.2.56

從庫(slave1) 192.168.2.58

從庫(slave2) 192.168.2.59

虛擬VIP(vrrp漂移) 192.168.2.60

二、前期準備

a.系統Centos Liunx release

b.IP節點以上述為準

c.三台MySql版本為 5.7

d.MySql鏈接地址 MYSQL 5.7

三、在三台服務器執行操作

修改主機名稱(主)

[root@ c7m01]# vi /etc/hostname
c7m01
修改主機名(從)

[root@ c702]# vim /etc/hostname
c702

修改主機名(從)

[root@ c703]# vim /etc/hostname
c703
綜合上述：修改主機名

時間同步（3台機器）

[root@ c7m01]# echo “/5 * /usr/sbin/ntpdate ntp1.aliyun.com >/dev/null 2>&1” >>/var/spool/cron/root

修改完成，重啟機器就可以

[root@c7m01]# systemctl restart mysqld

四、三台服務器配置hosts解析（主機名稱和自己一樣）

[root@ c7m01]# vim /etc/hostsEOF

c7m01 192.168.2.56
c702 192.168.2.57
c703 192.168.2.58

五、關閉防火牆和selinux(三台機器執行同樣的操作)

systemctl stop firewalld
systemctl disable firewalld
setenforce 0
[root@ c7m01 ~]# sed -i ‘ /^SELINUX/s#enforcing#disabled#g’ /etc/selinux/config

六、SSH互信免密登錄

三台服務器做SSH互信

192.168.2.56

192.168.2.57

192.168.2.58

A.在c7m01服務器上執行

ssh-keygen -t rsa
一路默認回車，系統在/root/.ssh下生成id_rsa、id_rsa.pub

[root@C7m01]# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
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:A1sPI0++eebjaumP8TlwgZZUGG1jsHahWpvHJkhGGUY root@host101
The key’s randomart image is:
+—[RSA 2048]—-+
| .Eooo |
| o. oo=. |
| =.X=.. |
| o &+O. |
| +.S =. |
| .B. |
| ++o |
| oBo. |
| o++o |
+—-[SHA256]—–+

B.查看生成的公鑰秘鑰對

命令:ls /root/.ssh

[root@c7m01]# ls /root/.ssh
id_rsa id_rsa.pub known_hosts

C.將生成的公鑰私鑰對id_rsa.pub發送到其他的服務器上

命令: ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.57

[root@c7m01]# ssh-copy-id -i /root/.ssh/id_rsa.pub 192.168.2.57
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: “/root/.ssh/id_rsa.pub”
/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
[email protected]’s password: —此處需要輸入57服務器的密碼

Number of key(s) added: 1

Now try logging into the machine, with: “ssh ‘192.168.2.57’”
and check to make sure that only the key(s) you wanted were added.

可以看到成功將公鑰私鑰對發送到了其他服務器

ssh 192.168.2.57 根據提示信息，只要執行這個命令就可以連接到57服務器了

D.現在可以測試連接其他的服務器的是否不需要密碼就能登錄，可以看到確實成功了。

[root@c7m01]ssh 192.168.2.57
Last login: Mon Oct 19 09:16:59 2020 from gateway
[root@c702]# exit
logout
Connection to 192.168.150.102 closed
E.ssh 192.168.2.56 測試

[root@c702]# ssh 192.168.2.56
Last login: Tue Dec 8 15:26:35 2020 from 192.168.2.15 #無密碼登錄
[root@c7m01]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.2.56 netmask 255.255.255.0 broadcast 192.168.2.255 #查看ifconfig變化
inet6 fe80::1ded:1203:fad9:ae6 prefixlen 64 scopeid 0x20
ether 00:0c:29:3b:a8:c3 txqueuelen 1000 (Ethernet)
RX packets 14379 bytes 1590236 (1.5 MiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 7012 bytes 677052 (661.1 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10
loop txqueuelen 1 (Local Loopback)
RX packets 280 bytes 23849 (23.2 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 280 bytes 23849 (23.2 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

[root@c7m01 ~]# exit #在192.168.2.57 一定記得退出上述操作查看ifconfig

七、MySql環境搭建

安裝MYSQL5.7的Yum Repository

wget -i -c //dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql-community-server

2.啟動MySQL，三台服務器執行同樣的操作

[root@ c7m01]# systemctl restart mysql

八.修改登錄(免密登錄之前)

修改登錄密碼涉及到一個版本BUG

本意向修改一個用戶的密碼，命令如下

< mysql> update user set password=password(「新密碼」) where user=」用戶名」;

執行後報錯　ERROR 1054(42S22) Unknown column ‘password’ in 『field list』

錯誤的原因是 5.7版本下的mysql數據庫下已經沒有password這個字段了，password字段改成了authentication_string

所以請使用一下命令：

mysql -u root -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.18-log MySQL Community Server (GPL)

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> update user set password=password(““) where user=”“; #修改密碼報錯 **是指自己的密碼
ERROR 1054 (42S22): Unknown column ‘password’ in ‘field list’
mysql> update mysql.user set authentication_string=password(‘‘) where user=’‘; #修改密碼成功
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1

mysql> flush privileges; #立即生效
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

n>mysql -u ******* -p #以該用戶登錄成功.
Enter password: ********
…………………………
mysql>

九、修改c7m01服務器的mysql配置文件（三台都是需要做修改的，只有server-id修改為不一樣的就可以了）

vi /etc/my.cnf

[mysqld]
server-id=1 #修改server-id=2、server-id=3
log-bin=mysql-bin

禁止mysql自動刪除relaylog工能

relay_log_purge = 0

mysql5.6已上的特性，開啟gtid，必須主從全開

gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1更改完成後重啟mysqlsystemctl restart mysql

十、在三個MySQL節點上做授權配置(主從授權)

A.示例:（注意三個節點都需要授權）

MySQL賦予用戶權限的命令的簡單格式為

　　grant 權限 on 數據庫對象 to 用戶
　　grant 權限 on 數據庫對象 to 用戶 identified by “密碼”
mysql> grant replication slave on . to ‘repl’@’192.168.2.%’ identified by ‘123456’;#授權主從同步用戶
mysql> grant all on . to ‘mha’@’192.168.2.%’ identified by ‘123456’; #授權MHA管理用戶,很重要
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

B.在兩個salve節點上，只讀限制（防止意外被寫數據）-特別重要

mysql> set global read_on=1;

十一、提示:確保前面部署設置都是OK的，在進入下面環節

十二、在主查看Master的狀態（主從備份）

a.打開鏈接

mysql>start slave;
b.查看主master狀態

mysql> change master to master_host=’192.168.2.56′,
master_user=’rep’,
master_password=’123456′,
master_log_file=’binlog.000011′,
master_log_pos=154;
d.查看主從狀態兩個YES代表正常（57、58）都需要查看

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.56
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000052
Relay_Log_Pos: 360
Relay_Master_Log_File: mysql-bin.000017
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 194
Relay_Log_Space: 19375
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:

E:驗證MySql主從同步，在主庫寫入數據

 1.寫入數據

mysql> use mydb;
Database changed
mysql> create table test(id int primary key);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into test values(1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into test values(2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(3);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from mydb.test;
+—-+
| id |
+—-+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+—-+
2.slave1節點驗證

mysql> select * from mydb.test;
+—-+
| id |
+—-+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+—-+
3.slave2節點驗證

mysql> select * from mydb.test;
+—-+
| id |
+—-+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+—-+

十三、部署MHA集群架構

安裝MHA軟件（在三個節點上都的安裝MHA的node軟件）

a.安裝依賴

wget //dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
rpm -ivh epel-release-latest-7.noarch.rpm
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager -y
b. 安裝node(二選一)

wget //qiniu.wsfnk.com/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
wget //github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
c.slave 3 配置MHA（manager節點在c703:192.168.2.58）上

wget //github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

2.配置MHA

創建目錄準備配置文件

[root@ c703 ~]# mkdir -p /etc/mha
[root@ c703 ~]# mkdir -p /var/log/mha/app1
[root@ c703 ~]# vim /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1
master_binlog_dir=/var/lib/mysql #binlog的目錄，如果說miysql的環境不一樣，binlog位置不同，每台服務器的binlog的位置寫在server標籤裏面即可
user=mha
password=mha
ping_interval=2
repl_password=123456
repl_user=rep
ssh_user=root
[server1]
hostname=10.0.0.12
port=3306
[server2]
hostname=10.0.0.13
port=3306
[server3]
hostname=10.0.0.14
port=3306
ignore_fail=1 #如果這個節點掛了，mha將不可用，加上這個參數，slave掛了一樣可以用
no_master=1 #從不將這台主機轉換為master

candidate_master=1 #如果候選master有延遲的話，relay日誌超過100m，failover切換不能成功，加上此參數後會忽略延遲日誌大小。

check_repl_delay=0 #用防止master故障時,切換時slave有延遲,卡在那裡切不過來

注意這裡的配置要把注釋和空格全部刪除

十四、驗證MHA狀態

驗證ssh狀態

[root@ c703 ~]# masterha_check_ssh –con=/etc/mha/app1.cnf
Fri Feb 21 13:37:37 2020 – [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Feb 21 13:37:37 2020 – [info] Reading application default configuration from /etc/mha/app1.cnf..
Fri Feb 21 13:37:37 2020 – [info] Reading server configuration from /etc/mha/app1.cnf..
Fri Feb 21 13:37:37 2020 – [info] Starting SSH connection tests..
Fri Feb 21 13:37:38 2020 – [debug]
Fri Feb 21 13:37:37 2020 – [debug] Connecting via SSH from [email protected](10.0.0.12:22) to [email protected](10.0.0.13:22)..
Fri Feb 21 13:37:38 2020 – [debug] ok.
Fri Feb 21 13:37:38 2020 – [debug] Connecting via SSH from [email protected](10.0.0.12:22) to [email protected](10.0.0.14:22)..
Fri Feb 21 13:37:38 2020 – [debug] ok.
Fri Feb 21 13:37:39 2020 – [debug]
Fri Feb 21 13:37:37 2020 – [debug] Connecting via SSH from [email protected](10.0.0.13:22) to [email protected](10.0.0.12:22)..
Fri Feb 21 13:37:39 2020 – [debug] ok.
Fri Feb 21 13:37:39 2020 – [debug] Connecting via SSH from [email protected](10.0.0.13:22) to [email protected](10.0.0.14:22)..
Fri Feb 21 13:37:39 2020 – [debug] ok.
Fri Feb 21 13:37:40 2020 – [debug]
Fri Feb 21 13:37:38 2020 – [debug] Connecting via SSH from [email protected](10.0.0.14:22) to [email protected](10.0.0.12:22)..
Fri Feb 21 13:37:39 2020 – [debug] ok.
Fri Feb 21 13:37:39 2020 – [debug] Connecting via SSH from [email protected](10.0.0.14:22) to [email protected](10.0.0.13:22)..
Fri Feb 21 13:37:40 2020 – [debug] ok.
Fri Feb 21 13:37:40 2020 – [info] All SSH connection tests passed successfully.

十五、啟動MHA

1.參考MHA常用命令：

1.查看ssh登陸是否成功 masterha_check_ssh –con=/etc/masterha/app1.cnf

2.查看複製是否建立好 masterha_check_repl –con=/etc/masterha/app1.cnf

3.啟動mha nohup masterha_manager –con=/etc/masterha/app1.cnf > /tmp/mha_manager.log < /dev/null 2>&1 &
當有slave節點宕掉的情況是啟動不了的，加上–ignore_fail_on_start
即使有節點宕掉也能啟動mha nohup masterha_manager –con=/etc/masterha/app1.cnf
–ignore_fail_on_start > /tmp/mha_manager.log < /dev/null 2>&1 &

4.檢查啟動的狀態masterha_check_status –con=/etc/masterha/app1.cnf

5.停止mhamasterha_stop –con=/etc/masterha/app1.cnf

6.failover後下次重啟每次failover切換後會在管理目錄生成文件app1.failover.complete ，
下次在切換的時候會發現有這個文件導致切換不成功，需要手動清理掉。
rm -rf /masterha/app1/app1.failover.complete也可以加上參數–ignore_last_failover

7.手工failover手工failover場景，master死掉，但是masterha_manager沒有開啟，
可以通過手工failover：masterha_master_switch –con=/etc/masterha/app1.cnf
–dead_master_host=10.50.2.10 –master_state=dead –new_master_host=10.50.2.12 –ignore_last_failover

8.masterha_manager是一種監視和故障轉移的程序。另一方面,masterha_master_switch程序不監控主庫。
masterha_master_switch可以用於主庫故障轉移,也可用於在線總開關。

9.手動在線切換masterha_master_switch –con=/etc/app1.cnf
–master_state=alive –new_master_host=192.168.119.74 –
-orig_master_is_new_slave 或者masterha_master_switch –con=/etc/app1.cnf
–master_state=alive –new_master_host=192.168.119.74
–orig_master_is_new_slave –running_updates_limit=10000–orig_master_is_new_slave

10.ps -ef|grep詳解 #ps 查找 grep 命令查找

2.啟動MHA

[root@ c703 ~]# nohup masterha_manager –con=/etc/mha/app1.cnf –remove_dead_master_conf –ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
[1] 6041
[root@ c703 ~]# ps -ef |grep mha
root 6041 4852 0 14:37 pts/0 00:00:00 perl /usr/bin/masterha_manager –con=/etc/mha/app1.cnf –remove_dead_master_conf –ignore_last_failover
root 6066 4852 0 14:38 pts/0 00:00:00 grep –color=auto mha

[root@ c703 ~]# ps -ef |grep mha

3.查看MHA的狀態

[root@ c703 ~]# masterha_check_status –con=/etc/mha/app1.cnf
app1 (pid:6041) is running(0:PING_OK), master:192.168.2.56

4.檢查MHA的配置文件

 在測試的過程中如果產生VIP的轉換，在未將MHA故障還原的情況下，會造成MHA管理的配置文件刪除

[root@ c703 ~]# cat /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1
master_binlog_dir=/var/lib/mysql
user=mha
password=mha
ping_interval=2
repl_password=123456
repl_user=rep
ssh_user=root

[server1]
hostname=10.0.0.12 #當c7m01上的主庫宕機之後，MHA會自動檢查，發現主庫mysql停機，IP會發生漂移
port=3306

[server2]
hostname=10.0.0.13
port=3306
[server3]
hostname=10.0.0.14
port=3306
ignore_fail=1
no_master=1

candidate_master=1

check_repl_delay=0

當c7m01上的主庫宕機之後，MHA會自動檢車，發現主庫mysql停機，立刻會把從庫提升為主庫，然後另一台服務器會把

mysql主從複製的master_host改為新提升的主庫。

5.MHA故障還原

  提示：1.現在c703查看  2.還原mastes_host 賬戶信息

[root@c703 ~]# grep “CHANGE MASTER TO MASTER” /var/log/mha/app1/manager.log | tail -1
Fri Feb 14 01:25:18 2020 – [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST=’192.168.2.56′, MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER=’rep’, MASTER_PASSWORD=’xxx’;

6.故障還原操作

  提示:在c7m01執行，故障還原 或者在本機57(主從)上執行也行更改。master_host

mysql -uroot -p123456 -e “CHANGE MASTER TO MASTER_HOST=’192.168.2.57′,MASTER_PORT=3306, MASTER_AUTO_POSITION=1,MASTER_USER=’rep’,MASTER_PASSWORD=’123456′;”

借鑒參考：MHA故障處理

7.重新啟動MHA

[root@ c703 ~]# nohup masterha_manager –con=/etc/mha/app1.cnf –remove_dead_master_conf –ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
[1] 6041
配置vip漂移，有倆種方式
第一種是keepalived的方式，管理虛擬ip的漂移
第二種是MHA自帶腳本的方式，管理虛擬ip的漂移（這個漂移就是那個服務器的mysql庫提升為主庫，就漂移到那個上面，要根據binlog的最新slave方式提升）

8.MHA腳本方式

a.修改app1配置文件、添加

[root@ c703 ~]# vi /etc/mha/app1.cnf
[server default]
master_ip_failover_script=/usr/bin/master_ip_failover #添加這一行
b.編寫腳本

[root@ c703 ~]# 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.2.59 /24’; #這裡的vip地址寫一個與自己IP地址相同的IP段
my $key = ‘1’;
my $ssh_start_vip = “/sbin/ifconfig ens33:$key $vip”; #這裡的網卡要看自己外網的外卡是不是ens33，不是的話要改成自己的外網網卡
my $ssh_stop_vip = “/sbin/ifconfig ens33:$key down”;
GetOptions(
‘command=s’ => $command,
‘ssh_user=s’ => $ssh_user,
‘orig_master_host=s’ => $orig_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”; exit 0;
}else {
&usage(); exit 1;
}
}
sub start_vip() {
ssh $ssh_user@$new_master_host ” $ssh_start_vip “;
}
sub stop_vip() {
return 0 unless ($ssh_user);
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”;
}

c.master_ip_failover_script問題終極解決方案

VIP漂移解決方案

9給腳本加權限（後面這些參考資料:腳本配置）

[root@ c703 ~]# chmod +x /usr/bin/master_ip_failover

10.手動添加vip，綁定到主庫的機子上，我這裡是192.168.2.57

[root@ c702 ~]# ifconfig ens33:1 192.168.2.59/24
[root@ c702 ~]# ip a show ens33

11、重啟MHA

[root@ c703 ~]# masterha_stop –con=/etc/mha/app1.cnf
Stopped app1 successfully.
[root@ c703 ~]# nohup masterha_manager –con=/etc/mha/app1.cnf –remove_dead_master_conf –ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &
[1] 6468

十二、模擬主庫宕機vip飄移

斷開某個服務器
在c702上操作

[root@ c702 ~]# systemctl stop mysql
[root@ c702 ~]# ip a show ens33

這個時候vip已經漂移到另一台服務器上，將另一台服務器做為主庫
查看c7m01上是否有vip漂移

[root@ c7m01 ~]# ip a show ens33

當vip漂移到另一台服務器上表示成功，然後查看MHA的服務器上是否改變到另一台服務器上的master_host

[root@ c703 ~]# mysql -uroot -p123456 -e ‘show slave status\G’;

[root@ c703 ~]# cat /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1
master_binlog_dir=/var/lib/mysql
master_ip_failover_script=/usr/bin/master_ip_failover
password=mha
ping_interval=2
repl_password=123456
repl_user=rep
ssh_user=root
user=mha

[server1]
hostname=10.0.0.12
port=3306

[server3]
hostname=10.0.0.14
ignore_fail=1
no_master=1
port=3306

這個時候開始修復，手動操作，將他恢復到之前的狀態

[root@ c703 ~]# grep “CHANGE MASTER TO MASTER” /var/log/mha/app1/manager.log | tail -1
[root@c702 ~]#systemctl restart mysql
[root@c702 ~]# mysql -uroot -p123456 -e “CHANGE MASTER TO MASTER_HOST=’10.0.0.12′,MASTER_PORT=3306, MASTER_AUTO_POSITION=1,MASTER_USER=’rep’,MASTER_PASSWORD=’123456′;”
[root@c702 ~]# mysql -uroot -p123456 -e “start slave;”

[root@c702 ~]# mysql -uroot -p123456 -e “show slave status \G”

重啟MHA

nohup masterha_manager –con=/etc/mha/app1.cnf –remove_dead_master_conf –ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

備註：

1.前部分MySql主從備份，以我的步驟為準，MHA布置已博文鏈接為主。
2.MHA高可用配置及故障切換——詳細部署步驟及報錯解決辦法
3.參考參考腳本配置：//www.cnblogs.com/chendian0/archive/2004/01/13/13993256.html
4.主從備份失誤已經全部繞開（包括版本錯誤）
5.歡迎交流溝通

Tags: MHA集群配置

MHA

禁止mysql自動刪除relaylog工能

mysql5.6已上的特性，開啟gtid，必須主從全開

candidate_master=1 #如果候選master有延遲的話，relay日誌超過100m，failover切換不能成功，加上此參數後會忽略延遲日誌大小。

check_repl_delay=0 #用防止master故障時,切換時slave有延遲,卡在那裡切不過來

注意這裡的配置要把注釋和空格全部刪除

candidate_master=1

check_repl_delay=0

!/usr/bin/env perl

重啟MHA

VirMach 便宜 VPS

QNews

MHA

禁止mysql自動刪除relaylog工能

mysql5.6已上的特性，開啟gtid，必須主從全開

candidate_master=1 #如果候選master有延遲的話，relay日誌超過100m，failover切換不能成功，加上此參數後會忽略延遲日誌大小。

check_repl_delay=0 #用防止master故障時,切換時slave有延遲,卡在那裡切不過來

注意這裡的配置要把注釋和空格全部刪除

candidate_master=1

check_repl_delay=0

!/usr/bin/env perl

重啟MHA

分享此文：

Related Posts

精盡MyBatis源碼分析 – 文章導讀

《JavaScript 模式》讀書筆記（5）— 對象創建模式4

中國電信打造的5G手機上市！不足千元 支持超級SIM卡

大廠也在用的 6種 數據脫敏方案，嚴防泄露數據的 「內鬼」

VirMach 便宜 VPS

QNews

熱門搜尋

中國電信打造的5G手機上市！不足千元支持超級SIM卡

大廠也在用的 6種數據脫敏方案，嚴防泄露數據的「內鬼」