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. > > > > 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.
Yes, it is a single machine (the one with the dev't environment) but it has a dozen databases on it, so the -A option is not appropriate. The engine is the default MyISAM and this made me think if it is the reason that two backups in a row were incomplete. Should I be converting all tables 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 hadn't had such a hiccup for years now, so it came as a surprise to me. I was thinking that I should perhaps use --lock-tables, because the --single- transaction states: "This option issues a BEGIN SQL statement before dumping data from the server" and I don't really understand how this will affect the backup ... ? -- Regards, Mick
signature.asc
Description: This is a digitally signed message part.
