MySQL 8: How to enable/disable remote connections

Introduction

MySQL is a robust and widely used open-source database management system. One of the more advanced features its administration includes the enabling and disabling of remote connections. This feature allows users to access and manage databases from remote locations. Whether you need to grant access to a remote developer or ensure the security of your database by restricting remote access, managing this feature is a crucial skill for a database administrator.

In this tutorial, we’ll explore how to enable and disable remote connections to your MySQL 8 server. We will start from the basic concepts and then demonstrate several operating techniques so you can manage these settings effectively.

Prerequisites

• MySQL Server 8 installed and running
• Administrative access to the MySQL server
• Basic knowledge of SQL and database concepts
• Access to a command-line interface (CLI)

Understanding MySQL User Accounts

Before adjusting remote connection settings, you need to understand MySQL user accounts and how they work. In MySQL, each user account is defined not only by its username but also by the host from which it can connect. The ‘host’ part is the key to controlling remote access. It can be an IP address, a domain name, or the wildcard characters ‘%’ or ‘_’.

Granting Access to a Remote User

GRANT ALL PRIVILEGES ON *.* TO 'username'@'remote_host_ip' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

The above SQL query grants all privileges on all databases to a user with specified credentials, but only when the connection is made from the specified remote IP.

Code Output

Query OK, 0 rows affected (0.01 sec)

Revoking Access from a Remote User

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'remote_host_ip';
FLUSH PRIVILEGES;

This SQL command removes all privileges previously granted to a remote user. The FLUSH PRIVILEGES command tells the MySQL server to reload the grant tables, thus applying the changes immediately.

Configuring MySQL for Remote Access

By default, MySQL is often configured to disallow remote connections for security reasons. You can alter this behavior by modifying the MySQL configuration file.

Locating the MySQL Configuration File

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

Edit the file by looking for the line with bind-address directive.

Allowed Remote Connections

bind-address = 0.0.0.0

Setting the bind-address to 0.0.0.0 will allow MySQL to accept connections from any IP address. Please make sure your firewall is configured to restrict access to only trusted users.

Restarting MySQL Service to Apply Changes

sudo systemctl restart mysql

Code Output:

 [ ok ] Restarting mysql (via systemctl): mysql.service.

Advanced Configuration

In more advanced scenarios, you may want to restrict access to specific network interfaces or configure MySQL to listen to a non-standard port.

Specific Network Interfaces

bind-address = 192.168.1.100

This will configure MySQL to listen for connections on a specific network interface. This is useful in server environments with multiple network interfaces.

Non-Standard Port

[mysqld]
port = 3307

Remote clients will now need to specify the non-standard port when they connect. This can add a layer of security by obscuring the expected communication port for MySQL.

Using Firewalls to Control Access

Another essential aspect of controlling remote access to MySQL is through firewalls. A well-configured firewall can enable necessary remote access while providing security from unauthorized attempts.

Using UFW on Ubuntu

sudo ufw allow from remote_IP_address to any port 3306
sudo ufw status verbose

Adjust the remote_IP_address placeholder with the actual IP address of the client that should be granted access.

Using iptables

sudo iptables -A INPUT -p tcp -s remote_IP_address --dport 3306 -j ACCEPT
sudo service iptables save

As with UFW, remember to replace remote_IP_address with the actual IP and ensure the correct port if you are using a non-standard port.

Testing Remote Connection

Once you have configured your server and firewall, test the remote connection. You can use MySQL command-line client from a remote machine:

mysql -h server_ip -u username -p

If you’ve set everything up correctly, you should be able to connect to the MySQL server using the specified user account.

Conclusion

Understanding how to manage remote connections in MySQL is critical for both functionality and security. Whether you are enabling connections for collaborative development or locking down the system for secure operations, following the steps outlined in this tutorial will help ensure that you have the desired access control over your MySQL instance.