How to remotely connect to your DigitalOcean mySQL database with HeidiSQL

Since I prefer to keep my DigitalOcean droplets als lean as possible, I never install phpMyAdmin but instead connect using HeidiSQL.

HeidiSQL is a free and open source client, or frontend for MySQL that makes managing your MySQL databases a breeze.

In this post I shall explain how to remotely connect to your DigitalOcean mySQL database with HeidiSQL over an SSH connection.

First you will need to download and install HeidiSQL from this link: https://www.heidisql.com/download.php
As we are going to connect to the MySQL database over SSH we need to install Putty, which is a free SHH client. You can download Putty here: http://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html

Now you can configure HeidiSQL. Use the following connection settings:

Settings
Networktype: MySQL (SSH tunnel)
Hostname / IP : 127.0.0.1
Prompt for credentials: unchecked
User: root
Password: [root_password]
Port: 3306
Compressed client/server protocol: unchecked

SSH Tunnel
plink.exe location: C:\Program Files\PuTTY\plink.exe
SSH host + port: [your_droplet_ip_address]
Username: root
Password: [leave empty]
plink.exe.timeout: 4
Private key file: [leave empty]
Local port: 3306

Now, when you connect you will get a message about the private key file, just click ‘Yes’ and then you are connected.

Note that if you have configured bind in you MySQL configuration file, you will have to uncomment this or you will not be able to login.

You can do so by logging in with SSH and following these steps:

sudo nano /etc/mysql/my.cnf

Comment-out:
# bind-address = 127.0.0.1

[save the file]

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Comment-out:
# bind-address = 127.0.0.1

[save the file]

Then finally restart MySQL to load the changed configuration:

sudo service mysql restart

If you sign-up to DigitalOcean using this link, you will get $25 discount!