MySQL Server: Bind To Multiple IP Address

I have MySQL database system running on FreeBSD. The server has multiple ip address. The mysqld used by many websites hosted on two other CentOS based servers. I would like to bind MySQL sever running on Unix / Linux server to more than one IP address such as 202.54.1.2, 202.54.1.10, and 202.54.1.15. How can I bind mysqld to multiple ips?


Short answer - No. You can not bind to multiple IP address under MySQL database server either running on FreeBSD or Linux/Unix like operating systems.

Long answer

From the mysql documentation:

The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces. The default address is 0.0.0.0. To specify an address explicitly, use the --bind-address=addr option at server startup, where addr is an IPv4 address or a host name. If addr is a host name, the server resolves the name to an IPv4 address and binds to that address. The server treats different types of addresses as follows:

1. If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces.
2. If the address is a "regular" IPv4 address (such as 127.0.0.1), the server accepts TCP/IP connections only for that particular IPv4 address.

Using a firewall

Consider the following setup:

+----------------------------------------------------------+
|                   +==========Server_IP1 202.54.1.2       |
|  +--------+       |                                      |
|  | mysqld +-------+==========Server_IP2 202.54.1.10      +-------> LAN/WAN ---->
|  +--------+       |                                      |
|  Server_IP0       +==========Server_IP3 with 202.54.1.15 |
|  202.54.1.1                                              |
+----------------------------------------------------------+
           UNIX/Linux Box called db1.cyberciti.biz

Where,

1. Mysqld server will bind to all IPs on all interfaces.
2. Use the firewall to control access to the mysqld running on this server. Make sure you only allow connections to 202.54.1.{2,10,15} tcp port # 3306

Configuration

You can set bind-address directive in my.cnf. Edit /etc/my.cnf or /usr/local/etc/my.cnf, run:
# vi /usr/local/etc/my.cnf
OR
# vi /etc/my.cnf
Set the address to 0.0.0.0:

bind-address    = 0.0.0.0

Make sure you delete the following line or comment out the following line:

#skip-networking

Save and close the file. Next setup the firewall and allows connection from or to select IPs only.

Linux iptables rules

Block all incomming connections to mysqld TCP port # 3306 except for 202.54.1.2, 202.54.1.10, and 202.54.1.15:

 ## Block all connections to 3306 ##
/sbin/iptables -A INPUT -p tcp --dport 3306 -j DROP
### Now, allow 202.54.1.2, 202.54.1.10, and 202.54.1.15 ###
/sbin/iptables -A INPUT -p tcp -d 202.54.1.2 --dport 3306 -j ACCEPT
/sbin/iptables -A INPUT -p tcp -d 202.54.1.10 --dport 3306 -j ACCEPT
/sbin/iptables -A INPUT -p tcp -d 202.54.1.15 --dport 3306 -j ACCEPT
 

OR

## Block all connections to 3306 except for three ips##
/sbin/iptables -A INPUT -p tcp --dport 3306 ! -d 202.54.1.2 -j DROP
/sbin/iptables -A INPUT -p tcp --dport 3306 ! -d 202.54.1.10 -j DROP
/sbin/iptables -A INPUT -p tcp --dport 3306 ! -d 202.54.1.15 -j DROP
 

Save the settings:
# service iptables save
Type the following command to verify new settings:

 
echo -e "target     prot opt source               destination\n$(iptables -L INPUT -n | grep 3306)"
 

Sample outputs:

pf firewall rules

Use the following syntax in your /etc/pf.conf:

 ## our interface ##
ext_if="vr0"
 
## do not block mysqld on ##
mysqld_ip="{ !202.54.1.2, !202.54.1.10, !202.54.1.15 }"
 
## Block everything for tcp port number 3306 except $mysqld_ip  ###
block in on $ext_if proto tcp from any to  $mysqld_ip port 3306
 

Load updated firewall rules using the pf.conf file:
# pfctl -f /etc/pf.conf
Show the current ruleset, enter:
# pfctl -sr
# pfctl -sr | grep 3306
Sample outputs:

Add/update mysql sever user and permissions settings

Let us assume that you are always making connection from remote IP called 202.54.1.100 for existing mysqld database called foo for user bar, To grant access to this IP address type the following command at mysql server 202.54.1.1:
mysql> update db set Host='202.54.1.100' where Db='foo';
mysql> update user set Host='202.54.1.100' where user='bar';

Restart / reload the mysql server

Type the following command to restart the mysqld. If you are on Red Hat Enterprise Linux and friends:
# service mysqld restart
Debian Linux and friends use the following command:
# service mysql restart
FreeBSD unix user type the following command to restart the mysql server:
# /usr/local/etc/rc.d/mysql-server stop && /usr/local/etc/rc.d/mysql-server start

How do I test my settings?

Type the following command from client (202.54.1.100) to connect to 202.54.1.2:3306, enter:
[user@202.54.1.100 ]$ mysql -u foo -h 202.54.1.2 -P 3306 -p bar

• Tweet
• 

If you would like to be kept up to date with our posts, you can follow us on Twitter, Facebook, Google+, or even by subscribing to our RSS Feed.