mysql5.7-centos7-MHA部署

1、实验环境介绍

OS：CentOS Linux release 7.6.1810 (Core) 

db: 5.7.30  MySQL Community Server (GPL)

ip	hostname
172.168.1.178	mysqldb1	master	Node
172.168.1.179	mysqldb2	slave1	Node
172.168.1.180	mysqldb3	slave2	Node Manager
172.168.1.188		vip

2、主从环境部署

基于GTID的半同步复制，一主两从

[root@mysqldb1 ~]# cat /etc/my.cnf
[client]
port            = 3306
socket          = /data/mysqldata/mysql.sock
default-character-set=utf8mb4

[mysql]
prompt="(\\u@\\h_\\d) [\\R:\\m]> "


[mysqld]
server_id=1
datadir=/data/mysqldata
socket=/data/mysqldata/mysql.sock

gtid_mode=on
enforce_gtid_consistency=on
log_bin=on
binlog_format=row

symbolic-links=0


[mysqld_safe]
log-error=/data/mysqldata/error.log
pid-file=/data/mysqldata/mysql.pid

!includedir /etc/my.cnf.d

[root@mysqldb2 ~]# cat /etc/my.cnf
[client]
port            = 3306
socket          = /data/mysqldata/mysql.sock
default-character-set=utf8mb4

[mysql]
prompt="(\\u@\\h_\\d) [\\R:\\m]> "

[mysqld]
server_id=2
datadir=/data/mysqldata
socket=/data/mysqldata/mysql.sock


log_bin=on
binlog_format=row

gtid_mode=on
enforce_gtid_consistency=on
log_slave_updates=1

symbolic-links=0

[mysqld_safe]
log-error=/data/mysqldata/error.log
pid-file=/data/mysqldata/mysql.pid

创建管理用户与同步用户

create user 'zs'@'%' identified by'123456';
grant  all privileges on *.* to 'zs'@'%';
flush privileges;

create user 'repl'@'%' identified by'repl';
grant replication slave on *.* to 'repl'@'%';
flush privileges;

mysqldb1 全备
/usr/local/mysql/bin/mysqldump --single-transaction -uroot -pmysql -A >all.sql
scp all.sql root@mysqldb2:/root/
scp all.sql root@mysqldb3:/root/

 

mysqldb2 mysqldb3
>mysql -uroot -pmysql
>source all.sql

change master to master_host='172.168.1.178',master_user='repl',master_password='repl',master_auto_position=1;
start slave;

3、MHA软件部署

3.1、ssh互信

[root@mysqldb1 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
172.168.1.178 mysqldb1
172.168.1.179 mysqldb2
172.168.1.180 mysqldb3

每个机器上执行
ssh-keygen -t dsa -P '' -f id_dsa
cat id_dsa.pub >> authorized_keys

scp 172.168.1.179:/root/.ssh/id_dsa.pub ./id_dsa.pub.179
scp 172.168.1.180:/root/.ssh/id_dsa.pub ./id_dsa.pub.180

cat id_dsa.pub.179 >> authorized_keys 
cat id_dsa.pub.180  >> authorized_keys 

scp authorized_keys 172.168.1.179:/root/.ssh
scp authorized_keys 172.168.1.180:/root/.ssh

测试ssh
ssh root@mysqldb2
ssh root@mysqldb3
ssh root@mysqldb1

yum源配置
[root@mysqldb1 yum.repos.d]# cat epel.repo 
[epel]
name=Extra Packages for Enterprise Linux 7 - $basearch
#baseurl=http://download.fedoraproject.org/pub/epel/7/$basearch
metalink=https://mirrors.fedoraproject.org/metalink?repo=epel-7&arch=$basearch
failovermethod=priority
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7

[epel-debuginfo]
name=Extra Packages for Enterprise Linux 7 - $basearch - Debug
#baseurl=http://download.fedoraproject.org/pub/epel/7/$basearch/debug
metalink=https://mirrors.fedoraproject.org/metalink?repo=epel-debug-7&arch=$basearch
failovermethod=priority
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
gpgcheck=1

[epel-source]
name=Extra Packages for Enterprise Linux 7 - $basearch - Source
#baseurl=http://download.fedoraproject.org/pub/epel/7/SRPMS
metalink=https://mirrors.fedoraproject.org/metalink?repo=epel-source-7&arch=$basearch
failovermethod=priority
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
gpgcheck=1

 

安装相关包

yum install -y lrzsz perl-DBD-MySQL perl-Config-Tiny* erl-Time-HiRes* erl-Params-Validate* perl-Log-Dispatch* erl-Parallel-ForkManager*   perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker cpan perl-CPAN* 

安装Node节点(3个机器)

unzip mha4mysql-node-master.zip
perl Makefile.PL
make && make install

安装manager节点（mysqldb3）

unzip mha4mysql-manager-master.zip
perl Makefile.PL
make
make install
(过程比较长，耐心等待)

配置管理节点

mkdir -p /usr/local/mha
mkdir -p /etc/mha
mkdir -p /usr/local/scripts


[root@mysqldb3 mha]# cat /etc/mha/mha.conf 
[server default]

user=zs
password=123456
manager_workdir=/usr/local/mha
manager_log=/usr/local/mha/manager.log
remote_workdir=/usr/local/mha
ssh_user=root
repl_user=repl
repl_password=repl
ping_interval=1
master_ip_failover_script=/usr/local/scripts/master_ip_failover
master_ip_online_change_script=/usr/local/scripts/master_ip_online_change

[server1]
hostname=172.168.1.178
port=3306
ssh_port=22
master_binlog_dir=/data/mysqldata
candidate_master=1

[server2]
hostname=172.168.1.179
port=3306
ssh_port=22
master_binlog_dir=/data/mysqldata
candidate_master=1

[server3]
hostname=172.168.1.180
port=3306
ssh_port=22
master_binlog_dir=/data/mysqldata
candidate_master=1



[root@mysqldb3 scripts]# cat /usr/local/scripts/master_ip_failover 
#!/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,  $new_master_user,
  $new_master_password
);

my $vip = '172.168.1.188/24';
my $key = '0';
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,
  '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