<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=us-ascii"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
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=HU link="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoNormal>Hi List,<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>We are using M/S replication in a couple of clusters, and there is an issue that has been causing headaches for me for quite some time.<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>My problem comes from the fact that binlog files grow very quickly on both the Master and Slave nodes. <o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal>Let<span lang=EN-US>’s assume, that node 1 is the master – it logs all operations to binlog.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Node 2 is the slave, it replicates everything properly. (It is strange, however, that node2 must also generate and keep binlog files while it is a slave, but let’s assume that this is by design).<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>There are ways to configure mysql to keep the binlog files only for some time, e.g. 10 days, but I had an issue with this:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>To explain the issue, please consider the following case:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Let’s say that both node1 and node2 are up-to-date, and we did a failover test on day 0. <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>DB1 is the master, DB2 is the slave.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>DB1 has master position “A”, DB1 has master position “B”.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>After 20 days, lots of binlog files exist on both servers, I would like to get rid of them, as the slave is up-to-date.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>I decide to delete all binlog files older than 1 day by issuing “purge binary logs…”.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>I try to fail over so that DB2 becomes the master, but DB1 tries to connect to DB2 for replication and wants to replicate starting from a position that it “remembers” from the times when DB2 was the master, and starts to look for some binlog files that are 20 days old. Now, the issue is, that those old binlog files have been deleted since, and replication stops with error (cannot find binlogs).<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>Am I doing something wrong here, or is something configured badly?<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>OR am I assuming correctly that in order to purge the binlog files from both servers I need to make a failover first?<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>Thank you!<o:p></o:p></span></p></div></body></html>