How to enable remote access on MySQL?

Introduction

When a website or application initially opens, database backend and server are typically hosted on the common system. However over time, this setup may become overwhelming and hard to scale. By creating a remote database, which enables the database and server to develop separately on their own machines, it is easy to split these functions.

The most typical issues that clients face is when trying to set up a remote MySQL database are fact that by default a MySQL server is only setup to search for local connections. The setting configuration will not function for remote database configuration since MySQL has to search for an external IP address, so that server may be accessed.

Prerequisite

You need to have the following setup on your system and knowledge of using them:

• An Ubuntu 20.04 server

• MySQL installed on your Ubuntu system

• Knowledge of CLI

Step-1: Changing the bind address

To enable remote access, open the mysqld.cnf file:

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

• Move down to the bind-address directive. It will look like this:

• Save the file using CTRL + X, Y, then press ENTER. After, making the required changes.

• After this restart MySQL, to see changes you made to mysqld.cnf file working:

sudo systemctl restart mysql

• You must modify a current MySQL user account to connect from the remote server rather than localhost if you intend to connect to the database using that account from your remote host. Access the MySQL client in this manner when logged in as the root MySQL account or some other privileged user:

Step-2: Creating new user and giving access priveleges

sudo mysql

• Type the following command to create a new user that will accept connections from remote host only:

CREATE USER 'john'@'remote_server_ip' IDENTIFIED BY 'password';

• Give the user the rights that are necessary for your requirements.

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'john'@'remote_server_ip' WITH GRANT OPTION;

• Use the FLUSH PRIVILEGES command after that to clear the memory used:

FLUSH PRIVILEGES;

• exit your MySQL client:

exit

If a firewall set up on the database server being used. To enable traffic to MySQL, you have to open the port 3306, the default port for MySQL.

• Using the following command, you may give a certain computer exclusive access to connect to the database remotely if you intend to gain access to the server from that machine only. Ensure that remote IP address is changed to the precise IP of the device you wish to connect to:

sudo ufw allow from remote_IP_address to any port 3306

• You can use this command to provide other machines temporary access to the database if you ever need to use it from another system. Just make sure to provide each IP address's location.

As an alternative, you may use the command below to permit connection to your database from any other IP address:

Caution: Anybody will be able to access your MySQL database with this command. If your database contains any sensitive information, do not execute it.

sudo ufw allow 3306

• Thereafter, try remote database access from a different machine.

• It should be noted that you must try to open database using the computer associated with the specified IP address if you created a firewall rule to only accept connections from that address.

mysql -u user -h database_server_ip -p

Conclusion

Now, as we have configured the required settings on in MySQL and can access it remotely from any IP, then bind-address rule in the configuration was the problem if you can access your database. Worth noting that setting it as 0.0.0.0 makes your server vulnerable as it accepts connections from all IP addresses.