0

As the title says I want to connect remotely to mysql and it is on windows server but I got this error message.

Cant connect to mysql server on X.X.X.X I am tring it with HeidiSql

I have MariaDB installed.

Also I can connect to server using remote connection.

Server is running and can connect to mysql localy

What have I tried:

  1. I located my my.ini file and checked that I dont have one of these commands:

Skip-networking

bind-address = some IP

I didnt have them there in the first place

  1. I logged in my MariaDB terminal and granted all permisions to user using this:

    GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.100.%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION;

  2. I have added port 3306 to windows firewall

  3. I guess it is worth mentioning that I cant ping that server either if I do

ping X.X.X.X

it returns:

Pinging X.X.X.X with 32 bytes of data: Request timed out. Request timed out. Request timed out. Request timed out. Ping statistics for X.X.X.X: Packets: Sent = 4, Received = 0, Lost = 4 (100% loss),

I have spent many hours on forums and tried all instructions but still cant connect.

Any help would be much appreciated!

Davis
  • 361
  • 1
  • 5
  • 19

1 Answers1

0

The detailed problem can be from two different causes:

First option:

MySQL is binded to 127.0.0.1 or no ip at all. Check netstat on windows cmd for locating 3306 port opened to mysql process and binded to your network LAN interface or 0.0.0.0 (not to loopback / 127.0.0.1 // ::1)

For check netstat, use netstat -a -n -o, you can see in the response the PID of the process too.

For check MySQL is working on the same machine, try to connect from local console using mysql -h 127.0.0.1 (for localhost connection) or mysql -h (your LAN IP) (If you have mysql binded only to your LAN IP address).

Second option:

If first test are OK. Maybe windows firewall is not configured correctly. Try to disable windows firewall (double check disabling it). The error response on your question are because windows firewall are active and blocking connections. When you have verified you can reach 3306 with firewall off, enable it and configure. Check always you're opening the port on the required network profile (Remember, windows firewall can have different rules for private / public connections).

For check firewall correctly configured, try to access to the 3306 port on the computer from another comp on the LAN.

If you have a huge LAN network with VLANs and similar technologies, check your routing topology. Also, remember always for initial checks, disable windows firewall. Your ping are failing because firewall are enabled.

Community
  • 1
  • 1
Sakura Kinomoto
  • 1,784
  • 2
  • 21
  • 30
  • When I check netstat under the PID I cant find port 3306. What would be the proper way to open it? However under the local address column I see this [::]:3306 Thats probably because I tried to add 3306 port to firewall exceptions. – Davis Dec 05 '18 at 16:52
  • If you see [::]:3306 is because port is opened to all ip addresses on IPv6 space. Normally, these bind are multi stack and open it for IPv4 and IPv6, so it's ok. Try to connect from local cmd for testing connectivity (check my first option) – Sakura Kinomoto Dec 05 '18 at 16:58
  • If I try mysql -h 127.0.0.1 then I got "ERROR 1045 (28000): Access denied for user 'windows_admin'@'localhost' (using password: NO)" If I try mysql -h X.X.X.X then I got back "ERROR 2002 (HY000): Can't connect to MySQL server on 'X.X.X.X' (10060)" So the second one is the same error as before when I tried to connect from heidiSql – Davis Dec 07 '18 at 08:44
  • Well, then the problem are simple. Your sql server are binding to localhost. You need to change in my.cnf the bind-address to 0.0.0.0 or your lan ip – Sakura Kinomoto Dec 07 '18 at 09:14
  • Or, the firewall are blocking connection. Try to disable any firewall software before doing new checks – Sakura Kinomoto Dec 07 '18 at 09:16
  • I cant find my.cnf file I have only my.ini and if I set bind-address = 0.0.0.0 then there are no changes If I set bind-address (MyserverIP) then I cant restart mysql – Davis Dec 07 '18 at 12:01
  • Nope. that is the weird thing. Could it be a router or some kind of anti-virus issue? – Davis Dec 10 '18 at 07:25
  • If you're connecting from LAN (with same ip address subnet) and not from Internet, it cannot be a routing problem. Some antivirus software included firewall service. – Sakura Kinomoto Dec 10 '18 at 09:43