Mysql8.0通过MHA+LVS+Keepalived搭建高可用方案

MySQL集群当主库故障时,通过MHA可以实现快速完成主从切换 ,本文讲解MHA+LVS+Keepalived搭建Mysql集群的方法,以此为基础,能够很轻松的实现横向扩展,比如读写分离、扩展主库、扩展从库。

系统环境

操作系统:CentOS 7.2 x64

 数据库:MySQL 8.0

应用平台:MHAmha4mysql-0.57mha4mysql-0.58、perl、LVS、Keepalived

服务器部署方案及说明

如果需要实现MHA高可用,可以将它部署在两台LVS中,或者再加一台,同样通过keepalived实现高可用;

LVS通过Keepalived实现高可用,其功能是负载多台Mysql从库,实现Mysql读分离,此时从库可以随意横向扩展。

当Mysql主库出现故障后,MHA将MySQL-candicate切换IP并提升为主库,同时将其VIP漂移至其他从库服务器上。

服务器IP 主机名 应用 角色 备注
10.10.10.11 MYSQL-A mysql mha-node master server_id=11
10.10.10.12 MYSQL-B mysql mha-node candicate server_id=12
10.10.10.13 MYSQL-C mysql mha-node slave server_id=13
10.10.10.101 MHA mha-manager mha-node monitor  
10.10.10.201 LVS-A ipvsadm keepalived lvs ha 10.10.10.200
10.10.10.202 LVS-B ipvsadm keepalived

部署数据库服务器

安装数据库

在各数据库节点上安装Mysql8.0,安装方法略。

配置主从复制

数据库配置为1主2从,2个从库中,1个用于主从切换,1个恒从库用于同步relay-log。

初始mysql-master的配置:

[server]
server_id=11
log_bin=master-log
relay-log=relay-bin
skip_name_resolve=ON
innodb_file_per_table=ON
max_connections=65536

所有mysql-slave的配置:

[server]
server_id=1[x]
log-bin=master-bin
relay-log=relay-bin
skip_name_resolve=ON
innodb_file_per_table=ON
max_connections=65536
relay_log_purge=0    #关闭中继日志自动修剪
read_only=1    #将从节点设置为只读

从库定时删除relay-log

mkdir /opt/mha/log
crontab -e

加入以下定时任务:

0 2 * * * /usr/bin/purge_relay_logs --user=root --disable_relay_log_purge >> /opt/mha/log/purge_relay_logs.log 2>&1

从库开启绑定VIP的服务

编辑脚本/etc/init.d/realserver

#./etc/rc.d/init.d/functions
LVS_VIP=10.10.10.200
case "$1" in
start)
ifconfig lo:0 $LVS_VIP netmask 255.255.255.255 broadcast $LVS_VIP
/sbin/route add -host $LVS_VIP dev lo:0
echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
sysctl -p >/dev/null 2>&1
echo "Server Started"
;;
stop)
ifconfig lo:0 down
route del $LVS_VIP >/dev/null 2>&1
echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce
echo "Server Stoped"
;;
*)
echo "Usage: $0 {start|stop}"
exit 1
esac
exit 0

保存并开启服务

chmod +x realserver

service realserver start

部署mha

安装mha4mysql-manager

首先,安装基础环境。

yum -y install epel-release

yum -y install perl perl-devel perl-CPAN perl-Config-Tiny perl-DBD-MySQL perl-ExtUtils-MakeMaker perl-Log-Dispatch.noarch perl-Parallel-ForkManager.noarch

然后,下载MHA4MySQL的软件包至/opt并解压,依次安装node和manager。

cd /opt

wget https://www.andylouse.net/wp-content/uploads/2018/07/mha4mysql.zip

unzip mha4mysql.zip

cd mha4mysql

首选源码安装方式:

1.安装mha4mysql-node

tar -zxvf mha4mysql-node-0.57.tar.gz

cd mha4mysql-node-0.57

perl Makefile.PL

make && make install

2.安装mha4mysql-manager

tar -zxvf mha4mysql-manager-0.57.tar.gz

cd mha4mysql-manager-0.57

perl Makefile.PL

make && make install

或选择rpm安装方式:

rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm

rpm -ivh mha4mysql-manager-0.57-0.el7.noarch.rpm

貌似epel的yum已经包含mha4mysql,但本人懒得试。

配置mha4mysql-manager

将源码包中的默认配置文件复制到/etc/mha,包含masterha_default.cnf和app1.cnf两个配置文件,然后编辑/etc/mha/app1.cnf,在该配置文件中,涉及到的目录或账号请自行创建。

[server default]
manager_workdir=/opt/mha/app1
manager_log=/opt/mha/app1/manager.log
#user=mysql
#password=mysqltellu
#ssh_user=mysql
repl_user=repl
repl_password=repltellu
ping_interval=1
#shutdown_script="/opt/mha/scripts/power_manager"
#master_ip_failover_script="/opt/mha/scripts/master_ip_failover"
#master_ip_online_change_script="/opt/mha/scripts/master_ip_online_change"
#report_script="/opt/mha/scripts/send_report"

[server1]
hostname=10.10.10.11
master_binlog_dir="/data/mysql/master-log"
candidate_master=1

[server2]
hostname=10.10.10.12
master_binlog_dir="/data/mysql/master-log"
candidate_master=1

[server3]
hostname=10.10.10.13
no_master=1

在源码包的samples目录中,除了conf文件,同时还有scripts文件,可以用作参照。

附:在此处下载配置有vip、keepalived的master_ip_failover文件,请根据实际需求修改vip等参数。

操作mha4myql-manager

#启动

masterha_manager --conf=/etc/mha/app1.cnf 2>&1 &

#停止

masterha_stop --conf=/etc/mha/app1.cnf

部署mha4mysql-node

1、各数据库节点安装mha4mysql-node。

rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm

2、MHA管理端与各数据库节点、各数据库节点之间需要实现免密登录。

ssh-keygen -t rsa -P ''

cat .ssh/id_rsa.pub >> .ssh/authorized_keys
chmod go= .ssh/authorized_keys
for i in 7 8 9;do scp -p /root/.ssh/id_rsa /root/.ssh/authorized_keys root@10.10.10.$i:/root/.ssh/ ;done

3、在各数据库上创建用于mha的复制账号

grant all privileges on *.* to 'repl'@'10.10.10.%' identified by 'repltellu';

flush privileges;

4、测试配置结果

masterha_check_ssh --conf=/etc/mha/app1.cnf
masterha_check_repl --conf=/etc/mha/app1.cnf

部署LVS+keepalived

安装ipvsadm+Keepalived

yum -y install ipvsadm keepalived

配置LVS

开启路由转发功能

sed -i 's/ip_forward = 0/ip_forward = 1/p' /etc/sysctl.conf
sysctl -p

配置Keepalived

分离keepalived日志

sed -i 's/KEEPALIVED_OPTIONS="-D"/KEEPALIVED_OPTIONS="-D -d -S 0"/g' /etc/sysconfig/keepalived

编辑keepalived.conf

global_defs {
    router_id LVS
}

#如果配置了mha通过lvs高可用,本人的mha是单机,所以不需要
#vrrp_script check_masterha_manager {
#    script "/opt/scripts/check_mha4mysql_manager.sh"
#    interval 3
#}

vrrp_instance MYSQL_SLAVE {

    state MASTER    #从lvs上把 MASTER 改为 BACKUP
    interface eno16777736
    virtual_router_id 200
    priority 100    #从lvs上把 100 改为 90
    advert_int 1

    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        10.10.10.200
    }

    nopreempt
    #smtp_alter

    #如果配置了mha通过lvs高可用,本人的mha是单机,所以不需要
    #notify_master "/opt/scripts/notify_master.sh"
    #notify_backup "/opt/scripts/notify_stop.sh"
    #notify_stop "/opt/scripts/notify_stop.sh"
}

virtual_server_group MysqlRead { 
    10.10.10.200 3306
} 

virtual_server 10.10.10.200 3306 {
    delay_loop 6
    lb_algo wlc
    lb_kind DR
    nat_mask 255.255.255.0 
    #persistence_timeout 
    protocol TCP

    #如果想加设一台专用故障转移的备机
    #sorry_server 10.10.10.10 3306
    real_server 10.10.10.12 3306 {
        weight 6
        TCP_CHECK {
            connect_timeout 10
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }

    real_server 10.10.10.13 3306{
    weight 0
    TCP_CHECK {
        connect_timeout 10
        nb_get_retry 3
        delay_before_retry 3
        connect_port 3306
        }
    }

}

下载keepalived.conf中涉及到的脚本文件lvs-keepalived-scripts

赞 (0) 打赏

评论 4

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
  1. ch8.0还用MHA,MHA已经是过去式了回复
    • 智林确实如此,Mysql8已经自带集群的,我也只不过以这个版本做个案例!回复
  2. canadianorderpharmacyHi there, There's no doubt that your website could possibly be having browser compatibility problems. Whenever I take a look at your blog in Safari, it looks fine however when opening in IE, it has some overlapping issues. I just wanted to give you a quick heads up! Other than that, excellent blog!回复
    • 智林Thank you very much! I will not to optimize my blog for IE, because it has been given up by Microsoft.回复

感谢您的支持与帮助

支付宝扫一扫打赏

微信扫一扫打赏