<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="replication-rules-examples">
      </a>
      19.2.5.3 Interactions Between Replication Filtering Options
     </h4>
    </div>
   </div>
  </div>
  <p>
   If you use a combination of database-level and table-level
        replication filtering options, the replica first accepts or
        ignores events using the database options, then it evaluates all
        events permitted by those options according to the table
        options. This can sometimes lead to results that seem
        counterintuitive. It is also important to note that the results
        vary depending on whether the operation is logged using
        statement-based or row-based binary logging format. If you want
        to be sure that your replication filters always operate in the
        same way independently of the binary logging format, which is
        particularly important if you are using mixed binary logging
        format, follow the guidance in this topic.
  </p>
  <p>
   The effect of the replication filtering options differs between
        binary logging formats because of the way the database name is
        identified. With statement-based format, DML statements are
        handled based on the current database, as specified by the
   <a class="link" href="use.html" title="15.8.4 USE Statement">
    <code class="literal">
     USE
    </code>
   </a>
   statement. With row-based
        format, DML statements are handled based on the database where
        the modified table exists. DDL statements are always filtered
        based on the current database, as specified by the
   <a class="link" href="use.html" title="15.8.4 USE Statement">
    <code class="literal">
     USE
    </code>
   </a>
   statement, regardless of the
        binary logging format.
  </p>
  <p>
   An operation that involves multiple tables can also be affected
        differently by replication filtering options depending on the
        binary logging format. Operations to watch out for include
        transactions involving multi-table
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   statements, triggers,
        cascading foreign keys, stored functions that update multiple
        tables, and DML statements that invoke stored functions that
        update one or more tables. If these operations update both
        filtered-in and filtered-out tables, the results can vary with
        the binary logging format.
  </p>
  <p>
   If you need to guarantee that your replication filters operate
        consistently regardless of the binary logging format,
        particularly if you are using mixed binary logging format
        (
   <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
    <code class="literal">
     binlog_format=MIXED
    </code>
   </a>
   ), use only
        table-level replication filtering options, and do not use
        database-level replication filtering options. Also, do not use
        multi-table DML statements that update both filtered-in and
        filtered-out tables.
  </p>
  <p>
   If you need to use a combination of database-level and
        table-level replication filters, and want these to operate as
        consistently as possible, choose one of the following
        strategies:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      If you use row-based binary logging format
            (
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
       <code class="literal">
        binlog_format=ROW
       </code>
      </a>
      ), for
            DDL statements, rely on the
      <a class="link" href="use.html" title="15.8.4 USE Statement">
       <code class="literal">
        USE
       </code>
      </a>
      statement to set the
            database and do not specify the database name. You can
            consider changing to row-based binary logging format for
            improved consistency with replication filtering. See
      <a class="xref" href="binary-log-setting.html" title="7.4.4.2 Setting The Binary Log Format">
       Section 7.4.4.2, “Setting The Binary Log Format”
      </a>
      for the conditions that
            apply to changing the binary logging format.
     </p>
    </li>
    <li class="listitem">
     <p>
      If you use statement-based or mixed binary logging format
            (
      <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
       <code class="literal">
        binlog_format=STATEMENT
       </code>
      </a>
      or
      <code class="literal">
       MIXED
      </code>
      ), for both DML and DDL statements,
            rely on the
      <a class="link" href="use.html" title="15.8.4 USE Statement">
       <code class="literal">
        USE
       </code>
      </a>
      statement and
            do not use the database name. Also, do not use multi-table
            DML statements that update both filtered-in and filtered-out
            tables.
     </p>
    </li>
   </ol>
  </div>
  <div class="example">
   <a name="idm46045137374144">
   </a>
   <p class="title">
    <b>
     Example 19.7 A
     <a class="link" href="replication-options-replica.html#option_mysqld_replicate-ignore-db">
      <code class="option">
       --replicate-ignore-db
      </code>
     </a>
     option and a
     <a class="link" href="replication-options-replica.html#option_mysqld_replicate-do-table">
      <code class="option">
       --replicate-do-table
      </code>
     </a>
     option
    </b>
   </p>
   <div class="example-contents">
    <p>
     On the replication source server, the following statements are
          issued:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa8661991"><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">USE</span> db1<span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token operator">LIKE</span> t1<span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> db2<span class="token punctuation">.</span>t3 <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 punctuation">;</span></code></pre>
    </div>
    <p>
     The replica has the following replication filtering options
          set:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa35810981"><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-simple">replicate<span class="token operator">-</span>ignore<span class="token operator">-</span>db <span class="token operator">=</span> db1
replicate<span class="token operator">-</span>do<span class="token operator">-</span>table <span class="token operator">=</span> db2<span class="token punctuation">.</span>t3</code></pre>
    </div>
    <p>
     The DDL statement
     <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
      <code class="literal">
       CREATE TABLE
      </code>
     </a>
     creates the table in
     <code class="literal">
      db1
     </code>
     , as specified by
          the preceding
     <a class="link" href="use.html" title="15.8.4 USE Statement">
      <code class="literal">
       USE
      </code>
     </a>
     statement.
          The replica filters out this statement according to its
     <a class="link" href="replication-options-replica.html#option_mysqld_replicate-ignore-db">
      <code class="option">
       --replicate-ignore-db = db1
      </code>
     </a>
     option, because
     <code class="literal">
      db1
     </code>
     is the current
          database. This result is the same whatever the binary logging
          format is on the replication source server. However, the
          result of the DML
     <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
      <code class="literal">
       INSERT
      </code>
     </a>
     statement is different depending on the binary logging format:
    </p>
    <div class="itemizedlist">
     <ul class="itemizedlist" style="list-style-type: disc; ">
      <li class="listitem">
       <p>
        If row-based binary logging format is in use on the source
              (
        <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
         <code class="literal">
          binlog_format=ROW
         </code>
        </a>
        ), the
              replica evaluates the
        <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
         <code class="literal">
          INSERT
         </code>
        </a>
        operation using the
              database where the table exists, which is named as
        <code class="literal">
         db2
        </code>
        . The database-level option
        <a class="link" href="replication-options-replica.html#option_mysqld_replicate-ignore-db">
         <code class="option">
          --replicate-ignore-db =
              db1
         </code>
        </a>
        , which is evaluated first, therefore does not
              apply. The table-level option
        <a class="link" href="replication-options-replica.html#option_mysqld_replicate-do-table">
         <code class="option">
          --replicate-do-table =
              db2.t3
         </code>
        </a>
        does apply, so the replica applies the
              change to table
        <code class="literal">
         t3
        </code>
        .
       </p>
      </li>
      <li class="listitem">
       <p>
        If statement-based binary logging format is in use on the
              source
              (
        <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
         <code class="literal">
          binlog_format=STATEMENT
         </code>
        </a>
        ),
              the replica evaluates the
        <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
         <code class="literal">
          INSERT
         </code>
        </a>
        operation using the
              default database, which was set by the
        <a class="link" href="use.html" title="15.8.4 USE Statement">
         <code class="literal">
          USE
         </code>
        </a>
        statement to
        <code class="literal">
         db1
        </code>
        and has not been changed. According
              to its database-level
        <a class="link" href="replication-options-replica.html#option_mysqld_replicate-ignore-db">
         <code class="option">
          --replicate-ignore-db = db1
         </code>
        </a>
        option, it therefore ignores the operation and does not
              apply the change to table
        <code class="literal">
         t3
        </code>
        . The
              table-level option
        <a class="link" href="replication-options-replica.html#option_mysqld_replicate-do-table">
         <code class="option">
          --replicate-do-table =
              db2.t3
         </code>
        </a>
        is not checked, because the statement
              already matched a database-level option and was ignored.
       </p>
      </li>
     </ul>
    </div>
    <p>
     If the
     <a class="link" href="replication-options-replica.html#option_mysqld_replicate-ignore-db">
      <code class="option">
       --replicate-ignore-db =
          db1
      </code>
     </a>
     option on the replica is necessary, and the use
          of statement-based (or mixed) binary logging format on the
          source is also necessary, the results can be made consistent
          by omitting the database name from the
     <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
      <code class="literal">
       INSERT
      </code>
     </a>
     statement and relying on
          a
     <a class="link" href="use.html" title="15.8.4 USE Statement">
      <code class="literal">
       USE
      </code>
     </a>
     statement instead, as
          follows:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa10770099"><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">USE</span> db1<span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t2 <span class="token operator">LIKE</span> t1<span class="token punctuation">;</span>
<span class="token keyword">USE</span> db2<span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t3 <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 punctuation">;</span></code></pre>
    </div>
    <p>
     In this case, the replica always evaluates the
     <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
      <code class="literal">
       INSERT
      </code>
     </a>
     statement based on the
          database
     <code class="literal">
      db2
     </code>
     . Whether the operation is
          logged in statement-based or row-based binary format, the
          results remain the same.
    </p>
   </div>
  </div>
  <br class="example-break"/>
 </div>
 <br/>
</div>
