MHA配置

1 配置主从

主：192.168.16.105

从：192.168.16.102

       192.168.16.106

管理：192.168.16.148

 

2      配置ssh免秘钥登录

创建ssh登录用户mysql5

useradd -d /home/mysql5 mysql5su - mysql5

其中一台mysql节点执行：

ssh-keygen -t rsa

3台mysql节点都使用这一组秘钥

重命名公钥

mv id_rsa.pub authorized_keys

在mysql用户目录下创建.ssh目录，授予700权限

chmod -R 700 ~/.sshchmod 600 ~/.ssh/authorized_keyschmod 600 ~/.ssh/id_rsa

 

管理节点要连接mysql节点,而mysql节点不需要连接管理节点

在管理节点生成一对秘钥

scp id_rsa.pub到其中一台mysql节点

mv authorized_keys  authorized_keys.bakcat authorized_keys id_rsa.pub > authorized_keysrm -rf authorized_keys.bak

测试，排错

3      配置mha

3.1  安装

所有节点

rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

有需要安装的包就安装

 

管理节点

yum -y install perl-DBD-MySQL*yum -y install perl-Config-Tiny*yum -y install perl-Parallel-ForkManager*yum -y install perl-MailTools*yum -y install perl-Email-Date-Format*yum -y install perl-Mail-Sender*yum -y install perl-MIME-Types*yum -y install perl-MIME-Lite*yum -y install perl-Mail-Sendmail*yum -y install perl-Log-Dispatch* rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

 

3.2  配置文件

创建目录

$　

mkdir /etc/mha

 

==============================================================

cat mha.cnf

 

[server default]# 需要在 master 中创建这个用户# mysql> grant ALLPRIVILEGES on *.* to mha@"%" Identified by "111111";user=mhamgrpassword=123456# 在各台服务器上创建目录 /opt/mhamanager_workdir=/data1/mha/masterha/app# 日志位置manager_log=/data1/mha/masterha/app/manager.logremote_workdir=/data1/mha/masterha/appssh_user=mysql5# 配置主从复制时创建的复制用户repl_user=reprepl_password=123456ping_interval=1# 在 master 上查询日志目录# mysql> show variableslike 'log_bin_basename%';# 取此变量值的目录#master_binlog_dir=/var/log/mysql/mysql-bin# VIP 切换脚本#master_ip_failover_script="/etc/mha/master_ip_failover.sh  10.0.0.10 1 " #失败切换,在 MySQL 从服务器提升为新的主服务器时，调用此脚本，因此可以将master_ip_failover_script=/etc/mha/master_ip_failover.sh#vip 信息写到此配置文件#master_ip_online_change_script="/etc/mha/master_ip_online_change.sh10.0.0.10 1 " #在线切脚本,使用masterha_master_switch 命令手动切换 MySQL 主服master_ip_online_change_script=/etc/mha/master_ip_online_change.sh#务器时后会调用此脚本#secondary_check_script=/usr/bin/masterha_secondary_check-s 192.168.11.102 -s 192.168.11.103 --user=root --master_host=192.168.11.101#--master_ip=192.168.11.101--master_port=5310[server1]hostname=192.168.16.105port=3306# 说明此服务器作为 master 候选服务器candidate_master=1[server2]hostname=192.168.16.106port=3306candidate_master=1[server3]hostname=192.168.16.102port=3306# 此服务器不作为 master 候选服务器，因为 manager 在这台服务器上no_master=1

 

 

=============================================================================

cat master_ip_failover.sh

 

#!/usr/bin/env perl #  Copyright (C) 2011 DeNA Co.,Ltd.##  This program is free software; you canredistribute it and/or modify#  it under the terms of the GNU General PublicLicense as published by#  the Free Software Foundation; either version2 of the License, or#  (at your option) any later version.##  This program is distributed in the hope thatit will be useful,#  but WITHOUT ANY WARRANTY; without even theimplied warranty of#  MERCHANTABILITY or FITNESS FOR A PARTICULARPURPOSE.  See the#  GNU General Public License for more details.##  You should have received a copy of the GNUGeneral Public License#   along with this program; if not, write to theFree Software#  Foundation, Inc.,#  51 Franklin Street, Fifth Floor, Boston,MA  02110-1301  USA ## Note: This is a samplescript and is not complete. Modify the script based on your environment.#!/usr/bin/env perl#!/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 $gateway ='10.77.133.1';my $vip  = '192.168.17.10';#my $bcast ='10.77.133.255';#my $netmask ='255.255.255.0';my $interface = 'eth0';my $key = 1;#my $ssh_start_vip ="sudo /sbin/ifconfig $interface:$key $vip netmask $netmask && sudo/sbin/arping -f -q -c 5 -w 5 -I $interface -s $vip  -U $gateway";my $ssh_stop_vip ="sudo /sbin/ifconfig $interface:$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 SCRIPTTEST====$ssh_stop_vip==$ssh_start_vip===\n\n";     if ( $command eq "stop" ||$command eq "stopssh" ) {         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" ) {         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";        exit 0;    }    else {        &usage();        exit 1;    }} sub start_vip() {    my $bcast = `ssh $ssh_user\@$new_master_host sudo /sbin/ifconfig | grep 'Bcast' |head -1 | awk '{print \$3}' | awk -F":" '{print \$2}'`;    chomp $bcast;    my $gateway = `ssh $ssh_user\@$new_master_host sudo /sbin/route -n  | grep 'UG' | awk '{print \$2}'`;    chomp $gateway;    my$netmask  = `ssh$ssh_user\@$new_master_host sudo /sbin/ifconfig | grep 'Bcast' | head -1 | awk'{print \$4}' | awk -F":" '{print \$2}'`;    chomp $netmask;    my $ssh_start_vip = "sudo/sbin/ifconfig $interface:$key $vip broadcast $bcast netmask $netmask&& sudo /sbin/arping -f -q -c 5 -w 5 -I $interface -s $vip  -U $gateway";    print"=======$ssh_start_vip=================\n";    `ssh $ssh_user\@$new_master_host \"$ssh_start_vip \"`;}sub stop_vip() {    my $ssh_user = "mysql5";    print "=======$ssh_stop_vip==================\n";    `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";}

====================================================================

 

黄色背景为需要修改参数

 

=====================================================================

cat /etc/mha/master_ip_online_change.sh

#!/usr/bin/env perl #  Copyright (C) 2011 DeNA Co.,Ltd.##  This program is free software; you canredistribute it and/or modify#  it under the terms of the GNU General PublicLicense as published by#  the Free Software Foundation; either version2 of the License, or#  (at your option) any later version.##  This program is distributed in the hope thatit will be useful,#  but WITHOUT ANY WARRANTY; without even theimplied warranty of#  MERCHANTABILITY or FITNESS FOR A PARTICULARPURPOSE.  See the#  GNU General Public License for more details.##  You should have received a copy of the GNUGeneral Public License#   along with this program; if not, write tothe Free Software#  Foundation, Inc.,#  51 Franklin Street, Fifth Floor, Boston,MA  02110-1301  USA ## Note: This is a samplescript and 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_host, $orig_master_ip,  $orig_master_port, $orig_master_user,  $new_master_host,  $new_master_ip,    $new_master_port,  $new_master_user, $orig_master_password,$new_master_password, );#my $gateway = '10.77.133.1';my $vip = '192.168.17.10';#my $bcast = '10.77.133.255';#my $netmask ='255.255.255.0';my $interface = 'eth0';my $key = 1;#my $ssh_start_vip ="sudo /sbin/ifconfig $interface:$key $vip netmask $netmask && sudo/sbin/arping -f -q -c 5 -w 5 -I $interface -s $vip  -U $gateway";my $ssh_stop_vip = "sudo/sbin/ifconfig $interface:$key down";my $ssh_user ="mysql5";#my$orig_master_user='mha_user';#my$orig_master_password='UKIw57RnC76m';#my$new_master_user='mha_user';#my$new_master_password='UKIw57RnC76m'; 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_user=s'     => \$orig_master_user,  'orig_master_password=s' =>\$orig_master_password,  '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 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("SHOWPROCESSLIST");  $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/ ifdefined($info);    next if ( $my_connection_id == $id );    next if ( defined($query_time) &&$query_time < $running_time_threshold );    next if ( defined($command)    && $command eq "BinlogDump" );    next if ( defined($user)       && $user eq "systemuser" );    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 thecurrent master    # 1. Set read_only= 1 on the new master    # 2. DROP USER so that no app user canestablish new connections    # 3. Set read_only= 1 on the current master    # 4. Kill current queries    # * Any database access failure will resultin 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_handler->connect($new_master_ip, $new_master_port,        $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();      $orig_master_handler->connect($orig_master_ip, $orig_master_port,        $orig_master_user,$orig_master_password, 1 );       ## 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%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 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%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});      }                   print "Disabling the VIPon old master: $orig_master_host \n";                &stop_vip();            ## 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      $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 thecatalog 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,MHA does not abort    my $exit_code = 10;    eval {      my $new_master_handler = newMHA::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() . " 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() . "Creating app 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                print "Enabling the VIP -$vip on the new master - $new_master_host \n";                &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;  }} # A simple system call thatenable the VIP on the new mastersub start_vip() {    my $bcast = `ssh $ssh_user\@$new_master_host sudo /sbin/ifconfig | grep 'Bcast' |head -1 | awk '{print \$3}' | awk -F":" '{print \$2}'`;    chomp $bcast;    my $gateway = `ssh $ssh_user\@$new_master_host sudo /sbin/route -n  | grep 'UG' | awk '{print \$2}'`;    chomp $gateway;    my $netmask = `ssh $ssh_user\@$new_master_host sudo /sbin/ifconfig | grep 'Bcast' |head -1 | awk '{print \$4}' | awk -F":" '{print \$2}'`;    chomp $netmask;    my $ssh_start_vip = "sudo/sbin/ifconfig $interface:$key $vip broadcast $bcast netmask $netmask&& sudo /sbin/arping -f -q -c 5 -w 5 -I $interface -s $vip  -U $gateway";    print"===========$ssh_start_vip===========================\n";    `ssh $ssh_user\@$new_master_host \"$ssh_start_vip \"`;}# A simple system call thatdisable the VIP on the old_mastersub stop_vip() {    print"===========$ssh_stop_vip===========================\n";    `ssh $ssh_user\@$orig_master_host \"$ssh_stop_vip \"`;} 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;}

 

 

 

也有需要改的

权限777 用户组ssh用户

3.3 配置VIP

  ifconfig 方式

ifconfig eth0:1 192.168.17.10 broadcast 192.168.17.1 netmask 255.255.255.0

arping -f -q -c 5 -w 5 -I eth0 -s 192.168.17.10 -U 192.168.17.1

##发一个arp广播，告诉别的机器此地址的存在

4      测试

ssh测试

masterha_check_ssh --conf=/etc/mha/mha.cnf

 

主从复制测试

masterha_check_repl --conf=/etc/mha/mha.cnf

 

 

5      启动

 

masterha_manager --conf=/etc/mha/mha.cnf--ignore_last_failover < /dev/null > 2 &>1 &

 

 

--ignore_last_failover

作用

忽略上次mha切换遗留的文件，也可以删除工作目录下的mha.failover.complete

 

 

6      问题

 

添加其他用户后会有问题：

权限问题：

/dev/null权限

chmod 666 /dev/nullusermod -a -G root mysql5

 

mysql日志目录权限

ssh用户加到mysql属组，日志目录给权限

 

6.1  sudo

sudo免密码权限

在/etc/sudoers文件的root行添加一行用户的免密码sudo权限

root   ALL=(ALL)       ALLmysql5 ALL=(ALL)       NOPASSWD:ALL

 

sudo报错：sorry, you musthave a tty to run sudo

后台执行不了sudo的解决办法：

调用shell需要使用终端，而不用终端调用shell需要配置：

在/etc/sudoers文件中注释下行：

Defaults requiretty

然后修改为下行

Defaults:mysql5 !requiretty

表示mysql5可以不用控制终端

 

 

 

6.2  目录权限

免秘钥登录的用户目录下的.ssh

.ssh目录权限为700

里面的公钥和私钥权限为600

用户属组为该用户

 

mha的工作目录用户属组为ssh免秘钥登录的用户

权限给777

/etc/mha 目录权限777