up vote 0 down vote favorite
share [g+] share [fb]

Right now I have an ubuntu server that has mySQL. I am able to connect to it using the command 

mysql -u root -p (password)

I've been looking online and cannot find a solution that has worked. What I want to do is be able to connect to it from the outside. For example from my home pc (I have the mysql file that allows me to connect to other servers) 

mysql -h domainname.com -u username -p

but every time I try this I always get the same error:

ERROR 2005 (HY000): Unknown MySQL server host 'domainName:3306' (11004)

I think I have to edit the my.conf file but I'm new to this and not 100% sure.

Thanks for your help in advanced.

link|improve this question
That is generally the right syntax for connecting to a remote mySQL sever. My question is: are you specifying the FQDN or the IP address? Also, have you verified that there is no software firewall on the ubuntu server (e.g. iptables)? – Rilindo Jul 2 '11 at 0:12
feedback

2 Answers

up vote 2 down vote accepted

For security reasons, TCP access to mysql is disabled by default in most Linux distributions. To enable it, you have to edit the my.cnf file (check in /etc/mysql/my.cnf or /etc/my.cnf) and remove the skip-networking option. For good measure, you can add:

bind-address=192.168.X.Y  <-- your eth0 address here

You should also be aware that MySQL user restriction is based on source host. Logging in as root from localhost is not the same as logging in as root from myothercoolserver.mydomain.com. Be sure to use the GRANT options required to allow user@source to access the proper tables.

It is not recommended to allow the root user access from outside localhost.

Some more information on GRANT statements here.

Some other ideas you may want to try based upon our long discussion in the comments of this original answer:

  • Use tcpdump on the MySQL server to determine if the traffic is even arriving at the host. A command like tcpdump tcp port 3306 should work. If you see no traffic when attempting to connect it's likely that iptables or a hardware firewall between your external connection and the server is blocking tcp/3306.
  • You can try service iptables stop (assuming you've saved your rules in a place where they are managed by the init scripts) and see if that allows the traffic. If it does, revisit the iptables rules in /etc/sysconfig/iptables.conf (or similiar) or post them into another question and someone should be able to point out what's blocking the traffic.
link|improve this answer
Right now, the my.cnf file doenst have skip-networking, as for the bind address is it my internal IP of my server? As for the creating a new user, that has been done and I have tested it with that user. (but thank you for the info about root it makes sense) – RMT Jul 1 '11 at 19:59
Hm, are you able to resolve the hostname you're trying to connect to from the other server? (i.e. host myhostname should return an IP address). – Kyle Smith Jul 1 '11 at 20:02
I am able to ping the domain it returns me my ip address (external). I can connect to it using putty from my pc no problem – RMT Jul 1 '11 at 20:09
Have you set up a port forward for tcp/3306? If you have a router between your external network and your internet network you need to either add the device to a 1-to-1 NAT, or a port forwarding table. You probably already did this for SSH to work. – Kyle Smith Jul 1 '11 at 20:15
ya I've done that already. – RMT Jul 1 '11 at 20:19
show 3 more comments
feedback
up vote 1 down vote

The most secure way to do this is to ssh tunnel to your mysql box, and then use that tunnel:

$ ssh username@domainname.com -L 3306:localhost:3306

The configure your mysql client to talk to localhost:3306

Mak sure you bind the mysql server to localhost (127.0.0.1)

link|improve this answer
Im trying to connect from a windows machine (command line) or through my eclipse workspace, which I will be connecting with the jdbc driver. – RMT Jul 2 '11 at 0:01
the use putty to tunnel from windows. putty can be scripted, so a simple macro or script from eclipse will work too – Sunny Jul 5 '11 at 12:49
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.