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
environment.
That will depend a lot on the workload.
Yes, very pertinent point. I should have qualified how I have a
write-intensive environment.
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!
Jed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org