Assuming you can connect locally to mysql, you’d expect to be able to connect remote…not! This is not as easy as it seems.
First make sure you CAN access the mysql server from a remote machine. By default, the my.cnf file contains a statement:
bind-address=127.0.0.1
that is in your way. This only allows connections from the localhost. Put a # in front of it.
After this, still no luck, because the mysql privilege system is not your default user/password pair, but mysql for some reason also needs to know where you’re calling from. This is due to the unusual non-standard structure of their user table: it contains host/user/password.
So add your remote host to the table by issuing:
grant all on *.* to user@remotehost identified by ‘password’
or, if you want to allow access from all hosts, use a % like:
grant all on *.* to ‘user’@’%’ identified by ‘password’
note: put quotes around username and host if neccesary. Beware not to quote the complete thing (like ‘user@host’) because mysql thinks you mean user “user@host” on the local machine.
Because mysql caches privileges, you need to flush them so enter:
flush privileges
after this you should be able to login
to see if its ok, do
use mysql
select * from user;
That’s it. Sounds easy, but as the mysql documentation index/search sucks bigtime it can take you hours to find this out. (for instance, searching on the 1130 error number puts you on the wrong trail by giving information about blocked-hosts that has nothing to do with the real problem)