In this article we shall see the steps to enable remote access to MySQL database which is available on a Ubuntu machine.
Step 1 – Allow connections to clients other than localhost in MySQL Configuration file
Allow MySQL connections from other clients in the configuration file. The MySQL configuration file will be available at /etc/mysql/mysql.conf.d directory and the file name will be mysqld.cnf. By default MySQL is set to allow connections only from localhost i.e 127.0.0.1, we need to change this to 0.0.0.0 to allow connections from other clients.
Change,
bind-address = 127.0.0.1
to
bind-address = 0.0.0.0
Step 2 – Whitelist client IP address in the Ubuntu Machine’s firewall
The ubuntu machine comes with Ubuntu Firewall and by default it does not allow incoming connection of MySQL port – 3306. Therefore we need to open the port for the client’s specific IP address or to all IP addresses if your client does not have a fixed IP address.
Let’s assume your client’s IP address as 50.75.120.81. On your terminal, the following line will allow incoming connections to port number 3306 from client with IP address 50.75.120.81:
ufw allow from 50.75.120.81 to any port 3306
If your client has no fixed IP address or if you need to permit all IP addresses (not-recommended as anyone can attempt to connect to 3306),
ufw allow 3306