MGR多主，此时这三台机器，均可读，可写 此时用keepalived 做写的切换，haproxy做读的负载

MGR多主，此时这三台机器，均可读，可写

此时用keepalived 做写的切换，haproxy做读的负载

 

 

1. HAProxy 的配置文件(两台主机)如下

vi /usr/local/haproxy/haproxy.cfg

图解：这一部分清除掉

把下面的内容加上去

frontend mysql_mgr_deen1_front_write

        bind *:3306

        mode tcp

        default_backend mysql_mgr_deen1_back_write

frontend mysql_mgr_deen1_front_read

        bind *:3307

        mode tcp

        default_backend mysql_mgr_deen1_back_read

backend mysql_mgr_deen1_back_write

        mode tcp

        balance leastconn

        option mysql-check user haproxy_check

        server mysql51 192.168.0.51:3306 weight 1 check inter 2000 rise 2 fall 5 on-marked-up shutdown-backup-sessions

        server mysql52 192.168.0.52:3306 weight 1 check inter 2000 rise 2 fall 5 backup

backend mysql_mgr_deen1_back_read

        mode tcp

        balance leastconn

        option mysql-check user haproxy_check

        server mysql52 192.168.0.52:3306 weight 1 check inter 2000 rise 2 fall 5 maxconn 300

        server mysql53 192.168.0.53:3306 weight 1 check inter 2000 rise 2 fall 5 maxconn 300

1. 主库添加 haproxy_check 用户

drop user haproxy_check@'%';

create user haproxy_check@'192.168.0.65';

create user haproxy_check@'192.168.0.61';

create user haproxy_check@'192.168.0.62';

grant usage on *.* to haproxy_check@'192.168.0.61';

grant usage on *.* to haproxy_check@'192.168.0.62';

grant usage on *.* to haproxy_check@'192.168.0.65';

1. 重启，检查

/etc/rc.d/init.d/haproxy stop

/etc/rc.d/init.d/haproxy start

http://192.168.0.65:8888/haproxy-stat

mysql -uroot -proot -h192.168.0.65 -P3306 -e "select @@hostname;"

mysql -uroot -proot -h192.168.0.65 -P3307 -e "select @@hostname;"

http://192.168.0.65:8888/haproxy-stat

 

模拟宕机01

停掉 mysql    ./mysql.stop

切换到02，此时02提供读写

./mysql.start 后

写回到01，(本省回到MGR 01的主是MGR的机制，会切回到原来的群主) 脚本里也做了01不行了，由02接管

 

再模拟 第一台 负载出问题了

systemctl stop keepalives

/etc/rc.d/init.d/haproxy stop

 

ip .65切换到62   ，下面的两台命令也是正常的

mysql -uroot -proot -h192.168.0.65 -P3306 -e "select @@hostname;"

mysql -uroot -proot -h192.168.0.65 -P3307 -e "select @@hostname;"

1. 调整  ，实现负载备机资源利用，两台负载实现热备，一台宕机不影响服务，下面的配置文件，61抢占65，62抢占66

上面用的是3306端口和3307端口来对接应用程序

可以恢复双vip

做成两个IP都用3306端口

 

配置脚本

61  vi /etc/keepalived/keepalived.conf 后面添加

vrrp_instance v_mysql_master_deen1 {

interface ens34

state BACKUP

virtual_router_id 66

priority 150

nopreempt

virtual_ipaddress {

192.168.0.66/24

}

track_script {

chk_haproxy

}

notify_master /etc/keepalived/scripts/haproxy_master.sh

notify_backup /etc/keepalived/scripts/haproxy_backup.sh

notify_fault /etc/keepalived/scripts/haproxy_fault.sh

notify_stop /etc/keepalived/scripts/haproxy_stop.sh

}

 

62 vi /etc/keepalived/keepalived.conf后面添加

vrrp_instance v_mysql_master_deen1 {

interface ens34

state MASTER

virtual_router_id 66

priority 200

nopreempt

virtual_ipaddress {

192.168.0.66/24

}

track_script {

chk_haproxy

}

notify_master /etc/keepalived/scripts/haproxy_master.sh

notify_backup /etc/keepalived/scripts/haproxy_backup.sh

notify_fault /etc/keepalived/scripts/haproxy_fault.sh

notify_stop /etc/keepalived/scripts/haproxy_stop.sh

}

 

 

61,62都添加这个

vi /usr/local/haproxy/haproxy.cfg

frontend mysql_mgr_deen1_front_write

        bind 192.168.0.65:3306

        mode tcp

        default_backend mysql_mgr_deen1_back_write

backend mysql_mgr_deen1_back_write

        mode tcp

        balance leastconn

        option mysql-check user haproxy_check

        server mysql51 192.168.0.51:3306 weight 1 check inter 2000 rise 2 fall 5 on-marked-up shutdown-backup-sessions

        server mysql52 192.168.0.52:3306 weight 1 check inter 2000 rise 2 fall 5 backup

 

listen mysql_master_deen_lb1

bind 192.168.0.66:3306

mode tcp

option mysql-check user haproxy_check

stats hide-version

balance roundrobin

server mysql52 192.168.0.52:3306 weight 1 check inter 2000 rise 2 fall 5 maxconn 300

server mysql53 192.168.0.53:3306 weight 1 check inter 2000 rise 2 fall 5 maxconn 300

 

 

 

开始测试：

 

先systemctl stop keepalived

看 ip a

两边都试一下

 

 

恢复   都起起来，做上面一步是为了检查配置文件正不正确

 

 

 

 

然后：

停掉61的 keepalived     haproxy

systemctl stop keepalived

/etc/rc.d/init.d/haproxy stop 

mysql -uroot -proot -h192.168.0.65 -P3306 -e "select @@hostname;"

mysql -uroot -proot -h192.168.0.65 -P3307 -e "select @@hostname;"

恢复

systemctl start keepalived

/etc/rc.d/init.d/haproxy start

ip a 查看下，确认好

mysql -uroot -proot -h192.168.0.65 -P3306 -e "select @@hostname;"

mysql -uroot -proot -h192.168.0.65 -P3307 -e "select @@hostname;"