<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<title>ProActive Software</title>
<meta name="GENERATOR" content="MSHTML 8.00.6001.19298">
</head>
<body bgcolor="#ffffff" text="#000000"><!--ppd1000037-->
<div dir="ltr" align="left"><span class="726292912-13062013"><font color="#0000ff" face="Calibri">Thanks for the good input, especially from Paul and Dirk. It may help people to understand that we have approximately 500 instances of MySQL distributed across
a dozen servers in 6 pacemaker+drbd clusters. Each cluster consists of 2 nodes, with 40 instances of MySQL per node. Each node is primary for its own drbd device and replicates to the other node. If a node fails, the surviving node becomes primary for both
drbd devices and temporarily runs all 80 MySQL instances. Running that many MySQL instances on one server has never been a significant problem for us and performance has been good. However, if we switch to innodb, then the disk and memory footprint will change
and I don't think the same model could be maintained.</font></span></div>
<div dir="ltr" align="left"><span class="726292912-13062013"><font color="#0000ff" face="Calibri"></font></span> </div>
<div dir="ltr" align="left"><span class="726292912-13062013"><font color="#0000ff" face="Calibri">Secondly, we backup each mysql instance at night by performing two rsyncs of the live running database to a separate archive server.The first rsync copies the
changes that have occurred during the production day. Then we do a 'flush tables with read lock' and perform a second rsync, which gets the changes that occurred while the first rsync was in progress. The databases are read locked for about 1 second, often
less. In this manner, we get a 100% binary backup of the databases, but the system remains accessible to users 24x7. This is way faster and more efficient than use mysqldump. However, again, this technique will apparently not work with innodb, for some reason
that I still do not understand. Apparently there is no 'flush tables with read lock' in innodb, or if there is, it still does not work the way it does with myisam, which is what I am struggling to understand.</font></span></div>
<div dir="ltr" align="left"><span class="726292912-13062013"><font color="#0000ff" face="Calibri"></font></span> </div>
<div dir="ltr" align="left"><span class="726292912-13062013"><font color="#0000ff" face="Calibri">To answer Jacek's question about why we don't use built in mysql replication, it is because it is inefficient and error prone. There are two many conditions that
lead to the slave not being a 100% exact copy of the master, and for that reason many db admins do not consider mysql slaves to be safe for using as a backup source. Verifying slave reliability can only be properly done using checksums, which is an expensive
process. Re-syncing the slave to the master is painful. And finally, doing so would require us to run twice as many instances of MySQL (1000 instead of 500) and our cluster node failover strategy described above would be more financially expensive due to the
requirement for more hardware. </font></span></div>
<div dir="ltr" align="left"><span class="726292912-13062013"><font color="#0000ff" face="Calibri"></font></span> </div>
<div dir="ltr" align="left"><span class="726292912-13062013"><font color="#0000ff" face="Calibri">I realize that all this mysql chatter is dangerously close to being off-topic, so I appreciate everyone's patience. Thanks again. </font></span></div>
<blockquote style="BORDER-LEFT: #0000ff 2px solid; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px" dir="ltr">
<font color="#0000ff" face="Calibri"></font><font color="#0000ff" face="Calibri"></font><font color="#0000ff" face="Calibri"></font><font color="#0000ff" face="Calibri"></font><font color="#0000ff" face="Calibri"></font><font color="#0000ff" face="Calibri"></font><font color="#0000ff" face="Calibri"></font><br>
For what it's worth, we run MySQL+InnoDB on DRBD and use Heartbeat+Pacemaker for failover between nodes at different sites. This supports our Moodle VLE which is an important part of our course provision. As yet we have had no problems. We also use
<b>mysqldump</b> to back up databases.<br>
<br>
To quote from Wikipedia:<br>
<br>
" <span style="TEXT-ALIGN: left; TEXT-TRANSFORM: none; BACKGROUND-COLOR: rgb(255,255,255); TEXT-INDENT: 0px; DISPLAY: inline !important; FONT: 13px/19px sans-serif; WHITE-SPACE: normal; FLOAT: none; LETTER-SPACING: normal; COLOR: rgb(0,0,0); WORD-SPACING: 0px; -webkit-text-stroke-width: 0px">
InnoDB recovers from a crash or other unexpected shutdown by replaying its logs"<br>
<br>
These logs, by nature of the fact they're on a DRBD device, will be identical on both nodes, so if, say, node-A fails, node-B can assume Primary role for the DRBD device and mysql can start up, replaying its logs to may sure the data is up-to-date.
<br>
<br>
I think whoever told you mysql would not start properly was possibly referring to what would happen if you simply copied all the files (with, for instance, rsync) whilst the database was active.<br>
</span>
<pre class="moz-signature" cols="120">--
Paul Walsh
</pre>
</blockquote>
<BR><BR><FONT color=silver><SPAN style="FONT-SIZE: 7.5pt; COLOR: gray; FONT-FAMILY: Verdana"><FONT size=1>Disclaimer - June 13, 2013</FONT></FONT></SPAN></FONT> <P></P><FONT size=1><FONT face=Verdana color=gray><SPAN style="FONT-SIZE: 7.5pt; COLOR: gray; FONT-FAMILY: Verdana">This email and any files <FONT size=1>transmitted</FONT> with it are confidential and intended solely for <STRONG>'Paul Walsh',drbd-user@lists.linbit.com</STRONG></SPAN></FONT><FONT face=Verdana color=gray><SPAN style="FONT-SIZE: 7.5pt; COLOR: gray; FONT-FAMILY: Verdana">. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.</SPAN></FONT></FONT></body>
</html>