mysql crashing

O

os2lover

Verified User
Joined
Dec 24, 2004
Messages
20
Hello,

Since a couple of days MySQL seems to have crashed every morning when I wake up.

When I look at the status in the DirectAdmin control panel all processes are still running, but it doesn't accept any connections any more. When I try to restart MySQL through the DirectAdmin control panel it doesn't give me an error message, but I'm still unable to connect to the mysql server.

When I'm on the command line, and I perform "killall -9 mysqld", then it restarts automatically, and then I'm able to log in to the MySQL server without any problems. But the next morning it's stuck again (the whole day it works perfectly!)

Best regards,
Olivier Baum
http://www.basm.be/

PS : the only special thing that I can think of that happens every night is a full system backup on a different partition. Maybe a problem while dropping the database data ?
 
Funny, I did a full sys backup this morning as a test of some new hardware (well, more of a configuration test), and i left out MySQL backup due to the fact I really can't use the downtime on that service.
(While performing backup the sysbk shuts down mysqld)

But when it was finished I had the same issue on that server. After killing all processes with killall they were able to start like they should, but a service mysqld restart gave back the following error:
050115 14:32:20 mysqld started
050115 14:32:20 Warning: Asked for 196608 thread stack, but got 126976
050115 14:32:20 Can't start server: Bind on TCP/IP port: Address already in use
050115 14:32:20 Do you already have another mysqld server running on port: 3306 ?
050115 14:32:20 Aborting

050115 14:32:20 /usr/sbin/mysqld: Shutdown Complete

050115 14:32:20 mysqld ended

050115 14:32:46 /usr/sbin/mysqld: Normal shutdown
Click to expand...

The port was still in use, but the PID file was gone and it wasn't taking in new connections...

Hello 18 more minutes of downtime on a service (it seems like january is gonna be the worst month this year), and DA was unable to fix it self...
 
Does this only happen or seem to the most when your running backups? If so, what backups are being run? (user, reseller, sysbak, etc.)
 
Admin level system backup, seems to happen when I want to backup everything BUT mysqld, won't happen if I only want to backup mysql.

Admin system is configured to transfer it by ftp to another server.

Hope anyone is able to recreate it...
 
I've had the same problem, just now. In DA under services there is start- stop- but no restart or reload like the other services.

errormessage if i want to create an new db: Error connecting to MySQL: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

mysqld started
Can't start server: Bind on TCP/IP port: Address already in use
Do you already have another mysqld server running on port: 3306 ?
Aborting
/usr/sbin/mysqld: Shutdown Complete
mysqld ended

Has a solution been found?
MySQL 4.0.17
CentOS 3.3

Rebooting the server solved it for now but hee, we're not using MS? I would like to find the proper solution.

Would the solution be to stop the MySQL service manually before backup?
 
Last edited:
I have this also on multiple machines (not every day, but sometimes).

That's a pain in the a*s, because DA is not correcting it itself.

The PID file is gone, but the socketfile remains (as it seems). Anyway, now I run this script in a cronjob and that fixes the issue.

Code: 
#!/bin/sh

# Auteur  : Dillard Blom ([email protected])
# Doel    : Checkup op MySQL en herstarten indien nodig
#
# Gebruik : Pas dit script aan voor eigen gebruik en plaats in /etc/crontab
#
# Commando: /root/scripts/mysqlcheck (chmod 700)
#
# Gemaakt : 11-04-2005
#
#

/usr/local/bin/mysqladmin ping 

if [ $? -eq 0 ] ; then
        # echo "MySQL werkt nog steeds"
        exit 0
else
        echo "MySQL herstart" | mail [email][email protected][/email]
        /usr/bin/killall mysqld
        /usr/local/etc/rc.d/mysqld start

        # checkup again
        sleep 30
        exec $0
fi

Run it every day/hour/10 minutes and this issue is fixed..
 
Thanks Dillard
It is a workaround but it should work, I'll test it out.
On my server I found that if i use an other backupprogram than DA i have the problem. If i let DA do the backup everything works fine.
So I let DA do the backups and it works fine.
I like simple things
 
I run FreeBSD 5.3 vith DA. Today I tried to optimize MySQL as described in http://help.directadmin.com/item.php?id=44

After that it seems to be working fine. Then I tried to make full system backup. During this procedue MySQL stop responding to clients sites. DA Service Monitor reported MySQL was still running and "ps - aux | grep mysql" command reported two pid's. But no one of client sites could connect to the MySQL throw tmp/mysql.sock

I tiried to restart MySQL with DA service monitor and manually with /usr/local/etc/rc.d/mysqld restart. No results, except:

050420 12:36:10 mysqld started
050420 12:36:10 Can't start server: Bind on TCP/IP port: Address already in use
050420 12:36:10 Do you already have another mysqld server running on port: 3306 ?
050420 12:36:10 Aborting

in the logs.

To make the long story short, I deleted some rows from my.cnf, after that MySQL starts and work untill now. I don't know what will be if I try to backup server again.

My my.cnf file now is:

[mysqld]
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=1000
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=64M
log-bin
server-id=1
 
I tried to run backup's again at night (a fiew minutes ago). The result is the same: clients are unable to connect to MySQL server.
This should be some kind of a DA backup system bug :( It is impossible to restart MuSQL from DA process monitor or with /usr/local/etc/rc.d mysqld stop | start (the process is still running). I am unable to wait untill backup will be done to check if it will come back.

A quick fix (on FreeBSD) is:

ps -aux | grep mysql

kill all of find process with kill PID

/usr/local/etc/rc.d mysqld start
 
FreeBSD 5.4 - Same Problem

Just wanted to let everybody know that I'm having this problem as well on FreeBSD 5.4. I've never installed anything other than what Direct Admin installs during its setup.sh routine.

The issue started only after setting up nightly backups via the web interface. Included in the backups are all directories, user data and of course mysql.

I setup the cron script as mentioned above.

Is there an actual fix, or cause for this problem?
 
The one server that had this problem (not fixed, just doing mysql backups on another way now) has a my.cnf in place with some optimalisations.

Agile: I don't know what FreeBSD does, however when testing with Debian I got a custom my.cnf...

So there has to be something in the my.cnf causing this, as Agon found out.
 
It's impossible to guarantee a clean MySQL backup unless MySQL is properly shut down before the backup.

We've never had a problem with it taking so long. If we did, then we'd engineer a custom solution to shutdown MySQL, then turn off RAID, then turn MySQL right back on (should take significantly less time than doing the entire backup to just turn off RAID, then backup the files in the partition removed from the RAID array. Then after those files are backed up, reconnect the drive to the RAID array and let the RAID rebuild itself.

Note that this will use significantly more machine resources than doing the backup "straight" and note also that we do NOT support it for anyone unless we do the entire server setup ourselves.

Jeff
 
Is it possible to backup only SQL then? Personally I don't like the fact that MySQL will be down for a fiew minutes every day.
 
I mean make backup by creating mysql dump and save it as plain txt file, not copy DB files. This metod not require shut down MySQL.
 
Where did you read that you can do a MySQL dump without shutting down the server?

Jeff
 
MySQL Dump w/o Shutdown

You can dump mysql databases without shutdown, this is what I'm doing now instead of letting DA do the backup.

I created a cron that runs at 3AM and just does:

mysqldump -u root -p... -A > backup.txt
gzip backup.txt

Then I include backup.txt.gz in with the other backups that DA spits out and updates via FTP to a remote box.
 
OK, let me rephrase what I wrote...

I know you can dump mysql without shutting it down; the question is does that create a restorable backup with guaranteed data in all the databases.

Jeff
 
jlasman said:
Where did you read that you can do a MySQL dump without shutting down the server?

Jeff
Click to expand...

I wrote it on the forums somewhere I guess...
Although I'm not sure about it anymore.
It is a (bad) solution to the problem, however I too am abusing this solution for 1 server at the moment. The other servers doesn't seem to have the problem.

So far I've never had problems with 'corrupt' tables due to incomplete transactions, however it's still possible... So don't trust it.
 
You must log in or register to reply here.
Back
Top