1 |
On Friday 20 August 2010 05:58:49 kashani wrote: |
2 |
> On 8/19/2010 12:03 PM, Mick wrote: |
3 |
> > I use mysqldump to back up a database from a development environment and |
4 |
> > upload it to a production environment. |
5 |
> > |
6 |
> > A couple of days ago I was surprised to see that I was getting errors as |
7 |
> > soon as I uploaded the backed up database to the production machine! I |
8 |
> > repeated the backup (more in disbelief than anything else) but the error |
9 |
> > remained. |
10 |
> > |
11 |
> > I spent a few minutes looking around and scratching my head as to what |
12 |
> > was amiss with it, until eventually I noticed that the recent backup was |
13 |
> > smaller than the previous version (it should have been bigger due to |
14 |
> > extra data that has accumulated in the database). I had another final |
15 |
> > go in running the same good ol' mysqldump command and this time it |
16 |
> > worked. The backup was a reasonable size and the upload restored the |
17 |
> > application in the production environment in a good working order. |
18 |
> > |
19 |
> > Is there a right and a wrong way of backing up mysql? Did I do something |
20 |
> > wrong? How should one verify that a back up is sound? (Imagine trying |
21 |
> > to restore from that incomplete backup!) |
22 |
> |
23 |
> mysqldump -A --single-transaction |
24 |
> |
25 |
> That's usually the best way to backup if you have a single machine. |
26 |
> Without --single-transaction you may or may not get a proper backup when |
27 |
> using Innodb tables on a busy server. |
28 |
|
29 |
Yes, it is a single machine (the one with the dev't environment) but it has a |
30 |
dozen databases on it, so the -A option is not appropriate. |
31 |
|
32 |
The engine is the default MyISAM and this made me think if it is the reason |
33 |
that two backups in a row were incomplete. Should I be converting all tables |
34 |
to Innodb? |
35 |
|
36 |
The production server is separate. |
37 |
|
38 |
> However in a busy production environment it's usually best to use a |
39 |
> slave to do backups. Bringing LVM snapshots into the mix is also useful, |
40 |
> but you must lock and flush Mysql in order to get a correct snapshot |
41 |
> which makes it only an option on the slave. |
42 |
|
43 |
Thanks kashani, I'll try the --single-transaction and see what I get. I |
44 |
hadn't had such a hiccup for years now, so it came as a surprise to me. I was |
45 |
thinking that I should perhaps use --lock-tables, because the --single- |
46 |
transaction states: |
47 |
|
48 |
"This option issues a BEGIN SQL statement before dumping data from the server" |
49 |
|
50 |
and I don't really understand how this will affect the backup ... ? |
51 |
-- |
52 |
Regards, |
53 |
Mick |