Mysql MHA高可用软件

1
2
3
4
5
6

# 临时 
sql> set global relay_log_purge = 0;

# 永久
$> vim /etc/my.cnf
relay_log_purge = 0

1

sql> set global read_only=1

1
2
3
4

$> ssh-keygen 
$> ssh-copy-id root@10.0.2.25
$> ssh-copy-id root@10.0.2.26
$> ssh-copy-id root@10.0.2.27

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91

## 所有节点安装mha node软件包
# mha node 软件包依赖
$> yum install perl-DBD-MySQL -y 

## mha node 软件安装
$> yum install -y mha4mysql-node-0.58-0.el7.centos.noarch.rpm

## 主库添加MHA管理用户
$> mysql -uroot -e "grant all privileges on *.* to mha@'10.0.2.%' identified by 'mha';"

## 管理机安装manager软件(管理节点安装，建议独立一台或非主节点服务器)
# 依赖环境
$> yum install perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y 

# mha manager 软件安装
$> yum install perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes -y 
$> yum install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm -y

# 创建配置文件目录 
$> mkdir /etc/mha
$> mkdir -p /var/log/mha/app1
$> vim /etc/mha/app1.cnf
[server default]
# 用于管理stop slave,change master,reset slave等操作的账号，缺省为root
user=mha
password=mha

# mha manager生成的日志据对路径，如果没有设置，mha manager将打印在标准输出，标准错误输出上
manager_log=/var/log/mha/app1/manager

# mha manager生成的相关状态文件的绝对路径，如果没有设置，则默认使用/var/tmp
manager_workdir=/var/log/mha/app1

# 在master上生成binlog的绝对路径
master_binlog_dir=/data/mysqldb/binlog

# 这个参数表示mha manager多久ping（执行select ping sql语句）一次master，连续三个丢失ping连接，mha master就判定mater死了，因此，通过4次ping间隔的最大时间的机制来发现故障，默认是3，表示间隔是3秒
ping_interval=2

# repl_user参数指定的用户名密码
repl_user=repl
repl_password=123123

# 访问MHA manger和MHA mysql节点的OS系统帐号 
ssh_user=root

# > https://www.cnblogs.com/xiaoboluo768/p/5973827.html
# 故障时自动调用的脚本，一般用于自动vip漂移
master_ip_failover_script=/usr/local/bin/master_ip_failover

# 故障时发送报告调用的脚本
# report_script=

# 节点信息配置，下列顺序将影响选主的权重，号码越小权重越高 
[server1]
hostname=10.0.2.25
port=3306

[server2]
hostname=10.0.2.26
port=3306

[server3]
hostname=10.0.2.27
port=3306

# binlogserver配置，要求一台额外的机器，mysql 5.6以上，支持gtid并开启, 其作用是用于同步主库的binlog内容

# 必须叫这个名字binlog1，是MHA定义好的 
[binlog1]
# 永远不会被选主
no_master=1
hostname=10.0.2.27
port=3306
# 需要手动创建这个目录，并目录不能与原目录一致
master_binlog_dir=/data/mysqldb/binlog-server


# 状态检查
## 互信状态检查 
$> masterha_check_ssh --conf=/etc/mha/app1.cnf

## 主从状态检查 
$> masterha_check_repl --conf=/etc/mha/app1.cnf


# 开启MHA --remove_dead_master_conf 自动将故障节点从配置文件中移除 --ignore_last_failover
$> nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

# 查看MHA状态
$> masterha_check_status --conf=/etc/mha/app1.cnf 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

# 1. binlogserver配置，要求一台额外的机器，mysql 5.6以上，支持gtid并开启, 其作用是用于同步主库的binlog内容
$> vim /etc/mha/app1.cnf 
# 必须叫这个名字binlog1，是MHA定义好的 
[binlog1]
# 永远不会被选主
no_master=1
hostname=10.0.2.27
port=3306
# 需要手动创建这个目录，并目录不能与原目录一致
master_binlog_dir=/data/mysqldb/binlog-server


# 2. 配置节点创建对应目录(权限)
$> mkdir -p /data/mysqldb/binlog-server

# 3. 拉取主库的binlog日志,先确认下主库是从多少开始的  show binary logs; 需全部拉取下来
# binlog拉取本身和mha没什么关系，但需要在mha启动是前处理好，否这mha将无法正常启动 
$> cd /data/mysqldb/binlog-server && mysqlbinlog -R --host=10.0.2.26 --user=mha --password=mha --raw --stop-never mysql-bin.000001 &

# 4. 重启mha 
$> masterha_stop --conf=/etc/mha/app1.cnf 
$> nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 & 

# 5. 故障演示 
# 主库宕机，binlogserver自动停止，manager也会自动停止
# 主库宕机，binlogserver日志也是无效了，需要重新同步 
# 解决方案:
# 1. 重新获取新主库的binlog到binlogserver中
# 2. 重新配置配文件的binlog server信息
# 3. 最后在启动mha

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99

$> 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 = '10.0.2.8/24';  # Virtual IP

# 通过ip绑定的ifconfig 无法查看到
my $ssh_start_vip = "/sbin/ip addr add $vip dev eth0";
my $ssh_stop_vip = "/sbin/ip addr del $vip dev eth0";  # 

# my $key = "1";
# my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
# my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

$ssh_user = "root";
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\@cluster1 \" $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";
}