How to Enable Remote Connections on MySQL Database Running on VPS

Allowing remote connections on MySQL database server allows users to access and interact with your MySQL database from a remote location. This can be advantageous in a number of scenarios, such as:

1. Collaborating with remote team members: Enabling remote connections allows your team members to access the database from their own machines, even if they are not physically located in the same office or on the same network.
2. Accessing your database from a web server: If you have a web server that needs to interact with your database, enabling remote connections is necessary for the two systems to communicate.
3. Accessing your database from a mobile app: If you have a mobile app that needs to interact with your database, enabling remote connections is necessary for the app to access the data.
4. Backup and disaster recovery: Enabling remote connections allows you to set up a secondary server that can replicate the data from the primary server in real-time. This can help you quickly recover your data in the event of a disaster.

If you've set up a MySQL database on a VPS such as AWS, DigitalOcean, Linode, Lightsail or Vultr; you may want to enable remote connections to allow other users or applications to access your database. In this blog post, I'll walk you through the steps to do it.

Step 1: Update MySQL Configuration File: mysqld.cnf

The first step is to update the MySQL configuration file to allow remote connections. You can do this by editing the mysqld.cnf file located in the /etc/mysql/mysql.conf.d/ directory. Open the file using your favorite text editor or nano like this:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Look for the following line:

bind-address = 127.0.0.1

This line tells MySQL to only listen for connections in socket mode on the local machine. You need to comment out this line by adding a # at the beginning of the line. Or you can also change the bind address to the following, telling MySQL server to start in TCP mode.

bind-address = 0.0.0.0

Save the file (Ctrl+O) and exit (Ctrl+X) the text editor.

Step 2: Create a New MySQL User who can Access the Database Remotely

Next, you need to create a MySQL user account that can connect to the database remotely. You can do this by logging in to the MySQL server using the mysql command-line tool:

Enter your MySQL root password when prompted. Once you're logged in,

mysql -u root -p

If you don't know the root password, then you can access mysql by running this command.

sudo -i mysql

Create a new user account with the following command:

Syntax: CREATE USER 'remote_user'@'%' IDENTIFIED with mysql_native_password BY 'password';

Example: CREATE USER 'amara'@'%' IDENTIFIED with mysql_native_password By 'Amara@Password';

Replace remote_user with the username you want to use for the remote connection, and password with a strong password for the user account.

Step 3: Grant Database Access to the Newly Created MySQL User

After creating the user account, you need to grant remote access to the user account. You can do this by running the following command:

Syntax:GRANT ALL ON *.* TO 'remote_user'@'%';

Example:GRANT ALL ON *.* TO 'amara'@'%';

This command will grant privileges to the remote_user (which is amara in our case) for all databases and tables on the MySQL server. If you want to limit the privileges to specific databases or tables, replace *.* with the name of a specific database or a table.

Step 4: Restart MySQL Server

Finally, you need to restart the MySQL server to apply the changes you made to the configuration file. You can do this by running the following command:

sudo systemctl restart mysql

Or, you can also use:

sudo service mysql restart

This command restarts the MySQL server on your VPS.

At this point, everything is configured. You can now easily connect to the MySQL remotely from anywhere. You can use a MySQL client to connect or, you can connect to a remote database from MySQL CLI as well from your local machine.

If you need a good MySQL client app for desktop then you can try HeidiSQL, PHPMyAdmin, etc.

The final conclusion:

Enabling remote connections on MySQL running on a VPS is quite straightforward process. By following the steps outlined in this blog post of mine, you can allow other users or apps to access MySQL database remotely. This method will work on VPS created on DigitalOcean, AWS, Google Cloud, and Microsoft Azure.

However, you may have to make sure that MySQL port 3306 is opened via Firewall. If you are using AWS EC2 or Lightsail then you will have to open the port 3306 in the Networking section by creating an inbound traffic rule.