From owner-freebsd-questions@FreeBSD.ORG Tue Oct 7 15:01:49 2008 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id 7AFB31065688 for ; Tue, 7 Oct 2008 15:01:49 +0000 (UTC) (envelope-from m.seaman@infracaninophile.co.uk) Received: from smtp.infracaninophile.co.uk (gate6.infracaninophile.co.uk [IPv6:2001:8b0:151:1::1]) by mx1.freebsd.org (Postfix) with ESMTP id C76EA8FC41 for ; Tue, 7 Oct 2008 15:01:48 +0000 (UTC) (envelope-from m.seaman@infracaninophile.co.uk) Received: from lack-of-gravitas.thebunker.net (gateway.ash.thebunker.net [213.129.64.4]) (authenticated bits=0) by smtp.infracaninophile.co.uk (8.14.3/8.14.3) with ESMTP id m97F1VhD066735 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Tue, 7 Oct 2008 16:01:37 +0100 (BST) (envelope-from m.seaman@infracaninophile.co.uk) X-DKIM: Sendmail DKIM Filter v2.7.2 smtp.infracaninophile.co.uk m97F1VhD066735 DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=infracaninophile.co.uk; s=200708; t=1223391697; bh=Y58Md0YP5vnDlu Ww3MUzxkJrm9nt4r+BOP0lb4wdEhY=; h=Message-ID:Date:From:MIME-Version: To:CC:Subject:References:In-Reply-To:Content-Type: Content-Transfer-Encoding:Cc:Content-Type:Date:From:In-Reply-To: Message-ID:Mime-Version:References:To; z=Message-ID:=20<48EB79CA.7 080901@infracaninophile.co.uk>|Date:=20Tue,=2007=20Oct=202008=2016: 01:30=20+0100|From:=20Matthew=20Seaman=20|Organization:=20Infracaninophile|User-Agent:=20Thunderbird= 202.0.0.17=20(X11/20081002)|MIME-Version:=201.0|To:=20mcoyles@horbu ry.wakefield.sch.uk|CC:=20freebsd-questions@freebsd.org|Subject:=20 Re:=20Consistency=20of=20MySQL=20dumps...|References:=20<008101c928 8a$1cffbdd0$56ff3970$@wakefield.sch.uk>|In-Reply-To:=20<008101c9288 a$1cffbdd0$56ff3970$@wakefield.sch.uk>|X-Enigmail-Version:=200.95.6 |Content-Type:=20text/plain=3B=20charset=3DUTF-8=3B=20format=3Dflow ed|Content-Transfer-Encoding:=207bit; b=C1w9ve8zYAunVkMLG5+kUBNRFfJ xNc3NYmHn1R/Ne22OC8g9F47ayI2b+hgdNdcg35k38utavgqa9kODLYmdnnL6EHrEZ7 dokd55FuCzlTfT3Olrck1mioHZsre6EsNdBBxXjKWf7Fk6LYb4xK4k3N0qfec+qrnn1 SDzAcb/aIk= Message-ID: <48EB79CA.7080901@infracaninophile.co.uk> Date: Tue, 07 Oct 2008 16:01:30 +0100 From: Matthew Seaman Organization: Infracaninophile User-Agent: Thunderbird 2.0.0.17 (X11/20081002) MIME-Version: 1.0 To: mcoyles@horbury.wakefield.sch.uk References: <008101c9288a$1cffbdd0$56ff3970$@wakefield.sch.uk> In-Reply-To: <008101c9288a$1cffbdd0$56ff3970$@wakefield.sch.uk> X-Enigmail-Version: 0.95.6 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0 (smtp.infracaninophile.co.uk [81.187.76.162]); Tue, 07 Oct 2008 16:01:37 +0100 (BST) X-Virus-Scanned: ClamAV 0.94/8386/Tue Oct 7 14:50:57 2008 on happy-idiot-talk.infracaninophile.co.uk X-Virus-Status: Clean X-Spam-Status: No, score=-2.6 required=5.0 tests=AWL,BAYES_00,DKIM_SIGNED, DKIM_VERIFIED,SPF_FAIL autolearn=no version=3.2.5 X-Spam-Checker-Version: SpamAssassin 3.2.5 (2008-06-10) on happy-idiot-talk.infracaninophile.co.uk Cc: freebsd-questions@freebsd.org Subject: Re: Consistency of MySQL dumps... 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: Tue, 07 Oct 2008 15:01:49 -0000 -----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 Marc Coyles wrote: | Here's one that's puzzling me... | | If I use /usr/local/bin/mysqldump to make a backup of a database, the | file it produces fails to restore with "Check syntax near..." error. | | If I then head into cPanel, to their "Backup" menu, and take a backup of | the database from there, the file it produces also fails to restore | with "Check syntax near..." error, but at a COMPLETELY different point | thru the restore. cPanel probably runs mysqldump internally, but with slightly different options than you've been using on the command line. | If I head into cPanel, to phpmyadmin, and do an export from there... the | file restores PERFECTLY without errors. phpMyAdmin I happen to know generates the dump file by running its own dynamically generated SQL. If it works for you... | Sooo... how can I write a script that'll backup a MySQL database and | produce a useable file?? | | This problem is occurring on 2 of my 8 databases... it appears the | chosen software used to produce the dump of MySQL data is the culprit... | what is the best commandline (ie: cron-able) tool to use for the task? Without seeing the error message is (the interesting bit is usually slightly before the 'Check syntax near...' instruction) and what exactly the SQL code around that point is, I'm shooting in the dark somewhat. mysqldump(1) is the canonical tool for producing database dumps for backup. There's a classic problem to do with 'Max Packet Size' where mysqldump is allowed to produce much larger chunks of SQL than mysql client is allowed to swallow. This is easily cured by setting the max-packet-size variable during your data load session -- or set the variable from my.cnf so it's there all the time. Other possible problems: mysqldump usually works by locking each table in sequence while dumping it out. This means that things like Foreign Keys can get out of sync if you're dumping the database while it is particularly active. To cure that problem, either you need to tell mysqldump to acquire a DB-wide lock (which will block all other access) or you have to use InnoDB tables and enable transactions. You can in theory dump all of the databases in an instance of MySQL as a single transaction, although you may well run into the 4GB transaction size limit on 32bit machines if your databases are that large. (64bit machines have a max transaction size so large it's unfeasible to ever run into it). In summary: you should always be able to get a good backup out of mysqldump, but you'll have to play around with variables and command line options a bit to make it work smoothly in your specific circumstanes. Cheers, Matthew - -- Dr Matthew J Seaman MA, D.Phil. Flat 3 ~ 7 Priory Courtyard PGP: http://www.infracaninophile.co.uk/pgpkey Ramsgate ~ Kent, CT11 9PW, UK -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.9 (FreeBSD) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEAREDAAYFAkjrecoACgkQ3jDkPpsZ+VZzwgCfaUFLS7L1uY93TazYk3wensoo 3HgAoMHeMGvgNGIJByB/WeESuBfp/gfj =2pXg -----END PGP SIGNATURE-----