Many websites and applications host their web server and database backend on the same machine. However, some organizations are moving to a distributed environment. A separate database server can improve hardware performance and security and allows you to scale resources quickly.
By default, MySQL is configured to allow connections only from the localhost. You will need to allow remote access to the MySQL database server if your application and database are hosted on different servers.
In this tutorial, we will show you how to enable remote connections to a MySQL database.
Step 1 – Configure MySQL
Before starting, verify the MySQL listening connection with the following command:
ss -tunlp | grep 3306
You should get the following output:
tcp LISTEN 0 151 127.0.0.1:3306 0.0.0.0:*
users:(("mysqld",pid=7753,fd=33))
As you can see, the MySQL server is listening on the localhost on port 3306. That means the MySQL server is accessible only from the localhost.
You will need to configure MySQL server to listen for an external IP address where the server can be reached.
To enable this, edit the mysqld.cnf file:
nano /etc/mysql/mysql.conf.d/mysqld.cnf
Find the following line:
bind-address = 127.0.0.1
And replace it with the following line:
bind-address = 0.0.0.0
Save and close the file when you are finished, then, restart the MySQL service to implement the changes:
systemctl restart mysql
At this point, MySQL server is configured to listen on external IP.
Step 2 – Grant Access to User from Remote System
In this section, we will create a new database and database user and grant access to the remote system to connect to the database.
First, log in to the MySQL shell with the following command:
mysql
Once logged in, create a database named testdb and a user named testuser for a remote system using the following command:
CREATE DATABASE testdb; CREATE USER 'testuser'@'remote-server-ip' IDENTIFIED BY 'password';
Next, grant access to the remote system (remote-server-ip) to connect to a database named testdb as a testuser:
GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'remote-server-ip';
Next, flush the privileges and exit from the MySQL shell with the following command:
FLUSH PRIVILEGES ; EXIT;
In the example above:
testdb: The name of the database.
testuser: The name of the user.
remote-server-ip: The IP address of the remote system.
Step 3 – Verify Database Connection
At this point, MySQL is configured to allow remote connections from the IP remote-server-ip.
Now, log in to the remote system and connect to the MySQL server with the following command:
mysql -u testuser -h remote-server-ip -p
Provide the password for testuser and hit Enter. If everything is set up correctly, you will be able to log in to the remote MySQL server. Next, list the database with the following command:
show databases;
You should see the following output:
+--------------------+ | Database | +--------------------+ | information_schema | | testdb | +--------------------+ 2 rows in set (0.01 sec)
Conclusion
In the above guide, we learned how to enable MySQL remote connection and grant access to the remote system to connect the database, and you can now host your application using a database hosted on the remote server. Get started with a MySQL remote connection on VPS Hosting from Atlantic.Net today! We can even help with your HIPAA compliant database.
