mysql bind-address change from 127.0.0.1 to server's IP Address breaks websites

Question

I'm trying to setup mysql replica. On master when I change bind-address value in my.cnf to server's IP address some of websites break as they are using 127.0.0.1 as database host value.

There are few other sites (WordPress) which work because WP uses localhost as db host value.

It is production server so I don't have liberty to change 127.0.0.1 to localhost on all those sites which break or experiment with changes.

Is there any other way by which I can keep mysql configuration bind-address value to be server's IP and it works with either localhost or 127.0.0.1 specified in the websites' configurations?

This is my /etc/hosts, some values are redacted

# cat /etc/hosts
::1     localhost localhost.localdomain localhost6 localhost6.localdomain6

127.0.0.1       localhost localhost.localdomain localhost4 localhost4.localdomain4
127.0.0.1       localhost ns123456

xx.xx.xx.xx     ns123456.ip-11-22-33.net ns123456

Updates

There is nice discussion on https://stackoverflow.com/questions/19712307/mysql-localhost-127-0-0-1 however it doesn't provide a clear answer.

Answer 1

When binding an IPv4 socket, the BSD socket API (used by most *nix and other OSes) leaves two choices: bind to a single address, or bind to all addresses using the reserved address 0.0.0.0 aka INADDR_ANY.

For your mysqld instance to be both able to be reachable by local clients using 127.0.0.1 (instead of the host's address) and remote replica servers which have to use the host's address, two addresses are needed. Either tell mysqld to bind twice, if the version supports it: one socket for each address, or else bind once to INADDR_ANY which allows all possible accesses.

From the documentation:

If bind_address is specified, its value must satisfy these requirements:

• Prior to MySQL 8.0.13, bind_address accepts a single address value, which may specify a single non-wildcard IP address or host name, or one of the wildcard address formats that permit listening on multiple network interfaces (*, 0.0.0.0, or ::).

• As of MySQL 8.0.13, bind_address accepts either a single value as just described, or a list of comma-separated values. When the variable names a list of multiple values, each value must specify a single non-wildcard IP address (either IPv4 or IPv6) or a host name. Wildcard address formats (*, 0.0.0.0, or ::) are not allowed in a list of values.

• As of MySQL 8.0.22, addresses may include a network namespace specifier.

In case of doubt, just use 0.0.0.0. If the server has more than 1 interface or more than 1 address (in addition to the loopback interface) and you wouldn't want access to all of these interfaces and/or addresses, even then, adequate firewalling should already be in place, or else be put in place, to handle this case.