ECS自建MySQL搭建MHA

背景

云上不能直接通过命令创建VIP
需要通过调用API配置辅助IP实现VIP飘逸功能

环境

MySQL一主两从，异步复制

MySQL1（主库）：10.172.58.137

MySQL2（备主）：10.172.58.146

MySQL3（从库）：10.172.58.209

MHA管理节点：10.172.58.146

MHA管理节点安装python环境

yum install python3.x86_64
pip3 install aliyun-python-sdk-core
pip3 install aliyun-python-sdk-ecs
pip3 install paramiko
pip3 install

MySQL搭建

创建过程忽略
三台机器账号授权 create user test identified by 'test';grant all on *.* to test;

主库ECS开启VIP

#!/usr/bin/env python
#coding=utf-8

from aliyunsdkcore.client import AcsClient
from aliyunsdkcore.acs_exception.exceptions import ClientException
from aliyunsdkcore.acs_exception.exceptions import ServerException
from aliyunsdkecs.request.v20140526.AssignPrivateIpAddressesRequest import AssignPrivateIpAddressesRequest
from aliyunsdkecs.request.v20140526.UnassignPrivateIpAddressesRequest import UnassignPrivateIpAddressesRequest

import os
import paramiko
import time

client = AcsClient('xxx', 'xxx', 'cn-hangzhou')


def add_vip(client):
    request = AssignPrivateIpAddressesRequest()
    request.set_accept_format('json')

    request.set_NetworkInterfaceId("eni-bp15n0xwiadgvv25vf38")
    request.set_PrivateIpAddresss(["10.172.58.240"])

    client.do_action_with_exception(request)


def add_config_file():
    host = "10.172.58.137"
    user = "root"

    echo_cmd = "echo -e 'DEVICE=eth0:0
TYPE=Ethernet
BOOTPROTO=static
ONBOOT=yes
IPADDR=10.172.58.240
NETMASK=255.255.255.0
GATEWAY=10.172.255.253' > /etc/sysconfig/network-scripts/ifcfg-eth0:0"
    restart_cmd = "ip addr add 10.172.58.240/24 dev eth0 label eth0:0"
    cmd = echo_cmd + ";" + restart_cmd

    ssh = paramiko.SSHClient()
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    ssh.connect(hostname=host, username=user)

    stdin, stdout, stderr = ssh.exec_command(cmd)
    out = stdout.readlines()
    err = stderr.readlines()

    ssh.close()
    return out, err


if __name__ == '__main__':
    add_vip(client)

    add_result = add_config_file()
    if len(add_result[1]) == 0:
        print('vip设置成功')

View Code

MHA搭建

打通SSH

三台机器分别执行

ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.172.58.146
ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.172.58.137
ssh-copy-id -i /root/.ssh/id_rsa.pub root@10.172.58.209

安装MHA

所以机器安装node包 yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y
管理节点安装manager包 yum install mha4mysql-manager-0.56-0.el6.noarch.rpm

配置文件

mkdir apps logs scripts workdir

[root@test-miria02 mha]# cat apps/mha.cnf 
[server default]
# mysql user and password
user=test
password=test
ssh_user=root
# working directory on the manager
manager_workdir=/data/mha/workdir
# working directory on MySQL servers
remote_workdir=/data/mha/workdir
repl_user=test
repl_password=test
master_ip_failover_script=/data/mha/scripts/master_ip_failover.pl

[server1]
hostname=10.172.58.137
port=9999
master_binlog_dir=/data/my9999/binlog/
candidate_master=1
check_repl_delay=0
[server2]
hostname=10.172.58.146
port=9999
master_binlog_dir=/data/my9999/binlog/
candidate_master=1
check_repl_delay=0
[server3]
hostname=10.172.58.209
port=9999
master_binlog_dir=/data/my9998/binlog/
ignore_fail=1
no_master=1

View Code

[root@test-miria02 scripts]# cat master_ip_failover.pl
#!/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, %network_list, $orig_master_network_card, @host_list, $new_host, $new_network_card
);

my $vip = '10.172.58.240/24';

GetOptions(
    'command=s'          => $command,
    'ssh_user=s'         => $ssh_user,
    'orig_master_host=s' => $orig_master_host,
    'new_master_ip=s'    => $new_master_ip,
    'new_master_port=i'  => $new_master_port,
    'orig_master_port=i'  => $orig_master_port,
    'new_master_host=s'  => $new_master_host,
    'orig_master_ip=s'  => $orig_master_ip,
);

exit &main();

sub main {

    print "旧主ip：$orig_master_ip
";
    %network_list = ('10.172.58.137'=>'eni-bp15n0xwiadgvv25vf38', '10.172.58.146'=>'eni-bp1b6zjhne81tkiad8mg');
    $orig_master_network_card = $network_list{$orig_master_ip};
    print "旧主网卡：$orig_master_network_card





";

    delete $network_list{$orig_master_ip};
    @host_list = keys %network_list;
    $new_host = $host_list[0];
    $new_network_card = $network_list{$new_host};
    print "新主ip：$new_host
";
    print "新主网卡：$new_network_card
";


    if ( $command eq "stop" || $command eq "stopssh" ) {

        my $exit_code = 1;
        eval {
            print "Disabling the VIP on old master: $orig_master_host 
";
            &stop_vip();
            $exit_code = 0;
        };
        if ($@) {
            warn "Got Error: $@
";
            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 
";
            &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 
";
        exit 0;
    }
    else {
        &usage();
        exit 1;
    }
}

sub start_vip() {
    print "新主开启VIP
";
    print "$new_host
";
    print "$new_network_card
";
    system("python3 /data/mha/scripts/startvip.py $new_host $new_network_card $new_master_port $orig_master_ip $orig_master_port");
}
sub stop_vip() {
     return 0  unless  ($ssh_user);
    system("python3 /data/mha/scripts/stopvip.py $orig_master_ip $orig_master_network_card");
}

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

View Code

[root@test-miria02 scripts]# cat startvip.py
#!/usr/bin/env python
#coding=utf-8

from aliyunsdkcore.client import AcsClient
from aliyunsdkcore.acs_exception.exceptions import ClientException
from aliyunsdkcore.acs_exception.exceptions import ServerException
from aliyunsdkecs.request.v20140526.AssignPrivateIpAddressesRequest import AssignPrivateIpAddressesRequest

import os
import paramiko
import time
import sys
import requests
import json


client = AcsClient('xxx', 'xxx', 'cn-hangzhou')


def add_vip(client, network_card):
    request = AssignPrivateIpAddressesRequest()
    request.set_accept_format('json')

    request.set_NetworkInterfaceId(network_card)
    request.set_PrivateIpAddresss(["10.172.58.240"])

    client.do_action_with_exception(request)
    print("绑定辅助ip")


def add_config_file(host):
#    host = "10.172.58.146"
    user = "root"

    echo_cmd = "echo -e 'DEVICE=eth0:0
TYPE=Ethernet
BOOTPROTO=static
ONBOOT=yes
IPADDR=10.172.58.240
NETMASK=255.255.255.0
GATEWAY=10.172.255.253' > /etc/sysconfig/network-scripts/ifcfg-eth0:0"
    vipadd_cmd = "ip addr add 10.172.58.240/24 dev eth0 label eth0:0"
    cmd = echo_cmd + ";" + vipadd_cmd

    ssh = paramiko.SSHClient()
    ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
    ssh.connect(hostname=host, username=user)

    stdin, stdout, stderr = ssh.exec_command(cmd)
    out = stdout.readlines()
    err = stderr.readlines()

    ssh.close()
    return out, err

def dingTalk(host, port, old_host, old_port):
    messages = "测试环境主从切换告警 
MySQL Master failover %s:%s to %s:%s succeeded 
" % (old_host, old_port, host, port)
    urlweb = "https://oapi.dingtalk.com/robot/send?access_token=e7787fe6ffe61c610d9ef83bc5317dc54feeee5c9e2633f44d94301d82de8492"
    headers={
        "Content-Type": "application/json"
            }
    data={
    "msgtype": "text",
    "text": {
        "content": messages
        },
    "at": {
        "atMobiles": [
            "15958043362"
        ],
        "isAtAll": 0
        }
        }
    json_data=json.dumps(data)
    requests.post(url=urlweb,data=json_data,headers=headers)    


if __name__ == '__main__':
    print("休眠2秒")
    time.sleep(2)
    host = sys.argv[1]
    network_card = sys.argv[2]
    port = sys.argv[3]
    old_host = sys.argv[4]
    old_port= sys.argv[5]


    add_vip(client, network_card)

    add_result = add_config_file(host)
    if len(add_result[1]) == 0:
        print('vip设置成功')

    now = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    print(now)

    dingTalk(host, port, old_host, old_port)

View Code