Ubuntu – Can’t connect to MySQL Server 5.7 through TCP connection in Ubuntu Server 17.10 – Only through local socket

MySQLUbuntu

I know that this question looks duplicate with many others, but before you downvote me, I already read about others related questions but didn't solve my problem. Please take a look about my environment:

$ ps -Af | grep mysqld
mysql      901     1  0 10:11 ?        00:00:00 /usr/sbin/mysqld
ubuntu    3229  2542  0 10:13 pts/0    00:00:00 grep --color=auto mysqld

More output:

# netstat -lnp | grep mysql
tcp        0      0 0.0.0.0:3306
unix  2      [ ACC ]     STREAM     LISTENING     19274    901/mysqld           /var/run/mysqld/mysqld.sock

I think this will be useful too:

$ cat /etc/mysql/mysql.conf.d/mysqld.cnf | grep bind-address
bind-address = 0.0.0.0

There is no skip-networking option active, but even so, I can access the MySQL through local socket connection!

Already added 3306 to ufw allow-list, and even more, tried to connect with ufw disabled.

neither 127.0.0.1, or my lan ip 192.168.1.100 or 0.0.0.0 works, I keep getting the same output error:

ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (97)

Here is the nmap on port 3306:

# nmap -sS -O -p3306 127.0.0.1
PORT     STATE SERVICE
3306/tcp open  mysql

The telnet output:

$ telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to 127.0.0.1.
Escape character is '^]'.
[
5.7.21-0ubuntu0.17.10.1!5GAC"?0)kLQBLmysql_native_passwordConnection closed by foreign host.

A few interesting lines on the error.log:

2018-04-12T13:28:58.588887Z 0 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
2018-04-12T13:28:58.588895Z 0 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2018-04-12T13:28:58.588919Z 0 [Note] Server socket created on IP: '0.0.0.0'.
2018-04-12T13:28:58.593960Z 0 [Note] Event Scheduler: Loaded 0 events
2018-04-12T13:28:58.594091Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.21-0ubuntu0.17.10.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

In my point of view, it should be working fine…
Any help will be welcome!

=)

[Update]

Added the iptables output after been requested.
# iptables -L
Chain INPUT (policy ACCEPT)
target prot opt source destination 

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination         

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination

Best Answer

Comment out the bind-address line. Commenting out that line will force the MySQL server to accept connections regardless of origin. In theory setting 0.0.0.0 should do the same however I personally have had difficulties with that before.

Commenting out the bind address line will mean there is no questions about whether or not the change stuck. Comment how this goes, we’ll find you a solution one way or another

Related Topic