How could I configure my local server to allow jdbc connection to mysql?

Question

I have I small Java application. At the moment I'm trying to get it to work with mysql. I have VMware Workstation and Ubuntu Server 10.10 running on it. When I'm trying to connect to server (from my PC where VMware is running) to start JDBC connection for mysql driver, it doesn't work. I asked similar question earlier at SO. First and only answer (at the moment) is that firewall or connection type might be the issue, but I don't know how to correctly configure it.

Maybe someone could help with this part?

Also I tried to create a *.jar file from my application and in link I placed localhost and on server it works fine, so I guess it proves again, that there is some limitation during the connection.

Solution

My guess is the bind-address

The default port for mysql is 3306. Check if its listening on all interfaces with netstat -tanlp | grep LISTEN | grep 3306:

tcp        0      0 0.0.0.0:3306  0.0.0.0:*  LISTEN      mysqld

If that says 127.0.0.1:3306 then your mysql is listening on only on the localhost. If that's the case, change your mysql's my.cnf bind-address setting:

bind-address    = 10.0.0.1

or

bind-address = 0.0.0.0 

to listen on all interfaces.

Update: Ok, now that you are able to connect to port 3306. The error message:

Host 'vm_ip' is not allowed to connect to this MySQL server

Means that there is no firewall/iptables in the way. The message is coming from MySQL itself. You are going to need to do appropriate "grants" to allow your "user" to connect to the db from your "host". From a security perspective you need to be very careful what you enable here. See the following URL for help:

http://dev.mysql.com/doc/refman/5.5/en/access-denied.html

There are lots of sites that have grant all to everyone type links on them. Be very careful what you use from these. If your user needs read only access then you could start with something like:

GRANT select ON mydatabase.* to myusername@'192.168.1.1' IDENTIFIED BY 'mypasssword';

Be very wary about just doing a "GRANT all" type command.

Comments

[Eugene]

Okei. Great suggestion. Moved bit further, but now I get error Details : java.sql.SQLException: null, message from server: "Host 'vm_ip' is not allowed to connect to this MySQL server" when I'm trying to establish connection java.sql.Connection db_connection = DriverManager.getConnection( url, "usr", "pwd" );. So I guess, that something is still blocking the connection.

[Eugene]

That's odd. I'm using root user to connect to database. This user should have all privileges by default. I might be wrong, but as I know it should be so.

[davey]

root will have all at "localhost" but when it's connection comes in from another IP address he probably has no privilege.

[Eugene]

Okey. I will try another way around. It might help then.