A website or application is usually built with a server device in which there is a web server and database on the same machine. However, with the increase in the volume of data and the number of users of the application, the server load becomes heavier. One solution to this problem is to separate the two (web server and database) on separate servers, so that the computational burden is not too heavy because each has its own server.
This article will discuss how to do the settings for remote access to MySQL databases. Suppose we have two servers, namely Server A (10.87.0.1) where MySQL is running and Server B (10.87.0.2) which will perform remote access to Server A. Here are the steps:
1. Change the mysqld.cnf configuration on Server A
In order for MySQL to receive access from outside IPs, it is necessary to change the settings in mysqld.cnf in the following way:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Look for the line containing the ‘bind-address’ like this:
bind-address = 127.0.0.1
Change 127.0.0.1 by Server B’s IP which is 10.87.0.2, as below:
bind-address = 10.87.0.2
Then Save and Close the file.
Restart the MySQL service with the following command:
sudo systemctl restart mysql
2. Create MySQL User
The next step is to create a special MySQL user for remote access purposes. For example, we will create a user with the name: admin2, password: PassAdmin2, remote IP address: 10.87.0.2, then the command is as follows:
mysql -u root -p
CREATE USER ‘admin2’@’10.87.0.2’ IDENTIFIED BY ‘PassAdmin2’;
Then give authority to the user as needed. For example, we will give full authority to user admin2 to access the entire database, then the command is as follows:
GRANT ALL PRIVILEGES ON *.* TO ‘admin2’@’10.87.0.2’;
3. Firewall settings
Still on Server A, the next step is to set the firewall to open port 3306 (the default MySQL port) so that it can be used for remote MySQL access.
sudo ufw allow from 10.87.0.2 to any port 3306
sudo systemctl restart ufw
Make sure port 3306 is allow for 10.87.0.2 (Server B) with the following command:
sudo ufw status
4. Connection Test
The last step is to test the connection from the outside (Server B). Here is the command:
mysql -u admin2 -h 10.87.0.1 -p
If you can log into MySQL on Server A, it means that the configuration settings you have done are correct. If not, please re-check the configuration settings steps from start to finish.
So that can be conveyed, thank you for visiting and hopefully useful.