How to Allow Remote Connections to MySQL

MySQL is a popular open-source relational database management system that allows users to store, manage, and retrieve data. By default, MySQL is configured to only accept connections from the localhost, which means that it can only be accessed from the same machine it is installed on. However, in some cases, it may be necessary to allow remote connections to MySQL to enable applications or users to access the database from another location.

In this article, we will guide you through the process of allowing remote connections to MySQL.

Step 1: Check MySQL Bind Address

Before allowing remote connections to MySQL, you need to check the bind address that MySQL is currently configured to listen on. The bind address is the IP address that MySQL uses to accept incoming connections. By default, MySQL is configured to listen on the localhost (127.0.0.1), which means that it can only be accessed from the same machine it is installed on.

To check the MySQL bind address, open the MySQL configuration file (my.cnf) using your preferred text editor. The location of the my.cnf file varies depending on the operating system and installation method. For example, on Ubuntu, you can find the my.cnf file in the /etc/mysql/mysql.conf.d/ directory.

Once you have opened the my.cnf file, locate the bind-address parameter. If the bind-address parameter is set to 127.0.0.1 or localhost, then MySQL is only listening on the localhost.

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

Search for the bind-address setting in the file. The default bind address is set to 127.0.0.1 as below:

[sh]
bind-address = 127.0.0.1
[/sh]

Change the bind-address value to 0.0.0.0 to enable the MySQL database server to listen on all network interfaces.

[sh]

bind-address = 0.0.0.0
[/sh]

After the change, the value will look like the above.

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
bind-address    = 0.0.0.0

Save the configuration file and close it.

Restart MySQL service to apply changes.

systemctl restart mysql.service 

Now, verify the changes and MySQL is listening on from all remote hosts. Execute the following command to view the service status:

netstat -tulpn  | grep -e "Local Address" -e "mysql" 

Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      1698595/mysqld
tcp6       0      0 :::33060                :::*                    LISTEN      1698595/mysqld

You will see that MySQL is now listening on 0 0.0.0.0:3306 local address. It means the server is now listening on all network interfaces on port 3306.

Step 2: Create MySQL User for Remote Access

To allow remote connections to MySQL, you need to create a MySQL user account that is allowed to connect from the remote location. The MySQL user account should have the necessary privileges to access the database and perform the required operations.

To create a MySQL user account, open the MySQL shell using the following command:

mysql -u root -p 

Enter your MySQL root password when prompted. Once you are logged in to the MySQL shell, enter the following command to create a new MySQL user account:

CRATE USER 'remote_user'@'%' IDENTIFIED BY 'pa$$word'; 

• remote_user – The name of the user account used for logging in to MySQL server
• % – Here `%` is used to allow all hosts, You can replace this with the hostname of the client machine.

Step 3: Grant MySQL Privileges to Remote User

After creating the MySQL user account, you need to grant the necessary privileges to the remote user to access the database and perform the required operations.

To grant MySQL privileges to the remote user, enter the following command in the MySQL shell:

GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@'%'; 

This command grants all privileges to the remote user for all databases and tables. If you want to restrict the privileges to a specific database or table, replace `*.*` with the database name and table name.

Step 4: Enable MySQL Port on Firewall

To allow remote connections to MySQL, you need to enable the MySQL port (3306) on the firewall. By default, the MySQL port is not open on most firewall configurations, which means that remote connections will be blocked.

To enable the MySQL port on the firewall, you need to add a rule to allow incoming traffic on port 3306. The exact method of adding a rule to the firewall varies depending on the operating system and firewall software that you are using.

For example, on Ubuntu, you can use the ufw firewall to allow incoming traffic on port 3306 using the following command:

sudo ufw allow 3306/tcp 

The systems with FirewallD can use the following command to open MySQL port 3306 for everyone, but it it not recommended for production servers.

sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp 
sudo firewall-cmd --reload 

Step 5: Test Remote Connection to MySQL

After completing the previous steps, you can test the remote connection to MySQL using a MySQL client or command-line tool. You can use any MySQL client that supports remote connections, such as MySQL Workbench or HeidiSQL.

To connect to MySQL remotely, you need to specify the IP address or hostname of the MySQL server, along with the MySQL username and password that you created earlier.

For example, if the MySQL server IP address is 10.0.0.100 and the remote MySQL user is remote_user, you can connect to MySQL using the following command:

mysql -u remote_user -p -h 10.0.0.100 

Enter the password for the remote MySQL user when prompted. If the connection is successful, you will be logged in to the MySQL shell, and you can perform the required database operations.

Conclusion

Allowing remote connections to MySQL can be a useful feature when you need to access the database from a different location or application. However, it is important to ensure that the MySQL server is secured properly to prevent unauthorized access or data breaches. You can use additional security measures such as SSL/TLS encryption, two-factor authentication, or network segmentation to further enhance the security of your MySQL server.