Comprehensive Technical Guide: Connecting to MySQL on Amazon EC2 from Remote Servers

Problem Context and Common Error Analysis

In cloud computing environments, connecting remotely from local machines to MySQL databases on Amazon EC2 instances is a common development requirement. However, many developers encounter typical connection errors when executing the command mysql -uUSERNAME -hEC2_IP -pPASSWORD: ERROR 2003 (HY000): Can't connect to MySQL server on 'IP' (110). Error code 110 typically indicates connection timeout, suggesting network-level access is being blocked.

AWS Security Group Configuration: The First Line of Defense

Amazon EC2 instance security groups act as virtual firewalls controlling traffic to and from instances. By default, security groups may only allow SSH (port 22) access, while MySQL's standard port 3306 remains closed. This is one of the most common reasons for remote connection failures.

Detailed security group configuration steps:

1. Log into AWS Management Console and navigate to EC2 service panel
2. Select "Security Groups" from the left navigation menu
3. Locate and select the security group associated with the target EC2 instance
4. Click the "Inbound Rules" tab
5. Click "Edit inbound rules" button
6. Add new rule: Type "MySQL/Aurora", Protocol TCP, Port range 3306
7. In the Source field, choose based on security requirements:
   • 0.0.0.0/0: Allow access from any IP address (testing environments)
   • Specific IP address or CIDR range: Recommended for production environments
8. Save rule changes

Example AWS CLI command for security group configuration:

aws ec2 authorize-security-group-ingress \
    --group-id sg-xxxxxxxx \
    --protocol tcp \
    --port 3306 \
    --cidr 0.0.0.0/0

MySQL Bind-Address Configuration: Removing Local Restrictions

MySQL is configured by default to listen only on local loopback address (127.0.0.1), a design that enhances security but prevents remote access. Modifying MySQL configuration files is necessary to allow external connections.

On Debian/Ubuntu-based systems, primary configuration files are typically located at:

• /etc/mysql/my.cnf
• /etc/mysql/mysql.conf.d/mysqld.cnf
• /etc/mysql/mysql.cnf

Key configuration steps:

1. Open MySQL configuration file with a text editor:

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

2. Locate the bind-address parameter under the [mysqld] section
3. Change the default value bind-address = 127.0.0.1 to:

   bind-address = 0.0.0.0

4. Save the file and exit the editor

Important note: Some systems may have multiple configuration files. After modification, verify configuration effectiveness using the netstat command:

netstat -nat | grep :3306

Expected output should show MySQL listening on all network interfaces:

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN

If it still shows 127.0.0.1:3306, the configuration file modification hasn't taken effect, and other potentially overriding configuration files should be checked.

MySQL User Privilege Management: Secure Access Control

Even with network connectivity established, MySQL's own user privilege system may still prevent remote access. By default, MySQL users are typically restricted to connections only from localhost.

Creating MySQL users that support remote access requires the following steps:

1. Log into MySQL as root user:

   mysql -u root -p

2. Create new user with password:

   CREATE USER 'remote_user'@'%' IDENTIFIED BY 'secure_password';

3. Grant appropriate database privileges:

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

4. Flush privileges to make changes effective:

   FLUSH PRIVILEGES;

5. Exit MySQL:

   EXIT;

Security recommendation: In production environments, avoid using the % wildcard and instead specify specific client IP addresses or ranges:

CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'password';

Operating System Firewall Verification: Final Barrier

Beyond AWS security groups, the EC2 instance's operating system firewall (such as iptables or ufw) may also block MySQL port access. Ensure firewall rules allow inbound connections on port 3306.

For Ubuntu systems using ufw:

sudo ufw allow 3306/tcp
sudo ufw reload

For CentOS/RHEL systems using firewalld:

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

Connection Testing and Troubleshooting

After completing all configurations, test connectivity from a remote client. Use the following command to verify connection:

mysql -h EC2_PUBLIC_IP -u remote_user -p

If connection still fails, follow this troubleshooting sequence:

1. Verify the EC2 instance's public IP address is correct
2. Test port connectivity using telnet:

   telnet EC2_PUBLIC_IP 3306

3. Check MySQL error log for detailed information:

   sudo tail -f /var/log/mysql/error.log

4. Confirm MySQL service is running:

   sudo systemctl status mysql

Security Best Practices

When enabling remote MySQL access, consider the following security measures:

• Implement strong password policies, avoiding default or weak passwords
• Limit security group source IP ranges, avoiding opening to 0.0.0.0/0
• Consider using SSH tunnels for encrypted connections
• Regularly update MySQL and operating system security patches
• Enable SSL/TLS encrypted connections for MySQL
• Implement additional protection with Network Access Control Lists (NACLs)

By systematically configuring AWS security groups, MySQL bind-address settings, user privileges, and firewall rules, secure connections can be successfully established from remote servers to MySQL databases on Amazon EC2. Each aspect requires careful configuration and verification to ensure both functional requirements and system security are maintained.