Recently, we got access to an Ubuntu 16.04 LTS server that had MySQL server installed on it but was not accessible to our external servers.
The service was accessible when testing from localhost but it was not when testing from any other machine.
Executing nmap from another machine would return the value 3306/tcp closed mysql conn-refused as below.
[bytefreaks@fedora ~]$ nmap -vv -p 3306 192.168.10.11
Starting Nmap 7.40 ( https://nmap.org ) at 2017-03-06 17:21 EET
Initiating Ping Scan at 17:21
Scanning 192.168.10.11 [2 ports]
Completed Ping Scan at 17:21, 0.06s elapsed (1 total hosts)
Initiating Parallel DNS resolution of 1 host. at 17:21
Completed Parallel DNS resolution of 1 host. at 17:21, 0.00s elapsed
Initiating Connect Scan at 17:21
Scanning 192.168.10.11 [1 port]
Completed Connect Scan at 17:21, 0.06s elapsed (1 total ports)
Nmap scan report for 46.101.137.70
Host is up, received syn-ack (0.061s latency).
Scanned at 2017-03-06 17:21:31 EET for 1s
PORT STATE SERVICE REASON
3306/tcp closed mysql conn-refused
Read data files from: /usr/bin/../share/nmap
Nmap done: 1 IP address (1 host up) scanned in 0.16 seconds
The problem was with the default configuration of mysqld that is found in the file /etc/mysql/mysql.conf.d/mysqld.cnf.
At line 41 we got the following snippet:
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
What the line bind-address = 127.0.0.1 says is that, the service will only listen on localhost.
At this stage there are two solutions that you can apply using your favorite text editor (e.g. sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf):
Solution A:
Completely remove the line bind-address = 127.0.0.1 or comment it out by adding a # in front of it as follows #bind-address = 127.0.0.1.
Solution B:
Replace 127.0.0.1 with the IP that you want mysql service to be available to. In our case the line became bind-address = 192.168.10.11.
After you are done with the change, you need to restart the service for the change to take place:
bytefreaks@OSUbuntu:~$ sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.
From an external machine you can verify that the configuration was applied correctly using nmap as below:
[bytefreaks@fedora ~]$ nmap -vv -p 3306 192.168.10.11
Starting Nmap 7.40 ( https://nmap.org ) at 2017-03-06 17:24 EET
Initiating Ping Scan at 17:24
Scanning 192.168.10.11 [2 ports]
Completed Ping Scan at 17:24, 0.06s elapsed (1 total hosts)
Initiating Parallel DNS resolution of 1 host. at 17:24
Completed Parallel DNS resolution of 1 host. at 17:24, 0.00s elapsed
Initiating Connect Scan at 17:24
Scanning 192.168.10.11 [1 port]
Discovered open port 3306/tcp on 46.101.137.70
Completed Connect Scan at 17:24, 0.06s elapsed (1 total ports)
Nmap scan report for 46.101.137.70
Host is up, received syn-ack (0.061s latency).
Scanned at 2017-03-06 17:24:30 EET for 0s
PORT STATE SERVICE REASON
3306/tcp open mysql syn-ack
Read data files from: /usr/bin/../share/nmap
Nmap done: 1 IP address (1 host up) scanned in 0.16 seconds
You should get the value 3306/tcp open mysql syn-ack.
