18

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?

9
  • 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
42

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.

5
  • 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
  • 1
    @kenn, it seems conf.d contains global options while mariadb.conf.d contains MariaDB-only options. I understand this to allow MariaDB and MySQL running on the same server with some shared options (conf.d) and specific options (mariadb.conf.d and, i guess, mysql.conf.d).
    – sanzante
    Dec 31 '20 at 13:05
  • @sanzante ah, that makes sense! If only the intention was clearly documented in the comment...
    – kenn
    Jan 3 at 9:45

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.