0

I am trying to allow users on my local subnet to log into my MySQL server.

The server is running Ubuntu 23.10 and MySql version 0.0.35-0ubuntu0.213.10.1

The Clients are 2 iMacs with macos Sonoma 14.2.1 and a Windows 11 PC.

The ubuntu server is on my local subnet (192.168.1.0) but is also available via a public fixed IP address. All the clients are using MYSQL Workbench 8.0

I have blocked public access to the server (apart from HTTP) via my firewall. But my issue is giving access to to MySQL server to my local clients.

I have amended /etc/mysql/mysql.conf.d/mysqld.cnf and changed bind-address = 127.00.0.1 to 0.0.0.0 making Mysqld listen on any subnet. But this does seem very secure but it does work.

I have tried using bind-address = 192.168.1.xxx, 192.168.1.yyy, 192.1658.1.zzz but when I try to restart the MySQL service I get the error message.

 **Job for mysql.service failed because the control process exited with error code.**
 **See "systemctl status mysql.service" and "journalctl -xeu mysql.service" for details** With an exit code of 1.

Also I have tried bind-address = 192.168.1.0 but this gives me the same error message. I would most grateful for any advice on how to limit the bind-address to just my local subnet or even just my three clients

James Martin
  • 1

2 Answers2

2

Bind doesn't work the way you think it does.

It says which network interface to bind to. It has to be a network interface configured on the machine.

0.0.0.0 is a special case, essentially saying all available interfaces.

It does not restrict traffic in any way, any IP can contact it on the interfaces it is listening. If you want to limit traffic, use a firewall.

Thus in your scenario bind to 0.0.0.0 will be fine; it will be no different from binding to the specific interface if you only have one.

vidarlo
  • 11,723
0

I have a similar situation, on which I added an HAproxy "buffer". I can't say if this will work for you, but it should work.

I added HAproxy on the same host as the mysqld, and created a listener in HAproxy on port 33061 (instead of the default 3306)

ss -antlp;
#LISTEN    0         128          <PUBLIC_IP>:33061            0.0.0.0:*       users:(("haproxy",pid=32645,fd=12))

The config is like this...

listen mysql
  bind        <PUBLIC_IP>:33061
  mode        tcp
  option      tcplog
  acl         mysql_ip_OK src -f /etc/haproxy/whitelist.IPs
  tcp-request connection reject if !mysql_ip_OK
  server      mysql1 127.0.0.1:3306

and the "filter" is like this...

cat <<EOF> /etc/haproxy/whitelist.IPs`
192.168.1.xxx
192.168.1.yyy
192.168.1.zzz
EOF

Any connection attempt would theoretically be blocked if it isn't specifically listed in /etc/haproxy/whitelist.IPs The clients then connect to <PUBLIC_IP>:33061 instead of <PUBLIC_IP>:3306 and you gain additional security

Trent Murgatroyd
  • 64
  • 1
  • 6