MariaDB refuses remote connections

Question

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

I've been to:

• MariaDB official docs for remote access
• MySQL: Allow both remote AND local connections
• Mysql through ssh often can't connect or timeout

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.