Before the implementation of WL#11652 in MySQL 8.0.13 , the MySQL server listened to incoming connection requests either on one or all configured network interfaces.

However, in case the underlying platform supports several network interfaces it might be useful to allocate more than one network interface for specific use, and therefore allow specifying bind-address for several network interfaces. One use case for this feature is to split network traffic on a MySQL server for specific purposes like:

1. Replication
2. Backup
3. Monitoring (Users)
4. Business flow (Users)

Note that this also enable you to *not* use some interface for MySQL too.

How to configure

MySQL Server 8.0.13  supports configuring listening to more than one specific interface. So if you have a server with addresses 192.168.1.1 and 10.0.0.1, you specify the binding like: --bind-address=192.168.1.1,10.0.0.1

Note that the port number used for accepting incoming TCP connections is the same for every IP address listed in the –bind-address option. So the implementation of  WL#11652 does not change the way the port number is specified.

Implementation

To support listening of incoming connection requests on several interfaces specified by a user the following modifications were done:

• The command line option –bind-address was extended to support  a comma separated list of IP addresses/host names to bind.
• For every IP address specified by a user or IP address resulting from host name resolution a separate TCP socket is created and added to a list of sockets that listen for incoming TCP connections.
  • All sockets are set up for listening with either poll() or select() API functions.
  • Connection events on any of these sockets are handled in the usual way (i.e. as  performed by a server listening on a single IP address).
• Extra checking of the –bind-address option value was added to exclude specifying       wildcard IP address values as part of a list of IP addresses.   Wildcard address values here means one of the following string values: '*', '::' and '0.0.0.0'

This limitation was introduced intentionally in the worklog’s requirements since there is no sense in specifying both wildcard IP address and specific IP address together: the former address covers the latter one anyway.

• The new error code ER_INVALID_VALUE_OF_BIND_ADDRESSES was introduced to report an error in parsing a value of the –bind-address option. E.g. this error emitted when encountering a wildcard IP address specified in a comma separated list of values for the –bind-address option.

The MySQL 8.0.13 server performs the following steps when handling multiple IP addresses specified by the –bind-address option:

•  Parse value passed to the –bind-address option
  • Report the error ER_INVALID_VALUE_OF_BIND_ADDRESSES in case a string value has the wrong format, parsing of a string results in a syntax error or a wildcard address is specified as one of the values in a comma separated list of addresses.
• A TCP socket is created for every IP address specified by the –bind-address option.  The new created socket is bound with a TCP port number specified by the –port option.
  • Every created socket is set up as a server socket listening for incoming TCP  connections by invoking the listen() API function.
• Depending on the platform where a server runs, either poll() or select() is called to wait for incoming TCP connections.
  • Every ready server socket is handled in the usual way – the accept() function is invoked to get a connected socket for an accepted TCP connection. The connected socket is set up to listen for incoming IO events by calling either poll() or select() API functions.

Note that a side effect of introducing support for multiple addresses listening for incoming connections is that every created server socket counts against the limit specified by the –open-files-limit option. So theoretically, if too many interfaces were specified by the –bind-address option it could result in the error “Too many open files” if the MySQL server configuration had already opened a large number of files. We do not consider this a major problem many users will face.

Limitations

The scope of this work is limited to the core MySQL server. Neither the Xplugin nor Group Replication were modified to allow for listening and accepting incoming connections on multiple interfaces.

Thanks for using MySQL!