313

Is there a secret way to bind MySQL to more than one IP address?

As far as I can see the bind-address parameter in the my.cnf does not support more than one IP and you can't have it more than once.

2

8 Answers 8

302

No, there isn't (I just checked 1 hour ago). You can comment the bind-address in my.cnf:

Note: « 1 hour ago » is now more than 15 years ago.

#skip-networking
#bind-address                   = 127.0.0.1

If you want only 2 IPs, you will then have to use a firewall.

For MySql version 8.0.13 and above, and MariaDB version 10.11 and above, you can specify a list of comma-separated IP addresses.

bind-address = 10.0.0.1,10.0.1.1,10.0.2.1

Relevant MySql documentation.

Relevant MariaDB documentation.

Thank you @KurtFitzner for the MariaDB info.

Remember to restart your MySQL instance after changing the config file.

13
81

Binding to 127.0.0.x won't make it available to all the devices, it will make it available locally only. If you wish to make it available to all the interfaces, you should use 0.0.0.0. If you wish to access it from more than one, but less than all the interfaces, you should bind to 0.0.0.0 and firewall off the interfaces you don't want to be accessed through.

Also, as a second layer of security, you should make sure that all your MySQL users have host field set to something other than % (ie any host).

6
  • 1
    Unless you have a broken network stack you cannot bind a TCP port to address 0.0.0.0. May 6, 2010 at 22:49
  • 30
    You can bind to 0.0.0.0. You just can't route to it. If you're on Linux (or even on Windows, just install netcat for windows) try: in one terminal: nc -l 0.0.0.0 4321 and in a second terminal: telnet <IP of any interface your computer has> 4321 And it will connect to it. May 18, 2010 at 16:49
  • As I said, unless you have a broken network stack... Jun 25, 2010 at 2:58
  • 2
    @JohnGardeniers Is that why it's in the linux ip(7) man page defined under special addresses: INADDR_ANY (0.0.0.0) means any address for binding;?
    – ebyrob
    Jul 24, 2013 at 17:06
  • 3
    On Debian, create a file /etc/mysql/conf.d/bindaddress.cnf file with content [mysqld] \n bind-address = 0.0.0.0 Aug 16, 2013 at 12:07
52

You can't bind to more than one IP address, but you can bind to all available IP addresses instead. If so, just use 0.0.0.0 for a binding address in your MySQL configuration file (e.g. /etc/mysql/my.cnf) as follows:

bind-address    = 0.0.0.0

If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces.

Furthermore if the address is ::, the server accepts TCP/IP connections on all server host IPv4 and IPv6 interfaces. Use this address to permit both IPv4 and IPv6 connections on all server interfaces.

Or you can simply comment out bind-address= altogether, so it will bind to all addresses. But make sure that you don't have skip-networking enabled in your my.cnf if you want to allow remote connections as well (Read more: MySQL: Allow both remote AND local connections).

After changing the binding address, don't forget to restart your MySQL server by:

sudo service mysql restart

Eventually you can consider to run multiple instances of MySQL on a single machine (different ports) with Master/Slave replication. Replication enables data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves).

Read more:

1
  • This works fine. Just remember to check if all users are allowed to connect from that 2nd, 3rd, whatever IP address
    – gies0r
    Aug 9, 2019 at 14:15
19

No, you cannot. The page you link to clearly states:

The IP address to bind to. Only one address can be selected. If this option is specified multiple times, the last address given is used.

If no address or 0.0.0.0 is specified, the server listens on all interfaces.

18

As others have answered, there isn't a way yet to selectively bind to more than one interface.

Linux has some TCP tools which make it possible. In this setup, you'd configure mysql to listen on 127.0.0.1 and then use redir to expose it on arbitrary interfaces.

I've been using this to help a virtual box guest see mysql installed on the host machine.

redir --laddr=192.168.33.1 --lport=3306 --caddr=127.0.0.1 --cport=3306 &
2
  • 1
    Note MySQL >= 8.0.13 allows binding to more than one interface.
    – txyoji
    Jun 21, 2019 at 20:09
  • 1
    rinetd would be a similar solution ... Apr 6, 2021 at 22:03
17

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 a single value as just described, or a list of comma-separated values. When the option names a list of multiple values, each value must specify a single non-wildcard IP address or host name; none can specify a wildcard address format (*, 0.0.0.0, or ::).

Source: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_bind_address

1
  • 1
    works for me, in my.cnf: bind-address = 127.0.0.1,192.168.1.3
    – obe
    Aug 25, 2020 at 11:38
6

I think your question is related to this bug http://bugs.mysql.com/bug.php?id=14979 The bug report suggest some workaround.

1
  • A MySQL feature request dating back from 2005!
    – Lætitia
    Apr 24, 2014 at 14:02
5

In my.cnf change (usually /etc/mysql/my.cnf on Linux or for windows check this answer.

bind-address                   = 127.0.0.1

to

bind-address                   = 0.0.0.0

Then restart mysql (on Ubuntu service mysql restart) on windows usually service restart thru Win+R services.msc

0.0.0.0 tells it to bind to all available IP's with port also given in my.cnf

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .