Need to connect to MySQL on port 3306

[psand]

I would like to use my Bubba Two as a MySQL server on my home network. However, it seems port 3306 is not accessible from outside the Bubba.

Despite having defined an open public TCP port 3306 in the Firewall menu, I just cannot connect. An nmap scan shows this port is not in an open state. When I try to connect from inside my shell on the Bubba (i.e. $ telnet localhost 3306) I receive a warning from libshishi (libshishi: warning: /home/psand/.shishi/tickets: No such file or directory), which seems to indicate there is some authorization going on. I've never encountered this libshishi before and I'm quite at a loss.

Please note that this is not a question about how to configure MySQL. I just want to make SQL queries to my configured MySQL server from another machine on the network. I do understand the security implications of this.

Per

[psand]

I've solved my problem. Embarassingly enough, it was a simple MySQL configuration problem.

There was no need to reconfigure my Bubba's firewall, as this only applies to the WAN interface (eth0) whereas I wanted access to port 3306 over the LAN interface (eth1).

What I did was to edit the configuration file /etc/mysql/my.cnf by commenting out the line "bind-address = 127.0.0.1". If this line is encountered by the mysqld daemon when it starts, no TCP connections from foreign hosts will be allowed.

There must also be at least one database present which allows access to a user on the foreign host. I created a "test" database with anonymous access from any host, and suddenly I was able to telnet to port 3306 from my other machine:

$ mysql -u root
mysql> CREATE DATABASE test;
mysql> GRANT ALL ON test.* TO ''@'%';

Hope this helps someone else.

[JanPihlgren]

I also have this problem, I can't connect to MySQL from a host other than BubbaTwo.
I have change as mention above.
My call is like this:

[jan@humlan etc]$ mysql -u jan -h bubba -p
Enter password:
|

I write the password and the cursor stands blinking on the row below
Then I have to use ctrl-c to disconnect.

I don't understand how to connect to mysql in host 1 from host 2.

[Ubi]

sounds like a routing or firewall issue more then a mysql problem

[Gordon]

The default for MySQL is to create host-bound users. If you get phpMyAdmin you can easily verify that what you think is just user "jan" is in fact user "jan@bubba". If you want Jan to connect from any place the user should be named "jan@%". Unfortunately I do not know of any method to lift the host restriction other that creating a new user (i.e. jan@%) and reassigning rights.

[Ubi]

thats correct, but it will will give you this answer immediately, not make you wait for a timeout. That's why I think its not a mysql error per se.

[JanPihlgren]

@Ubi
This is within a LAN. I can reach host bubba from host humlan with ssh. Then I can access mysql.
I can also reach bubba the normal way (by http).
So I wunder why I can't access mysql from humlan
How can it be a firewall or routing issue?
The computers are connected to the same LAN, the same router.

@Gordon
I have setup a user jan at host 192.168,x.x in mysql.


I solved the problem.
I have to open port 3306
Administration
Settings - Firewall - Advance firewall settings - Open Bubba2 port - Public port
I set it to 3306 and then it works.

[Gordon]

Sounds like a routing/DNS issue. The firewall settings control just the outside (WAN) interface, so if this solves it for you that would imply that MySQL is communicating primarily over the WAN interface. It should be possible to verify this using netstat after you opened a session:

Code: Select all

# netstat -an | grep "3306"

Look for the line(s?) that says "ESTABLISHED" and note the IP addresses listed for this connection.

[JanPihlgren]

Isn't the WAN-interface for the incomming traffic to bubba?
I have connected bubba to a Netgear router which is connected to internet through cabel.

This is the answer I get from bubba when used command netstat -an | grep 3306

tcp 0 0 192.168.x.x:3306 192.168.x.y:60778 ESTABLISHED

[Gordon]

Is 192.168.x.0 the subnet that is connected to the Bubba LAN port or the WAN port

[JanPihlgren]

It's connected to the WAN-port.

[Gordon]

Well then that explains the confusion.

Yes, the WAN port is shielded by the firewall. So while plugging in the WAN connector is the quickest and easiest way to connect the Bubba to an existing LAN environment (because of the DHCP client), it will get you in trouble connecting to any of the Bubba hosted services.

If you don't want to use the Bubba as a gateway to the internet, you should disable the firewall all together. A better solution though is to reconfigure the LAN port for a free address within your existing LAN IP range and make this address known to your router as a fixed address.