Proxy Protocol and Percona XtraDB Cluster: A Quick Guide

PREVIOUS POST
NEXT POST

On September 21st, we released Percona XtraDB Cluster 5.6.25. This is the first PXC release supporting proxy-protocol that has been included in Percona Server since 5.6.25-73.0.

With this blog post, I want to promote a new feature that you may have ignored.

Let’s start with a description of the use case:

Architecture Overview:

cluster3

HAProxy is configured like this:

So until now, when we connected from any machine to HA Proxy in MySQL, the connection was made by HAProxy’s host.

In the processlist we could see it like this:

Shown like this, it’s impossible to know which connection is made from app1 and which from app2, as they all come from the proxy. Therefore this is also the host that’s required in GRANTS.

And it is particularly for this reason that we decided to support proxy protocol.

Let’s add the required setting in my.cnf:

Then we need to also modify HAProxy’s config to be like this:

send-proxy-v2 is also supported.


From HA Proxy’s Manual:

Version 1 senders MAY only produce the human-readable header format. Version 2
senders MAY only produce the binary header format. Version 1 receivers MUST at
least implement the human-readable header format. Version 2 receivers MUST at
least implement the binary header format, and it is recommended that they also
implement the human-readable header format for better interoperability and ease
of upgrade when facing version 1 senders.

So now, when MySQL and HAProxy are restarted, we can see in the processlist the origin of the connection:

Is everything perfect then? Not really…

One disadvantage of this proxy_protocol_network setting is that now you are not able to connect to MySQL if you don’t send the proxy headers:

This connection seems stalled… no answer, no error…

In the MySQL processlist we can see:

This means you can connect only via HAProxy or the socket… Of course this limits you a lot, not only for DBAs or developers but also for things like replication slaves, for example.

And you can also imagine how fast you could reach the max connections as every tentative will be stalled in that state:

The usual MySQL variable connect_timeout doesn’t work.

To avoid this and also to connect directly to MySQL, you need to specify from which network MySQL expects those proxy headers. Therefore proxy_protocol_network (ppn) should be set to the proxy address (or network range if your proxies use a different dedicated one):

Even if this is the recommended solution, it won’t work because it’s mandatory to bind MySQL to listen to the IPv4 address:

This is the error you would face if you don’t bind MySQL:

When this is implemented in the configuration and MySQL is restarted, it’s now possible to connect to MySQL from the proxy or directly:

I tried to completely disable IPv6 instead of binding to a specific IP in MySQL (something I’ve heard of?) but it didn’t help:

To summarize, if you want to enable the resolution of the real client (origin) instead of HAProxy’s IP in MySQL’s processlist and GRANTS (for authentication), you need:

  • add proxy-protocol in HAProxy
  • add proxy_protocol_networks to my.cnf and set it to the proxy’s IP
  • bind mysql to its IPv4 IP

This is also a summary of settings I’ve tried and their results:

MySQL bind proxy_protocol_networks HA Proxy protocol option Result
n/a n/a n/a We see HA Proxy IP/host in MySQL as source for the connection
n/a n/a send-proxy ERROR 2013 (HY000): Lost connection to MySQL server at ‘reading authorization packet’, system error: 0
n/a ppn=* send-proxy We see APP as source of connection when connecting using the proxy
We can’t connect directly to MySQL
n/a ppn=ha proxy’s IP send-proxy We can connect directly
We can’t conection using the proxy: ERROR 2013 (HY000): Lost connection to MySQL server at ‘reading authorization packet’, system error: 0
IP ppn=ha proxy’s IP send-proxy We see APP as source of the connection when using the proxy
We can connect direclty
IP ppn=MySQL’s IP send-proxy Cannot connect using the proxy: ERROR 2013 (HY000): Lost connection to MySQL server at ‘reading authorization packet’, system error: 0
IP ppn=network range (192.168.56.0/24) send-proxy We can connect using the Proxy and see APP as source
We cannot connect directly

These two bugs were reported while writing this blog post:

PREVIOUS POST
NEXT POST

Share this post

Comments (2)

  • SuperQ Reply

    This is why we use IPVS and not haproxy for MySQL load balancing. IPVS also has the added benefit that it’s Direct Server Return, so the query results do not have to cross back through the load-balancer.

    But in reality, we’re moving away from this and will be switching to service discovery, currently implemented by consul and DNS. This avoids load-balancers altogether.

    October 18, 2015 at 4:18 am
  • wodesuck Reply

    set proxy_protocol_networks to an IPv4-mapped address(::ffff:a.b.c.d) make it work properly without bind_address.

    November 22, 2016 at 3:51 am

Leave a Reply