MHA

最新推荐文章于 2025-02-24 12:00:00 发布

vhkgcf

最新推荐文章于 2025-02-24 12:00:00 发布

阅读量292

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/vhkgcf/article/details/82971922

MySQL MHA架构介绍：

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

该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。

在MHA自动故障切换过程中，MHA试图从宕机的主服务器上保存二进制日志，最大程度的保证数据的不丢失，但这并不总是可行的。例如，如果主服务器硬件故障或无法通过ssh访问，MHA没法保存二进制日志，只进行故障转移而丢失了最新的数据。使用MySQL 5.5的半同步复制，可以大大降低数据丢失的风险。MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性。

目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库，因为至少需要三台服务器，出于机器成本的考虑，淘宝也在该基础上进行了改造，目前淘宝TMHA已经支持一主一从。


角色                    ip地址          主机名          server_id      类型
Monitor host            172.25.61.6    server6            -         监控复制组
Master                  172.25.61.3    server3            1          写入
Candicate master        172.25.61.4    server4            2          读
Slave                   172.25.61.5    server5            3          读

Server3,Server4,server5:

[root@server4 mnt]# vim /etc/my.cnf ##配置server3，server5的配置文件时，需修改server-id

server-id=2
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE

[root@server4 mysql]# /etc/init.d/mysqld start

Initializing MySQL database:                               [  OK  ]
Installing validate password plugin:                       [  OK  ]
Starting mysqld:                                           [  OK  ]

[root@server4 mysql]# grep password /var/log/mysqld.log

2018-09-29T11:50:58.842562Z 1 [Note] A temporary password is generated for root@localhost: ?8wsi,YwUQLG

mysql> alter user root@localhost identified by 'Moshuai123!';
Query OK, 0 rows affected (0.07 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'westos'@'172.25.61.%' identified by 'Moshuai123!';
Query OK, 0 rows affected, 1 warning (0.11 sec)

Server4,server5:

mysql> change master to master_host='172.25.61.3',master_user='westos',
master_password='Moshuai123!',MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.47 sec)

mysql> start slave;
Query OK, 0 rows affected (0.08 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.61.3
                  Master_User: westos
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: server4-relay-bin.000003
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

测试：

Server3：

mysql> create database westos;
Query OK, 1 row affected (0.06 sec)

mysql> use westos;
Database changed
mysql> create table usertb(
    -> username varchar(25) not null,
    -> password varchar(25) not null);
Query OK, 0 rows affected (0.42 sec)

mysql> insert into usertb value ('user1','123');
Query OK, 1 row affected (0.15 sec)

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
1 row in set (0.00 sec)

Server4，server5：

mysql> use westos;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
+----------+----------+
1 row in set (0.00 sec)

Server3，server4，server5：

[root@server3 mnt]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y

Server6:

yum install -y mha4mysql-manager-0.56-0.el6.noarch.rpm \
mha4mysql-node-0.56-0.el6.noarch.rpm \
perl-Config-Tiny-2.12-7.1.el6.noarch.rpm \
perl-Email-Date-Format-1.002-5.el6.noarch.rpm \
perl-Log-Dispatch-2.27-1.el6.noarch.rpm \
perl-Mail-Sender-0.8.16-3.el6.noarch.rpm \
perl-Mail-Sendmail-0.79-12.el6.noarch.rpm \
perl-MIME-Lite-3.027-2.el6.noarch.rpm \
perl-MIME-Types-1.28-2.el6.noarch.rpm \
perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm

[root@server6 MHA]# mkdir /etc/masterha

[root@server6 MHA]# cd /etc/masterha/

[root@server6 masterha]# vim app1.cnf

[server default]
manager_workdir=/etc/masterha
manager_log=/etc/masterha/manager.log
master_binlog_dir=/var/lib/mysql
#master_ip_failover_script= /usr/local/bin/master_ip_failover
#master_ip_online_change_script= /usr/local/bin/master_ip_online_change  
password=Moshuai123!
user=root
ping_interval=1
remote_workdir=/tmp
repl_password=Moshuai123!
repl_user=westos
#report_script=/usr/local/send_report
#secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02            
#shutdown_script=""
ssh_user=root

[server3]
hostname=172.25.61.3
port=3306

[server4]
hostname=172.25.61.4
port=3306
candidate_master=1
check_repl_delay=0

[server5]
hostname=172.25.61.5
port=3306
#no_master=1

[root@server6 masterha]# ssh-keygen

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:
82:21:b1:e9:8a:e5:cd:f4:a9:1e:5c:43:52:4b:75:77 root@server6
The key's randomart image is:
+--[ RSA 2048]----+
|  .   o.. . . E  |
|   + o . . . .   |
|  + o o          |
| . . =           |
|  o o + S        |
|.+ = o +         |
|o . = o          |
|     o           |
|   .o            |
+-----------------+

[root@server6 masterha]# ssh-copy-id root@172.25.61.6

The authenticity of host '172.25.61.6 (172.25.61.6)' can't be established.
RSA key fingerprint is 29:5f:8f:5a:ca:fa:aa:90:e3:93:e5:e7:13:5f:e7:c1.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.25.61.6' (RSA) to the list of known hosts.
root@172.25.61.6's password: 
Now try logging into the machine, with "ssh '172.25.61.6'", and check in:

  .ssh/authorized_keys

to make sure we haven't added extra keys that you weren't expecting.

[root@server6 masterha]# scp -r ~/.ssh/ root@server3:
Warning: Permanently added 'server3,172.25.61.3' (RSA) to the list of known hosts.
root@server3's password: 
authorized_keys                         100%  394     0.4KB/s   00:00    
id_rsa.pub                              100%  394     0.4KB/s   00:00    
known_hosts                             100% 1195     1.2KB/s   00:00    
id_rsa                                  100% 1675     1.6KB/s   00:00    
[root@server6 masterha]# scp -r ~/.ssh/ root@server4:
root@server4's password: 
authorized_keys                         100%  394     0.4KB/s   00:00    
id_rsa.pub                              100%  394     0.4KB/s   00:00    
known_hosts                             100% 1596     1.6KB/s   00:00    
id_rsa                                  100% 1675     1.6KB/s   00:00    
[root@server6 masterha]# scp -r ~/.ssh/ root@server5:
root@server5's password: 
authorized_keys                         100%  394     0.4KB/s   00:00    
id_rsa.pub                              100%  394     0.4KB/s   00:00    
known_hosts                             100% 1596     1.6KB/s   00:00    
id_rsa                                  100% 1675     1.6KB/s   00:00

[root@server6 masterha]# masterha_check_ssh \ --conf=/etc/masterha/app1.cnf   ##检查ssh是否可以免密互联
Sat Sep 29 20:33:50 2018 - [info] All SSH connection tests passed successfully.

[root@server6 masterha]# masterha_check_repl --conf=/etc/masterha/app1.cnf   ##检查是否可以登陆数据库
MySQL Replication Health is OK.

[root@server6 masterha]# nohup masterha_manager --conf=/etc/masterha/app1.cnf  --ignore_last_failover  &   ##开启masterha
[1] 1242

[root@server6 masterha]# masterha_check_status --conf=/etc/masterha/app1.cnf     ##查看masterha状态
app1 (pid:1242) is running(0:PING_OK), master:172.25.61.3

[root@server6 masterha]# masterha_stop --conf=/etc/masterha/app1.cnf     ##停止masterha
Stopped app1 successfully.
[1]+  Exit 1                

[root@server6 masterha]# masterha_check_status --conf=/etc/masterha/app1.cnf
app1 is stopped(2:NOT_RUNNING).

测试：

自动切换：

Server6:

[root@server6 masterha]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --ignore_last_failover &

[1] 1242

Server3：

pkill -9 mysqld

手动切换：

Server4：

pkill -9 mysqld


[root@server6 ~]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.25.61.4 --dead_master_port=3306 --new_master_host=172.25.61.3 --new_master_port=3306 --ignore_last_failover

在线切换：

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

基于vip的切换(脚本方式)

Server6:

vim /usr/local/bin/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 = '172.25.20.100/24';
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" ) {

        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";
}

vim /usr/local/bin/master_ip_online_change

#!/usr/bin/env perl
use strict;
use warnings FATAL =>'all';

use Getopt::Long;

my $vip = '172.25.20.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";
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";
}

vim /usr/local/bin/send_report

#!/usr/bin/perl

#  Copyright (C) 2011 DeNA Co.,Ltd.
#
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#   along with this program; if not, write to the Free Software
#  Foundation, Inc.,
#  51 Franklin Street, Fifth Floor, Boston, MA  02110-1301  USA

## Note: This is a sample script and is not complete. Modify the script based on your environment.

use strict;
use warnings FATAL => 'all';
use Mail::Sender;
use Getopt::Long;

#new_master_host and new_slave_hosts are set only when recovering master succeeded
my ( $dead_master_host, $new_master_host, $new_slave_hosts, $subject, $body );
my $smtp='smtp.163.com';
my $mail_from='****@163.com';
my $mail_user='****@163.com';
my $mail_pass='****';
my $mail_to='****@163.com';
GetOptions(
  'orig_master_host=s' => \$dead_master_host,
  'new_master_host=s'  => \$new_master_host,
  'new_slave_hosts=s'  => \$new_slave_hosts,
  'subject=s'          => \$subject,
  'body=s'             => \$body,
);

mailToContacts($smtp,$mail_from,$mail_user,$mail_pass,$mail_to,$subject,$body);

sub mailToContacts {
    my ( $smtp, $mail_from, $user, $passwd, $mail_to, $subject, $msg ) = @_;
    open my $DEBUG, "> /tmp/monitormail.log"
        or die "Can't open the debug      file:$!\n";
    my $sender = new Mail::Sender {
        ctype       => 'text/plain; charset=utf-8',
        encoding    => 'utf-8',
        smtp        => $smtp,
        from        => $mail_from,
        auth        => 'LOGIN',
        TLS_allowed => '0',
        authid      => $user,
        authpwd     => $passwd,
        to          => $mail_to,
        subject     => $subject,
        debug       => $DEBUG
    };

    $sender->MailMsg(
        {   msg   => $msg,
            debug => $DEBUG
        }
    ) or print $Mail::Sender::Error;
    return 1;
}



# Do whatever you want here

exit 0;

chmod +x /usr/local/bin/*

vim /etc/masterha/app1.cnf

master_ip_failover_script= /usr/local/bin/master_ip_failover
master_ip_online_change_script= /usr/local/bin/master_ip_online_change
report_script=/usr/local/bin/send_report