[ale] MySQL array based snapshot

[Lightner, Jeffrey]

The question says “array” which makes it sound like they’re NOT talking about LVM snapshot but rather native snapshotting within an external storage array (e.g. Thin Image within Hitachi).   Snapshots one has to be careful with because often they do not contain all the data from the original but only a subset with pointers back to the original and separate storage for deltas where the copy the original diverge over time.

Also the original question didn’t say he wanted this for backup.  It said the only results of a web search were thousands of cautioning about relying on snapshots as a replacement for backups.   It’s quite possible the question has another purpose in mind entirely (e.g. using a copy of a database for testing or reporting purposes).


From: ale-bounces at ale.org [mailto:ale-bounces at ale.org] On Behalf Of Jim Kinney
Sent: Monday, November 21, 2016 4:39 PM
To: Atlanta Linux Enthusiasts
Subject: Re: [ale] MySQL array based snapshot

This sounds to me like a LVM snapshot of the database file space.

http://dev<http://devN>.mysql.com/doc/refman/5.7/en/backup-methods.html

Snapshot _IS_ a backup as long as it is copied off the original machine. Loud google mouths are not always accurate. Dump your Google search cookie and see what comes up again.

There is a STUN event but it's pretty short. The key that I see is to lock the DB after a flush (see link) , make the LVM snapshot, then release the lock, copy out the snapshot as a backup. The performance drag happens during the copy of the snapshot but the copy can be 'nice 20' if things are on a decent drive subsystem and drive failure is not expected to be eminent.

On Mon, 2016-11-21 at 14:45 -0500, Chris Fowler wrote:
I've received this question today and I can't pinpoint on if MySQL supports it or not.   Google is flooding me with "snapshot is not a backup" pages.

"Need Processor counts and if the internal DB supports array based snapshots which result in minimal STUN of the virtual disk."

I'm running MySQL 5.X.


Chris

_______________________________________________

Ale mailing list

Ale at ale.org<mailto:Ale at ale.org>

http://mail.ale.org/mailman/listinfo/ale

See JOBS, ANNOUNCE and SCHOOLS lists at

http://mail.ale.org/mailman/listinfo

--

James P. Kinney III



Every time you stop a school, you will have to build a jail. What you

gain at one end you lose at the other. It's like feeding a dog on his

own tail. It won't fatten the dog.

- Speech 11/23/1900 Mark Twain



http://heretothereideas.blogspot.com/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.ale.org/pipermail/ale/attachments/20161122/db1c4caf/attachment.html>