9

I've gone through a lot of tutorials and questions and I still can't get it to work.

I've been to:

I installed MariaDB on Ubuntu 16.04. Then set up two users, one of which is intended for public use so I can post it here.

The users are added as:

CREATE USER 'anon'@'%' IDENTIFIED BY '';

Does local connections work?

Yes, I can connect as the users via ssh on the server:

mysql -u anon

Did you verify the users were added correctly?

I think so:

MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';
+------+------+
| User | Host |
+------+------+
| anon | %    |
| user | %    |
+------+------+
2 rows in set (0.01 sec)

Have you unblocked the firewall?

One may need to unblock the firewall:

[user]@popfreq:/etc/mysql$ firewall-cmd --add-port=3306/tcp 
The program 'firewall-cmd' is currently not installed. You can install it by typing:
sudo apt install firewalld

Firewall isn't installed.

Did you check the my.cnf file for correct settings?

Incorrect settings in my.cnf ([user]@popfreq:/etc/mysql) can cause it to refuse connections. These are skip-networking and bind-address. My file looks like this:

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

It doesn't have the offending lines at all.

Did you check the other config files?

Yes. They did not have the offending lines either.

Does telnet work?

No.

mint@mint-VirtualBox ~ $ telnet 128.199.203.208 3306
Trying 128.199.203.208...
telnet: Unable to connect to remote host: Connection refused

Not sure what this means or how to fix.

What interface is the server using?

Local only it seems:

[user]@popfreq:/etc/mysql/mariadb.conf.d$ sudo netstat -ntlup | grep mysql
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      16884/mysqld

Did you remember to restart? Yes. I restarted using this between all attempts:

sudo service mysql restart
Community
  • 1
CoderGuy123
  • 321
  • 1
  • 3
  • 11
  • 1
    You've fixed your problem so firewall obviously wasn't the issue. However the firewall command suggested in the docs isn't ideal for Ubuntu which prefers the `ufw` interface. Use `ufw status` to check firewall state. See http://askubuntu.com/questions/174646/how-do-i-find-my-firewall-is-blocking-mysql for how to allow access using `ufw`. – Paul Haldane Oct 14 '16 at 05:07
  • 2
    @Deleeet your solution helped me to fix the problem. Just in case if anyone is having the same issue you may need to restart the machine after the changes cause in my case sudo netstat -ntlup | grep mysql wasn't binding to :: until I restarted the machine even though I tried sudo service mysql restart. It took some time for me to figure it out. – ralixyle May 09 '17 at 22:33

2 Answers2

10

The solution to the error in my case was that there was no [mysqld] section at all in the my.cnf config files. Adding this solved the issue:

[mysqld]
bind-address = ::

Not sure why it was not added by default. Note that the reason to use :: over 0.0.0.0 is that :: works for IPv6 too (mentioned in mySQL manual, but not mariaDB manual).

This also fixed the telnet:

mint@mint-VirtualBox ~ $ telnet 128.199.203.208 3306
Trying 128.199.203.208...
Connected to 128.199.203.208.

And the network output is now:

[user]@popfreq:/etc/mysql$ sudo netstat -ntlup | grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      17609/mysqld

Hope this helps someone else.

CoderGuy123
  • 321
  • 1
  • 3
  • 11
  • The reason it doesn't exist that way in the first place is because it's insecure for a service, by default, to simply listen on all available interfaces. Service configuration should explicitly require defining network resources out of the box to prevent dangerous mistakes. – brent Mar 28 '18 at 17:07
4

After having the same problem (on Debian Stretch) and already tried all the solutions mentioned here without any success, I finally found this from where I just post the important part:

Edit /etc/mysql/mariadb.conf.d/50-server.cnf, comment the bind-address, and add the sql-mode statement:

[...]
# 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

sql-mode="NO_ENGINE_SUBSTITUTION"

[...]

and - it worked.

Andrew Schulman
  • 8,561
  • 21
  • 31
  • 47
jhort
  • 41
  • 3