如何从Amazon EC2实例连接非AWS托管的远程MariaDB数据库？

Hey there! Let's walk through fixing your EC2 to remote MariaDB connection problem. I’ve worked through similar setups before, so here are the key checks and fixes to get you connected:

1. Verify MariaDB's Bind Address Configuration #

MariaDB often defaults to only listening on 127.0.0.1 (localhost), which blocks remote connections. Here's how to fix it:

• Edit your MariaDB config file (usually located at /etc/mysql/mariadb.conf.d/50-server.cnf or /etc/my.cnf depending on your distro)
• Look for the bind-address line and change it to either:
  • 0.0.0.0 (allows connections from any IP, less secure but quick for testing)
  • Your EC2 instance's public IP (more secure, restricts access to only your EC2)
• Save the file and restart MariaDB: sudo systemctl restart mariadb

2. Check Remote Server Firewall Rules #

Even if your MariaDB user has access, the remote server's firewall might be blocking port 3306:

• If using ufw, allow traffic from your EC2 IP: sudo ufw allow from [EC2_PUBLIC_IP] to any port 3306
• If using iptables, add a rule: sudo iptables -A INPUT -p tcp -s [EC2_PUBLIC_IP] --dport 3306 -j ACCEPT
• Don’t forget to save your firewall rules (e.g., sudo iptables-save > /etc/iptables/rules.v4 for Debian/Ubuntu)

3. Confirm User Permissions Are Active #

You mentioned creating a user with remote access, but let’s double-check:

• Log into your MariaDB instance locally (via sudo) and run:

  SELECT user, host FROM mysql.user;
  

• Ensure your user has a host value of % (any IP) or your EC2's public IP. If you just ran the GRANT command, flush privileges to apply changes immediately:

  FLUSH PRIVILEGES;
  

• Pro tip: Avoid using the root user for remote connections. Create a dedicated user (e.g., ec2_db_user) with only the permissions it needs—this is more secure and avoids potential default restrictions on root remote access.

4. Validate EC2 Security Group Outbound Rules #

Don’t overlook your EC2 instance’s security group:

• Go to the AWS Console, navigate to your EC2 instance’s security group
• Ensure there’s an outbound rule allowing TCP traffic on port 3306 to your remote MariaDB server’s public IP (or 0.0.0.0/0 for testing)

5. Test Basic Connectivity First #

Before diving into MySQL commands, confirm the port is reachable from EC2:

• Run telnet [REMOTE_DB_IP] 3306 or nc -zv [REMOTE_DB_IP] 3306 on your EC2 instance
• If this fails, the issue is network-related (firewall/security group). If it succeeds, the problem is likely with MariaDB config or user permissions.

Start with these steps—most remote DB connection issues boil down to one of these checks. Let me know if you hit any snags along the way!

内容的提问来源于stack exchange，提问作者Damien