MHA搭建手册

类别 版本
OS CentOS Linux release 7.1.1503 (Core)
MySQL 5.7.21
GTID 开启
MHA 0.56

一、搭建主从

这里不多说了，比较简单。

二、配置ssh信任

在slave节点上 ssh-keygen生成key，一路回车就可以了。

  1. cd ~/.ssh/
  2. cat id_rsa.pub > authorized_keys
  3. chmod 600 *
  4. cd ~
  5. scp -r .ssh 192.168.50.3:~/

三、安装依赖

添加eperl源

  1. wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo

安装依赖

  1. yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch    
  2. yum install perl-Parallel-ForkManager

四、两个节点安装node、manager

  1. rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm
  2. rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

五、MySQL初始化

  1. grant all privileges on *.* to 'root'@'%' identified by '123456';
  2. grant replication slave on *.* ro 'repl'@'%' identified by '123456';
  3. flush privileges;

六、修改配置文件

相关文件拷贝到/etc/masterha下

  1. cp -r masterha /etc/

全局级配置文件:

  1. vim /etc/masterha/masterha_default.conf
  1. [server default]
  2. #MySQL的用户和密码
  3. user=mha
  4. password=123456
  6. #系统ssh用户
  7. ssh_user=root
  9. #复制用户
  10. repl_user=repl
  11. repl_password= 123456
  14. #监控
  15. ping_interval=1
  16. #shutdown_script=""
  18. #切换调用的脚本
  19. master_ip_failover_script= /etc/masterha/master_ip_failover
  20. master_ip_online_change_script= /etc/masterha/master_ip_online_change

说明：shutdown_script主要用来设置master进行切换时，要执行的脚本动作，这个动作可以设置吧机器关了来防止脑裂，也可以做一些其他动作（前提是那台机器还活着）
下面贴一下两个切换用到的脚本：
master_ip_failover

  1. #!/usr/bin/env perl
  3. #  Copyright (C) 2011 DeNA Co.,Ltd.
  4. #
  5. #  This program is free software; you can redistribute it and/or modify
  6. #  it under the terms of the GNU General Public License as published by
  7. #  the Free Software Foundation; either version 2 of the License, or
  8. #  (at your option) any later version.
  9. #
  10. #  This program is distributed in the hope that it will be useful,
  11. #  but WITHOUT ANY WARRANTY; without even the implied warranty of
  12. #  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13. #  GNU General Public License for more details.
  14. #
  15. #  You should have received a copy of the GNU General Public License
  16. #   along with this program; if not, write to the Free Software
  17. #  Foundation, Inc.,
  18. #  51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
  20. ## Note: This is a sample script and is not complete. Modify the script based on your environment.
  22. use strict;
  23. use warnings FATAL => 'all';
  25. use Getopt::Long;
  26. use MHA::DBHelper;
  27. #自定义该组机器的vip
  28. my $vip = "192.168.1.100";
  29. my $if = "eth0";
  30. my (
  31.   $command,        $ssh_user,         $orig_master_host,
  32.   $orig_master_ip, $orig_master_port, $new_master_host,
  33.   $new_master_ip,  $new_master_port,  $new_master_user,
  34.   $new_master_password
  35. );
  37. GetOptions(
  38.   'command=s'             => \$command,
  39.   'ssh_user=s'            => \$ssh_user,
  40.   'orig_master_host=s'    => \$orig_master_host,
  41.   'orig_master_ip=s'      => \$orig_master_ip,
  42.   'orig_master_port=i'    => \$orig_master_port,
  43.   'new_master_host=s'     => \$new_master_host,
  44.   'new_master_ip=s'       => \$new_master_ip,
  45.   'new_master_port=i'     => \$new_master_port,
  46.   'new_master_user=s'     => \$new_master_user,
  47.   'new_master_password=s' => \$new_master_password,
  48. );
  50. sub add_vip {
  51.     my $output1 = `ssh -o ConnectTimeout=15  -o ConnectionAttempts=3 $orig_master_host /sbin/ip addr del $vip/32 dev $if`;
  52.     my $output2 = `ssh -o ConnectTimeout=15  -o ConnectionAttempts=3 $new_master_host /sbin/ip addr add $vip/32 dev $if`;
  54. }
  55. exit &main();
  57. sub main {
  58.   if ( $command eq "stop" || $command eq "stopssh" ) {
  60.     # $orig_master_host, $orig_master_ip, $orig_master_port are passed.
  61.     # If you manage master ip address at global catalog database,
  62.     # invalidate orig_master_ip here.
  63.     my $exit_code = 1;
  64.     eval {
  66.       # updating global catalog, etc
  67.       $exit_code = 0;
  68.     };
  69.     if ($@) {
  70.       warn "Got Error: $@\n";
  71.       exit $exit_code;
  72.     }
  73.     exit $exit_code;
  74.   }
  75.   elsif ( $command eq "start" ) {
  77.     # all arguments are passed.
  78.     # If you manage master ip address at global catalog database,
  79.     # activate new_master_ip here.
  80.     # You can also grant write access (create user, set read_only=0, etc) here.
  81.     my $exit_code = 10;
  82.     eval {
  83.       my $new_master_handler = new MHA::DBHelper();
  85.       # args: hostname, port, user, password, raise_error_or_not
  86.       $new_master_handler->connect( $new_master_ip, $new_master_port,
  87.         $new_master_user, $new_master_password, 1 );
  89.       ## Set read_only=0 on the new master
  90.       $new_master_handler->disable_log_bin_local();
  91.       print "Set read_only=0 on the new master.\n";
  92.       $new_master_handler->disable_read_only();
  94.       ## Creating an app user on the new master
  95.       #print "Creating app user on the new master..\n";
  96.       #FIXME_xxx_create_user( $new_master_handler->{dbh} );
  97.       $new_master_handler->enable_log_bin_local();
  98.       $new_master_handler->disconnect();
  100.       ## Update master ip on the catalog database, etc
  101.       &add_vip();
  102.       $exit_code = 0;
  103.     };
  104.     if ($@) {
  105.       warn $@;
  107.       # If you want to continue failover, exit 10.
  108.       exit $exit_code;
  109.     }
  110.     exit $exit_code;
  111.   }
  112.   elsif ( $command eq "status" ) {
  114.     # do nothing
  115.     exit 0;
  116.   }
  117.   else {
  118.     &usage();
  119.     exit 1;
  120.   }
  121. }
  123. sub usage {
  124.   print
  125. "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";
  126. }

master_ip_online_change:

  1. #!/usr/bin/env perl
  3. #  Copyright (C) 2011 DeNA Co.,Ltd.
  4. #
  5. #  This program is free software; you can redistribute it and/or modify
  6. #  it under the terms of the GNU General Public License as published by
  7. #  the Free Software Foundation; either version 2 of the License, or
  8. #  (at your option) any later version.
  9. #
  10. #  This program is distributed in the hope that it will be useful,
  11. #  but WITHOUT ANY WARRANTY; without even the implied warranty of
  12. #  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  13. #  GNU General Public License for more details.
  14. #
  15. #  You should have received a copy of the GNU General Public License
  16. #   along with this program; if not, write to the Free Software
  17. #  Foundation, Inc.,
  18. #  51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA
  20. ## Note: This is a sample script and is not complete. Modify the script based on your environment.
  22. use strict;
  23. use warnings FATAL => 'all';
  25. use Getopt::Long;
  26. use MHA::DBHelper;
  27. use MHA::NodeUtil;
  28. use Time::HiRes qw( sleep gettimeofday tv_interval );
  29. use Data::Dumper;
  31. my $_tstart;
  32. my $_running_interval = 0.1;
  33. #添加vip定义
  34. my $vip = "192.168.1.100";
  35. my $if = "eth0";
  37. my (
  38.   $command,              $orig_master_is_new_slave, $orig_master_host,
  39.   $orig_master_ip,       $orig_master_port,         $orig_master_user,
  40.   $orig_master_password, $orig_master_ssh_user,     $new_master_host,
  41.   $new_master_ip,        $new_master_port,          $new_master_user,
  42.   $new_master_password,  $new_master_ssh_user,
  43. );
  44. GetOptions(
  45.   'command=s'                => \$command,
  46.   'orig_master_is_new_slave' => \$orig_master_is_new_slave,
  47.   'orig_master_host=s'       => \$orig_master_host,
  48.   'orig_master_ip=s'         => \$orig_master_ip,
  49.   'orig_master_port=i'       => \$orig_master_port,
  50.   'orig_master_user=s'       => \$orig_master_user,
  51.   'orig_master_password=s'   => \$orig_master_password,
  52.   'orig_master_ssh_user=s'   => \$orig_master_ssh_user,
  53.   'new_master_host=s'        => \$new_master_host,
  54.   'new_master_ip=s'          => \$new_master_ip,
  55.   'new_master_port=i'        => \$new_master_port,
  56.   'new_master_user=s'        => \$new_master_user,
  57.   'new_master_password=s'    => \$new_master_password,
  58.   'new_master_ssh_user=s'    => \$new_master_ssh_user,
  59. );
  61. exit &main();
  62. sub drop_vip {
  63.         my $output = `ssh -o ConnectTimeout=15  -o ConnectionAttempts=3 $orig_master_host /sbin/ip addr del $vip/32 dev $if`;
  64.     #mysql里的连接全部干掉
  65.     #FIXME
  66. }
  67. sub add_vip {
  68.         my $output = `ssh -o ConnectTimeout=15  -o ConnectionAttempts=3 $new_master_host /sbin/ip addr add $vip/32 dev $if`;
  70. }
  73. sub current_time_us {
  74.   my ( $sec, $microsec ) = gettimeofday();
  75.   my $curdate = localtime($sec);
  76.   return $curdate . " " . sprintf( "%06d", $microsec );
  77. }
  79. sub sleep_until {
  80.   my $elapsed = tv_interval($_tstart);
  81.   if ( $_running_interval > $elapsed ) {
  82.     sleep( $_running_interval - $elapsed );
  83.   }
  84. }
  86. sub get_threads_util {
  87.   my $dbh                    = shift;
  88.   my $my_connection_id       = shift;
  89.   my $running_time_threshold = shift;
  90.   my $type                   = shift;
  91.   $running_time_threshold = 0 unless ($running_time_threshold);
  92.   $type                   = 0 unless ($type);
  93.   my @threads;
  95.   my $sth = $dbh->prepare("SHOW PROCESSLIST");
  96.   $sth->execute();
  98.   while ( my $ref = $sth->fetchrow_hashref() ) {
  99.     my $id         = $ref->{Id};
  100.     my $user       = $ref->{User};
  101.     my $host       = $ref->{Host};
  102.     my $command    = $ref->{Command};
  103.     my $state      = $ref->{State};
  104.     my $query_time = $ref->{Time};
  105.     my $info       = $ref->{Info};
  106.     $info =~ s/^\s*(.*?)\s*$/$1/ if defined($info);
  107.     next if ( $my_connection_id == $id );
  108.     next if ( defined($query_time) && $query_time < $running_time_threshold );
  109.     next if ( defined($command)    && $command eq "Binlog Dump" );
  110.     next if ( defined($user)       && $user eq "system user" );
  111.     next
  112.       if ( defined($command)
  113.       && $command eq "Sleep"
  114.       && defined($query_time)
  115.       && $query_time >= 1 );
  117.     if ( $type >= 1 ) {
  118.       next if ( defined($command) && $command eq "Sleep" );
  119.       next if ( defined($command) && $command eq "Connect" );
  120.     }
  122.     if ( $type >= 2 ) {
  123.       next if ( defined($info) && $info =~ m/^select/i );
  124.       next if ( defined($info) && $info =~ m/^show/i );
  125.     }
  127.     push @threads, $ref;
  128.   }
  129.   return @threads;
  130. }
  132. sub main {
  133.   if ( $command eq "stop" ) {
  134.     ## Gracefully killing connections on the current master
  135.     # 1. Set read_only= 1 on the new master
  136.     # 2. DROP USER so that no app user can establish new connections
  137.     # 3. Set read_only= 1 on the current master
  138.     # 4. Kill current queries
  139.     # * Any database access failure will result in script die.
  140.     my $exit_code = 1;
  141.     eval {
  142.       ## Setting read_only=1 on the new master (to avoid accident)
  143.       my $new_master_handler = new MHA::DBHelper();
  145.       # args: hostname, port, user, password, raise_error(die_on_error)_or_not
  146.       $new_master_handler->connect( $new_master_ip, $new_master_port,
  147.         $new_master_user, $new_master_password, 1 );
  148.       print current_time_us() . " Set read_only on the new master.. ";
  149.       $new_master_handler->enable_read_only();
  150.       if ( $new_master_handler->is_read_only() ) {
  151.         print "ok.\n";
  152.       }
  153.       else {
  154.         die "Failed!\n";
  155.       }
  156.       $new_master_handler->disconnect();
  158.       # Connecting to the orig master, die if any database error happens
  159.       my $orig_master_handler = new MHA::DBHelper();
  160.       $orig_master_handler->connect( $orig_master_ip, $orig_master_port,
  161.         $orig_master_user, $orig_master_password, 1 );
  163.       ## Drop application user so that nobody can connect. Disabling per-session binlog beforehand
  164.       $orig_master_handler->disable_log_bin_local();
  165.      # print current_time_us() . " Drpping app user on the orig master..\n";
  166.       print current_time_us() . " drop vip $vip..\n";
  167.       #drop_app_user($orig_master_handler);
  168.      &drop_vip();
  170.       ## Waiting for N * 100 milliseconds so that current connections can exit
  171.       my $time_until_read_only = 15;
  172.       $_tstart = [gettimeofday];
  173.       my @threads = get_threads_util( $orig_master_handler->{dbh},
  174.         $orig_master_handler->{connection_id} );
  175.       while ( $time_until_read_only > 0 && $#threads >= 0 ) {
  176.         if ( $time_until_read_only % 5 == 0 ) {
  177.           printf
  178. "%s Waiting all running %d threads are disconnected.. (max %d milliseconds)\n",
  179.             current_time_us(), $#threads + 1, $time_until_read_only * 100;
  180.           if ( $#threads < 5 ) {
  181.             print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n"
  182.               foreach (@threads);
  183.           }
  184.         }
  185.         sleep_until();
  186.         $_tstart = [gettimeofday];
  187.         $time_until_read_only--;
  188.         @threads = get_threads_util( $orig_master_handler->{dbh},
  189.           $orig_master_handler->{connection_id} );
  190.       }
  192.       ## Setting read_only=1 on the current master so that nobody(except SUPER) can write
  193.       print current_time_us() . " Set read_only=1 on the orig master.. ";
  194.       $orig_master_handler->enable_read_only();
  195.       if ( $orig_master_handler->is_read_only() ) {
  196.         print "ok.\n";
  197.       }
  198.       else {
  199.         die "Failed!\n";
  200.       }
  202.       ## Waiting for M * 100 milliseconds so that current update queries can complete
  203.       my $time_until_kill_threads = 5;
  204.       @threads = get_threads_util( $orig_master_handler->{dbh},
  205.         $orig_master_handler->{connection_id} );
  206.       while ( $time_until_kill_threads > 0 && $#threads >= 0 ) {
  207.         if ( $time_until_kill_threads % 5 == 0 ) {
  208.           printf
  209. "%s Waiting all running %d queries are disconnected.. (max %d milliseconds)\n",
  210.             current_time_us(), $#threads + 1, $time_until_kill_threads * 100;
  211.           if ( $#threads < 5 ) {
  212.             print Data::Dumper->new( [$_] )->Indent(0)->Terse(1)->Dump . "\n"
  213.               foreach (@threads);
  214.           }
  215.         }
  216.         sleep_until();
  217.         $_tstart = [gettimeofday];
  218.         $time_until_kill_threads--;
  219.         @threads = get_threads_util( $orig_master_handler->{dbh},
  220.           $orig_master_handler->{connection_id} );
  221.       }
  223.       ## Terminating all threads
  224.       print current_time_us() . " Killing all application threads..\n";
  225.       $orig_master_handler->kill_threads(@threads) if ( $#threads >= 0 );
  226.       print current_time_us() . " done.\n";
  227.       $orig_master_handler->enable_log_bin_local();
  228.       $orig_master_handler->disconnect();
  230.       ## After finishing the script, MHA executes FLUSH TABLES WITH READ LOCK
  231.       $exit_code = 0;
  232.     };
  233.     if ($@) {
  234.       warn "Got Error: $@\n";
  235.       exit $exit_code;
  236.     }
  237.     exit $exit_code;
  238.   }
  239.   elsif ( $command eq "start" ) {
  240.     ## Activating master ip on the new master
  241.     # 1. Create app user with write privileges
  242.     # 2. Moving backup script if needed
  243.     # 3. Register new master's ip to the catalog database
  245. # We don't return error even though activating updatable accounts/ip failed so that we don't interrupt slaves' recovery.
  246. # If exit code is 0 or 10, MHA does not abort
  247.     my $exit_code = 10;
  248.     eval {
  249.       my $new_master_handler = new MHA::DBHelper();
  251.       # args: hostname, port, user, password, raise_error_or_not
  252.       $new_master_handler->connect( $new_master_ip, $new_master_port,
  253.         $new_master_user, $new_master_password, 1 );
  255.       ## Set read_only=0 on the new master
  256.       $new_master_handler->disable_log_bin_local();
  257.       print current_time_us() . " Set read_only=0 on the new master.\n";
  258.       $new_master_handler->disable_read_only();
  260.       ## Creating an app user on the new master
  261.       #print current_time_us() . " Creating app user on the new master..\n";
  262.       print current_time_us() . "Add vip $vip on $if..\n";
  263.      # create_app_user($new_master_handler);
  264.       &add_vip();
  265.       $new_master_handler->enable_log_bin_local();
  266.       $new_master_handler->disconnect();
  268.       ## Update master ip on the catalog database, etc
  269.       $exit_code = 0;
  270.     };
  271.     if ($@) {
  272.       warn "Got Error: $@\n";
  273.       exit $exit_code;
  274.     }
  275.     exit $exit_code;
  276.   }
  277.   elsif ( $command eq "status" ) {
  279.     # do nothing
  280.     exit 0;
  281.   }
  282.   else {
  283.     &usage();
  284.     exit 1;
  285.   }
  286. }
  288. sub usage {
  289.   print
  290. "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";
  291.   die;
  292. }
  1. vim /etc/masterha/app1.conf
  1. [server default]
  4. #mha manager工作目录
  5. manager_workdir = /var/log/masterha/app1
  6. manager_log = /var/log/masterha/app1/app1.log
  7. remote_workdir = /var/log/masterha/app1
  9. [server1]
  10. hostname=192.168.50.3
  11. master_binlog_dir = /storage/mysql/logs
  12. candidate_master = 1
  13. check_repl_delay = 0     #用防止master故障时，切换时slave有延迟，卡在那里切不过来。
  15. [server2]
  16. hostname=192.168.50.4
  17. master_binlog_dir=/storage/mysql/logs
  18. candidate_master=1
  19. check_repl_delay=0

七、配置文件测试

  1. masterha_check_ssh --global_conf=/etc/masterha/masterha_default.cnf --conf=/etc/masterha/app1.conf

确认可以看到所有的服务器上ssh测试通过

查看是不是具备跑masterha_manager，主从结构是不是OK

  1. masterha_check_repl --global_conf=/etc/masterha/masterha_default.cnf --conf=/etc/masterha/app1.conf

可以看到现有master/slave结构

MHA启动及关闭

  1. masterha_manager --global_conf=/etc/masterha/masterha_default.cnf --conf=/etc/masterha/app1.conf

注意：第一次启动，主库上的VIP不会自动绑定，需要手动调用init_vip.sh去绑定，主库发生故障切换会进行vip的飘逸。
至此，一主一从结构的MHA就配置好了。