Consistency of MySQL dumps...

[Matthew Seaman]

-----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-----