<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="replication-solutions-backups-read-only">
      </a>
      19.4.1.3 Backing Up a Source or Replica by Making It Read Only
     </h4>
    </div>
   </div>
  </div>
  <p>
   It is possible to back up either source or replica servers in a
        replication setup by acquiring a global read lock and
        manipulating the
   <a class="link" href="server-system-variables.html#sysvar_read_only">
    <code class="literal">
     read_only
    </code>
   </a>
   system variable to change the read-only state of the server to
        be backed up:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Make the server read-only, so that it processes only
            retrievals and blocks updates.
     </p>
    </li>
    <li class="listitem">
     <p>
      Perform the backup.
     </p>
    </li>
    <li class="listitem">
     <p>
      Change the server back to its normal read/write state.
     </p>
    </li>
   </ol>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    The instructions in this section place the server to be backed
          up in a state that is safe for backup methods that get the
          data from the server, such as
    <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
     <span class="command">
      <strong>
       mysqldump
      </strong>
     </span>
    </a>
    (see
    <a class="xref" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
     Section 6.5.4, “mysqldump — A Database Backup Program”
    </a>
    ). You should not attempt to
          use these instructions to make a binary backup by copying
          files directly because the server may still have modified data
          cached in memory and not flushed to disk.
   </p>
  </div>
  <p>
   The following instructions describe how to do this for a source
        and for a replica. For both scenarios discussed here, suppose
        that you have the following replication setup:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A source server S1
     </p>
    </li>
    <li class="listitem">
     <p>
      A replica server R1 that has S1 as its source
     </p>
    </li>
    <li class="listitem">
     <p>
      A client C1 connected to S1
     </p>
    </li>
    <li class="listitem">
     <p>
      A client C2 connected to R1
     </p>
    </li>
   </ul>
  </div>
  <p>
   In either scenario, the statements to acquire the global read
        lock and manipulate the
   <a class="link" href="server-system-variables.html#sysvar_read_only">
    <code class="literal">
     read_only
    </code>
   </a>
   variable are
        performed on the server to be backed up and do not propagate to
        any replicas of that server.
  </p>
  <p>
   <span class="bold">
    <strong>
     Scenario 1: Backup with a Read-Only
        Source
    </strong>
   </span>
  </p>
  <p>
   Put the source S1 in a read-only state by executing these
        statements on it:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa36311640"><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-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">FLUSH</span> <span class="token keyword">TABLES</span> <span class="token keyword">WITH</span> <span class="token keyword">READ</span> <span class="token keyword">LOCK</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> <span class="token keyword">read_only</span> <span class="token operator">=</span> <span class="token keyword">ON</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   While S1 is in a read-only state, the following properties are
        true:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Requests for updates sent by C1 to S1 block because the
            server is in read-only mode.
     </p>
    </li>
    <li class="listitem">
     <p>
      Requests for query results sent by C1 to S1 succeed.
     </p>
    </li>
    <li class="listitem">
     <p>
      Making a backup on S1 is safe.
     </p>
    </li>
    <li class="listitem">
     <p>
      Making a backup on R1 is not safe. This server is still
            running, and might be processing the binary log or update
            requests coming from client C2.
     </p>
    </li>
   </ul>
  </div>
  <p>
   While S1 is read only, perform the backup. For example, you can
        use
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   .
  </p>
  <p>
   After the backup operation on S1 completes, restore S1 to its
        normal operational state by executing these statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa71916111"><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-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> <span class="token keyword">read_only</span> <span class="token operator">=</span> <span class="token keyword">OFF</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">UNLOCK</span> <span class="token keyword">TABLES</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Although performing the backup on S1 is safe (as far as the
        backup is concerned), it is not optimal for performance because
        clients of S1 are blocked from executing updates.
  </p>
  <p>
   This strategy applies to backing up a source in a replication
        setup, but can also be used for a single server in a
        nonreplication setting.
  </p>
  <p>
   <span class="bold">
    <strong>
     Scenario 2: Backup with a Read-Only
        Replica
    </strong>
   </span>
  </p>
  <p>
   Put the replica R1 in a read-only state by executing these
        statements on it:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa1306950"><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-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">FLUSH</span> <span class="token keyword">TABLES</span> <span class="token keyword">WITH</span> <span class="token keyword">READ</span> <span class="token keyword">LOCK</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> <span class="token keyword">read_only</span> <span class="token operator">=</span> <span class="token keyword">ON</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   While R1 is in a read-only state, the following properties are
        true:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The source S1 continues to operate, so making a backup on
            the source is not safe.
     </p>
    </li>
    <li class="listitem">
     <p>
      The replica R1 is stopped, so making a backup on the replica
            R1 is safe.
     </p>
    </li>
   </ul>
  </div>
  <p>
   These properties provide the basis for a popular backup
        scenario: Having one replica busy performing a backup for a
        while is not a problem because it does not affect the entire
        network, and the system is still running during the backup. In
        particular, clients can still perform updates on the source
        server, which remains unaffected by backup activity on the
        replica.
  </p>
  <p>
   While R1 is read only, perform the backup. For example, you can
        use
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   .
  </p>
  <p>
   After the backup operation on R1 completes, restore R1 to its
        normal operational state by executing these statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa45052324"><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-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">GLOBAL</span> <span class="token keyword">read_only</span> <span class="token operator">=</span> <span class="token keyword">OFF</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">UNLOCK</span> <span class="token keyword">TABLES</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   After the replica is restored to normal operation, it again
        synchronizes to the source by catching up with any outstanding
        updates from the source's binary log.
  </p>
 </div>
 <br/>
</div>
