mysql mha 负载均衡_mysql MHA扩展haproxy搭建从库只读负载均衡

检查配置文件是否有问题

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

&~

设置完成之后，重新系统日志进程

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

6月 11 15:53:09 mysqldb2 systemd[1]: Starting 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.

查看进程信息：

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

查看端口信息：

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

【负载均衡测试】

通过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   |

+------------+

【进程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

#}

#notification_email_from my163yun@163.com

#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

}

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

}

virtual_ipaddress {

192.168.142.115/24 dev eno16777736    #VIP

}

track_interface {

eth0

}

track_script {

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

查看VIP信息：

ip a

1: lo: 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: 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即可