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:
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.
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.
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:
CREATE USER 'remote_user'@'%' IDENTIFIED with mysql_native_password BY 'password';
CREATE USER 'amara'@'%' IDENTIFIED with mysql_native_password By 'Amara@Password';
remote_user with the username you want to use for the remote connection, and
password with a strong password for the user account.
After creating the user account, you need to grant remote access to the user account. You can do this by running the following command:
GRANT ALL ON *.* TO 'remote_user'@'%';
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.
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.
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.