Machine A 10.0.0.5
– running Suse 11.0 and MySQL 5.0.51a
Machine B 10.0.0.10
– running Windows XP SP3
When I install some software onto Machine B it wants me to install MySQL onto Machine B, I don’t want to as I already have MySQL running on Machine A. However, if I tell Machine B to use the MySQL server on Machine A, I get told that “host can not connect”.
I have done a search on the MySQL pages and Google and can find how to GRANT permissions for users to databases, but I can’t seem to figure out how to allow a machine to connect in (so it can create databases etc)
Example:-
GRANT ALL PRIVILEGES ON databasename. TO username@10.0.0.10 IDENTIFIED BY ‘password’*
Although this works, in that username can now do all actions on the DATABASE from 10.0.0.10, I wonder how can I grant permission to the server to create the databases required by the application?
I hope this makes sense but if you need any clarification, please ask!!
I have never used Windows and will not be able to give you any useful advice regarding machine B. But I can add some thoughts in the hope to get you airborne.
Make sure that machineA has a proper my.cnf i.e. you have not enabled ‘skip-networking’.
You can’t tell machineB as such to use the MySQL server on machineA. Instead, the application using MySQL on machineB must be configured to contact someuser@machineA. In Linux style this would mean to execute this on machineB:
mysql -h 10.0.0.5 -u someuser -pPassword mydatabase
The grant on machineA (where MySQL resides) must allow access for someuser@10.0.0.10 including CREATE. Check for Create_priv=Y in table ‘user’ database ‘mysql’ on machineA.
Can you try with the command line client if you can connect from machineB to machineA?
In contradiction with 2. there is a way for an application on machineB to connect to machineB locally and forward this to machineA. Create an ssh tunnel forwarding port 3306 from machineB to machineA. If machineB were running Linux it would look like:
I have no idea if this is possible in Windows, sorry. Then a final remark: MySQL is often in troubles when DNS is not working properly. I see that you are using IP numbers and that should be ok, but you better check this too.
OTOH “host can not connect” seems rather basic to me. When no connection, then no login, let alone that it comes to the differences in intepreting commands. So when this error is anything near the truth (I can not say, having no MS experience):
I a should check if MySQL is listening
netstat -tlp
You could check by connecting to the port from outside with
telnet A <MySQL-port>
(or some other tool available on B), when refused there is something blocking things (Firewall, …). When allowed, just disconnect, but you know more.
This is commented out, so it is “off”.
I appreciate this is a linux forum - as an expert user in Windows, it’s OK, I can handle THAT side of things!
I used the same user on MachineB as I did for my AutoMySQLBackup.sh script (that you also helped with on another thread) so I know it works locally but I do not think I have done a GRANT for the user@MachineB - maybe this is all that is wrong …
Hmm, i did notice on MachineB’s application it had a default “servername” which was localhost … I think I only tried the IP address instead of the computer name. Maybe it is as simple as that …
OK, so thank you Voodoo, I will attempt to:-
Create a new user on MachineA and give this user CREATE access to . using your modification of my (almost correct!) original attempt:-
GRANT ALL PRIVILEGES ON . TO ‘machineBuser’@‘10.0.0.10’ IDENTIFIED BY ‘password’;
When prompted by the application on MachineB, I will attempt to use DNS instead of IP address …
Aha, but again reading the message and trying to interprete, it says this **host **is not allowed, nothing about a user (which would e impossible because you only tried to make a TCP/IP connection and not to login).
So it seemss it is listening, but only allowing cetain hosts. This is analogue to other programs like e.g. xinetd, where you can conigure that only certain hosts (or networks) are allowed. Now I am not good enough MySQL guru, but I should look for a place where you can define allowed hosts.
hence my original post
interestingly enough, after creating the user I COULD telnet into the MYSQL server from another host BUT silly me - I turned off machineB last night, so for my test today I used MachineC (Win Server 2003) 10.0.0.20
Of course, I modified the GRANT statement to the right IP address but when I tried to install the application, it did not like Win Server and so would not install as far as the DB connection :\
I’ve looked all over for such a configuration entry, I would expect it to be in my.cnf but alas, it is not
When things don’t work let’s start verifying the basics:
You can ping host A from host B (I’m sure you already checked this).
On host A: prompt> mysqladmin ping
to verify that mysql is running.
On host A: let’s re-check the grants you have:
echo "SELECT * FROM user WHERE Host = '10.0.0.10';" | mysql -E mysql
Check that MySQL is listening on port 3306. Do on host A:
echo "SHOW variables LIKE 'port';" | mysql -N
Check the firewall. FW_SERVICES_INT_TCP or FW_SERVICES_EXT_TCP (depending on your setup) should include port 3306.
Then you should be able to connect using the command line client on host B
mysql -h 10.0.0.5 -u someuser -pPassword database
Most or all of points 1. to 5. should arready be ok, because you reported previously:
telnet 10.0.0.5 3306
Host 10.0.0.10 is not allowed to connect to this MySQL server
The response is coming from the MySQL server on Host A. This is not perfect but still very good, because it shows that the server is responding, but is refusing the connection. I guess that there is still a problem with the grants. Probably telnet from host B does not provide for a proper username in accordance with the name in MySQL’s user table. You should really try the mysql command line tool and report what the error message is.
Usernames must match. And in case there is some DNS (or for any Windows-specific reason), host B may identify itself as ‘hostname.domain.tld’ (you name your own) or even just as ‘hostname’ to the MySQL server. In this case you need another grant (not using IP numbers)
GRANT ALL ON *.* TO 'user'@'hostB.domain.tld' IDENTIFIED BY 'Password';
GRANT ALL ON *.* TO 'user'@'hostB' IDENTIFIED BY 'Password';
The two (or three) scenarios are completely different for MySQL.
interestingly enough, after creating the user I COULD telnet into the MYSQL server from another host BUT silly me - I turned off machineB last night, so for my test today I used MachineC (Win Server 2003) 10.0.0.20
… and keep in mind that joe@hostB is not the same user as joe@hostC for mysql. They need individual grants.
Voodoo, yes, starting from basics is a good idea; here are my resopnses:-
PING A from B = 100% success
“mysql ping” reports unknown db “ping”, but it is running as I can access phpMyAdmin and the internal websites I created which use databases on the server.
SELECT statement reads:
*************************** 1. row ***************************
Host: 10.0.0.10
User: machineb
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
Response is “port 3306”
As this is a local network and not internet facing, the firewall is turned off on Machine A (the MySQL server).
I also made sure to add a new user to the DNS instead of the IP address (I did IP last try) and then I tried to telnet in again.
Perhaps a little bit of progress here but Telnet is not the ideal tool to check … when I try I get:
5
5.0.51a??LprlG^",?TF@OKkXW+njI
Connection to host lost.
lol. However, it seems to connect now (no longer getting host not allowed) but telnet gets a garbled response (probably to be expected as telnet’s not going to be able to handle the response from the server as the mysql CLI would I suspect).
Let me quickly install the CLI tool on my machineB and I will write back with results
This is indeed what I intended. The telnet will not get back much usefull text, but it is very handy to test if you do get an answer, which is the case now. On to the next step …
Haha, yes, only I am stuck for now, my XP machine is turned off and the Server2003 does not want to install MySQL so no access to the MYSQL CLI for me just yet
Bear with me, I finish work in 10 mins and will be home in an hour or so when I can fire up the XP box and test …
Thanks to all so far for their replies, a problem shared is indeed a problem halved!!
Very good. This means that a user called ‘machineb’ may connect from hostB as long as this host identifies itself with its IP number and this number is 10.0.0.10.
Perhaps a little bit of progress here but Telnet is not the ideal tool to check … when I try I get:
5
5.0.51a??LprlG^",?TF@OKkXW+njI
Connection to host lost.
This coudn’t be better! MySQL server is responding with its binary protocol. This is a BIG progress. But from now on telnet is not the right tool to use. You should now be able to make a connection with the command line tool ‘mysql’. There is no need to install the whole server on machine B, just the clients.
Don’t play around with DNS for the time being. Use IP numbers. Once DNS is working, you may need additional grants on host A.
I tried READING your post and saw the mysqladmin error that I made earlier, corrected it and get a success result from it.
I downloaded the mysql tools from MySQL :: MySQL GUI Tools and these allowed me to connect into the remote mysql server straight away!
It looks like the mysql command below has worked
GRANT ALL ON *.* TO 'user'@'10.0.0.10' IDENTIFIED BY 'Password';
I can now use MachineB to monitor the mysql server health and all sorts of wonderful things - although I must confess, I’ve not tried creating any databases or using the application yet … I am fairly certain that it will work and if not, I’ll have a good foundation on how to get it working!!
Three cheers for all your help!!!
(and you too HCVV of course)
