How To Connect To A Mysql Database Remotely

Photo by Jan Antonin Kolar on Unsplash

This article explains how to setup a remote connection to an external mysql server from your application, my example makes use of a laravel app to connect to a remote mysql server. Regardless, this method works for all kinds of applications. So, lets dive in!

Make sure you have the following ready somewhere:
The root user password for your mysql server.
You can use the following command to get it.
sudo vim /root/.my.cnf

Step 1:

Log in to your MySQL server locally as the root user by using the following command:
# mysql -u root -p
You’ll be prompted to input the password for the root user.

Step 2:

Use a GRANT command in the following format to enable access for the remote user. Ensure that you change remoteDatabase to the name of the remote database you will be connecting to and remote_user_passwordto the password that you want remoteUserNameto use:

GRANT ALL ON remoteDatabase.* TO remoteUserName IDENTIFIED BY 'remote_user_password';

This statement grants ALL permissions to the new user when the user connects by using the specified password.

Step 3:

One of the more common problems that users run into when trying to set up a remote MySQL database is that their MySQL instance is only configured to listen for local connections. This is MySQL’s default setting, but it won’t work for a remote database setup since MySQL must be able to listen for an external IP address where the server can be reached. To enable this, open up your mysqld.cnf file:
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

Navigate to the line that begins with the bind-address directive. It will look like this:

By default, this value is set to 127.0.0.1, meaning that the server will only look for local connections. You will need to change this directive to reference an external IP address. For the purposes of troubleshooting, you could set this directive to a wildcard IP address, either *, ::, or 0.0.0.0:

bind-address = 0.0.0.0

After changing this line, save and close the file (esc, :wqif you edited it with vim).

Then restart the MySQL service to put the changes you made to mysqld.cnf into effect:

sudo systemctl restart mysql

Step 4:

Lastly, assuming you’ve configured a firewall on your database server, you will also need to open port 3306 — MySQL’s default port — to allow traffic to MySQL.

If you only plan to access the database server from one specific machine, you can grant that machine exclusive permission to connect to the database remotely with the following command. Make sure to replace remote_IP_address with the actual IP address of the machine you plan to connect with:

sudo ufw allow from remote_IP_address to any port 3306

If you need to access the database from other machines in the future, you can grant them access on an ad hoc basis with this command. Just remember to include their respective IP addresses.

Alternatively, you can allow connections to your MySQL database from any IP address with the following command:

Warning: This command will enable anyone to access your MySQL database. Do not run it if your database holds any sensitive data.

sudo ufw allow 3306

That’s all!
You should be able to connect remotely to a mysql server!

Article reference:
https://www.digitalocean.com/community/tutorials/how-to-allow-remote-access-to-mysql
https://forums.cpanel.net/threads/the-system-failed-to-connect-to-the-mysql-database-issue.637229/