Hi all. I just want to figure out why I can't access a MySQL server from two different machines.
The MySQL Server is residing on a web server.
I have my home machine. Logs on 100%
I am then connecting to another business machine and trying to do exactly the same as am getting an error.
From ODBC : Coonection Failed [HY000][MYSQL][ODBC 5.1 Driver] Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (10065)
From an ADO.net connection I get : Test conenction failed becuase of an error in initializing provider. Unable to connect to any of the specified MySQL Hosts.
Could it be firewall? Presumably all is open for the way out. Is it getting block coming back in?
network issues are always tricky. What I usually do in situations like this is
a) Check that we can connect to the MySQL server and that there are no firewalls blocking the connection request
b) Test using a separate application which uses the MySQL client
c) Test using a specific username and password
d) Test with my own connection string
Lets look at connectivity. The "telnet" utility is powerful as it allows you see if a connection can be established between a client machine and the server. If you run the following command:
telnet hostname_or_ip_address 3306
The 3306 is the default port on which the MySQL server is listening. I have created a mini screencast explaining the basics of client server here
If you do not get any error messages appearing then this means that a connection was established. The MySQL may return garbage but it is the connection that we are trying to establish here.
If you do not get a connection, this suggests that there is a firewall type issue on your PC or on your server.
I would suggest for the second part to download MySQL Workbench and install it on your client machine. Using the same hostname and port number establish a connection with the MySQL server specifying a username and a password.
If it is unable to establish a connection then have a look at the username and password that you are using and this is configured in the database.
Be aware that usernames and passwords are handled in a specific way in MySQL. It is possible to have multiple user accounts with the same name, different password depending on the host from which you are connecting. Make sure you have a username created in the database called name@hostname. If you want the user to be connected from any hostname then use the wildcard % i.e. name@%.
Finally if this works you need to have a look your applications configuration as it is most likely this is the source of the issue.
As for firewalls, these can exist at multiple locations, for example, on your Windows PC the firewall can block outgoing connections as well as incoming connections. You will need to determine which is the source of the problem. Also your web server database may have its own firewall which is blocking access or may even be limiting it to a specific source IP address. In other words if the IP address from which you are connecting is not configured in the firewall you may be blocked but someone else may have access.
wow. thanks so much for the detailed response. It's great to get a definitive answer which I can follow up.
I have just run the telnet command and got
Connecting to xxx.xxx.xxx.xxx...Could not open connection on the host, on part 3306 : connect failed.
All hints towards a firewall problem.
I don't have control over the firewall so will pass on my findings to the administrator.