# mySQL used to work; now it doesn't. [SOLVED]

## myrmidon

A lot of other people have had problems similar to mine, but none of the solutions offered has fixed my problem, so I'm going to ask again, being as detailed as I possibly can.

I installed MySQL about three days ago, executed "/usr/bin/mysql_install_db", and then executed the "mysqladmin -u root password 'new_pass'".  Immediately after doing so (still 3 days ago), I was able to log in to the server without any problems.  I didn't create any tables or databases, or modify anything that was already there, but I could at least log in.

When I tried logging back in this morning, I couldn't.  After quite a lot of playing around -- including unmerging mySQL and then re-emerging it -- I have come to the conclusion that executing "/etc/init.d/mysql start" does not actually start the mysql daemon -- even though I get an [ok] when I execute the command.  Here's why I think what I do:

1. When I execute "ps aux | grep 'mysql'", nothing is returned.  When I just execute "ps aux", I don't see mySQL among the processes listed.

2. When I execute "/etc/init.d/mysql stop", I get [!!] instead of [ok], as if something had gone wrong.  Also, when I execute "/etc/init.d/mysql restart", I get a [!!] when it tries to stop the mysql daemon, and then it never gets around to starting it again.

3. The most obvious reason I believe mysql is not started is because I cannot log into the server.  I can't even run the "mysqladmin" program to try and change the root password.  Whether I try to run "mysql" or "mysqladmin", I get the same error:

ERROR 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

And yet, if I execute "/etc/init.d/mysql status", it says that mysql is running -- and yet obviously there is no process listed when I run ps.  The only conclusion I can draw from all this is that, even though "/etc/init.d/mysql start" says it starts the mySQL daemon, and even though the mySQL daemon thinks it has started, it in actuality has not.

And, of course, underlying this entire issue is the fact that I was able to log in just three days ago!  And then, without having done anything at all to mySQL, all of a sudden I just can't log in!

Anybody have any ideas at all?  Sorry for such a long post.

Justin PetersonLast edited by myrmidon on Fri Nov 05, 2004 1:36 pm; edited 1 time in total

----------

## SoTired

First try 

```
cat /var/run/mysqld/mysqld.pid
```

 it will return the PID of mysql, if it is running.

Of course, seeing how you cannot connect to the socket it sounds like it is not, so trying

```
/etc/init.d/mysql zap && /etc/init.d/mysql start
```

 ought to at least give you some error messages.

Anyways, the above ought to at least either get the server running again or give you a better idea of what's going wrong, the contents of /var/log/mysql/mysqld.err might be helpful in figuring out why mysql decided to shut down on it's own - well, crash probably.

So if you want, post whatever you get from the first command and what's in the log and I'll see if there's anything else I can suggest.

----------

## myrmidon

Thanks for replying so quickly, SoTired.  I'm sorry I couldn't respond to your reply until just now.  Anyway, here we go....

-----

Executing "cat /var/run/mysqld/mysqld.pid" gives an error:

cat: /var/run/mysqld/mysql.pid: No such file or directory

When I cd to the directory /var/run/mysqld/, it is completely empty.  Also, for what it's worth, executing "/etc/init.d/mysql status" gives this:

* status:  started

-----

Now, when I execute "/etc/init.d/mysql zap && /etc/init.d/mysql start", I find this in my .err file:

041104 17:59:11  mysqld started

041104 17:59:11 Can't start server: Bind on TCP/IP port: Cannot assign requested address

041104 17:59:11 Do you already have another mysqld server running on port: 3306 ?

041104 17:59:11 Aborting

041104 17:59:11 /usr/sbin/mysqld: Shutdown Complete

041104 17:59:11  mysqld ended

-----

So, it's asking me if I already have another mysqld server running when, obviously, I don't.  Does this mean some other service is running on port 3306?  Should I edit my.cnf to have mysql run on another port?

What does this error message mean, and what do you think I can do about it?  And why might port 3306 be in use?

Thanks,

Justin Peterson

----------

## SoTired

Yep, this means something else is bound to 3306/tcp.

The fix is easy - if you only need to access your database locally, uncomment 'skip-networking' in your /etc/mysql/my.cnf file.  With only local (socket) connections you can still do most of the things MySQL databases usally due (aka serve webpages) - if you need to do more changing the port is one option.  (You might also want to note that it's more secure to have skip-networking set.)

As for what is using port 3306, try 

```
netstat -lpn | grep 3306
```

 and if that doesn't give you anything (which would be odd) feel free to post the results of netstat -lpn and I'd be happy to take a look at it.

Edit: As for why the port would be in use, i really have no clue, I've never heard of another service using it.  Perhaps there was some rather massive MySQL glitch where some process is still using the port but the socket and lockfile got deleted.  Netstat ought to clear things up anyhow.

----------

## myrmidon

Here's the output from "netstat -lpn" (sorry if the formatting is messed up):

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name

tcp        0      0 0.0.0.0:139             0.0.0.0:*               LISTEN      1291/smbd

tcp        0      0 192.168.0.2:80          0.0.0.0:*               LISTEN      1202/apache2

tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1185/sshd

tcp        0      0 0.0.0.0:445             0.0.0.0:*               LISTEN      1291/smbd

udp        0      0 192.168.0.2:137         0.0.0.0:*                           1300/nmbd

udp        0      0 0.0.0.0:137             0.0.0.0:*                           1300/nmbd

udp        0      0 192.168.0.2:138         0.0.0.0:*                           1300/nmbd

udp        0      0 0.0.0.0:138             0.0.0.0:*                           1300/nmbd

Active UNIX domain sockets (only servers)

Proto RefCnt Flags       Type       State         I-Node PID/Program name    Path

unix  2      [ ACC ]     STREAM     LISTENING     2159   1290/apache2        /var/run/cgisock

-----

I didn't see anything there having to do with port 3306, but maybe you'll be able to understand what all that says better than I could.

Could you explain a little bit more about what mySQL can and can't do with skip-networking uncommented?  Because when I uncommented the line, mySQL started up without any problems.  What do you mean when you say skip-networking will work "if you only need to access your database locally"?  With skip-networking, can I run a website that is available to the public, or could I only use it with a local webserver (http://localhost/, for example)?

-----

By the way, *with* mySQL running (with the skip-networking line uncommented), this is what "netstat -lpn" looks like:

Active Internet connections (only servers)

Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name

tcp        0      0 0.0.0.0:139             0.0.0.0:*               LISTEN      1291/smbd

tcp        0      0 192.168.0.2:80          0.0.0.0:*               LISTEN      1202/apache2

tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1185/sshd

tcp        0      0 0.0.0.0:445             0.0.0.0:*               LISTEN      1291/smbd

udp        0      0 192.168.0.2:137         0.0.0.0:*                           1300/nmbd

udp        0      0 0.0.0.0:137             0.0.0.0:*                           1300/nmbd

udp        0      0 192.168.0.2:138         0.0.0.0:*                           1300/nmbd

udp        0      0 0.0.0.0:138             0.0.0.0:*                           1300/nmbd

Active UNIX domain sockets (only servers)

Proto RefCnt Flags       Type       State         I-Node PID/Program name    Path

unix  2      [ ACC ]     STREAM     LISTENING     5095   1904/mysqld         /var/run/mysqld/mysqld.

sock

unix  2      [ ACC ]     STREAM     LISTENING     2159   1290/apache2        /var/run/cgisock

-----

Thanks for all the help.  And, by the way, how do I insert code into my posts the way you've been doing?  This is only my second day as a member of the forums, and I haven't figured that out yet.

----------

## SoTired

Odd, I can't say about what is bound to 3306, doesn't look like anything is.

Code is done by the code tag {code}this would be in a box{/code} but you use brackets instead of braces.  There is also a code button below the subject field.

With skip-networking you can do a lot.  What happens with most (sorry about the generalization MySQL zealots) MySQL servers is that they sit on the same server that is running the webserver and act as a repository for dynamic content - it sounds as though this may be what you're planning.  What happens in these models is a client connects to the webserver, and then the webserver user (apache, or nouser, or whatever) connects to the MySQL database locally.  Hence, the client has no need to be able to get onto the database directly.  Even if you were to host other people, via SSH or phpmyadmin and whatnot you still wouldn't need to have MySQL run with networking.

You would need networking if you had your webserver on a different machine (from the output of netstat I can see that you don't, so that's obviously not the case), or if you were in a more complex situaiton where you had databases spanned over multiple computers, or in a cluster (in such situations more robust RDMBS are usually used, oracle, postgres, etc.)

Yikes, that was a lot, sorry, but probably you don't need networking for MySQL.

----------

## myrmidon

Excellent.  Thanks so much for your time.  I've been using the mySQL server with the skip-networking option for most of the evening, and have been tinkering with my webserver over ssh, so everything seems to be running smoothly.  I think we can consider this matter closed.

I'd really like to know why I was able to log in when I first installed mySQL but then suddenly wasn't able to 3 days later; and I'd like to know why starting the mySQL daemon didn't start a process; and I'd like to know why mySQL thought something was bound to 3306 when nothing actually was ... but it may just be that no one can ever answer those questions.  And it doesn't matter, because skip-networking makes everything work fine, anyway.

Again, thanks for your time, SoTired.  Let me know if I can ever help you with anything (though I probably won't be able to).

Justin Peterson

----------

## SoTired

Well, to try to answer the unanswerable.

It sounds like MySQL did something really weird - just plain crashed and didn't send any messages anywhere.  It removed it's PID file, but didn't manage to close it's TCP socket.

I can't pretend to explain why, not quite that good with MySQL source code   :Wink: 

You could see if by removing skip-networking, removing mysql from the default runlevel (if you added it in the first pace), and then rebooting and trying to start MySQL if it would start.  I would think that it might - but then again, as you said it really doesn't matter in the end anyways.

Glad I could help.

Edit:

A common thing to do (just suggesting this because you mentioned that you're new around here) is to edit your orginal post and append [SOLVED] to the end, to indicate that a) it's solved, so people stop trying to solve it, and b) it's solved, so that people searching for solutions know they might find something in this thread.  Some people do it, others don't, it's up to you.

----------

## ColeSlaw

This just solved a problem I was having with my MythTV!  Thanks for the help!

----------

