16

I have always successfully set up a remote connection for MySQL 5.5.

Today I installed a new server with Ubuntu 16.04 and MySQL 5.7. But for some reasons, I can't make this MySQL installation listen to other hosts but 127.0.0.1.

Here is my /etc/mysql/conf.d/mysql.cnf:

[mysqld]
bind-address = 0.0.0.0

I couldn't connect to this MySQL server from a remote host, and when I checked my netstat, I realized that MySQL listens to connections from localhost only.

lsof -Pni :3306 output is:

COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  5302 mysql   25u  IPv4  37280      0t0  TCP 127.0.0.1:3306 (LISTEN)

What is the problem?

  • Did you bounce mysql? What's the output of netstat -lntp ? – Linuxx Jun 5 '16 at 21:34
  • @Linuxx I even restarted the whole machine. tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 13050/mysqld – Hast Jun 5 '16 at 21:36
  • I assume you added that line in that file. Remove that line and edit the file /etc/mysql/mysql.conf.d/mysqld.cnf and change the parameter there. – Linuxx Jun 5 '16 at 21:43
  • 1
    @Linuxx This worked like magic :) I haven't noticed that there's yet another *.conf.d directory. So basically 5.7 keeps [mysql] config in the conf.d directory, whereas [mysqld] configs are kept in the mysql.conf.d. Thank you! Please, make an answer, so I'll accept it. – Hast Jun 5 '16 at 21:51
  • 1
    I've changed bind-address on both /etc/mysql/mysql.conf.d/mysqld.cnf and /etc/mysql/conf.d/mysql.cnf , restarted de server yet I still see "tcp 0 0 127.0.0.1:3306 " when I run <netstat -tulpen – Chepech Feb 24 '17 at 15:09
36

They changed the MySQL package so that the bind parameter is in /etc/mysql/mysql.conf.d/mysqld.cnf now. Please change the bind parameter there and remove anything you placed in /etc/mysql/conf.d/mysql.cnf.

  • This is ridiculous though. The purpose of "conf.d" was to NOT edit the package-provided defaults and override by your own config. But as now "mysql.conf.d" is overriding ours, there's no reliable way to make sure ours come at last unless we edit "mysql.conf.d/mysqld.cnf". – kenn Nov 7 '16 at 23:22
  • @kenn: add your config under mysql.conf.d/ instead and name it "x-something" – Bell Apr 6 '17 at 1:14
  • 3
    Sure, but what's the point of "conf.d" then? Isn't "mysql.conf.d" a package-provided defaults, which are supposed to be overwritten by "conf.d"? I think the load order of "conf.d" and "mysql.conf.d" should be reversed in "/etc/mysql/my.cnf" at the MySQL package level. – kenn Apr 7 '17 at 4:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.