How to connect to MySql in another Ubuntu machine?

Question

Hi How do i connect to MySql from another Ubuntu machine. I tried this

mysql -h 'IP' -P 3306 -u test -ptest

I get ERROR 2003 (HY000): Can't connect to MySQL server on 'IP' (111). then i tried

 nmap -sS -O -p 3306 'IP'   and it says 3306/tcp closed mysql

But in the MySql machine when i try it with localhost it works

 nmap -sS -O -p 3306 localhost it says 3306/tcp open mysql

What am i missing here

Is your other machine set up for remote access to mysql? – Angelo R. May 19 '11 at 19:55 — Angelo R., May 19 '11 at 19:55

score 2 · Accepted Answer · answered May 19 '11 at 19:57

2

Two ways to check this:

[1] in /etc/mysql/my.cnf if the database is configured to be listening on localhost (127.0.0.1) only. Look for the line

bind-address        = 127.0.0.1

and comment this line, follow by a restart of the database. I think this is your issue, since this is default in Ubuntu.

[2] Run "netstat -an | grep 3306" - it should give this when open for connections from the outside:

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN

answered May 19 '11 at 19:57

Henk

When bind-address was commented it worked. Thanks mate – Rakesh May 19 '11 at 20:11
By commenting bind-address is mysql secure are there any security issues – Rakesh May 19 '11 at 21:40
Yes this IS a potential security issue indeed. At least use a firewall to limit access to port 3306. Google for the rest ;-) – Henk May 20 '11 at 07:13

score -1 · Answer 2 · answered May 19 '11 at 20:14

You must create an user that's allowed to connect to that MySQL server from the machine you want to connect from.

mysql > create user 'user'@'ip_from_you_want_to_connect' identified by 'password'.

The trick is, in the create user syntax, what you put after the @

create user 'user'@'localhost' only gives permission to that user when connecting from the local machine.

If you want to connect from another machine, replace the localhost with the IP or the name (FQDN) of that machine.

You can find out more about creating users here

Also, check the hint given by Henk.

2 Answers2