MySQL port issues

skulbite · 05-05-2005, 10:34 PM

I am having a strange problem where I cannot connect to MySQL using port 3306.

MySQL is running as a 'ps -A' lists mysqld and mysqld_safe.

I've copied the my-small.cnf to /etc/my.cnf and restarted the service. I've made sure that, under [mysqld] port=3306 (by default) and skip-networking is commented out (as it is by default).

I've turned off iptables and shorewall just to be safe.

I've tried 'telnet localhost 3306' and 'telnet ip_address 3306' and it refuses the connection.

'netstat -tlnp' does not show anything listening on port 3306.

The most strange thing that I find is that when I run 'mysqladmin variables' I see the port is 0 and I thought that it should be 3306.

Does anyone have any idea why this is happening.

I am running Mandriva 2005LE with MySQL 4.1.11

Thanks.

michaelsanford · 05-05-2005, 10:57 PM

Did you ever try it without an /etc/my.cnf file at all?

skulbite · 05-06-2005, 09:44 AM

Quote:

Originally posted by michaelsanford
Did you ever try it without an /etc/my.cnf file at all?

I just tried it and the service will not start back.

michaelsanford · 05-06-2005, 01:47 PM

Sorry, I didn't see this last night : why are you using telnet to access mysqld and not the mysql client application?

This is what happens when I try to telnet in (and mysqld is running just fine on my system)

Code:

root@gateway:~# telnet localhost 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
(
4.0.23a+2YLN"^5,Connection closed by foreign host.

I don't think the mysql client application is a strict character terminal, so it's no surprise it looks wierd and dies.

Try using the `mysql` command.

PS If that works, issue a `\s` command in mysql and post what it prints out here, mine looks like this:

Code:

mysql> \s
--------------
mysql  Ver 12.22 Distrib 4.0.23a, for slackware-linux-gnu (i486)

Connection id:          3
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Server version:         4.0.23a
Protocol version:       10
Connection:             Localhost via UNIX socket
Client characterset:    latin1
Server characterset:    latin1
UNIX socket:            /var/run/mysql/mysql.sock
Uptime:                 2 min 58 sec

Threads: 1  Questions: 4  Slow queries: 0  Opens: 6  Flush tables: 1  Open tables: 0  Queries per second avg: 0.022
--------------

skulbite · 05-06-2005, 02:09 PM

Quote:

Originally posted by michaelsanford
Sorry, I didn't see this last night : why are you using telnet to access mysqld and not the mysql client application?

This is what happens when I try to telnet in (and mysqld is running just fine on my system)

Code:

root@gateway:~# telnet localhost 3306
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
(
4.0.23a+2YLN"^5,Connection closed by foreign host.

I don't think the mysql client application is a strict character terminal, so it's no surprise it looks wierd and dies.
[/B]

If you check out the MySQL manual and some of the posts on this forums, you'll see that telneting to the port is a good and easy indicator as to see if MySQL is listening on the port. That's why it responded the way it did for you, it indicates that your MySQL server is listening on port 3306. I get 'telnet: Unable to connect to remote host: Connection refused' which is an indicator that nothing is listening on that port.

Code:

mysql> \s
--------------
mysql  Ver 14.7 Distrib 4.1.11, for mandrake-linux-gnu (i586)

Connection id:          6
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         4.1.11
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 4 hours 56 min 29 sec

Threads: 1  Questions: 5  Slow queries: 0  Opens: 11  Flush tables: 1  Open tables: 0  Queries per second avg: 0.000
--------------

.... but you see, this doesn't tell you if the MySQL is listening on a port at all. It just tells you server version info, uptime and a query analysis. My problem is that I cannot get it to listen on a port and that is what is frustrating the hell out of me.

michaelsanford · 05-06-2005, 02:33 PM

That's true, since the client app uses a socket for local connections, I'm just trying to debug (to see if you can connect at all which might point to other problems than just port attachment

)

What does `/var/lib/mysql/HOST.err` say ?

skulbite · 05-06-2005, 02:41 PM

Quote:

Originally posted by michaelsanford
That's true, since the client app uses a socket for local connections, I'm just trying to debug (to see if you can connect at all which might point to other problems than just port attachment

)

What does `/var/lib/mysql/HOST.err` say ?

Code:

050506 10:03:15  mysqld started
050506 10:03:15  InnoDB: Started; log sequence number 0 43634
/usr/sbin/mysqld: ready for connections.
Version: '4.1.11'  socket: '/var/lib/mysql/mysql.sock'  port: 0  Source distribution

You see... this is what confuses me. It isn't binding to a port as you can see above. Even when I run mysqladmin variables the port is 0. It's set in the my.cnf, and yet it still does not bind.

michaelsanford · 05-06-2005, 03:04 PM

Maybe a silly question but is mysqld run as root? (I assume it is since your mysq \s says `Current user: root@localhost`).

Try starting mysql as

Code:

safe_mysqld -P 3306

If that doesn't work either, have a look at the error log again.

skulbite · 05-06-2005, 03:26 PM

Quote:

Originally posted by michaelsanford
Maybe a silly question but is mysqld run as root? (I assume it is since your mysq \s says `Current user: root@localhost`).

Try starting mysql as

Code:

safe_mysqld -P 3306

If that doesn't work either, have a look at the error log again. [/B]

As you can see from your \s, you have root@localhost as well. This is actually the MySQL's root, not the OS's root. The MySQL server is ran as mysql with the group as mysql.

I have tried using mysql_safe, but it is not allowing me to execute as either root or as the mysql user. Error log does not help as there are no errors. It is just not binding to the port even though the my.cnf is telling it to.

skulbite · 05-06-2005, 03:28 PM

The other thing I tried is to set the port to 3307. That did not work either. It just does not want to bind to a port at all!

michaelsanford · 05-06-2005, 09:09 PM

Yeah I know it's mysql's root user but since it takes the default user from $USER it's a safe assumption that you're logged in as root.

At this point I'm lost...but I'll keep looking for ya. This is a rather unique problem.

skulbite · 05-09-2005, 09:40 PM

Quote:

Originally posted by michaelsanford
Yeah I know it's mysql's root user but since it takes the default user from $USER it's a safe assumption that you're logged in as root.

At this point I'm lost...but I'll keep looking for ya. This is a rather unique problem.

Soooooooooooooooo... I found it and it is a real pain in the ass.

You have to edit /etc/sysconfig/mysqld....

Code:

# (oe) Remove --skip-networking to enable network access from
# non local clients. Access from localhost will still work.
MYSQLD_OPTIONS="--skip-networking"

... and comment out the last line.

I only got this when I was using urpmi to reinstall it. I guess it is just one of those things that make Linux so mysterious!

michaelsanford · 05-09-2005, 10:36 PM

I would never ever have gotten that because Slackware comes by default without /etc/sysconfig/ :P

Good on you!

skulbite · 05-09-2005, 11:03 PM

Quote:

Originally posted by michaelsanford
I would never ever have gotten that because Slackware comes by default without /etc/sysconfig/ :P

Good on you!

Thank you... it does feel good to crack that one.

blanny · 11-28-2005, 01:36 AM

Thank you, this post really helped me out. I'm running Mandriva 2006.

Blanny