##################MHA安装和部署####################

1.服务器

192.168.0.21 mydb1#Master

192.168.0.22 mydb2#Slave

192.168.0.23 mydb3#MHA manager

2. 所有机器修改vim /etc/hosts

192.168.0.21mydb1

192.168.0.22mydb2

192.168.0.23mydb3

3.设置SSH公钥免密码登录

#root用户操作(/root),pwd

mkdir ~/.ssh

chmod 700 ~/.ssh

cd ~/.ssh

ssh-keygen -t rsa #一直回车

ls -al

cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys

ls -al

chmod 600 ~/.ssh/authorized_keys

cd ..

scp -r .ssh 192.168.0.22:/root/

scp -r .ssh 192.168.0.23:/root/

ssh mydb1

ssh mydb2

ssh mydb3

4.下载mha4mysql-manager和mha4mysql-node

https://github.com/yoshinorim/mha4mysql-manager

https://github.com/yoshinorim/mha4mysql-node

5.在三个节点（node 和 manager）安装perl-DBD-MySQL，用光盘作yum源，#包括了3个包：Perl-DBD-MySQL,mysql-libs,perl-DBI

yum install perl-DBD-MySQL

yum install perl-DBI

yum install mysql-libs

6.在三个节点安装node（包括管理节点）

tar xzvf mha4mysql-node-0.56.tar.gz

cd mha4mysql-node-0.56

perl Makefile.PL

make && make install

7.在管理节点安装manager

rpm -ivh perl-Config-Tiny-2.12-1.el5.rf.noarch.rpm

rpm -ivh perl-Params-Validate-0.95-1.el5.rf.i386.rpm

rpm -ivh perl-MIME-Types-1.28-2.el6.noarch.rpm

rpm -ivh perl-Email-Date-Format-1.002-5.el6.noarch.rpm

rpm -ivh perl-Mail-Sender-0.8.16-3.el6.noarch.rpm

rpm -ivh perl-Mail-Sendmail-0.79-12.el6.noarch.rpm

rpm -ivh perl-TimeDate-1.16-11.1.el6.noarch.rpm

rpm -ivh perl-MailTools-2.04-4.el6.noarch.rpm

rpm -ivh perl-MIME-Lite-3.027-2.el6.noarch.rpm

rpm -ivh perl-Log-Dispatch-2.26-1.el5.rf.noarch.rpm

rpm -ivh perl-Parallel-ForkManager-0.7.5-2.2.el5.rf.noarch.rpm

yum install perl-Time-HiRes#光盘yum源

tar xzvf mha4mysql-manager-0.56.tar.gz

cd mha4mysql-manager-0.56

perl Makefile.PL

make && make install

####################常用命令###############

/usr/local/bin/masterha_check_ssh --conf=/u01/mha/etc/app.cnf

/usr/local/bin/masterha_check_repl --conf=/u01/mha/etc/app.cnf

/usr/local/bin/masterha_manager --conf=/u01/mha/etc/app.cnf &

/u01/mha/etc/masterha_check_status --conf=/u01/mha/etc/app.cnf

/usr/local/bin/masterha_stop --conf=/u01/mha/etc/app.cnf

###################切换######################

一、failover故障切换

1.模拟主库宕机

[root@mydb3~]# ssh mydb1 "killall -r mysqld"

2.查看管理节点日志，可以看到VIP已经漂移

[root@mydb3 ~]# cat /u01/mha/log/manager.log |grep -i vip

3. 验证VIP是否位于节点mydb2

[root@mydb3 ~]# ssh mydb2 "ifconfig |grep 0.201 -B1"

4. 查看管理节点MHA切换日志

[root@mydb3 ~]# tail /u01/mha/log/manager.log

5. new master(old slave)

show master status\G

6. new slave(old:master)

(1)打开MySQL

mysqld_safe --defaults-file=/u01/my3306/my.cnf &

(2)检查数据库

show master status\G

show slave status\G

(3)在管理节点日志中查主库的日志文件和位置

cat /u01/mha/etc/app/manager.log |grep -i change

(4)在slave连接master

CHANGE MASTER TO

MASTER_HOST='192.168.0.21',

MASTER_PORT=3306,

MASTER_LOG_FILE='mysql-bin.000005',

MASTER_LOG_POS=120,

MASTER_USER='repl',

MASTER_PASSWORD='123456';

7.启动管理节点

/usr/local/bin/masterha_manager --conf=/u01/mha/etc/app.cnf &

/usr/local/bin/masterha_manager --conf=/u01/mha/etc/app.cnf --ignore_last_failover &

二、switchover线上切换

1.master：关闭event_scheduler（即192.168.0.21）

set global event_scheduler=off;

2.manager：关闭管理进程 （即192.168.0.23）

/usr/local/bin/masterha_stop --conf=/u01/mha/etc/app.cnf

3.manager：检查配置文件

/u01/mha/etc/app.cnf 有没有被修改破坏。如果破坏需要重新编辑正确配置文件：/u01/mha/etc/app.cnf

cp /u01/mha/etc/app.cnf.bak /u01/mha/etc/app.cnf

4.开始切换：

/u01/mha/etc/masterha_master_switch --master_state=alive --conf=/u01/mha/etc/app.cnf

5.new master(old slave)

mysql> show master status\G

*************************** 1. row ***************************

File: binlog.000021

Position: 299

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

6.new slave(old master)

CHANGE MASTER TO

MASTER_HOST='192.168.0.22',

MASTER_PORT=3306,

MASTER_LOG_FILE='binlog.000021',

MASTER_LOG_POS=299,

MASTER_USER='repl',

MASTER_PASSWORD='123456';

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.56.12

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000017

Read_Master_Log_Pos: 299

Relay_Log_File: relaylog.000002

Relay_Log_Pos: 280

Relay_Master_Log_File: binlog.000017

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

7.启动管理节点

/u01/mha/etc/masterha_manager --conf=/u01/mha/etc/app.cnf &

/u01/mha/etc/masterha_manager --conf=/u01/mha/etc/app.cnf --remove_dead_master_conf --ignore_last_failover

#################配置文件###################

vim /u01/mha/etc/app.cnf

[server default]

user = root

password = root123

ssh_user = root

repl_user = rep

repl_password = 123456

ping_interval = 1

ping_type = SELECT

manager_workdir=/u01/mha/etc/app

manager_log=/u01/mha/log/manager.log

remote_workdir=/u01/mha/etc/app

master_binlog_dir="/u01/my3306/log/binlog"

master_ip_failover_script="/u01/mha/etc/master_ip_failover"

master_ip_online_change_script="/u01/mha/etc/master_ip_failover"

shutdown_script=""

report_script=""

#check_repl_delay=0

[server1]

hostname=mydb1

port=3306

master_binlog_dir="/apps/dbdat/mysql5_data3306/log"

candidate_master=1

ignore_fail=1

[server2]

hostname=mydb2

port=3306

master_binlog_dir="/u01/my3306/log/binlog"

candidate_master=1

ignore_fail=1

---------------------------------------------------------------------------------

vim /u01/mha/etc/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.0.201/24'; # Virtual IP

my $key = "1";

my $int = "eth0";

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

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

my $arp_effect = "/sbin/arping -Uq -s192.168.0.201 -I $int 192.168.0.1 -c 3";# Virtual IP and gat

eway

#my $test = "echo successfull >/tmp/test.txt";

$ssh_user = "root";

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" ) {

# $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 $@;

exit $exit_code;

}

exit $exit_code;

}

elsif ( $command eq "status" ) {

print "Checking the Status of the script.. OK \n";

#`ssh $ssh_user\@cluster1 \" $ssh_start_vip \"`;

&status();

exit 0;

}

else {

&usage();

exit 1;

}

}

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

sub start_vip() {

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

`ssh $ssh_user\@$new_master_host \" $arp_effect \"`;

#`ssh $ssh_user\@$new_master_host \" $test \"`;

}

# 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 status() {

print `ssh $ssh_user\@$orig_master_host \" ip add show $int \"`;

}

sub usage {

print

"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_maste

r_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";

}