MySQL集群读写分离方案B-(MySQL+GTID+MaxScale+Keepalive)之3/3：Keepalived安装配置(实现:MaxScale双机高可用 HA方案）

2024年1月15日 1260点热度 0人点赞 0条评论

一、MaxScale双机高可用 HA方案与架构

1、客户需求

需求：对MaxScale实现双机高可用。

实现：如果一台proxysql出现故障，另一台立刻接管。

原理：使用keepalived的vip功能。

2、架构图

二、安装配置keepalived（VIP-192.168.21.168）

1、配置linux数据包转发

出于安全考虑，Linux系统默认是禁止数据包转发的。所谓转发即当主机拥有多于一块的网卡时，其中一块收到数据包，根据数据包的目的ip地址将数据包发往本机另一块网卡，该网卡根据路由表继续发送数据包。这通常是路由器所要实现的功能。

要让Linux系统具有路由转发功能，需要配置一个Linux的内核参数net.ipv4.ip_forward。这个参数指定了Linux系统当前对路由转发功能的支持情况；其值为0时表示禁止进行IP转发；如果是1,则说明IP转发功能已经打开。

echo "net.ipv4.ip_nonlocal_bind=1" >> /etc/sysctl.conf
echo "net.ipv4.ip_forward=1" >> /etc/sysctl.conf 
sysctl -p

2、配置 iptables，添加 VRRP通讯支持

（如果开启了防火墙需要配置，如果未开则无需理会）

-A INPUT -d 224.0.0.18 -j ACCEPT
-A INPUT -s 192.168.1.0/24 -d 224.0.0.18 -j ACCEPT #允许组播地址通信
-A INPUT -s 192.168.1.0/24 -p vrrp -j ACCEPT #允许 VRRP（虚拟路由器冗余协）通信
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT #开放 mysql的 3306端口

3、开启路由转发功能（临时生效）

echo "1" >/proc/sys/net/ipv4/ip_forward

4、安装keepalived

（1）安装依赖

mount /dev/cdrom /mnt
yum install kernel-devel openssl-devel popt-devel gcc* -y

（2）安装keepalived

mkdir -p /soft
cd /soft/
wget https://download.topunix.com/keepalived/keepalived-1.4.4.tar.gz
tar zxvf keepalived-1.4.4.tar.gz
cd keepalived-1.4.4
./configure --prefix=/usr/local/keepalived/
make && make install
systemctl enable keepalived

5、配置keepalived日志参数

（1）配置日志

vi /usr/local/keepalived/etc/sysconfig/keepalived

# Options for keepalived. See `keepalived --help' output and keepalived(8) and
# keepalived.conf(5) man pages for a list of all options. Here are the most...
# --log-facility -S 0-7 Set local syslog facility (default=LOG_DAEMON)
#
KEEPALIVED_OPTIONS="-D -d -S 0"

（2）配置系统日志

vi /etc/rsyslog.conf

local0.*  /var/log/keepalived.log

systemctl restart rsyslog

6、配置keepalived.conf

（1）第一台配置（192.168.21.166）

mkdir /etc/keepalived
vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived
global_defs {
  router_id mysql-router
  notification_email {
    284833194@qq.com
  }
  notification_email_from 284833194@qq.com
  smtp_server stmp.qq.com
  smtp_connect_timeout 30
}
vrrp_script chk_myscript {
  script "/mysql/app/maxscale/maxscale_running.sh"
  interval 2
  fall 2
  rise 2
}
vrrp_instance v_mysql_maxscale_wgpt1 {
  state MASTER
  interface ens192
  virtual_router_id 201   #注意：取值：1-255，同一网段，此值在一个集群中是一致的，不同集群不能重复，否则报错:bogus VRRP packet received on ens192 !!!
  priority 100
  advert_int 1
  nopreempt
  authentication {
    auth_type PASS
    auth_pass test
  }
 virtual_ipaddress { 
  192.168.21.168/24 
 }
  track_script {
    chk_myscript
  }
  notify /mysql/app/maxscale/notify_maxscale.sh

}

（2）第二台配置（192.168.21.167）

mkdir /etc/keepalived
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
  router_id mysql-router
  notification_email {
    284833194@qq.com
  }
  notification_email_from 284833194@qq.com
  smtp_server stmp.qq.com
  smtp_connect_timeout 30
}
vrrp_script chk_myscript {
script "/mysql/app/maxscale/maxscale_running.sh"
interval 2
fall 2
rise 2
}
vrrp_instance v_mysql_maxscale_wgpt1{
  state BACKUP
  interface ens192
  virtual_router_id 201   #注意：取值：1-255，同一网段，此值在一个集群中是一致的，不同集群不能重复，否则报错:bogus VRRP packet received on ens192 !!! 
  priority 90
  advert_int 1
  nopreempt
  authentication {
    auth_type PASS
    auth_pass test
  }
 virtual_ipaddress { 
   192.168.21.168/24 
 }
  track_script {
     chk_myscript
  }
  notify /mysql/app/maxscale/notify_maxscale.sh
}

7、监控脚本配置

（1）配置maxscale_running.sh

vi /mysql/app/maxscale/maxscale_running.sh

#!/bin/bash
fileName="/mysql/app/maxscale/logs/maxadmin_output.txt"
rm $fileName
#timeout 2s maxadmin -S /mysql/app/maxscale/tmp/maxadmin.sock list servers > $fileName #老版本用这个命令
timeout 2s maxctrl list servers > $fileName    #新版本用这个命令
to_result=$?

if [ $to_result -ge 1 ]
then
  echo Timed out or error, timeout returned $to_result
  exit 3
else
  echo MaxAdmin success, rval is $to_result
  echo Checking maxadmin output sanity
  grep1=$(grep server1 $fileName)
  grep2=$(grep server2 $fileName)
  #grep3=$(grep server3 $fileName)  #第3台maxscale

  if [ "$grep1" ] && [ "$grep2" ]   #第3台maxscale&& [ "$grep3" ]
  then
    echo All is fine
    exit 0
  else
    echo Something is wrong
    exit 3
  fi
fi

（2）关于maxscale使用keepalived的文档

MaxScale active/passive-setting：
https://mariadb.com/resources/blog/maxscale-ha-setup-using-keepalived-and-maxctrl

（3）配置notify_maxscale.sh

vi /mysql/app/maxscale/notify_maxscale.sh

#!/bin/bash
TYPE=$1
NAME=$2
STATE=$3
OUTFILE=/mysql/app/maxscale/logs/state.txt

case $STATE in
  "MASTER") echo "Setting this MaxScale node to active mode" > $OUTFILE
            maxctrl alter maxscale passive false
            exit 0
            ;;
  "BACKUP") echo "Setting this MaxScale node to passive mode" > $OUTFILE
            maxctrl alter maxscale passive true
            exit 0
            ;;
  "FAULT") echo "MaxScale failed the status check." > $OUTFILE
           maxctrl alter maxscale passive true
           exit 0
           ;;
  *)       echo "Unknown state" > $OUTFILE
           exit 1
           ;;
esac

（4）授权较别扭

chmod 755 /mysql/app/maxscale/*.sh

8、重启keepalived

（1）两台机器重启keepalived服务

systemctl start keepalived

（2）查看keepalived

ps -ef|grep keepalived

三、测试

1、查询测试

mysql -umaxscale -pmaxscale123 -h192.168.21.168 -P3306 -e 'select @@hostname';
select @@hostname;

mysql -umaxscale -pmaxscale123 -h192.168.21.168 -P3306 -e 'select @@hostname';

2、插入数据测试

mysql -uroot -proot
create user test@'%' identified by '123'
grant all on testdb2.* to test@'%';
mysql -utest -p123 -h192.168.21.168 -P3306 -e "insert into testdb.test2 values (6,'LLL216');commit;"
mysql -utest -p123 -h192.168.21.168 -P3306 -e "insert into testdb.test2 values (7,'LLL217');commit;"
mysql -utest -p123 -h192.168.21.168 -P3306 -e "insert into testdb.test2 values (6,'LLL216');commit;"
mysql -utest -p123 -h192.168.21.168 -P3306 -e "insert into testdb.test2 values (7,'LLL217');commit;"
mysql -utest -p123 -h192.168.21.168 -P3306 -e "insert into testdb.test2 values (8,'LLL218');commit;"
select * from testdb.test2;

本作品采用知识共享署名 4.0 国际许可协议进行许可