How to Connect to a Remote MySQL/MariaDB Server via SSH Tunnel

MariaDB server runs locally, and remote connections are not allowed by default. So, to connect to a remote MariaDB server, you can use an SSH tunnel. From a security perspective, this is much better than allowing remote connections on a MySQL/MariaDB server directly.

Also, make sure you are using SSH keys instead of passwords. Assuming that you have already setup the server with SSH key-based authentication, here is how you can connect to a remote database server via a tunnel.

First of all, set up the remote server's details in the SSH config file on your PC/desktop machine. I am using an Ubuntu Linux desktop, so my file is located at /home/abhinav/.ssh/config.

Host myserver
    HostName 123.456.78.90
    User abhinav
    IdentityFile ~/.ssh/host-keys/myserver_priv_key

(make sure to replace the details with your own)

Now that the detail is saved in the config file, you can just use myserver with the ssh command to login to the server.

ssh myserver

That's a normal SSH login. We can use the same to setup something called a 'tunnel' as well.

An SSH tunnel can be of two types:

• Local port forwarding
• Remote port forwarding

Establishing an SSH Tunnel

To connect to a remote mysql server, what we need is local port forwarding. You can achieve it with this command:

ssh -L 3307:localhost:3306 myserver

• the flag -L denotes that it is a local port forwarding
• 3307 is the port on your local machine, that is your PC, desktop, etc. You can use any free port on your computer. Here, 3307 is being forwarded.
• localhost:3306 is relative to the remote server, that is myserver. 3306 is usually the default MySQL or MariaDB port. localhost is the localhost on the remote server, not on your PC.
• so, whenever a request is made to port 3307 on your PC, it will be forwarded to port 3307 on the remote server.

When you run this command in a terminal on your desktop, the connection will be established, and you will be logged in to the remote server's terminal.

Instead of that, you can also run SSH tunnel in the background by adding a few extra flags:

ssh -fN -L 3307:localhost:3306 myserver

• -f flag tells SSH to run in the background, so closing the terminal won't close the tunnel.
• -N flag prevents SSH from executing remote commands. This prevents opening the remote shell.

Connecting to a remote MySQL console via SSH Tunnel

Keep that terminal opened like that, and open another terminal on your desktop. Or if it is in a background process, you can use the same terminal itself.

Then run the mysql or mariadb command to login to the remote MySQL terminal.

mysql -h 127.0.0.1 -P 3307 -u root -p

You'll be prompted to enter the root user's password, that is, the password of the root mysql user on the remote server, not on your PC.

If successful, you'll see the remote MySQL console.

Taking database backups via SSH Tunnel

SSH tunnel can also be used to take database backups:

mysqldump -h 127.0.0.1 -P 3307 -u root -p > my_site_db.sql

You'll see the file my_site_db.sql downloaded right to your PC.

Checking if Tunnel is working

To check if the SSH tunnel is still established, you can search it:

ps aux | grep ssh

You should find something like this if it is still running:

abhinav    24506  0.2  0.0  16956  8352 pts/0    S+   20:34   0:00 ssh -L 3307:localhost:3306 myserver

Closing an SSH Tunnel

To close an SSH tunnel, log out from the first terminal where you opened the SSH tunnel, and close the terminal window.

Or if you know the process ID, as from the ps aux result, you can kill the process to close the tunnel:

kill 24506

This can be used especially to close SSH tunnels working in the background, where closing the terminal window has no effect.