Please refer to the following 3 steps to add a remote MySQL connection in Linux.
Edit MySQL config file
- Use your appropriate editor to open the MySQL config file. In this tutorial, we will use a nano text editor. Perform the below command in order to open the config file.
# sudo nano /etc/mysql/ mysql.conf.d/mysql.cnf
- 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 address should match the server.
- Once you make the necessary changes in the file, save it and exit from the config file.
- In order to implement changes in the file, we need to restart the service.
# sudo systemctl restart mysql
Need to setup Firewall to allow remote connection
- If you have noticed, in the configuration file there is line “port =3306”. Now, we will need to open traffic for the specific port.
- Uncomplicated Firewall is the default tool in linux. Fire the below command to allow traffic and match IP.
# sudo ufw allow from remote_ip_address to any port 3306
- 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
- Now, we will open MySQL port along with iptables to unrestricted traffic.
# sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT
- To provide access to specific IP address.
# sudo iptables -A INPUT -p tcp -s 127.0.0.1 --dport 3306 -j ACCEPT
- 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 your fire the above command, it is necessary to save the changes that we made to the IPtable rules.
# sudo netfilter-persistent save
# sudo netfilter-persistent reload
# service iptable save
Connect with MySQL remotely
- Run the below command in order to establish 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 the MySQL.
- Once your connection gets successful, you’ll see a success message like this.
- Connection to mysql_server ip 3306 port [tcp/mysql] succeeded!
This is how you can add a Remote MySQL connection in Linux.