MySQL/MariaDB Server: Bind To Multiple IP Address

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

Tutorial details
Difficulty Advanced (rss)
Root privileges Yes
Requirements MySQLDpf or iptables
Time 15m
[/donotprint] 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 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, the server accepts TCP/IP connections on all server host IPv4 interfaces.
  2. If the address is a “regular” IPv4 address (such as, the server accepts TCP/IP connections only for that particular IPv4 address.

Using a firewall

Consider the following setup:

|                   +==========Server_IP1       |
|  +--------+       |                                      |
|  | mysqld +-------+==========Server_IP2      +-------> LAN/WAN ---->
|  +--------+       |                                      |
|  Server_IP0       +==========Server_IP3 with |
|                                              |
           UNIX/Linux Box called


  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


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
# vi /etc/my.cnf
Set the address to

bind-address    =

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


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,, and

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


## Block all connections to 3306 except for three ips##
/sbin/iptables -A INPUT -p tcp --dport 3306 ! -s -j DROP
/sbin/iptables -A INPUT -p tcp --dport 3306 ! -s -j DROP
/sbin/iptables -A INPUT -p tcp --dport 3306 ! -s -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:

Fig.01: MySQL Server: Iptables negate a range in Iptables

pf firewall rules

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

 ## our interface ##
## do not block mysqld on ##
mysqld_ip="{ !, !, ! }"
## 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:

Fig.02: BSD PF Firewall Block All IPs Except A few IPS to MySQL Port

Add/update mysql sever user and permissions settings

Let us assume that you are always making connection from the remote IP called for existing mysqld database called foo for user bar, To grant access to this IP address type the following command at mysql server
mysql> update db set Host='' where Db='foo';
mysql> update user set Host='' 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 ( to connect to, enter:
[nixcraft@ ]$ mysql -u foo -h -P 3306 -p bar

  1. -u foo : The MySQL user name to use when connecting to the server.
  2. -h : Connect to the MySQL server on the given host/ip address.
  3. -P 3306 : The TCP/IP port number to use for the connection.
  4. bar : The database name.

🐧 Get the latest tutorials on Linux, Open Source & DevOps via RSS feed or Weekly email newsletter.

🐧 8 comments so far... add one

CategoryList of Unix and Linux commands
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04
8 comments… add one
  • John Dec 7, 2012 @ 11:50

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

    This will not work. The first rule will block all incoming connections. The last three rules will never be checked. More specific rules must be put before more generic rules.

    • Tesra Dec 8, 2012 @ 5:16

      +1 this need to be fixed ASAP.

      ### Now, allow,, and ###
      /sbin/iptables -A INPUT -p tcp -d –dport 3306 -j ACCEPT
      /sbin/iptables -A INPUT -p tcp -d –dport 3306 -j ACCEPT
      /sbin/iptables -A INPUT -p tcp -d –dport 3306 -j ACCEPT
      ## Block all connections to 3306 ##
      /sbin/iptables -A INPUT -p tcp –dport 3306 -j DROP
    • David Gillies Oct 25, 2013 @ 14:58

      Yes, the more specific rules must come first. In addition, the -d flag to iptables indicates the IP address the incoming connection is trying to bind to (i.e. the [d]estination). If you want to deny all incoming connections except those from a given IP address (surely the most common situation) then this must be specified using the -s (i.e. [s]source) flag. So a working ruleset looks more like this:

      iptables -A INPUT -p tcp -s source IP address –dport 3306 -j ACCEPT
      iptables -A INPUT -p tcp –dport 3306 -j DROP

  • John Dec 8, 2012 @ 5:35

    MySQL server can bind to one IP address or ALL IP addresses on a server. It cannot bind to a specific list of IPs. This article does not state this clearly. It says MySQL cannot bind to multiple IPs. Then it talks about binding to all IPs. Aren’t all IPs multiple?

  • astucee Nov 3, 2014 @ 16:16

    Can you have multiple values in bind-address (my.cnf)

  • Mathieu Feb 17, 2015 @ 15:39
  • André Mar 30, 2016 @ 20:20

    my.cnf is to configure Mariadb (or Mysql), which only accepts one IP reference. (bind-address = x.x.x.x) If is (or missing) all addresses are accepted.
    iptables relates to the firewall, which blocks ports. Specific ports can be unblocked, as shown above. The blockage/unblockage is treated in order. So ports to unblock are listed/processed first. Other ports then are blocked with a single statement, last.

  • Wellington Torrejais da Silva May 19, 2016 @ 13:12


Leave a Reply

Your email address will not be published.

Use HTML <pre>...</pre> for code samples. Problem posting comment? Email me @