From owner-freebsd-questions@FreeBSD.ORG Thu Dec 8 15:51:50 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 DB63016A426 for ; Thu, 8 Dec 2005 15:51:50 +0000 (GMT) (envelope-from danstemporaryaccount@yahoo.ca) Received: from kenyan.nodes.net.ad-flow.com (KENYAN.NODES.NET.AD-FLOW.COM [66.117.33.216]) by mx1.FreeBSD.org (Postfix) with ESMTP id 7318F43D55 for ; Thu, 8 Dec 2005 15:51:48 +0000 (GMT) (envelope-from danstemporaryaccount@yahoo.ca) Received: from [66.38.196.186] (helo=dquinn.cubearmy.com) by kenyan.nodes.net.ad-flow.com with esmtps (TLSv1:AES256-SHA:256) (Exim 4.50) id 1EkKBD-000Mut-O2 for freebsd-questions@freebsd.org; Thu, 08 Dec 2005 11:43:19 +0000 From: daniel To: freebsd-questions@freebsd.org Date: Thu, 8 Dec 2005 10:51:38 -0500 User-Agent: KMail/1.9 MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200512081051.38306.danstemporaryaccount@yahoo.ca> Subject: 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: Thu, 08 Dec 2005 15:51:51 -0000 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