<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 14 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:"Consolas","serif";}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D">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).
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span style="font-size:10.0pt;font-family:"Tahoma","sans-serif""> ale-bounces@ale.org [mailto:ale-bounces@ale.org]
<b>On Behalf Of </b>Jim Kinney<br>
<b>Sent:</b> Monday, November 21, 2016 4:39 PM<br>
<b>To:</b> Atlanta Linux Enthusiasts<br>
<b>Subject:</b> Re: [ale] MySQL array based snapshot<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">This sounds to me like a LVM snapshot of the database file space.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><a href="http://devN">http://dev</a>.mysql.com/doc/refman/5.7/en/backup-methods.html<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">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.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">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.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">On Mon, 2016-11-21 at 14:45 -0500, Chris Fowler wrote:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<div>
<p class="MsoNormal"><span style="font-family:"Arial","sans-serif";color:black">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.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Arial","sans-serif";color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Arial","sans-serif";color:black">"Need Processor counts and if the internal DB supports array based snapshots which result in minimal STUN of the virtual disk."<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Arial","sans-serif";color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Arial","sans-serif";color:black">I'm running MySQL 5.X.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Arial","sans-serif";color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Arial","sans-serif";color:black"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Arial","sans-serif";color:black">Chris<o:p></o:p></span></p>
</div>
</div>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>Ale mailing list<o:p></o:p></pre>
<pre><a href="mailto:Ale@ale.org">Ale@ale.org</a><o:p></o:p></pre>
<pre><a href="http://mail.ale.org/mailman/listinfo/ale">http://mail.ale.org/mailman/listinfo/ale</a><o:p></o:p></pre>
<pre>See JOBS, ANNOUNCE and SCHOOLS lists at<o:p></o:p></pre>
<pre><a href="http://mail.ale.org/mailman/listinfo">http://mail.ale.org/mailman/listinfo</a><o:p></o:p></pre>
</blockquote>
<div>
<pre>-- <o:p></o:p></pre>
<pre>James P. Kinney III<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>Every time you stop a school, you will have to build a jail. What you<o:p></o:p></pre>
<pre>gain at one end you lose at the other. It's like feeding a dog on his<o:p></o:p></pre>
<pre>own tail. It won't fatten the dog.<o:p></o:p></pre>
<pre>- Speech 11/23/1900 Mark Twain<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre><a href="http://heretothereideas.blogspot.com/">http://heretothereideas.blogspot.com/</a><o:p></o:p></pre>
</div>
</div>
</body>
</html>