MHA deployment notes (centos6+mysql5.6)
**Environment and software version: **
CentOS6.5x86_64
MySQL5.6.34 compile and install version
MHA version: mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm
**Node role: **
node93: 10.1.20.93 default main library
node94: 10.1.20.94 slave library 1, the original main library can be upgraded to the main library after it goes down [mha management node is also deployed on this machine]
node95: 10.1.20.95 slave library 2, not allowed to be promoted to master library
The prepared VIP is 10.1.20.100/24
step1, configure the master-slave relationship##
**Key parts of node93's /etc/my.cnf configuration file: **
[ mysqld]
port = 3306
socket = /tmp/mysql.sock
datadir = /bdata/data/nowdb2
innodb_file_per_table=ON
character-set-server = utf8
default_storage_engine = InnoDB
skip-innodb_adaptive_hash_index
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1 #crash safe
log-bin=mysql-bin
binlog_format=row
sync_binlog =1 #Ensure that BINLOG is placed on the disk when the transaction is submitted
log-slave-updates
log_bin_trust_function_creators =1
binlog_rows_query_log_events=ON #Record executed statements to BINLOG query event
server-id=1020093
relay_log_purge=0
read_only=1
**Key parts of node94's /etc/my.cnf configuration file: **
[ mysqld]
port = 3306
socket = /tmp/mysql.sock
datadir = /bdata/data/nowdb2
innodb_file_per_table=ON
character-set-server = utf8
default_storage_engine = InnoDB
skip-innodb_adaptive_hash_index
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1 #crash safe
log-bin=mysql-bin
binlog_format=row
sync_binlog =1 #Ensure that BINLOG is placed on the disk when the transaction is submitted
log-slave-updates
log_bin_trust_function_creators =1
binlog_rows_query_log_events=ON #Record executed statements to BINLOG query event
server-id=1020094
relay_log_purge=0
read_only=1
**Key parts of node95's /etc/my.cnf configuration file: **
[ mysqld]
port = 3306
socket = /tmp/mysql.sock
datadir = /bdata/data/nowdb2
innodb_file_per_table=ON
character-set-server = utf8
default_storage_engine = InnoDB
skip-innodb_adaptive_hash_index
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1 #crash safe
log-bin=mysql-bin
binlog_format=row
sync_binlog =1 #Ensure that BINLOG is placed on the disk when the transaction is submitted
log-slave-updates
log_bin_trust_function_creators = 1
binlog_rows_query_log_events=ON #Record executed statements to BINLOG query event
server-id=1020095
relay_log_purge=0
read_only=1
Create an account with replication permissions on node93, GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO'rpl'@'10.1.%.%' IDENTIFIED BY'rpl';
Then configure 1 master and 2 slaves (skip the specific steps).
Note: We need to make sure that the nodes (node93, node94) that can become the master database have master-slave synchronization accounts. If the rpl account does not exist on node94, just go to the node94 node to add it manually.
After the master-slave relationship is established, we create a mha management account on the master, which will be used later:
grant all on . to 'mhauser'@'10.1.%.%' identified by 'Abcd@1234';
( The management account must exist on all nodes of node93, node94, and node95)
step2, install MHA
Because MHA relies on SSH, SSH keyless login needs to be established between 3 hosts. Step skip.
3 Install perl packages on all nodes:
yum install perl perl-DBD-MySQL perl-CPAN perl-devel perl-Time-HiRes
Install node package on node93-node95:
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
Install the Manager package on node94 (Of course, we can install the Manager package on all 3 nodes):
rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
Initialize MHA in node94
mkdir /etc/masterha/
The contents of vim /etc/masterha/app1.cnf are as follows:
[ server default]
user=mhauser
password=Abcd@1234
manager_workdir=/data/masterha/app1
manager_log=/data/masterha/app1/manager.log
remote_workdir=/data/masterha/app1
ssh_user=root
repl_user=rpl
repl_password=rpl
ping_interval=1
master_binlog_dir=/bdata/data/nowdb2/ # This path must be the same as your mysql binlog storage path
master_ip_failover_script=/etc/masterha/master_ip_failover
report_script=/etc/masterha/send_report
It is not necessary to confirm whether the target main library is alive through a third-party machine, even if it does not, it can be used
secondary_check_script=masterha_secondary_check -s remote_host1 -s remote_host2
The script to shut down the host after the failure occurs is not necessary, but you must set it to empty
shutdown_script=""
Manually switch the VIP script online, it is not necessary, even if there is no, it can be used.
If you have keepalived to switch VIP, you can just use it.
master_ip_online_change_script==/etc/masterha/master_ip_online_change
[ server1]
hostname=10.1.20.93
candidate_master=1
[ server2]
hostname=10.1.20.94
candidate_master=1
[ server3]
hostname=10.1.20.95
no_master=1 # prohibit
Add the script /etc/masterha/master_ip_failover on node94 (fill in the relevant VIP information)
#! /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,
$orig_master_ssh_port, $new_master_ssh_port
);
my $vip ='10.1.20.100'; # Virtual IP
my $devic='eth0';
my $key = "0";
my$net_mask='255.255.255.0';
my $ssh_start_vip ="/sbin/ifconfig
my $ssh_stop_vip ="/sbin/ifconfig
my$mysql_conf="/etc/my.cnf";
my $open_readonly="/bin/sed-i 's/.read_only./read_only=1/g' $mysql_conf ";
my $close_readonly="/bin/sed-i 's/.read_only./read_only=0/g' $mysql_conf ";
my $open_relaylog_purge="/bin/sed-i 's/.relay_log_purge./relay_log_purge=0/g' $mysql_conf ";
my$close_relaylog_purge="/bin/sed -i's/.relay_log_purge./relay_log_purge=0/g' $mysql_conf ";
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,
'orig_master_ssh_port=i' => $orig_master_ssh_port,
'new_master_host=s' => $new_master_host,
'new_master_ip=s' =>$new_master_ip,
'new_master_port=i' =>$new_master_port,
'new_master_ssh_port=i' => $new_master_ssh_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPTTEST====
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host,
# If you manage master ip address atglobal catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
print "Disabling the VIP onold 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 atglobal 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 - $vipon 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 thescript.. OK \n";
# ssh $ssh_user\@cluster1 \"$ssh_start_vip \";
exit 0;
}
else {
&usage();
exit 1;
}
}
A simple system call that enablethe VIP on the new master
sub start_vip() {
ssh $ssh_user\@$new_master_host \" $ssh_start_vip \";
print "Disable read_only and relay_log_purge in my.cnf - on the new master - $new_master_host\n";
ssh $ssh_user\@$new_master_host \" $close_readonly \";
ssh $ssh_user\@$new_master_host \" $close_relaylog_purge \";
}
A simple system call thatdisable the VIP on the old_master
sub stop_vip() {
ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \";
print "Enable read_only and relay_log_purge in my.cnf - on the orig master - $orig_master_host\n";
ssh $ssh_user\@$orig_master_host \" $open_readonly \";
ssh $ssh_user\@$orig_master_host \" $open_relaylog_purge \";
}
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--orig_master_ssh_port=ssh_port --new_master_ssh_port = ssh_port\n";
}
Add the script /etc/masterha/send_report on node94 (fill in the relevant smtp account information):
#! /usr/bin/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 scriptand is not complete. Modify the script based on your environment.
use strict;
use warnings FATAL => 'all';
use Mail::Sender;
use Getopt::Long;
new_master_host and new_slave_hostsare set only when recovering master succeeded
my (
my$smtp='smtp.exmail.qq.com';
my$mail_from='[email protected]';
my$mail_user='[email protected]';
my $mail_pass='xxxxxxx';
my$mail_to=['[email protected]'];
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,
'conf=s' => $conf,
);
mailToContacts(
check_if_sendmail_ok('/tmp/monitormail.log');
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;
}
sub check_if_sendmail_ok{
#>>250 2.0.0 Ok: queued as 3532C6DA009D
#<<QUIT
#>>221 2.0.0 Bye
my$logf = shift;
openRLOG, $logf or die "cannot open file $logf.\n";
my@log =
closeRLOG;
my$val = 0;
if(
print"Meet Bye.\t";
$val++;
}
if(
print"Meet QUIT.\t";
$val++;
}
if(
print"Meet queued.\t";
$val++;
}
print"\n";
if($val== 3){
print"send mail success.\n";
}
else{
print"send mail failed.check DNS/SMTP config\n";
}
return$val;
}
Do whatever you want here
exit 0;
Add the script /etc/masterha/master_ip_online_change on node94 (fill in the relevant VIP information):
#! /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 scriptand is not complete. Modify the script based on your environment.
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
use MHA::DBHelper;
use MHA::NodeUtil;
use Time::HiRes qw( sleepgettimeofday tv_interval );
use Data::Dumper;
my $_tstart;
my $_running_interval = 0.1;
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
);
my $vip ='10.1.20.100/24';
my $key = '0';
my
my
my $orig_master_ssh_port = 22;
my $new_master_ssh_port = 22;
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,
'orig_master_ssh_port=i' =>$orig_master_ssh_port,
'new_master_ssh_port=i' =>$new_master_ssh_port,
);
exit &main();
sub current_time_us {
my ( $sec, $microsec ) = gettimeofday();
my
return $curdate . " " . sprintf( "%06d", $microsec);
}
sub sleep_until {
my
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;
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
my $state = $ref->{State};
my $query_time = $ref->{Time};
my $info = $ref->{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(
next if ( defined(
}
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 = newMHA::DBHelper();
# args: hostname, port, user, password,raise_error(die_on_error)_or_not
$new_master_user, $new_master_password,1 );
print current_time_us() . " Setread_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 ifany database error happens
my $orig_master_handler = newMHA::DBHelper();
## Drop application user so that nobodycan connect. Disabling per-session binlog beforehand
$orig_master_handler->disable_log_bin_local();
print current_time_us() . " Drppingapp user on the orig master..\n";
#FIXME_xxx_drop_app_user($orig_master_handler);
## Waiting for N * 100 milliseconds sothat 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 %dthreads are disconnected.. (max %d milliseconds)\n",
current_time_us(),
if ( $#threads < 5 ) {
printData::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 currentmaster so that nobody(except SUPER) can write
print current_time_us() . " Setread_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 sothat 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 %dqueries are disconnected.. (max %d milliseconds)\n",
current_time_us(),
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() . " Killingall 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, MHAexecutes FLUSH TABLES WITH READ LOCK
eval {
ssh -p$orig_master_ssh_port$orig_master_ssh_user\@$orig_master_host \" $ssh_stop_vip \";
};
if ($@) {
warn $@;
}
$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 eventhough activating updatable accounts/ip failed so that we don't interruptslaves' recovery.
If exit code is 0 or 10, MHAdoes not abort
my $exit_code = 10;
eval {
my $new_master_handler = newMHA::DBHelper();
# args: hostname, port, user, password,raise_error_or_not
$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() . " Setread_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() . " Creatingapp user on the new master..\n";
#FIXME_xxx_create_app_user($new_master_handler);
$new_master_handler->enable_log_bin_local();
$new_master_handler->disconnect();
## Update master ip on the catalogdatabase, etc
ssh -p$new_master_ssh_port$new_master_ssh_user\@$new_master_host \" $ssh_start_vip \";
$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 {
" 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;
}
Check if MHA's SSH configuration is correct on node94:
masterha_check_ssh--conf=/etc/masterha/app1.cnf
If "All SSH connection tests passed successfully." appears, the configuration is ok
Check whether the master-slave replication of MHA is configured correctly on node94:
masterha_check_repl--conf=/etc/masterha/app1.cnf
If it says "MySQL Replication Health is OK", the configuration is ok
Start MHA in the foreground on node94:
masterha_manager--conf=/etc/masterha/app1.cnf --ignore_last_failover Start monitoring in the foreground
Simulate node93master downtime and observe the automatic switch of master:
Stop the mysql service of node93, you can find that the masterha_manager process started on node94 has automatically exited at this time, go to other nodes to check, you can find that the master-slave switch.
Then start the mysql of node93, it will not automatically become the master if it goes online again
[!! Note: If you directly put node93 online, there will be two master nodes in the cluster, split brain, masterha_manger can not start], we need to manually change it to the slave node, the operation is as follows:
On node93, execute:
change master to
master_host='10.1.20.94',
master_user='rpl',
master_password='rpl',
master_log_file='mysql-bin.000003',
master_log_pos=1881; # The location here, you need to look at the content of manager.log in the /data/masterha/app1 directory of node94 to find the specific binlog location.
start slave;
show slave status\G
After adding node93 back to the cluster, we executed masterha_manager--conf=/etc/masterha/app1.cnf --ignore_last_failover again on the node94 manager node and found that the startup did not exit. (Do not close this window during the verification process)
**Check the current master-slave configuration: **
node94 opens another xshell window, you can execute masterha_check_repl--conf=/etc/masterha/app1.cnf
It can be seen that the master node and slave node have changed:
Check if masterha is started:
In addition, open an xshell window, you can execute masterha_check_status--conf=/etc/masterha/app1.cnf
**If you need to stop masterha, do not use stop or kill, use the following command: **
masterha_stop--conf=/etc/masterha/app1.cnf
Manually switch master and slave method:
masterha_master_switch-h View help information
masterha_master_switch--conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=10.1.20.93--new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
There are 2 points to pay attention to when switching manually:
1、 When performing manual switching, you need to turn off the old master and the event scheduler of the host that will be promoted to master first, otherwise you cannot switch (set global event_scheduler = OFF;)
2、 When performing manual switching, you need to turn off MHA monitoring masterha_stop--conf=/etc/masterha/app1.cnf)
3、 When the manual switch script is executed, it will automatically execute FLUSH TABLES WITH READ LOCK on the original master; after the switch is completed, UNLOCK TABLES releases the original master lock.
The script for sending emails needs to install the plug-in first:
yuminstall perl-Mail-Sender
If the sending fails, you can check /tmp/monitormail.log to find the reason for the failure.
**If MHA is abnormal: **
You can view the log path: /data/masterha/app1/
masterha_manager also has several useful startup parameters:
--remove_dead_master_conf This parameter represents that when the master-slave switch occurs, the ip of the old master library will be removed from the configuration file.
--manger_log log storage location, you can add it if you want to standardize the management log
--ignore_last_failover This parameter represents ignoring the files generated by the last MHA trigger switch. By default, after the MHA switch occurs, the app1.failover.complete file will be generated in the log directory, which is the /data I set above, and it will be switched again next time If it is found that the file exists in the directory, it will not be allowed to trigger the switch, unless the file is deleted after the first switch. By default, if the MHA detects continuous downtime and the interval between two downtime is insufficient Failover will not be performed for 8 hours. The reason for this restriction is to avoid the ping-pong effect. [If we need to force switch, we need to remove this file app1.failover.complete first]