Baron Schwartz wrote:
>> In contrast, InnoDB actually needs to "shut down" to cleanly close its table
>> structures before you can physically copy the filesystem.
> Actually, not true -- an LVM snapshot (or other snapshot) is a great
> way to take a backup of InnoDB. You just need a truly atomic
> snapshot, and then you can let InnoDB run its recovery routine on the
> snapshot to get back to a consistent state.
Fascinating. From reading the mysql docs, I would never have assumed
that I was doing the right thing by taking a snapshot of a live innodb
instance with a flush tables. I will certainly keep this in mind for
future. My current snapshotting procedure takes between 45s and 90s
depending on which instance I snapshot, and that's about 20G of data
that I start copying over. That's not counting time copying anything
from the snapshotted volume.
>> Why do I leave it firewalled? Because once you start writing to an LVM
>> volume that's been snapshotted, you start copying disk extents like mad,
>> creating a high load condition that can force queries to reach
>> connect_timeout. I have my connect_timeout set pretty low in my
> That will depend a lot on the workload.
Yes, very pertinent point. I should have qualified how I have a
I used to just drop the firewall after I restarted mysql, but when my
application reliability was criticized during my snapshots, I had to
leave the firewall up until the snapshotted copy was copied off before
pooling it back in. Luckily I have four servers and there's only rare
conditions when I need to switch masters. I'm grateful that I have a
maintenance window for the site, too.
Thank you, Baron!