| 1 |
On 8/19/2010 12:03 PM, Mick wrote: |
| 2 |
> I use mysqldump to back up a database from a development environment and |
| 3 |
> upload it to a production environment. |
| 4 |
> |
| 5 |
> A couple of days ago I was surprised to see that I was getting errors as soon |
| 6 |
> as I uploaded the backed up database to the production machine! I repeated |
| 7 |
> the backup (more in disbelief than anything else) but the error remained. |
| 8 |
> |
| 9 |
> I spent a few minutes looking around and scratching my head as to what was |
| 10 |
> amiss with it, until eventually I noticed that the recent backup was smaller |
| 11 |
> than the previous version (it should have been bigger due to extra data that |
| 12 |
> has accumulated in the database). I had another final go in running the same |
| 13 |
> good ol' mysqldump command and this time it worked. The backup was a |
| 14 |
> reasonable size and the upload restored the application in the production |
| 15 |
> environment in a good working order. |
| 16 |
> |
| 17 |
> Is there a right and a wrong way of backing up mysql? Did I do something |
| 18 |
> wrong? How should one verify that a back up is sound? (Imagine trying to |
| 19 |
> restore from that incomplete backup!) |
| 20 |
|
| 21 |
mysqldump -A --single-transaction |
| 22 |
|
| 23 |
That's usually the best way to backup if you have a single machine. |
| 24 |
Without --single-transaction you may or may not get a proper backup when |
| 25 |
using Innodb tables on a busy server. |
| 26 |
|
| 27 |
However in a busy production environment it's usually best to use a |
| 28 |
slave to do backups. Bringing LVM snapshots into the mix is also useful, |
| 29 |
but you must lock and flush Mysql in order to get a correct snapshot |
| 30 |
which makes it only an option on the slave. |
| 31 |
|
| 32 |
kashani |
Archives