<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="replication-snapshot-method">
      </a>
      19.1.2.5 Choosing a Method for Data Snapshots
     </h4>
    </div>
   </div>
  </div>
  <p>
   If the source database contains existing data it is necessary to
        copy this data to each replica. There are different ways to dump
        the data from the source database. The following sections
        describe possible options.
  </p>
  <p>
   To select the appropriate method of dumping the database, choose
        between these options:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Use the
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      tool to create a dump
            of all the databases you want to replicate. This is the
            recommended method, especially when using
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      If your database is stored in binary portable files, you can
            copy the raw data files to a replica. This can be more
            efficient than using
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      and
            importing the file on each replica, because it skips the
            overhead of updating indexes as the
      <code class="literal">
       INSERT
      </code>
      statements are replayed. With
            storage engines such as
      <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
       <code class="literal">
        InnoDB
       </code>
      </a>
      this is not recommended.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use MySQL Server's clone plugin to transfer all the data
            from an existing replica to a clone. For instructions to use
            this method, see
      <a class="xref" href="clone-plugin-replication.html" title="7.6.7.7 Cloning for Replication">
       Section 7.6.7.7, “Cloning for Replication”
      </a>
      .
     </p>
    </li>
   </ul>
  </div>
  <div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Tip
   </div>
   <p>
    To deploy multiple instances of MySQL, you can use
    <a class="ulink" href="/doc/mysql-shell/8.4/en/mysql-innodb-cluster.html" target="_top">
     InnoDB Cluster
    </a>
    which enables you to easily administer a group of MySQL server instances in
    <a class="ulink" href="/doc/mysql-shell/8.4/en/" target="_top">
     MySQL Shell
    </a>
    . InnoDB Cluster wraps MySQL Group Replication in a programmatic environment that enables you easily deploy a cluster of MySQL instances to achieve high availability. In addition, InnoDB Cluster interfaces seamlessly with
    <a class="ulink" href="/doc/mysql-router/8.4/en/" target="_top">
     MySQL Router
    </a>
    , which enables your applications to connect to the cluster without writing your own failover process. For similar use cases that do not require high availability, however, you can use
    <a class="ulink" href="/doc/mysql-shell/8.4/en/mysql-innodb-replicaset.html" target="_top">
     InnoDB ReplicaSet
    </a>
    . Installation instructions for MySQL Shell can be found
    <a class="ulink" href="/doc/mysql-shell/8.4/en/mysql-shell-install.html" target="_top">
     here
    </a>
    .
   </p>
  </div>
  <div class="section">
   <div class="titlepage">
    <div>
     <div>
      <h5 class="title">
       <a name="replication-howto-mysqldump">
       </a>
       19.1.2.5.1 Creating a Data Snapshot Using mysqldump
      </h5>
     </div>
    </div>
   </div>
   <p>
    To create a snapshot of the data in an existing source
          database, use the
    <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
     <span class="command">
      <strong>
       mysqldump
      </strong>
     </span>
    </a>
    tool. Once the
          data dump has been completed, import this data into the
          replica before starting the replication process.
   </p>
   <p>
    The following example dumps all databases to a file named
    <code class="filename">
     dbdump.db
    </code>
    , and includes the
    <a class="link" href="mysqldump.html#option_mysqldump_source-data">
     <code class="option">
      --source-data
     </code>
    </a>
    option which
          automatically appends the
    <a class="link" href="change-replication-source-to.html" title="15.4.2.2 CHANGE REPLICATION SOURCE TO Statement">
     <code class="literal">
      CHANGE
          REPLICATION SOURCE TO
     </code>
    </a>
    statement required on the
          replica to start the replication process:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa62492222"><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-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysqldump</span> <span class="token property">--all-databases</span> <span class="token property">--source-data</span> &gt; dbdump<span class="token punctuation">.</span>db</code></pre>
   </div>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     If you do not use
     <a class="link" href="mysqldump.html#option_mysqldump_source-data">
      <code class="option">
       --source-data
      </code>
     </a>
     , then it is
            necessary to lock all tables in a separate session manually.
            See
     <a class="xref" href="replication-howto-masterstatus.html" title="19.1.2.4 Obtaining the Replication Source Binary Log Coordinates">
      Section 19.1.2.4, “Obtaining the Replication Source Binary Log Coordinates”
     </a>
     .
    </p>
   </div>
   <p>
    It is possible to exclude certain databases from the dump
          using the
    <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
     <span class="command">
      <strong>
       mysqldump
      </strong>
     </span>
    </a>
    tool. If you want to
          choose which databases to include in the dump, do not use
    <a class="link" href="mysqldump.html#option_mysqldump_all-databases">
     <code class="option">
      --all-databases
     </code>
    </a>
    . Choose one
          of these options:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Exclude all the tables in the database using
       <a class="link" href="mysqldump.html#option_mysqldump_ignore-table">
        <code class="option">
         --ignore-table
        </code>
       </a>
       option.
      </p>
     </li>
     <li class="listitem">
      <p>
       Name only those databases which you want dumped using the
       <a class="link" href="mysqldump.html#option_mysqldump_databases">
        <code class="option">
         --databases
        </code>
       </a>
       option.
      </p>
     </li>
    </ul>
   </div>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     By default, if GTIDs are in use on the source
            (
     <a class="link" href="replication-options-gtids.html#sysvar_gtid_mode">
      <code class="literal">
       gtid_mode=ON
      </code>
     </a>
     ),
     <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
      <span class="command">
       <strong>
        mysqldump
       </strong>
      </span>
     </a>
     includes the GTIDs from the
     <a class="link" href="replication-options-gtids.html#sysvar_gtid_executed">
      <code class="literal">
       gtid_executed
      </code>
     </a>
     set on the
            source in the dump output to add them to the
     <a class="link" href="replication-options-gtids.html#sysvar_gtid_purged">
      <code class="literal">
       gtid_purged
      </code>
     </a>
     set on the
            replica. If you are dumping only specific databases or
            tables, it is important to note that the value that is
            included by
     <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
      <span class="command">
       <strong>
        mysqldump
       </strong>
      </span>
     </a>
     includes the GTIDs
            of all transactions in the
     <a class="link" href="replication-options-gtids.html#sysvar_gtid_executed">
      <code class="literal">
       gtid_executed
      </code>
     </a>
     set on the
            source, even those that changed suppressed parts of the
            database, or other databases on the server that were not
            included in the partial dump. Check the description for
            mysqldump's
     <code class="option">
      --set-gtid-purged
     </code>
     option to
            find the outcome of the default behavior for the MySQL
            Server versions you are using, and how to change the
            behavior if this outcome is not suitable for your situation.
    </p>
   </div>
   <p>
    For more information, 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>
    .
   </p>
   <p>
    To import the data, either copy the dump file to the replica,
          or access the file from the source when connecting remotely to
          the replica.
   </p>
  </div>
  <div class="section">
   <div class="titlepage">
    <div>
     <div>
      <h5 class="title">
       <a name="replication-howto-rawdata">
       </a>
       19.1.2.5.2 Creating a Data Snapshot Using Raw Data Files
      </h5>
     </div>
    </div>
   </div>
   <p>
    This section describes how to create a data snapshot using the
          raw files which make up the database.

          

          Employing this method with a table using a storage engine that
          has complex caching or logging algorithms requires extra steps
          to produce a perfect
    <span class="quote">
     “
     <span class="quote">
      point in time
     </span>
     ”
    </span>
    snapshot:
          the initial copy command could leave out cache information and
          logging updates, even if you have acquired a global read lock.
          How the storage engine responds to this depends on its crash
          recovery abilities.
   </p>
   <p>
    If you use
    <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
     <code class="literal">
      InnoDB
     </code>
    </a>
    tables, you can
          use the
    <span class="command">
     <strong>
      mysqlbackup
     </strong>
    </span>
    command from the MySQL Enterprise Backup
          component to produce a consistent snapshot. This command
          records the log name and offset corresponding to the snapshot
          to be used on the replica. MySQL Enterprise Backup is a commercial product that
          is included as part of a MySQL Enterprise subscription. See
    <a class="xref" href="mysql-enterprise-backup.html" title="32.1 MySQL Enterprise Backup Overview">
     Section 32.1, “MySQL Enterprise Backup Overview”
    </a>
    for detailed
          information.
   </p>
   <p>
    This method also does not work reliably if the source and
          replica have different values for
    <a class="link" href="server-system-variables.html#sysvar_ft_stopword_file">
     <code class="literal">
      ft_stopword_file
     </code>
    </a>
    ,
    <a class="link" href="server-system-variables.html#sysvar_ft_min_word_len">
     <code class="literal">
      ft_min_word_len
     </code>
    </a>
    , or
    <a class="link" href="server-system-variables.html#sysvar_ft_max_word_len">
     <code class="literal">
      ft_max_word_len
     </code>
    </a>
    and you are
          copying tables having full-text indexes.
   </p>
   <p>
    Assuming the above exceptions do not apply to your database,
          use the
    <a class="link" href="glossary.html#glos_cold_backup" title="cold backup">
     cold backup
    </a>
    technique to obtain a reliable binary snapshot of
    <code class="literal">
     InnoDB
    </code>
    tables: do a
    <a class="link" href="glossary.html#glos_slow_shutdown" title="slow shutdown">
     slow shutdown
    </a>
    of the
          MySQL Server, then copy the data files manually.
   </p>
   <p>
    To create a raw data snapshot of
    <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
     <code class="literal">
      MyISAM
     </code>
    </a>
    tables when your MySQL
          data files exist on a single file system, you can use standard
          file copy tools such as
    <span class="command">
     <strong>
      cp
     </strong>
    </span>
    or
    <span class="command">
     <strong>
      copy
     </strong>
    </span>
    , a remote copy tool such as
    <span class="command">
     <strong>
      scp
     </strong>
    </span>
    or
    <span class="command">
     <strong>
      rsync
     </strong>
    </span>
    , an
          archiving tool such as
    <span class="command">
     <strong>
      zip
     </strong>
    </span>
    or
    <span class="command">
     <strong>
      tar
     </strong>
    </span>
    , or a file system snapshot tool such as
    <span class="command">
     <strong>
      dump
     </strong>
    </span>
    . If you are replicating only certain
          databases, copy only those files that relate to those tables.
          For
    <code class="literal">
     InnoDB
    </code>
    , all tables in all databases are
          stored in the
    <a class="link" href="glossary.html#glos_system_tablespace" title="system tablespace">
     system
          tablespace
    </a>
    files, unless you have the
    <a class="link" href="innodb-parameters.html#sysvar_innodb_file_per_table">
     <code class="option">
      innodb_file_per_table
     </code>
    </a>
    option
          enabled.
   </p>
   <p>
    The following files are not required for replication:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Files relating to the
       <code class="literal">
        mysql
       </code>
       database.
      </p>
     </li>
     <li class="listitem">
      <p>
       The replica's connection metadata repository file
       <code class="literal">
        master.info
       </code>
       , if used; the use of this
              file is now deprecated (see
       <a class="xref" href="replica-logs.html" title="19.2.4 Relay Log and Replication Metadata Repositories">
        Section 19.2.4, “Relay Log and Replication Metadata Repositories”
       </a>
       ).
      </p>
     </li>
     <li class="listitem">
      <p>
       The source's binary log files, with the exception of the
              binary log index file if you are going to use this to
              locate the source binary log coordinates for the replica.
      </p>
     </li>
     <li class="listitem">
      <p>
       Any relay log files.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Depending on whether you are using
    <code class="literal">
     InnoDB
    </code>
    tables or not, choose one of the following:
   </p>
   <p>
    If you are using
    <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
     <code class="literal">
      InnoDB
     </code>
    </a>
    tables,
          and also to get the most consistent results with a raw data
          snapshot, shut down the source server during the process, as
          follows:
   </p>
   <div class="orderedlist">
    <ol class="orderedlist" type="1">
     <li class="listitem">
      <p>
       Acquire a read lock and get the source's status. See
       <a class="xref" href="replication-howto-masterstatus.html" title="19.1.2.4 Obtaining the Replication Source Binary Log Coordinates">
        Section 19.1.2.4, “Obtaining the Replication Source Binary Log Coordinates”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       In a separate session, shut down the source server:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa63644063"><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-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysqladmin</span> shutdown</code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Make a copy of the MySQL data files. The following
              examples show common ways to do this. You need to choose
              only one of them:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa59437617"><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-terminal"><span class="token prompt">$&gt; </span><span class="token command">tar</span> cf <em class="replaceable">/tmp/db<span class="token punctuation">.</span>tar</em> <span class="token punctuation"></span><em class="replaceable"><span class="token punctuation">.</span>/data</em>
<span class="token prompt">$&gt; </span><span class="token command">zip</span> <span class="token property">-r</span> <em class="replaceable">/tmp/db<span class="token punctuation">.</span>zip</em> <span class="token punctuation"></span><em class="replaceable"><span class="token punctuation">.</span>/data</em>
<span class="token prompt">$&gt; </span><span class="token command">rsync</span> <span class="token property">--recursive</span> <span class="token punctuation"></span><em class="replaceable"><span class="token punctuation">.</span>/data</em> <em class="replaceable">/tmp/dbdata</em></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Restart the source server.
      </p>
     </li>
    </ol>
   </div>
   <p>
    If you are not using
    <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
     <code class="literal">
      InnoDB
     </code>
    </a>
    tables, you can get a snapshot of the system from a source
          without shutting down the server as described in the following
          steps:
   </p>
   <div class="orderedlist">
    <ol class="orderedlist" type="1">
     <li class="listitem">
      <p>
       Acquire a read lock and get the source's status. See
       <a class="xref" href="replication-howto-masterstatus.html" title="19.1.2.4 Obtaining the Replication Source Binary Log Coordinates">
        Section 19.1.2.4, “Obtaining the Replication Source Binary Log Coordinates”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Make a copy of the MySQL data files. The following
              examples show common ways to do this. You need to choose
              only one of them:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa96749995"><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-terminal"><span class="token prompt">$&gt; </span><span class="token command">tar</span> cf <em class="replaceable">/tmp/db<span class="token punctuation">.</span>tar</em> <span class="token punctuation"></span><em class="replaceable"><span class="token punctuation">.</span>/data</em>
<span class="token prompt">$&gt; </span><span class="token command">zip</span> <span class="token property">-r</span> <em class="replaceable">/tmp/db<span class="token punctuation">.</span>zip</em> <span class="token punctuation"></span><em class="replaceable"><span class="token punctuation">.</span>/data</em>
<span class="token prompt">$&gt; </span><span class="token command">rsync</span> <span class="token property">--recursive</span> <span class="token punctuation"></span><em class="replaceable"><span class="token punctuation">.</span>/data</em> <em class="replaceable">/tmp/dbdata</em></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       In the client where you acquired the read lock, release
              the lock:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa34817234"><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">UNLOCK</span> <span class="token keyword">TABLES</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
    </ol>
   </div>
   <p>
    Once you have created the archive or copy of the database,
          copy the files to each replica before starting the replication
          process.
   </p>
  </div>
 </div>
 <br/>
</div>
