How to bind MySQL server to more than one IP address?

Question

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.

As indicated in Tadej's answer, it has become possible in the meantime, since version 8.0.13. See also "The –bind-address option now supports multiple addresses". However, it is still not as flexible as in PostgreSQL, and is still not supported in MariaDB v. 10. — mivk, Aug 09 '20 at 20:15
MariaDB got support for this as well in 10.11 https://mariadb.com/kb/en/server-system-variables/#bind_address — Skgland, Sep 11 '23 at 14:08

Christian Lescuyer · Accepted Answer · 2023-10-01T09:14:43.883

303

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.

edited Oct 01 '23 at 09:14

answered Sep 02 '08 at 16:08

Christian Lescuyer

3,204

36

Correct. Binding is limited to either 0, 1, or all IP addresses on the server. – Joe Sep 14 '10 at 21:01
8

Note, however, than you can publish and serve both a local unix socket and a network socket by specifying both the socket and bind-address options. – danorton Mar 30 '12 at 17:04
5

still true as of today. – Dennis Nolte Aug 11 '14 at 13:07
37

That is ridiculous. – Phillipp Dec 11 '15 at 15:01
On my installation I can use mysql -h localhost etc... and mysql -h <IP address> etc... to connect to the server (assuming mysql.users table is up to date http://stackoverflow.com/questions/36603973/mysql-not-honouring-bind-address ). This seems at odds with the this answer, and the other answers and comments. – AJP Apr 13 '16 at 16:56
@AJP the question was about the server listening to multiple IP addresses. You can connect to any address you like with the client. – Christian Lescuyer Apr 14 '16 at 12:23
1

@ChristianLescuyer not true. If the server is set to bind-address 127.0.0.1 then the client can connect to 127.0.0.1 or localhost. If the server is set to bind-address a different interface then the client can not connect to 127.0.0.1 but ** can still ** connect to the server through localhost. Hence my comment. Apologies if I have misunderstood your comment. – AJP Apr 14 '16 at 14:51
@AJP I suspect that in your second case, the client connects to localhost via the socket as danorton said in his comment. I did not check this, though. – Christian Lescuyer Apr 14 '16 at 19:23
1

@ChristianLescuyer ah interesting. Thanks :) netstat does show a socket connection on my machine: unix 2 [ ACC ] STREAM LISTENING 175927 31481/mysqld /var/run/mysqld/mysqld.sock I wonder how you'd figure out if specifying -h localhost was using the socket? [not expecting an answer]. – AJP Apr 15 '16 at 11:46
2

@AJP Mysql treats ‘localhost’ and ‘127.0.0.1’ differently in the privilege system. Confirmed here : A Unix socket file is used if you do not specify a host name or if you specify the special host name localhost. – Christian Lescuyer Apr 15 '16 at 18:02
Argh. Was trying to open local (bogon) ranges for monitors only. It's true you can use a firewall, but you gotta drop it correctly to pass PCI without headaches, then still prob have to submit cases against the false positives. PITA IMO. Do some experimentation with the type of drop if you are PCI and make these changes. – dhaupin May 23 '16 at 14:38
1

as pointed out by this newer answer: https://serverfault.com/a/941618/281422 - as of 8.0.13, it's possible to specify a comma-delimited list of IPs in bind-address. – obe Aug 25 '20 at 11:40
1

It would be useful to note, though technically out of scope of the original question, that MariaDB added the comma-separated multi-ip setting as of version 10.11. It would be useful to add that here, since they make no mention on their documentation what version of MariaDB adds the feature. – Kurt Fitzner Sep 28 '23 at 01:21

score 81 · Answer 2 · answered Sep 02 '08 at 16:29

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).

answered Sep 02 '08 at 16:29

Grey Panther

988

1

Unless you have a broken network stack you cannot bind a TCP port to address 0.0.0.0. – John Gardeniers May 06 '10 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 4321

And it will connect to it.
– Grey Panther May 18 '10 at 16:49
As I said, unless you have a broken network stack... – John Gardeniers Jun 25 '10 at 02: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 '13 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 – Yves Martin Aug 16 '13 at 12:07
Works on FreeBSD without issues: grep bind-add /usr/local/etc/my.cnf bind-address = 0.0.0.0 – Kevin_Kinsey Aug 31 '18 at 20:50

score 53 · Answer 3 · edited Apr 13 '17 at 12:14

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:

5.3 Running Multiple MySQL Instances on One Machine at MySQL.com
Chapter 17 Replication at MySQL.com

This works fine. Just remember to check if all users are allowed to connect from that 2nd, 3rd, whatever IP address — gies0r, Aug 09 '19 at 14:15

score 19 · Answer 4 · edited Jun 11 '20 at 10:02

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.

edited Jun 11 '20 at 10:02

Community

1

answered Sep 14 '10 at 20:45

Andreas

315

score 18 · Answer 5 · answered Feb 24 '17 at 20:23

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 &

answered Feb 24 '17 at 20:23

txyoji

385

1

Note MySQL >= 8.0.13 allows binding to more than one interface. – txyoji Jun 21 '19 at 20:09
1

rinetd would be a similar solution ... – 0xC0000022L Apr 06 '21 at 22:03

score 17 · Answer 6 · edited Aug 09 '20 at 20:09

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

works for me, in my.cnf: bind-address = 127.0.0.1,192.168.1.3 — obe, Aug 25 '20 at 11:38

score 6 · Answer 7 · answered Sep 02 '08 at 16:14

6

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

answered Sep 02 '08 at 16:14

A MySQL feature request dating back from 2005! – Lætitia Apr 24 '14 at 14:02

score 5 · Answer 8 · answered Sep 19 '17 at 18:34

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

How to bind MySQL server to more than one IP address?

8 Answers8

Linked