<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="mysql-cluster-replication-auto-sync">
      </a>
      25.7.9.1 NDB Cluster Replication: Automating Synchronization of the Replica to
the Source Binary Log
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045085828912">
  </a>
  <a class="indexterm" name="idm46045085827456">
  </a>
  <p>
   It is possible to automate much of the process described in the
        previous section (see
   <a class="xref" href="mysql-cluster-replication-backups.html" title="25.7.9 NDB Cluster Backups With NDB Cluster Replication">
    Section 25.7.9, “NDB Cluster Backups With NDB Cluster Replication”
   </a>
   ). The
        following Perl script
   <code class="filename">
    reset-replica.pl
   </code>
   serves as an example of how you can do this.
  </p>
  <a class="indexterm" name="idm46045085823936">
  </a>
  <a class="indexterm" name="idm46045085822416">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-perl"><div class="docs-select-all right" id="sa18724443"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-perl"><span class="token comment" spellcheck="true">#!/user/bin/perl -w</span>

<span class="token comment" spellcheck="true">#  file: reset-replica.pl</span>

<span class="token comment" spellcheck="true">#  Copyright (c) 2005, 2020, Oracle and/or its affiliates. All rights reserved.</span>

<span class="token comment" spellcheck="true">#  This program is free software; you can redistribute it and/or modify</span>
<span class="token comment" spellcheck="true">#  it under the terms of the GNU General Public License as published by</span>
<span class="token comment" spellcheck="true">#  the Free Software Foundation; either version 2 of the License, or</span>
<span class="token comment" spellcheck="true">#  (at your option) any later version.</span>

<span class="token comment" spellcheck="true">#  This program is distributed in the hope that it will be useful,</span>
<span class="token comment" spellcheck="true">#  but WITHOUT ANY WARRANTY; without even the implied warranty of</span>
<span class="token comment" spellcheck="true">#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the</span>
<span class="token comment" spellcheck="true">#  GNU General Public License for more details.</span>

<span class="token comment" spellcheck="true">#  You should have received a copy of the GNU General Public License</span>
<span class="token comment" spellcheck="true">#  along with this program; if not, write to:</span>
<span class="token comment" spellcheck="true">#  Free Software Foundation, Inc.</span>
<span class="token comment" spellcheck="true">#  59 Temple Place, Suite 330</span>
<span class="token comment" spellcheck="true">#  Boston, MA 02111-1307 USA</span>
<span class="token comment" spellcheck="true">#</span>
<span class="token comment" spellcheck="true">#  Version 1.1</span>


<span class="token comment" spellcheck="true">######################## Includes ###############################</span>

<span class="token keyword">use</span> DBI<span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">######################## Globals ################################</span>

<span class="token keyword">my</span>  <span class="token variable">$m_host</span><span class="token operator">=</span><span class="token string">''</span><span class="token punctuation">;</span>
<span class="token keyword">my</span>  <span class="token variable">$m_port</span><span class="token operator">=</span><span class="token string">''</span><span class="token punctuation">;</span>
<span class="token keyword">my</span>  <span class="token variable">$m_user</span><span class="token operator">=</span><span class="token string">''</span><span class="token punctuation">;</span>
<span class="token keyword">my</span>  <span class="token variable">$m_pass</span><span class="token operator">=</span><span class="token string">''</span><span class="token punctuation">;</span>
<span class="token keyword">my</span>  <span class="token variable">$s_host</span><span class="token operator">=</span><span class="token string">''</span><span class="token punctuation">;</span>
<span class="token keyword">my</span>  <span class="token variable">$s_port</span><span class="token operator">=</span><span class="token string">''</span><span class="token punctuation">;</span>
<span class="token keyword">my</span>  <span class="token variable">$s_user</span><span class="token operator">=</span><span class="token string">''</span><span class="token punctuation">;</span>
<span class="token keyword">my</span>  <span class="token variable">$s_pass</span><span class="token operator">=</span><span class="token string">''</span><span class="token punctuation">;</span>
<span class="token keyword">my</span>  <span class="token variable">$dbhM</span><span class="token operator">=</span><span class="token string">''</span><span class="token punctuation">;</span>
<span class="token keyword">my</span>  <span class="token variable">$dbhS</span><span class="token operator">=</span><span class="token string">''</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">####################### Sub Prototypes ##########################</span>

<span class="token function"><span class="token keyword">sub</span> CollectCommandPromptInfo</span><span class="token punctuation">;</span>
<span class="token function"><span class="token keyword">sub</span> ConnectToDatabases</span><span class="token punctuation">;</span>
<span class="token function"><span class="token keyword">sub</span> DisconnectFromDatabases</span><span class="token punctuation">;</span>
<span class="token function"><span class="token keyword">sub</span> GetReplicaEpoch</span><span class="token punctuation">;</span>
<span class="token function"><span class="token keyword">sub</span> GetSourceInfo</span><span class="token punctuation">;</span>
<span class="token function"><span class="token keyword">sub</span> UpdateReplica</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">######################## Program Main ###########################</span>

CollectCommandPromptInfo<span class="token punctuation">;</span>
ConnectToDatabases<span class="token punctuation">;</span>
GetReplicaEpoch<span class="token punctuation">;</span>
GetSourceInfo<span class="token punctuation">;</span>
UpdateReplica<span class="token punctuation">;</span>
DisconnectFromDatabases<span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">################## Collect Command Prompt Info ##################</span>

<span class="token function"><span class="token keyword">sub</span> CollectCommandPromptInfo</span>
<span class="token punctuation">{</span>
  <span class="token comment" spellcheck="true">### Check that user has supplied correct number of command line args</span>
  <span class="token keyword">die</span> <span class="token string">"Usage:\n
       reset-replica &gt;source MySQL host&lt; &gt;source MySQL port&lt; \n
                   &gt;source user&lt; &gt;source pass&lt; &gt;replica MySQL host&lt; \n
                   &gt;replica MySQL port&lt; &gt;replica user&lt; &gt;replica pass&lt; \n
       All 8 arguments must be passed. Use BLANK for NULL passwords\n"</span>
       <span class="token keyword">unless</span> <span class="token variable">@ARGV</span> <span class="token operator">==</span> <span class="token number">8</span><span class="token punctuation">;</span>

  <span class="token variable">$m_host</span>  <span class="token operator">=</span>  <span class="token variable">$ARGV</span><span class="token punctuation">[</span><span class="token number">0</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
  <span class="token variable">$m_port</span>  <span class="token operator">=</span>  <span class="token variable">$ARGV</span><span class="token punctuation">[</span><span class="token number">1</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
  <span class="token variable">$m_user</span>  <span class="token operator">=</span>  <span class="token variable">$ARGV</span><span class="token punctuation">[</span><span class="token number">2</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
  <span class="token variable">$m_pass</span>  <span class="token operator">=</span>  <span class="token variable">$ARGV</span><span class="token punctuation">[</span><span class="token number">3</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
  <span class="token variable">$s_host</span>  <span class="token operator">=</span>  <span class="token variable">$ARGV</span><span class="token punctuation">[</span><span class="token number">4</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
  <span class="token variable">$s_port</span>  <span class="token operator">=</span>  <span class="token variable">$ARGV</span><span class="token punctuation">[</span><span class="token number">5</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
  <span class="token variable">$s_user</span>  <span class="token operator">=</span>  <span class="token variable">$ARGV</span><span class="token punctuation">[</span><span class="token number">6</span><span class="token punctuation">]</span><span class="token punctuation">;</span>
  <span class="token variable">$s_pass</span>  <span class="token operator">=</span>  <span class="token variable">$ARGV</span><span class="token punctuation">[</span><span class="token number">7</span><span class="token punctuation">]</span><span class="token punctuation">;</span>

  <span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token variable">$m_pass</span> <span class="token operator">eq</span> <span class="token string">"BLANK"</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token variable">$m_pass</span> <span class="token operator">=</span> <span class="token string">''</span><span class="token punctuation">;</span><span class="token punctuation">}</span>
  <span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token variable">$s_pass</span> <span class="token operator">eq</span> <span class="token string">"BLANK"</span><span class="token punctuation">)</span> <span class="token punctuation">{</span> <span class="token variable">$s_pass</span> <span class="token operator">=</span> <span class="token string">''</span><span class="token punctuation">;</span><span class="token punctuation">}</span>
<span class="token punctuation">}</span>

<span class="token comment" spellcheck="true">###############  Make connections to both databases #############</span>

<span class="token function"><span class="token keyword">sub</span> ConnectToDatabases</span>
<span class="token punctuation">{</span>
  <span class="token comment" spellcheck="true">### Connect to both source and replica cluster databases</span>

  <span class="token comment" spellcheck="true">### Connect to source</span>
  <span class="token variable">$dbhM</span>
    <span class="token operator">=</span> DBI<span class="token operator">-&gt;</span>connect<span class="token punctuation">(</span>
    <span class="token string">"dbi:mysql:database=mysql;host=$m_host;port=$m_port"</span><span class="token punctuation">,</span>
    <span class="token string">"$m_user"</span><span class="token punctuation">,</span> <span class="token string">"$m_pass"</span><span class="token punctuation">)</span>
      <span class="token operator">or</span> <span class="token keyword">die</span> <span class="token string">"Can't connect to source cluster MySQL process!
              Error: $DBI::errstr\n"</span><span class="token punctuation">;</span>

  <span class="token comment" spellcheck="true">### Connect to replica</span>
  <span class="token variable">$dbhS</span>
    <span class="token operator">=</span> DBI<span class="token operator">-&gt;</span>connect<span class="token punctuation">(</span>
          <span class="token string">"dbi:mysql:database=mysql;host=$s_host"</span><span class="token punctuation">,</span>
          <span class="token string">"$s_user"</span><span class="token punctuation">,</span> <span class="token string">"$s_pass"</span><span class="token punctuation">)</span>
    <span class="token operator">or</span> <span class="token keyword">die</span> <span class="token string">"Can't connect to replica cluster MySQL process!
            Error: $DBI::errstr\n"</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token comment" spellcheck="true">################  Disconnect from both databases ################</span>

<span class="token function"><span class="token keyword">sub</span> DisconnectFromDatabases</span>
<span class="token punctuation">{</span>
  <span class="token comment" spellcheck="true">### Disconnect from source</span>

  <span class="token variable">$dbhM</span><span class="token operator">-&gt;</span>disconnect
  <span class="token operator">or</span> warn <span class="token string">" Disconnection failed: $DBI::errstr\n"</span><span class="token punctuation">;</span>

  <span class="token comment" spellcheck="true">### Disconnect from replica</span>

  <span class="token variable">$dbhS</span><span class="token operator">-&gt;</span>disconnect
  <span class="token operator">or</span> warn <span class="token string">" Disconnection failed: $DBI::errstr\n"</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token comment" spellcheck="true">######################  Find the last good GCI ##################</span>

<span class="token function"><span class="token keyword">sub</span> GetReplicaEpoch</span>
<span class="token punctuation">{</span>
  <span class="token variable">$sth</span> <span class="token operator">=</span> <span class="token variable">$dbhS</span><span class="token operator">-&gt;</span>prepare<span class="token punctuation">(</span><span class="token string">"SELECT MAX(epoch)
                         FROM mysql.ndb_apply_status;"</span><span class="token punctuation">)</span>
      <span class="token operator">or</span> <span class="token keyword">die</span> <span class="token string">"Error while preparing to select epoch from replica: "</span><span class="token punctuation">,</span>
             <span class="token variable">$dbhS</span><span class="token operator">-&gt;</span>errstr<span class="token punctuation">;</span>

  <span class="token variable">$sth</span><span class="token operator">-&gt;</span>execute
      <span class="token operator">or</span> <span class="token keyword">die</span> <span class="token string">"Selecting epoch from replica error: "</span><span class="token punctuation">,</span> <span class="token variable">$sth</span><span class="token operator">-&gt;</span>errstr<span class="token punctuation">;</span>

  <span class="token variable">$sth</span><span class="token operator">-&gt;</span>bind_col <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token operator">\</span><span class="token variable">$epoch</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token variable">$sth</span><span class="token operator">-&gt;</span>fetch<span class="token punctuation">;</span>
  <span class="token keyword">print</span> <span class="token string">"\tReplica epoch =  $epoch\n"</span><span class="token punctuation">;</span>
  <span class="token variable">$sth</span><span class="token operator">-&gt;</span>finish<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token comment" spellcheck="true">#######  Find the position of the last GCI in the binary log ########</span>

<span class="token function"><span class="token keyword">sub</span> GetSourceInfo</span>
<span class="token punctuation">{</span>
  <span class="token variable">$sth</span> <span class="token operator">=</span> <span class="token variable">$dbhM</span><span class="token operator">-&gt;</span>prepare<span class="token punctuation">(</span><span class="token string">"SELECT
                           SUBSTRING_INDEX(File, '/', -1), Position
                         FROM mysql.ndb_binlog_index
                         WHERE epoch &gt; $epoch
                         ORDER BY epoch ASC LIMIT 1;"</span><span class="token punctuation">)</span>
      <span class="token operator">or</span> <span class="token keyword">die</span> <span class="token string">"Prepare to select from source error: "</span><span class="token punctuation">,</span> <span class="token variable">$dbhM</span><span class="token operator">-&gt;</span>errstr<span class="token punctuation">;</span>

  <span class="token variable">$sth</span><span class="token operator">-&gt;</span>execute
      <span class="token operator">or</span> <span class="token keyword">die</span> <span class="token string">"Selecting from source error: "</span><span class="token punctuation">,</span> <span class="token variable">$sth</span><span class="token operator">-&gt;</span>errstr<span class="token punctuation">;</span>

  <span class="token variable">$sth</span><span class="token operator">-&gt;</span>bind_col <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token operator">\</span><span class="token variable">$binlog</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token variable">$sth</span><span class="token operator">-&gt;</span>bind_col <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token operator">\</span><span class="token variable">$binpos</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
  <span class="token variable">$sth</span><span class="token operator">-&gt;</span>fetch<span class="token punctuation">;</span>
  <span class="token keyword">print</span> <span class="token string">"\tSource binary log file =  $binlog\n"</span><span class="token punctuation">;</span>
  <span class="token keyword">print</span> <span class="token string">"\tSource binary log position =  $binpos\n"</span><span class="token punctuation">;</span>
  <span class="token variable">$sth</span><span class="token operator">-&gt;</span>finish<span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token comment" spellcheck="true">##########  Set the replica to process from that location #########</span>

<span class="token function"><span class="token keyword">sub</span> UpdateReplica</span>
<span class="token punctuation">{</span>
  <span class="token variable">$sth</span> <span class="token operator">=</span> <span class="token variable">$dbhS</span><span class="token operator">-&gt;</span>prepare<span class="token punctuation">(</span><span class="token string">"CHANGE REPLICATION SOURCE TO
                         SOURCE_LOG_FILE='$binlog',
                         SOURCE_LOG_POS=$binpos;"</span><span class="token punctuation">)</span>
      <span class="token operator">or</span> <span class="token keyword">die</span> <span class="token string">"Prepare to CHANGE REPLICATION SOURCE error: "</span><span class="token punctuation">,</span> <span class="token variable">$dbhS</span><span class="token operator">-&gt;</span>errstr<span class="token punctuation">;</span>

  <span class="token variable">$sth</span><span class="token operator">-&gt;</span>execute
       <span class="token operator">or</span> <span class="token keyword">die</span> <span class="token string">"CHANGE REPLICATION SOURCE on replica error: "</span><span class="token punctuation">,</span> <span class="token variable">$sth</span><span class="token operator">-&gt;</span>errstr<span class="token punctuation">;</span>
  <span class="token variable">$sth</span><span class="token operator">-&gt;</span>finish<span class="token punctuation">;</span>
  <span class="token keyword">print</span> <span class="token string">"\tReplica has been updated. You may now start the replica.\n"</span><span class="token punctuation">;</span>
<span class="token punctuation">}</span>

<span class="token comment" spellcheck="true"># end reset-replica.pl</span></code></pre>
  </div>
 </div>
 <br/>
</div>
