整个MHA+keepalived+lvs+mysql高可用架构配置说明

约定不等于承诺〃 2022-10-02 03:44 234阅读 0赞

## **整个MHA+keepalived+lvs+mysql高可用架构配置说明   1.1. 环境简介**   1.1.1、vmvare虚拟机，系统版本CentOS7.5 x86\_64位最小化安装，mysql的版本5.7.21，   1.1.2、虚拟机器的ssh端口均为默认22,   1.1.3、虚拟机的iptables全部关闭，   1.1.4、虚拟机的selinux全部关闭，   1.1.5、虚拟机服务器时间全部一致 ntpdate 0.asia.pool.ntp.org   1.1.6、3台机器的ssh端口为22\*\*   1.2、此次试验采用的是3台机器，机器具体部署如下：   角色 IP地址（内网） 主机名称 节点机器部署服务 业务用途   Monitor 192.168.52.250 db250 mha4mysql-manager-0.58-0.el6   Master 192.168.52.251 db251 mha4mysql-node-0.58-0.el6 写入(keepalived) ##

slave(备master) 192.168.52.252 db252 mha4mysql-node-0.58-0.el6 keepalived  
Slave 192.168.52.253 db253 mha4mysql-node-0.58-0.el6  
读+备份数据  
Lvs01 192.168.52.248 lvs248 lvs+keepalived  
从库负载均衡  
Lvs02 192.168.52.249 lvs249 lvs+keepavlied  
从库负载均衡  
Mysql write vip： 192.168.52.199  
Mysql read vip： 192.168.52.159  
1.3 系统架构图：

故障后架构图

1.4 架构实现原理：  
1． 读操作  
1 ） LVS 实现读操作的负载均衡；  
2） Keepalived 在上层管理 LVS， 并对两台从库进行健康检测（通过定义 Ch  
eck 脚本） ；  
3） 一台从库出现故障后， Keepalived 将其剔除出负载均衡集群；

1.  写操作  
    1 ） 在 Master 上绑定写 VIP（MHA 启动后会通过脚本进行操作）；  
    2） MHA 监控 Master 状态，当 Master 出现故障后（宕机、复制暂停）时;  
    3） 通过 Failover 脚本，卸载 Master 上的 WVIP；  
    4） 通过 Failover 脚本在 Backup Master 上绑定 WVIP，提升其为主库；  
    5） 同步并应用差异日志， 并将从库指向新主库；  
    问题：当 MHA 把 Master 切换到了 Backup Master 上后， LVS 如何处理分发在 Ba  
    ckup Master 上的读操作？  
    解释： 由于 Keepalived 会通过脚本定期监控 Backup Master 的状态， 包括同步、  
    SQL 线程、 I/O 线程， 所以当 Backup Master 升级为主库后， 这些状态都将消失，  
    Keepalived 将自动将 Backup Master 剔除出负载均衡集群。  
    1.5 说明介绍：  
    Db252和db253是db251的slave从库，复制环境搭建后面会简单演示,其中master对外提供写服务，备选master（实际的slave，主机名server03）提供读服务，slave也提供相关的读服务，一旦master宕机，将会把备  
    选备master提升为新的master，slave指向新的master,db250上部署Monitor（MHA Manager监控），主要是监控主从复制的集群中主库master是否正常，一旦master挂掉，MHA Manager会自动完成主库和slave从库的自动切换  
    1.6 安装mysql  
    Mater和slave都需要安装  
    wget [http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm][http_dev.mysql.com_get_mysql57-community-release-el7-8.noarch.rpm]  
    yum localinstall mysql57-community-release-el7-8.noarch.rpm  
    yum install mysql-community-server -y  
    systemctl enable mysqld  
    systemctl daemon-reload  
    systemctl start mysqld  
    systemctl status mysqld  
    grep 'temporary password' /var/log/mysqld.log  
    mysql -uroot -p  
    \[root@server04 ~\]\# mysql -u root  
    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

service mysqld restart  
192.168.52.251  
vim /etc/my.cnf

\[mysqld\]  
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
character-set-server=utf8  
server-id=1  
log-bin=master-log  
relay-log=relay-log  
innodb\_file\_per\_table = ON  
skip\_name\_resolve = ON  
max\_connections = 5000\#不在配置文件中限定只读，但是要记得在slave上限制只读

symbolic-links=0  
log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid  
192.168.52.252  
vim /etc/my.cnf  
\[mysqld\]  
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
character-set-server=utf8  
server-id=2  
log-bin=master-log  
relay-log=relay-log  
relay\_log\_purge=0  
read\_only=1  
skip\_name\_resolve=1  
innodb\_file\_per\_table=1  
**\# Disabling symbolic-links is recommended to prevent assorted security risks**  
symbolic-links=0

log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid  
192.168.52.253  
vim /etc/my.cnf  
\[mysqld\]  
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
character-set-server=utf8

server-id=3  
log-bin=master-log  
relay-log=relay-log  
relay\_log\_purge=0  
read\_only=1  
skip\_name\_resolve=1  
innodb\_file\_per\_table=1  
**\# Disabling symbolic-links is recommended to prevent assorted security risks**  
symbolic-links=0

log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid

第二：新建用户repl\_user设置密码123456  
重置root密码：123456

\#更改密码  
mysql -u root  
\#更改密码策略  
set global validate\_password\_policy=0;  
set global validate\_password\_length=4;  
SET PASSWORD = PASSWORD('123456');  
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.52.%' IDENTIFIED BY '123456' WITH GRANT OPTION;  
flush privileges;  
主从复制授权：  
第三：在 3 个 mysql 节点做授权配置(主从复制授权)  
Master:  
grant replication slave,replication client on *.* to 'repluser'@'192.168.52.%' identified by '123456' ;  
flush privileges;  
\#授权MHA管理用户-mhaadmin  
grant all on *.* to 'mhaadmin'@'192.168.52.%' identified by 'mhapass' ;  
flush privileges;  
mysql> show master status;  
\+-------------------+----------+--------------+------------------+-------------------+  
| File | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set |  
\+-------------------+----------+--------------+------------------+-------------------+  
| master-log.000005 | 154 | | | |  
\+-------------------+----------+--------------+------------------+-------------------+  
1 row in set (0.00 sec)  
slave（两个从库）  
\#配置主从复制起点  
change master to master\_host='192.168.52.251',master\_user='repluser',master\_password='123456',master\_log\_file='master-log.000005',master\_log\_pos=154;  
start slave;  
show slave status\\G  
Slave\_IO\_Running: Yes  
Slave\_SQL\_Running: Yes  
mysql> set global read\_only=1; \#查看slave IO和slave sql是否都正常  
\#查看主从复制情况  
show grants for 'repluser'@'192.168.52.%';  
mysql> flush privileges; \#刷新权限  
Query OK, 0 rows affected (0.00 sec)  
删除多余用户  
mysql> drop user root@'localhost';  
mysql> select user,host from mysql.user;

1.7 ssh授信  
配置三台机器的ssh互信（三台都要操作）  
ssh-keygen -t rsa  
ssh-copy-id -i /root/.ssh/id\_rsa.pub root@192.168.52.250  
ssh-copy-id -i /root/.ssh/id\_rsa.pub root@192.168.52.251  
ssh-copy-id -i /root/.ssh/id\_rsa.pub root@192.168.52.252  
ssh-copy-id -i /root/.ssh/id\_rsa.pub root@192.168.52.253

\#测试是否成功  
ssh 192.168.52.251 date  
1.8 安装MHA软件  
安装MHA软件（在三个节点上都装mha的node软件）  
\#先安装依赖  
wget [http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm][http_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  
下载软件  
wget [https://qiniu.wsfnk.com/mha4mysql-node-0.58-0.el7.centos.noarch.rpm][https_qiniu.wsfnk.com_mha4mysql-node-0.58-0.el7.centos.noarch.rpm]  
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

仅在manager节点上安装mha管理软件(192.168.52.250)  
wget [https://qiniu.wsfnk.com/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm][https_qiniu.wsfnk.com_mha4mysql-manager-0.58-0.el7.centos.noarch.rpm]  
yum install perl-Parallel-ForkManager -y  
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

yum install mailx -y    #该软件是用来发送邮件的

\-rwxr-xr-x 1 root root 5172 Jan 7 14:09 masterha\_secondary\_check  
\-rwxr-xr-x 1 root root 1739 Jan 7 14:09 masterha\_stop  
\-rwxr-xr-x 1 root root 8337 Jan 7 14:14 purge\_relay\_logs  
\-rwxr-xr-x 1 root root 7525 Jan 7 14:14 save\_binary\_logs  
1.9 配置MHA软件  
192.168.52.250机器操作：

\[root@db250 bin\]\# cd /usr/bin/  
\[root@db250 bin\]\# find ./ -name apply\_diff\_relay\_logs  
./apply\_diff\_relay\_logs  
\[root@db250 bin\]\# cp /usr/bin/save\_binary\_logs /usr/local/bin/  
\[root@db250 bin\]\# cp /usr/bin/purge\_relay\_logs /usr/local/bin/  
\[root@db250 bin\]\# cp /usr/bin/filter\_mysqlbinlog /usr/local/bin/  
\[root@db250 bin\]\# cp /usr/bin/apply\_diff\_relay*logs /usr/local/bin/  
\[root@db250 bin\]\# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql  
\[root@db250 bin\]\# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog  
\[root@db250 bin\]\# cp -r /usr/bin/masterha*\* /usr/local/bin  
\[root@db250 bin\]\# cd /usr/local/bin/  
\[root@db250 bin\]\# ll  
total 88  
\-rwxr-xr-x 1 root root 17639 Jan 7 14:14 apply\_diff\_relay\_logs  
\-rwxr-xr-x 1 root root 4807 Jan 7 14:14 filter\_mysqlbinlog  
\-rwxr-xr-x 1 root root 1995 Jan 7 14:09 masterha\_check\_repl  
\-rwxr-xr-x 1 root root 1779 Jan 7 14:09 masterha\_check\_ssh  
\-rwxr-xr-x 1 root root 1865 Jan 7 14:09 masterha\_check\_status  
\-rwxr-xr-x 1 root root 3201 Jan 7 14:09 masterha\_conf\_host  
\-rwxr-xr-x 1 root root 2517 Jan 7 14:09 masterha\_manager  
\-rwxr-xr-x 1 root root 2165 Jan 7 14:09 masterha\_master\_monitor  
\-rwxr-xr-x 1 root root 2373 Jan 7 14:09 masterha\_master\_switch

MHA配置文件如下  
mkdir -p /etc/masterha  
\[root@db250 app1\]\# cat /etc/masterha/app1.cnf  
\[server default\]  
manager\_log=/var/log/masterha/app1/manager.log  
manager\_workdir=/var/log/masterha/app1.log  
master\_binlog\_dir=/var/lib/mysql  
master\_ip\_failover\_script=/usr/local/bin/master\_ip\_failover  
master\_ip\_online\_change\_script=/usr/local/bin/master\_ip\_online\_change  
password=123456  
ping\_interval=1  
remote\_workdir=/tmp  
repl\_password=123456  
repl\_user=repluser  
secondary\_check\_script=/usr/local/bin/masterha\_secondary\_check -s db251 -s db252 --user=root --master\_host=db252 --master\_ip=192.168.52.252 --master\_port=3306  
shutdown\_script=""  
ssh\_port=22  
ssh\_user=root

\[server1\]  
hostname=192.168.52.251  
candidate\_master=1  
port=3306

\[server2\]  
candidate\_master=1  
check\_repl\_delay=0  
hostname=192.168.52.252  
port=3306

\[server3\]  
hostname=192.168.52.253  
port=3306

二、设置relay log的清除方式（在每个slave节点上）：  
三个节点服务器本地hosts解析  
\[root@db250 app1\]\# vim /etc/hosts  
192.168.52.250 db250  
192.168.52.251 db251  
192.168.52.252 db252  
192.168.52.253 db253

在slave master01 192.168.52.252操作：  
\[root@db251 ~\]\# mysql -uroot -p123456 -e "set global relay\_log\_purge=0"  
mysql: \[Warning\] Using a password on the command line interface can be insecure.

在slave master02 192.168.52.253操作：  
\[root@ db252 ~\]\# mysql -uroot -p123456 -e "set global relay\_log\_purge=0"  
mysql: \[Warning\] Using a password on the command line interface can be insecure.

注意：  
MHA在发生切换的过程中，从库的恢复过程中依赖于relay log的相关信息，所以这里要将relay log的自动清除设置为OFF，采用手动清除relay log的方式。在默认情况下，从服务器上的中继日志会在SQL线程执行完毕后被自动删除。但是在MHA环境中，这些中继日志在恢复其他从服务器时可能会被用到，因此需要禁用中继日志的自动删除功能。定期清除中继日志需要考虑到复制延时的问题。在ext3的文件系统下，删除大的文件需要一定的时间，会导致严重的复制延时。为了避免复制延时，需要暂时为中继日志创建硬链接，因为在linux系统中通过硬链接删除大文件速度会很快。（在mysql数据库中，删除大表时，通常也采用建立硬链接的方式）

2.2设置定期清理relay脚本（两台slave服务器）：  
\[root@ db252~\]\# cat /data/scripts/purge\_relay\_log.sh  
\#!/bin/bash  
user=root  
passwd=123456  
port=3306  
log\_dir='/data/masterha/log'  
work\_dir='/data'  
purge='/usr/local/bin/purge\_relay\_logs'

if \[ ! -d $log\_dir \]  
then  
mkdir $log\_dir -p  
fi

$purge --user=$user --password=$passwd --disable\_relay\_log\_purge --port=$port --workdir=$work\_dir >> $log\_dir/purge\_relay\_logs.log 2>

脚本介绍：  
\--user mysql //用户名  
\--password mysql //密码  
\--port //端口号  
\--workdir //指定创建relay log的硬链接的位置，默认是/var/tmp，由于系统不同分区创建硬链接文件会失败，故需要执行硬链接具体位置，成功执行脚本后，硬链接的中继日志文件被删除  
\--disable\_relay\_log\_purge //默认情况下，如果relay\_log\_purge=1，脚本会什么都不清理，自动退出，通过设定这个参数，当relay\_log\_purge=1的情况下会将relay\_log\_purge设置为0。清理relay log之后，最后将参数设置为OFF。

purge\_relay\_logs脚本删除中继日志不会阻塞SQL线程。下面我们手动执行看看什么情况:  
\[root@ db252 ~\]\# purge\_relay\_logs --user=root --password=123456 --port=3306 -disable\_relay\_log\_purge --workdir=/data/  
2018-07-01 11:53:16: purge\_relay\_logs script started.  
Found relay\_log.info: /data/mysql/relay-log.info  
Opening /data/mysql/logs/relay-log/relay-bin.000001 ..  
Opening /data/mysql/logs/relay-log/relay-bin.000002 ..  
Executing SET GLOBAL relay\_log\_purge=1; FLUSH LOGS; sleeping a few seconds so that SQL thread can delete older relay log files (if it keeps up); SET GLOBAL relay\_log\_purge=0; .. ok.  
2018-07-01 11:53:20: All relay log purging operations succeeded.

主从failover脚本  
\[root@db250 app1\]\# cat /usr/local/bin/master\_ip\_failover  
\#!/usr/bin/env perl

\#This program is free software; you can redistribute it and/or modify  
\#t under the terms of the GNU General Public License as published by  
\#the Free Software Foundation; either version 2 of the License, or  
\#(at your option) any later version.

\#This program is distributed in the hope that it will be useful,  
\#but WITHOUT ANY WARRANTY; without even the implied warranty of  
\#MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the  
\#GNU General Public License for more details.  
\#You should have received a copy of the GNU General Public License  
\#along with this program; if not, write to the Free Software  
\#Foundation, Inc.,  
\#51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA

\#\#Note: This is a sample script and is not complete. Modify the script based on your environment.  
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#  
use strict;  
use warnings FATAL => 'all';

use Getopt::Long;  
use MHA::DBHelper;

my (  
$command, $ssh\_user, $orig\_master\_host,  
$orig\_master\_ip, $orig\_master\_port, $new\_master\_host,  
$new\_master\_ip, $new\_master\_port, $new\_master\_user,  
$new\_master\_password  
);  
my $vip = '192.168.52.199';  
my $ssh\_start\_vip = "systemctl start keepalived";  
my $ssh\_stop\_vip = "systemctl stop keepalived ";

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,  
'new\_master\_user=s' => \\$new\_master\_user,  
'new\_master\_password=s' => \\$new\_master\_password,  
);

exit &main();

sub main \{

print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

if ( $command eq "stop" || $command eq "stopssh" ) \{

#$orig_master_host, $orig_master_ip, $orig_master_port are passed.
    #If you manage master ip address at global catalog database,
    #invalidate orig_master_ip here.
    my $exit_code = 1;
    eval {
      print "Disabling the VIP on old master: $orig_master_host \n";
      &stop_vip();
      #updating global catalog, etc
      $exit_code = 0;
    };
    if ($@) {
      warn "Got Error: $@\n";
      exit $exit_code;
    }
    exit $exit_code;

\}  
elsif ( $command eq "start" ) \{

#all arguments are passed.
    #If you manage master ip address at global catalog database,
    #activate new_master_ip here.
    #You can also grant write access (create user, set read_only=0, etc) here.
    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 $@;
    
      #If you want to continue failover, exit 10.
      exit $exit_code;
    }
    exit $exit_code;

\}  
elsif ( $command eq "status" ) \{  
print "Checking the Status of the script.. OK \\n";  
\#do nothing  
exit 0;  
\}  
else \{  
&usage();  
exit 1;  
\}  
\}

sub start\_vip() \{  
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;  
\}

\#A simple system call that disable the VIP on the old\_master  
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";  
\}

chmod +x /usr/local/bin/master\_ip\_failover

1.10 MHA相关测试  
测试SSH免密码登录  
masterha\_check\_ssh --conf=/etc/masterha/app1.cnf

测试MHA数据库同步  
masterha\_check\_repl --conf=/etc/masterha/app1.cnf  
chmod +x /usr/local/bin/masterha\_check\_repl

查看MHA Manager监控是否正常：  
masterha\_check\_status --conf=/etc/masterha/app1.cnf

关闭MHA Manage监控  
masterha\_stop --conf=/etc/masterha/app1.cnf

1.11 启动mha数据监控  
masterha\_check\_status --conf=/etc/masterha/app1.cnf  
mkdir -p /var/log/masterha/app1/

nohup masterha\_manager --conf=/etc/masterha/app1.cnf --remove\_dead\_master\_conf --ignore\_last\_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &  
ps -ef|grep perl

1.12 配置vip实现MHA架构中主库故障自动切换  
192.168.52.251和192.168.52.252  
Yum install keepalived -y

192.168.52.251  
\[root@db251 ~\]\# vim /etc/keepalived/keepalived.conf  
global\_defs \{  
notification\_email \{ [br/>305xxx7536@qq.com  
\}  
notification\_email\_from Alexandre.Cassen@firewall.loc  
smtp\_server 192.168.52.251  
smtp\_connect\_timeout 30  
router\_id LVS\_01  
\}][br_305xxx7536_qq.com _ notification_email_from Alexandre.Cassen_firewall.loc smtp_server 192.168.52.251 smtp_connect_timeout 30 router_id LVS_01]

vrrp\_instance VI\_1 \{  
\#state MASTER  
state BACKUP  
interface ens33  
virtual\_router\_id 51  
priority 100  
advert\_int 1  
nopreempt  
authentication \{  
auth\_type PASS  
auth\_pass 1111  
\}  
virtual\_ipaddress \{  
192.168.2.199/24  
\}  
\}

systemctl start keepalived

192.168.52.252  
\[root@db252 ~\]\# vim /etc/keepalived/keepalived.conf

global\_defs \{  
notification\_email \{ [br/>305xxx7536@qq.com  
\}  
notification\_email\_from Alexandre.Cassen@firewall.loc  
smtp\_server 192.168.52.252  
smtp\_connect\_timeout 30  
router\_id LVS\_01  
\}][br_305xxx7536_qq.com _ notification_email_from Alexandre.Cassen_firewall.loc smtp_server 192.168.52.251 smtp_connect_timeout 30 router_id LVS_01]\*\*

vrrp\_instance VI\_1 \{  
\#state MASTER  
state BACKUP  
interface ens33  
virtual\_router\_id 51  
priority 90  
advert\_int 1  
nopreempt  
authentication \{  
auth\_type PASS  
auth\_pass 1111  
\}  
virtual\_ipaddress \{  
192.168.52.199/24  
\}  
\}

systemctl start keepalived

\#\#\#\#\#特别注意！！！！！  
上面两台服务器的keepalived都设置为了BACKUP模式，在keepalived中2种模式，分别是master->backup模式和backup->backup模式。这两种模式有很大区别。在master->backup模式下，一旦主库宕机，虚拟ip会自动漂移到从库，当主库修复后，keepalived启动后，还会把虚拟ip抢占过来，即使设置了非抢占模式（nopreempt）抢占ip的动作也会发生。在backup->backup模式下，当主库宕机后虚拟ip会自动漂移到从库上，当原主库恢复和keepalived服务启动后，并不会抢占新主的虚拟ip，即使是优先级高于从库的优先级别，也不会发生抢占。为了减少ip漂移次数，通常是把修复好的主库当做新的备库。  
\++++到此处MHA架构中keepalived服务安装配置完成++++

1.13检查故障切换后MHA集群相关服务的变化  
配置文件/etc/masterha/app1.cnf变化  
1、在db250 192.168.52.250管理节点查看一下配置文件/etc/masterha/app1.cnf可以发现\[server1\]的内容已经被自动去掉了：  
2、masterha\_manager 服务自动退出  
3、源master192.168.52.251机器上keepalived服务被停掉了  
4、查看日志/var/log/masterha/app1/manager.log

\#\#\#\#\#\#\#重要提示！！！  
当db251 192.168.52.251 机器上的mysql挂掉后，db252 192.168.52.252机器提升为master时，192.168.52.251 机器上的keepalived会停掉，而 192.168.52.252机器的keepalived会开启，VIP票到199机器上。  
此时需要重启192.168.52.251上的mysql,一般都是要恢复它作为252新主的从库，此时192.168.52.251机器上的keepalived千万不要开启，因为开启keepalived，会抢占252机器上的VIP,导致程序连接数据库出现混乱。同时192.168.52.251机器和192.168.52.252机器上的keepalived服务不要设置为开机自启动  
1.14 mha高可用重新加入监控  
1、出问题的master:  
show master status;

Last\_IO\_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs;  
these UUIDs must be different for replication to work.

2、解决方法：  
rm -rf /var/lib/mysql/auto.cnf  
systemctl restart mysqld

3、数据库从库同步主库

show master status；  
\#配置主从复制起点

change master to master\_host='192.168.52.251',master\_user='repluser',master\_password='123456',master\_log\_file='master-log.000004',master\_log\_pos=154; \#注意masterIP地址  
start slave;  
show slave status\\G

4、切换完毕后缺少

secondary\_check\_script=/usr/local/bin/masterha\_secondary\_check -s db251 -s db252 --user=root --master\_host=db252 --master\_ip=192.168.52.252 --master\_port=3306

添加：  
\[server1\]  
hostname=192.168.52.252  
candidate\_master=1  
port=3306

5、测试  
测试ssh  
masterha\_check\_ssh --conf=/etc/masterha/app1.cnf

测试MHA数据库同步，并自动启动  
masterha\_check\_repl --conf=/etc/masterha/app1.cnf

查看MHA Manager监控是否正常：  
masterha\_check\_status --conf=/etc/masterha/app1.cnf

6、日志查看  
tailf /var/log/masterha/app1/manager.log

7、开启MHA Manager监控  
nohup masterha\_manager --conf=/etc/masterha/app1.cnf --remove\_dead\_master\_conf --ignore\_last\_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 &

8、查询manger程序

ps -ef|grep perl  
1.15 安装配置LVS+keepalived  
192.168.52.251（backup\_master）和192.168.52.252（slave）  
\[root@db251 ~\]\# vi /etc/rc.d/init.d/realserver  
\#!/bin/sh  
**\# chkconfig: - 80 90**  
\# description:realserver  
\# mysql\_vip start realserver\*  
*mysql\_vip=192.168.52.199  
. /etc/rc.d/init.d/functions  
case "$1" in  
start)  
ifconfig lo:0 $mysql\_vip netmask 255.255.255.255 broadcast $mysql\_vip  
/sbin/route add -host $mysql\_vip dev lo:0  
echo "1" >/proc/sys/net/ipv4/conf/lo/arp\_ignore  
echo "2" >/proc/sys/net/ipv4/conf/lo/arp\_announce  
echo "1" >/proc/sys/net/ipv4/conf/all/arp\_ignore  
echo "2" >/proc/sys/net/ipv4/conf/all/arp\_announce  
sysctl -p >/dev/null 2>&1  
echo "RealServer Start OK"  
;;  
stop)  
ifconfig lo:0 down  
route del $mysql\_vip >/dev/null 2>&1  
echo "0" >/proc/sys/net/ipv4/conf/lo/arp\_ignore  
echo "0" >/proc/sys/net/ipv4/conf/lo/arp\_announce  
echo "0" >/proc/sys/net/ipv4/conf/all/arp\_ignore  
echo "0" >/proc/sys/net/ipv4/conf/all/arp\_announce  
echo "RealServer Stoped"  
;;*  
)  
echo "Usage: $0 \{start|stop\}"  
exit 1  
esac  
exit 0

chmod +x /etc/rc.d/init.d/realserver \#添加脚本执行权限  
chkconfig realserver on \#添加开机启动  
/etc/rc.d/init.d/realserver start \#开启，参数stop为关闭

Lvs01-192.168.52.248,配置文件249和248一致  
\[root@lvs248 ~\]\# yum install ipvsadm keepalived gcc gcc-c++ make openssl-devel kernel-devel ncurses-devel -y  
\[root@lvs248 ~\]\# lsmod |grep ip\_vs  
ip\_vs 141432 0  
nf\_conntrack 133053 1 ip\_vs  
libcrc32c 12644 3 xfs,ip\_vs,nf\_conntrack  
\[root@lvs248 ~\]\# cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf-bak  
\[root@lvs248 ~\]\# >/etc/keepalived/keepalived.conf  
\[root@lvs248 ~\]\# vim /etc/keepalived/keepalived.conf

state MASTER
        interface ens33
        virtual_router_id 51
        priority 100
        advert_int 1
        authentication {
        auth_type PASS
        auth_pass 1111
        }
    
        virtual_ipaddress {
                192.168.52.159
        }

virtual\_server 192.168.52.159 3306 \{

delay_loop 6
                lb_algo wlc
                lb_kind DR
                #persistence_timeout 120
                protocol TCP
    
        real_server 192.168.52.252 3306 {
    
                weight 1
                TCP_CHECK {
                        connect_timeout 3
                        nb_get_retry 3
                        delay_before_retry 3
                        connect_port 3306
                }
                MISC_CHECK {
                         misc_path "/etc/keepalived/check_slave.py 192.168.52.252 3306"
                         misc_dynamic

\}  
\}

real_server 192.168.52.253 3306 {
    
                weight 1
                TCP_CHECK {
                        connect_timeout 3
                        nb_get_retry 3
                        delay_before_retry 3
                        connect_port 3306
        }
                 MISC_CHECK {
                         misc_path "/etc/keepalived/check_slave.py 192.168.52.253 3306"
                         misc_dynamic
        }
    }

\[root@lvs248 keepalived\]\# pwd  
/etc/keepalived  
\[root@lvs248 keepalived\]\# ll  
total 12  
\-rw-r--r-- 1 root root 816 Jan 11 11:05 check\_slave.py  
\-rw-r--r-- 1 root root 1489 Jan 11 11:35 keepalived.conf  
\-rw-r--r-- 1 root root 3598 Jan 11 10:01 keepalived.conf-bak

vim /etc/keepalived/check\_slave.py  
\#!/usr/bin/env python  
\#encoding:utf-8  
import MySQLdb  
import sys  
ip=sys.argv\[1\]  
user='repluser'  
pwd='123456'  
port=int(sys.argv\[2\])  
sbm=200  
Slave\_IO\_Running = ''  
Slave\_SQL\_Running = ''  
Seconds\_Behind\_Master = ''  
e=''  
try:  
conn = MySQLdb.connect(host=ip,user=user,passwd=pwd,port=port,charset='utf8')  
cur = conn.cursor()  
cur.execute('show slave status')  
db\_info = cur.fetchall()  
for n in db\_info:  
Slave\_IO\_Running = n\[10\]  
Slave\_SQL\_Running = n\[11\]  
Seconds\_Behind\_Master = n\[32\]  
cur.close()  
conn.close()  
except MySQLdb.Error,e:  
print "MySQLdb Error",e  
ife == "":  
ifdb\_info != ():  
ifSlave\_IO\_Running == "No" or Slave\_SQL\_Running == "No":  
\#print 'thread err'  
exit(1)  
else:  
ifSeconds\_Behind\_Master > sbm:  
\#print 'timeout err'  
exit(1)  
else:  
\#print 'OK'  
exit(0)  
else:  
\#print 'slave err'  
exit(1)  
else:  
\#print 'db err'  
exit(1)  
~

\[root@lvs248 keepalived\]\# systemctl start keepalived  
\[root@lvs248 keepalived\]\# systemctl status keepalived  
\[root@lvs248 keepalived\]\# ipvsadm -Ln  
IP Virtual Server version 1.2.1 (size=4096)  
Prot LocalAddress:Port Scheduler Flags  
\-> RemoteAddress:Port Forward Weight ActiveConn InActConn  
TCP 192.168.52.199:3306 wlc  
\-> 192.168.52.252:3306 Route 1 0 0  
\-> 192.168.52.253:3306 Route 1 0 0  
1.16 测试  
测试 read vip 负载均衡  
分别在从库 db252、 db253、 创建可区分的库，库名分别为  
read\_one、 read\_two  
**\# db252**  
mysql> create database read\_one;  
Query OK, 1 row affected (0.00 sec)  
mysql> show databases like 'read\_one';  
\+---------------------+  
| Database (read\_one) |  
\+---------------------+  
| read\_one |  
\+---------------------+  
1 row in set (0.00 sec)  
**\# db252 操作略**  
\[root@db251 ~\]\# mysql -uroot -p123456 -h 192.168.52.159 -P3306 -e "show databases"|egrep -v "*schema|mysql|test|Database"  
mysql: \[Warning\] Using a password on the command line interface can be insecure.  
read\_one  
read\_two  
sys  
\[root@db251 ~\]\# mysql -uroot -p123456 -h 192.168.52.159 -P3306 -e "show databases"|egrep -v "*schema|mysql|test|Database"  
mysql: \[Warning\] Using a password on the command line interface can be insecure.  
read\_one  
sys

测试从库故障被剔除，恢复被挂起  
将 db253 数据库关闭，观察 lvs 状态  
\#lvs 未关闭 mysql 时的状态  
\[root@master1 keepalived\]\# ipvsadm -Ln  
IP Virtual Server version 1.2.1 (size=4096)  
Prot LocalAddress:Port Scheduler Flags  
\-> RemoteAddress:Port Forward Weight ActiveConn InActConn  
TCP 192.168.52.159:3306 wlc  
\-> 192.168.52.252:3306 Route 1 0 0  
\-> 192.168.52.253:3306 Route 1 0 0  
\[root@master1 keepalived\]\# msyql -uroot -h 192.168.52.159 -p  
\-bash: msyql: command not found  
\[root@master1 keepalived\]\# mysql -uroot -h 192.168.52.159 -p  
Enter password:  
^Z  
\[1\]+ Stopped mysql -uroot -h 192.168.52.159 -p  
\#lvs 关闭 mysql 后的状态  
\[root@master1 keepalived\]\# ipvsadm -Ln  
IP Virtual Server version 1.2.1 (size=4096)  
Prot LocalAddress:Port Scheduler Flags  
\-> RemoteAddress:Port Forward Weight ActiveConn InActConn  
TCP 192.168.52.159:3306 wlc  
\-> 192.168.52.252:3306 Route 1 0 0

注：数据库开启后，会自动加入到节点中  
\[root@master1 keepalived\]\# ipvsadm -Ln  
IP Virtual Server version 1.2.1 (size=4096)  
Prot LocalAddress:Port Scheduler Flags  
\-> RemoteAddress:Port Forward Weight ActiveConn InActConn  
TCP 192.168.52.159:3306 wlc  
\-> 192.168.52.252:3306 Route 1 0 0  
\-> 192.168.52.253:3306 Route 1 0 0

测试 keepalived 高可用 vip 切换  
192.168.52.248  
\[root@lvs248 keepalived\]\# systemctl stop keepalived

192.168.52.249  
\[root@lvs249 ~\]\# ipvsadm -Ln  
IP Virtual Server version 1.2.1 (size=4096)  
Prot LocalAddress:Port Scheduler Flags  
\-> RemoteAddress:Port Forward Weight ActiveConn InActConn  
TCP 192.168.52.159:3306 wlc  
\-> 192.168.52.252:3306 Route 1 0 0  
\-> 192.168.52.253:3306 Route 1 0 0

192.168.52.249  
\[root@lvs249 keepalived\]\# systemctl stop keepalived

192.168.52.248  
\[root@lvs248 ~\]\# ipvsadm -Ln  
IP Virtual Server version 1.2.1 (size=4096)  
Prot LocalAddress:Port Scheduler Flags  
\-> RemoteAddress:Port Forward Weight ActiveConn InActConn  
TCP 192.168.52.159:3306 wlc  
\-> 192.168.52.252:3306 Route 1 0 0  
\-> 192.168.52.253:3306 Route 1 0 0

1.17 报错日志和解决方法  
报错日志：  
\[root@ MHA\]\# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog  
12345678910111213 Binlog found at /log/mysql, up to mysql-bin.000009  
Tue Apr 5 22:43:55 2016 - \[info\] Binlog setting check done.  
Tue Apr 5 22:43:55 2016 - \[info\] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..  
Tue Apr 5 22:43:55 2016 - \[info\] Executing command : apply\_diff\_relay\_logs --command=test --slave\_user='root' --slave\_host=192.168.1.248 --slave\_ip=192.168.1.248 --slave\_port=4008 --workdir=/usr/local/mha --target\_version=5.6.16-log --manager\_version=0.57 --relay\_log\_info=/data/mysql/relay-log.info --relay\_dir=/data/mysql/ --slave\_pass=xxx  
Tue Apr 5 22:43:55 2016 - \[info\] Connecting to root@192.168.1.248(192.168.1.248:22)..  
mysqlbinlog: unknown variable 'default-character-set=utf8'  
mysqlbinlog version command failed with rc 7:0, please verify PATH, LD\_LIBRARY\_PATH, and client options  
at /usr/local/bin/apply\_diff\_relay\_logs line 493  
Tue Apr 5 22:43:55 2016 - \[error\]\[/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln205\] Slaves settings check failed!  
Tue Apr 5 22:43:55 2016 - \[error\]\[/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln413\] Slave configuration failed.  
Tue Apr 5 22:43:55 2016 - \[error\]\[/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln424\] Error happened on checking configurations. at /usr/local/bin/masterha\_check\_repl line 48  
Tue Apr 5 22:43:55 2016 - \[error\]\[/usr/local/lib64/perl5/MHA/MasterMonitor.pm, ln523\] Error happened on monitoring servers.  
Tue Apr 5 22:43:55 2016 - \[info\] Got exit code 1 (Not master dead).  
解决方案  
注释掉my.cnf中的  
\[client\]  
\#default-character-set=utf8  
报错2：  
\[root@db52 ~\]\# tailf /var/log/mysqld.log  
2019-01-10T03:10:01.260664Z 0 \[ERROR\] Error during --relay-log-recovery: Could not locate rotate event from the master.  
2019-01-10T03:10:01.294452Z 0 \[Note\] InnoDB: Buffer pool(s) load completed at 190110 11:10:01  
2019-01-10T03:10:01.294559Z 0 \[ERROR\] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave\_master\_info' table, consider deleting it.  
2019-01-10T03:10:01.294590Z 0 \[ERROR\] Failed to create or recover replication info repositories.  
2019-01-10T03:10:01.294599Z 0 \[Note\] Some of the channels are not created/initialized properly. Check for additional messages above. You will not be able to start replication on those channels until the issue is resolved and the server restarted.  
2019-01-10T03:10:01.301143Z 0 \[Note\] Event Scheduler: Loaded 0 events  
2019-01-10T03:10:01.301320Z 0 \[Note\] /usr/sbin/mysqld: ready for connections.  
解决方法：  
mysql> reset slave;  
Query OK, 0 rows affected (0.31 sec)  
mysql> change master to master\_host='192.168.20.52',master\_user='repluser',master\_password='123456',master\_log\_file='master-log.000015',master\_log\_pos=2923;  
Query OK, 0 rows affected, 2 warnings (0.32 sec)  
mysql> start slave;  
Query OK, 0 rows affected (0.51 sec)  
报错3：  
Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

解决方法：  
/usr//bin/mysqlbinlog --start-position=2923 /var/lib/mysql/mysql-bin.000015

转载于:https://blog.51cto.com/guoshaoliang789/2341722

[http_dev.mysql.com_get_mysql57-community-release-el7-8.noarch.rpm]: http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
[http_dl.fedoraproject.org_pub_epel_epel-release-latest-7.noarch.rpm]: http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
[https_qiniu.wsfnk.com_mha4mysql-node-0.58-0.el7.centos.noarch.rpm]: https://qiniu.wsfnk.com/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[https_qiniu.wsfnk.com_mha4mysql-manager-0.58-0.el7.centos.noarch.rpm]: https://qiniu.wsfnk.com/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
[br_305xxx7536_qq.com _ notification_email_from Alexandre.Cassen_firewall.loc smtp_server 192.168.52.251 smtp_connect_timeout 30 router_id LVS_01]: mailto:br/