利用keepalived和haproxy配置mysql的高可用负载均衡

实验系统：CentOS 7.5

实验前提：防火墙和selinux都关闭

实验说明：本实验共有4台主机，IP分配如拓扑

实验软件：keepalived　　haproxy-1.5.19　　mariadb

实验拓扑：

一：安装MySql（在两个mysql节点上都配置）
1：安装mariadb
[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# setenforce 0

[root@localhost ~]# yum -y install mariadb-server
[root@localhost ~]# systemctl start mariadb

2：修改root密码：
[root@localhost ~]# mysqladmin -u root password ‘pwd123‘

二：mysql配置双主复制
1：节点1配置

修改 Master 的配置文件/etc/my.cnf

vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1
log-slave-updates=true

[root@localhost ~]# systemctl restart mariadb

2：节点2配置

vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=2
log-slave-updates=true

3：在两个mysql上都创建用于复制操作的用户
创建用户并授权
[root@localhost ~]# mysql -uroot -ppwd123

MariaDB [(none)]> grant all privileges on . to zhangsan@‘%‘ identified by ‘pwd123‘;

刷新授权表信息
MariaDB [(none)]> flush privileges;

4：在两个节点上获取主节点当前binary log文件名和位置（position）

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 155 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5：在两个节点上设置主节点参数
（1）节点一
mysql> change master to master_host=‘192.168.10.102‘,master_user=‘zhangsan‘,master_password=‘pwd123‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=155;

开启从节点
mysql> start slave;

（2）节点二

mysql> change master to master_host=‘192.168.10.101‘,master_user=‘zhangsan‘,master_password=‘pwd123‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=155;

开启从节点
mysql> start slave;

6：在两个节点上查看主从同步状态

mysql> show slave status\G;

二、编译安装haproxy

1：在10.103和10.104上编译安装haproxy：
[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# setenforce 0
[root@localhost ~]# yum -y install pcre-devel bzip2-devel gcc*

[root@localhost ~]# tar zxvf haproxy-1.5.19.tar.gz
[root@localhost ~]# cd haproxy-1.5.19/
[root@localhost haproxy-1.5.19]# make TARGET=linux26
[root@localhost haproxy-1.5.19]# make install

2：建立haproxy配置文件
[root@localhost haproxy-1.5.19]# mkdir /etc/haproxy
[root@localhost haproxy-1.5.19]# cp examples/haproxy.cfg /etc/haproxy/

4：创建启动脚本
[root@localhost haproxy-1.5.19]# cp examples/haproxy.init /etc/init.d/haproxy
[root@localhost haproxy-1.5.19]# ln -s /usr/local/sbin/haproxy /usr/sbin/haproxy
[root@localhost haproxy-1.5.19]# chmod +x /etc/init.d/haproxy
[root@localhost haproxy-1.5.19]# /etc/init.d/haproxy start

3：修改配置文件：
[root@localhost ~]# mkdir /usr/share/haproxy
[root@localhost ~]# vim /etc/haproxy/haproxy.cfg

global
log 127.0.0.1 local0
log 127.0.0.1 local1 notice
#log loghost local0 info
maxconn 4096
chroot /usr/share/haproxy
uid 99
gid 99
daemon
#debug
#quiet

defaults
log global
mode tcp
option httplog
option dontlognull
retries 3
redispatch
maxconn 2000
contimeout 5000
clitimeout 50000
srvtimeout 50000

listen stats
mode http
bind :6677
stats enable
stats hide-version
stats uri /admin
stats realm haproxy
stats auth admin:admin
stats admin if TRUE

frontend main *:3306
default_backend mysql

backend mysql
balance leastconn
server m1 192.168.10.101:3306 check port 3306 maxconn 300
server m2 192.168.10.102:3306 check port 3306 maxconn 300

*```
注释：
frontend main :3306
default_backend mysql

backend mysql
balance leastconn
server m1 192.168.10.101:3306 check port 3306 maxconn 300
server m2 192.168.10.102:3306 check port 3306 maxconn 300

此部分可以写成
listen mysql 0.0.0.0:3306
balance leastconn
server m1 192.168.10.101:3306 check port 3306 maxconn 300
server m2 192.168.10.102:3306 check port 3306 maxconn 300
**

注释：listen stats ##配置haproxy状态页 mode http bind :6677 ##找一个比较特殊的端口 stats enable stats hide-version ##隐藏haproxy版本号 stats uri /haproxyadmin?stats ##用于打开状态页的uri stats realm Haproxy\ Statistics ##输入账户密码时的提示文字 stats auth admin:admin ##用户名:密码 stats admin if TRUE ##开启状态页的管理功能frontend main *:3306 ##这里为了实验方便，使用3306端口 default_backend mysql ##后端服务器组名backend mysql balance leastconn ##使用最少连接方式调度注意：以上配置是为了在网页中测试 haproxy 调度情况5：启动测试haproxy：service haproxy startnetstat -tnlp　6：按照上述步骤部署第二台haproxy三：在客户机上测试haproxy的代理功能1：在mysql01上创建测试用的账号，并授权GRANT ALL ON *.* TO ‘lisi‘@‘192.168.10.%‘ IDENTIFIED BY ‘123456‘;FLUSH PRIVILEGES;2：在客户端分别登录两个haproxy，若都能连接成功则继续往下：yum -y install mysql ##如果没有mysql客户端则运行此命令mysql -ulisi -p123456 -h192.168.10.103 mysql -ulisi -p123456 -h192.168.10.104 四、安装keepalived1.在10.103和10.104上安装keepalived：[root@localhost ~]# systemctl stop NetworkManager ##关于IP 协议的，需关闭此项[root@localhost ~]# setenforce 0[root@localhost ~]# systemctl stop firewalld[root@localhost ~]# yum -y install keepalived ipvsadm[root@localhost ~]# systemctl enable keepalived2.在10.103上配置keepalived：[root@localhost ~]# vim /etc/keepalived/keepalived.confglobal_defs { notification_email { acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc } notification_email_from Alexandre.Cassen@firewall.loc smtp_server 192.168.200.1 smtp_connect_timeout 30 router_id LVS_01}vrrp_script chk_haproxy { script "/etc/keepalived/chk.sh" ##检查haproxy的脚本 interval 2 ##每两秒检查一次}vrrp_instance VI_1 { state BACKUP ##定义为BACKUP节点 nopreempt ##开启不抢占 interface eth0 virtual_router_id 51 priority 100 ##开启了不抢占，所以此处优先级必须高于另一台 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.10.100 ##配置VIP } track_script { chk_haproxy ##调用检查脚本 } notify_backup "/etc/init.d/haproxy restart" notify 通告 track 跟踪 chk检查 notify_fault "/etc/init.d/haproxy stop"}3.在10.104上配置keepalived：[root@localhost ~]# vim /etc/keepalived/keepalived.confglobal_defs { notification_email { acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc } notification_email_from Alexandre.Cassen@firewall.loc smtp_server 192.168.200.1 smtp_connect_timeout 30 router_id LVS_02}vrrp_script chk_haproxy { script "/etc/keepalived/chk.sh" interval 2}vrrp_instance VI_1 { state BACKUP interface eth0 virtual_router_id 51 priority 99 advert_int 1 authentication { auth_type PASS auth_pass 1111 } virtual_ipaddress { 192.168.10.100 } track_script { chk_haproxy } notify_backup "/etc/init.d/haproxy restart" notify_fault "/etc/init.d/haproxy stop"}注释： 如果不写，则Haproxy stop 后，IP 漂移不过 notify_master:当当前节点成为master时，通知脚本执行任务notify_backup:当当前节点成为backup时，通知脚本执行任务notify_fault：当当前节点出现故障，执行的任务; 4.在两台机器上创建chk.sh文件：[root@localhost ~]# vim /etc/keepalived/chk.sh#!/bin/bash#if [ $(ps -C haproxy --no-header | wc -l) -eq 0 ]; then systemctl restart keepalivedfi[root@localhost ~]# chmod +x /etc/keepalived/chk.sh5.在10.103和10.104上进行测试：[root@localhost ~]# service keepalived start　　　　此处两台主机均配置为BACKUP，因此哪台先运行keepalived，VIP就在哪台上。我这里刚开始VIP运行在10.103上，然后进行连接测试：　　　　mysql -ulisi -p123456 -h192.168.10.100CREATE DATABASE test；　　　　测试：　　　　　　　　停掉10.103的keepalived服务，让VIP转移到10.104上，再进行测试：[root@localhost ~]# service keepalived stop ##停掉10.103的keepalived服务mysql -ujason -p123456 -h192.168.10.100------------------------------------------->SHOW DATABASES;　　　　在浏览器打开http:##192.168.19.150:6677/admin，打开haproxy状态页：在10.101上关闭mysql服务，可以看到haproxy对于后端服务器的检测是很迅速的：[root@localhost ~]# service mysqld stop　　7.额外说明：　　　　继续之前的实验，将10.103上的keepalived服务再次启动，可以发现，VIP仍然在10.104上，这就是之前为什么要配置不抢占的原因。如果按照正常的配置，将10.103配置为MASTER，当它重启keepalived服务后，则一定会将VIP抢回。但实际上我们并不希望这样，因为10.104仍在正常工作，10.103没有理由去抢夺资源，造成没必要的资源切换。实验演示就到这里，谢谢大家！