溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
? CentOS7+MySQL 5.7 + GTID 業(yè)務(wù)系統(tǒng)：mainBusiness
? node1 : 192.168.1.109 port:3109
? node2 : 192.168.1.110 port:3110
? VIP :192.168.1.88
? manager:192.168.1.8
? 除了galera cluster(Mariadb Cluster,GroupReplication,PXC)和KeepAlived之外，業(yè)界廣泛使用的MySQL高可用就是MHA架構(gòu)了。
? MHA作者在離開DeNA加入facebook后就極少更新了這個工具了。
? rpm包安裝的方式最簡單，但是作者在27天前增加了對從庫上啟用了super-read-only參數(shù)的優(yōu)化，簡而言之就是：當開啟這個參數(shù)后，有可能會發(fā)生配置文件中的用戶無法對差異事務(wù)進行應(yīng)用的問題。于是增加了判斷super-read-only參數(shù)是否開啟的邏輯判斷，若開啟，則先關(guān)閉此參數(shù)，然后進行應(yīng)用差異事務(wù)然后重新開啟。
? 所以這里我們采用編譯Github上最新的代碼的辦法進行安裝。地址為：
? https://github.com/yoshinorim/mha4mysql-manager
? https://github.com/yoshinorim/mha4mysql-node
#創(chuàng)建工作文件夾
mkdir -p /data/mha/
#下載源碼包
wget https://codeload.github.com/yoshinorim/mha4mysql-node/zip/master -O /usr/local/src/mha-node.zip
wget https://codeload.github.com/yoshinorim/mha4mysql-manager/zip/master -O /usr/local/src/mha-manager.zip
#解壓
unzip /usr/local/src/mha-manager.zip -d /usr/local/src
unzip /usr/local/src/mha-node.zip -d /usr/local/src
#安裝perl及其相關(guān)依賴
yum -y install perl perl-ExtUtils-MakeMaker perl-ExtUtils-CBuilder perl-Parallel-ForkManager perl-Config-Tiny
yum -y install 'perl(inc::Module::Install)'
yum -y install 'perl(Test::Without::Module)'
yum -y install 'perl(Log::Dispatch)'
#編譯管理端
cd /usr/local/src/mha4mysql-manager-master/
perl Makefile.PL #提示時輸入y
make &&make install
#編譯節(jié)點端
cd /usr/local/src/mha4mysql-node-master/
perl Makefile.PL
make &&make install
#更改配置文件
cp /usr/
#在數(shù)據(jù)庫中創(chuàng)建用于MHA系統(tǒng)工作的管理員權(quán)限賬號
#node1
mysql --login-path=3109 -e 'create user mha@'192.168.1.8' identified by 'sa123456''
mysql --login-path=3109 -e 'grant all privileges on *.* to mha@'192.168.1.8''
mysql --login-path=3109 -e 'flush privilges'
#node2
mysql --login-path=3110 -e 'create user mha@'192.168.1.8' identified by 'sa123456''
mysql --login-path=3110 -e 'grant all privileges on *.* to mha@'192.168.1.8''
mysql --login-path=3110 -e 'flush privilges'#創(chuàng)建工作目錄
mkdir -p /data/mha/mainBusiness
#下載源碼包
wget https://codeload.github.com/yoshinorim/mha4mysql-node/zip/master -O /usr/local/src/mha-node.zip
wget https://codeload.github.com/yoshinorim/mha4mysql-manager/zip/master -O /usr/local/src/mha-manager.zip
#解壓
unzip /usr/local/src/mha-manager.zip -d /usr/local/src
unzip /usr/local/src/mha-node.zip -d /usr/local/src
#安裝perl及其相關(guān)依賴
yum -y install perl perl-ExtUtils-MakeMaker perl-ExtUtils-CBuilder perl-Parallel-ForkManager perl-Config-Tiny
yum -y install 'perl(inc::Module::Install)'
yum -y install 'perl(Test::Without::Module)'
yum -y install 'perl(Log::Dispatch)'
#編譯管理端
cd /usr/local/src/mha4mysql-manager-master/
perl Makefile.PL #提示時輸入y
make &&make install
#編譯節(jié)點端
cd /usr/local/src/mha4mysql-node-master/
perl Makefile.PL
make &&make install
#創(chuàng)建配置文件
cp /usr/local/src/mha4mysql-manager-master/samples/conf/* /etc/mha
mv /etc/mha/app1.cnf /etc/mha/mainBusiness.cnfmanager_workdir=/data/mha/mainBusiness #設(shè)置MHA的工作目錄
manager_log=/data/mha/mainBusiness/manager.log #MHA manager的日志輸出
remote_workdir=/data/mha/ #預(yù)設(shè)MHA node端的工作目錄
master_binlog_dir= /data/mysql/3109/log/,/data/mysql/3110/log/ #預(yù)設(shè)MHA node端的binlog目錄
#secondary_check_script= masterha_secondary_check -s 192.168.1.109 -s 192.168.1.110
master_ip_failover_script= /etc/mha/master_ip_failover #故障時VIP更改
master_ip_online_change_script= /etc/mha/master_ip_online_change #在線切換時VIP更改
report_script= /etc/mha/send_report #報警腳本
ping_interval=1 #設(shè)置MHA manager的檢測間隔（1秒）
[server1]
hostname=192.168.1.109
port=3109
ssh_user=root
ssh_port=22
candidate_master=1 #設(shè)置該節(jié)點是否可以提升為主，1為是,0否
check_repl_delay=0 #發(fā)生故障后是否檢查本實例主從落后程度,0否,1是
[server2]
hostname=192.168.1.110
port=3110
ssh_user=root
ssh_port=22
candidate_master=1 #設(shè)置該節(jié)點是否可以提升為主，1為是,0否
check_repl_delay=0 #發(fā)生故障后是否檢查本實例主從落后程度,0否,1是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.mxhichina.com';
my $mail_from='zabbix@xxx.com';
my $mail_user='zabbix@xxx.com';
my $mail_pass='123456';
my $mail_to=['psyduck007@outlook.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,
);
# Do whatever you want here
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;
}
cat >/etc/auto_clean_relay_log.sh<<EOF
#!/bin/sh
USER=proxysql
PASSWORD=123456
PORT=3110
log_dir='/data/mha'
work_dir='/data/mha'
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 >> $log_dir/purge_relay_logs.log 2>&1crontab -e
0 0 */3 * * sh /etc/auto_clean_relay_log.sh
vim /etc/mha/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
use MHA::DBHelper;
#自定義該組機器的vip
my $vip = "192.168.1.100";
my $if = "eth0";
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
);
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,
);
sub add_vip {
my $output1 = `ssh -o ConnectTimeout=15 -o ConnectionAttempts=3 $orig_master_host /sbin/ip addr del $vip/32 dev $if`;
my $output2 = `ssh -o ConnectTimeout=15 -o ConnectionAttempts=3 $new_master_host /sbin/ip addr add $vip/32 dev $if`;
}
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 {
# 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 {
my $new_master_handler = new MHA::DBHelper();
# args: hostname, port, user, password, raise_error_or_not
$new_master_handler->connect( $new_master_ip, $new_master_port,
$new_master_user, $new_master_password, 1 );
## Set read_only=0 on the new master
$new_master_handler->disable_log_bin_local();
print "Set read_only=0 on the new master.\n";
$new_master_handler->disable_read_only();
## Creating an app user on the new master
#print "Creating app user on the new master..\n";
#FIXME_xxx_create_user( $new_master_handler->{dbh} );
$new_master_handler->enable_log_bin_local();
$new_master_handler->disconnect();
## Update master ip on the catalog database, etc
&add_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
# If you want to continue failover, exit 10.
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
# do nothing
exit 0;
}
else {
&usage();
exit 1;
}
}
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
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
use MHA::DBHelper;
use MHA::NodeUtil;
use Time::HiRes qw( sleep gettimeofday tv_interval );
use Data::Dumper;
my $_tstart;
my $_running_interval = 0.1;
#添加vip定義
my $vip = "192.168.1.100";
my $if = "eth0";
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 drop_vip {
my $output = `ssh -o ConnectTimeout=15 -o ConnectionAttempts=3 $orig_master_host /sbin/ip addr del $vip/32 dev $if`;
#mysql里的連接全部干掉
#FIXME
}
sub add_vip {
my $output = `ssh -o ConnectTimeout=15 -o ConnectionAttempts=3 $new_master_host /sbin/ip addr add $vip/32 dev $if`;
}
sub current_time_us {
my ( $sec, $microsec ) = gettimeofday();
my $curdate = localtime($sec);
return $curdate . " " . sprintf( "%06d", $microsec );
}
sub sleep_until {
my $elapsed = tv_interval($_tstart);
if ( $_running_interval > $elapsed ) {
sleep( $_running_interval - $elapsed );
}
}
sub get_threads_util {
my $dbh = shift;
my $my_connection_id = shift;
my $running_time_threshold = shift;
my $type = shift;
$running_time_threshold = 0 unless ($running_time_threshold);
$type = 0 unless ($type);
my @threads;
my $sth = $dbh->prepare("SHOW PROCESSLIST");
$sth->execute();
while ( my $ref = $sth->fetchrow_hashref() ) {
my $id = $ref->{Id};
my $user = $ref->{User};
my $host = $ref->{Host};
my $command = $ref->{Command};
my $state = $ref->{State};
my $query_time = $ref->{Time};
my $info = $ref->{Info};
$info =~ s/^\s*(.*?)\s*$/$1/ if defined($info);
next if ( $my_connection_id == $id );
next if ( defined($query_time) && $query_time < $running_time_threshold );
next if ( defined($command) && $command eq "Binlog Dump" );
next if ( defined($user) && $user eq "system user" );
next
if ( defined($command)
&& $command eq "Sleep"
&& defined($query_time)
&& $query_time >= 1 );
if ( $type >= 1 ) {
next if ( defined($command) && $command eq "Sleep" );
next if ( defined($command) && $command eq "Connect" );
}
if ( $type >= 2 ) {
next if ( defined($info) && $info =~ m/^select/i );
next if ( defined($info) && $info =~ m/^show/i );
}
push @threads, $ref;
}
return @threads;
}
sub main {
if ( $command eq "stop" ) {
## Gracefully killing connections on the current master
# 1. Set read_only= 1 on the new master
# 2. DROP USER so that no app user can establish new connections
# 3. Set read_only= 1 on the current master
# 4. Kill current queries
# * Any database access failure will result in script die.
my $exit_code = 1;
eval {
## Setting read_only=1 on the new master (to avoid accident)
my $new_master_handler = new MHA::DBHelper();
# args: hostname, port, user, password, raise_error(die_on_error)_or_not
$new_master_handler->connect( $new_master_ip, $new_master_port,
$new_master_user, $new_master_password, 1 );
print current_time_us() . " Set read_only on the new master.. ";
$new_master_handler->enable_read_only();
if ( $new_master_handler->is_read_only() ) {
print "ok.\n";
}
else {
die "Failed!\n";
}
$new_master_handler->disconnect();
# Connecting to the orig master, die if any database error happens
my $orig_master_handler = new MHA::DBHelper();
$orig_master_handler->connect( $orig_master_ip, $orig_master_port,
$orig_master_user, $orig_master_password, 1 );
## Drop application user so that nobody can connect. Disabling per-session binlog beforehand
$orig_master_handler->disable_log_bin_local();
# print current_time_us() . " Drpping app user on the orig master..\n";
print current_time_us() . " drop vip $vip..\n";
#drop_app_user($orig_master_handler);
&drop_vip();
## Waiting for N * 100 milliseconds so that current connections can exit
my $time_until_read_only = 15;
$_tstart = [gettimeofday];
my @threads = get_threads_util( $orig_master_handler->{dbh},
$orig_master_handler->{connection_id} );
while ( $time_until_read_only > 0 && $#threads >= 0 ) {
if ( $time_until_read_only % 5 == 0 ) {
printf
"%s Waiting all running %d threads are disconnected.. (max %d milliseconds)\n",
current_time_us(), $#threads + 1, $time_until_read_only * 100;
if ( $#threads < 5 ) {
print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n"
foreach (@threads);
}
}
sleep_until();
$_tstart = [gettimeofday];
$time_until_read_only--;
@threads = get_threads_util( $orig_master_handler->{dbh},
$orig_master_handler->{connection_id} );
}
## Setting read_only=1 on the current master so that nobody(except SUPER) can write
print current_time_us() . " Set read_only=1 on the orig master.. ";
$orig_master_handler->enable_read_only();
if ( $orig_master_handler->is_read_only() ) {
print "ok.\n";
}
else {
die "Failed!\n";
}
## Waiting for M * 100 milliseconds so that current update queries can complete
my $time_until_kill_threads = 5;
@threads = get_threads_util( $orig_master_handler->{dbh},
$orig_master_handler->{connection_id} );
while ( $time_until_kill_threads > 0 && $#threads >= 0 ) {
if ( $time_until_kill_threads % 5 == 0 ) {
printf
"%s Waiting all running %d queries are disconnected.. (max %d milliseconds)\n",
current_time_us(), $#threads + 1, $time_until_kill_threads * 100;
if ( $#threads < 5 ) {
print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n"
foreach (@threads);
}
}
sleep_until();
$_tstart = [gettimeofday];
$time_until_kill_threads--;
@threads = get_threads_util( $orig_master_handler->{dbh},
$orig_master_handler->{connection_id} );
}
## Terminating all threads
print current_time_us() . " Killing all application threads..\n";
$orig_master_handler->kill_threads(@threads) if ( $#threads >= 0 );
print current_time_us() . " done.\n";
$orig_master_handler->enable_log_bin_local();
$orig_master_handler->disconnect();
## After finishing the script, MHA executes FLUSH TABLES WITH READ LOCK
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
## Activating master ip on the new master
# 1. Create app user with write privileges
# 2. Moving backup script if needed
# 3. Register new master's ip to the catalog database
# We don't return error even though activating updatable accounts/ip failed so that we don't interrupt slaves' recovery.
# If exit code is 0 or 10, MHA does not abort
my $exit_code = 10;
eval {
my $new_master_handler = new MHA::DBHelper();
# args: hostname, port, user, password, raise_error_or_not
$new_master_handler->connect( $new_master_ip, $new_master_port,
$new_master_user, $new_master_password, 1 );
## Set read_only=0 on the new master
$new_master_handler->disable_log_bin_local();
print current_time_us() . " Set read_only=0 on the new master.\n";
$new_master_handler->disable_read_only();
## Creating an app user on the new master
#print current_time_us() . " Creating app user on the new master..\n";
print current_time_us() . "Add vip $vip on $if..\n";
# create_app_user($new_master_handler);
&add_vip();
$new_master_handler->enable_log_bin_local();
$new_master_handler->disconnect();
## Update master ip on the catalog database, etc
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
# do nothing
exit 0;
}
else {
&usage();
exit 1;
}
}
sub usage {
print
"Usage: master_ip_online_change --command=start|stop|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";
die;
}
? 至此：安裝與配置已經(jīng)完全結(jié)束，開始進入運行環(huán)節(jié)
masterha_manager --conf=/etc/mha/mainBusiness.cnf &? 檢查masterha_manager運行情況：
masterha_check_status --conf=/etc/mha/mainBusiness.cnf? 我們將主庫關(guān)機，進行觀測整個故障轉(zhuǎn)移的過程
[g:\~]$ Connecting to 192.168.1.110:22...
Could not connect to '192.168.1.110' (port 22): Connection failed.
Type `help' to learn how to use Xshell prompt.? manager端輸出如下：
Sun Feb 4 12:26:46 2018 - [info] Starting ping health check on 192.168.1.110(192.168.1.110:3110)..
Sun Feb 4 12:26:46 2018 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..
Sun Feb 4 12:30:41 2018 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away)
Sun Feb 4 12:30:41 2018 - [info] Executing SSH check script: exit 0
Sun Feb 4 12:30:41 2018 - [warning] HealthCheck: SSH to 192.168.1.110 is NOT reachable.
Sun Feb 4 12:30:43 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.1.110' (4))
Sun Feb 4 12:30:43 2018 - [warning] Connection failed 2 time(s)..
Sun Feb 4 12:30:44 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.1.110' (4))
Sun Feb 4 12:30:44 2018 - [warning] Connection failed 3 time(s)..
Sun Feb 4 12:30:45 2018 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.1.110' (4))
Sun Feb 4 12:30:45 2018 - [warning] Connection failed 4 time(s)..
Sun Feb 4 12:30:45 2018 - [warning] Master is not reachable from health checker!
Sun Feb 4 12:30:45 2018 - [warning] Master 192.168.1.110(192.168.1.110:3110) is not reachable!
Sun Feb 4 12:30:45 2018 - [warning] SSH is NOT reachable.
Sun Feb 4 12:30:45 2018 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/mha/mainBusiness.cnf again, and trying to connect to all servers to check server status..
Sun Feb 4 12:30:45 2018 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Feb 4 12:30:45 2018 - [info] Reading application default configuration from /etc/mha/mainBusiness.cnf..
Sun Feb 4 12:30:45 2018 - [info] Reading server configuration from /etc/mha/mainBusiness.cnf..
Sun Feb 4 12:30:46 2018 - [info] GTID failover mode = 1
Sun Feb 4 12:30:46 2018 - [info] Dead Servers:
Sun Feb 4 12:30:46 2018 - [info] 192.168.1.110(192.168.1.110:3110)
Sun Feb 4 12:30:46 2018 - [info] Alive Servers:
Sun Feb 4 12:30:46 2018 - [info] 192.168.1.109(192.168.1.109:3109)
Sun Feb 4 12:30:46 2018 - [info] Alive Slaves:
Sun Feb 4 12:30:46 2018 - [info] 192.168.1.109(192.168.1.109:3109) Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Feb 4 12:30:46 2018 - [info] GTID ON
Sun Feb 4 12:30:46 2018 - [info] Replicating from 192.168.1.110(192.168.1.110:3110)
Sun Feb 4 12:30:46 2018 - [info] Primary candidate for the new Master (candidate_master is set)
Sun Feb 4 12:30:46 2018 - [info] Checking slave configurations..
Sun Feb 4 12:30:46 2018 - [info] Checking replication filtering settings..
Sun Feb 4 12:30:46 2018 - [info] Replication filtering check ok.
Sun Feb 4 12:30:46 2018 - [info] Master is down!
Sun Feb 4 12:30:46 2018 - [info] Terminating monitoring script.
Sun Feb 4 12:30:46 2018 - [info] Got exit code 20 (Master dead).
Sun Feb 4 12:30:46 2018 - [info] MHA::MasterFailover version 0.57.
Sun Feb 4 12:30:46 2018 - [info] Starting master failover.
Sun Feb 4 12:30:46 2018 - [info]
Sun Feb 4 12:30:46 2018 - [info] * Phase 1: Configuration Check Phase..
Sun Feb 4 12:30:46 2018 - [info]
Sun Feb 4 12:30:47 2018 - [info] GTID failover mode = 1
Sun Feb 4 12:30:47 2018 - [info] Dead Servers:
Sun Feb 4 12:30:47 2018 - [info] 192.168.1.110(192.168.1.110:3110)
Sun Feb 4 12:30:47 2018 - [info] Checking master reachability via MySQL(double check)...
Sun Feb 4 12:30:48 2018 - [info] ok.
Sun Feb 4 12:30:48 2018 - [info] Alive Servers:
Sun Feb 4 12:30:48 2018 - [info] 192.168.1.109(192.168.1.109:3109)
Sun Feb 4 12:30:48 2018 - [info] Alive Slaves:
Sun Feb 4 12:30:48 2018 - [info] 192.168.1.109(192.168.1.109:3109) Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Feb 4 12:30:48 2018 - [info] GTID ON
Sun Feb 4 12:30:48 2018 - [info] Replicating from 192.168.1.110(192.168.1.110:3110)
Sun Feb 4 12:30:48 2018 - [info] Primary candidate for the new Master (candidate_master is set)
Sun Feb 4 12:30:48 2018 - [info] Starting GTID based failover.
Sun Feb 4 12:30:48 2018 - [info]
Sun Feb 4 12:30:48 2018 - [info] ** Phase 1: Configuration Check Phase completed.
Sun Feb 4 12:30:48 2018 - [info]
Sun Feb 4 12:30:48 2018 - [info] * Phase 2: Dead Master Shutdown Phase..
Sun Feb 4 12:30:48 2018 - [info]
Sun Feb 4 12:30:48 2018 - [info] Forcing shutdown so that applications never connect to the current master..
Sun Feb 4 12:30:48 2018 - [info] Executing master IP deactivation script:
Sun Feb 4 12:30:48 2018 - [info] /etc/mha/master_ip_failover --orig_master_host=192.168.1.110 --orig_master_ip=192.168.1.110 --orig_master_port=3110 --command=stop
Sun Feb 4 12:30:48 2018 - [info] done.
Sun Feb 4 12:30:48 2018 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Sun Feb 4 12:30:48 2018 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Sun Feb 4 12:30:48 2018 - [info]
Sun Feb 4 12:30:48 2018 - [info] * Phase 3: Master Recovery Phase..
Sun Feb 4 12:30:48 2018 - [info]
Sun Feb 4 12:30:48 2018 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Sun Feb 4 12:30:48 2018 - [info]
Sun Feb 4 12:30:48 2018 - [info] The latest binary log file/position on all slaves is 3110binlog.000073:234
Sun Feb 4 12:30:48 2018 - [info] Latest slaves (Slaves that received relay log files to the latest):
Sun Feb 4 12:30:48 2018 - [info] 192.168.1.109(192.168.1.109:3109) Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Feb 4 12:30:48 2018 - [info] GTID ON
Sun Feb 4 12:30:48 2018 - [info] Replicating from 192.168.1.110(192.168.1.110:3110)
Sun Feb 4 12:30:48 2018 - [info] Primary candidate for the new Master (candidate_master is set)
Sun Feb 4 12:30:48 2018 - [info] The oldest binary log file/position on all slaves is 3110binlog.000073:234
Sun Feb 4 12:30:48 2018 - [info] Oldest slaves:
Sun Feb 4 12:30:48 2018 - [info] 192.168.1.109(192.168.1.109:3109) Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Feb 4 12:30:48 2018 - [info] GTID ON
Sun Feb 4 12:30:48 2018 - [info] Replicating from 192.168.1.110(192.168.1.110:3110)
Sun Feb 4 12:30:48 2018 - [info] Primary candidate for the new Master (candidate_master is set)
Sun Feb 4 12:30:48 2018 - [info]
Sun Feb 4 12:30:48 2018 - [info] * Phase 3.3: Determining New Master Phase..
Sun Feb 4 12:30:48 2018 - [info]
Sun Feb 4 12:30:48 2018 - [info] Searching new master from slaves..
Sun Feb 4 12:30:48 2018 - [info] Candidate masters from the configuration file:
Sun Feb 4 12:30:48 2018 - [info] 192.168.1.109(192.168.1.109:3109) Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sun Feb 4 12:30:48 2018 - [info] GTID ON
Sun Feb 4 12:30:48 2018 - [info] Replicating from 192.168.1.110(192.168.1.110:3110)
Sun Feb 4 12:30:48 2018 - [info] Primary candidate for the new Master (candidate_master is set)
Sun Feb 4 12:30:48 2018 - [info] Non-candidate masters:
Sun Feb 4 12:30:48 2018 - [info] Searching from candidate_master slaves which have received the latest relay log events..
Sun Feb 4 12:30:48 2018 - [info] New master is 192.168.1.109(192.168.1.109:3109)
Sun Feb 4 12:30:48 2018 - [info] Starting master failover..
Sun Feb 4 12:30:48 2018 - [info]
From:
192.168.1.110(192.168.1.110:3110) (current master)
+--192.168.1.109(192.168.1.109:3109)
To:
192.168.1.109(192.168.1.109:3109) (new master)
Sun Feb 4 12:30:48 2018 - [info]
Sun Feb 4 12:30:48 2018 - [info] * Phase 3.3: New Master Recovery Phase..
Sun Feb 4 12:30:48 2018 - [info]
Sun Feb 4 12:30:48 2018 - [info] Waiting all logs to be applied..
Sun Feb 4 12:30:48 2018 - [info] done.
Sun Feb 4 12:30:48 2018 - [info] Getting new master's binlog name and position..
Sun Feb 4 12:30:48 2018 - [info] 3109binlog.000089:234
Sun Feb 4 12:30:48 2018 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.1.109', MASTER_PORT=3109, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';
Sun Feb 4 12:30:48 2018 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: 3109binlog.000089, 234, 2285de8a-9bc2-11e7-8a15-000c298e9a6f:1-14,
28ea40ab-9bbd-11e7-8cd1-000c29c31069:1-15308048
Sun Feb 4 12:30:48 2018 - [info] Executing master IP activate script:
Sun Feb 4 12:30:48 2018 - [info] /etc/mha/master_ip_failover --command=start --ssh_user=root --orig_master_host=192.168.1.110 --orig_master_ip=192.168.1.110 --orig_master_port=3110 --new_master_host=192.168.1.109 --new_master_ip=192.168.1.109 --new_master_port=3109 --new_master_user='mha' --new_master_password=xxx
Set read_only=0 on the new master.
ssh: connect to host 192.168.1.110 port 22: Connection timed out
Sun Feb 4 12:31:35 2018 - [info] OK.
Sun Feb 4 12:31:35 2018 - [info] ** Finished master recovery successfully.
Sun Feb 4 12:31:35 2018 - [info] * Phase 3: Master Recovery Phase completed.
Sun Feb 4 12:31:35 2018 - [info]
Sun Feb 4 12:31:35 2018 - [info] * Phase 4: Slaves Recovery Phase..
Sun Feb 4 12:31:35 2018 - [info]
Sun Feb 4 12:31:35 2018 - [info]
Sun Feb 4 12:31:35 2018 - [info] * Phase 4.1: Starting Slaves in parallel..
Sun Feb 4 12:31:35 2018 - [info]
Sun Feb 4 12:31:35 2018 - [info] All new slave servers recovered successfully.
Sun Feb 4 12:31:35 2018 - [info]
Sun Feb 4 12:31:35 2018 - [info] * Phase 5: New master cleanup phase..
Sun Feb 4 12:31:35 2018 - [info]
Sun Feb 4 12:31:35 2018 - [info] Resetting slave info on the new master..
Sun Feb 4 12:31:35 2018 - [info] 192.168.1.109: Resetting slave info succeeded.
Sun Feb 4 12:31:35 2018 - [info] Master failover to 192.168.1.109(192.168.1.109:3109) completed successfully.
Sun Feb 4 12:31:35 2018 - [info]
----- Failover Report -----
mainBusiness: MySQL Master failover 192.168.1.110(192.168.1.110:3110) to 192.168.1.109(192.168.1.109:3109) succeeded
Master 192.168.1.110(192.168.1.110:3110) is down!
Check MHA Manager logs at Client-Cent7-IP008:/data/mha/mainBusiness/manager.log for details.
Started automated(non-interactive) failover.
Invalidated master IP address on 192.168.1.110(192.168.1.110:3110)
Selected 192.168.1.109(192.168.1.109:3109) as a new master.
192.168.1.109(192.168.1.109:3109): OK: Applying all logs succeeded.
192.168.1.109(192.168.1.109:3109): OK: Activated master IP address.
192.168.1.109(192.168.1.109:3109): Resetting slave info succeeded.
Master failover to 192.168.1.109(192.168.1.109:3109) completed successfully.
Sun Feb 4 12:31:35 2018 - [info] Sending mail..
Option new_slave_hosts requires an argument #雙節(jié)點，沒有多余的從節(jié)點，所以會報錯，下面也是。
Unknown option: conf
? 1.每秒檢查MySQL，連續(xù)4次無法連上MySQL服務(wù)后，進入SSH檢查階段，SSH也不通后，確認實例故障。由于故障實例為主庫，觸發(fā)切換主庫的操作。
? 2.再次讀取配置文件信息，獲取所有注冊的實例，及其切換偏好。關(guān)閉manager節(jié)點，啟用切換腳本進行切換操作。切換操作的邏輯與之前的【MySQL】【高可用】purge_relay_logs工具的使用文章中分析的相近。??
? 3.切換主庫成功后，輸出切換報告，同時在/data/mha中生成 mainBusiness.failover.complete文件。接著在新的主庫上進行虛擬IP的掛載，發(fā)送故障報告郵件。
? 兩篇前言文章：
【MySQL】【高可用】purge_relay_logs工具的使用
【MySQL】【高可用】從masterha_master_switch工具簡單分析MHA的切換邏輯
? 1.千萬要注意定時清理relay log。我在搭建完之后，直接進行了壓測數(shù)據(jù)的寫入，大量relay log用盡了所有的硬盤空間。這時從庫MySQL服務(wù)假死，mha所有的腳本也都會因為得不到從庫的回應(yīng)也同樣卡住。
? 2.另一種簡單的send_report的腳本：
vim /etc/mha/mha_error.sh
#!/bin/sh
SYSTEM=$1
echo $1 'mha error,please check it immediately!'|mail -s 'mha error' psyduck007@outlook.com? 郵件系統(tǒng)安裝腳本：
printf "安裝mail相關(guān)軟件和服務(wù)"
yum -y install sendmail* > /dev/null 2>&1
yum -y install mailx > /dev/null 2>&1
printf "\033[32;1m%20s\033[0m\n" "[ OK ]"
printf "創(chuàng)建郵件發(fā)送配置"
cat > /etc/mail.rc<<EOF
set from=zabbix@xxx.com
set smtp=smtp.xxxx.com
set smtp-auth-user=zabbix@xxx.com
set smtp-auth-password=123456
set smtp-auth=login
EOF
printf "\033[32;1m%20s\033[0m\n" "[ OK ]"
printf "設(shè)置郵件服務(wù)自啟動"
systemctl enable sendmail > /dev/null 2>&1
printf "\033[32;1m%20s\033[0m\n" "[ OK ]"
printf "啟動郵件服務(wù)"
systemctl restart sendmail > /dev/null 2>&1
printf "\033[32;1m%20s\033[0m\n" "[ OK ]"
printf "發(fā)送測試郵件"
hostname |mail -s "test" psyduck007@outlook.com
printf "\033[32;1m%20s\033[0m\n" "[ OK ]"? 調(diào)用方式：
/etc/mha/mah_error.sh mainBusiness免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼