<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="replication-solutions-partitioning">
      </a>
      19.4.6 Replicating Different Databases to Different Replicas
     </h3>
    </div>
   </div>
  </div>
  <p>
   There may be situations where you have a single source server and
      want to replicate different databases to different replicas. For
      example, you may want to distribute different sales data to
      different departments to help spread the load during data
      analysis. A sample of this layout is shown in
   <a class="xref" href="replication-solutions-partitioning.html#figure_replication-multi-db" title="Figure 19.2 Replicating Databases to Separate Replicas">
    Figure 19.2, “Replicating Databases to Separate Replicas”
   </a>
   .
  </p>
  <div class="figure">
   <a name="figure_replication-multi-db">
   </a>
   <p class="title">
    <b>
     Figure 19.2 Replicating Databases to Separate Replicas
    </b>
   </p>
   <div class="figure-contents">
    <div class="mediaobject">
     <img alt="The MySQL source has three databases, databaseA, databaseB, and databaseC. databaseA is replicated only to MySQL Replica 1, databaseB is replicated only to MySQL Replica 2, and databaseC is replicated only to MySQL Replica 3." src="images/multi-db.png" style="width: 100%; max-width: 370px;"/>
    </div>
   </div>
  </div>
  <br class="figure-break"/>
  <p>
   You can achieve this separation by configuring the source and
      replicas as normal, and then limiting the binary log statements
      that each replica processes by using the
   <a class="link" href="replication-options-replica.html#option_mysqld_replicate-wild-do-table">
    <code class="option">
     --replicate-wild-do-table
    </code>
   </a>
   configuration option on each replica.
  </p>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    You should
    <span class="emphasis">
     <em>
      not
     </em>
    </span>
    use
    <a class="link" href="replication-options-replica.html#option_mysqld_replicate-do-db">
     <code class="option">
      --replicate-do-db
     </code>
    </a>
    for this
        purpose when using statement-based replication, since
        statement-based replication causes this option's effects to
        vary according to the database that is currently selected. This
        applies to mixed-format replication as well, since this enables
        some updates to be replicated using the statement-based format.
   </p>
   <p>
    However, it should be safe to use
    <a class="link" href="replication-options-replica.html#option_mysqld_replicate-do-db">
     <code class="option">
      --replicate-do-db
     </code>
    </a>
    for this
        purpose if you are using row-based replication only, since in
        this case the currently selected database has no effect on the
        option's operation.
   </p>
  </div>
  <p>
   For example, to support the separation as shown in
   <a class="xref" href="replication-solutions-partitioning.html#figure_replication-multi-db" title="Figure 19.2 Replicating Databases to Separate Replicas">
    Figure 19.2, “Replicating Databases to Separate Replicas”
   </a>
   , you should
      configure each replica as follows, before executing
   <a class="link" href="start-replica.html" title="15.4.2.4 START REPLICA Statement">
    <code class="literal">
     START REPLICA
    </code>
   </a>
   :
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Replica 1 should use
      <code class="option">
       --replicate-wild-do-table=databaseA.%
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Replica 2 should use
      <code class="option">
       --replicate-wild-do-table=databaseB.%
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Replica 3 should use
      <code class="option">
       --replicate-wild-do-table=databaseC.%
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   Each replica in this configuration receives the entire binary log
      from the source, but executes only those events from the binary
      log that apply to the databases and tables included by the
   <a class="link" href="replication-options-replica.html#option_mysqld_replicate-wild-do-table">
    <code class="option">
     --replicate-wild-do-table
    </code>
   </a>
   option in
      effect on that replica.
  </p>
  <p>
   If you have data that must be synchronized to the replicas before
      replication starts, you have a number of choices:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Synchronize all the data to each replica, and delete the
          databases, tables, or both that you do not want to keep.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use
      <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 a separate dump
          file for each database and load the appropriate dump file on
          each replica.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use a raw data file dump and include only the specific files
          and databases that you need for each replica.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       This does not work with
       <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
        <code class="literal">
         InnoDB
        </code>
       </a>
       databases unless you use
       <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
        <code class="literal">
         innodb_file_per_table
        </code>
       </a>
       .
      </p>
     </div>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
