MySQL集群读写分离方案A-(MySQL+GTID+ProxySQL+Keepalive)之6/7：Keepalive安装配置(实现:ProxySQL双机高可用 HA方案）
一、ProxySQL双机高可用 HA方案与架构
1、客户需求
需求：对ProxySQL实现双机高可用。
实现：如果一台proxysql出现故障，另一台立刻接管。
原理：使用keepalived的vip功能
2、架构图
二、安装配置keepalived（192.168.21.171/192.168.21.172）
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 /mysql/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.171）
mkdir /etc/keepalived vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id proxysql-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_proxysql {
script "killall -0 proxysql &> /dev/null && exit 0 || exit 1"
interval 1
weight -30
fall 3
rise 3
}
vrrp_instance v_mysql_proxy_wgpt1 {
state BACKUP
#state MASTER
interface ens192
virtual_router_id 200
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass test
}
track_script {
chk_proxysql
}
virtual_ipaddress {
192.168.21.175/24
}
}
（2）第二台配置（192.168.21.172）
mkdir /etc/keepalived vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id proxysql-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_proxysql {
script "killall -0 proxysql &> /dev/null && exit 0 || exit 1"
interval 1
weight -30
fall 3
rise 3
}
vrrp_instance v_mysql_proxy_wgpt1 {
state BACKUP
interface ens192
virtual_router_id 200
priority 90
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass test
}
track_script {
chk_proxysql
}
virtual_ipaddress {
192.168.21.175/24
}
}
7、重启rsyslog和keepalived
systemctl restart rsyslog systemctl start keepalived tail -f /var/log/keepalived.log
五、常见测试
1、测试当前vip在哪个上面
ip ad |grep .175
2、测试读：
mysql -utest -ptest123 -h192.168.21.175 -P6033 -e "select @@hostname;"
3、测试写：
（1）主库创建数据库和表
create database llltest; use llltest; create table test(id int,name char(10)); insert into test values(1,'LLL01'); commit;
（2）插入数据
mysql -utest -ptest123 -h192.168.21.175 -P6033 -A -e "use llltest; insert into llltest.test values (2,'LLL02');commit;show variables like 'server_id'";
4、关闭192.168.21.171的keepalived
systemctl stop keepalived
mysql -utest -ptest123 -h192.168.21.175 -P6033 -e "select @@hostname;" --这个时候测试，同样 OK
systemctl start keepalived
