Configuring MySQL Remote Access on Ubuntu: A Comprehensive Technical Guide

Fundamental MySQL Remote Access Configuration

Enabling remote access to MySQL on Ubuntu systems requires comprehensive configuration adjustments. The initial step involves modifying the MySQL configuration file, with the path varying by version: MySQL 5.6 and below use /etc/mysql/my.cnf, while MySQL 5.7 and above utilize /etc/mysql/mysql.conf.d/mysqld.cnf.

The core configuration parameter bind-address determines which network interfaces MySQL listens on. The default setting 127.0.0.1 restricts connections to localhost only. To enable remote access, this must be changed to either the server's actual IP address or 0.0.0.0 (listen on all interfaces). Configuration examples include:

# Specify server IP address
bind-address = 192.168.1.100

# Or listen on all network interfaces
bind-address = 0.0.0.0

After modifying the configuration, the MySQL service must be restarted for changes to take effect:

sudo systemctl restart mysql

Network Connection Verification and Monitoring

Post-configuration verification ensures MySQL is properly listening on the designated port. The lsof utility examines port 3306 status:

lsof -i -P | grep :3306

Expected output should show the MySQL process listening on the configured IP address:

mysqld  1046  mysql  10u  IPv4  5203  0t0  TCP 192.168.1.100:3306 (LISTEN)

If lsof is not installed on the system, it can be installed via package manager:

sudo apt-get install lsof

Granular User Privilege Management

Remote connection capabilities depend on proper user configuration. MySQL uses the username@host format for user identification, where host supports wildcard % to represent any host. Creating remote users requires executing the following SQL commands:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';

Subsequently grant appropriate privileges and refresh the privilege tables:

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';
FLUSH PRIVILEGES;
EXIT;

Privilege assignment should adhere to the principle of least privilege, granting only necessary database and operation permissions. For example, if a user only requires query access to a specific database:

GRANT SELECT ON mydatabase.* TO 'myuser'@'%';

Firewall Configuration Strategy

Ubuntu systems typically enable UFW firewall by default, requiring explicit allowance for MySQL port access. Configuration examples for specific IP addresses include:

sudo ufw allow from 192.168.1.50 to any port 3306

To allow access from an entire subnet:

sudo ufw allow from 192.168.1.0/24 to any port 3306

Avoid using sudo ufw allow 3306 without restrictions, as this exposes the database to significant security risks.

Common Troubleshooting Scenarios

Connection failures frequently occur in practical deployments. Typical scenarios and solutions include:

Error 1045 Access Denied: Verify user existence and correct host configuration. Use SELECT user, host FROM mysql.user; to validate user information, ensuring remote hostnames or IPs match user definitions.

Configuration Not Taking Effect: Confirm modification of the correct configuration file. In some cases, check /etc/mysql/mariadb.conf.d/50-server.cnf. The MySQL service must be restarted after every configuration change.

Firewall Blocking: Use sudo ufw status to inspect firewall rules, ensuring port 3306 is open to client IP addresses.

Security Hardening Measures

Enabling remote access necessitates complementary security protections: disable remote root login using dedicated standard users; configure SSL/TLS encryption for data transmission; regularly audit user privileges, removing unnecessary @'%' users; integrate AI monitoring tools to detect anomalous login patterns.

Through systematic configuration and rigorous security practices, efficient MySQL remote access can be achieved while maintaining data security integrity.