Date: Tue, 07 Oct 2008 16:01:30 +0100 From: Matthew Seaman <m.seaman@infracaninophile.co.uk> To: mcoyles@horbury.wakefield.sch.uk Cc: freebsd-questions@freebsd.org Subject: Re: Consistency of MySQL dumps... Message-ID: <48EB79CA.7080901@infracaninophile.co.uk> In-Reply-To: <008101c9288a$1cffbdd0$56ff3970$@wakefield.sch.uk> References: <008101c9288a$1cffbdd0$56ff3970$@wakefield.sch.uk>
next in thread | previous in thread | raw e-mail | index | archive | help
-----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-----
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?48EB79CA.7080901>