<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="backup-types">
      </a>
      9.1 Backup and Recovery Types
     </h2>
    </div>
   </div>
  </div>
  <p>
   This section describes the characteristics of different types of
      backups.
  </p>
  <h3>
   <a name="idm46045231384208">
   </a>
   Physical (Raw) Versus Logical Backups
  </h3>
  <p>
   Physical backups consist of raw copies of the directories and
      files that store database contents. This type of backup is
      suitable for large, important databases that need to be recovered
      quickly when problems occur.
  </p>
  <p>
   Logical backups save information represented as logical database
      structure (
   <a class="link" href="create-database.html" title="15.1.12 CREATE DATABASE Statement">
    <code class="literal">
     CREATE DATABASE
    </code>
   </a>
   ,
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statements) and
      content (
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   statements or
      delimited-text files). This type of backup is suitable for smaller
      amounts of data where you might edit the data values or table
      structure, or recreate the data on a different machine
      architecture.
  </p>
  <p>
   Physical backup methods have these characteristics:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The backup consists of exact copies of database directories
          and files. Typically this is a copy of all or part of the
          MySQL data directory.
     </p>
    </li>
    <li class="listitem">
     <p>
      Physical backup methods are faster than logical because they
          involve only file copying without conversion.
     </p>
    </li>
    <li class="listitem">
     <p>
      Output is more compact than for logical backup.
     </p>
    </li>
    <li class="listitem">
     <p>
      Because backup speed and compactness are important for busy,
          important databases, the MySQL Enterprise Backup product performs physical
          backups. For an overview of the MySQL Enterprise Backup product, see
      <a class="xref" href="mysql-enterprise-backup.html" title="32.1 MySQL Enterprise Backup Overview">
       Section 32.1, “MySQL Enterprise Backup Overview”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Backup and restore granularity ranges from the level of the
          entire data directory down to the level of individual files.
          This may or may not provide for table-level granularity,
          depending on storage engine. For example,
      <code class="literal">
       InnoDB
      </code>
      tables can each be in a separate
          file, or share file storage with other
      <code class="literal">
       InnoDB
      </code>
      tables; each
      <code class="literal">
       MyISAM
      </code>
      table corresponds uniquely to a set
          of files.
     </p>
    </li>
    <li class="listitem">
     <p>
      In addition to databases, the backup can include any related
          files such as log or configuration files.
     </p>
    </li>
    <li class="listitem">
     <p>
      Data from
      <code class="literal">
       MEMORY
      </code>
      tables is tricky to back
          up this way because their contents are not stored on disk.
          (The MySQL Enterprise Backup product has a feature where you can retrieve data
          from
      <code class="literal">
       MEMORY
      </code>
      tables during a backup.)
     </p>
    </li>
    <li class="listitem">
     <p>
      Backups are portable only to other machines that have
          identical or similar hardware characteristics.
     </p>
    </li>
    <li class="listitem">
     <p>
      Backups can be performed while the MySQL server is not
          running. If the server is running, it is necessary to perform
          appropriate locking so that the server does not change
          database contents during the backup. MySQL Enterprise Backup does this locking
          automatically for tables that require it.
     </p>
    </li>
    <li class="listitem">
     <p>
      Physical backup tools include the
      <span class="command">
       <strong>
        mysqlbackup
       </strong>
      </span>
      of MySQL Enterprise Backup for
      <code class="literal">
       InnoDB
      </code>
      or any other tables, or file
          system-level commands (such as
      <span class="command">
       <strong>
        cp
       </strong>
      </span>
      ,
      <span class="command">
       <strong>
        scp
       </strong>
      </span>
      ,
      <span class="command">
       <strong>
        tar
       </strong>
      </span>
      ,
      <span class="command">
       <strong>
        rsync
       </strong>
      </span>
      ) for
      <code class="literal">
       MyISAM
      </code>
      tables.
     </p>
    </li>
    <li class="listitem">
     <p>
      For restore:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         MySQL Enterprise Backup restores
         <code class="literal">
          InnoDB
         </code>
         and other tables
              that it backed up.
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="mysql-cluster-programs-ndb-restore.html" title="25.5.23 ndb_restore — Restore an NDB Cluster Backup">
          <span class="command">
           <strong>
            ndb_restore
           </strong>
          </span>
         </a>
         restores
         <a class="link" href="mysql-cluster.html" title="Chapter 25 MySQL NDB Cluster 8.4">
          <code class="literal">
           NDB
          </code>
         </a>
         tables.
        </p>
       </li>
       <li class="listitem">
        <p>
         Files copied at the file system level can be copied back
              to their original locations with file system commands.
        </p>
       </li>
      </ul>
     </div>
    </li>
   </ul>
  </div>
  <p>
   Logical backup methods have these characteristics:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The backup is done by querying the MySQL server to obtain
          database structure and content information.
     </p>
    </li>
    <li class="listitem">
     <p>
      Backup is slower than physical methods because the server must
          access database information and convert it to logical format.
          If the output is written on the client side, the server must
          also send it to the backup program.
     </p>
    </li>
    <li class="listitem">
     <p>
      Output is larger than for physical backup, particularly when
          saved in text format.
     </p>
    </li>
    <li class="listitem">
     <p>
      Backup and restore granularity is available at the server
          level (all databases), database level (all tables in a
          particular database), or table level. This is true regardless
          of storage engine.
     </p>
    </li>
    <li class="listitem">
     <p>
      The backup does not include log or configuration files, or
          other database-related files that are not part of databases.
     </p>
    </li>
    <li class="listitem">
     <p>
      Backups stored in logical format are machine independent and
          highly portable.
     </p>
    </li>
    <li class="listitem">
     <p>
      Logical backups are performed with the MySQL server running.
          The server is not taken offline.
     </p>
    </li>
    <li class="listitem">
     <p>
      Logical backup tools include the
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      program and the
      <a class="link" href="select.html" title="15.2.13 SELECT Statement">
       <code class="literal">
        SELECT
          ... INTO OUTFILE
       </code>
      </a>
      statement. These work for any
          storage engine, even
      <code class="literal">
       MEMORY
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      To restore logical backups, SQL-format dump files can be
          processed using the
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      client. To load
          delimited-text files, use the
      <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
       <code class="literal">
        LOAD
          DATA
       </code>
      </a>
      statement or the
      <a class="link" href="mysqlimport.html" title="6.5.5 mysqlimport — A Data Import Program">
       <span class="command">
        <strong>
         mysqlimport
        </strong>
       </span>
      </a>
      client.
     </p>
    </li>
   </ul>
  </div>
  <h3>
   <a name="idm46045231335104">
   </a>
   Online Versus Offline Backups
  </h3>
  <p>
   Online backups take place while the MySQL server is running so
      that the database information can be obtained from the server.
      Offline backups take place while the server is stopped. This
      distinction can also be described as
   <span class="quote">
    “
    <span class="quote">
     hot
    </span>
    ”
   </span>
   versus
   <span class="quote">
    “
    <span class="quote">
     cold
    </span>
    ”
   </span>
   backups; a
   <span class="quote">
    “
    <span class="quote">
     warm
    </span>
    ”
   </span>
   backup is one
      where the server remains running but locked against modifying data
      while you access database files externally.
  </p>
  <p>
   Online backup methods have these characteristics:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The backup is less intrusive to other clients, which can
          connect to the MySQL server during the backup and may be able
          to access data depending on what operations they need to
          perform.
     </p>
    </li>
    <li class="listitem">
     <p>
      Care must be taken to impose appropriate locking so that data
          modifications do not take place that would compromise backup
          integrity. The MySQL Enterprise Backup product does such locking automatically.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Offline backup methods have these characteristics:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Clients can be affected adversely because the server is
          unavailable during backup. For that reason, such backups are
          often taken from a replica that can be taken offline without
          harming availability.
     </p>
    </li>
    <li class="listitem">
     <p>
      The backup procedure is simpler because there is no
          possibility of interference from client activity.
     </p>
    </li>
   </ul>
  </div>
  <p>
   A similar distinction between online and offline applies for
      recovery operations, and similar characteristics apply. However,
      it is more likely for clients to be affected by online recovery
      than by online backup because recovery requires stronger locking.
      During backup, clients might be able to read data while it is
      being backed up. Recovery modifies data and does not just read it,
      so clients must be prevented from accessing data while it is being
      restored.
  </p>
  <h3>
   <a name="idm46045231325680">
   </a>
   Local Versus Remote Backups
  </h3>
  <p>
   A local backup is performed on the same host where the MySQL
      server runs, whereas a remote backup is done from a different
      host. For some types of backups, the backup can be initiated from
      a remote host even if the output is written locally on the server.
      host.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      can connect to local or remote
          servers. For SQL output (
      <code class="literal">
       CREATE
      </code>
      and
      <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
       <code class="literal">
        INSERT
       </code>
      </a>
      statements), local or
          remote dumps can be done and generate output on the client.
          For delimited-text output (with the
      <a class="link" href="mysqldump.html#option_mysqldump_tab">
       <code class="option">
        --tab
       </code>
      </a>
      option), data files
          are created on the server host.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="select-into.html" title="15.2.13.1 SELECT ... INTO Statement">
       <code class="literal">
        SELECT ... INTO
          OUTFILE
       </code>
      </a>
      can be initiated from a local or remote
          client host, but the output file is created on the server
          host.
     </p>
    </li>
    <li class="listitem">
     <p>
      Physical backup methods typically are initiated locally on the
          MySQL server host so that the server can be taken offline,
          although the destination for copied files might be remote.
     </p>
    </li>
   </ul>
  </div>
  <h3>
   <a name="idm46045231315600">
   </a>
   Snapshot Backups
  </h3>
  <p>
   Some file system implementations enable
   <span class="quote">
    “
    <span class="quote">
     snapshots
    </span>
    ”
   </span>
   to be taken. These provide logical copies of the file system at a
      given point in time, without requiring a physical copy of the
      entire file system. (For example, the implementation may use
      copy-on-write techniques so that only parts of the file system
      modified after the snapshot time need be copied.) MySQL itself
      does not provide the capability for taking file system snapshots.
      It is available through third-party solutions such as Veritas,
      LVM, or ZFS.
  </p>
  <h3>
   <a name="idm46045231313520">
   </a>
   Full Versus Incremental Backups
  </h3>
  <p>
   A full backup includes all data managed by a MySQL server at a
      given point in time. An incremental backup consists of the changes
      made to the data during a given time span (from one point in time
      to another). MySQL has different ways to perform full backups,
      such as those described earlier in this section. Incremental
      backups are made possible by enabling the server's binary log,
      which the server uses to record data changes.
  </p>
  <h3>
   <a name="idm46045231311920">
   </a>
   Full Versus Point-in-Time (Incremental) Recovery
  </h3>
  <p>
   A full recovery restores all data from a full backup. This
      restores the server instance to the state that it had when the
      backup was made. If that state is not sufficiently current, a full
      recovery can be followed by recovery of incremental backups made
      since the full backup, to bring the server to a more up-to-date
      state.
  </p>
  <p>
   Incremental recovery is recovery of changes made during a given
      time span. This is also called point-in-time recovery because it
      makes a server's state current up to a given time. Point-in-time
      recovery is based on the binary log and typically follows a full
      recovery from the backup files that restores the server to its
      state when the backup was made. Then the data changes written in
      the binary log files are applied as incremental recovery to redo
      data modifications and bring the server up to the desired point in
      time.
  </p>
  <h3>
   <a name="idm46045231309040">
   </a>
   Table Maintenance
  </h3>
  <p>
   Data integrity can be compromised if tables become corrupt. For
   <a class="link" href="innodb-storage-engine.html" title="Chapter 17 The InnoDB Storage Engine">
    <code class="literal">
     InnoDB
    </code>
   </a>
   tables, this is not a typical
      issue. For programs to check
   <a class="link" href="myisam-storage-engine.html" title="18.2 The MyISAM Storage Engine">
    <code class="literal">
     MyISAM
    </code>
   </a>
   tables and repair them if problems are found, see
   <a class="xref" href="myisam-table-maintenance.html" title="9.6 MyISAM Table Maintenance and Crash Recovery">
    Section 9.6, “MyISAM Table Maintenance and Crash Recovery”
   </a>
   .
  </p>
  <h3>
   <a name="idm46045231304560">
   </a>
   Backup Scheduling, Compression, and Encryption
  </h3>
  <p>
   Backup scheduling is valuable for automating backup procedures.
      Compression of backup output reduces space requirements, and
      encryption of the output provides better security against
      unauthorized access of backed-up data. MySQL itself does not
      provide these capabilities. The MySQL Enterprise Backup product can compress
   <code class="literal">
    InnoDB
   </code>
   backups, and compression or encryption
      of backup output can be achieved using file system utilities.
      Other third-party solutions may be available.
  </p>
 </div>
 <br/>
</div>
