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
2
  • 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 askubuntu.com/questions/174646/… for how to allow access using ufw. Oct 14, 2016 at 5: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 9, 2017 at 22:33

4 Answers 4

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.

1
  • 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, 2018 at 17:07
5

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.

4
  • This is really no different than the existing accepted answer.
    – brent
    Mar 28, 2018 at 17:09
  • 1
    @brent: the existing accepted answer didn't work in my case
    – jhort
    Mar 28, 2018 at 19:46
  • 1
    This 50-server.cnf method is the only method that works on the latest MariaDB release... spent many hours trying with the other methods Dec 17, 2018 at 11:58
  • +50 if i could. Thanks for posting this pal. Oct 23, 2020 at 20:11
1

Note: bind-address = :: did not work for me, but 0.0.0.0 worked. So, :: is for IPv6, but 0.0.0.0 for IPv4! I.e. :: does not work for IPv4.

0

I also had this problem. Having a non-UI ubuntu box I could use just mysql command line through SSH (like logged in on the localhost), but couldn't access the database from my workstation.

First thing is to allow non-local host connections. Edit /etc/mysql/mariadb.conf.d/50-server.cnf an check the bind-address:

#bind-address           = 127.0.0.1

It then complained about my host 'mymachine.mydomain.local' not allowed, so to fix user access I just created a user within the MariaDB.

SELECT host, user FROM mysql.user;

listed localhost users only.

So I created a user to use by other machines on my network:

CREATE USER 'myuser'@'%.mydomain.local' IDENTIFIED BY 'a-password';
GRANT ALL PRIVILEGES ON the-database TO myuser IDENTIFIED BY 'a-password';

and MariaDB allows me to connect.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .