Search code examples
mysqlwindows-server-2012-r2mysql-8.0

MySQL port 3306 not accessible remotely. port enabled on Windows server firewall

It looks like weird happening, I've followed and fixed all the possible things but still MySQL port 3306 is not accessible remotely.

Here's my setup:

Machine: Windows Server 2012 R2

MySQL Server: 8.xx

Settings have been applied:

  1. In my.ini bind-address did to 0.0.0.0 and and thus after exec command netstat -an this is what I get TCP 0.0.0.0:3306 0.0.0.0:0 LISTENING
  2. From Windows Firewall I've enabled port 3306 inbound/outbound both. and set that to allow edge traversal as well
  3. From the MySQL server I've created a user with % all host and then provided permission to all databases as well. which has been checked

I guess these are the actions I took afterward. But this is not working at all.

When I'm trying to do telnet server_ip 3306 error: Connecting To server_ip ...Could not open a connection to the host, on port 3306: Connect failed from other machines. The local host is working fine.

I don't see any other workaround that could else cause this issue?

Solution

  • I've tried installing MySQL 8.0 on my local PC:

    1. With default setting
    2. Firewall turned on
    3. No firewall exception added manually

    Then I tried to connect from a remote Windows Server 2016 Standard using telnet local_ip port through command-prompt and I received this:

    J
 8.0.27
       ZPhqTaMC v|)64Pthmmysql_native_password

    Which means the connection has been established. I test it further using SQLYog tool: enter image description here
    and for sure I can connect to it.

    What I did next was I went to my firewall setting here

    Control Panel\All Control Panel Items\Windows Defender Firewall\Allowed applications

    and removed all related firewall exception that was made during the installation so I've removed the following:

    mysqld
port3366 <-- this is the port I used
port33060

    And when I tried to connect remotely, it tells me this:

    Connecting To local_ip...
Could not open connection to the host, on port 3366: Connect failed

    This confirms my suspicion that firewall have blocked the connection. Now, OP said that the port have been added to the firewall but still can't connect so my first step after that is adding back mysqld into the firewall exception.

    1. In the firewall setting click "Allow another app"
    2. The "Add and app" dialog opens then click "Browse"
    3. Locate mysqld from MySQL folder - usually found in C:\Program Files\MySQL\MySQL Server 8.0\bin
    4. Click "Add" then tick both "Public" and "Private" checkbox enter image description here

    Once I've done that, I tried connecting and it's successful. Note that I did not re-add the ports that I've removed previously but I still can connect remotely. Also, I didn't add bind-address in my.ini setting.