Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Use the HAProxy proxy MySQL Master-Sl method

2025-01-27 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Servers >

Share

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

This article is mainly about the use of HAProxy proxy MySQL Master-Sl method, if you are interested, let's take a look at this article. I believe it is of some reference value to everyone after reading the HAProxy proxy MySQL Master-Sl method.

Background

I have a MySQL Master-Slave Replication that I want to do read-write separation through HAProxy agents.

Write traffic to the Master node and read traffic to the Slave node.

First of all, I should have a HAProxy configuration, which I think should be written like this (pseudo configuration):

Listen mysql bind *: 3316 mode tcp... Server 192.168.1.111 192.168.1.111:3306 check server 192.168.1.112 192.168.1.112:3306 check server 192.168.1.113 192.168.1.113:3306 check

Obviously, this only achieves the distribution of traffic, but not what I need: write traffic to Master, read traffic to Slave nodes.

So the problem is, first of all, I need to be able to identify Master Slave nodes in order to have the next traffic separation.

HAProxy has a configuration item called mysql-check. But it can't be used for Master-Slave testing here, because this option only provides survival tests.

As long as it can be connected, it means that the mysql backend is Health OK.

This still doesn't meet my needs.

It would be nice if MySQL could pass the tcp-check test to see if it was Master or Slave.

In that case, why not write a TCP-check Wrapper?

TCP-Check-Wrapper

This Wrapper should listen on the TCP port and run as a daemon.

When HAproxy connects to the port it listens on, it executes the corresponding script and sends the script execution result to Haproxy. TCP connection.

The code is as follows:

TCP-Check-Wrappermysqlchk

Place the mysqlchk script and run the tcp-check-wrapper service on each MySQL node.

Test the script on the Master node

[root@control-01] # mysqlchk.mysqlHTTP/1.1 200OKContent-Type: text/htmlContent-Length: 43MySQL master is running.

Test it on the Slave node

[root@control-02] # mysqlchk.mysqlHTTP/1.1 200OKContent-Type: text/htmlContent-Length: 43MySQL slave is running. (Slave lag: 0)

OK, through the mysql check script can already help us distinguish between Master and Slave.

Then configure tcp-check-wrapper.

# / etc/haproxy-tcp-check-wrapperhost = "0.0.0.0" port = 9090script = "/ usr/bin/mysqlchk.mysql"

On each MySQL node, run tcp-check-wrapper.

Haproxy_tcp_check & configuration of HAProxy

Based on what we did earlier, we can now write out the configuration file for HAProxy.

Listen mysql_write bind *: 3316 mode tcp timeout client 10800s timeout server 10800s balance leastconn option tcp-check tcp-check expect string MySQL\ master\ is\ running. Option allbackups default-server port 9090 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 64 maxqueue 128 weight 100 server 192.168.1.111 192.168.1.111 192.168.1.111 192.168.1.112 192.168.1.112 check server 192.168.1.113 192.168.1.113mer 3306 checklisten mysql_read bind *: 3317 mode tcp timeout client 10800s timeout server 10800s balance Leastconn option tcp-check tcp-check expect string MySQL\ slave\ is\ running. Option allbackups default-server port 9090 inter 2s downinter 5s rise 3 fall 2 slowstart 60s maxconn 64 maxqueue 128 weight 100 server 192.168.1.111 192.168.1.111 192.168.1.111 192.168.1.112 192.168.1.112 check server 192.168.1.113 192.168.1.113Luv 3306 check Summary

So far, we have achieved the read-write separation of MySQL Master-Slave Replication through HAProxy and with the help of "TCP-Check-Wrapper" + "mysqlchk".

TCP-Check-Wrapper helps us check some applications that cannot use tcp-check option.

Mysqlchk provides a specific method of detection.

In fact, if you think a little more, you can further use MHA + HAPRoxy to achieve high availability of MySQL Master-Slave Replication.

That is, when the Master fails, MHA can upgrade one of the MySQL Slave to Master, and configure the other Slave to point to the new Master.

Is the above details about using HAProxy proxy MySQL Master-Sl method helpful to you? If you want to know more about it, you can continue to follow our industry information section.

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.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Servers

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report