溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

mha 搭建 及注意事項

發(fā)布時間：2020-07-21 00:24:49 來源：網絡 閱讀：4913 作者：Love軒軒 欄目：MySQL數據庫

簡介：

MHA（Master High Availability）目前在MySQL高可用方面是一個相對成熟的解決方案，它由日本DeNA公司youshimaton（現(xiàn)就職于Facebook公司）開發(fā)，是一套優(yōu)秀的作為MySQL高可用性環(huán)境下故障切換和主從提升的高可用軟件。在MySQL故障切換過程中，MHA能做到在0~30秒之內自動完成數據庫的故障切換操作，并且在進行故障切換的過程中，MHA能在最大程度上保證數據的一致性，以達到真正意義上的高可用。

該軟件由兩部分組成：MHA Manager（管理節(jié)點）和MHA Node（數據節(jié)點）。MHA Manager可以單獨部署在一臺獨立的機器上管理多個master-slave集群，也可以部署在一臺slave節(jié)點上。MHA Node運行在每臺MySQL服務器上，MHA Manager會定時探測集群中的master節(jié)點，當master出現(xiàn)故障時，它可以自動將最新數據的slave提升為新的master，然后將所有其他的slave重新指向新的master。整個故障轉移過程對應用程序完全透明。

在MHA自動故障切換過程中，MHA試圖從宕機的主服務器上保存二進制日志，最大程度的保證數據的不丟失，但這并不總是可行的。例如，如果主服務器硬件故障或無法通過ssh訪問，MHA沒法保存二進制日志，只進行故障轉移而丟失了最新的數據。使用MySQL 5.5的半同步復制，可以大大降低數據丟失的風險。MHA可以與半同步復制結合起來。如果只有一個slave已經收到了最新的二進制日志，MHA可以將最新的二進制日志應用于其他所有的slave服務器上，因此可以保證所有節(jié)點的數據一致性。

目前MHA主要支持一主多從的架構，要搭建MHA,要求一個復制集群中必須最少有三臺數據庫服務器，一主二從，即一臺充當master，一臺充當備用master，另外一臺充當從庫，因為至少需要三臺服務器，出于機器成本的考慮，淘寶也在該基礎上進行了改造，目前淘寶TMHA已經支持一主一從。另外對于想快速搭建的可以參考：MHA快速搭建

我們自己使用其實也可以使用1主1從，但是master主機宕機后無法切換，以及無法補全binlog。master的mysqld進程crash后，還是可以切換成功，以及補全binlog的。

官方介紹：https://code.google.com/p/mysql-master-ha/

圖01展示了如何通過MHA Manager管理多組主從復制?？梢詫HA工作原理總結為如下：

 mha 搭建 及注意事項

                                 （ 圖01 ）

（1）從宕機崩潰的master保存二進制日志事件（binlog events）;

（2）識別含有最新更新的slave；

（3）應用差異的中繼日志（relay log）到其他的slave；

（4）應用從master保存的二進制日志事件（binlog events）；

（5）提升一個slave為新的master；

（6）使其他的slave連接新的master進行復制；

MHA軟件由兩部分組成，Manager工具包和Node工具包，具體的說明如下。

Manager工具包主要包括以下幾個工具：

masterha_check_ssh              檢查MHA的SSH配置狀況
masterha_check_repl             檢查MySQL復制狀況
masterha_manger                 啟動MHA
masterha_check_status           檢測當前MHA運行狀態(tài)
masterha_master_monitor         檢測master是否宕機
masterha_master_switch          控制故障轉移（自動或者手動）
masterha_conf_host              添加或刪除配置的server信息


ode工具包（這些工具通常由MHA Manager的腳本觸發(fā)，無需人為操作）主要包括以下幾個工具：

save_binary_logs                保存和復制master的二進制日志
apply_diff_relay_logs           識別差異的中繼日志事件并將其差異的事件應用于其他的slavefilter_mysqlbinlog              去除不必要的ROLLBACK事件（MHA已不再使用這個工具）
purge_relay_logs                清除中繼日志（不會阻塞SQL線程）



環(huán)境：

系統(tǒng)：CentOS Linux release 7.3.1611 (Core) 

mysql :5.7.15-log

mha: mha4mysql-manager-0.57.tar.gz mha4mysql-node-0.57.tar.gz



角色                    ip地址          主機名          server_id                  類型
mha mangager            170.17.0.6    server01            -                      監(jiān)控復制組
Master                  170.17.0.5    server02            1                      寫入
Candicate master        170.17.0.4    server03            2                      讀
Candicate master        170.17.0.3    server03            3                      讀
Slave                   170.17.0.2    server05            4                      讀


node 組件
[root@53a15bac5d70 bin]# ll
total 44
-rwxr-xr-x 1 1001 1001 16381 May 31  2015 apply_diff_relay_logs
-rwxr-xr-x 1 1001 1001  4807 May 31  2015 filter_mysqlbinlog
-rwxr-xr-x 1 1001 1001  8261 May 31  2015 purge_relay_logs
-rwxr-xr-x 1 1001 1001  7525 May 31  2015 save_binary_logs
[root@53a15bac5d70 bin]#



manger節(jié)點



|-- bin

|   |-- masterha_check_repl

|   |-- masterha_check_ssh

|   |-- masterha_check_status

|   |-- masterha_conf_host

|   |-- masterha_manager

|   |-- masterha_master_monitor

|   |-- masterha_master_switch

|   |-- masterha_secondary_check

|   `-- masterha_stop


/soft/mha4mysql-manager-0.57/samples/scripts

|-- master_ip_failover （masterha_master_swith --master_state=dead 主機down掉，3306端口完蛋，將啟用此腳本，原始腳本不帶vip自動切換，如果主機不可達，ssh不可用，就用原始的，反之，就用自己添加的vip切換腳本）

|-- master_ip_online_change （主機可用，3306還可以使用對應 masterha_master_swith --master_state=alive）

|-- power_manager

`-- send_report


  masterha_master_switch --master_state=dead

    --global_conf=/etc/masterha_default.cnf

    --conf=/usr/local/masterha/conf/app1.cnf --dead_master_host=host1

    調用 master_ip_failover 

    # For online master switch


    masterha_master_switch --master_state=alive

    --global_conf=/etc/masterha_default.cnf

    --conf=/usr/local/masterha/conf/app1.cnf

    調用 master_ip_online_change 

    See online reference

    (http://code.google.com/p/mysql-master-ha/wiki/masterha_master_switch)





其中script中有簡單的配置文件及切換，發(fā)送郵件等腳本


vim /etc/masterha/app01.conf

[server default]


####save binlog failover flag and so on

manager_workdir=/var/log/masterha/app1 

manager_log=/var/log/masterha/app1/manager.log 

user=root 

password=123456

ssh_user=root

##  path of master binlog 

master_binlog_dir= /data/binlog/,/var/lib/mysql,/var/log/mysql 

# scp the diff binlog to slave's path and save it

remote_workdir=/tmp 

ping_interval=3     

# mha will send ping packet to master per interval ,no ping will failover

#ping_type='select'

#shutdown_script= /script/masterha/power_manager

repl_user=repl  

repl_password=repl

# failover happen ,send email to administrator

report_script=/etc/masterha/script/send_report 

secondary_check_script= /usr/local/bin/masterha_secondary_check -s 172.17.0.2 -s 172.17.0.3 -s 172.17.0.4 -s 172.17.0.5 


# master is down failover 主機掛掉（分兩種情況，ssh可以連接，ssh不可連接，選擇性配置腳本，根據實際情況更改下面配置）

#######################################################################################

#master_ip_failover_script= /etc/masterha/script/master_ip_failover_vip

# master can reachable and down vip ssh可以連接 ，可以腳本關掉vip

#######################################################################################


#######################################################################################

master_ip_failover_script= /etc/masterha/script/master_ip_failover

# master can not reachable and not down vip ssh不可用，無法腳本關掉vip

#######################################################################################



# master online, mysql is alive and ssh is can reachable mysql 可用（分兩種情況

ssh可達，ssh不可達，選擇性配置腳本，根據實際情況更改下面配置）

#######################################################################################

# master can reachable and down vip

#master_ip_online_change_script= /etc/masterha/script/master_ip_online_change_vip     

#######################################################################################


#######################################################################################

# master can not reachable and can't down vip

master_ip_online_change_script= /etc/masterha/script/master_ip_online_change 

#######################################################################################



#[server1]

#hostname=172.17.0.5

#candidate_master=1

#check_repl_delay=0 


[server2]

hostname=172.17.0.4

candidate_master=1

check_repl_delay=0


[server3]

hostname=172.17.0.3

#candidate_master=1


[server4]

hostname=172.17.0.2

no_master=1

 cat master_ip_failover

添加的腳本紅色標出

#!/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 script and is not complete. Modify the script based on your environment.


use strict;

use warnings FATAL => 'all';


use Getopt::Long;

use MHA::DBHelper;


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,

);


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

      # FIXME_xxx;


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

}


cat master_ip_failover_vip

#!/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 script and is not complete. Modify the script based on your environment.


use strict;

use warnings FATAL => 'all';


use Getopt::Long;

use MHA::DBHelper;


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

);


my $vip = '172.17.0.100/24';

my $key = '1';

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

my $ssh_stop_vip = "/usr/sbin/ifconfig eth0:$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,

  'new_master_user=s'     => \$new_master_user,

  'new_master_password=s' => \$new_master_password,

);


exit &main();


sub main {

  

  print "\n\nIN SCRIPT TEST====$ssh_start_vip==$ssh_stop_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();  

      # 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 {

      

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

      &start_vip();

      

      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

      #FIXME_xxx;


      $exit_code = 0;

    };

    if ($@) {

      warn $@;


      # If you want to continue failover, exit 10.

      exit $exit_code;

    }

    exit $exit_code;

  }

  elsif ( $command eq "status" ) {

    print "Checking the status of the script.. ok \n";

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

    # do nothing

    exit 0;

  }

  else {

    &usage();

    exit 1;

  }

}


sub start_vip(){


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


}



sub stop_vip(){

  `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 master_ip_online_change（原版稍作改動FIXME去掉）

#!/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 script 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( sleep gettimeofday 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,

);

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

      #FIXME_xxx_drop_app_user($orig_master_handler);


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

      #FIXME_xxx_create_app_user($new_master_handler);

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

}


cat master_ip_online_change_vip 

#!/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 script 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( sleep gettimeofday 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 = '172.17.0.100';

my $key = "1";

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

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



##########################################################








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

############################################################

      #FIXME_xxx_drop_app_user($orig_master_handler);

############################################################

      ## 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} );

      }


#################################################################

      print "Disable the VIP on old master:$orig_master_host \n";

      &stop_vip();

#################################################################


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


#############################################################

      #FIXME_xxx_create_app_user($new_master_handler);


##############################################################

      $new_master_handler->enable_log_bin_local();

      $new_master_handler->disconnect();


      ## Update master ip on the catalog database, etc

      

##############################################################

      print "Enable the VIP: $vip on the new master host: $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;

  }

}


sub stop_vip {


    `ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;

}


sub start_vip {


     `ssh $new_master_ssh_user\@$new_master_host \" $ssh_start_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;

}





故障處理：

masterha_master_switch --conf=/etc/masterha/app1.cnf   --dead_master_host=172.17.0.3 --master_state=dead

Thu Sep  7 14:19:58 2017 - [warning] SQL Thread is stopped(no error) on 172.17.0.5(172.17.0.5:3306)

Thu Sep  7 14:19:58 2017 - [info] GTID failover mode = 0

Thu Sep  7 14:19:58 2017 - [info] Dead Servers:

Thu Sep  7 14:19:58 2017 - [info]   172.17.0.4(172.17.0.4:3306)

Thu Sep  7 14:19:58 2017 - [info]   172.17.0.3(172.17.0.3:3306)

Thu Sep  7 14:19:58 2017 - [info] Checking master reachability via MySQL(double check)...

Thu Sep  7 14:19:58 2017 - [info]  ok.

Thu Sep  7 14:19:58 2017 - [info] Alive Servers:

Thu Sep  7 14:19:58 2017 - [info]   172.17.0.5(172.17.0.5:3306)

Thu Sep  7 14:19:58 2017 - [info]   172.17.0.2(172.17.0.2:3306)

Thu Sep  7 14:19:58 2017 - [info] Alive Slaves:

Thu Sep  7 14:19:58 2017 - [info]   172.17.0.5(172.17.0.5:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled

Thu Sep  7 14:19:58 2017 - [info]     Replicating from 172.17.0.3(172.17.0.3:3306)

Thu Sep  7 14:19:58 2017 - [info]     Primary candidate for the new Master (candidate_master is set)

Thu Sep  7 14:19:58 2017 - [info]   172.17.0.2(172.17.0.2:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled

Thu Sep  7 14:19:58 2017 - [info]     Replicating from 172.17.0.3(172.17.0.3:3306)

Thu Sep  7 14:19:58 2017 - [info]     Not candidate for the new Master (no_master is set)

Thu Sep  7 14:19:58 2017 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln492]  Server 172.17.0.4(172.17.0.4:3306) is dead, but must be alive! Check server settings.

Thu Sep  7 14:19:58 2017 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR:  at /usr/local/share/perl5/MHA/MasterFailover.pm line 268.

這是由于配置文件中配置了172.17.0.4，而此服務器mysql當掉了，修改配置文件，將其屏蔽



[server1]

hostname=172.17.0.5

candidate_master=1

check_repl_delay=0


#[server2]

#hostname=172.17.0.4

#candidate_master=1

#check_repl_delay=0


[server3]

hostname=172.17.0.3

#candidate_master=1


[server4]

hostname=172.17.0.2

no_master=1


手動切換：

masterha_master_switch --conf=/etc/masterha/app1.cnf   --dead_master_host=172.17.0.3 --master_state=dead

[root@f8dc93c1f02f script]# masterha_master_switch --conf=/etc/masterha/app1.cnf   --dead_master_host=172.17.0.3 --master_state=dead

--dead_master_ip=<dead_master_ip> is not set. Using 172.17.0.3.

--dead_master_port=<dead_master_port> is not set. Using 3306.

Thu Sep  7 14:22:41 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Thu Sep  7 14:22:41 2017 - [info] Reading application default configuration from /etc/masterha/app1.cnf..

Thu Sep  7 14:22:41 2017 - [info] Reading server configuration from /etc/masterha/app1.cnf..

Thu Sep  7 14:22:41 2017 - [info] MHA::MasterFailover version 0.57.

Thu Sep  7 14:22:41 2017 - [info] Starting master failover.

Thu Sep  7 14:22:41 2017 - [info] 

Thu Sep  7 14:22:41 2017 - [info] * Phase 1: Configuration Check Phase..

Thu Sep  7 14:22:41 2017 - [info] 

Thu Sep  7 14:22:42 2017 - [warning] SQL Thread is stopped(no error) on 172.17.0.5(172.17.0.5:3306)

Thu Sep  7 14:22:42 2017 - [info] GTID failover mode = 0

Thu Sep  7 14:22:42 2017 - [info] Dead Servers:

Thu Sep  7 14:22:42 2017 - [info]   172.17.0.3(172.17.0.3:3306)

Thu Sep  7 14:22:42 2017 - [info] Checking master reachability via MySQL(double check)...

Thu Sep  7 14:22:42 2017 - [info]  ok.

Thu Sep  7 14:22:42 2017 - [info] Alive Servers:

Thu Sep  7 14:22:42 2017 - [info]   172.17.0.5(172.17.0.5:3306)

Thu Sep  7 14:22:42 2017 - [info]   172.17.0.2(172.17.0.2:3306)

Thu Sep  7 14:22:42 2017 - [info] Alive Slaves:

Thu Sep  7 14:22:42 2017 - [info]   172.17.0.5(172.17.0.5:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled

Thu Sep  7 14:22:42 2017 - [info]     Replicating from 172.17.0.3(172.17.0.3:3306)

Thu Sep  7 14:22:42 2017 - [info]     Primary candidate for the new Master (candidate_master is set)

Thu Sep  7 14:22:42 2017 - [info]   172.17.0.2(172.17.0.2:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled

Thu Sep  7 14:22:42 2017 - [info]     Replicating from 172.17.0.3(172.17.0.3:3306)

Thu Sep  7 14:22:42 2017 - [info]     Not candidate for the new Master (no_master is set)

Master 172.17.0.3(172.17.0.3:3306) is dead. Proceed? (yes/NO): yes

Thu Sep  7 14:22:51 2017 - [error][/usr/local/share/perl5/MHA/MasterFailover.pm, ln309] Last failover was done at 2017/09/07 12:08:18. Current time is too early to do failover again. If you want to do failover, manually remove /var/log/masterha/app1/app1.failover.complete and run this script again.

Thu Sep  7 14:22:51 2017 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR:  at /usr/local/bin/masterha_master_switch line 53.

[root@f8dc93c1f02f script]# 

 錯誤提示，之前做過failover，并留了粑粑 

remove /var/log/masterha/app1/app1.failover.complete

Thu Sep  7 14:27:45 2017 - [info] MHA::MasterFailover version 0.57.

Thu Sep  7 14:27:45 2017 - [info] Starting master failover.

Thu Sep  7 14:27:45 2017 - [info] 

Thu Sep  7 14:27:45 2017 - [info] * Phase 1: Configuration Check Phase..

Thu Sep  7 14:27:45 2017 - [info] 

Thu Sep  7 14:27:46 2017 - [info] GTID failover mode = 0

Thu Sep  7 14:27:46 2017 - [info] Dead Servers:

Thu Sep  7 14:27:46 2017 - [info]   172.17.0.3(172.17.0.3:3306)

Thu Sep  7 14:27:46 2017 - [info] Checking master reachability via MySQL(double check)...

Thu Sep  7 14:27:46 2017 - [info]  ok.

Thu Sep  7 14:27:46 2017 - [info] Alive Servers:

Thu Sep  7 14:27:46 2017 - [info]   172.17.0.5(172.17.0.5:3306)

Thu Sep  7 14:27:46 2017 - [info]   172.17.0.2(172.17.0.2:3306)

Thu Sep  7 14:27:46 2017 - [info] Alive Slaves:

Thu Sep  7 14:27:46 2017 - [info]   172.17.0.5(172.17.0.5:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled

Thu Sep  7 14:27:46 2017 - [info]     Replicating from 172.17.0.3(172.17.0.3:3306)

Thu Sep  7 14:27:46 2017 - [info]     Primary candidate for the new Master (candidate_master is set)

Thu Sep  7 14:27:46 2017 - [info]   172.17.0.2(172.17.0.2:3306)  Version=5.7.15-log (oldest major version between slaves) log-bin:enabled

Thu Sep  7 14:27:47 2017 - [info]     Replicating from 172.17.0.3(172.17.0.3:3306)

Thu Sep  7 14:27:47 2017 - [info]     Not candidate for the new Master (no_master is set)

Thu Sep  7 14:27:47 2017 - [error][/usr/local/share/perl5/MHA/MasterFailover.pm, ln281] Failover error flag file /var/log/masterha/app1/app1.failover.error exists. This means the last failover failed. Check error logs for detail, fix problems, remove /var/log/masterha/app1/app1.failover.error, and restart this script.

Thu Sep  7 14:27:47 2017 - [error][/usr/local/share/perl5/MHA/ManagerUtil.pm, ln177] Got ERROR:  at /usr/local/bin/masterha_master_switch line 53.

[root@f8dc93c1f02f script]# 

 錯誤提示：上次切換失敗，清除/var/log/masterha/app1/app1.failover.error 



定期清理relay log

--user mysql                      用戶名

--password mysql                  密碼

--port                            端口號

--workdir                         指定創(chuàng)建relay log的硬鏈接的位置，默認是/var/tmp，由于系統(tǒng)不同分區(qū)創(chuàng)建硬鏈接文件會失敗，故需要執(zhí)行硬鏈接具體位置，成功執(zhí)行腳本后，硬鏈接的中繼日志文件被刪除

--disable_relay_log_purge         默認情況下，如果relay_log_purge=1，腳本會什么都不清理，自動退出，通過設定這個參數，當relay_log_purge=1的情況下會將relay_log_purge設置為0。清理relay log之后，最后將參數設置為OFF。



cat purge_relay_log.sh 


#!/bin/bash

user=root

passwd=123456

port=3306

log_dir='/data/masterha/log'

work_dir='/data'

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





最后啟動MHA Manger監(jiān)控，查看集群里面現(xiàn)在誰是master（在切換后監(jiān)控就停止了。。。還有東西沒搞對？）后來在官方網站看到這句話就明白了 。

Running MHA Manager from daemontools

Currently MHA Manager process does not run as a daemon. If failover completed successfully or the master process was killed by accident, the manager stops working. To run as a daemon, daemontool. or any external daemon program can be used. Here is an example to run from daemontools.













向AI問一下細節(jié)

免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng)、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。

mha
AI
助
手