How to allow remote access to MySQL

Remote access to MySQL is essential for modern web applications and database management systems. It allows administrators and developers to connect to the database server from any location, which is especially useful for working in distributed teams or supporting applications hosted on cloud platforms. Properly configured remote access to MySQL provides flexibility and efficiency in data management, and simplifies the process of database monitoring and administration. However, it is important to remember that opening access to the server can create potential security risks if proper precautions are not taken. In order to allow remote access to MySQL, you must make changes to the MySQL configuration file. In this file, you should change the bind-address parameter so that the server listens to requests not only from the local host, but also from other IP addresses. You should also make sure that the user who is granted remote access has the correct privileges and password to connect. It is important to remember to configure the firewall to allow connection to the correct port, usually port 3306 for MySQL. Thus, proper configuration of remote access to MySQL is key to ensure efficient database operation, while taking into account security aspects to protect data from unauthorized access.

Configuration file of MySQL

In order to allow remote access, you first need to understand which MySQL configuration file is used by the service. There are several ways to do this. I suggest one of them below:
mysql --help | grep /my.cnf | xargs ls 2>/dev/nullThis command will display the path to the file, most commonly /etc/mysql/my.cnf. The contents of the file are roughly as follows:


Together with this configuration file, other files are loaded, the directory of which is specified by the includedir parameter. It is necessary to check the presence of the bind-address parameter in all these files, in addition to the main /etc/mysql/my.cnf. You can do this with the command:
grep includedir  /etc/mysql/my.cnf | awk '{print $2}' | while read cnf
do
    grep -R bind-address ${file}
doneEmpty output means that the parameter was not found. If the parameter has already been found, it should be edited in the found file.

For Debian and Ubuntu operating systems, the basic MySQL server parameters are written in the /etc/mysql/mariadb.conf.d/50-server.cnf file:

bind-address            = 0.0.0.0

where 0.0.0.0.0 indicates that connection from any address is allowed. If necessary, you can also specify a specific IP address, but I advise you to do it in the MySQL user parameters.

To apply the parameters, restart the service with the following command:
systemctl restart mysql

Remote access for MySQL user

Now we need to set the existing MySQL user permission to connect from the desired address. Or, alternatively, create a new user.

Changing MySQL user permissions

In order to change the user address of the connection, you can use the RENAME USER command, which must be entered in the MySQL shell. In the command below, replace username with your user name and ip_address with the IP address of the client from which the connection to the database server will be made:
RENAME USER 'username'@'localhost' TO 'username'@'ip_address';

Create a MySQL user with remote access

If there is no user, create one. In the MySQL shell, execute the SQL command:
CREATE USER 'username'@'ip_address' IDENTIFIED BY 'password';where password is the username password.

After creating a user, it is necessary to give him/her the necessary access rights. This issue is described in detail in the article Creating a MySQL user and setting permissions.

Configuring a firewall for remote MySQL access

After remote access has been configured in MySQL itself, you should open access in the server's firewall. First, let's check which port our database service is listening to:
ss -ltpn |grep -E 'mariadb|mysql'
In this output, 127.0.0.1 is the host. If we did everything correctly earlier, the host should be 0.0.0.0.0. And 3306 is the port on which MySQL is running.

Let's check to see if there are any firewall bans at all on the server, which is iptables:
iptables -nL | grep policy

• policy ACCEPT indicates that the default access is open. Then look at the rules, if they have prohibitions. If not, you don't need to open anything - everything is open;
• policy DROP - access is denied by default. Define a permissive rule.

The resolving rule itself will be of the form:
iptables -A INPUT -p tcp --src 1.2.3.4 --dport 3306 -j ACCEPTwhere 1.2.3.4 is your IP address that we previously set for the user.

This rule should be executed in the console and also added to the list of loaded rules when starting the server.