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:

HAProxy is configured like this:

listen 3307-active-passive-writes 0.0.0.0:3307
    mode tcp
    balance leastconn
    option  httpchk
    server pxc1 pxc1:3306 check port 8000 inter 1000 rise 3 fall 3
    server pxc2 pxc2:3306 check port 8000 inter 1000 rise 3 fall 3   backup
    server pxc3 pxc3:3306 check port 8000 inter 1000 rise 3 fall 3   backup

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

[root@app1 ~]# mysql -h haproxy -P 3307 -utest -ptest -e "select @@wsrep_node_name, sleep(20)";
[root@app2 ~]# mysql -h haproxy -P 3307 -utest -ptest -e "select @@wsrep_node_name, sleep(20)";

In the processlist we could see it like this:

pxc1 mysql>  SELECT PROCESSLIST_ID AS id, PROCESSLIST_USER AS user,
             PROCESSLIST_HOST AS host, PROCESSLIST_INFO
             FROM performance_schema.threads
             WHERE PROCESSLIST_INFO LIKE 'select @% sleep%';
+------+------+---------+-------------------------------------+
| id   | user | host    | PROCESSLIST_INFO                    |
+------+------+---------+-------------------------------------+
|  961 | test | haproxy | select @@wsrep_node_name, sleep(20) |
|  963 | test | haproxy | select @@wsrep_node_name, sleep(20) |
+------+------+---00----+-------------------------------------+

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:

proxy_protocol_networks = *

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

listen 3307-active-passive-writes 0.0.0.0:3307
    mode tcp
    balance leastconn
    option  httpchk
    server pxc1 pxc1:3306 send-proxy check port 8000 inter 1000 rise 3 fall 3
    server pxc2 pxc2:3306 send-proxy check port 8000 inter 1000 rise 3 fall 3   backup
    server pxc3 pxc3:3306 send-proxy check port 8000 inter 1000 rise 3 fall 3   backup

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:

+------+------+------+-------------------------------------+
| id   | user | host | PROCESSLIST_INFO                    |
+------+------+------+-------------------------------------+
|  283 | test | app1 | select @@wsrep_node_name, sleep(20) |
|  284 | test | app2 | select @@wsrep_node_name, sleep(20) |
+------+------+------+-------------------------------------+

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:

[root@app1 ~]# mysql -h pxc1 -P 3306 -utest -ptest -e "select @@wsrep_node_name, sleep(20)";

This connection seems stalled… no answer, no error…

In the MySQL processlist we can see:

pxc1 mysql> show full processlist;
+-----+----------------------+-----------------+------+---------+------+--------------------+-----------------------+-----------+---------------+
| Id  | User                 | Host            | db   | Command | Time | State              | Info                  | Rows_sent | Rows_examined |
+-----+----------------------+-----------------+------+---------+------+--------------------+-----------------------+-----------+---------------+
|   1 | system user          |                 | NULL | Sleep   | 1922 | NULL               | NULL                  |         0 |             0 |
|   2 | system user          |                 | NULL | Sleep   | 1922 | wsrep aborter idle | NULL                  |         0 |             0 |
|   9 | root                 | localhost       | NULL | Query   |    0 | init               | show full processlist |         0 |             0 |
| 360 | unauthenticated user | connecting host | NULL | Connect | NULL | login              | NULL                  |         0 |             0 |
+-----+----------------------+-----------------+------+---------+------+--------------------+-----------------------+-----------+---------------+

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:

pxc1 mysql> show full processlist;
+-----+----------------------+-----------------+------+---------+------+--------------------+-----------------------+-----------+---------------+
| Id  | User                 | Host            | db   | Command | Time | State              | Info                  | Rows_sent | Rows_examined |
+-----+----------------------+-----------------+------+---------+------+--------------------+-----------------------+-----------+---------------+
|   1 | system user          |                 | NULL | Sleep   | 1969 | NULL               | NULL                  |         0 |             0 |
|   2 | system user          |                 | NULL | Sleep   | 1969 | wsrep aborter idle | NULL                  |         0 |             0 |
|   9 | root                 | localhost       | NULL | Query   |    0 | init               | show full processlist |         0 |             0 |
| 990 | unauthenticated user | connecting host | NULL | Connect | NULL | login              | NULL                  |         0 |             0 |
| 992 | unauthenticated user | connecting host | NULL | Connect | NULL | login              | NULL                  |         0 |             0 |
| 993 | unauthenticated user | connecting host | NULL | Connect | NULL | login              | NULL                  |         0 |             0 |
| 994 | unauthenticated user | connecting host | NULL | Connect | NULL | login              | NULL                  |         0 |             0 |
| 996 | unauthenticated user | connecting host | NULL | Connect | NULL | login              | NULL                  |         0 |             0 |
| 997 | unauthenticated user | connecting host | NULL | Connect | NULL | login              | NULL                  |         0 |             0 |
+-----+----------------------+-----------------+------+---------+------+--------------------+-----------------------+-----------+---------------+

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):

proxy_protocol_networks = 192.168.56.5

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

bind_address=192.168.56.2 (on pxc1)

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

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 0

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

[root@app1 ~]# mysql -h haproxy -P 3307 -utest -ptest -e "select @@wsrep_node_name, sleep(20)"
+-------------------+-----------+
| @@wsrep_node_name | sleep(20) |
+-------------------+-----------+
| pxc1              |         0 |
+-------------------+-----------+
[root@app1 ~]# mysql -h pxc1 -P 3306 -utest -ptest -e "select @@wsrep_node_name, sleep(20)"
+-------------------+-----------+
| @@wsrep_node_name | sleep(20) |
+-------------------+-----------+
| pxc1              |         0 |
+-------------------+-----------+

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:

[root@pxc2 ~]# mysql -h haproxy -P 3307 -utest -ptest -e "select @@wsrep_node_name, sleep(20)"
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 0

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:

These two bugs were reported while writing this blog post:

• When using proxy protocol, mysql needs to bind to an address instead of 0.0.0.0
• proxy-protocol doesn’t take in consideration connect_timeout, possible DOS

The post Proxy Protocol and Percona XtraDB Cluster: A Quick Guide appeared first on MySQL Performance Blog.