Shulou(Shulou.com)06/01 Report--

Haproxy+mysql cluster how to achieve load balancing, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

HAProxy is a layer-7 proxy. After enabling HAProxy, the source IP address of Apps cannot be seen on the MySQL, but the HAProxy address is seen. The permission access setting of MySQL is related to the IP address, which makes it impossible for MySQL to distinguish permissions for applications, so you should pay attention to it when using it.

1. Installation of HAProxy

Shell > yum install haproxy

2. Configuration of HAProxy

2.1. Haproxy.cfg

Save the following configuration file as / etc/haproxy/haproxy.cfg

[root@mysql3 haproxy] # cat / etc/haproxy/haproxy.cfg

# this config needs haproxy-1.1.28 or haproxy-1.2.1

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 http

# option httplog

Option dontlognull

Retries 3

Option redispatch

Maxconn 2000

Contimeout 5000

Clitimeout 50000

Srvtimeout 50000

# bound IP and port

Listen MySQL 10.100.25.42:3308

Mode tcp

Maxconn 200

# load balancing algorithm is polling

Balance roundrobin

# check whether mysql can be accessed through mysql connection

Option mysql-check user haproxy_check

Server mysql_1 10.100.25.40:3308 inter 1s rise 2 fall 2

Server mysql_3 10.100.25.41:3307 inter 1s rise 2 fall 2

Server mysql_3 10.100.25.41:3308 inter 1s rise 2 fall 2

# configuration of the built-in monitoring server

Listen admin_status

Mode http

Bind 0.0.0.0:8899

Option httplog

Log global

Stats enable

Stats refresh 10s

Stats hide-version

Stats realm Haproxy\ Statistics

Stats uri /

# user name and password for monitoring

Stats auth myadmin:myadmin

Stats admin if TRUE

2.2. Add haproxy_check user

Execute the following SQL statement on the Master side and pass it to the Slave through the copy function.

Drop user haproxy_check@'XX'

Create user haproxy_check@'XX'

Grant usage on *. * to haproxy_check@'XX'

2.3. Configuration log

Note: this method is only used on CentOS 6.x, and can be viewed by systemctl status haproxy after CentOS 7.x installs HAProxy.

Save the following file as / 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

& ~

Then restart the rsyslog service

Shell > service rsyslog restart

Shutting down system logger: [OK]

Starting system logger: [OK]

3. Start HAProxy

Shell > service haproxy start

[root@mysql3 ~] # netstat-tunlp | grep haproxy

Tcp 0 0 0.0.0.0 3308 0.0.0.015 * LISTEN 2583/haproxy

Tcp 0 0 0.0.0. 0. 0. 0. 0. 0. 8. 8. 0. 0. 0. 0. 0. 0

Udp 0 0 0.0.0.0 33136 0.0.0.015 * 2583/haproxy

4. HAProxy test

[root@mysql3 haproxy] # mysql-h 10.100.25.42-P 3308-uroot-pmysql-e "show variables like 'server_id'"

Mysql: [Warning] Using a password on the command line interface can be insecure.

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 22 | |

+-+ +

[root@mysql3 haproxy] # mysql-h 10.100.25.42-P 3308-uroot-pmysql-e "show variables like 'server_id'"

Mysql: [Warning] Using a password on the command line interface can be insecure.

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 33 | |

+-+ +

[root@mysql3 haproxy] # mysql-h 10.100.25.42-P 3308-uroot-pmysql-e "show variables like 'server_id'"

Mysql: [Warning] Using a password on the command line interface can be insecure.

+-+ +

| | Variable_name | Value |

+-+ +

| | server_id | 44 | |

+-+ +

After reading the above, have you mastered how to achieve load balancing in haproxy+mysql clusters? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.