MariaDB Refusing remote connections

Question

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

I've used the following great example: MariaDB refuses remote connections But still no luck connecting... But thanks Deleet for all the steps and this perfect template! XD

I installed MariaDB on CentOS 7, so not Ubuntu like most others. Then set up two users, one of which is intended for localhost use and phpMyAdmin, this user can login via phpMyAdmin and SSH @ localhost. I've then added another user to use as login for my connecting server.

The users are added as:

CREATE USER 'clust3r1ogin'@'%' IDENTIFIED BY '';  And GRANT all permissions. 

Does local connections work?

No, I cannot connect as the user via ssh on the server:

[root@zeus ~]# mysql -u clust3r1ogin -p
Enter password:
ERROR 1045 (28000): Access denied for user 'clust3r1ogin'@'localhost' (using password: YES)

The other localhost user works fine...

Did you verify the users were added correctly?

I think so:

MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';
+--------------+-----------+
| User         | Host      |
+--------------+-----------+
| clust3r1ogin | %         |
| root         | 127.0.0.1 |
| root         | ::1       |
+--------------+-----------+
3 rows in set (0.00 sec)

Have you unblocked the firewall?

Yes, otherwise I would get another error? But I did run: firewall-cmd --zone=public --add-port=3306/tcp --permanent

I did alter it a little that it would only allow specific IP addresses to connect to the port.

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

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

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

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Did you check the other config files?

Yes. They did not have the offending lines either.

Does telnet work?

Yes.

[root@titan ~]# telnet zeus.xxx.xxx 3306
Trying xxx.xxx.xxx.xxx...
Connected to zeus.mairel.com.
Escape character is '^]'.
Y
5.5.5-10.1.22-MariaDBAYm9rS)q?�QQ%`98N_q<tVmysql_native_passwordConnection closed by foreign host.

What interface is the server using?

Local only it seems:

[root@zeus ~]# netstat -ntlup | grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      1198/mysqld

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

service mariadb restart

I do not understand anymore what is wrong. I bet it's some configuration thing but no luck yet...

As Deleet suggested I've added

[mysqld]
bind-address = ::

To my.cnf file but no luck...

When I use my other localhost login for remote access, after changing the HOSTparameter to % Wildcard still the same error:

Unable to connect to the MySQL host “zeus”. Connection failed with error: Access denied for user 'local_user'@'titan' (using password: YES)

Answer 1

The problem was the following. The users where created with the SSL Required option. Turning this off resolved all the problems connecting!

Answer 2

I needed to setup MySQL for remote for testing purposes.

I am using ubuntu, and I had to change the

bind-address = 0.0.0.0

Instead of

bind-address = 127.0.0.1

Mine was in /etc/mysql/mariadb.conf.d/50-server.conf file.

I did found it by doing:

$ cd /etc/mysql
$ grep bind-address *
$ grep bind-address */*
$ grep bind-address */*/*

Look for it that way. It may or may not be in /etc/mysql. Look below the top directory too.

Also do a grep search for skip-network and, if found comment it out or remove it. And if you had to add the bind-address, more than likely it has the

skip-networking 

line in a config file. Again use grep to find it.

Then, to check, login to the other machine and run the command

$ mysql -u user-name -h server-ip-address -p

to see if it lets you connect that way.

Answer 3

disabled selinux ,resolved all the problems connecting