Atlantic.Net Blog

How to Allow Remote Connection to MySQL Database Server

Hitesh Jethva
by Atlantic.Net (125posts) under VPS Hosting
0 Comments

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.

Prerequisites

  • A fresh Ubuntu 20.04 VPS with MySQL 8 installed on the Atlantic.Net Cloud Platform
  • A root password configured on your server

Step 1 – Create Atlantic.Net Cloud Server

First, log in to your Atlantic.Net Cloud Server. Create a new server, choosing Ubuntu 20.04 as the operating system with at least 1GB RAM. Connect to your Cloud Server via SSH and log in using the credentials highlighted at the top of the page.

Once you are logged in to your Ubuntu 20.04 server, run the following command to update your base system with the latest available packages.

apt-get update -y

Step 2 – 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 3 – 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 4 – 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!

Get A Free To Use Cloud VPS

Free Tier Includes:
G3.2GB Cloud VPS Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


Looking for a Hosting Solution?

We Provide Cloud, Dedicated, & Colocation.

  • Seven Global Data Center Locations.
  • Flexible Private, Public, & Hybrid Hosting.
  • 24x7x365 Security, Support, & Monitoring.
Contact Us Now! Med Tech Award FTC
SOC Audit HIPAA Audit HITECH Audit

Recent Posts

Get started with 12 months of free cloud VPS hosting

Free Tier includes:
G3.2GB Cloud VPS Server Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


New York, NY

100 Delawanna Ave, Suite 1

Clifton, NJ 07014

United States

San Francisco, CA

2820 Northwestern Pkwy,

Santa Clara, CA 95051

United States

Dallas, TX

2323 Bryan Street,

Dallas, Texas 75201

United States

Ashburn, VA

1807 Michael Faraday Ct,

Reston, VA 20190

United States

Orlando, FL

440 W Kennedy Blvd, Suite 3

Orlando, FL 32810

United States

Toronto, Canada

20 Pullman Ct, Scarborough,

Ontario M1X 1E4

Canada

London, UK

14 Liverpool Road, Slough,

Berkshire SL1 4QZ

United Kingdom

Resources