From owner-freebsd-questions@FreeBSD.ORG Fri Dec 9 03:22:40 2005 Return-Path: X-Original-To: freebsd-questions@freebsd.org Delivered-To: freebsd-questions@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 85AEE16A41F for ; Fri, 9 Dec 2005 03:22:40 +0000 (GMT) (envelope-from mv@roq.com) Received: from vault.mel.jumbuck.com (ppp166-27.static.internode.on.net [150.101.166.27]) by mx1.FreeBSD.org (Postfix) with ESMTP id 1B61943D62 for ; Fri, 9 Dec 2005 03:22:34 +0000 (GMT) (envelope-from mv@roq.com) Received: from vault.mel.jumbuck.com (localhost [127.0.0.1]) by vault.mel.jumbuck.com (Postfix) with ESMTP id 4A25B8A00D; Fri, 9 Dec 2005 14:22:33 +1100 (EST) Received: from [192.168.46.52] (unknown [192.168.46.250]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by vault.mel.jumbuck.com (Postfix) with ESMTP id 2E0C28A00A; Fri, 9 Dec 2005 14:22:33 +1100 (EST) Message-ID: <4398F879.2010306@roq.com> Date: Fri, 09 Dec 2005 14:22:33 +1100 From: Michael Vince User-Agent: Mozilla/5.0 (X11; U; FreeBSD amd64; en-US; rv:1.7.12) Gecko/20051208 X-Accept-Language: en-us, en MIME-Version: 1.0 To: daniel References: <200512081051.38306.danstemporaryaccount@yahoo.ca> In-Reply-To: <200512081051.38306.danstemporaryaccount@yahoo.ca> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV using ClamSMTP Cc: freebsd-questions@freebsd.org Subject: Re: MySQL "forgot" some of my data! X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 09 Dec 2005 03:22:40 -0000 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