MySQL下高可用故障轉(zhuǎn)移方案MHA的超級部署教程

更新時(shí)間：2015年12月23日 14:49:55 作者：zdz

這篇文章主要介紹了MySQL下高可用故障切換方案MHA的超級部署教程,文中隊(duì)MHA方案的一些特點(diǎn)做了介紹,示例基于Linux系統(tǒng)的服務(wù)器環(huán)境,需要的朋友可以參考下

MHA介紹
MHA是一位日本MySQL大牛用Perl寫的一套MySQL故障切換方案，來保證數(shù)據(jù)庫系統(tǒng)的高可用.在宕機(jī)的時(shí)間內(nèi)（通常10—30秒內(nèi)），完成故障切換，部署MHA，可避免主從一致性問題，節(jié)約購買新服務(wù)器的費(fèi)用，不影響服務(wù)器性能，易安裝，不改變現(xiàn)有部署。

   還支持在線切換，從當(dāng)前運(yùn)行master切換到一個(gè)新的master上面，只需要很短的時(shí)間（0.5-2秒內(nèi)），此時(shí)僅僅阻塞寫操作，并不影響讀操作，便于主機(jī)硬件維護(hù)。

在有高可用，數(shù)據(jù)一致性要求的系統(tǒng)上，MHA 提供了有用的功能，幾乎無間斷的滿足維護(hù)需要。

優(yōu)點(diǎn)：

1.master自動監(jiān)控和故障轉(zhuǎn)移

在當(dāng)前已存在的主從復(fù)制環(huán)境中，MHA可以監(jiān)控master主機(jī)故障，并且故障自動轉(zhuǎn)移。

即使有一些slave沒有接受新的relay log events，MHA也會從最新的slave自動識別差異的relay log events，并apply差異的event到其他slaves。因此所有的slave都是一致的。MHA秒級別故障轉(zhuǎn)移（9-12秒監(jiān)測到主機(jī)故障，任選7秒鐘關(guān)閉電源主機(jī)避免腦裂，接下來apply差異relay logs，注冊到新的master，通常需要時(shí)間10-30秒即total downtime）。另外，在配置文件里可以配置一個(gè)slave優(yōu)先成為master。因?yàn)镸HA修復(fù)了slave之間的一致性，dba就不用去處理一致性問題。

     當(dāng)遷移新的master之后，并行恢復(fù)其他slave。即使有成千上萬的slave,也不會影響恢復(fù)master時(shí)間，slave也很快完成。

      DeNA公司在150+主從環(huán)境中用MHA。當(dāng)其中一個(gè)master崩潰，MHA4秒完成故障轉(zhuǎn)移，這是主動/被動集群解決方案無法完成的。

2.互動(手動)master故障轉(zhuǎn)移

MHA可以用來只做故障轉(zhuǎn)移，而不監(jiān)測master，MHA只作為故障轉(zhuǎn)移的交互。

3.非交互式故障轉(zhuǎn)移

非交互式的故障轉(zhuǎn)移也提供（不監(jiān)控master，自動故障轉(zhuǎn)移）。這個(gè)特性很有用，特別是你已經(jīng)安裝了其他軟件監(jiān)控master。比如，用Pacemaker(Heartbeat)監(jiān)測master故障和vip接管，用MHA故障轉(zhuǎn)移和slave提升。

4.在線切換master到不同主機(jī)

在很多情況下，有必要將master轉(zhuǎn)移到其他主機(jī)上（如替換raid控制器，提升master機(jī)器硬件等等）。這并不是master崩潰，但是計(jì)劃維護(hù)必須去做。計(jì)劃維護(hù)導(dǎo)致downtime，必須盡可能快的恢復(fù)?？焖俚膍aster切換和優(yōu)雅的阻塞寫操作是必需的，MHA提供了這種方式。優(yōu)雅的master切換， 0.5-2秒內(nèi)阻塞寫操作。在很多情況下0.5-2秒的downtime是可以接受的，并且即使不在計(jì)劃維護(hù)窗口。這意味著當(dāng)需要更換更快機(jī)器，升級高版本時(shí)，dba可以很容易采取動作。

5.master crash不會導(dǎo)致主從數(shù)據(jù)不一致性

    當(dāng)master crash后，MHA自動識別slave間relay logevents的不同，然后應(yīng)用與不同的slave，最終所有slave都同步。結(jié)合通過半同步一起使用，幾乎沒有任何數(shù)據(jù)丟失。

其他高可用方案

6.MHA部署不影響當(dāng)前環(huán)境設(shè)置

MHA最重要的一個(gè)設(shè)計(jì)理念就是盡可能使用簡單。使用與5.0+以上主從環(huán)境，其他HA方案需要改變mysql部署設(shè)置，MHA不會讓dba做這些部署配置，同步和半同步環(huán)境都可以用。啟動/停止/升級/降級/安裝/卸載 MHA都不用改變mysql主從（如啟動/停止）。

當(dāng)你需要升級MHA到新版本時(shí)，不需要停止mysql，僅僅更新HMA版本，然后重新啟動MHAmanger即可。

   MHA 支持包含5.0/5/1/5.5(應(yīng)該也支持5.6，翻譯文檔時(shí)MHA開發(fā)者沒更新對于5.6版本)。有些HA方案要求特定的mysql版本（如mysqlcluster，mysql with global transaction id 等）,而且你可能不想僅僅為了MasterHA而遷移應(yīng)用。很多情況下，公司已經(jīng)部署了許多傳統(tǒng)的mysql應(yīng)用，開發(fā)或dba不想花太多時(shí)間遷移到不同的存儲引擎或新的特性（newer bleeding edge distributions 不知道這個(gè)是否該這么翻譯）。

7.不增加服務(wù)器費(fèi)用

MHA 包含MHA Manager和MHA node。MHA node運(yùn)行在每臺mysql服務(wù)器上，Manager可以單獨(dú)部署一臺機(jī)器，監(jiān)控100+以上master，總服務(wù)器數(shù)量不會有太大增加。需要注意的是Manager也可以運(yùn)行在slaves中的一臺機(jī)器上。

8.性能無影響

當(dāng)監(jiān)控master，MHA只是幾秒鐘（默認(rèn)3秒）發(fā)送ping包，不發(fā)送大的查詢。主從復(fù)制性能不受影響

9.適用任何存儲引擎

Mysql不僅僅適用于事務(wù)安全的innodb引擎，在主從中適用的引擎，MHA都可以適用。即使用遺留環(huán)境的mysiam引擎，不進(jìn)行遷移，也可以用MHA。

安裝配置方法
1、示例環(huán)境介紹

centos 6.4 x64
mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz
mha4mysql-manager-0.54.tar.gz
mha4mysql-node-0.54.tar.gz

2、服務(wù)器說明

masnager 192.168.216.50
master 192.168.216.51
slave 192.168.216.52

三臺服務(wù)器都安裝mysql，將manager作為管理節(jié)點(diǎn)

3、配置等價(jià)性

manager：

          ssh-keyger -t rsa

          ssh-coyp-id -i /root/.ssh/id_rsa.pub root@192.168.216.50

          ssh-coyp-id -i /root/.ssh/id_rsa.pub root@192.168.216.51

          ssh-coyp-id -i /root/.ssh/id_rsa.pub root@192.168.216.52

          ssh 192.168.216.50 date

          ssh 192.168.216.51 date

          ssh 192.168.216.52 date

master:

          ssh-keyger -t rsa

          ssh-coyp-id -i /root/.ssh/id_rsa.pub root@192.168.216.50

          ssh-coyp-id -i /root/.ssh/id_rsa.pub root@192.168.216.51

          ssh-coyp-id -i /root/.ssh/id_rsa.pub root@192.168.216.52

          ssh 192.168.216.50 date

          ssh 192.168.216.51 date

          ssh 192.168.216.52 date

slave:

          ssh-keyger -t rsa

          ssh-coyp-id -i /root/.ssh/id_rsa.pub root@192.168.216.50

          ssh-coyp-id -i /root/.ssh/id_rsa.pub root@192.168.216.51

          ssh-coyp-id -i /root/.ssh/id_rsa.pub root@192.168.216.52

          ssh 192.168.216.50 date

          ssh 192.168.216.51 date

          ssh 192.168.216.52 date

4、安裝mysql

    groupadd mysql

     useradd -g mysql -s /bin/nologin -M mysql

     mkdir -pv /data/mysql

     tar -zxvf mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz
     mv mysql-5.6.16-linux-glibc2.5-x86_64 /usr/local/mysql

     cd /usr/local/mysql/script

     ./ mysql_install_db –user=mysql –basedir=/usr/local/mysql –datadair=/data/mysql

創(chuàng)建mysql配置文件

     vim /usr/local/mysql/my.cnf

    [mysql]

     # CLIENT #

     port              = 3306

     socket             = /tmp/mysql.sock

 

     [mysqld]

     # GENERAL #

     user              = mysql

     default-storage-engine     = InnoDB

     socket             = /tmp/mysql.sock

     pid-file            = /data/mysql/mysql.pid

 

     # MyISAM #

     key-buffer-size        = 32M

     myisam-recover         = FORCE,BACKUP

 

     # SAFETY #

     max-allowed-packet       = 16M

     max-connect-errors       = 1000000

    

     # DATA STORAGE #

     datadir            = /data/mysql/

 

     # BINARY LOGGING #

     server_id                        = 1      #不同服務(wù)器不一樣

     log-bin            = /data/mysql/mysql-bin

     expire-logs-days        = 14

     sync-binlog          = 1

 

     # CACHES AND LIMITS #

     tmp-table-size         = 32M

     max-heap-table-size      = 32M

     query-cache-type        = 0

     query-cache-size        = 0

     max-connections        = 500

     thread-cache-size       = 50

     open-files-limit        = 65535

     table-definition-cache     = 1024

     table-open-cache        = 2048

 

     # INNODB #

     innodb-flush-method      = O_DIRECT

     innodb-log-files-in-group   = 2

     innodb-log-file-size      = 64M

     innodb-flush-log-at-trx-commit = 1

     innodb-file-per-table     = 1

     innodb-buffer-pool-size    = 592M

 

     # LOGGING #

     log-error           = /data/mysql/mysql-error.log

     log-queries-not-using-indexes = 1

     slow-query-log         = 1

     slow-query-log-file      = /data/mysql/mysql-slow.log

5、配置權(quán)限

manager：

          grant all privileges on *.* to root@'127.0.0.1' identified by 'root';

          grant all privileges on *.* to root@'localhost' identified by 'root';

          grant all privileges on *.* to root@'192.168.216.50' identified by 'root';

          grant replication slave on *.* to slave@'192.168.216.50' identified by 'slave';

          grant replication slave on *.* to slave@'192.168.216.51' identified by 'slave';

          grant replication slave on *.* to slave@'192.168.216.52' identified by 'slave';

master:

          grant all privileges on *.* to root@'127.0.0.1' identified by 'root';

          grant all privileges on *.* to root@'localhost' identified by 'root';

          grant all privileges on *.* to root@'192.168.216.50' identified by 'root';

          grant replication slave on *.* to slave@'192.168.216.50' identified by 'slave';

          grant replication slave on *.* to slave@'192.168.216.51' identified by 'slave';

          grant replication slave on *.* to slave@'192.168.216.52' identified by 'slave';

slave:

          grant all privileges on *.* to root@'127.0.0.1' identified by 'root';

          grant all privileges on *.* to root@'localhost' identified by 'root';

          grant all privileges on *.* to root@'192.168.216.50' identified by 'root';

          grant all privileges on *.* to root@'192.168.216.51' identified by 'root';

          grant replication slave on *.* to slave@'192.168.216.50' identified by 'slave';

          grant replication slave on *.* to slave@'192.168.216.51' identified by 'slave';

          grant replication slave on *.* to slave@'192.168.216.52' identified by 'slave';

6、安裝依賴包

管理服務(wù)器manager需要安裝以下全部

perl-Config-Tiny

perl-Params-Validate

perl-Parallel-ForkManager

perl-Log-Dispatch

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

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

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

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

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

          |- perl-Data-ShowTable-3.3-3.4.noarch.rpm

|- perl-Mail-Sender-0.8.22-21.1.noarch.rpm

     |- perl-IO-Socket-SSL-1.31-2.el6.noarch.rpm

          |- perl-Net-LibIDN-0.12-3.el6.x86_64.rpm

          |- perl-Net-SSLeay-1.35-9.el6.x86_64.rpm

     |-perl-Win32API-Registry

|- perl-Mail-Sendmail-0.79_16-4.2.noarch.rpm

     rpm -ivh ncftp-debuginfo-3.2.3-1.3.x86_64.rpm

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

     rpm -ivh perl-Params-Validate-0.91-2.4.x86_64.rpm

     rpm -ivh perl-Config-Tiny-2.12-7.1.el6.noarch.rpm

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

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

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

     rpm -ivh perl-Data-ShowTable-3.3-3.4.noarch.rpm

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

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

     rpm -ivh perl-Net-LibIDN-0.12-3.el6.x86_64.rpm

     rpm -ivh perl-Net-SSLeay-1.35-9.el6.x86_64.rpm

     rpm -ivh perl-IO-Socket-SSL-1.31-2.el6.noarch.rpm

     rpm -ivh perl-Mail-Sendmail-0.79_16-4.2.noarch.rpm

     rpm -ivh perl-Mail-Sender-0.8.22-21.1.noarch.rpm

     rpm -ivh perl-Log-Dispatch-2.22-7.3.noarch.rpm

如果安perl-DBD-MySQL-4.013-3.el6.x86_64.rpm在檢查rep是報(bào)錯(cuò)則需要手動編譯安裝

     tar -zxvf DBD-mysql-4.027.tar.gz

     cd DBD-mysql-4.0.27

     perl Makefile.PL

     make && make install

節(jié)點(diǎn)服務(wù)器安裝

     rpm -ivh ncftp-debuginfo-3.2.3-1.3.x86_64.rpm

     rpm -ivh perl-DBD-MySQL-4.013-3.el6.x86_64.rpm

7、創(chuàng)建軟連接

     ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog

     ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

導(dǎo)出mysql庫搜索路徑

     vim /etc/ld.so.conf.d/mysql-x86_64.conf

       /usr/local/mysql/lib

     ldconfig

8、在所有服務(wù)器上安裝mha4mysql-node-0.54.tar.gz

     tar -zxvf mha4mysql-node-0.54.tar.gz

     cd mha4mysql-node-0.54

     perl Makefile.PL

     make && make install

9、在管理服務(wù)上安裝 mha4mysql-manager-0.54.tar.gz

     tar -zxvf mha4mysql-manager-0.54.tar.gz

     cd mha4mysql-manager-0.54

     perl Makefile.PL

     make && make install

     mkdir -pv /etc/masterha

     mkdir -pv /masterha/app1

     cp samples/conf/* /etc/masterha

     cp samples/scripts/* /usr/local/bin

     vim /etc/masterha/app1.cnf

     [server default]

     manager_workdir=/masterha/app1

     manager_log=/masterha/app1/manager.log

 

     user=root

     password=root

 

     ssh_user=root

     repl_user=slave

     repl_password=slave

     shutdown_script=""

     #master_ip_failover_script="/usr/local/bin/masterha_ip_failover"

     master_ip_online_change_script="/usr/local/bin/masterha_ip_failover "

     report_script=""

 

     [server1]

     hostname=192.168.216.50

     master_binlog_dir="/data/mysql/"

     candidate_master=1

 

     [server2]

     hostname=192.168.216.51

     master_binlog_dir="/data/mysql/"

     candidate_master=1

 

     [server3]

     hostname=192.168.216.52

     master_binlog_dir="/data/mysql/"

     candidate_master=1

10、測試ssh連接

     masterha_check_ssh –conf=/etc/masterha/app1.cnf

11、測試replication

    masterha_check_repl –conf=/etc/masterha/app1.cnf

12、開啟管理節(jié)點(diǎn)進(jìn)程

   masterha_manager –conf=/etc/masterha/app1.cnf

13、測試故障轉(zhuǎn)移

關(guān)閉主庫mysql，查看從庫的的狀態(tài)是否將同步ip切換到新的主庫

14、設(shè)置故障轉(zhuǎn)移的ip

     vim /etc/masterha/app1.cnf

          master_ip_failover_script="/usr/local/bin/masterha_ip_failover "

          master_ip_online_change_script="/usr/local/bin/masterha_ip_failover "

編輯故障轉(zhuǎn)移腳本，將vip設(shè)置成192.168.216.100

     vim /usr/local/bin/masterha_ip_failover

#!/usr/bin/env perl

use strict;

use warnings FATAL => 'all';

 

use Getopt::Long;

 

my (

  $command,     $ssh_user,    $orig_master_host, $orig_master_ip,

  $orig_master_port, $new_master_host, $new_master_ip,  $new_master_port

);

 

# my $vip = '172.16.21.119/24'; # Virtual IP

my $vip = '192.168.216.100/24'; # Virtual IP

my $key = "1";

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

my $ssh_stop_vip = "/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,

);

 

exit &main();

 

sub main {

 

  print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

 

  if ( $command eq "stop" || $command eq "stopssh" ) {

 

    # $orig_master_host, $orig_master_ip, $orig_master_port are passed.

    # If you manage master ip address at global catalog database,

    # invalidate orig_master_ip here.

    my $exit_code = 1;

    eval {

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

      &stop_vip();

      $exit_code = 0;

    };

    if ($@) {

      warn "Got Error: $@\n";

      exit $exit_code;

    }

    exit $exit_code;

  }

  elsif ( $command eq "start" ) {

 

    # all arguments are passed.

    # If you manage master ip address at global catalog database,

    # activate new_master_ip here.

    # You can also grant write access (create user, set read_only=0, etc) here.

    my $exit_code = 10;

    eval {

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

      &start_vip();

      $exit_code = 0;

    };

    if ($@) {

      warn $@;

      exit $exit_code;

    }

    exit $exit_code;

  }

  elsif ( $command eq "status" ) {

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

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

    exit 0;

  }

  else {

    &usage();

    exit 1;

  }

}

 

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

sub start_vip() {

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

}

# A simple system call that disable the VIP on the old_master

sub stop_vip() {

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

}

 

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

}

測試腳本

/usr/local/bin/masterha_ip_failover –command=status –ssh_user=root –orig_master_host=192.168.216.51 –orig_master_ip=192.168.216.51 –orig_master_port=3306

虛擬ip要手動啟動，在主庫故障后會自動轉(zhuǎn)移。

/usr/local/bin/masterha_ip_failover –command=start –ssh_user=root –orig_master_host=192.168.216.51 –orig_master_ip=192.168.216.51 –orig_master_port=3306 –new_master_host=192.168.216.51

測試ip故障轉(zhuǎn)移：

關(guān)閉主庫mysql，查看vip是否轉(zhuǎn)移的新的主庫上。

您可能感興趣的文章: