How to get client’s IP address when using MySQL Router ?

When you connect to a server (or cluster) using a TCP proxy level 7, also referred to as an application-level or Layer 7 proxy (highest level of the OSI model), the application doesn’t connect directly to the back-end server(s). The proxy usually understands the protocol used and can eventually take some decisions or even change the request.

The problem when using such proxy (like HA Proxy, ProxySQL and MySQL Router) is that the server doesn’t really know from where the client is connecting. The server sees the IP address of the proxy/router as the source IP of the client.

HA Proxy initially designed the Proxy Protocol, a simple protocol that allows a TCP connection to transport proxy-related information between the client, the proxy server and the destination server. The main purpose of the Proxy Protocol is then to preserve the client’s original IP address (with some others metadata). See HA Proxy’s manual.

The back-end application must also be aware of this protocol and understand it to be able to benefit from it.

However, there are potential security issues associated with the Proxy Protocol (like spoofing, information leakage, Denial of Service using malformed or very large Proxy Protocol headers, …).

For these reasons, as you know security is very important for us at Oracle, MySQL doesn’t support the Proxy Protocol.

Does this mean that if you use MySQL Router you have no way of knowing the client’s IP address?

Of course not, if you use a secure connection (SSL client), the MySQL Router adds attributes to the handshake and these attributes are available on the server. See Connections Attributes.

With this simple query, it is now possible to list the connections and originating IP addresses of clients when they connect via MySQL Router:

select program_name, last_statement, user,attr_value 'client ip'
  from performance_schema.session_account_connect_attrs 
  join sys.processlist on conn_id=processlist_id 
 where attr_name='_client_ip' ;
+--------------+----------------------------------+-------------------------+-----------+
| program_name | last_statement                   | user                    | client ip |
+--------------+----------------------------------+-------------------------+-----------+
| mysqlsh      | NULL                             | root@mysql-router-shell | 127.0.0.1 |
| mysql        | select @@version_comment limit 1 | root@mysql-router-shell | 10.0.0.76 |
+--------------+----------------------------------+-------------------------+-----------+

MySQL Server and MySQL Router don’t support Proxy Protocol but implement the connection attributes that provides even more information.

As usual, enjoy MySQL !

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

5 Comments

  1. Hi Frederic;

    Thanks for your article, can you confirm from which MySQL version, it will populate with data, and do we need to enable anything before running this query?

  2. Hi Fred,

    we created user as ‘user’@’10.0.0.1’ and tried to connect mysql innodb cluster db using mysql router. We are getting access denied error for user ‘user’@’routerproxy’ error. We could see that the mysql router used its own hostname as the client ip which in turn made the connection failed. Since the purpose is to restrict the unwanted client authentication when using ‘%’. How we can ensure client can authenticate as per the user id?

  3. Hi Fred,

    we created user as ‘user’@’10.0.0.1’ and tried to connect mysql innodb cluster db using mysql router. We are getting access denied error for user ‘user’@’routerproxy’ error. Could see that the mysql router used its own hostname envelop and send the connection request to database available instance which in turn made the connection failed. Since the purpose is to restrict the unwanted client authentication when using ‘%’. How we can ensure client can authenticate as per the user id?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.