<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="backup-policy">
      </a>
      9.3.1 Establishing a Backup Policy
     </h3>
    </div>
   </div>
  </div>
  <p>
   To be useful, backups must be scheduled regularly. A full backup
        (a snapshot of the data at a point in time) can be done in MySQL
        with several tools. For example,
   <a class="link" href="mysql-enterprise-backup.html" title="32.1 MySQL Enterprise Backup Overview">
    MySQL Enterprise Backup
   </a>
   can perform
        a
   <a class="link" href="glossary.html#glos_physical_backup" title="physical backup">
    physical backup
   </a>
   of
        an entire instance, with optimizations to minimize overhead and
        avoid disruption when backing up
   <code class="literal">
    InnoDB
   </code>
   data
        files;
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   provides online
   <a class="link" href="glossary.html#glos_logical_backup" title="logical backup">
    logical backup
   </a>
   . This
        discussion uses
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   .
  </p>
  <p>
   Assume that we make a full backup of all our
   <code class="literal">
    InnoDB
   </code>
   tables in all databases using the
        following command on Sunday at 1 p.m., when load is low:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa77511381"><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> <span class="token property">--single-transaction</span> &gt; backup_sunday_1_PM<span class="token punctuation">.</span>sql</code></pre>
  </div>
  <p>
   The resulting
   <code class="filename">
    .sql
   </code>
   file produced by
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   contains a set of SQL
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statements that can be
        used to reload the dumped tables at a later time.
  </p>
  <p>
   This backup operation acquires a global read lock on all tables
        at the beginning of the dump (using
   <a class="link" href="flush.html#flush-tables-with-read-lock">
    <code class="literal">
     FLUSH
        TABLES WITH READ LOCK
    </code>
   </a>
   ). As soon as this lock has been
        acquired, the binary log coordinates are read and the lock is
        released. If long updating statements are running when the
   <a class="link" href="flush.html" title="15.7.8.3 FLUSH Statement">
    <code class="literal">
     FLUSH
    </code>
   </a>
   statement is issued, the
        backup operation may stall until those statements finish. After
        that, the dump becomes lock-free and does not disturb reads and
        writes on the tables.
  </p>
  <p>
   It was assumed earlier that the tables to back up are
   <code class="literal">
    InnoDB
   </code>
   tables, so
   <a class="link" href="mysqldump.html#option_mysqldump_single-transaction">
    <code class="option">
     --single-transaction
    </code>
   </a>
   uses a
        consistent read and guarantees that data seen by
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   does not change. (Changes made by
        other clients to
   <code class="literal">
    InnoDB
   </code>
   tables are not seen
        by the
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   process.) If the backup
        operation includes nontransactional tables, consistency requires
        that they do not change during the backup. For example, for the
   <code class="literal">
    MyISAM
   </code>
   tables in the
   <code class="literal">
    mysql
   </code>
   database, there must be no administrative changes to MySQL
        accounts during the backup.
  </p>
  <p>
   Full backups are necessary, but it is not always convenient to
        create them. They produce large backup files and take time to
        generate. They are not optimal in the sense that each successive
        full backup includes all data, even that part that has not
        changed since the previous full backup. It is more efficient to
        make an initial full backup, and then to make incremental
        backups. The incremental backups are smaller and take less time
        to produce. The tradeoff is that, at recovery time, you cannot
        restore your data just by reloading the full backup. You must
        also process the incremental backups to recover the incremental
        changes.
  </p>
  <p>
   To make incremental backups, we need to save the incremental
        changes. In MySQL, these changes are represented in the binary
        log, so the MySQL server should always be started with the
   <a class="link" href="replication-options-binary-log.html#option_mysqld_log-bin">
    <code class="option">
     --log-bin
    </code>
   </a>
   option to enable that
        log. With binary logging enabled, the server writes each data
        change into a file while it updates data. Looking at the data
        directory of a MySQL server that has been running for some days,
        we find these MySQL binary log files:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa81602084"><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"><span class="token operator">-</span>rw<span class="token operator">-</span>rw<span class="token operator">--</span><span class="token operator">--</span> 1 guilhem  guilhem   1277324 Nov 10 23<span class="token operator">:</span>59 gbichot2<span class="token operator">-</span>bin<span class="token punctuation">.</span>000001
<span class="token operator">-</span>rw<span class="token operator">-</span>rw<span class="token operator">--</span><span class="token operator">--</span> 1 guilhem  guilhem         4 Nov 10 23<span class="token operator">:</span>59 gbichot2<span class="token operator">-</span>bin<span class="token punctuation">.</span>000002
<span class="token operator">-</span>rw<span class="token operator">-</span>rw<span class="token operator">--</span><span class="token operator">--</span> 1 guilhem  guilhem        79 Nov 11 11<span class="token operator">:</span>06 gbichot2<span class="token operator">-</span>bin<span class="token punctuation">.</span>000003
<span class="token operator">-</span>rw<span class="token operator">-</span>rw<span class="token operator">--</span><span class="token operator">--</span> 1 guilhem  guilhem       508 Nov 11 11<span class="token operator">:</span>08 gbichot2<span class="token operator">-</span>bin<span class="token punctuation">.</span>000004
<span class="token operator">-</span>rw<span class="token operator">-</span>rw<span class="token operator">--</span><span class="token operator">--</span> 1 guilhem  guilhem 220047446 Nov 12 16<span class="token operator">:</span>47 gbichot2<span class="token operator">-</span>bin<span class="token punctuation">.</span>000005
<span class="token operator">-</span>rw<span class="token operator">-</span>rw<span class="token operator">--</span><span class="token operator">--</span> 1 guilhem  guilhem    998412 Nov 14 10<span class="token operator">:</span>08 gbichot2<span class="token operator">-</span>bin<span class="token punctuation">.</span>000006
<span class="token operator">-</span>rw<span class="token operator">-</span>rw<span class="token operator">--</span><span class="token operator">--</span> 1 guilhem  guilhem       361 Nov 14 10<span class="token operator">:</span>07 gbichot2<span class="token operator">-</span>bin<span class="token punctuation">.</span>index</code></pre>
  </div>
  <p>
   Each time it restarts, the MySQL server creates a new binary log
        file using the next number in the sequence. While the server is
        running, you can also tell it to close the current binary log
        file and begin a new one manually by issuing a
   <a class="link" href="flush.html#flush-logs">
    <code class="literal">
     FLUSH LOGS
    </code>
   </a>
   SQL statement or with
        a
   <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
    <span class="command">
     <strong>
      mysqladmin flush-logs
     </strong>
    </span>
   </a>
   command.
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   also has an option to flush the
        logs. The
   <code class="literal">
    .index
   </code>
   file in the data directory
        contains the list of all MySQL binary logs in the directory.
  </p>
  <p>
   The MySQL binary logs are important for recovery because they
        form the set of incremental backups. If you make sure to flush
        the logs when you make your full backup, the binary log files
        created afterward contain all the data changes made since the
        backup. Let's modify the previous
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   command a bit so that it flushes the MySQL binary logs at the
        moment of the full backup, and so that the dump file contains
        the name of the new current binary log:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa89859330"><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">--single-transaction</span> <span class="token property">--flush-logs</span> <span class="token constant">--source-data</span><span class="token attr-value"><span class="token punctuation">=</span>2</span> \
         <span class="token property">--all-databases</span> &gt; backup_sunday_1_PM<span class="token punctuation">.</span>sql</code></pre>
  </div>
  <p>
   After executing this command, the data directory contains a new
        binary log file,
   <code class="filename">
    gbichot2-bin.000007
   </code>
   ,
        because the
   <a class="link" href="mysqldump.html#option_mysqldump_flush-logs">
    <code class="option">
     --flush-logs
    </code>
   </a>
   option causes the server to flush its logs. The
   <a class="link" href="mysqldump.html#option_mysqldump_source-data">
    <code class="option">
     --source-data
    </code>
   </a>
   option causes
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   to write binary log information to
        its output, so the resulting
   <code class="filename">
    .sql
   </code>
   dump file
        includes these lines:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa31677991"><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"><span class="token operator">--</span> Position to start replication or point<span class="token operator">-</span>in<span class="token operator">-</span>time recovery from
<span class="token operator">--</span> CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE<span class="token operator">=</span>'gbichot2<span class="token operator">-</span>bin<span class="token punctuation">.</span>000007'<span class="token punctuation">,</span>SOURCE_LOG_POS<span class="token operator">=</span>4<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Because the
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   command made a full
        backup, those lines mean two things:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The dump file contains all changes made before any changes
            written to the
      <code class="filename">
       gbichot2-bin.000007
      </code>
      binary log file or higher.
     </p>
    </li>
    <li class="listitem">
     <p>
      All data changes logged after the backup are not present in
            the dump file, but are present in the
      <code class="filename">
       gbichot2-bin.000007
      </code>
      binary log file or
            higher.
     </p>
    </li>
   </ul>
  </div>
  <p>
   On Monday at 1 p.m., we can create an incremental backup by
        flushing the logs to begin a new binary log file. For example,
        executing a
   <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
    <span class="command">
     <strong>
      mysqladmin flush-logs
     </strong>
    </span>
   </a>
   command
        creates
   <code class="filename">
    gbichot2-bin.000008
   </code>
   . All changes
        between the Sunday 1 p.m. full backup and Monday 1 p.m. are
        written in
   <code class="filename">
    gbichot2-bin.000007
   </code>
   . This
        incremental backup is important, so it is a good idea to copy it
        to a safe place. (For example, back it up on tape or DVD, or
        copy it to another machine.) On Tuesday at 1 p.m., execute
        another
   <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
    <span class="command">
     <strong>
      mysqladmin flush-logs
     </strong>
    </span>
   </a>
   command. All
        changes between Monday 1 p.m. and Tuesday 1 p.m. are written in
   <code class="filename">
    gbichot2-bin.000008
   </code>
   (which also should be
        copied somewhere safe).
  </p>
  <p>
   The MySQL binary logs take up disk space. To free up space,
        purge them from time to time. One way to do this is by deleting
        the binary logs that are no longer needed, such as when we make
        a full backup:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa78319973"><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">--single-transaction</span> <span class="token property">--flush-logs</span> <span class="token constant">--source-data</span><span class="token attr-value"><span class="token punctuation">=</span>2</span> \
         <span class="token property">--all-databases</span> <span class="token property">--delete-source-logs</span> &gt; backup_sunday_1_PM<span class="token punctuation">.</span>sql</code></pre>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    Deleting the MySQL binary logs with
    <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
     <span class="command">
      <strong>
       mysqldump
      </strong>
     </span>
    </a>
    <a class="link" href="mysqldump.html#option_mysqldump_delete-source-logs">
     <code class="option">
      --delete-source-logs
     </code>
    </a>
    can be
          dangerous if your server is a replication source server,
          because replicas might not yet fully have processed the
          contents of the binary log. The description for the
    <a class="link" href="purge-binary-logs.html" title="15.4.1.1 PURGE BINARY LOGS Statement">
     <code class="literal">
      PURGE BINARY LOGS
     </code>
    </a>
    statement
          explains what should be verified before deleting the MySQL
          binary logs. See
    <a class="xref" href="purge-binary-logs.html" title="15.4.1.1 PURGE BINARY LOGS Statement">
     Section 15.4.1.1, “PURGE BINARY LOGS Statement”
    </a>
    .
   </p>
  </div>
 </div>
 <br/>
</div>
