MySQL高可用方案测试

IT那活儿发布于2023-01-11 13:19 / 3009人阅读

MySQL高可用方案测试

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

一

方案背景

涉及项目：XXXXXX银行项目

涉及功能：自动化运维平台

优化项：mysql数据库高可用架构

描述：为解决mysql数据库故障导致的数据丢失、业务不可用、人工恢复时间长等问题，故设计mysql双主热备高可用架构方案，通过此方案可实现mysql数据库双主实时备份，故障后秒级自动恢复可用，且数据不丢失，实现了数据完整、功能高可用。

二

规划vs目标

2.1.测试环境规划

主机：192.168.31.113、192.168.31.114
操作系统：CentOS7.6
中间件及其版本：mysql5.7、keepalived-2.1.2
操作用户：root、shsnc

2.2 实现目标

通过MySQL高可用方案方案可实现mysql数据库双主实时备份，故障后秒级自动恢复可用，且数据不丢失，实现数据完整、功能高可用的mysql双主架构。

三

实施过程

3.1 建立mysql数据库双主并验证

主机：192.168.31.113、192.168.31.114
操作用户：shsnc

1）192.168.31.113 mysql配置

修改mysql配置文件添加如下配置：

</>复制代码 
vim my.cnf

server-id=113
log-bin=mysql-bin
enforce-gtid-consistency=true
gtid-mode=on

重启mysql。

连接mysql并配置mysql双主：

</>复制代码 
mysql -h192.168.31.113 -uroot -P3306 -pshsnc!@#

stop slave;
change master to master_host=135.10.110.114,master_user=root,master_password=shsnc!@#,master_port=3306,master_auto_position=1;
start slave;

2）192.168.31.114 mysql配置

修改mysql配置文件添加如下配置：

</>复制代码 
vim my.cnf

server-id=114
log-bin=mysql-bin
enforce-gtid-consistency=true
gtid-mode=on

重启Mysql。

连接mysql并配置mysql双主：

</>复制代码 
mysql -h192.168.31.114 -uroot -P3306 -pshsnc!@#

stop slave;
change master to master_host=135.10.110.113,master_user=root,master_password=shsnc!@#,master_port=3306,master_auto_position=1;
start slave;

3）验证mysql双主

配置完成后在分别连接两台mysql，查看双主状态：

</>复制代码 
show slave statusG;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

确认双方mysql的slave状态均为双yes，主主搭建成功。

3.2 搭建keepalived（两台）

主机：192.168.31.113、192.168.31.114
操作用户：root

--安装keepalived：

安装编译依赖包

</>复制代码 
yum install -y libnl;
yum install -y libnfnetlink-devel zlib zlib-devel gcc gcc-c++ openssl openssl-devel openssh;

yum install -y bc;

解压安装包

</>复制代码 
tar zxvf keepalived-2.1.2.tar.gz

编译安装

</>复制代码 
cd keepalived-2.1.2

mkdir /home/shsnc/keepalived

./configure --prefix=/home/shsnc/keepalived

make

makeinstall

复制配置文件
复制keepalived配置文件目录到etc下，用于systemctl的keepalived服务启动。

</>复制代码 
cp -r /home/shsnc/keepalived/etc/keepalived /etc/keepalived

启动keepalived服务
systemctl启动keepalived服务

</>复制代码 
systemctl start keepalived

3.3 编写keepalived脚本

1）编写check脚本

作用：检查mysql是否可用，不可用则停止本机keepalived，使虚拟ip转移至可用mysql。

vim /etc/keepalived/script/check_mysql.sh

</>复制代码 
#!/bin/bash

##检测mysql实例端口是否通
RETVAL=$?

# failover
check_port=$(ss -nlpt | grep "mysqld" | awk -F[: ]+ /3306/{print $6})
if [ ! -n "${check_port}" ]
then
    /bin/systemctl stop keepalived
else
    #exit $RETVAL
    echo  mysql is alive
fi

cpuidle=$(vmstat 1 3 |tail -1 |awk $0~/[[:digit:]]+/{print $(NF-2)})
cpu_used=$(awk -v x=$cpuidle BEGIN{printf "%.2f
",100-x})
failpoint=93.0
if [ $(echo "${cpu_used} >= $failpoint" | bc ) -eq 1 ]
then
    /bin/systemctl stop keepalived
    
else
    echo "CPU utilization does not exceed 93"
    exit $RETVAL
fi

exit 0

2）编写notify脚本

作用：设置切换后的mysql主库可读写，从库只读，确保数据写入的唯一入口。

vim /etc/keepalived/script/keepalived_notify.sh

</>复制代码 
#!/bin/bash


. ~/.bash_profile


#mysql user
DB_USER="root"

#mysql user password
DB_PASSWORD=!QAZ3wsx@gzyd

MYSQL_SOCK=""

#mysql_bin
MYSQL_BIN="/data/mysql/mysql_5737/bin/mysql"

#mysql client command
MYSQL_CMD="${MYSQL_BIN} -u${DB_USER} -p${DB_PASSWORD}"

#query the killed seesions id sql
MYSQL_SQL="select concat(kill ,id,;) from information_schema.processlist where user not in (system user,repl,replic,backup,bkpuser,bomcjk,root,myrobot)"


#define function: get mysql service information
function get_mysql_infor() 
{
    for sock in `ps -ef | grep mysqld | grep --socket= | awk -F--socket= {print $2} | awk {print $1}`
    do
        MYSQL_SOCK="${MYSQL_SOCK}$(echo $sock)"
    done
}


#define function: mysql kill sessions
function kill_sessions()
{
    #receive a mysql socket file parameter
    #my_sock=$(get_mysql_infor)
    unset MYSQL_SOCK
    get_mysql_infor
    ln -sv ${MYSQL_SOCK} /tmp/mysql.sock
    ${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "${MYSQL_SQL}" 2>/dev/null | ${MYSQL_CMD} > /dev/null 2>&1
}


#define function: set mysql read_only mode
function set_readonly()
{
    #receive mysql socket file && read_only sign parameter
    #my_sock=$(get_mysql_infor)
    unset MYSQL_SOCK
    get_mysql_infor
    my_sign=$1
    #begin to set mysql read_only mode
    #${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "set global read_only=${my_sign}" 2>/dev/null
    ${MYSQL_CMD} -S ${MYSQL_SOCK} -Ne "SET GLOBAL sync_binlog=1;SET GLOBAL innodb_flush_log_at_trx_commit=1;SET GLOBAL read_only=${my_sign};SET GLOBAL super_read_only=${my_sign};" 2>/dev/null
}


#define function: keepalived state changed to master
function Keepalived_changed_to_master()
{
    #my_sock=$(get_mysql_infor)
    unset MYSQL_SOCK
    get_mysql_infor
    Seconds_Behind_Master=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Seconds_Behind_Master | awk -F": " {print $2})
    Slave_IO_Running=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Slave_IO_Running | awk -F": " {print $2})
    Slave_SQL_Running=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Slave_SQL_Running | awk -F": " {print $2})
    Master_Log_File=$(${MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Master_Log_File | awk -F": " {print $2})
    Relay_Master_Log_File=$({MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Relay_Master_Log_File | awk -F": " {print $2})
    Read_Master_Log_Pos=$({MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Read_Master_Log_Pos | awk -F": " {print $2})
    Exec_Master_Log_Pos=$({MYSQL_CMD} -S ${MYSQL_SOCK} -e "show slave statusG" | grep -w Exec_Master_Log_Pos | awk -F": " {print $2})
    echo "正常双YES的情况下切换"
    if [ "${Slave_IO_Running}" = "Yes" -a "${Slave_SQL_Running}" = "Yes" ]
    then
        if [ $Seconds_Behind_Master -eq 0 ]
        then
            set_readonly 0
            exit 0
        fi
    fi
        #if [ ${Slave_IO_Running} = Connecting -a ${Slave_SQL_Running} = Yes -a ${Seconds_Behind_Master} == NULL ];
    if [ "${Slave_IO_Running}" = "Connecting" -a "${Slave_SQL_Running}" = "Yes" ]
    then
        if [ "${Master_Log_File}" = "${Relay_Master_Log_File}" -a "${Read_Master_Log_Pos}" = "${Exec_Master_Log_Pos}" ]
        then
            set_readonly 0
            exit 0
                fi
    fi

}

#define function: keepalived state changed to backup
function Keepalived_changed_to_backup()
{
    #set mysql read_only mode
        set_readonly 1
        #kill mysql sessions
        kill_sessions
}

#start this shell
case $1 in
    master)
            Keepalived_changed_to_master
        ;;
         
        backup)
            Keepalived_changed_to_backup
            ;;
        *)
            ;;
esac
exit 0

3.4 配置两台keepalived

1）配置192.168.31.113的keepalived

vim /etc/keepalived/keepalived.conf

</>复制代码 
! Configuration File for keepalived
global_defs {
    notification_email {
       shsnc@shsnc.com
   }
    notification_email_from smtp.163.com
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id mysql-ha
}
vrrp_script check_mysql {
    script "/etc/keepalived/script/check_mysql.sh"
    interval 5
    fall 3
    rise 2
    #timeout 60
}
vrrp_instance VI_3306 {
    state BACKUP
    interface ens192
    virtual_router_id 188
    priority 100
    nopreempt
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 110120
    }
    virtual_ipaddress {
        114.168.1.188/24 dev ens192
    }
    track_script {
        check_mysql
    }
    notify_master "/etc/keepalived/script/keepalived_notify.sh master"
    notify_backup "/etc/keepalived/script/keepalived_notify.sh backup"
}

2）配置192.168.31.114的keepalived

vim /etc/keepalived/keepalived.conf

</>复制代码 
! Configuration File for keepalived
global_defs {
    notification_email {
       shsnc@shsnc.com
   }
    notification_email_from smtp.163.com
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id mysql-ha
}
vrrp_script check_mysql {
    script "/etc/keepalived/script/check_mysql.sh"
    interval 5
    fall 3
    rise 2
    #timeout 60
}
vrrp_instance VI_3306 {
    state BACKUP
    interface ens192
    virtual_router_id 188
    priority 90
    #nopreempt
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 110120
    }
    virtual_ipaddress {
        114.168.1.188/24 dev ens192
    }
    track_script {
        check_mysql
    }
    notify_master "/etc/keepalived/script/keepalived_notify.sh master"
notify_backup "/etc/keepalived/script/keepalived_notify.sh backup"
}

重启192.168.31.113、192.168.31.114的keepalived服务。

</>复制代码 
systemctl stop keepalived;

systemctl start keepalived;

四

测试

4.1 mysql故障自动切换测试

1）测试方法

模拟mysql故障，查看vip是否自动切换至可用mysql，查看mysql读写权限是否自动切换。模拟故障数据库修复后启动，查看mysql双主同步是否正常，数据是否同步。

2）测试过程

停用192.168.31.114的mysql服务，此时vip已漂移至192.168.31.113（说明keepalived的check脚本执行成功）。

查看mysql读写权限，此时192.168.31.113的mysql数据库为可读可写，192.168.31.114的mysql数据库为只读（说明keepalived的notify脚本执行成功）。

通过虚拟ip连接数据库成功，说明自动切换可用mysql成功。

故障自动切换恢复后，再次启动192.168.31.114的mysql数据库，可以看到两台数据库数据一致，数据已经同步，可继续提供高可用的mysql双主架构。

4.2 Keepalived脑裂测试

测试方法：keepalived配置不同VRRP组播通讯时间进行测试，通过禁止192.168.31.114上iptables的vrrp协议访问，触发keepalived脑裂，再分别查看脑裂触发时间。

1）测试样例一：配置VRRP组播间隔通讯时间为1秒。

配置策略
配置组播时间为1秒

执行命令
iptables禁止vrrp协议访问并记录时间。

查看日志
查看keepalived日志，得到脑裂触发时间。

测试结论
当advert_int配置为1的时候，脑裂触发时间2~3s。

2）测试样例二：配置VRRP组播间隔通讯时间为3秒时。

配置策略
配置组播时间为3秒

执行命令
iptables禁止vrrp协议访问并记录时间。

查看日志
查看keepalived日志，得到脑裂触发时间。

测试结论
当advert_int配置为3的时候，脑裂触发时间2~3s。

3）测试样例三：配置VRRP组播间隔通讯时间为5秒时。

配置策略
配置组播时间5秒。

执行命令
iptables禁止vrrp协议访问并记录时间。

查看日志
查看keepalived日志，得到脑裂触发时间。

测试结论
当advert_int配置为5的时候，脑裂触发时间10~12s。

4）测试样例四：配置vrrp_garp_master_refresh为10秒，VRRP组播间隔通讯时间为1秒。

配置策略
配置组播时间1秒，配置vrrp_garp_master_refresh为10秒。

执行命令
iptables禁止vrrp协议访问并记录时间。

查看日志分：

测试结论
当vrrp_garp_master_refresh设置为10s的时候，脑裂恢复之后主节点每间隔10S发一次包到网关。

最终测试结果

通过以上测试验证，Keepalived + MySQL双主热备方案可实现mysql数据库双主实时备份，m故障后秒级自动恢复可用，且数据不丢失，提供了数据完整、功能高可用的故障处理能力，方案可行。

本文作者：张帅(上海新炬中北团队）

本文来源：“IT那活儿”公众号

GPU云服务器云服务器高可用负载均衡方案 sqlserver 高可用方案高可用mysql mysql高可用

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129112.html

MySQL - 高可用性：少宕机即高可用？

摘要：归根到底，高可用性就意味着更少的宕机时间。首先，可以尽量避免应用宕机来减少宕机时间。降低平均失效时间我们对系统变更缺少管理是所有导致宕机事件中最普遍的原因。我们之前了解了复制、扩展性，接下来就让我们来了解可用性。归根到底，高可用性就意味着更少的宕机时间。老规矩，讨论一个名词，首先要给它下个定义，那么什么是可用性？ 1 什么是可用性我们常见的可用性通常以百分比表示，这本身就有其隐...

JessYanCoding 2019-07-01 12:43 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师

我要关注我要私信

TA的文章

消息中间件故障分析一例

阅读 1480·2023-01-11 13:20
RAC双节点crash回复一例

阅读 1832·2023-01-11 13:20
ORA-600处理一例

阅读 1281·2023-01-11 13:20
双节点RAC实例2 HANG 故障分析一例

阅读 2024·2023-01-11 13:20
RAC集群节点1重启分析一例

阅读 4237·2023-01-11 13:20
CRS启动报错CRS-1656处理分享

阅读 2907·2023-01-11 13:20
oracle 12CR2打补丁报错处理一例

阅读 1497·2023-01-11 13:20
分布式缓存组件故障分析及监控优化

阅读 3831·2023-01-11 13:20

资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

MySQL高可用方案测试

3.1 建立mysql数据库双主并验证

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

3.2 搭建keepalived（两台）

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

3.3 编写keepalived脚本

</>复制代码

</>复制代码

3.4 配置两台keepalived

</>复制代码

</>复制代码

</>复制代码

本文作者：张帅(上海新炬中北团队）

本文来源：“IT那活儿”公众号

相关文章

MySQL - 高可用性：少宕机即高可用？

发表评论

0条评论

IT那活儿

男|高级讲师

TA的文章

消息中间件故障分析一例

RAC双节点crash回复一例

ORA-600处理一例

双节点RAC实例2 HANG 故障分析一例

RAC集群节点1重启分析一例

CRS启动报错CRS-1656处理分享

oracle 12CR2打补丁报错处理一例

分布式缓存组件故障分析及监控优化

最新活动

资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

MySQL高可用方案测试

3.1 建立mysql数据库双主并验证

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

3.2 搭建keepalived（两台）

</>复制代码

</>复制代码

</>复制代码

</>复制代码

</>复制代码

3.3 编写keepalived脚本

</>复制代码

</>复制代码

3.4 配置两台keepalived

</>复制代码

</>复制代码

</>复制代码

本文作者：张 帅(上海新炬中北团队）

本文来源：“IT那活儿”公众号

相关文章

发表评论

0条评论

男|高级讲师

TA的文章

最新活动

本文作者：张帅(上海新炬中北团队）