Date: Thu, 8 Dec 2005 10:51:38 -0500 From: daniel <danstemporaryaccount@yahoo.ca> To: freebsd-questions@freebsd.org Subject: MySQL "forgot" some of my data! Message-ID: <200512081051.38306.danstemporaryaccount@yahoo.ca>
next in thread | raw e-mail | index | archive | help
Our company has a few very large databases (>9gb) which required a shut down,
upgrade and restart last night, it this act has caused me all manour of
headache:
1. I did a full backup of all the data (Good Idea)
2. I ran "mysqladmin -uroot -p shutdown
3. I waited... and waited... and waited
Twenty minutes passed and the server continued to say that it was indeed
shutting down. The only problem was that it wasn't shutting down. The
server's load was 0.00 and i could access mysql via the command-line client.
Ctrl-C'ing the shutdown and restarting it didn't seem to help either. What's
worse, logging in with the client had me freaking out when I couldn't find
some of my data:
mysql> SELECT * FROM campaigns LIMIT 0;
ERROR 1017 at line 1: Can't find file: 'campaigns.MYI' (errno: 2)
After some investigation, we realised that a cron on another machine was
running every minute, talking to this database, and assuming that this was
the issue, we turned that stuff off too only to find that mysqladmin *STILL*
wouldn't actually shut anything down.
So I decided to try a few other things:
# kill -s SIGTERM `cat /var/db/mysql/hostname.pid`
nothing
# kill `cat /var/db/mysql/hostname.pid`
still nothing
# kill -9 `cat /var/db/mysql/hostname.pid`
That did it. Bringing the database back up though confirmed it, we'd lost a
few whole tables and possibly some additional data as well. No data
corruption though, just loss. A full restore from our now roughly 24hours
old backup would be required.
So my questions to you, deal list are:
1. Should mysqladmin shutdown take this long?
a. Is this normal even with a load of 0.00?
2. Is this the best way to shut down a database of this size?
3. What is most likely to be the cause of the data loss?
Thanks for any insight here. I hope this is a lesson to you all: Backups are
Groovy ;-)
--
democracy, taken in its narrower, purely political sense, suffers from the
fact that those in economic or political power possess the means for molding
public opinion to serve their own class interests.
- albert Einstein
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?200512081051.38306.danstemporaryaccount>