Connect to vultr mysql databases from your machine
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.
[mysqld] bind-address = *
To take effect we restart the server:
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'; mysql> FLUSH PRIVILEGES;
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