这篇文章主要介绍“mysql下MHA搭建过程”，在日常操作中，相信很多人在mysql下MHA搭建过程问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”mysql下MHA搭建过程”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
环境
1主2从，manager放在从库
主库：192.168.137.201
从库（manager ）：192.168.137.202
从库：192.168.137.203
需要的软件包：
epel-release-7-10.noarch.rpm
mha4mysql-node-0.56.tar.gz
mha4mysql-manager-0.56.tar.gz
mysql-5.7.14-linux-glibc2.5-x86_64.tar.gz
主从搭建
创建用户
create user 'repl'@'%' identified by 'repl';
grant all on *.* to 'repl'@'%';
create user 'root'@'%' identified by 'root';
grant all on *.* to 'root'@'%';
建立主从
CHANGE MASTER TO
MASTER_HOST='192.168.137.201',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_PORT=3306,
master_auto_position=1;
配置ssh互相信任
c1服务器
[root@c1 ~]# ssh-keygen -t rsa
[root@c1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.201
[root@c1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.202
[root@c1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.203
c2服务器
[root@c2 ~]# ssh-keygen -t rsa
[root@c2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.201
[root@c2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.202
[root@c2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.203
c3服务器
[root@c3 ~]# ssh-keygen -t rsa
[root@c3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.201
[root@c3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.202
[root@c3 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@192.168.137.203
安装MHA软件
安装epel源（所有节点）
centos6
rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
centos7
rpm -ivh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-10.noarch.rpm
安装MHA软件
安装依赖包（所有节点）
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN net-tools -y
epel源的所有epel，epel-debuginfo，epel-source，都启用
软件下载
MHA软件下载
https://downloads.mariadb.com/MHA/
https://github.com/yotoobo/linux/tree/master/mha
安装MHA-node（所有节点）
tar zxf mha4mysql-node-0.56.tar.gz
cd mha4mysql-node-0.56
perl Makefile.PL
make && make install
安装MHA-manager（202）
tar zxf mha4mysql-manager-0.56.tar.gz
cd mha4mysql-manager-0.56
perl Makefile.PL
make && make install
配置MHA-manager-conf文件（202）
vi /data/mha/3306/log/mha.cnf
[server default]
client_bindir=/usr/local/mysql/bin/
manager_log=/data/mha/3306/log/manager.log
manager_workdir=/data/mha/3306/log
master_binlog_dir=/data/mysql/mysql3306/logs
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
report_script=/usr/local/bin/send_report
#init_conf_load_script=/usr/local/bin/load_cnf
remote_workdir=/data/mysql/mysql3306/tmp
#secondary_check_script= /usr/local/bin/masterha_secondary_check -s 192.168.137.201 -s 192.168.137.202
user=root
password=root
ping_interval=3
repl_user=repl
repl_password=repl
ssh_port=22
ssh_user=root
#max_ping_errors=40
[server1]
hostname=192.168.137.201
port=3306
[server2]
#check_repl_delay=0
hostname=192.168.137.202
port=3306
[server3]
candidate_master=1
check_repl_delay=0
hostname=192.168.137.203
port=3306
设置从库readonly（所有从库）
mysql> show global variables like "%read_only%";
mysql> flush tables with read lock;
mysql> set global read_only=1;
mysql> show global variables like "%read_only%";
拷贝脚本
cp /usr/local/mha_manager/samples/scripts/master_ip_failover /usr/local/bin/
cp /usr/local/mha_manager/samples/scripts/master_ip_online_change /usr/local/bin/
cp /usr/local/mha_manager/samples/scripts/power_manager /usr/local/bin/
cp /usr/local/mha_manager/samples/scripts/send_report /usr/local/bin/
修改master_ip_failover脚本
原脚本中无VIP切换
#!/usr/bin/env perl
# Copyright (C) 2011 DeNA Co.,Ltd.
#
# This program is free software; you can redistribute it and/or modify
# it 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
);
my $vip='192.168.137.88/24';
my $key="1";
my $ssh_start_vip ="/sbin/ifconfig ens33:$key $vip";
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,
'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 {
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();
$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 \"`;
}
sub stop_vip(){
`ssh $ssh_user\@$orig_master_host \ " $ssh_stop_vip \"`;
}
sub usage {
"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";
}
检测配置文件（202节点）
[root@c2 ~]# masterha_check_repl --conf=/data/mha/3306/log/mha.cnf
主库添加VIP
[root@c1 ~]# ifconfig ens33:1 192.168.137.88/16
或者/sbin/ip addr add 192.168.137.88/32 dev ens33
删除VIP ifconfig ens33:1 down
/sbin/ip addr del 192.168.137.88/32 dev ens33
MHA manager启动和关闭
[root@c2 ~]# nohup masterha_manager --conf=/data/mha/3306/log/mha.cnf < /dev/null > /data/mha/3306/log/manager.log 2>&1 &
[root@c2 ~]# masterha_stop --conf=/data/mha/3306/log/mha.cnf
注意项
所有主从数据完全一致，包含业务数据和系统表数据，不然会导致故障切换途中，无法切到新主库中
切换VIP时，需要各个服务器都有net-tools包，能够执行ifconfig
到此，关于“mysql下MHA搭建过程”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
