How to Allow Remote Connections to MySQL Database on Ubuntu 20.04

#ubuntu #database #server #mysql

In this article we will see how we can allow remote connection to MySQL database installed in our ubuntu 20.04 server. This can help you to manage your database from you desktop or laptops or even application running on your PC’s.

Before we begin, you have to know how to establish an SSH connection to your server using putty. Here I am using a windows desktop. Open putty and provide IP address of your server and click open.

It will ask for your username and password, provide it and click enter.
Yeah! you just logged into your server.

What’s next?
It’s quite simple. First you have to change your MySQL configuration as by default MySQL will not be listening for external connections. For that just follow the bellow steps.

1. Determine the location of you MySQL config file. Run the below command to find the same.

mysql --help | grep "Default options" -A 1

Your will get an output like

Default options are read from the following files in the given order : /etc/my.cnf /etc/mysql/my.cnf ~/my.cnf

It’s the order in which MySQL looks for config file and let’s take the first dir path /etc/my.cnf.

2. We will edit the /etc/my.cnf file, for that run the below command.

Note:- if you are not logged in with a user having root access, add sudo before the command.

sudo nano /etc/my.cnf

3. Locate the line that contain [mysqlid].

Don’t worry if your file is blank. Just add the following line to the file.

bind-address= xxx.ip.xxx

replace xxx.ip.xxx with your server ip. Example 192.168.0.100

4. Save the file by typing CTRL+O will ask for confirmation click enter and the exit the editor by pressing CTRL+X.

Well that’s it. But for the config changes to take action, restart your MySQL server by running

sudo systemctl restart mysql

That’s all with the MySQL side, now we have to open MySQL port (default 3306) in the firewall for external connections. For that follow the below steps.

1. Run the command below.

iptables -A INPUT -i enp1s0 -p tcp --destination-port 3306 -j ACCEPT

Alternatively you can grant access to specific ip address.

iptables -A INPUT -i enp1s0 -s xxx.xxx.xxx.xxx -p tcp --destination-port 3306 -j ACCEPT

replace xxx.xxx.xxx.xxx with ip of the system from which you need to establish the connection. In my case I have provided the ip of my desktop.

** Make sure the you replace enp1s0 with your network interface name. To find your network interface name, run ifconfig on terminal/putty, you can find it out.