Connect to vultr mysql databases from your machine

Posted on December 5, 2020

For security, mysql does not let incoming connections in the databases. So, if we want to connect to them from our local machine, we can use a firewall that will allow our public ip.

First we have to let connections to mysql server from other ips. We do it by adding bind-address option.

On Ubuntu servers, we access the MySQL configuration file /etc/mysql/my.cnf and modify the line as follows.

bind-address = *

To take effect we restart the server:

/etc/init.d/mysql restart

We create a new user to access the database. For better security we grant him only the SELECT privilege.

mysql> GRANT SELECT ON dbname.* TO dbuser@'%' IDENTIFIED BY 'apassword';

Test that the user is correctly created

mysql> SHOW GRANTS FOR 'dbuser'@'%';

Exit mysql, and from command line set the rules for the firewall, ufw should be installed.

ufw allow from xx.xx.xx.xx to any port 3306
ufw allow ssh
ufw allow http
ufw allow https

Port 3306 is for mysql, and we use our public ip, only this ip will be able to read from the database. Ssh/http/https ports are also needed (22/80/443) for the file trannsfer to your server and the website to keep working.

Finaly, to enable and disable the firewall:

ufw enable
ufw disable

When firewall is enabled, test that you can connect to the remove mysql server from your local machine's console:

telnet xx.xx.xx.xx 3306