Question

How to connect to MariaDB using SSH Tunneling and Port Forwarding?

I have Ubuntu 18.04 which is connected through putty using a private key and running fresh install of MariaDB 10.1 and done the mysql secure installation.

I have HeidiSQL installed on my windows PC which I want to connect to the MariaDB server via SSH tunneling and port forwarding.

In the server terminal I did:

sudo ssh user@myserverip -L 3333:localhost:3306

But i get an error: Permission denied (public key)

Any help would be much appreciated!

Submit an answer

This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

Sign In or Sign Up to Answer

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

baraboom • January 6, 2020

Hi, good job so far getting everything installed and secured.

In order to connect to your new server (mariadb) from your windows SQL client (heidi), please try the following from Heidi’s documentation:

https://www.heidisql.com/screenshots.php?which=ssh_tunnel

It utilizes a separate app to handle the connection for you. You will not be opening the tunnel from your server but instead Heidi will open it from your PC.

Hope this helps and good luck - let us know how it goes or if you have more questions :)

belakey • January 8, 2020

Finally go it working. In HeidiSQL session manager:

Under Settings Tab

Network Type: MariaDB or MYSQL (SSH TUNNEL) Library: libmariadb.dll

Hostname: 127.0.0.1 or localhost

//Use the command below to find the hostname for the user you want to use to connect.

mysql> SELECT user,host FROM mysql.user;

User: mysql username Password: mysql password Port: 3306 (default)

Under SSH Tab tunnel

plink.exe location: find in location of putty

SSH Host + Port: Your digitalocean server IP and port (default is 22)

Username: Username to access your server terminal

Password: If you use SSH key when accessing terminal, leave empty

plink.exe timeout: 4 (default)

Private Key file: Location of private key file .ppk

Local Port: 22 (Default)

This should now connect to the database.

// additional There are some suggestions online to edit the /etc/mysql/my.cnf file and comment out the bind-address for it to connect but this should be for testing only as it allows connection from any IP address with the key, unless you change it to your client static IP address.

MYSQL Workbench is similar but convert .ppk keyfile to OPENSSH using Puttygen for it to log in if using SSH key file.