乱爱性全过程免费视频/午夜毛片视频/中文字幕av三区/一级视频网址

當前位置： 維易PHP培訓學院 > MYSQL教程 > 內容正文

Mysql入門MySQL下高可用故障轉移方案MHA的超級部署教程

作者：VEPHP   時間 2017-09-24

《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_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 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是否轉移的新的主庫上.

轉載請注明本頁網址：
http://www.fzlkiss.com/jiaocheng/3273.html


    標簽：