MHA的搭建步骤

  • A+
所属分类:运维教程

Master High Availability又称MHA,是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的软件。MySQL故障切换过程中,MHA能够做到在30秒之内自动完成数据库的故障切换操作,并在进行故障切换的过程中,MHA能在最大程度上保证数据的一致性,以达到真正意义上的高可用。

主机及实例IP

  1. Manager : 192.168.18.250
  2. Master : 172.16.18.2:3306
  3. Slave1 : 172.16.18.3:3306
  4. Slave2 : 172.16.18.4:3306
  5. VIP : 172.16.18.5

Slave的配置参数

  1. log_bin=/home/birdteam/log/mysql-bin
  2. read_only=1
  3. relay_log_purge=0
  4. #一主一从不用此项,两从及以上建议打开此参数,防止切换为成主库的从库自动删除中继日志后,无法给其他从库应用这部分日志

配置主从同步

  1. mysql>grant replication slave on *.* to 'repl_17zuoye'@'%' identified by 'office.repl.17zuoye';
  2. mysql>flush privileges;
  3. #三个节点都要配置,用于当某个slave升为主后其他的从进行同步
  4. mysql>change master to master_host='172.16.18.2',master_user='dtstack',master_port=3306,master_password='abc123',master_log_file='logbin.000014',master_log_pos=70980879;
  5. mysql>start slave;

配置SSH免秘钥,四台服务器之间可互通

  1. # ssh-keygen -t rsa
  2. # ssh-copy-id -i .ssh/id_rsa.pub root@172.16.18.2

四个节点安装EPEL源以及相关yum包

  1. # rpm -ivh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm          
  2. # yum -y install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perlTime-HiRes

下载安装mha包

  1. # wget https://code.google.com/p/mysql-master-ha/wiki/Downloads?tm=2          
  2. manager节点:
  3. # rpm –ivh mha4mysql-manager-0.56-0.el6.noarch.rpm
  4. # rpm –ivh mha4mysql-node-0.56-0.el6.noarch.rpm
  5. node 节点:
  6. # rpm –ivh mha4mysql-node-0.56-0.el6.noarch.rpm

建立与授权mha用户

  1. mysql>GRANT ALL PRIVILEGES ON *.* TO 'mha'@'%' IDENTIFIED BY 'mhamha';
  2. mysql>flush privileges;

manager节点建立相关目录和配置文件

  1. # tree /mha
  2. /mha
  3. ├── app1
  4. │   ├── app1.conf
  5. │   └── manager.log
  6. └── conf
  7. ├── master_ip_failover_3306
  8. ├── master_ip_online_change
  9. └── send_report
  10. 2 directories, 5 files

相关配置文件内容

  1. # cat app1.conf
  2. [server default]
  3. manager_workdir = /mha/app1
  4. manager_log = /mha/app1/manager.log
  5. remote_workdir = /mha/app1
  6. master_ip_failover_script=/mha/conf/master_ip_failover_3306
  7. #master failover时执行
  8. report_script=/mha/conf/send_report
  9. #master failover时执行,发送邮件使用
  10. master_ip_online_change_script=/mha/conf/master_ip_online_change
  11. #master_switchover时执行(手动切换)
  12. user=mha
  13. password=mhamha
  14. ping_interval=1
  15. ping_type=CONNECT
  16. repl_password=office.repl.17zuoye
  17. repl_user=repl_17zuoye
  18. ssh_port=22
  19. ssh_user=root
  20. [server1]
  21. hostname = 10.200.3.2
  22. port=3306
  23. master_binlog_dir = /database1/data_5.6.17_3306/binlog
  24. candidate_master = 1
  25. #这个服务器有较高的优先级提升为新的master(还要具备:开启binlog使复制没有延迟)
  26. [server2]
  27. hostname = 10.200.3.3
  28. port=3306
  29. master_binlog_dir = /database1/data_5.6.17_3306/binlog
  30. candidate_master =1
  31. ignore_fail=1
  32. [server3]
  33. hostname = 10.200.3.4
  34. port=3306
  35. master_binlog_dir = /database1/data_5.6.17_3306/binlog
  36. candidate_master =1
  37. ignore_fail=1
  38. #如slave存在故障,在主库出现问题时默认情况下mha不会进行故障切换,该参数即设定MHA会在所有的机器有问题的时间也会进行故障切换
  39. no_master=1
  40. #不将这台主机转换为master

主库启动一个虚IP

  1. # /sbin/ifconfig em1:0 10.200.3.5/23 up

失败切换脚本

  1. # cat master_ip_failover_3306
  2. #!/usr/bin/env perl
  3. use strict;
  4. use warnings FATAL => 'all';
  5. use Getopt::Long;
  6. my (
  7.     $command,          $ssh_user,        $orig_master_host, $orig_master_ip,
  8.     $orig_master_port, $new_master_host, $new_master_ip,    $new_master_port
  9. );
  10. my $vip = '10.200.3.5/23';  # Virtual IP
  11. my $key = "0";
  12. my $ssh_start_vip = "/sbin/ifconfig em1:$key $vip";
  13. my $start_new_master_vip = "/sbin/ifconfig em1:$key $vip";
  14. my $ssh_stop_vip = "/sbin/ifconfig em1:$key down";
  15. my $arp = "/usr/sbin/arping -A -q -c 2 -I em1:$key 10.200.3.5";
  16. #虚IP配置,在哪个网卡上,key编号的对应
  17. GetOptions(
  18.     'command=s'          => \$command,
  19.     'ssh_user=s'         => \$ssh_user,
  20.     'orig_master_host=s' => \$orig_master_host,
  21.     'orig_master_ip=s'   => \$orig_master_ip,
  22.     'orig_master_port=i' => \$orig_master_port,
  23.     'new_master_host=s'  => \$new_master_host,
  24.     'new_master_ip=s'    => \$new_master_ip,
  25.     'new_master_port=i'  => \$new_master_port,
  26. );
  27. exit &main();
  28. sub main {
  29.     print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$start_new_master_vip===\n\n";
  30.     if ( $command eq "stop" || $command eq "stopssh" ) {
  31.         # $orig_master_host, $orig_master_ip, $orig_master_port are passed.
  32.         # If you manage master ip address at global catalog database,
  33.         # invalidate orig_master_ip here.
  34.         my $exit_code = 1;
  35.         eval {
  36.             print "Disabling the VIP on old master: $orig_master_host \n";
  37.             &stop_vip();
  38.             $exit_code = 0;
  39.         };
  40.         if ($@) {
  41.             warn "Got Error: $@\n";
  42.             exit $exit_code;
  43.         }
  44.         exit $exit_code;
  45.     }
  46.     elsif ( $command eq "start" ) {
  47.         # all arguments are passed.
  48.         # If you manage master ip address at global catalog database,
  49.         # activate new_master_ip here.
  50.         # You can also grant write access (create user, set read_only=0, etc) here.
  51.         my $exit_code = 10;
  52.         eval {
  53.             print "Enabling the VIP - $vip on the new master - $new_master_host \n";
  54.             &start_vip();
  55.             $exit_code = 0;
  56.         };
  57.         if ($@) {
  58.             warn $@;
  59.             exit $exit_code;
  60.         }
  61.         exit $exit_code;
  62.     }
  63.     elsif ( $command eq "status" ) {
  64.        # print "Checking the Status of the script.. OK \n";
  65.        # `ssh $ssh_user\@tm01.okooo.cn \" $ssh_start_vip \"`;
  66.         exit 0;
  67.     }
  68.     else {
  69.         &usage();
  70.         exit 1;
  71.     }
  72. }
  73. # A simple system call that enable the VIP on the new master
  74. sub start_vip() {
  75.     `ssh root\@$new_master_host \" $ssh_start_vip \"`;
  76.     `ssh root\@$new_master_host \" $arp \"`;
  77. }
  78. # A simple system call that disable the VIP on the old_master
  79. sub stop_vip() {
  80.     return 0 unless ($ssh_user);
  81.     `ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
  82. }
  83. sub usage {
  84.     print
  85.     "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";
  86. }

手动在线切换脚本

  1. # cat master_ip_online_change
  2. #!/usr/bin/env perl
  3. use strict;
  4. use warnings FATAL =>'all';
  5. use Getopt::Long;
  6. my $vip = '10.200.3.5/23';  # Virtual IP
  7. my $key = "0";
  8. my $ssh_start_vip = "/sbin/ifconfig em1:$key $vip";
  9. my $ssh_stop_vip = "/sbin/ifconfig em1:$key down";
  10. my $exit_code = 0;
  11. my (
  12.   $command,              $orig_master_is_new_slave, $orig_master_host,
  13.   $orig_master_ip,       $orig_master_port,         $orig_master_user,
  14.   $orig_master_password, $orig_master_ssh_user,     $new_master_host,
  15.   $new_master_ip,        $new_master_port,          $new_master_user,
  16.   $new_master_password,  $new_master_ssh_user,
  17. );
  18. GetOptions(
  19.   'command=s'                => \$command,
  20.   'orig_master_is_new_slave' => \$orig_master_is_new_slave,
  21.   'orig_master_host=s'       => \$orig_master_host,
  22.   'orig_master_ip=s'         => \$orig_master_ip,
  23.   'orig_master_port=i'       => \$orig_master_port,
  24.   'orig_master_user=s'       => \$orig_master_user,
  25.   'orig_master_password=s'   => \$orig_master_password,
  26.   'orig_master_ssh_user=s'   => \$orig_master_ssh_user,
  27.   'new_master_host=s'        => \$new_master_host,
  28.   'new_master_ip=s'          => \$new_master_ip,
  29.   'new_master_port=i'        => \$new_master_port,
  30.   'new_master_user=s'        => \$new_master_user,
  31.   'new_master_password=s'    => \$new_master_password,
  32.   'new_master_ssh_user=s'    => \$new_master_ssh_user,
  33. );
  34. exit &main();
  35. sub main {
  36. #print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
  37. if ( $command eq "stop" || $command eq "stopssh" ) {
  38.         # $orig_master_host, $orig_master_ip, $orig_master_port are passed.
  39.         # If you manage master ip address at global catalog database,
  40.         # invalidate orig_master_ip here.
  41.         my $exit_code = 1;
  42.         eval {
  43.             print "\n\n\n***************************************************************\n";
  44.             print "Disabling the VIP - $vip on old master: $orig_master_host\n";
  45.             print "***************************************************************\n\n\n\n";
  46. &stop_vip();
  47.             $exit_code = 0;
  48.         };
  49.         if ($@) {
  50.             warn "Got Error: $@\n";
  51.             exit $exit_code;
  52.         }
  53.         exit $exit_code;
  54. }
  55. elsif ( $command eq "start" ) {
  56.         # all arguments are passed.
  57.         # If you manage master ip address at global catalog database,
  58.         # activate new_master_ip here.
  59.         # You can also grant write access (create user, set read_only=0, etc) here.
  60. my $exit_code = 10;
  61.         eval {
  62.             print "\n\n\n***************************************************************\n";
  63.             print "Enabling the VIP - $vip on new master: $new_master_host \n";
  64.             print "***************************************************************\n\n\n\n";
  65. &start_vip();
  66.             $exit_code = 0;
  67.         };
  68.         if ($@) {
  69.             warn $@;
  70.             exit $exit_code;
  71.         }
  72.         exit $exit_code;
  73. }
  74. elsif ( $command eq "status" ) {
  75.         print "Checking the Status of the script.. OK \n";
  76.         `ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_start_vip \"`; 
  77.         exit 0; 
  78. }  
  79. else { 
  80. &usage(); 
  81.         exit 1; 
  82.  
  83. # A simple system call that enable the VIP on the new master 
  84. sub start_vip() { 
  85. `ssh $new_master_ssh_user\@$new_master_host \" $ssh_start_vip \"`; 
  86. # A simple system call that disable the VIP on the old_master 
  87. sub stop_vip() { 
  88. `ssh $orig_master_ssh_user\@$orig_master_host \" $ssh_stop_vip \"`; 
  89.  
  90. sub usage { 
  91. print 
  92. "Usage: master_ip_failover –command=start|stop|stopssh|status –orig_master_host=host –orig_master_ip=ip –orig_master_port=po
  93. rt –new_master_host=host –new_master_ip=ip –new_master_port=port\n";
  94. }

failover后发送邮件脚本

  1. # cat send_report
  2. #!/bin/bash
  3. source /root/.bash_profile
  4. orig_master_host=`echo "$1" | awk -F = '{print $2}'`
  5. new_master_host=`echo "$2" | awk -F = '{print $2}'`
  6. new_slave_hosts=`echo "$3" | awk -F = '{print $2}'`
  7. subject=`echo "$4" | awk -F = '{print $2}'`
  8. body=`echo "$5" | awk -F = '{print $2}'`
  9. #判断日志结尾是否有successfully,有则表示切换成功,成功与否都发邮件
  10. tac /mha/app1/manager.log | sed -n 2p | grep 'successfully' > /dev/null
  11. if [ $? -eq 0 ]
  12.   then
  13.   echo -e "MHA $subject 主从切换成功\n master:$orig_master_host --> $new_master_host \n $body \n 当前从库:$new_slave_hosts" | mailx -s "MySQL实例宕掉,MHA $subject 切换成功" noreply@birdteam.net
  14. else
  15.   echo -e "MHA $subject 主从切换失败\n master:$orig_master_host --> $new_master_host \n $body" | mailx -s "MySQL实例宕掉,MHA $subject 切换失败" noreply@birdteam.net
  16. fi

修改脚本属主属组,并且增加执行权限

  1. # chown mysql.mysql ./*
  2. # chmod +x ./*

检查SSH的配置

  1. # masterha_check_ssh --conf=/mha/app1/app1.conf
  2. Tue Jan  5 17:16:41 2016 - [info] All SSH connectiontests passed successfully.

检查MHA的配置

  1. # masterha_check_repl --conf=/mha/app1/app1.conf
  2. MySQL Replication Health is OK.

启动MHA的服务

  1. # masterha_manager --conf=/mha/app1/app1.conf

发生failover主从切换后,MHAmanager服务会自动停掉,且在manager_workdir目录下面生成文件app1.failover.complete,若想要启动MHA,必须先确保没有此文件

  1. # ll
  2. total 80
  3. -rw-r--r-- 1 mysql mysql   556 Aug 29 11:23 app1.conf
  4. -rw-r--r-- 1 root  root      0 Aug 29 15:33 app1.failover.complete
  5. -rw-r--r-- 1 root  root  69838 Aug 29 15:33 manager.log
  6. -rw-r--r-- 1 root  root    143 Aug 29 15:33 saved_master_binlog_from_192.168.100.111_3306_20160829153340.binlog

在线手动切换主从,如果MHA在运行,需要先停止MHA,然后再检查MHA当前设置

  1. # masterha_check_repl --conf=/mha/app1/app1.conf

手动切换

  • 如果不指定new_master_host,则会根据配置文件app1.cnf选出new_master_host,但new_master_port默认是3306
  1. masterha_master_switch --master_state=alive --conf=/mha/app1/app1.conf  --orig_master_is_new_slave -running_updates_limit=3600 --interactive=0
  • 以下为切换时指定了new_master_host和new_master_port
  1. masterha_master_switch --master_state=alive --conf=/mha/app1/app1.conf  --orig_master_is_new_slave -running_updates_limit=3600 --interactive=0 --new_master_host=10.200.3.2 --new_master_port=3306
  1. 参数--running_updates_limit如果现在master执行写操作的执行时间大于这个参数,或任何一台slave的Seconds_Behind_Master大于这个参数,那么master switch将自动放弃,默认参数为1s;
  2. 参数--interactive=0非交互切换,建议加上,可以大大加快切换速度,加上后库不忙时大概3秒内切换完成。

注意

  • 如果需要将现有的从库修改为从,再启动mha的时候可能会报错;
  1. Wed Sep  7 12:18:56 2016 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln671] Master 192.168.100.111:3306 from which slave 10.200.3.2(10.200.3.2:3306) replicates is not defined in the configuration file!
  • 切换脚本可在MHA的官网查看。
  • 问答中心
  • 微信小程序扫一扫
  • weinxin
  • 微信公众
  • 微信公众号扫一扫
  • weinxin

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

目前评论:10   其中:访客  10   博主  0

    • avatar check over here

      I just couldn’t leave your website before suggesting that I really enjoyed the usual info an individual supply for your guests? Is going to be again steadily to investigate cross-check new posts

      • avatar go

        Thanks a lot for sharing this with all folks you really recognise what you are talking about! Bookmarked. Kindly also seek advice from my website =). We can have a hyperlink exchange arrangement between us!

        • avatar erjilopterin

          This is the right blog for anyone who wants to find out about this topic. You realize so much its almost hard to argue with you (not that I actually would want…HaHa). You definitely put a new spin on a topic thats been written about for years. Great stuff, just great!

          • avatar click for more info

            I would like to thank you for the efforts you’ve put in writing this site. I am hoping the same high-grade site post from you in the upcoming as well. In fact your creative writing abilities has inspired me to get my own web site now. Really the blogging is spreading its wings quickly. Your write up is a good example of it.

            • avatar https://lesptitutos.fr/

              I was suggested this blog via my cousin. I am not sure whether this publish is written by way of him as no one else recognize such particular about my problem. You are incredible! Thanks!

              • avatar family photography Raleigh

                Whats up very cool web site!! Guy .. Excellent .. Amazing .. I will bookmark your website and take the feeds also…I am satisfied to search out so many useful info here within the post, we’d like develop extra techniques in this regard, thanks for sharing.

                • avatar find here

                  Thanks for the post, can I set it up so I get an email sent to me every time you publish a fresh article?

                  • avatar smoretraiolit

                    This actually answered my problem, thank you!

                    • avatar Buy crystal online

                      I like what you guys are up too. Such clever work and reporting! Keep up the superb works guys I have incorporated you guys to my blogroll. I think it will improve the value of my website :)

                      • avatar firtukloimutrzas

                        hello!,I really like your writing very much! share we keep up a correspondence more about your post on AOL? I require an expert in this space to solve my problem. Maybe that is you! Taking a look forward to see you.