From mboxrd@z Thu Jan 1 00:00:00 1970 Received: from pigeon.gentoo.org ([208.92.234.80] helo=lists.gentoo.org) by finch.gentoo.org with esmtp (Exim 4.60) (envelope-from ) id 1OmJhS-00063y-7P for garchives@archives.gentoo.org; Fri, 20 Aug 2010 04:59:30 +0000 Received: from pigeon.gentoo.org (localhost [127.0.0.1]) by pigeon.gentoo.org (Postfix) with SMTP id 5574DE0937; Fri, 20 Aug 2010 04:58:55 +0000 (UTC) Received: from www01.badapple.net (www01.badapple.net [64.79.219.163]) by pigeon.gentoo.org (Postfix) with ESMTP id 3FC7FE0937 for ; Fri, 20 Aug 2010 04:58:55 +0000 (UTC) Received: from [127.0.0.1] (unknown [76.14.68.122]) (Authenticated sender: ramin@badapple.net) by www01.badapple.net (Postfix) with ESMTPSA id D8FA99FAFBCA for ; Thu, 19 Aug 2010 21:58:51 -0700 (PDT) Message-ID: <4C6E0B89.9040809@badapple.net> Date: Thu, 19 Aug 2010 21:58:49 -0700 From: kashani User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.8) Gecko/20100802 Thunderbird/3.1.2 Precedence: bulk List-Post: List-Help: List-Unsubscribe: List-Subscribe: List-Id: Gentoo Linux mail X-BeenThere: gentoo-user@lists.gentoo.org Reply-to: gentoo-user@lists.gentoo.org MIME-Version: 1.0 To: gentoo-user@lists.gentoo.org Subject: Re: [gentoo-user] [OT] Incomplete mysql backup References: <201008192004.05558.michaelkintzios@gmail.com> In-Reply-To: <201008192004.05558.michaelkintzios@gmail.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Archives-Salt: b501d8e1-07fa-408f-9f52-53de9c07ed71 X-Archives-Hash: 38087de7a2664cc7e52e26bf27971ad2 On 8/19/2010 12:03 PM, Mick wrote: > I use mysqldump to back up a database from a development environment and > upload it to a production environment. > > A couple of days ago I was surprised to see that I was getting errors as soon > as I uploaded the backed up database to the production machine! I repeated > the backup (more in disbelief than anything else) but the error remained. > > I spent a few minutes looking around and scratching my head as to what was > amiss with it, until eventually I noticed that the recent backup was smaller > than the previous version (it should have been bigger due to extra data that > has accumulated in the database). I had another final go in running the same > good ol' mysqldump command and this time it worked. The backup was a > reasonable size and the upload restored the application in the production > environment in a good working order. > > Is there a right and a wrong way of backing up mysql? Did I do something > wrong? How should one verify that a back up is sound? (Imagine trying to > restore from that incomplete backup!) mysqldump -A --single-transaction That's usually the best way to backup if you have a single machine. Without --single-transaction you may or may not get a proper backup when using Innodb tables on a busy server. However in a busy production environment it's usually best to use a slave to do backups. Bringing LVM snapshots into the mix is also useful, but you must lock and flush Mysql in order to get a correct snapshot which makes it only an option on the slave. kashani