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?

galoget
  • 235
  • 1
  • 9
ozahorulia
  • 282
  • 1
  • 2
  • 7
  • Did you bounce mysql? What's the output of netstat -lntp ? – Linuxx Jun 05 '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` – ozahorulia Jun 05 '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 05 '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. – ozahorulia Jun 05 '16 at 21:51
  • Wait until you try PHP v7. I had to revert to PHP 5.6... it broke my PHPBB... – Linuxx Jun 05 '16 at 21:52
  • @Linuxx what exactly didn't work for you? AFAIK, my colleagues has their procjets with symfony3+PHP7+mysql5.7, and they work fine. – ozahorulia Jun 05 '16 at 21:54
  • Some deprecated functions were removed. If you were using some older functions that were depreciated in 5.6, they will be gone in 7. Depending on how you had logging setup you would have seen info messages in 5.6 – Linuxx Jun 05 '16 at 21:57
  • 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 – Chepech Feb 24 '17 at 15:09
  • i've removed mysql 5.7 on my ubuntu 16, reinstalled , and now mysql not obey my config file /etc/mysql/conf.d/mysql.cnf , with sql_mode="TRADITIONAL,NO_AUTO_CREATE_USER" value !!, the first time before remove and reinstall, was working – stackdave May 01 '17 at 13:46

1 Answers1

43

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.

Linuxx
  • 1,189
  • 8
  • 7
  • 1
    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 07 '16 at 23:22
  • @kenn: add your config under mysql.conf.d/ instead and name it "x-something" – Bell Apr 06 '17 at 01:14
  • 4
    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 07 '17 at 04: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 03 '21 at 09:45
  • More rant. I agree: ridiculous. One of main points of adding `conf.d/` folder structures (or, in this case `mysql.conf.d/`) to your code and installer is to allow end-user to include their own customization/file(s) and not edit/modify ANY installed files. Normally, you create your own. Just not in mysql case I guess? Any `.conf` (or `.cnf` in this case) read in AFTER the main `/etc/mysql/mysql.conf.d/mysqld.cnf` should override any previous directives. Guess it got lost on them because it certainly doesn't work for me. I wonder if /root/.my.cnf would overrride /etc/mysql/mysql.conf.d? smh – B. Shea Oct 01 '21 at 21:33
  • 1
    For mariadb it is currently `/etc/mysql/mariadb.conf.d/50-server.cnf` when using their official package (instead of the distro) – Mattisdada Oct 13 '21 at 23:11