Trying to connect remotely to a MySQL server

Hi,

I'm running MySQL version 5.6.22 on a Win 2012 server and am trying to create a web interface which will allow me to manage users remotely.

I've got the PHP code working locally but am now trying to connect to this server remotely and running into problems. The Port is open from this server to our LAN address but when I try to connect using telnet I get the following error:

Host 'xxx.xxx.xxx.xxx' is not allowed to connect to this MySQL server.

So I thought I would dial it back a bit and test locally on the server.

So I'm now testing locally on the server and I can connect using:

mysql -h localhost -u administrator -p

but if I try:

mysql -h xxx.xxx.xxx.xxx -u administrator -p

It prompts to enter the password as usual but then gives me:

ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (10061)

I'm sure I have perms to connect from any host as I ran:

SELECT User,Host FROM mysql.user;

and got:

+---------------+-----------+
| User          | Host      |
+---------------+-----------+
| administrator | %         |
| root          | 127.0.0.1 |
| root          | ::1       |
| administrator | localhost |
| root          | localhost |
+---------------+-----------+

I've now come screeching to a halt in my knowledge of MySQL and could really do with some guidance please.

Thanks.
LVL 1
LetterpartAsked:
Who is Participating?
 
LetterpartConnect With a Mentor Author Commented:
Yes. as per above.

Now, I've just run the following command:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'thepassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Open in new window


And am now able to connect using:

mysql -h xxx.xxx.xxx.xxx -u administrator -p on the local machine. So that's me one step closer. Now need to test connecting from a remote machine.
0
 
Julian HansenCommented:
Have you checked Windows Firewall on the 2012 server to ensure that port 3306 is open and allowing you through.
0
 
LetterpartAuthor Commented:
Sorry, yes.

The Windows Firewall is switched off for testing.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
LetterpartAuthor Commented:
Just to add.

When i test it locally with

mysql -h xxx.xxx.xxx.xxx -u administrator -p

It prompts to enter the password as usual but then gives me:

ERROR 2003 (HY000): Can't connect to MySQL server on 'xxx.xxx.xxx.xxx' (10061)

So it is def getting through to MySQL.
0
 
Julian HansenCommented:
Is the IP you are connecting to the server on the internal network address or external?
0
 
LetterpartAuthor Commented:
I'm testing this on the local machine to rule everything else out.
0
 
Julian HansenCommented:
So mysql -h localhost works?
0
 
Julian HansenConnect With a Mentor Commented:
That should work now - sorry I thought I had already done a post regarding GRANT PRIVILEGES - but seems it did not go through.
0
 
LetterpartAuthor Commented:
I resolved the issue myself but appreciate the time Julian put in to the question to help resolve it so would like to assign him points.
0
 
Julian HansenCommented:
Thank you and you are welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.