You can add a remote MySQL connection in Linux by following these 3 steps –
- Edit MySQL config file
- Setup a Firewall to allow remote connection
- Connect with MySQL remotely
I. Edit MySQL config file
1. Use your appropriate editor to open the MySQL config file.
In this tutorial, we will use a nano text editor.
2. Execute the command given below to open the config file –
# sudo nano /etc/mysql/ mysql.conf.d/mysql.cnf
3. The location of the config file may vary based on the version in use. Once you open the config file, go to the Bind-Address line.
The default IP will be 127.0.0.1; you will need to change the IP address but make sure that the new IP should match the server.
4. Once you make the necessary changes in the file, save it and exit from the config file.
5. To implement changes in the file, we need to restart the service.
# sudo systemctl restart mysql
II. Setup a Firewall to allow remote connection
If you have noticed, the line “port =3306” is in the configuration file.
Now, we will need to open traffic for the specific port.
1. Uncomplicated Firewall is the default tool in Linux.
Enter the command given below to allow traffic and match IP –
# sudo ufw allow from remote_ip_address to any port 3306
2. The firewall tool in CentOS uses zones to dedicate what traffic is to be allowed.
We’ll create a new zone to set the rules for the MySQL server traffic.
# sudo firewall-cmd --new-zone=rule_name --permanent
# sudo firewall-cmd --reload
# sudo firewall-cmd -permanent --zone=rule_name --add-source=127.0.0.1
# sudo firewall-cmd --permanent --zone=rule_name --add-port=3306/tcp
# suo firewall-cmd --reload
3. Now, we will open the MySQL port along with iptables to unrestricted traffic
# sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
4. Provide access to a specific IP address
# sudo iptables -A INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT
5. The above command will allow IP address 127.0.0.1.
Please note that you will have to change 127.0.0.1 to your IP address. Once you execute the above command, we must save the changes we made to the IPtable rules.
# sudo netfilter-persistent save
# sudo netfilter-persistent reload
# service iptable save
III. Connect with MySQL Remotely
1. Run the command given below to establish a connection with MySQL remotely
# mysql -u username -h mysql_server_ip -p
-u username - it represents the username of your MySQL username.
-h mysql_server_ip - IP or Hostname of your MySQL server.
-p - it will prompt you to enter the password for MySQL.
2. Once your connection is successfully established, you’ll see a success message -
Connection to mysql_server ip 3306 port [tcp/mysql] succeeded!
Following the above steps will let you add a Remote MySQL connection in Linux.