mariaDB can't connect from remote host

Question

MariaDB [(none)]> show variables like '%skip_networking%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

When I try

mysql -uroot -p -h 192.168.0.30

I received this

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.30' (111 "Connection refused")

In the file

 /etc/mysql/mariadb.conf.d/50-server.cnf

I have this:

bind-address            = 0.0.0.0
# skip-networking

I hope you can help me.

local connection works.

sudo netstat -ntlup | grep mysql
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      11580/mysqld

I see the "127.0.0.1:3306" but I don't know how can I change it.

Possible duplicate of [What causes the 'Connection Refused' message?](http://serverfault.com/questions/725262/what-causes-the-connection-refused-message) — user9517, Mar 05 '17 at 13:40

score 2 · Accepted Answer · edited May 23 '17 at 12:41

Credits : https://stackoverflow.com/a/14779244/7499402

What is disabled by default is remote root access. If you want to enable that, run this SQL command locally:

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
 FLUSH PRIVILEGES;

And then find the following line and comment it out in your my.cnf file, which usually lives on /etc/mysql/my.cnf on Unix/OSX systems. If it's a Windows system, you can find it in the MySQL installation directory, usually something like C:\Program Files\MySQL\MySQL Server 5.5\ and the filename will be my.ini.

Change line

 bind-address = 127.0.0.1

to

 #bind-address = 127.0.0.1

And restart the MySQL server for the changes to take effect.

score 1 · Answer 2 · answered Apr 03 '18 at 08:14

I had the same problem and i could solve it by checking this aspects:

First of all, accessing your db with an account that accepts remote connections. As mentioned on other posts that account should have a % in the server name instead of a localhost.

I don't know if it is a secure practice to do this but i think it is ok to start.

Example of phpmyadmin lay out

After that you should check the configuration files from

/etc/mysql
/etc/mysql/mariadb.conf.d/

I had to deduce from the */etc/mysql/my.conf because it includes this lines

user@debian:/etc/mysql$ cat my.cnf
# 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.
[client-server]

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

The thing is the second dir owns this files:

user@debian:/etc/mysql/mariadb.conf.d$ ls
50-client.cnf  50-mysql-clients.cnf  50-mysqld_safe.cnf  50-server.cnf

In which logically, in the 50-server.cnf contains the lines that are referenced in the posts but no longer in /etc/mysql/my.cnf. Note: The output from this commands has been edited to clarify the answer.

Just comment the bind-address line and connect to your database from a remote terminal.

user@debian:/etc/mysql/mariadb.conf.d$ cat 50-server.cnf 
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#
[server]
[mysqld]
# 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

After that my remote connection worked. I hope this helps.

score 0 · Answer 3 · answered Mar 05 '17 at 13:36

0

The process mysqld is listening on 127.0.0.1:3306 as shown in netstat output. This explains why you are getting connection refused error when connecting remotely. It is just accepting connections locally.

After verifying you are changing the right config file, you need to restart mysqld process.

answered Mar 05 '17 at 13:36

Khaled

35,688
8
69
98

Yes. But how can i change it? I have check all config files where I have found and there is no bind-address or skip-networking – Koda Mar 05 '17 at 13:37
@Koda: Did you restart your DB process after that? – Khaled Mar 05 '17 at 13:38
yes. I have try to reboot the complete system too – Koda Mar 05 '17 at 13:38
If you are on a system running firewalld you've to allow the `mysql` service using `firewall-cmd --permanent --add-service=mysql && firewall-cmd --reload` . – Valentin Bajrami Mar 05 '17 at 13:52

score 0 · Answer 4 · answered Mar 17 '17 at 07:19

The issue is the location of your configuration. I don't think /etc/mysql/mariadb.conf.d is picked up on by mariadb.

Their documentation to address your issue (https://mariadb.com/kb/en/mariadb/configuring-mariadb-for-remote-client-access/) suggests putting your bind-address in a my.cnf file:

  * /etc/my.cnf                              (*nix/BSD)
  * $MYSQL_HOME/my.cnf                       (*nix/BSD) *Most Notably /etc/mysql/my.cnf
  * SYSCONFDIR/my.cnf                        (*nix/BSD)
  * DATADIR\my.ini                           (Windows)

I suspect you may be able to add your bind-address config in /etc/mysql/my.cnf.local, and that should make mariadb listen on 0.0.0.0.

Based on your netstat output, I think your extra config file is simply not being picked up.

mariaDB can't connect from remote host

4 Answers4