In order to access MySQL remotely on your Digital Ocean droplet you’ll need to open up the port used for MySQL.
By default, the port is closed for remote access if you’ve setup your server using Server Pilot.
Server Pilot doesn’t advise that you open ports in the firewall, as they’ve setup the bare minimum to allow things to work, but to allow your websites to work. That’s great, but on occasions you do need to access the MySQL databases remotely for various reasons. So enough on the why, and more about the how!
SSH onto your Digital Ocean Droplet as root.
Type the following
sudo ufw allow ssh
What does this do?
sudo – Elevates you to root
ufw – The Ubuntu built-in Firewall (stands for Uncomplicated Firewall)
allow – Obviously allows access. (Can be allow / deny)
ssh – Pseudonym for port 22. You can replace this with your port number or service name.
If you want to only allow a specific IP address then you can add this to the end of the argument. Only do this if you have a fixed IP, or server that will be accessing the MySQL.
sudo ufw allow from 18.104.22.168/24 to any port 3306
This will allow connections from the 22.214.171.124/24 subnet. You can also put a single IP addess without the subnet mask. (i.e. Just put your specific IP address)
ufw status will show you which ports are open and if the firewall is running.
Now that you’ve opened up the necessary port, you need to tell MySQL to listen on any IP address. By default it will be set to listen to the local host only 127.0.0.1.
To change the bind address, use the following commands.
sudo nano /etc/mysql/my.cnf
Find bind-address in the file and change it from 127.0.0.1 to 0.0.0.0
You’re effectively telling MySQL to listen on any IP address. Press Ctrl+X and Y to save the changes, then restart MySQL with the following command.
sudo service mysql restart
Now you can remotely access that database with MySQL Workbench, Navicat or any other program or script you needed the remote access for.
If you need more information on the Firewall built into Ubuntu, have a look here https://help.ubuntu.com/community/UFW