Date: Fri, 09 Dec 2005 14:22:33 +1100 From: Michael Vince <mv@roq.com> To: daniel <danstemporaryaccount@yahoo.ca> Cc: freebsd-questions@freebsd.org Subject: Re: MySQL "forgot" some of my data! Message-ID: <4398F879.2010306@roq.com> In-Reply-To: <200512081051.38306.danstemporaryaccount@yahoo.ca> References: <200512081051.38306.danstemporaryaccount@yahoo.ca>
next in thread | previous in thread | raw e-mail | index | archive | help
daniel wrote: >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 ;-) > > I have had this problem as well on a 30gig db, I dont remember exactly what it was but I had a hunch it was something to do with maybe my hostname change, I think I changed it from 'database' to 'database.domain.com' via the 'hostname' command then edited the /etc/rc.conf file I think this stuffed up the running database pid file because it was looking for /var/db/mysql/hostname.pid instead of /var/db/mysql/hostname.domain.com.pid, but trying to bring it down manually didn't seem to work either. It could be been something else all together, but I do believe it was config changes around the system that caused it, I do believe an instant clean shutdown of MySQL is possible (I can't test for you on mine, sorry) but it might require you to really restart you setup clean. Mike
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?4398F879.2010306>