Shulou(Shulou.com)05/31 Report--

This article focuses on "how to use MySQL+Keepalived+haproxy to achieve load balancing", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to use MySQL+Keepalived+haproxy to achieve load balancing.

Database environment:

Slave2:192.168.8.59

Slave3:192.168.8.61

Toolkit:

Keepalived-1.4.5.tar.gz

Haproxy-1.8.9.tar.gz

In this experiment, slave2 and slave3 use keepalived and haproxy to achieve load balancing. This article focuses on the construction process of load balancing.

1. Modify kernel parameters

Echo "net.ipv4.ip_nonlocal_bind=1" > > / etc/sysctl.confecho "net.ipv4.ip_forward=1" > > / etc/sysctl.confsysctl-p

Net.ipv4.ip_nonlocal_bind=1 means that when starting haproxy, you are allowed to ignore the existence of VIP

Net.ipv4.ip_forward = 1 haproxy proxy server should also turn on kernel forwarding function

Second, install keepalived and haproxy

Mount / dev/cdrom / mediayum install kernel-devel openssl-devel popt-devel-ycd / softwaretar zxvf keepalived-1.4.5.tar.gzcd keepalived-1.4.5./configure-- prefix=/usr/local/keepalived/make & & make installsystemctl enable keepalived

Vi / usr/local/keepalived/etc/sysconfig/keepalived

KEEPALIVED_OPTIONS= "- D-d-S 0"

Vi / etc/rsyslog.conf

Local0.* / var/log/keepalived.logsystemctl restart rsyslogcd / softwaretar zxvf haproxy-1.8.9.tar.gzcd haproxy-1.8.9make TARGET=linux2628 PREFIX=/usr/local/haproxymake install PREFIX=/usr/local/haproxy/usr/local/haproxy/sbin/haproxy-vuseradd-r-s / sbin/nologin haproxytouch / var/log/haproxy.logchmod 755 / var/log/haproxy.log

Modify log location

Vi / etc/rsyslog.conflocal0.* / var/log/haproxy.logvi / etc/sysconfig/rsyslogSYSLOGD_OPTIONS= "- r-m 0-c 2" systemctl restart rsyslog

3. Configure keepalived

192.168.8.59

/ etc/keepalived/keepalived.conf

! Configuration File for keepalivedglobal_defs {router_id mysql-slavenotification_email {zdd5503@163.com} notification_email_from zdd5503@163.comsmtp_server stmp.163.comsmtp_connect_timeout 30} vrrp_script chk_haproxy {script "/ etc/keepalived/scripts/check_haproxy.sh" interval 2weight 2} vrrp_instance v_mysql_slave_zdd01 {interface enp0s3state MASTERvirtual_router_id 98priority 200nopreemptvirtual_ipaddress {192.168.98 and 24} track_script {chk_haproxy} notify_master / etc/keepalived/ Scripts/haproxy_master.shnotify_backup / etc/keepalived/scripts/haproxy_backup.shnotify_fault / etc/keepalived/scripts/haproxy_fault.shnotify_stop / etc/keepalived/scripts/haproxy_stop.sh}

192.168.8.61

/ etc/keepalived/keepalived.conf

! Configuration File for keepalivedglobal_defs {router_id mysql-slavenotification_email {zdd5503@163.com} notification_email_from zdd5503@163.comsmtp_server stmp.163.comsmtp_connect_timeout 30} vrrp_script chk_haproxy {script "/ etc/keepalived/scripts/check_haproxy.sh" interval 2weight 2} vrrp_instance v_mysql_slave_zdd01 {interface enp0s3state BACKUPvirtual_router_id 98priority 150nopreemptvirtual_ipaddress {192.168.98 and 24} track_script {chk_haproxy} notify_master / etc/keepalived/ Scripts/haproxy_master.shnotify_backup / etc/keepalived/scripts/haproxy_backup.shnotify_fault / etc/keepalived/scripts/haproxy_fault.shnotify_stop / etc/keepalived/scripts/haproxy_stop.sh}

4. Configure haproxy

Both 192.168.8.59 and 192.168.8.61 are configured as follows:

Mkdir-p / etc/keepalived/scripts

Vi / etc/keepalived/scripts/check_haproxy.sh

#! / bin/bashSTARTHAPROXY= "/ usr/local/haproxy/sbin/haproxy-f / usr/local/haproxy/haproxy.cfg" # STOPKEEPALIVED= "systemctl stop keepalived" LOGFILE= "/ etc/keepalived/scripts/keepalived-haproxy-state.log" echo "[check_haproxy status]" > > $LOGFILEA= `ps-C haproxy--no-header | wc-l`echo "[check_haproxy status]" > $LOGFILEdate > > $LOGFILEif [$A-eq 0]; thenecho $STARTHAPROXY > > $LOGFILE$STARTHAPROXY > > $LOGFILE 2 > & 1sleep 5fiif [`ps-C haproxy--no-header | wc-l`-eq 0]; thenexit 0elseexit 1fi

Vi / etc/keepalived/scripts/haproxy_master.sh

#! / bin/bashSTARTHAPROXY= `/ usr/local/haproxy/sbin/haproxy-f / usr/local/haproxy/ haproxy.cfg`STOPHAPROXY = `ps-ef | grep sbin/haproxy | grep-v grep | awk'{print $2}'| xargs kill-s 9`LOGFILE = "/ etc/keepalived/scripts/keepalived-haproxy-state.log" echo "[master]" > > $LOGFILEdate > > $LOGFILEecho "Being master...." > $LOGFILE 2 > & 1echo "stop haproxy...." > > $LOGFILE 2 > & 1$ STOPHAPROXY > > $LOGFILE 2 > & 1echo "start haproxy...." > > $LOGFILE 2 > & 1$ STARTHAPROXY > > $LOGFILE 2 > & 1echo "haproxy stared..." > > $LOGFILE

Vi / etc/keepalived/scripts/haproxy_backup.sh

#! / bin/bashSTARTHAPROXY= `/ usr/local/haproxy/sbin/haproxy-f / usr/local/haproxy/ haproxy.cfg`STOPHAPROXY = `ps-ef | grep sbin/haproxy | grep-v grep | awk'{print $2}'| xargskill-s 9`LOGFILE = "/ etc/keepalived/scripts/keepalived-haproxy-state.log" echo "[master]" > > $LOGFILEdate > > $LOGFILEecho "Being master...." > $LOGFILE 2 > & 1echo "stop haproxy...." > $LOGFILE 2 > & 1$ STOPHAPROXY > > $LOGFILE 2 > & 1echo "start haproxy...." > > $LOGFILE 2 > & 1$ STARTHAPROXY > $LOGFILE 2 > & 1echo "haproxy stared..." > $LOGFILE

Vi / etc/keepalived/scripts/haproxy_fault.sh

#! / bin/bashLOGFILE=/etc/keepalived/scripts/keepalived-haproxy-state.logecho "[fault]" > > $LOGFILEdate > > $LOGFILE

Vi / etc/keepalived/scripts/haproxy_stop.sh

#! / bin/bashLOGFILE=/etc/keepalived/scripts/keepalived-haproxy-state.logecho "[stop]" > > $LOGFILEdate > > $LOGFILE

Change script properties

Chmod 755 / etc/keepalived/scripts/*

Configure haproxy

192.168.8.59

Vi / usr/local/haproxy/haproxy.cfg

Global # Global configuration parameter log 127.0.0.1 local0 notice # Log # user haproxy # group haproxy daemon # number of haproxy # quiet nbproc 1 # processes running later You can set up multiple processes to improve the performance of pidfile / usr/local/haproxy/haproxy.pid # haproxy pid storage path defaults # some default parameters log global # global log retries 3 # 3 if the connection fails, the server is considered unavailable option dontlognull # Save haproxy does not record the last load After the server corresponding to the heartbeat packet option redispatch # sent by equalization to detect the status of no data hangs up Force to point to another normal server maxconn 2000 # default maximum number of connections timeout queue 1m # wait for the maximum length of time timeout http-request 10s # client to establish a connection without requesting data exceeds 10s To close the client connection timeout connect 10s # define the timeout for haproxy to forward client requests to the back-end server timeout server 1m # server inactive timeout client 1m # set http for client inactive timeout http-keep-alive 10s # -keep-alive timeout duration timeout check 10s # timeout balance roundrobin # load balancing method for health monitoring Polling method (keep session session: source address / cookies, for web application server) # define the configuration of load balancer listen mysql_slave_wgpt_lb1 bind 192.168.98 listen mysql_slave_wgpt_lb1 bind 3307 # bind IP to port (recommended bind *: 3306) mode tcp # mode is TCP, (layer 7 http,4 layer tcp, if it is mysql, use tcp If it is weblogic/was/tomcat or the like, use http) option mysql-check user haproxy_check # to check whether mysql can access haproxy version information on the stats hide-version # hidden statistics page through mysql connection. Balance roundrobin # load balancing method Polling method: server slave2 192.168.8.59 fall 3306 weight 1 check inter 2000 rise 2 fall 5 maxconn 300 server slave3 192.168.61 fall 3306 weight 1 check inter 2000 rise 2 fall 5 maxconn 300 # Server definition, check inter 2000 detects heartbeat rate, rise 22 times correctly considers the server available, fall 55 failures consider the server unavailable # maximum number of connections for maxconn 300The configuration of the built-in monitoring server listen haproxy_stats mode http bind *: 8888 option httplog stats refresh 5s stats uri / haproxy-stat stats realm www.zdd.com monitor stats realm Haproxy Manager stats auth haproxy:haproxy

192.168.8.61

Vi / usr/local/haproxy/haproxy.cfg

Global # Global configuration parameter log 127.0.0.1 local0 notice # Log # user haproxy # group haproxy daemon # number of haproxy # quiet nbproc 1 # processes running later You can set up multiple processes to improve the performance of pidfile / usr/local/haproxy/haproxy.pid # haproxy pid storage path defaults # some default parameters log global # global log retries 3 # 3 if the connection fails, the server is considered unavailable option dontlognull # Save haproxy does not record the last load After the server corresponding to the heartbeat packet option redispatch # sent by equalization to detect the status of no data hangs up Force to point to another normal server maxconn 2000 # default maximum number of connections timeout queue 1m # wait for the maximum length of time timeout http-request 10s # client to establish a connection without requesting data exceeds 10s To close the client connection timeout connect 10s # define the timeout for haproxy to forward client requests to the back-end server timeout server 1m # server inactive timeout client 1m # set http for client inactive timeout http-keep-alive 10s # -keep-alive timeout duration timeout check 10s # timeout balance roundrobin # load balancing method for health monitoring Polling method (keep session session: source address / cookies, for web application server) # define the configuration of load balancer listen mysql_slave_wgpt_lb1 bind 192.168.98 listen mysql_slave_wgpt_lb1 bind 3307 # bind IP to port (recommended bind *: 3306) mode tcp # mode is TCP, (layer 7 http,4 layer tcp, if it is mysql, use tcp If it is weblogic/was/tomcat or the like, use http) option mysql-check user haproxy_check # to check whether mysql can access haproxy version information on the stats hide-version # hidden statistics page through mysql connection. Balance roundrobin # load balancing method Polling method: server slave2 192.168.8.59 fall 3306 weight 1 check inter 2000 rise 2 fall 5 maxconn 300 server slave3 192.168.61 fall 3306 weight 1 check inter 2000 rise 2 fall 5 maxconn 300 # Server definition, check inter 2000 detects heartbeat rate, rise 22 times correctly considers the server available, fall 55 failures consider the server unavailable # maximum number of connections for maxconn 300The configuration of the built-in monitoring server listen haproxy_stats mode http bind *: 8888 option httplog stats refresh 5s stats uri / haproxy-stat stats realm www.zdd.com monitor stats realm Haproxy Manager stats auth haproxy:haproxy

5. Add haproxy_check users

Drop user haproxy_check@'%';create user haproxy_check@'192.168.8.59';create user haproxy_check@'192.168.8.61';create user haproxy_check@'192.168.8.98';grant usage on *. * to haproxy_check@'192.168.8.59';grant usage on *. * to haproxy_check@'192.168.8.61';grant usage on *. * to haproxy_check@'192.168.8.98';mysql > select user,host from mysql.user +-+-+ | user | host | +-+-+ | repl | 192.168.8.% | | root | 192.168.8.8% | | haproxy_check | 192.168.59 | | haproxy_check | | 192.168.8.61 | | haproxy_check | 192.168.8.98 | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +-+ |

6. Start keepalived

Systemctl daemon-reloadsystemctl enable keepalivedsystemctl start keepalivedip a | grep 98ps-ef | grep keepalived

7. Configure keepalived monitoring script

Both nodes are configured as follows:

Vi / etc/keepalived/keepcheck.sh

#! / bin/bashwhile: dokeepalivedcheck= `ps-C keepalived-- no-header | wc-l`if [$keepalivedcheck-eq 0]; thensystemctl start keepalivedelseecho "keepalived is running" fisleep 5donechmod + x / etc/keepalived/keepcheck.shnohup / etc/keepalived/keepcheck.sh & echo "nohup / etc/keepalived/keepcheck.sh &" > > / etc/rc.d/rc.local

Configure the haproxy startup script

Vim / etc/rc.d/init.d/haproxy

#! / bin/shset-ePATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/haproxy/sbinPROGDIR=/usr/local/haproxyPROGNAME=haproxyDAEMON=$PROGDIR/sbin/$PROGNAMECONFIG=$PROGDIR/$PROGNAME.cfgPIDFILE=$PROGDIR/$PROGNAME.pidDESC= "HAProxy daemon" SCRIPTNAME=/etc/init.d/$PROGNAME# Gracefully exit if the package has been removed.test-x $DAEMON | | exit 0start () {echo-n "Starting $DESC: $PROGNAME" $DAEMON-f $CONFIGecho "."} stop () {echo-n "Stopping $DESC: $PROGNAME" haproxy_pid= `cat $PIDFILE`kill-9 $haproxy_pidecho "."} restart () {echo-n "Restarting $DESC: $PROGNAME" $DAEMON-f $CONFIG-p $PIDFILE-sf $(cat $PIDFILE) echo "."} case "$1" instart) start ; stop) stop;;restart) restart;;*) echo "Usage: $SCRIPTNAME {start | stop | restart}" > & 2exit 1 politics esacexit 0chmod + x / etc/rc.d/init.d/haproxyecho "nohup / etc/rc.d/init.d/haproxy start &" > / etc/rc.d/rc.localsystemctl stop keepalivedsystemctl start keepalivedtail-f / var/log/keepalived.log/etc/rc.d/init.d/haproxy start/etc/rc.d/init.d/haproxy stoptail-f / var/log/haproxy.log

Haproxy console access address:

Http://192.168.8.98:8888/haproxy-stat

9. Test load balancing

[root@slave3] # mysql-uroot-pmysql-h292.168.98-P3307-e "show variables like 'server_id' "mysql: [Warning] Using a password on the command line interface can be insecure.+-+-+ | Variable_name | Value | +-+ | server_id | 59 | +-+-+ [root@slave3 ~] # mysql-uroot-pmysql- H 292.168.8.98-P3307-e "show variables like 'server_id' "mysql: [Warning] Using a password on the command line interface can be insecure.+-+-+ | Variable_name | Value | +-+ | server_id | 61 | +-+-+ [root@slave3 ~] # mysql-uroot-pmysql- H 292.168.8.98-P3307-e "show variables like 'server_id' "mysql: [Warning] Using a password on the command line interface can be insecure.+-+-+ | Variable_name | Value | +-+ | server_id | 59 | +-+-+ [root@slave3 ~] # mysql-uroot-pmysql- H 292.168.8.98-P3307-e "show variables like 'server_id' "mysql: [Warning] Using a password on the command line interface can be insecure.+-+-+ | Variable_name | Value | +-+ | server_id | 61 | +-+-+ [root@slave3 ~] # mysql-uroot-pmysql- H 292.168.8.98-P3307-e "show variables like 'server_id' "mysql: [Warning] Using a password on the command line interface can be insecure.+-+-+ | Variable_name | Value | +-+ | server_id | 59 | +-+-+ [root@slave3 ~] # mysql-uroot-pmysql- H 292.168.8.98-P3307-e "show variables like 'server_id' "mysql: [Warning] Using a password on the command line interface can be insecure.+-+-+ | Variable_name | Value | +-+-+ | server_id | 61 | +-- +

Because we configured the weight at 1:1 in the haproxy configuration file, the session will be distributed alternately between the two nodes.

At this point, I believe you have a deeper understanding of "how to use MySQL+Keepalived+haproxy to achieve load balancing". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.