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 1OmXIX-0005KI-74 for garchives@archives.gentoo.org; Fri, 20 Aug 2010 19:30:41 +0000 Received: from pigeon.gentoo.org (localhost [127.0.0.1]) by pigeon.gentoo.org (Postfix) with SMTP id 7747EE0946; Fri, 20 Aug 2010 19:30:33 +0000 (UTC) Received: from mail-wy0-f181.google.com (mail-wy0-f181.google.com [74.125.82.181]) by pigeon.gentoo.org (Postfix) with ESMTP id 3EC7EE0946 for ; Fri, 20 Aug 2010 19:30:33 +0000 (UTC) Received: by wyf28 with SMTP id 28so4556309wyf.40 for ; Fri, 20 Aug 2010 12:30:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:from:reply-to:to:subject:date :user-agent:references:in-reply-to:mime-version:content-type :content-transfer-encoding:message-id; bh=JSK1VWAqYycs3S6zLe4BFgo9qfHYO7dOzGexmSd29TI=; b=hyBBHoQXTCIjCEGIYRjjyk67hF1Izd2edYX+lv9dDXHdkm8wvurk35gFkXr49fXu/X a65RyIkAItbK2UG/xGlx+6PcIK7aE4S3oITFZS9iU5N+JaFTAS4wWaRwWs2n+KCfKZSO 4P5Et5vR4E7LdymxRc1V0DNs+35uhf4Pie1Ew= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=from:reply-to:to:subject:date:user-agent:references:in-reply-to :mime-version:content-type:content-transfer-encoding:message-id; b=eIBGkS6n/ZePxW3w36roDpr4RaXuoDXQWnyrcFcPeIHwX+uFeHsOSCImzrJy2VKNBl y0LjJVSuKQsIV1yQwnwXfy29+upmy9RSiET8e1WYpx5BfFg7e+WtPfQzecLqv37fjOt3 o27Fgo+A5RDsBYrLKSFKQ/QhhC8SpPUaE4zrY= Received: by 10.216.232.90 with SMTP id m68mr1687052weq.10.1282332626870; Fri, 20 Aug 2010 12:30:26 -0700 (PDT) Received: from (230.3.169.217.in-addr.arpa [217.169.3.230]) by mx.google.com with ESMTPS id k83sm2051442weq.38.2010.08.20.12.30.20 (version=TLSv1/SSLv3 cipher=RC4-MD5); Fri, 20 Aug 2010 12:30:22 -0700 (PDT) From: Mick To: gentoo-user@lists.gentoo.org Subject: Re: [gentoo-user] [OT] Incomplete mysql backup Date: Fri, 20 Aug 2010 20:29:48 +0100 User-Agent: KMail/1.13.5 (Linux/2.6.34-gentoo-r1; KDE/4.4.5; x86_64; ; ) References: <201008192004.05558.michaelkintzios@gmail.com> <4C6E0B89.9040809@badapple.net> In-Reply-To: <4C6E0B89.9040809@badapple.net> 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 Content-Type: multipart/signed; boundary="nextPart3291623.NVe3oPh5k3"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <201008202030.15398.michaelkintzios@gmail.com> X-Archives-Salt: eb0122f9-27d2-43a1-96b4-c454da2befb5 X-Archives-Hash: 1e20a21859139ded54db162552220087 --nextPart3291623.NVe3oPh5k3 Content-Type: Text/Plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable On Friday 20 August 2010 05:58:49 kashani wrote: > 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. > >=20 > > 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. > >=20 > > 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. > >=20 > > Is there a right and a wrong way of backing up mysql? Did I do somethi= ng > > wrong? How should one verify that a back up is sound? (Imagine trying > > to restore from that incomplete backup!) >=20 > mysqldump -A --single-transaction >=20 > 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. Yes, it is a single machine (the one with the dev't environment) but it has= a=20 dozen databases on it, so the -A option is not appropriate. =20 The engine is the default MyISAM and this made me think if it is the reason= =20 that two backups in a row were incomplete. Should I be converting all tabl= es=20 to Innodb? The production server is separate. > 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. Thanks kashani, I'll try the --single-transaction and see what I get. I=20 hadn't had such a hiccup for years now, so it came as a surprise to me. I = was=20 thinking that I should perhaps use --lock-tables, because the --single- transaction states:=20 "This option issues a BEGIN SQL statement before dumping data from the serv= er" and I don't really understand how this will affect the backup ... ? =2D-=20 Regards, Mick --nextPart3291623.NVe3oPh5k3 Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part. -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.16 (GNU/Linux) iEYEABECAAYFAkxu18cACgkQVTDTR3kpaLZMnwCeP7AnnDyPGYxSIIoAnqkhV5KF HX8AoOpwdJwKNcI5TRDVgElhLUYQ2QFH =/CYh -----END PGP SIGNATURE----- --nextPart3291623.NVe3oPh5k3--