MHA 安装部署

1、MHA简介

MHA（Master High Availability）目前在MySQL高可用方面是一个相对成熟的解决方案，它由日本DeNA公司的youshimaton（现就职于Facebook公司）开发，是一套优秀的作为MySQL高可用性环境下故障切换和主从提升的高可用软件。在MySQL故障切换过程中，MHA能做到在0~30秒之内自动完成数据库的故障切换操作，并且在进行故障切换的过程中，MHA能在最大程度上保证数据的一致性，以达到真正意义上的高可用。

2、实验环境

10.10.10.2 master

10.10.10.3 slave+mha_manage

vip: 10.10.10.10

网关：10.10.10.1

3、环境搭建

3.1 安装mysql及主从配置略

3.2 配置SSH免密登陆

1）在所有节点执行

[root@dyh02 ~]# mkdir .ssh
[root@dyh02 ~]# chmod 700 .ssh
[root@dyh02 ~]# cd .ssh
[root@dyh02 .ssh]# ssh-keygen -t rsa 
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:pAj2ZdMJAXvU0XDQF8lrAvsCVxWzdQ1fkWyj2d1SbYI root@dyh02
The key's randomart image is:
+---[RSA 2048]----+
|    ..+o+*.o==+oB|
|     o ooo+ E+.B*|
|  o . = ++ ...=+=|
| . o =.+o . oo..o|
|    o .oS. o   . |
|        . .      |
|         .       |
|                 |
|                 |
+----[SHA256]-----+

[root@dyh02 .ssh]# ssh 10.10.10.2 cat /root/.ssh/id_rsa.pub >> authorized_keys
[root@dyh02 .ssh]# ssh 10.10.10.3 cat /root/.ssh/id_rsa.pub >> authorized_keys
[root@dyh02 .ssh]# ssh 10.10.10.4 cat /root/.ssh/id_rsa.pub >> authorized_keys
[root@dyh02 .ssh]# chmod 600 authorized_keys
[root@dyh02 .ssh]# scp -p authorized_keys 10.10.10.3:/root/.ssh/
[root@dyh02 .ssh]# scp -p authorized_keys 10.10.10.4:/root/.ssh/

3)各节点验证

[root@dyh02 .ssh]# ssh 10.10.10.2 date
Wed Mar 20 13:54:05 CST 2019
[root@dyh02 .ssh]# 
[root@dyh02 .ssh]# ssh 10.10.10.3 date
Wed Mar 20 13:54:09 CST 2019
[root@dyh02 .ssh]# 
[root@dyh02 .ssh]# ssh 10.10.10.4 date
Wed Mar 20 13:54:12 CST 2019
[root@dyh02 .ssh]#

3.3 在各节点安装node

[root@dyh02 mha]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm 
Preparing...                          ################################# [100%]
Updating / installing...
   1:mha4mysql-node-0.56-0.el6        ################################# [100%]
[root@dyh02 mha]# 
[root@dyh02 mha]#

3.4在管理节点安装manage

[root@dyh02 rpm]# rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm 
Preparing...                          ################################# [100%]
Updating / installing...
   1:mha4mysql-manager-0.56-0.el6     ################################# [100%]
[root@dyh02 rpm]#

3.5 创建软链接


[root@dyh02 rpm]# ln -s /apps/svr/mysql-5.6.25/bin/mysql /usr/bin/mysql
[root@dyh02 rpm]# ln -s /apps/svr/mysql-5.6.25/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@dyh02 rpm]#

3.6 修改配置文件


[root@dyh02 mha]# pwd
/apps/svr/mha
[root@dyh02 mha]# 
[root@dyh02 mha]# vi app1.cnf
 
[server default]
manager_workdir=/apps/svr/mha
manager_log=/apps/svr/mha/logs/manager.log
secondary_check_script= masterha_secondary_check -s 10.10.10.2 -s 10.10.10.3
master_ip_failover_script=/apps/svr/mha/master_ip_failover
 
#mha用户
user=root
password=123456
 
#ssh用户
ssh_user=root
 
# 主从复制用户
repl_user=root
repl_password=123456
 
ping_interval=3
 
[server1]
hostname=10.10.10.2
candidate_master=1
master_binlog_dir=/apps/svr/mysql-5.6.25/logs/binlog
 
[server2]
hostname=10.10.10.3
candidate_master=1
master_binlog_dir=/apps/svr/mysql-5.6.25/logs/binlog
 
[server3]
hostname=10.10.10.4
no_master=1
master_binlog_dir=/apps/svr/mysql-5.6.25/logs/binlog

3.7 修改切换脚本

[root@dyh02 mha]# vi /apps/svr/mha/master_ip_failover
#!/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 = '10.10.10.10/24'; # Virtual IP
my $gateway = '10.10.10.1'; #Gateway IP
my $interface = 'ens37';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1";
my $ssh_stop_vip = "/sbin/ifconfig $interface:$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 - $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";
`ssh $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 $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 --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.8 SSH配置测试

[root@dyh02 rpm]# /bin/masterha_check_ssh --conf=/apps/svr/mha/app1.cnf 
Wed Mar 20 19:45:00 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Wed Mar 20 19:45:00 2019 - [info] Reading application default configuration from /apps/svr/mha/app1.cnf..
Wed Mar 20 19:45:00 2019 - [info] Reading server configuration from /apps/svr/mha/app1.cnf..
Wed Mar 20 19:45:00 2019 - [info] Starting SSH connection tests..
Wed Mar 20 19:45:01 2019 - [debug] 
Wed Mar 20 19:45:00 2019 - [debug]  Connecting via SSH from [email protected](10.10.10.2:22) to [email protected](10.10.10.3:22)..
Wed Mar 20 19:45:00 2019 - [debug]   ok.
Wed Mar 20 19:45:00 2019 - [debug]  Connecting via SSH from [email protected](10.10.10.2:22) to [email protected](10.10.10.4:22)..
Wed Mar 20 19:45:01 2019 - [debug]   ok.
Wed Mar 20 19:45:01 2019 - [debug] 
Wed Mar 20 19:45:00 2019 - [debug]  Connecting via SSH from [email protected](10.10.10.3:22) to [email protected](10.10.10.2:22)..
Wed Mar 20 19:45:01 2019 - [debug]   ok.
Wed Mar 20 19:45:01 2019 - [debug]  Connecting via SSH from [email protected](10.10.10.3:22) to [email protected](10.10.10.4:22)..
Wed Mar 20 19:45:01 2019 - [debug]   ok.
Wed Mar 20 19:45:02 2019 - [debug] 
Wed Mar 20 19:45:01 2019 - [debug]  Connecting via SSH from [email protected](10.10.10.4:22) to [email protected](10.10.10.2:22)..
Wed Mar 20 19:45:01 2019 - [debug]   ok.
Wed Mar 20 19:45:01 2019 - [debug]  Connecting via SSH from [email protected](10.10.10.4:22) to [email protected](10.10.10.3:22)..
Wed Mar 20 19:45:02 2019 - [debug]   ok.
Wed Mar 20 19:45:02 2019 - [info] All SSH connection tests passed successfully.
[root@dyh02 rpm]#

3.9 REPL测试

[root@dyh02 rpm]# /bin/masterha_check_repl --conf=/apps/svr/mha/app1.cnf
Checking the Status of the script.. OK 
Wed Mar 20 19:51:05 2019 - [info]  OK.
Wed Mar 20 19:51:05 2019 - [warning] shutdown_script is not defined.
Wed Mar 20 19:51:05 2019 - [info] Got exit code 0 (Not master dead).
 
MySQL Replication Health is OK.
[root@dyh02 rpm]#

4、故障转移测试

4.1启动MHA

[root@dyh02 mha]# /bin/masterha_manager --conf=/apps/svr/mha/conf/app1.cnf > /apps/svr/mha/logs/manager.log 2>&1 &

4.2查看当前vip,在主库10.2上

[root@dyh02 mha]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        ether 00:0c:29:0e:a2:66  txqueuelen 1000  (Ethernet)
        RX packets 14274  bytes 1177999 (1.1 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
ens37: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.10.10.2  netmask 255.255.255.0  broadcast 10.10.10.255
        inet6 fe80::d058:bb81:ac3d:6d62  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:0e:a2:70  txqueuelen 1000  (Ethernet)
        RX packets 519713  bytes 465341468 (443.7 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 1977431  bytes 5114309202 (4.7 GiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 433  bytes 41772 (40.7 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 433  bytes 41772 (40.7 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
[root@dyh02 mha]# 
[root@dyh02 mha]#

4.3 停止主库

[root@dyh02 mha]# /apps/sh/mysql.sh stop
Warning: Using a password on the command line interface can be insecure.
[root@dyh02 mha]# 
[root@dyh02 mha]#

4.4 查看vip已经漂到10.3上了

[root@dyh03 mha]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        ether 00:0c:29:4e:d7:49  txqueuelen 1000  (Ethernet)
        RX packets 14662  bytes 1209992 (1.1 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
ens37: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.10.10.3  netmask 255.255.255.0  broadcast 10.10.10.255
        inet6 fe80::d058:bb81:ac3d:6d62  prefixlen 64  scopeid 0x20<link>
        inet6 fe80::be81:e911:65d0:6cd6  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:4e:d7:53  txqueuelen 1000  (Ethernet)
        RX packets 959880  bytes 1356912440 (1.2 GiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 99524  bytes 9990168 (9.5 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
ens37:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 10.10.10.10  netmask 255.255.255.0  broadcast 10.10.10.255
        ether 00:0c:29:4e:d7:53  txqueuelen 1000  (Ethernet)
 
lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 1000  (Local Loopback)
        RX packets 5917  bytes 731961 (714.8 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 5917  bytes 731961 (714.8 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
 
[root@dyh03 mha]#

4.5 查看主从状态是否切换

[root@dyh04 mha]# /apps/svr/mysql-5.6.25/bin/mysql -uroot -p123456 -h127.0.0.1
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.6.25-log MySQL Community Server (GPL)
 
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.10.3
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 120
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 456
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 74ac1c93-4b01-11e9-8bf5-000c294ed753
             Master_Info_File: /apps/svr/mysql-5.6.25/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)
mysql>