<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="replication-solutions-diffengines">
      </a>
      19.4.4 Using Replication with Different Source and Replica Storage Engines
     </h3>
    </div>
   </div>
  </div>
  <p>
   It does not matter for the replication process whether the
      original table on the source and the replicated table on the
      replica use different storage engine types. In fact, the
   <a class="link" href="server-system-variables.html#sysvar_default_storage_engine">
    <code class="literal">
     default_storage_engine
    </code>
   </a>
   system
      variable is not replicated.
  </p>
  <p>
   This provides a number of benefits in the replication process in
      that you can take advantage of different engine types for
      different replication scenarios. For example, in a typical
      scale-out scenario (see
   <a class="xref" href="replication-solutions-scaleout.html" title="19.4.5 Using Replication for Scale-Out">
    Section 19.4.5, “Using Replication for Scale-Out”
   </a>
   ), you want to use
   <code class="literal">
    InnoDB
   </code>
   tables on the source to take advantage
      of the transactional functionality, but use
   <code class="literal">
    MyISAM
   </code>
   on the replicas where transaction
      support is not required because the data is only read. When using
      replication in a data-logging environment you may want to use the
   <code class="literal">
    Archive
   </code>
   storage engine on the replica.
  </p>
  <p>
   Configuring different engines on the source and replica depends on
      how you set up the initial replication process:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      If you used
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      to create the
          database snapshot on your source, you could edit the dump file
          text to change the engine type used on each table.
     </p>
     <p>
      Another alternative for
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      is to
          disable engine types that you do not want to use on the
          replica before using the dump to build the data on the
          replica. For example, you can add the
      <code class="option">
       --skip-federated
      </code>
      option on your replica to
          disable the
      <a class="link" href="federated-storage-engine.html" title="18.8 The FEDERATED Storage Engine">
       <code class="literal">
        FEDERATED
       </code>
      </a>
      engine. If
          a specific engine does not exist for a table to be created,
          MySQL uses the default engine type, usually
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      . (This requires that the
      <a class="link" href="sql-mode.html#sqlmode_no_engine_substitution">
       <code class="literal">
        NO_ENGINE_SUBSTITUTION
       </code>
      </a>
      SQL
          mode is not enabled.) If you want to disable additional
          engines in this way, you may want to consider building a
          special binary to be used on the replica that supports only
          the engines you want.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you use raw data files (a binary backup) to set up the
          replica, it is not possible to change the initial table
          format. Instead, use
      <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
       <code class="literal">
        ALTER
          TABLE
       </code>
      </a>
      to change the table types after the replica
          has been started.
     </p>
    </li>
    <li class="listitem">
     <p>
      For new source/replica replication setups where there are
          currently no tables on the source, avoid specifying the engine
          type when creating new tables.
     </p>
    </li>
   </ul>
  </div>
  <p>
   If you are already running a replication solution and want to
      convert your existing tables to another engine type, follow these
      steps:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Stop the replica from running replication updates:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa62110754"><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">STOP</span> <span class="token keyword">REPLICA</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      This makes it possible to change engine types without
          interruption.
     </p>
    </li>
    <li class="listitem">
     <p>
      Execute an
      <code class="literal">
       ALTER TABLE ...
          ENGINE='
       <em class="replaceable">
        <code>
         engine_type
        </code>
       </em>
       '
      </code>
      for
          each table to be changed.
     </p>
    </li>
    <li class="listitem">
     <p>
      Start the replication process again:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa51004341"><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">START</span> <span class="token keyword">REPLICA</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ol>
  </div>
  <p>
   Although the
   <a class="link" href="server-system-variables.html#sysvar_default_storage_engine">
    <code class="literal">
     default_storage_engine
    </code>
   </a>
   variable
      is not replicated, be aware that
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE
      TABLE
    </code>
   </a>
   and
   <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
    <code class="literal">
     ALTER TABLE
    </code>
   </a>
   statements that include the engine specification are replicated to
      the replica correctly. If, in the case of a
   <a class="link" href="csv-storage-engine.html" title="18.4 The CSV Storage Engine">
    <code class="literal">
     CSV
    </code>
   </a>
   table, you execute this
      statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa13030485"><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">ALTER</span> <span class="token keyword">TABLE</span> csvtable <span class="token keyword">ENGINE</span><span class="token operator">=</span><span class="token string">'MyISAM'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   This statement is replicated; the table's engine type on the
      replica is converted to
   <code class="literal">
    InnoDB
   </code>
   , even if you
      have previously changed the table type on the replica to an engine
      other than
   <code class="literal">
    CSV
   </code>
   . If you want to retain engine
      differences on the source and replica, you should be careful to
      use the
   <a class="link" href="server-system-variables.html#sysvar_default_storage_engine">
    <code class="literal">
     default_storage_engine
    </code>
   </a>
   variable on the source when creating a new table. For example,
      instead of:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa1063653"><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">CREATE</span> <span class="token keyword">TABLE</span> tablea <span class="token punctuation">(</span>columna <span class="token datatype">int</span><span class="token punctuation">)</span> <span class="token keyword">Engine</span><span class="token operator">=</span>MyISAM<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Use this format:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa65213496"><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> default_storage_engine<span class="token operator">=</span>MyISAM<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> tablea <span class="token punctuation">(</span>columna <span class="token datatype">int</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   When replicated, the
   <a class="link" href="server-system-variables.html#sysvar_default_storage_engine">
    <code class="literal">
     default_storage_engine
    </code>
   </a>
   variable
      is ignored, and the
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statement executes on the replica using the replica's default
      engine.
  </p>
 </div>
 <br/>
</div>
