Mysql主主高可用方案
master配置
[root@master ~]# yum -y install keepalived
[root@master ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id LVS_****MASTER-A
}
vrry_script mysql {
script "/opt/mysql.sh"
interval 2
weight -5
fall 2
rise 1
}
vrrp_instance VI_1 {
state MASTER
interface ens33
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
mysql
}
virtual_ipaddress {
192.168.200.210
}
}
}
[root@master ~]# vim /opt/mysql.sh
#!/bin/bash
counter=$(netstat -na |grep "LISTEN" |grep "3306" |wc -l)
if [ "${counter}" -eq 0 ]
then
systemctl stop keepalived
fi
~
[root@master ~]# chmod +x /opt/mysql.sh
[root@master ~]# systemctl start keepalived
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
inet 192.168.30.25/24 brd 192.168.30.255 scope global noprefixroute ens33
inet 192.168.200.210/32 scope global ens33
[root@master ~]# tail -f /var/log/messages
Apr 9 15:36:43 master ntpd_intres[3721]: host name not found: 0.centos.pool.ntp.org
Apr 9 15:36:43 master ntpd_intres[3721]: host name not found: 1.centos.pool.ntp.org
Apr 9 15:36:43 master ntpd_intres[3721]: host name not found: 2.centos.pool.ntp.org
Apr 9 15:36:43 master ntpd_intres[3721]: host name not found: 3.centos.pool.ntp.org
Apr 9 15:36:45 master Keepalived_vrrp[9706]: Sending gratuitous ARP on ens33 for 192.168.200.210
Apr 9 15:36:45 master Keepalived_vrrp[9706]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on ens33 for 192.168.200.210
Apr 9 15:36:45 master Keepalived_vrrp[9706]: Sending gratuitous ARP on ens33 for 192.168.200.210
Apr 9 15:36:45 master Keepalived_vrrp[9706]: Sending gratuitous ARP on ens33 for 192.168.200.210
Apr 9 15:36:45 master Keepalived_vrrp[9706]: Sending gratuitous ARP on ens33 for 192.168.200.210
Apr 9 15:36:45 master Keepalived_vrrp[9706]: Sending gratuitous ARP on ens33 for 192.168.200.210
Apr 9 15:40:01 master systemd: Started Session 40 of user root.
Apr 9 15:40:01 master systemd: Starting Session 40 of user root.
第二台master2从
[root@master1 ~]# yum -y install keepalived
[root@master1 ~]# vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
router_id LVS_****MASTER-B
}
vrry_script mysql {
script "/opt/mysql.sh"
interval 2
weight -5
fall 2
rise 1
}
vrrp_instance VI_1 {
state BACKUP
interface ens33
virtual_router_id 51
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
mysql
}
virtual_ipaddress {
192.168.200.210
}
}
}
测试VIP转移
[root@master1 ~]# vim /opt/mysql.sh
#!/bin/bash
counter=$(netstat -na |grep "LISTEN"|grep "3306" |wc -l)
if [ "${counter}" -eq 0 ]
then
systemctl stop keepalived
fi
[root@master1 ~]# chmod +x /opt/mysql.sh
[root@master1 ~]# systemctl start keepalived
[root@master1 ~]# tail -f /var/log/messages
[root@master ~]# systemctl stop mariadb
[root@master ~]# ip a |grep ens33
[root@localhost ~]# ip a |grep ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
inet 192.168.30.25/24 brd 192.168.30.255 scope global noprefixroute ens33
2,在远程客户端 测试
Mysql 服务器授权
[root@localhost ~]# mysql -uroot
MariaDB [(none)]> grant all on *.* to 'root'@'192.168.30.%'identified by '123456';
MariaDB [(none)]> flush privileges;
通过vip 登陆测试
[root@localhost ~]# mysql -uroot -p123456 -h 192.168.30.210;
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sampdb |
| test |
| test01 |
| test02 |
+--------------------+