bconnelly
By:
bconnelly

Remote Connection to MariaDB on Ubuntu

December 22, 2017 1.5k views
MySQL Ubuntu

I have set up maraDB and have been trying to connect to it remotely with no luck. I have read through a bunch of posts and have tried the following:

  1. I have looked in my.cnf to change the bind-address to 0.0.0.0.0 but have not found it defined anywhere

  2. Configured ufw to open port 3306

# ufw status
Status: active

To                         Action      From
--                         ------      ----
22                         ALLOW       Anywhere                  
3306/tcp                   ALLOW       Anywhere                  
3306                       ALLOW       Anywhere                  
22 (v6)                    ALLOW       Anywhere (v6)             
3306/tcp (v6)              ALLOW       Anywhere (v6)             
3306 (v6)                  ALLOW       Anywhere (v6) 

It hasn't worked so far.

Using a suggestion from a post, it looks like mysql is only listening locally on port 3306.

# sudo lsof -iTCP -sTCP:LISTEN -P
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
sshd    1540  root    3u  IPv4  15872      0t0  TCP *:22 (LISTEN)
sshd    1540  root    4u  IPv6  15881      0t0  TCP *:22 (LISTEN)
mysqld  3771 mysql   16u  IPv4  20248      0t0  TCP localhost:3306 (LISTEN)

Is there anyway to change this? Thanks for any help.

2 Answers
bconnelly December 23, 2017
Accepted Answer

After a lot of searching, I finally found where the bind address was hiding: It was in

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

Once I commented out the bind address and restarted mariadb, I was able to connect remotely.

I thought I'd post this in case it helps anyone else.

As per my understanding , changing bind address to your server IP address allow everyone to connect to your database, which might pose security risk .
keeping bind to 127.0.0.1 only allows server users to connect and manage database.
you could use SSH over TCP to tunnel to your database as a user with SSH enabled on your server , if you have SSH enabled for the user that will mange your database.
i use workbench for connecting to mysql remotely , and i have bind set to 127.0.0.1 and have no issue connecting.

Have another answer? Share your knowledge.