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.
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.
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.
Note:- if you are not logged in with a user having root access, add sudo before the command.
sudo nano /etc/my.cnf
Don’t worry if your file is blank. Just add the following line to the file.
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.
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.
for IPv4 :
for IPv6 :
It all done. Just reboot your system and try connecting from a deferent system.
To reboot you can run sudo init 6 or sudo reboot.
Thanks for reading,
If you find any issues or corrections please update on the comment. I love to improve. 😊