How to Give Remote Access to Mysql Database
Oct 18, 2021
Edgaras G.
4min Read
How to Allow Remote Connections to a MySQL Database
Hosting and managing MySQL databases and web servers on the same local machine is a common practice. However, this setup can be inconvenient and limit scaling opportunities.
That's why many businesses and organizations choose to set up a remote MySQL server, having their database and web server grow at their own pace. Accessing the database server from a remote location can also improve hardware performance and security.
The MySQL server is configured to listen for local incoming connections by default, which means that access to this database server from any remote system is disabled.
If you want to grant remote access to an existing database, keep reading, as we'll show you how to do it on an RHEL or CentOS server and hPanel.
How to Allow Remote Connection to a MySQL Database Server on RHEL or CentOS
Before starting with this guide, ensure that you have access to the following:
- A command line or terminal window.
- A remote MySQL server.
- Root privileges on local and remote machines.
Note that you'll need to establish an SSH connection if the server is outside your data center by using PuTTY on Windows or the built-in terminal shell on Linux and macOS.
If you are a Hostinger VPS hosting user, find the login details on the hPanel's Servers tab.
1. Changing the MySQL Server Configuration File
The first step is to make the remote MySQL server listen for external connections by adding an extra option to the configuration file.
To do this, log in to your server as the root user and run the following command to determine the location of the MySQL configuration file:
mysql --help | grep "Default options" -A 1
The output should look like this:
Then, open and edit the MySQL config file using the nano editor:
nano /etc/my.cnf
Once the file opens, locate the line that contains [mysqld] label and add the following code:
bind-address=YOUR.SERVER.IP
Remember to replace YOUR.SERVER.IP with the specific IP address of the remote machine to which the MySQL server should listen.
If there is a line containing skip-networking, make sure to delete it or mark it as a comment by adding # at the beginning. This line makes the system not listen for TCP/IP connections at all.
After editing the file, save it by pressing CTRL+X or COMMAND+X if you are on Mac.
For changes to take effect, restart the MySQL service by running the following command:
systemctl restart mysqld
2. Opening The Required MySQL Port
The default MySQL port for external connections is 3306. If you have configured a firewall service on the MySQL server, you need to allow traffic through this specific port.
If iptables is your default firewall tool, execute the following command to open port 3306 for unrestricted traffic:
iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
Note that the command above will allow remote access from all IP addresses on the internet through the MySQL port.
Alternatively, you can grant access to a remote user from one specified IP address with the following command:
iptables -A INPUT -i eth0 -s remote_IP_address -p tcp --destination-port 3306 -j ACCEPT
Remember to change remote_IP_address to the IP address of the remote host.
Once you're finished, save the iptables configuration by entering:
service iptables save
The response will look like this:
Now the server will allow remote connections to your MySQL service.
Try running the MySQL server to establish a connection from your remote system by typing the command below:
mysql -u username -h mysql_server_ip -p
Replace the username with your MySQL username and mysql_server_ip with the IP address or hostname of the MySQL server. The -p parameter will prompt you to enter the password for the MySQL user account.
The output will look similar to this:
Connection to <em>mysql_server_ip</em> 3306 port [tcp/mysql] succeeded!
How to Allow Remote Connections to MySQL Database Server on hPanel
If you use Hostinger, you can connect to the MySQL server remotely by allowing an IP address on your account's side.
To start the process, log in to your hPanel and navigate to Databases -> Remote MySQL.
On the Remote MySQL page, type the IP address of your remote server in the IP (IPv4 or IPv6) field or tick the Any Host box to connect from any IP.
Then, select the Database that you want to access remotely. Once you're done, click Create.
Remember that remote connections also require a MySQL user to use their MySQL server hostname – you can find the hostname at the top of the same page.
Conclusion
In this tutorial, we've gone over how to connect to the MySQL server from a remote host or grant access to a remote MySQL user.
Here's a quick recap of the steps required to accept remote connection from remote hosts on an RHEL or CentOS server:
- Change the MySQL server configuration file – use the command line to add an extra option and the remote user's specific IP address to the MySQL config file.
- Open the required MySQL port – add an entry to your firewall rules for port 3306.
To enable remote MySQL connection on hPanel, simply log in to your Hostinger account and go to the Remote MySQL page. Enter the remote host's IP address and choose your database, then save the changes.
For more information, consider reading other Hostinger tutorials about MySQL, like how to optimize a database using the phpMyAdmin MySQL client.
Good luck. Be sure to leave a comment below if you have any questions.
Edgaras is a veteran server administrator at Hostinger. He makes sure that every server runs at full throttle and has all the latest technological advancements. When he's not working, Edgaras enjoys skiing and exploring the world.
How to Give Remote Access to Mysql Database
Source: https://www.hostinger.com/tutorials/mysql/how-to-grant-remote-access-mysql-vps