How to get remote access on MariaDB

Question

New setup of Ubuntu 20.04 and MariaDB. MariaDB works fine locally, but I cannot access it remotely. I've searched online and think I've done what's suggested so there may be a step I've missed.

Here's what I've done thus far:

GRANT ALL ON 'admin'@'%' IDENTIFIED BY 'THEPASSWORD';
FLUSH PRIVILEGES;

I then changed the bind address to:

bind-address            = 0.0.0.0

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

(Note that the file /etc/mysql/my.cnf doesn't have any bind-address information in it.)

In AWS firewall, I added port 3306 to the incoming ports for all locations.

The error I get trying to connect remotely is is:

2002 - Can't connect to server on '(Ip address here)' (36)

I've tried using both the IP of the server and the DNS name. Both approaches give same error.

What could I be missing?

Thanks!

Answer 1

As of security reasons I prefer to set up port-forwarding (tunnelling) through SSH between two linux hosts. I suppose you have a SSH access to the host with MySQL/MariaDB on it:

ssh user@dbhost -N -f -L localhost:3306:localhost:3306  

This is a bit weird syntax meaning literally:

port 3306 on 127.0.0.1 of the working machine should be tunneled to the port 3306 on 127.0.0.1 on the remote machine DBHost having DB service running. Then on the localhost you'll see a listened port 3306 by

ss -l | egrep "3306|mysql"

Now you need a MySQL/MariaDB client installed on the working machine and you can connect to the remote host in a plain straightforward way:

sudo mysql

Local client will connect to the local port 3306 that will be forwarded to the dbhost:localhost:3306. Service's answers will be routed back through the SSH tunnel.

If you already have another MySQL/MariaDB running on working machine and you do not want to mix it up with a remote one just change port nmber to any other:

ssh user@dbhost -N -f -L localhost:3308:localhost:3306
sudo mysql -P 3308