本文共 15641 字，大约阅读时间需要 52 分钟。

1 MHA的概述

支持故障切换，在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用

MHA还提供在线主库切换的功能，能够安全地切换当前运行的主库到一个新的主库中(通过将从库提升为主库),大概0.5-2秒内即可完成

部署MHA的原因

• 传统mysql主从架构存在单点故障的问题
• 传统架构中，只有一个mysql主服务器，所以当出现单点故障的时候，整个服务器群集就会瘫痪掉
  为了解决这种情况，我们需要在主服务器宕机的时候，重新建立一台主服务器，负责监控等工作

MHA的组成

• MHA Manager（管理节点）：用来接收外部信号，监控下方数据节点的工作状态
• MHA Node（数据节点）：工作的单位，负责具体的工作

MHA特点

• 自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失
• 使用 MySQL55的半同步复制，可以大大降低数据丢失的风险

2 实验环境

2.1 配置无密码认证

（1）节点间的免密

server4：ssh-keygen

server4和123三个节点免密

ssh-copy-id server1	ssh-copy-id server2	ssh-copy-id server3

server1、server2、server3之间互相免密

scp -r .ssh/ server1:	scp -r .ssh/ server2:	scp -r .ssh/ server3:

2.2 数据库的主从复制

主从复制环境中的mysql同步的用户名为：root，密码：westos

（1）为保证实验环境的纯净，server1、server2、server3三个节点清除mysql的数据目录：

cd /data/mysql/	 rm -rf *	 mysqld --initialize --user=mysql	 /etc/init.d/mysqld start 	 mysql -p	 ALTER USER root@localhost identified by 'westos';

（2）所有数据库授权marry用户对数据库的操作权限，每一个mysql数据库都要设置

• 授权：grant replication slave on *.* to marry@'%' identified by 'westos';
• 查看master的状态信息：

show master status\G;

server2和server3

cd /data/mysql/	 rm -rf *	 mysqld --initialize --user=mysql ## 初始化数据库	 /etc/init.d/mysqld start   ## 启动mysql	 mysql -p  ##  登录数据库	 ALTER USER root@localhost identified by 'westos';  ## 修改本地root用户的密码	 change master to master_host='172.25.12.1', master_user='marry',master_password='westos',master_auto_position=1;	 start slave;  ## 启动slave

server1:远程登录

• 在server1上更改，因为server1为master，server2、server3自动生效

grant all on *.* to root@'%' identified by 'westos';  ## 授予远程用户root所有的权限 select * from mysql.user where user='root';  ## 查看root用户的信息

3 MHA的部署

3.1 安装Node组件和Manager组件

（1）安装mysql的高可用节点（server1、server2、server3）：

yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm

（2）server4安装mysql高可用管理软件:yum install -y mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

3.2 配置MHA

（1）创建MHA软件目录并拷贝配置文件

mkdir /etc/masterhacp  /root/MHA-7/mha4mysql-manager-0.58/samples/conf/app1.cnf   /etc/masterha

• 编辑配置文件：

cd   /root/MHA-7/mha4mysql-manager-0.58/samples/conf/vim masterha_default.cnf

• 编辑配置文件：vim /etc/masterha/app1.cnf

[server default]user=rootpassword=westosssh_user=rootmaster_binlog_dir= /data/mysqlremote_workdir=/tmpsecondary_check_script= masterha_secondary_check -s 172.25.12.1 -s 172.25.12.2ping_interval=3# master_ip_failover_script= /script/masterha/master_ip_failover# shutdown_script= /script/masterha/power_manager# report_script= /script/masterha/send_report# master_ip_online_change_script= /script/masterha/master_ip_online_changemanager_workdir=/etc/masterha/app1manager_log=/etc/masterha/app1/manager.logrepl_user=marryrepl_password=westos[server1]hostname=172.25.12.1[server2]hostname=172.25.12.2candidate_master=1check_repl_delay=0[server3]hostname=172.25.12.3no_master=1

（3）查看ssh登陆是否成功：masterha_check_ssh --conf=/etc/masterha/app1.cnf

• 查看复制是否建立好 ：masterha_check_repl --conf=/etc/masterha/app1.cnf

[root@server4 masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnfThu Jan 14 05:29:00 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.Thu Jan 14 05:29:00 2021 - [info] Reading application default configuration from /etc/masterha/app1.cnf..Thu Jan 14 05:29:00 2021 - [info] Reading server configuration from /etc/masterha/app1.cnf..Thu Jan 14 05:29:00 2021 - [info] MHA::MasterMonitor version 0.58.Creating directory /etc/masterha/app1.. done.Thu Jan 14 05:29:02 2021 - [info] GTID failover mode = 1Thu Jan 14 05:29:02 2021 - [info] Dead Servers:Thu Jan 14 05:29:02 2021 - [info] Alive Servers:Thu Jan 14 05:29:02 2021 - [info]   172.25.12.1(172.25.12.1:3306)Thu Jan 14 05:29:02 2021 - [info]   172.25.12.2(172.25.12.2:3306)Thu Jan 14 05:29:02 2021 - [info]   172.25.12.3(172.25.12.3:3306)Thu Jan 14 05:29:02 2021 - [info] Alive Slaves:Thu Jan 14 05:29:02 2021 - [info]   172.25.12.2(172.25.12.2:3306)  Version=5.7.31-log (oldest major version between slaves) log-bin:enabledThu Jan 14 05:29:02 2021 - [info]     GTID ONThu Jan 14 05:29:02 2021 - [info]     Replicating from 172.25.12.1(172.25.12.1:3306)Thu Jan 14 05:29:02 2021 - [info]     Primary candidate for the new Master (candidate_master is set)Thu Jan 14 05:29:02 2021 - [info]   172.25.12.3(172.25.12.3:3306)  Version=5.7.31-log (oldest major version between slaves) log-bin:enabledThu Jan 14 05:29:02 2021 - [info]     GTID ONThu Jan 14 05:29:02 2021 - [info]     Replicating from 172.25.12.1(172.25.12.1:3306)Thu Jan 14 05:29:02 2021 - [info]     Not candidate for the new Master (no_master is set)Thu Jan 14 05:29:02 2021 - [info] Current Alive Master: 172.25.12.1(172.25.12.1:3306)Thu Jan 14 05:29:02 2021 - [info] Checking slave configurations..Thu Jan 14 05:29:02 2021 - [info]  read_only=1 is not set on slave 172.25.12.2(172.25.12.2:3306).Thu Jan 14 05:29:02 2021 - [info]  read_only=1 is not set on slave 172.25.12.3(172.25.12.3:3306).Thu Jan 14 05:29:02 2021 - [info] Checking replication filtering settings..Thu Jan 14 05:29:02 2021 - [info]  binlog_do_db= , binlog_ignore_db= Thu Jan 14 05:29:02 2021 - [info]  Replication filtering check ok.Thu Jan 14 05:29:02 2021 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.Thu Jan 14 05:29:02 2021 - [info] Checking SSH publickey authentication settings on the current master..Thu Jan 14 05:29:02 2021 - [info] HealthCheck: SSH to 172.25.12.1 is reachable.Thu Jan 14 05:29:02 2021 - [info] 172.25.12.1(172.25.12.1:3306) (current master) +--172.25.12.2(172.25.12.2:3306) +--172.25.12.3(172.25.12.3:3306)Thu Jan 14 05:29:02 2021 - [info] Checking replication health on 172.25.12.2..Thu Jan 14 05:29:02 2021 - [info]  ok.Thu Jan 14 05:29:02 2021 - [info] Checking replication health on 172.25.12.3..Thu Jan 14 05:29:02 2021 - [info]  ok.Thu Jan 14 05:29:02 2021 - [warning] master_ip_failover_script is not defined.Thu Jan 14 05:29:02 2021 - [warning] shutdown_script is not defined.Thu Jan 14 05:29:02 2021 - [info] Got exit code 0 (Not master dead).MySQL Replication Health is OK.

4 MHA的故障切换

4.1 手动切换master

（1）手动执行failover机制（server4），切换master

masterha_master_switch --master_state=dead \ ## --master_state=dead 强制的参数，参数值为"dead" 或者 "alive" . 如果 设置为 alive 模式，masterha_master_switch 开始在线主库切换操作。--conf=/etc/masterha/app1.cnf \ --master_state=alive \--new_master_host=172.25.12.2 \ ##   新主机地址，可选参数，明确新的主库的主机--new_master_port=3306 \  ## --orig_master_is_new_slave

• 查看slave端的状态（server3）：show slave status\G;

• server2： show slave status\G;

• server1：show slave status\G;

（2）server2端的mysql停止服务： /etc/init.d/mysqld stop

• 手动执行failover机制（server4），切换master

masterha_master_switch --master_state=dead \ --conf=/etc/masterha/app1.cnf \ --master_state=dead \ --dead_master_host=172.25.12.2 \ --dead_master_port=3306  \ --new_master_host=172.25.12.1 \ --new_master_port=3306

• master节点切换到server1

• server3：show slave status\G;

• 查看slave的状态（server1）：show slave status\G;

server2:

• 同步server1的mysql信息

CHANGE MASTER TO MASTER_HOST='172.25.12.1', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='marry', MASTER_PASSWORD='westos';

• 启动serve2的slave：start slave;
• 查看slave的状态：show slave status\G;

• 将mha的manager打入后台运行：

masterha_manager --conf=/etc/masterha/app1.cnf &

• 查看进程： ps ax

• 每次failover切换后会在管理目录生成文件app1.failover.complete，下次在切换的时候会发现有这个文件导致切换不成功，需要手动清理掉

rm -rf app1.failover.complete

server1停止mysql服务：/etc/init.d/mysqld stop

server4:

• 查看manager的日志信息：cat /etc/masterha/app1/manager.log，mysql的master节点server1已经宕机

server3为slave，master为server2：show slave status\G;

将server1的master切换为server2

CHANGE MASTER TO MASTER_HOST='172.25.12.2', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='marry', MASTER_PASSWORD='westos'; start slave; ## 启动slave show slave status\G;  ## 查看slave的状态

4.2 自动切换master

（1）给脚本添加执行的权限

chmod +x master_ip_failoverchmod +x master_ip_online_change

（2） MHA故障切换脚本： vim master_ip_failover

#!/usr/bin/env perluse 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.25.12.100/24';my $ssh_start_vip = "/sbin/ip addr add  $vip dev eth0";my $ssh_stop_vip = "/sbin/ip addr  del  $vip dev eth0";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" ) {           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" ) {           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";        exit 0;    }    else {           &usage();        exit 1;    }}sub start_vip() {       `ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;}sub stop_vip() {        return 0  unless  ($ssh_user);    `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";}

（3）编辑主库在线切换脚本：vim master_ip_online_change

#!/usr/bin/env perluse strict;  use warnings FATAL =>'all';    use Getopt::Long;  my $vip = '172.25.12.100/24';my $ssh_start_vip = "/sbin/ip addr add  $vip dev eth0";my $ssh_stop_vip = "/sbin/ip addr  del  $vip dev eth0";  my $exit_code = 0;    my (    $command,              $orig_master_is_new_slave, $orig_master_host,    $orig_master_ip,       $orig_master_port,         $orig_master_user,    $orig_master_password, $orig_master_ssh_user,     $new_master_host,    $new_master_ip,        $new_master_port,          $new_master_user,    $new_master_password,  $new_master_ssh_user,  );  GetOptions(    'command=s'                => \$command,    'orig_master_is_new_slave' => \$orig_master_is_new_slave,    'orig_master_host=s'       => \$orig_master_host,    'orig_master_ip=s'         => \$orig_master_ip,    'orig_master_port=i'       => \$orig_master_port,    'orig_master_user=s'       => \$orig_master_user,    'orig_master_password=s'   => \$orig_master_password,    'orig_master_ssh_user=s'   => \$orig_master_ssh_user,    'new_master_host=s'        => \$new_master_host,    'new_master_ip=s'          => \$new_master_ip,    'new_master_port=i'        => \$new_master_port,    'new_master_user=s'        => \$new_master_user,    'new_master_password=s'    => \$new_master_password,    'new_master_ssh_user=s'    => \$new_master_ssh_user,  );      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 "\n\n\n***************************************************************\n";              print "Disabling the VIP - $vip on old master: $orig_master_host\n";              print "***************************************************************\n\n\n\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 "\n\n\n***************************************************************\n";              print "Enabling the VIP - $vip on new master: $new_master_host \n";              print "***************************************************************\n\n\n\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 $orig_master_ssh_user\@$orig_master_host \" $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 $new_master_ssh_user\@$new_master_host \" $ssh_start_vip \"`;  }  # A simple system call that disable the VIP on the old_master  sub stop_vip() {     `ssh $orig_master_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";  }

（4）编辑mha的配置文件：vim /etc/masterha/app1.cnf

[server default]user=rootpassword=westosssh_user=rootmaster_binlog_dir= /data/mysqlremote_workdir=/tmpsecondary_check_script= masterha_secondary_check -s 172.25.12.1 -s 172.25.12.2ping_interval=3master_ip_failover_script= /etc/masterha/master_ip_failover ## MHA的故障切换脚本# shutdown_script= /script/masterha/power_manager# report_script= /script/masterha/send_reportmaster_ip_online_change_script= /etc/masterha/master_ip_online_change  ## MHA的master在线切换脚本manager_workdir=/etc/masterha/app1manager_log=/etc/masterha/app1/manager.logrepl_user=marryrepl_password=westos[server1]hostname=172.25.12.1[server2]hostname=172.25.12.2candidate_master=1check_repl_delay=0[server3]hostname=172.25.12.3no_master = 1

（5）每次failover切换后会在管理目录生成文件app1.failover.complete，下次在切换的时候会发现有这个文件导致切换不成功，需要手动清理掉

rm -rf app1.failover.complete

（6） 启动MHA：

masterha_manager --conf=/etc/masterha/app1.cnf &

• 查看进程：ps ax

（7）测试

• server2添加vip：ip addr add 172.25.12.100/24 dev eth0

• 停止server2的mysql服务：

/etc/init.d/mysqld stop

（8）实验结果

• server4查看manager的日志信息：

cat /etc/masterha/app1/manager.log

• master自动迁移到server1上

• server3查看slave的状态：show slave status\G;

• master节点自动切换后，vip也随之迁移到server1：ip addr

• server2启动mysql服务：/etc/init.d/mysqld start

• server2同步server1的数据库

CHANGE MASTER TO MASTER_HOST='172.25.12.1', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='marry', MASTER_PASSWORD='westos';

• server2启动slave：start slave;

（1）手动执行failover机制

• server4删除文件：rm -rf app1.failover.complete

• 手动执行failover机制（server4），切换master

masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.12.2 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

（2）实验结果

• 查看server4的日志信息：cat /etc/masterha/app1/manager.log

• master切换到server2，vip也随之漂移，查看slave的状态（server3）：show slave status\G;

• 查看slave的状态（server2）：show slave status\G;

• 查看ip（server2）： ip addr

• 查看server1的slave的状态：

show slave status\G;

• 查看server1的ip地址： ip addr

转载地址：http://jlknz.baihongyu.com/