MHA安裝檔案

準備四臺服務器
192.168.126.177 manager
192.168.126.178 主
192.168.126.179 從
192.168.126.180 從

四臺服務器需要做ssh免密操作：

178、179、180需要安裝mariadb

yum -y install mariadb mariadb-server

Mariadb組態檔修改

178，主上的操作：

```bash=
server-id=1
log-bin=mysql-bin

179，從1的操作：

server-id=2
log-bin=slave1-bin
relay-log=slave1-log

180，從2上的操作：

server-id=3
log-bin=slave2-bin
relay-log=slave2-log

開啟資料庫（mariadb）

systemctl start mariadb

進入資料庫做主從復制：

178主上的操作：

grant all on *.* to 'tom'@'192.168.126.%' identified by '123';

179、180兩臺從上的操作：

change master to master_host='192.168.126.178,master_user='tom',master_password='123',master_log_file='slave1-bin.000003',master_log_pos=519;

set global read_only=1;

178、179、180主從上都需要的操作：

grant all privileges on *.* to 'tom'@'192.168.126.% identified  by '123';

四臺都做時間同步操作：

yum -y install ntpdate

ntpdate ntp.aliyun.com

四臺機器同裝node節點，一致操作:

yum -y install  perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-DBD-MySQL perl-devel perl-CPAN

mkdir -p /etc/mha
tar zxf mha4mysql-node-0.57.tar.gz -C /etc/mha/
mv /etc/mha/mha4mysql-node-0.57/ /etc/mha/node
cd  /etc/mha/node/
perl Makefile.PL
make && make install

177 安裝manager節點:

yum -y install epel-release --nogpgcheck

yum install -y perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes

tar zxf mha4mysql-manager-0.57.tar.gz -C /etc/mha/
mv /etc/mha/mha4mysql-manager-0.57/ /etc/mha/manager
cd /etc/mha/manager/
perl Makefile.PL
make && make install

[server default]
manager_workdir=/etc/mha/app1
manager_log=/etc/mha/app1/manager.log
master_binlog_dir="/var/lib/mysql"
remote_workdir=/etc/mha/app1
master_ip_failover_script=/etc/mha/master_ip_failover
master_ip_online_change_script=/etc/mha/master_ip_online_change
report_script=/etc/mha/send_report
user=tom
password=123
repl_user=tom
repl_password=123
ping_interval=1
secondary_check_script= masterha_secondary_check -s 192.168.181.168 -s 192.168.181.169


[server1]
hostname=192.168.181.142
port=3306
ssh_port=22

[server2]
hostname=192.168.181.168
port=3306
ssh_port=22
candidate_master=1
check_repl_delay=0

[server3]
hostname=192.168.181.169
port=3306
no_master=1
ssh_port=22

179,180 兩從

set global relay_log_purge=0;

mkdir /var/lib/mysql/logs1
ln /var/lib/mysql/relay-log.* /var/lib/mysql/logs1/

#!/bin/bash
user=root

passwd=123

port=3306

log_dir='/var/lib/mysql/'

work_dir='/var/lib/mysql/logs1'

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 --host=localhost --workdir=$work_dir >> $log_dir/purge_relay_logs.log 2>&1

測驗

mysqladmin -uroot password 123
purge_relay_logs --user=root --host=localhost  --port=3306 --password=123 -disable_relay_log_purge --workdir=/var/lib/mysql/

crontab -e
0 0 */3 * * sh /etc/auto_clean_relay_log.sh

177 179 180 manager+兩從

vim /etc/mha/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.181.111/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,

                );

                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";

                }

vim /etc/mha/master_ip_online_change 手動切換腳本

    #!/usr/bin/env perl

                use strict;

                use warnings FATAL =>'all';

                use Getopt::Long;

                my $vip = '192.168.181.111/24'; # Virtual IP

                my $key = "0";

                my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";

                my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";

                my $exit_code = 0;

                my (

                $command, $orig_master_is_new_slave, $orig_master_host,

                $orig_master_ip, $orig_master_port, $orig_master_user,

                $orig_master_password, $orig_master_ssh_user, $new_master_host,

                $new_master_ip, $new_master_port, $new_master_user,

                $new_master_password, $new_master_ssh_user,

                );

                GetOptions(

                'command=s' => \$command,

                'orig_master_is_new_slave' => \$orig_master_is_new_slave,
           'orig_master_host=s' => \$orig_master_host,

                'orig_master_ip=s' => \$orig_master_ip,

                'orig_master_port=i' => \$orig_master_port,

                'orig_master_user=s' => \$orig_master_user,

                'orig_master_password=s' => \$orig_master_password,

                'orig_master_ssh_user=s' => \$orig_master_ssh_user,

                '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,

                'new_master_ssh_user=s' => \$new_master_ssh_user,

                );

                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 "\n\n\n***************************************************************\n";

                print "Disabling the VIP - $vip on old master: $orig_master_host\n";

                print "***************************************************************\n\n\n\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 "\n\n\n***************************************************************\n";

                print "Enabling the VIP - $vip on new master: $new_master_host \n";

                print "***************************************************************\n\n\n\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 $orig_master_ssh_user\@$orig_master_host \" $ssh_start_vip \"`;

                exit 0;
 }

                else {

                &usage();

                exit 1;

                }

                }

                # A simple system call that enable the VIP on the new master

                sub start_vip() {

                `ssh $new_master_ssh_user\@$new_master_host \" $ssh_start_vip \"`;

                }

                # A simple system call that disable the VIP on the old_master

                sub stop_vip() {

                `ssh $orig_master_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=po

                rt -new_master_host=host -new_master_ip=ip -new_master_port=port\n";
 }
   

vim /etc/mha/send_report 報警腳本

    #!/usr/bin/perl

                use strict;

                use warnings FATAL => 'all';

                use Mail::Sender;

                use Getopt::Long;

                #new_master_host and new_slave_hosts are set only when recovering master succeeded

                my ( $dead_master_host, $new_master_host, $new_slave_hosts, $subject, $body );

my $smtp='smtp.qq.com';

my $mail_from='807220870@qq.com';

my $mail_user='807220870@qq.com';

                my $mail_pass='xxxxxxxxxxxx';

my $mail_to=['807220870@qq.com'];

                GetOptions(

                  'orig_master_host=s' => \$dead_master_host,

                  'new_master_host=s'  => \$new_master_host,

                  'new_slave_hosts=s'  => \$new_slave_hosts,

                  'subject=s'          => \$subject,

                  'body=s'             => \$body,

                );
   mailToContacts($smtp,$mail_from,$mail_user,$mail_pass,$mail_to,$subject,$body);

            

sub mailToContacts {

                my ( $smtp, $mail_from, $user, $passwd, $mail_to, $subject, $msg ) = @_;

                open my $DEBUG, "> /tmp/monitormail.log"

                    or die "Can't open the debug      file:$!\n";

                my $sender = new Mail::Sender {

                    ctype       => 'text/plain; charset=utf-8',

                    encoding    => 'utf-8',

                    smtp        => $smtp,

                    from        => $mail_from,

                    auth        => 'LOGIN',

                    TLS_allowed => '0',

                    authid      => $user,

                    authpwd     => $passwd,

                    to          => $mail_to,

                    subject     => $subject,

                    debug       => $DEBUG

                };

                $sender->MailMsg(
                 {   msg   => $msg,

                        debug => $DEBUG

                    }

                ) or print $Mail::Sender::Error;

                return 1;

            }

            # Do whatever you want here

            exit 0;

177manager：

 /etc/mha/manager/bin/masterha_check_ssh --conf=/etc/mha/app1.cnf

 /etc/mha/manager/bin/masterha_check_repl --conf=/etc/mha/app1.cnf

出現以下情況說明正確

178主

ifconfig ens33:0 192.168.126.111 netmask 255.255.255.0
130 manager

啟動

nohup /etc/mha/manager/bin/masterha_manager --conf=/etc/mha/app1.cnf --ignore_last_failover >/tmp/mha_manager.log < /dev/null 2>&1 &

查看狀態

/etc/mha/manager/bin/masterha_check_status --conf=/etc/mha/app1.cnf

測驗

tail -300f /etc/mha/app1/manager.log

關閉178主mysql

179服務器 從

Vip漂移到179機器，成為了新主，然后啟動舊主178的mariadb，為178和179做主從

Show master status;

178

177
修改組態檔

啟動mha:

 nohup /etc/mha/manager/bin/masterha_manager --conf=/etc/mha/app1.cnf --ignore_last_failover >/tmp/mha_manager.log < /dev/null 2>&1 &

可以查看當前主的狀態:

/etc/mha/manager/bin/masterha_check_status --conf=/etc/mha/app1.cnf

然后去關閉178mariadb:

Systemctl stop mariadb

去178服務器查看，vip漂移到了179上，179成為了新主
然后開啟178的mariadb，為178和179做主從

報錯解決方案： 報錯資訊：

解決方案：

grant all on *.* to tom@'192.168.126.%' identified by '123';