Download ultimate SSH commands cheat sheet<\/a><\/p> \n\n\n\n\n<\/p> Before starting with this guide, ensure that you have access to the following:<\/p> Hostinger makes connecting to MySQL<\/a> server remotely hassle-free with our graphical interface. Follow these steps to enable a remote database connection:<\/p> For security reasons, you need a Secure Shell (SSH) connection to enable remote connections to your MySQL database server. SSH is a secure network protocol<\/a> that encrypts remote server access.<\/p> While it’s not the only method to secure a remote MySQL database connection, SSH ensures a high level of security, making it a preferred choice for protecting data during transmission. This approach is critical when handling sensitive data or connecting on unsecured or public networks.<\/p> Windows users can use PuTTY<\/a>, a popular SSH client, to create an SSH tunnel. Meanwhile, Linux and macOS users have built-in terminal applications that support SSH. In all these cases, you’ll be using a MySQL client to interact with your database server remotely.<\/p> If you are an existing or new user of Hostinger’s VPS service<\/a>, find the server’s login credentials under hPanel’s VPS →<\/strong> SSH access<\/strong> tab or use the integrated Browser terminal<\/strong>.<\/p> The first step is to make the remote MySQL server listen for external connections by adding an extra option to the configuration file.<\/p> To do this, log in to your server as a root MySQL user and connect to the MySQL command line. Run the following command to determine the location of the MySQL configuration file:<\/p> The output should look like this:<\/p> Next, open and edit the MySQL config file using nano<\/strong>, a text editor for Unix-like systems with a command-line interface:<\/p> Once the file opens, locate the line that contains the [mysqld]<\/strong> label and add the following code:<\/p> Replace server_ip<\/strong> with the remote server IP address to limit connections. Avoid setting the bind-address<\/strong> value to 0.0.0.0<\/strong>, as it enables any IPv4 address to access the MySQL servers, increasing the risk of unauthorized access.<\/p> If the MySQL config file has a line containing skip-networking<\/strong>, remove it or mark it as a comment by adding # <\/strong>at the beginning of the line. This directive, when active, makes the MySQL server not listen for TCP\/IP connections, effectively disabling remote access.<\/p> After editing the file, save it by pressing CTRL+X<\/strong>. To apply the changes, restart the MySQL service using the following command:<\/p> The default MySQL port for external connections is port 3306<\/strong>. If you have a firewall configured on your MySQL server, you’ll need to allow traffic through this specific port to enable access from remote machines.<\/p> Hostinger users on KVM VPS plans can add new firewall rules directly from hPanel by navigating to Security <\/strong>→ Firewall<\/strong>. Select Create firewall configuration<\/strong>, name it, and click Edit <\/strong>to configure it.<\/p> This tutorial uses the Transmission Control Protocol (TCP)<\/a> and port number 3306<\/strong>. The source is set to Custom<\/strong>, allowing incoming traffic only from a specific IP address. Once you’re done setting up the remote database connection configuration, click Add rule<\/strong>.<\/p> Alternatively, open hPanel’s Browser terminal<\/strong> and use the following command to restrict access to a specific remote IP address using <\/strong>iptables<\/a>, a widely used firewall tool by the Linux kernel. Proceed with securing remote access by replacing remote_IP_address<\/strong> with the actual IP address of the remote MySQL host:<\/p> Omitting -s remote_IP_address<\/strong> from the syntax opens port 3306 <\/strong>for unrestricted traffic. Be careful with this setting, as it increases the risk of unauthorized access.<\/p> Save the iptables<\/strong> configuration by entering:<\/p> After configuring your MySQL server to allow remote connections, users from the specified IP address can set up a new connection from a remote machine. To do so, enter the following command in your terminal:<\/p> Replace username <\/strong>with your MySQL username and mysql_server_ip<\/strong> with your MySQL server’s IP address or hostname. The -p<\/strong> parameter enables password authentication, prompting you to enter the password for the specified user account.<\/p> Next, enter the password value for the MySQL user account. If the connection is successful, you will enter the MySQL command-line interface, indicated by a mysql><\/strong> prompt. Once connected, you can specify the database name you wish to work with using the command below:<\/p> You can then start executing commands and interacting with the MySQL database remotely.<\/p> Run a simple query to test the MySQL database interaction. For example, this basic command lists all databases available to your user account:<\/p> Exit the MySQL command-line interface by typing:<\/p> Follow these steps if you’re having trouble establishing a remote MySQL link:<\/p> Setting up a remote connection to a MySQL database lets you access and manage your MySQL databases from anywhere, enhancing remote teams’ efficiency. It also improves your database’s performance and keeps it safe from unauthorized access and network attacks.<\/p> Hostinger simplifies enabling MySQL remote access through hPanel with a user-friendly firewall configuration tool and an integrated Browser terminal<\/strong> for ease of use.<\/p> Here’s a quick recap of the steps to allow remote connection to MySQL if you’re not using Hostinger’s built-in tools:<\/p> We hope this article helped your understanding of remote MySQL connection and how to leverage it for a better workflow. Leave a comment below if you have any questions.<\/p>\n","protected":false},"excerpt":{"rendered":" A remote MySQL server helps you work more efficiently and keeps your data secure. It enhances accessibility, letting you and your team access the database from anywhere, which is perfect for remote working. This setup improves MySQL database performance by not utilizing computer resources from the same local machine, resulting in faster and smoother operations. […]<\/p>\nHow to allow MySQL remote connection in hPanel<\/h2>
\n
\n
![\"The](\"https:\/\/www.hostinger.com\/tutorials\/wp-content\/uploads\/sites\/2\/2023\/12\/hpanel-remote-database-connection-1024x403.png\")
<\/a><\/figure><\/div>\n
![\"Locating](\"https:\/\/www.hostinger.com\/tutorials\/wp-content\/uploads\/sites\/2\/2023\/12\/hpanel-remote-database-connection-hostname-1024x407.png\")
<\/a><\/figure><\/div>\n
![\"A](\"https:\/\/www.hostinger.com\/tutorials\/wp-content\/uploads\/sites\/2\/2023\/12\/hpanel-remote-database-connection-delete-1024x242.png\")
<\/a><\/figure><\/div>![\"Hostinger](\"https:\/\/www.hostinger.com\/tutorials\/wp-content\/uploads\/sites\/2\/2023\/11\/Web-hosting_in-text-banner.png\")
<\/a><\/figure>How to grant remote connection to a MySQL database server<\/h2>
![\"Hostinger](\"https:\/\/www.hostinger.com\/tutorials\/wp-content\/uploads\/sites\/2\/2023\/12\/hpanel-vps-browser-terminal-1024x500.png\")
<\/a><\/figure><\/div>![\"\"](\"https:\/\/www.hostinger.com\/tutorials\/wp-content\/uploads\/sites\/2\/2023\/02\/VPS-hosting-banner-1024x300.png\")
<\/a><\/figure>1. Edit the MySQL configuration file for remote access<\/h3>
mysql --help | grep \"Default options\" -A 1<\/pre>
![\"Locating](\"https:\/\/www.hostinger.com\/tutorials\/wp-content\/uploads\/sites\/2\/2023\/12\/output-locate-mysql-configuration-file.png\")
<\/a><\/figure><\/div>nano \/etc\/my.cnf<\/pre>
bind-address=server_IP<\/pre>
sudo systemctl restart mysql<\/pre>
2. Open the required MySQL port<\/h3>
![\"Hostinger](\"https:\/\/www.hostinger.com\/tutorials\/wp-content\/uploads\/sites\/2\/2023\/12\/hpanel-firewall-1024x528.png\")
<\/a><\/figure><\/div>![\"Setting](\"https:\/\/www.hostinger.com\/tutorials\/wp-content\/uploads\/sites\/2\/2023\/12\/hpanel-firewall-configuration-1024x480.png\")
<\/a><\/figure><\/div>iptables -A INPUT -i eth0 -s remote_IP_address -p tcp --dport 3306 -j ACCEPT<\/pre>
iptables-save > \/etc\/sysconfig\/iptables<\/pre>
3. Connect to the remote MySQL server<\/h3>
mysql -u username -h mysql_server_ip -p<\/pre>
USE database_name;<\/pre>
SHOW DATABASES;<\/pre>
exit<\/pre>
Troubleshooting MySQL remote connection issues<\/h2>
\n
Conclusion<\/h2>
\n