mysql MHA扩展haproxy搭建从库只读负载均衡
【环境介绍】
系统环境：Red Hat Enterprise Linux 7 + 5.7.18 + MHA version 0.57
MHA架构中从库之间的负责均衡可选择mysql_route&keepalived+lvs&haproxy+keepalived&proxy中间件来实现，这里简单使用haproxy来实现
备库：192.168.142.112 192.168.142.113 这里设置192.168.142.113为haproxy配置主机
【搭建步骤：软件部署】
系统软件安装：
在一个从库或者在另一台主机上安装即可：直接使用系统自带的haproxy安装，yum install haproxy
mysql数据库用户创建：
两个从库创建，用于haproxy检测数据库状态，注意不要设置该用户的密码：
mysql> create user haproxy_check@'192.168.142.%';
Query OK, 0 rows affected (0.13 sec)
Query OK, 0 rows affected (0.13 sec)
mysql> grant usage on *.* to haproxy_check@'192.168.142.%';
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.21 sec)
Query OK, 0 rows affected (0.21 sec)
设置haproxy配置文件：
cd /etc/haproxy/
cat >haproxy.cfg
#---------------------------------------------------------------------
# Example configuration for a possible web application. See the
# full configuration options online.
#
# http://haproxy.1wt.eu/download/1.4/doc/configuration.txt
#
#---------------------------------------------------------------------
#---------------------------------------------------------------------
# Example configuration for a possible web application. See the
# full configuration options online.
#
# http://haproxy.1wt.eu/download/1.4/doc/configuration.txt
#
#---------------------------------------------------------------------
#---------------------------------------------------------------------
# Global settings
#---------------------------------------------------------------------
global
# to have these messages end up in /var/log/haproxy.log you will
# need to:
#
# 1) configure syslog to accept network log events. This is done
# by adding the '-r' option to the SYSLOGD_OPTIONS in
# /etc/sysconfig/syslog
#
# 2) configure local2 events to go to the /var/log/haproxy.log
# file. A line like the following can be added to
# /etc/sysconfig/syslog
#
# local2.* /var/log/haproxy.log
log 127.0.0.1 local0 notice
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
# Global settings
#---------------------------------------------------------------------
global
# to have these messages end up in /var/log/haproxy.log you will
# need to:
#
# 1) configure syslog to accept network log events. This is done
# by adding the '-r' option to the SYSLOGD_OPTIONS in
# /etc/sysconfig/syslog
#
# 2) configure local2 events to go to the /var/log/haproxy.log
# file. A line like the following can be added to
# /etc/sysconfig/syslog
#
# local2.* /var/log/haproxy.log
log 127.0.0.1 local0 notice
chroot /var/lib/haproxy
pidfile /var/run/haproxy.pid
maxconn 4000
user haproxy
group haproxy
#---------------------------------------------------------------------
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
log global
retries 3
option dontlognull
option redispatch
maxconn 2000
timeout connect 3000
timeout server 5000
timeout client 5000
# common defaults that all the 'listen' and 'backend' sections will
# use if not designated in their block
#---------------------------------------------------------------------
defaults
log global
retries 3
option dontlognull
option redispatch
maxconn 2000
timeout connect 3000
timeout server 5000
timeout client 5000
#---------------------------------------------------------------------
# round robin balancing between the various backends
#---------------------------------------------------------------------
listen mysql-mha
bind 192.168.142.113:4040
mode tcp
option mysql-check user haproxy_check
balance roundrobin
server mysql_mha2 192.168.142.112:3306 weight 1 check
server mysql_mha3 192.168.142.113:3306 weight 1 check
# round robin balancing between the various backends
#---------------------------------------------------------------------
listen mysql-mha
bind 192.168.142.113:4040
mode tcp
option mysql-check user haproxy_check
balance roundrobin
server mysql_mha2 192.168.142.112:3306 weight 1 check
server mysql_mha3 192.168.142.113:3306 weight 1 check
#---------------------------------------------------------------------
# static backend for serving up images, stylesheets and such
#---------------------------------------------------------------------
listen stats *:8888
mode http
option httpclose
balance roundrobin
stats uri /
stats realm Haproxy\ Statistics
stats auth myadmin:myadmin
# static backend for serving up images, stylesheets and such
#---------------------------------------------------------------------
listen stats *:8888
mode http
option httpclose
balance roundrobin
stats uri /
stats realm Haproxy\ Statistics
stats auth myadmin:myadmin
检查配置文件是否有问题
haproxy -f /etc/haproxy/haproxy.cfg -c 输出：Configuration file is valid为正常
设置日志路径配置：
cat /etc/rsyslog.d/49-haproxy.conf
# Create an additional socket in haproxy's chroot in order to allow logging via
# /dev/log to chroot'ed HAProxy processes
$AddUnixListenSocket /var/lib/haproxy/dev/log
$ModLoad imudp
$UDPServerRun 514
local3.* /var/log/haproxy.log
# Send HAProxy messages to a dedicated logfile
if $programname startswith 'haproxy' then /var/log/haproxy.log
&~
# Create an additional socket in haproxy's chroot in order to allow logging via
# /dev/log to chroot'ed HAProxy processes
$AddUnixListenSocket /var/lib/haproxy/dev/log
$ModLoad imudp
$UDPServerRun 514
local3.* /var/log/haproxy.log
# Send HAProxy messages to a dedicated logfile
if $programname startswith 'haproxy' then /var/log/haproxy.log
&~
设置完成之后，重新系统日志进程
service rsyslog restart
【启动haproxy进程】
启动haproxy进程
service haproxy start
查看进程状态及日志信息
service haproxy status
Redirecting to /bin/systemctl status haproxy.service
haproxy.service - HAProxy Load Balancer
Loaded: loaded (/usr/lib/systemd/system/haproxy.service; disabled)
Active: active (running) since 一 2018-06-11 15:53:09 CST; 35s ago
Main PID: 10512 (haproxy-systemd)
CGroup: /system.slice/haproxy.service
├─10512 /usr/sbin/haproxy-systemd-wrapper -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid
├─10513 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
└─10514 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
Redirecting to /bin/systemctl status haproxy.service
haproxy.service - HAProxy Load Balancer
Loaded: loaded (/usr/lib/systemd/system/haproxy.service; disabled)
Active: active (running) since 一 2018-06-11 15:53:09 CST; 35s ago
Main PID: 10512 (haproxy-systemd)
CGroup: /system.slice/haproxy.service
├─10512 /usr/sbin/haproxy-systemd-wrapper -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid
├─10513 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
└─10514 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
6月 11 15:53:09 mysqldb2 systemd[1]: Starting HAProxy Load Balancer...
6月 11 15:53:09 mysqldb2 systemd[1]: Started HAProxy Load Balancer.
6月 11 15:53:09 mysqldb2 systemd[1]: Started HAProxy Load Balancer.
查看日志信息：
tail -f /var/log/haproxy.log
Jun 11 15:53:09 localhost haproxy[10513]: Proxy mysql-mha started.
Jun 11 15:53:09 localhost haproxy[10513]: Proxy stats started.
Jun 11 15:53:09 localhost haproxy[10513]: Proxy stats started.
查看进程信息：
ps -ef |grep haproxy
root 10512 1 0 15:53 ? 00:00:00 /usr/sbin/haproxy-systemd-wrapper -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid
haproxy 10513 10512 0 15:53 ? 00:00:00 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
haproxy 10514 10513 0 15:53 ? 00:00:00 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
root 10609 62609 0 15:53 pts/0 00:00:00 tail -f /var/log/haproxy.log
root 10809 62956 0 15:54 pts/5 00:00:00 grep --color=auto haproxy
root 10512 1 0 15:53 ? 00:00:00 /usr/sbin/haproxy-systemd-wrapper -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid
haproxy 10513 10512 0 15:53 ? 00:00:00 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
haproxy 10514 10513 0 15:53 ? 00:00:00 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
root 10609 62609 0 15:53 pts/0 00:00:00 tail -f /var/log/haproxy.log
root 10809 62956 0 15:54 pts/5 00:00:00 grep --color=auto haproxy
查看端口信息：
netstat -nltp|grep haproxy
tcp 0 0 192.168.142.113:4040 0.0.0.0:* LISTEN 10513/haproxy
tcp 0 0 0.0.0.0:8888 0.0.0.0:* LISTEN 10513/haproxy
tcp 0 0 192.168.142.113:4040 0.0.0.0:* LISTEN 10513/haproxy
tcp 0 0 0.0.0.0:8888 0.0.0.0:* LISTEN 10513/haproxy
【负载均衡测试】
通过192.168.142.113 4040端口访问数据库，每次执行都访问不同的数据库即可
mysql -ubackup -pbackup -h192.168.142.113 -P4040 -e "select @@hostname";
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysqldb1 |
+------------+
mysql -ubackup -pbackup -h192.168.142.113 -P4040 -e "select @@hostname";
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysqldb2 |
+------------+
mysql -ubackup -pbackup -h192.168.142.113 -P4040 -e "select @@hostname";
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysqldb1 |
+------------+
mysql -ubackup -pbackup -h192.168.142.113 -P4040 -e "select @@hostname";
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysqldb2 |
+------------+
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysqldb1 |
+------------+
mysql -ubackup -pbackup -h192.168.142.113 -P4040 -e "select @@hostname";
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysqldb2 |
+------------+
mysql -ubackup -pbackup -h192.168.142.113 -P4040 -e "select @@hostname";
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysqldb1 |
+------------+
mysql -ubackup -pbackup -h192.168.142.113 -P4040 -e "select @@hostname";
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysqldb2 |
+------------+
【进程web监控】
访问：http://192.168.142.113:8888/haproxyadmin?stats 用户密码为配置文件中的：myadmin/myadmin
【haproxy高可用用扩展】
当需要对VIP进行高可用配置时可以使用keepalived配置
yum install keepalived
keepalived配置文件，注意挂载VIP不要被占用：
cat >/etc/keepalived/keepalived.conf
global_defs {
#notification_email {
# xxx@qq.com
#}
global_defs {
#notification_email {
# xxx@qq.com
#}
#notification_email_from my163yun@163.com
#smtp_server smtp.163.com
smtp_connect_timeout 30
router_id LVS_HAPROXY
}
#smtp_server smtp.163.com
smtp_connect_timeout 30
router_id LVS_HAPROXY
}
vrrp_script chk_haproxy {
script "killall -0 haproxy" #服务探测，返回0说明服务是正常的
interval 1 #每隔1秒探测一次
weight 2
}
script "killall -0 haproxy" #服务探测，返回0说明服务是正常的
interval 1 #每隔1秒探测一次
weight 2
}
vrrp_instance VI_1 {
state MASTER #mha1为主，mha2为备
interface eno16777736
virtual_router_id 113 #实例1的VRID为113
garp_master_delay 1
priority 100 #主的优先级为100，从的优先级为99
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
state MASTER #mha1为主，mha2为备
interface eno16777736
virtual_router_id 113 #实例1的VRID为113
garp_master_delay 1
priority 100 #主的优先级为100，从的优先级为99
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.142.115/24 dev eno16777736 #VIP
}
track_interface {
eth0
}
192.168.142.115/24 dev eno16777736 #VIP
}
track_interface {
eth0
}
track_script {
chk_haproxy
}
chk_haproxy
}
}
启动keepalived进程
service keepalived start
查看日志信息
[root@mysqldb2 keepalived]# tail -f /var/log/messages
Jun 11 18:35:45 localhost Keepalived_healthcheckers[30312]: Opening file '/etc/keepalived/keepalived.conf'.
Jun 11 18:35:45 localhost Keepalived_healthcheckers[30312]: Configuration is using : 7595 Bytes
Jun 11 18:35:45 localhost Keepalived_healthcheckers[30312]: Using LinkWatch kernel netlink reflector...
Jun 11 18:35:46 localhost Keepalived_vrrp[30313]: VRRP_Instance(VI_1) Transition to MASTER STATE
Jun 11 18:35:47 localhost Keepalived_vrrp[30313]: VRRP_Instance(VI_1) Entering MASTER STATE
Jun 11 18:35:47 localhost Keepalived_vrrp[30313]: VRRP_Instance(VI_1) setting protocol VIPs.
Jun 11 18:35:47 localhost Keepalived_vrrp[30313]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eno16777736 for 192.168.142.115
Jun 11 18:35:47 localhost avahi-daemon[484]: Registering new address record for 192.168.142.115 on eno16777736.IPv4.
Jun 11 18:35:47 localhost Keepalived_healthcheckers[30312]: Netlink reflector reports IP 192.168.142.115 added
Jun 11 18:35:48 localhost Keepalived_vrrp[30313]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eno16777736 for 192.168.142.115
Jun 11 18:35:45 localhost Keepalived_healthcheckers[30312]: Opening file '/etc/keepalived/keepalived.conf'.
Jun 11 18:35:45 localhost Keepalived_healthcheckers[30312]: Configuration is using : 7595 Bytes
Jun 11 18:35:45 localhost Keepalived_healthcheckers[30312]: Using LinkWatch kernel netlink reflector...
Jun 11 18:35:46 localhost Keepalived_vrrp[30313]: VRRP_Instance(VI_1) Transition to MASTER STATE
Jun 11 18:35:47 localhost Keepalived_vrrp[30313]: VRRP_Instance(VI_1) Entering MASTER STATE
Jun 11 18:35:47 localhost Keepalived_vrrp[30313]: VRRP_Instance(VI_1) setting protocol VIPs.
Jun 11 18:35:47 localhost Keepalived_vrrp[30313]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eno16777736 for 192.168.142.115
Jun 11 18:35:47 localhost avahi-daemon[484]: Registering new address record for 192.168.142.115 on eno16777736.IPv4.
Jun 11 18:35:47 localhost Keepalived_healthcheckers[30312]: Netlink reflector reports IP 192.168.142.115 added
Jun 11 18:35:48 localhost Keepalived_vrrp[30313]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eno16777736 for 192.168.142.115
查看VIP信息：
ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:c9:13:a2 brd ff:ff:ff:ff:ff:ff
inet 192.168.142.113/24 brd 192.168.142.255 scope global eno16777736
valid_lft forever preferred_lft forever
inet 192.168.142.115/24 scope global secondary eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fec9:13a2/64 scope lin
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eno16777736: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:c9:13:a2 brd ff:ff:ff:ff:ff:ff
inet 192.168.142.113/24 brd 192.168.142.255 scope global eno16777736
valid_lft forever preferred_lft forever
inet 192.168.142.115/24 scope global secondary eno16777736
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fec9:13a2/64 scope lin
修改haproxy配置文件的IP为keepalived挂载的VIP即可
