<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="backup-methods">
      </a>
      9.2 Database Backup Methods
     </h2>
    </div>
   </div>
  </div>
  <p>
   This section summarizes some general methods for making backups.
  </p>
  <h3>
   <a name="idm46045231300480">
   </a>
   Making a Hot Backup with MySQL Enterprise Backup
  </h3>
  <p>
   Customers of MySQL Enterprise Edition can use the
   <a class="link" href="glossary.html#glos_mysql_enterprise_backup" title="MySQL Enterprise Backup">
    MySQL Enterprise Backup
   </a>
   product
      to do
   <a class="link" href="glossary.html#glos_physical" title="physical">
    physical
   </a>
   backups of
      entire instances or selected databases, tables, or both. This
      product includes features for
   <a class="link" href="glossary.html#glos_incremental_backup" title="incremental backup">
    incremental
   </a>
   and
   <a class="link" href="glossary.html#glos_compressed_backup" title="compressed backup">
    compressed
   </a>
   backups.
      Backing up the physical database files makes restore much faster
      than logical techniques such as the
   <code class="literal">
    mysqldump
   </code>
   command.
   <code class="literal">
    InnoDB
   </code>
   tables are copied using a
   <a class="link" href="glossary.html#glos_hot_backup" title="hot backup">
    hot backup
   </a>
   mechanism.
      (Ideally, the
   <code class="literal">
    InnoDB
   </code>
   tables should represent a
      substantial majority of the data.) Tables from other storage
      engines are copied using a
   <a class="link" href="glossary.html#glos_warm_backup" title="warm backup">
    warm
      backup
   </a>
   mechanism. 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>
  <h3>
   <a name="idm46045231290192">
   </a>
   Making Backups with mysqldump
  </h3>
  <p>
   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 can make backups. It can
      back up all kinds of tables. (See
   <a class="xref" href="using-mysqldump.html" title="9.4 Using mysqldump for Backups">
    Section 9.4, “Using mysqldump for Backups”
   </a>
   .)
  </p>
  <p>
   For
   <code class="literal">
    InnoDB
   </code>
   tables, it is possible to perform an
      online backup that takes no locks on tables using the
   <a class="link" href="mysqldump.html#option_mysqldump_single-transaction">
    <code class="option">
     --single-transaction
    </code>
   </a>
   option to
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   . See
   <a class="xref" href="backup-policy.html" title="9.3.1 Establishing a Backup Policy">
    Section 9.3.1, “Establishing a Backup Policy”
   </a>
   .
  </p>
  <h3>
   <a name="idm46045231282848">
   </a>
   Making Backups by Copying Table Files
  </h3>
  <p>
   MyISAM tables can be backed up by copying table files
      (
   <code class="filename">
    *.MYD
   </code>
   ,
   <code class="filename">
    *.MYI
   </code>
   files, and
      associated
   <code class="filename">
    *.sdi
   </code>
   files). To get a consistent
      backup, stop the server or lock and flush the relevant tables:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa46399997"><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">FLUSH</span> <span class="token keyword">TABLES</span> <em class="replaceable">tbl_list</em> <span class="token keyword">WITH</span> <span class="token keyword">READ</span> <span class="token keyword">LOCK</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   You need only a read lock; this enables other clients to continue
      to query the tables while you are making a copy of the files in
      the database directory. The flush is needed to ensure that the all
      active index pages are written to disk before you start the
      backup. See
   <a class="xref" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
    Section 15.3.6, “LOCK TABLES and UNLOCK TABLES Statements”
   </a>
   , and
   <a class="xref" href="flush.html" title="15.7.8.3 FLUSH Statement">
    Section 15.7.8.3, “FLUSH Statement”
   </a>
   .
  </p>
  <p>
   You can also create a binary backup simply by copying the table
      files, as long as the server is not updating anything. (But note
      that table file copying methods do not work if your database
      contains
   <code class="literal">
    InnoDB
   </code>
   tables. Also, even if the
      server is not actively updating data,
   <code class="literal">
    InnoDB
   </code>
   may still have modified data cached in memory and not flushed to
      disk.)
  </p>
  <p>
   For an example of this backup method, refer to the export and
      import example in
   <a class="xref" href="import-table.html" title="15.2.6 IMPORT TABLE Statement">
    Section 15.2.6, “IMPORT TABLE Statement”
   </a>
   .
  </p>
  <h3>
   <a name="idm46045231272480">
   </a>
   Making Delimited-Text File Backups
  </h3>
  <p>
   To create a text file containing a table's data, you can use
   <a class="link" href="select-into.html" title="15.2.13.1 SELECT ... INTO Statement">
    <code class="literal">
     SELECT * INTO OUTFILE
      '
     <em class="replaceable">
      <code>
       file_name
      </code>
     </em>
     ' FROM
     <em class="replaceable">
      <code>
       tbl_name
      </code>
     </em>
    </code>
   </a>
   . The file is created
      on the MySQL server host, not the client host. For this statement,
      the output file cannot already exist because permitting files to
      be overwritten constitutes a security risk. See
   <a class="xref" href="select.html" title="15.2.13 SELECT Statement">
    Section 15.2.13, “SELECT Statement”
   </a>
   . This method works for any kind of data
      file, but saves only table data, not the table structure.
  </p>
  <p>
   Another way to create text data files (along with files containing
   <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
    <code class="literal">
     CREATE TABLE
    </code>
   </a>
   statements for the
      backed up tables) is to use
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   with the
   <a class="link" href="mysqldump.html#option_mysqldump_tab">
    <code class="option">
     --tab
    </code>
   </a>
   option. See
   <a class="xref" href="mysqldump-delimited-text.html" title="9.4.3 Dumping Data in Delimited-Text Format with mysqldump">
    Section 9.4.3, “Dumping Data in Delimited-Text Format with mysqldump”
   </a>
   .
  </p>
  <p>
   To reload a delimited-text data file, use
   <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    <code class="literal">
     LOAD DATA
    </code>
   </a>
   or
   <a class="link" href="mysqlimport.html" title="6.5.5 mysqlimport — A Data Import Program">
    <span class="command">
     <strong>
      mysqlimport
     </strong>
    </span>
   </a>
   .
  </p>
  <h3>
   <a name="idm46045231260640">
   </a>
   Making Incremental Backups by Enabling the Binary Log
  </h3>
  <p>
   MySQL supports incremental backups using the binary log. The
      binary log files provide you with the information you need to
      replicate changes to the database that are made subsequent to the
      point at which you performed a backup. Therefore, to allow a
      server to be restored to a point-in-time, binary logging must be
      enabled on it, which is the default setting for MySQL
      8.4


      ; see
   <a class="xref" href="binary-log.html" title="7.4.4 The Binary Log">
    Section 7.4.4, “The Binary Log”
   </a>
   .
  </p>
  <p>
   At the moment you want to make an incremental backup (containing
      all changes that happened since the last full or incremental
      backup), you should rotate the binary log by using
   <a class="link" href="flush.html#flush-logs">
    <code class="literal">
     FLUSH LOGS
    </code>
   </a>
   . This done, you need to
      copy to the backup location all binary logs which range from the
      one of the moment of the last full or incremental backup to the
      last but one. These binary logs are the incremental backup; at
      restore time, you apply them as explained in
   <a class="xref" href="point-in-time-recovery.html" title="9.5 Point-in-Time (Incremental) Recovery">
    Section 9.5, “Point-in-Time (Incremental) Recovery”
   </a>
   . The next time you do a
      full backup, you should also rotate the binary log using
   <a class="link" href="flush.html#flush-logs">
    <code class="literal">
     FLUSH LOGS
    </code>
   </a>
   or
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
      --flush-logs
     </strong>
    </span>
   </a>
   . 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>
  <h3>
   <a name="idm46045231252368">
   </a>
   Making Backups Using Replicas
  </h3>
  <p>
   If you have performance problems with a server while making
      backups, one strategy that can help is to set up replication and
      perform backups on the replica rather than on the source. See
   <a class="xref" href="replication-solutions-backups.html" title="19.4.1 Using Replication for Backups">
    Section 19.4.1, “Using Replication for Backups”
   </a>
   .
  </p>
  <p>
   If you are backing up a replica, you should back up its connection
      metadata repository and applier metadata repository (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>
   ) when you back up the replica's
      databases, regardless of the backup method you choose. This
      information is always needed to resume replication after you
      restore the replica's data. If your replica is replicating
   <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    <code class="literal">
     LOAD DATA
    </code>
   </a>
   statements, you should
      also back up any
   <code class="filename">
    SQL_LOAD-*
   </code>
   files that exist
      in the directory that the replica uses for this purpose. The
      replica needs these files to resume replication of any interrupted
   <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
    <code class="literal">
     LOAD DATA
    </code>
   </a>
   operations. The location
      of this directory is the value of the system variable
   <a class="link" href="replication-options-replica.html#sysvar_replica_load_tmpdir">
    <code class="literal">
     replica_load_tmpdir
    </code>
   </a>
   . If the
      server was not started with that variable set, the directory
      location is the value of the
   <a class="link" href="server-system-variables.html#sysvar_tmpdir">
    <code class="literal">
     tmpdir
    </code>
   </a>
   system variable.
  </p>
  <h3>
   <a name="idm46045231242688">
   </a>
   Recovering Corrupt Tables
  </h3>
  <p>
   If you have to restore
   <code class="literal">
    MyISAM
   </code>
   tables that have
      become corrupt, try to recover them using
   <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
    <code class="literal">
     REPAIR TABLE
    </code>
   </a>
   or
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
      -r
     </strong>
    </span>
   </a>
   first. That should work in 99.9% of all cases. If
   <a class="link" href="myisamchk.html" title="6.6.4 myisamchk — MyISAM Table-Maintenance Utility">
    <span class="command">
     <strong>
      myisamchk
     </strong>
    </span>
   </a>
   fails, 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="idm46045231236256">
   </a>
   Making Backups Using a File System Snapshot
  </h3>
  <p>
   If you are using a Veritas file system, you can make a backup like
      this:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      From a client program, execute
      <a class="link" href="flush.html#flush-tables-with-read-lock">
       <code class="literal">
        FLUSH
          TABLES WITH READ LOCK
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      From another shell, execute
      <code class="literal">
       mount vxfs
          snapshot
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      From the first client, execute
      <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
       <code class="literal">
        UNLOCK
          TABLES
       </code>
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      Copy files from the snapshot.
     </p>
    </li>
    <li class="listitem">
     <p>
      Unmount the snapshot.
     </p>
    </li>
   </ol>
  </div>
  <p>
   Similar snapshot capabilities may be available in other file
      systems, such as LVM or ZFS.
  </p>
 </div>
 <br/>
</div>
