I ran into one of those situations today where it took me forever to figure out why a MySQL database I spun up on Amazon AWS Windows Server 2016 wasn’t allowing remote connections. I finally figured it out on my own today — no thanks to the internet. Hopefully this helps you.
There are a few obvious things you need to check first and which you probably already know about to enable remote MySQL connections:
1. MySQL Account with Remote Access: First, you need a MySQL user on the database server that can connect from a host other than locally (a la “@localhost”). So, a user like “myUser@%”. The ‘%’ after the “@” is a wildcard that allows the user to connect to the server from any IP address in the world. Ideally, you would want to lock this done further than that, but it’s great to toss the wildcard in there for troubleshooting.
2. Amazon Security Groups: Ensure the Amazon security group your instance is attached to has a special inbound entry that allows TCP traffic on port 3306 from your IP address (or all IP addresses). This is easily done through the Amazon EC2 interface and there is a special type called “MySQL/Aurora” that you can select from the drop-down that will pre-fill the port-number .
3. Windows Firewall: Finally, and this is the gotcha that took me forever to figure out, you must check your server’s local inbound firewall rules. I saw a bunch of “allow” entries for MySQL already in there so I assumed it was fine. However, by default it creates these rules under the profiles “Domain” and “Private”. You have to explicitly add in another one within the profile “Public”, or edit one of the existing rules to also allow public, to finally enable remote connections.
After I did the final step, it worked like a charm.