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:
1 2 3 4 5 6 7 8 | 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.
1 2 | [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:
1 2 3 4 5 6 7 8 9 10 | 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:
1 | proxy_protocol_networks = * |
Then we need to also modify HAProxy’s config to be like this:
1 2 3 4 5 6 7 8 | 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:
1 2 3 4 5 6 | +------+------+------+-------------------------------------+ | 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:
1 | [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:
1 2 3 4 5 6 7 8 9 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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):
1 | 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:
1 | bind_address=192.168.56.2 (on pxc1) |
This is the error you would face if you don’t bind MySQL:
1 | 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:
1 2 3 4 5 6 7 8 9 10 11 12 | [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:
1 2 | [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:
| 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:
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.
set proxy_protocol_networks to an IPv4-mapped address(::ffff:a.b.c.d) make it work properly without bind_address.