<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="innodb-and-mysql-replication">
      </a>
      17.19 InnoDB and MySQL Replication
     </h2>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045149111920">
  </a>
  <p>
   It is possible to use replication in a way where the storage engine
    on the replica is not the same as the storage engine on the source.
    For example, you can replicate modifications to an
   <code class="literal">
    InnoDB
   </code>
   table on the source to a
   <code class="literal">
    MyISAM
   </code>
   table on the replica. For more information
    see,
   <a class="xref" href="replication-solutions-diffengines.html" title="19.4.4 Using Replication with Different Source and Replica Storage Engines">
    Section 19.4.4, “Using Replication with Different Source and Replica Storage Engines”
   </a>
   .
  </p>
  <p>
   For information about setting up a replica, see
   <a class="xref" href="replication-setup-replicas.html" title="19.1.2.6 Setting Up Replicas">
    Section 19.1.2.6, “Setting Up Replicas”
   </a>
   , and
   <a class="xref" href="replication-snapshot-method.html" title="19.1.2.5 Choosing a Method for Data Snapshots">
    Section 19.1.2.5, “Choosing a Method for Data Snapshots”
   </a>
   . To make a new replica
    without taking down the source or an existing replica, use the
   <a class="link" href="mysql-enterprise-backup.html" title="32.1 MySQL Enterprise Backup Overview">
    MySQL Enterprise Backup
   </a>
   product.
  </p>
  <p>
   Transactions that fail on the source do not affect replication.
    MySQL replication is based on the binary log where MySQL writes SQL
    statements that modify data. A transaction that fails (for example,
    because of a foreign key violation, or because it is rolled back) is
    not written to the binary log, so it is not sent to replicas. See
   <a class="xref" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    Section 15.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”
   </a>
   .
  </p>
  <p>
   <b>
    Replication and CASCADE.
   </b>
   Cascading actions for
   <code class="literal">
    InnoDB
   </code>
   tables on the
      source are executed on the replica
   <span class="emphasis">
    <em>
     only
    </em>
   </span>
   if
      the tables sharing the foreign key relation use
   <code class="literal">
    InnoDB
   </code>
   on both the source and replica. This is
      true whether you are using statement-based or row-based
      replication. Suppose that you have started replication, and then
      create two tables on the source, where
   <code class="literal">
    InnoDB
   </code>
   is defined as the default storage engine, using the following
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa96885351"><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 keyword">CREATE</span> <span class="token keyword">TABLE</span> fc1 <span class="token punctuation">(</span>
    i <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    j <span class="token datatype">INT</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> fc2 <span class="token punctuation">(</span>
    m <span class="token datatype">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
    n <span class="token datatype">INT</span><span class="token punctuation">,</span>
    <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> ni <span class="token punctuation">(</span>n<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> fc1 <span class="token punctuation">(</span>i<span class="token punctuation">)</span>
        <span class="token keyword">ON</span> <span class="token keyword">DELETE</span> <span class="token keyword">CASCADE</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   If the replica has
   <code class="literal">
    MyISAM
   </code>
   defined as the default
    storage engine, the same tables are created on the replica, but they
    use the
   <code class="literal">
    MyISAM
   </code>
   storage engine, and the
   <code class="literal">
    FOREIGN KEY
   </code>
   option is ignored. Now we insert some
    rows into the tables on the source:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa75177364"><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">source&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> fc1 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</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 punctuation">(</span><span class="token number">2</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 output">Query OK, 2 rows affected (0.09 sec)</span>
<span class="token output">Records: 2  Duplicates: 0  Warnings: 0</span>

<span class="token prompt">source&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> fc2 <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</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 punctuation">(</span><span class="token number">2</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 punctuation">(</span><span class="token number">3</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 output">Query OK, 3 rows affected (0.19 sec)</span>
<span class="token output">Records: 3  Duplicates: 0  Warnings: 0</span></code></pre>
  </div>
  <p>
   At this point, on both the source and the replica, table
   <code class="literal">
    fc1
   </code>
   contains 2 rows, and table
   <code class="literal">
    fc2
   </code>
   contains 3 rows, as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa81892216"><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">source&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> fc1<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> i <span class="token punctuation">|</span> j    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 1 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2 <span class="token punctuation">|</span>    2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span>

<span class="token prompt">source&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> fc2<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> m <span class="token punctuation">|</span> n    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 1 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2 <span class="token punctuation">|</span>    2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 3 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">3 rows in set (0.00 sec)</span>

<span class="token prompt">replica&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> fc1<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> i <span class="token punctuation">|</span> j    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 1 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2 <span class="token punctuation">|</span>    2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">2 rows in set (0.00 sec)</span>

<span class="token prompt">replica&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> fc2<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> m <span class="token punctuation">|</span> n    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 1 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2 <span class="token punctuation">|</span>    2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 3 <span class="token punctuation">|</span>    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">3 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   Now suppose that you perform the following
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   statement on the source:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa11977555"><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">source&gt;</span> <span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> fc1 <span class="token keyword">WHERE</span> i<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.09 sec)</span></code></pre>
  </div>
  <p>
   Due to the cascade, table
   <code class="literal">
    fc2
   </code>
   on the source now
    contains only 1 row:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa99018064"><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">source&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> fc2<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> m <span class="token punctuation">|</span> n <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 2 <span class="token punctuation">|</span> 2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   However, the cascade does not propagate on the replica because on
    the replica the
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   for
   <code class="literal">
    fc1
   </code>
   deletes no rows from
   <code class="literal">
    fc2
   </code>
   .
    The replica's copy of
   <code class="literal">
    fc2
   </code>
   still contains all of
    the rows that were originally inserted:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa64956061"><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">replica&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> fc2<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> m <span class="token punctuation">|</span> n <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> 1 <span class="token punctuation">|</span> 1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 3 <span class="token punctuation">|</span> 1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> 2 <span class="token punctuation">|</span> 2 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">3 rows in set (0.00 sec)</span></code></pre>
  </div>
  <p>
   This difference is due to the fact that the cascading deletes are
    handled internally by the
   <code class="literal">
    InnoDB
   </code>
   storage engine,
    which means that none of the changes are logged.
  </p>
 </div>
 <br/>
</div>
