Mysql入门MySQL下高可用故障转移方案MHA的超级部署教程

《MysqL入门MysqL下高可用故障转移方案MHA的超级部署教程》要点：
本文介绍了MysqL入门MysqL下高可用故障转移方案MHA的超级部署教程，希望对您有用。如果有疑问，可以联系我们。

MysqL应用MHA介绍
MHA是一位日本MysqL大牛用Perl写的一套MysqL故障切换方案,来保证数据库系统的高可用.在宕机的时间内(通常10―30秒内),完成故障切换,部署MHA,可避免主从一致性问题,节约购买新服务器的费用,不影响服务器性能,易安装,不改变现有部署.

   还支持在线切换,从当前运行master切换到一个新的master上面,只需要很短的时间(0.5-2秒内),此时仅仅阻塞写操作,并不影响读操作,便于主机硬件维护.

在有高可用,数据一致性要求的系统上,MHA 提供了有用的功能,几乎无间断的满足维护需要.

优点：

1.master自动监控和故障转移

在当前已存在的主从复制环境中,MHA可以监控master主机故障,并且故障自动转移.

即使有一些slave没有接受新的relay log events,MHA也会从最新的slave自动识别差异的relay log events,并apply差异的event到其他slaves.因此所有的slave都是一致的.MHA秒级别故障转移(9-12秒监测到主机故障,任选7秒钟关闭电源主机避免脑裂,接下来apply差异relay logs,注册到新的master,通常需要时间10-30秒即total downtime).另外,在配置文件里可以配置一个slave优先成为master.因为MHA修复了slave之间的一致性,dba就不用去处理一致性问题.

     当迁移新的master之后,并行恢复其他slave.即使有成千上万的slave,也不会影响恢复master时间,slave也很快完成.

      DeNA公司在150+主从环境中用MHA.当其中一个master崩溃,MHA4秒完成故障转移,这是主动/被动集群解决方案无法完成的.

2.互动(手动)master故障转移

MHA可以用来只做故障转移,而不监测master,MHA只作为故障转移的交互.

3.非交互式故障转移

非交互式的故障转移也提供(不监控master,自动故障转移).这个特性很有用,特别是你已经安装了其他软件监控master.比如,用Pacemaker(Heartbeat)监测master故障和vip接管,用MHA故障转移和slave提升.

4.在线切换master到不同主机

在很多情况下,有必要将master转移到其他主机上(如替换raid控制器,提升master机器硬件等等).这并不是master崩溃,但是计划维护必须去做.计划维护导致downtime,必须尽可能快的恢复.快速的master切换和优雅的阻塞写操作是必需的,MHA提供了这种方式.优雅的master切换,0.5-2秒内阻塞写操作.在很多情况下0.5-2秒的downtime是可以接受的,并且即使不在计划维护窗口.这意味着当需要更换更快机器,升级高版本时,dba可以很容易采取动作.

5.master crash不会导致主从数据不一致性

    当master crash后,MHA自动识别slave间relay logevents的不同,然后应用与不同的slave,最终所有slave都同步.结合通过半同步一起使用,几乎没有任何数据丢失.

其他高可用方案

6.MHA部署不影响当前环境设置

MHA最重要的一个设计理念就是尽可能使用简单.使用与5.0+以上主从环境,其他HA方案需要改变MysqL部署设置,MHA不会让dba做这些部署配置,同步和半同步环境都可以用.启动/停止/升级/降级/安装/卸载 MHA都不用改变MysqL主从(如启动/停止).

当你需要升级MHA到新版本时,不需要停止MysqL,仅仅更新HMA版本,然后重新启动MHAmanger即可.

   MHA 支持包含5.0/5/1/5.5(应该也支持5.6,翻译文档时MHA开发者没更新对于5.6版本).有些HA方案要求特定的MysqL版本(如MysqLcluster,MysqL with global transaction id 等),而且你可能不想仅仅为了MasterHA而迁移应用.很多情况下,公司已经部署了许多传统的MysqL应用,开发或dba不想花太多时间迁移到不同的存储引擎或新的特性(newer bleeding edge distributions 不知道这个是否该这么翻译).

7.不增加服务器费用

MHA 包含MHA Manager和MHA node.MHA node运行在每台MysqL服务器上,Manager可以单独部署一台机器,监控100+以上master,总服务器数量不会有太大增加.需要注意的是Manager也可以运行在slaves中的一台机器上.

8.性能无影响

当监控master,MHA只是几秒钟(默认3秒)发送ping包,不发送大的查询.主从复制性能不受影响

9.适用任何存储引擎

MysqL不仅仅适用于事务安全的innodb引擎,在主从中适用的引擎,MHA都可以适用.即使用遗留环境的mysiam引擎,不进行迁移,也可以用MHA.

MysqL应用
安装配置方法
1、示例环境介绍

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

MysqL应用2、服务器说明

masnager 192.168.216.50
master 192.168.216.51
slave 192.168.216.52

MysqL应用三台服务器都安装MysqL,将manager作为管理节点

MysqL应用3、配置等价性

MysqL应用 manager：

MysqL应用
          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

MysqL应用 master:

MysqL应用
          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

MysqL应用 slave:

MysqL应用
          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

MysqL应用4、安装MysqL

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 Cuser=MysqL Cbasedir=/usr/local/MysqL Cdatadair=/data/MysqL

MysqL应用创建MysqL 配置文件

MysqL应用
     vim /usr/local/MysqL/my.cnf

MysqL应用

MysqL应用
    [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      #不同服务器不一样
     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

MysqL应用5、配置权限

MysqL应用 manager：

MysqL应用
          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';

MysqL应用 master:

MysqL应用
          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';

MysqL应用 slave:

MysqL应用
          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';

MysqL应用6、安装依赖包

MysqL应用管理服务器manager需要安装以下全部

MysqL应用
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

MysqL应用

MysqL应用
     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

MysqL应用

MysqL应用如果安perl-DBD-MysqL-4.013-3.el6.x86_64.rpm在检查rep是报错则需要手动编译安装

MysqL应用
     tar -zxvf DBD-MysqL-4.027.tar.gz
     cd DBD-MysqL-4.0.27
     perl Makefile.PL
     make && make install

MysqL应用节点服务器安装

MysqL应用
     rpm -ivh ncftp-debuginfo-3.2.3-1.3.x86_64.rpm
     rpm -ivh perl-DBD-MysqL-4.013-3.el6.x86_64.rpm

MysqL应用7、创建软连接

MysqL应用
     ln -s /usr/local/MysqL/bin/MysqLbinlog /usr/bin/MysqLbinlog
     ln -s /usr/local/MysqL/bin/MysqL /usr/bin/MysqL

MysqL应用导出MysqL库搜索路径

MysqL应用
     vim /etc/ld.so.conf.d/MysqL-x86_64.conf
       /usr/local/MysqL/lib
     ldconfig

MysqL应用8、在所有服务器上安装mha4MysqL-node-0.54.tar.gz

MysqL应用
     tar -zxvf mha4MysqL-node-0.54.tar.gz
     cd mha4MysqL-node-0.54
     perl Makefile.PL
     make && make install

MysqL应用9、在管理服务上安装 mha4MysqL-manager-0.54.tar.gz

MysqL应用
     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

MysqL应用

MysqL应用
     vim /etc/masterha/app1.cnf

MysqL应用
     [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

MysqL应用10、测试ssh连接

MysqL应用
     masterha_check_ssh Cconf=/etc/masterha/app1.cnf

MysqL应用11、测试replication

MysqL应用
    masterha_check_repl Cconf=/etc/masterha/app1.cnf

MysqL应用12、开启管理节点进程

MysqL应用

MysqL应用
   masterha_manager Cconf=/etc/masterha/app1.cnf

MysqL应用13、测试故障转移

MysqL应用关闭主库MysqL,查看从库的的状态是否将同步ip切换到新的主库

MysqL应用14、设置故障转移的ip

MysqL应用
     vim /etc/masterha/app1.cnf

MysqL应用
          master_ip_failover_script="/usr/local/bin/masterha_ip_failover "
          master_ip_online_change_script="/usr/local/bin/masterha_ip_failover "

MysqL应用编辑故障转移脚本,将vip设置成192.168.216.100

MysqL应用
     vim /usr/local/bin/masterha_ip_failover

MysqL应用
#!/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_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 C $vip on the new master C $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 Ccommand=start|stop|stopssh|status Corig_master_host=host Corig_master_ip=ip Corig_master_port=port Cnew_master_host=host Cnew_master_ip=ip Cnew_master_port=port\n";
}

MysqL应用

MysqL应用测试脚本

MysqL应用
/usr/local/bin/masterha_ip_failover Ccommand=status Cssh_user=root Corig_master_host=192.168.216.51 Corig_master_ip=192.168.216.51 Corig_master_port=3306

MysqL应用虚拟ip要手动启动,在主库故障后会自动转移.

MysqL应用
/usr/local/bin/masterha_ip_failover Ccommand=start Cssh_user=root Corig_master_host=192.168.216.51 Corig_master_ip=192.168.216.51 Corig_master_port=3306 Cnew_master_host=192.168.216.51

MysqL应用测试ip故障转移：

MysqL应用关闭主库MysqL,查看vip是否转移的新的主库上.

Mysql入门MySQL下高可用故障转移方案MHA的超级部署教程

猜你在找的MySQL相关文章