<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"><title>Chapter 6. Backup and Recovery</title><link rel="stylesheet" href="mysql-html.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL 5.1 Reference Manual"><link rel="up" href="index.html" title="MySQL 5.1 Reference Manual"><link rel="prev" href="server-administration.html" title="Chapter 5. MySQL Server Administration"><link rel="next" href="optimization.html" title="Chapter 7. Optimization"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">Chapter 6. Backup and Recovery</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="server-administration.html">Prev</a> </td><th width="60%" align="center"> </th><td width="20%" align="right"> <a accesskey="n" href="optimization.html">Next</a></td></tr></table><hr></div><div class="chapter" lang="en"><div class="titlepage"><div><div><h2 class="title"><a name="backup-and-recovery"></a>Chapter 6. Backup and Recovery</h2></div></div></div><div class="toc"><p><b>Table of Contents</b></p><dl><dt><span class="section"><a href="backup-and-recovery.html#backup-methods">6.1. Database Backup Methods</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#backup-strategy-example">6.2. Example Backup and Recovery Strategy</a></span></dt><dd><dl><dt><span class="section"><a href="backup-and-recovery.html#backup-policy">6.2.1. Backup Policy</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#recovery-from-backups">6.2.2. Using Backups for Recovery</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#backup-strategy-summary">6.2.3. Backup Strategy Summary</a></span></dt></dl></dd><dt><span class="section"><a href="backup-and-recovery.html#point-in-time-recovery">6.3. Point-in-Time Recovery</a></span></dt><dd><dl><dt><span class="section"><a href="backup-and-recovery.html#point-in-time-recovery-times">6.3.1. Point-in-Time Recovery from a Specific Time</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#point-in-time-recovery-positions">6.3.2. Point-in-Time Recovery from a Specific Log Position</a></span></dt></dl></dd><dt><span class="section"><a href="backup-and-recovery.html#table-maintenance">6.4. Table Maintenance and Crash Recovery</a></span></dt><dd><dl><dt><span class="section"><a href="backup-and-recovery.html#crash-recovery">6.4.1. Using <span><strong class="command">myisamchk</strong></span> for Crash Recovery</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#check">6.4.2. How to Check <code class="literal">MyISAM</code> Tables for Errors</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#repair">6.4.3. How to Repair Tables</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#table-optimization">6.4.4. Table Optimization</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#maintenance-schedule">6.4.5. Setting Up a Table Maintenance Schedule</a></span></dt></dl></dd></dl></div><a class="indexterm" name="id4257317"></a><a class="indexterm" name="id4257329"></a><p>
It is important to back up your databases in case problems occur so
that you can recover your data and be up and running again. MySQL
offers a variety of backup strategies from which you can choose to
select whatever methods best suit the requirements for your
installation.
</p><p>
Briefly summarized, backup concepts with which you should be
familiar include the following:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Logical versus physical backups
</p></li><li><p>
Online versus offline backups
</p></li><li><p>
Local versus remote backups
</p></li><li><p>
Snapshot backups
</p></li><li><p>
Full versus incremental backups
</p></li><li><p>
Point-in-time recovery
</p></li><li><p>
Backup scheduling, compression, and encryption
</p></li><li><p>
Table maintenance
</p></li></ul></div><div class="itemizedlist"><p>
More generally, the following discussion amplifies on the
properties of different backup methods.
</p><ul type="disc"><li><p>
<span class="bold"><strong>Logical versus physical (raw)
backups.</strong></span> Logical backups save information represented
as logical database structure (<a href="sql-syntax.html#create-database" title="12.1.10. CREATE DATABASE Syntax"><code class="literal">CREATE
DATABASE</code></a>, <a href="sql-syntax.html#create-table" title="12.1.17. CREATE TABLE Syntax"><code class="literal">CREATE TABLE</code></a>
statements) and content (<a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><code class="literal">INSERT</code></a>
statements or delimited-text files). Physical backups consist of
raw copies of the directories and files that store database
contents.
</p><p>
Logical backup methods have these characteristics:
</p><div class="itemizedlist"><ul type="circle"><li><p>
The backup is done by going through the MySQL server to
obtain database structure and content information.
</p></li><li><p>
Backup is slower than physical methods because the server
must access database information, convert it to logical
format, and send it to the backup program.
</p></li><li><p>
Output is larger than for physical backup, particularly when
saved in text format.
</p></li><li><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><p>
The backup does not include log or configuration files, or
other database-related files that are not part of databases.
</p></li><li><p>
Backups stored in logical format are machine independent and
highly portable.
</p></li><li><p>
Logical backups are performed with the MySQL server running
(the server is not taken offline).
</p></li><li><p>
Logical backup tools include the
<a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump</strong></span></a> program and the
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT ... INTO
OUTFILE</code></a> statement. These work for any storage
engine, even <code class="literal">MEMORY</code>.
</p><p>
For restore, SQL-format dump files can be processed using
the <a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql</strong></span></a> client. To load delimited-text
files, use the
<a href="sql-syntax.html#load-data" title="12.2.6. LOAD DATA INFILE
Syntax"><code class="literal">LOAD DATA
INFILE</code></a> statement or the
<a href="programs.html#mysqlimport" title="4.5.5. mysqlimport — A Data Import Program"><span><strong class="command">mysqlimport</strong></span></a> client.
</p></li></ul></div><p>
Physical backup methods have these characteristics:
</p><div class="itemizedlist"><ul type="circle"><li><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. Data from <code class="literal">MEMORY</code>
tables cannot be backed up this way because their contents
are not stored on disk.
</p></li><li><p>
Physical backup methods are faster than logical because they
involve only file copying without conversion.
</p></li><li><p>
Output is more compact than for logical backup.
</p></li><li><p>
Backup and restore granularity extends 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. (Each <code class="literal">MyISAM</code>
table corresponds uniquely to a set of files, but an
<code class="literal">InnoDB</code> table shares file storage with
other <code class="literal">InnoDB</code> tables.)
</p></li><li><p>
In addition to databases, the backup can include any related
files such as log or configuration files.
</p></li><li><p>
Backups are portable only to other machines that have
identical or similar hardware characteristics.
</p></li><li><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.
</p></li><li><p>
Physical backup tools include file system-level commands
(such as <span><strong class="command">cp</strong></span>, <span><strong class="command">scp</strong></span>,
<span><strong class="command">tar</strong></span>, <span><strong class="command">rsync</strong></span>),
<a href="programs.html#mysqlhotcopy" title="4.6.9. mysqlhotcopy — A Database Backup Program"><span><strong class="command">mysqlhotcopy</strong></span></a> for
<code class="literal">MyISAM</code> tables,
<span><strong class="command">ibbackup</strong></span> for <code class="literal">InnoDB</code>
tables, or <code class="literal">START BACKUP</code> for
<code class="literal">NDB</code> tables.
</p><p>
For restore, files copied at the file system level or with
<a href="programs.html#mysqlhotcopy" title="4.6.9. mysqlhotcopy — A Database Backup Program"><span><strong class="command">mysqlhotcopy</strong></span></a> can be copied back to their
original locations with file system commands;
<span><strong class="command">ibback</strong></span> restores <code class="literal">InnoDB</code>
tables, and <span><strong class="command">ndb_restore</strong></span> restores
<code class="literal">NDB</code> tables.
</p></li></ul></div></li><li><p>
<span class="bold"><strong>Online versus offline backups.</strong></span>
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">hot</span>” versus
“<span class="quote">cold</span>” backups; a “<span class="quote">warm</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 type="circle"><li><p>
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><p>
Care must be taken to impose appropriate locking so that
data modifications do not take place that compromise backup
integrity.
</p></li></ul></div><p>
Offline backup methods have these characteristics:
</p><div class="itemizedlist"><ul type="circle"><li><p>
Affects clients adversely because the server is unavailable
during backup.
</p></li><li><p>
Simpler backup procedure because there is no possibility of
interference from client activity.
</p></li></ul></div></li><li><p>
<span class="bold"><strong>Local versus remote backups.</strong></span> A
local backup is performed on the same host where the MySQL
server runs, whereas a remote backup is initiated from a
different host.
</p><div class="itemizedlist"><ul type="circle"><li><p>
<a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump</strong></span></a> can connect to local or remote
servers. For SQL output (<code class="literal">CREATE</code> and
<a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><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 href="programs.html#option_mysqldump_tab"><code class="option">--tab</code></a> option), data files
are created on the server host.
</p></li><li><p>
<a href="programs.html#mysqlhotcopy" title="4.6.9. mysqlhotcopy — A Database Backup Program"><span><strong class="command">mysqlhotcopy</strong></span></a> performs only local backups:
It connects to the server to lock it against data
modifications and then copies local table files.
</p></li><li><p>
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax"><code class="literal">SELECT ... INTO
OUTFILE</code></a> can be initiated from a remote client
host, but the output file is created on the server host.
</p></li><li><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 file copies might be
remote.
</p></li></ul></div></li><li><p>
<span class="bold"><strong>Snapshot backups.</strong></span> Some file
system implementations enable “<span class="quote">snapshots</span>” to be
taken. These provide logical copies of the file system at a
given point in time, without having to physically copy 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 or LVM.
</p></li><li><p>
<span class="bold"><strong>Full versus incremental
backups.</strong></span> 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 since the full backup.
MySQL has different ways to perform full backups, such as those
described in previous items. Incremental backups are made
possible by enabling the server's binary log, which the server
uses to record data changes.
</p></li><li><p>
<span class="bold"><strong>Point-in-time recovery.</strong></span> One use
for the binary log is to achieve point-in-time recovery. This is
done by recovering first from the backup files to restore the
server to its state when the backup was made, and then by
re-executing changes in subsequently written binary log files to
redo data modifications up to the desired point in time.
</p></li><li><p>
<span class="bold"><strong>Backup scheduling, compression, and
encryption.</strong></span> 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. <span><strong class="command">ibbackup</strong></span> 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></li><li><p>
<span class="bold"><strong>Table maintenance.</strong></span> Data
integrity can be compromised if tables become corrupt. MySQL
provides programs for checking tables and repairing them should
problems be found. These programs apply primarily to
<code class="literal">MyISAM</code> tables. See
<a href="backup-and-recovery.html#table-maintenance" title="6.4. Table Maintenance and Crash Recovery">Section 6.4, “Table Maintenance and Crash Recovery”</a>.
</p></li></ul></div><p>
<span class="bold"><strong>Additional resources</strong></span>
</p><p>
Resources related to backup or to maintaining data availability
include the following:
</p><div class="itemizedlist"><ul type="disc"><li><p>
A forum dedicated to backup issues is available at
<a href="http://forums.mysql.com/list.php?93" target="_top">http://forums.mysql.com/list.php?93</a>.
</p></li><li><p>
The syntax of the SQL statements described here is given in
<a href="sql-syntax.html" title="Chapter 12. SQL Statement Syntax">Chapter 12, <i>SQL Statement Syntax</i></a>.
</p></li><li><p>
Details for <a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump</strong></span></a>,
<a href="programs.html#mysqlhotcopy" title="4.6.9. mysqlhotcopy — A Database Backup Program"><span><strong class="command">mysqlhotcopy</strong></span></a>, and other MySQL backup programs
can be found in <a href="programs.html" title="Chapter 4. MySQL Programs">Chapter 4, <i>MySQL Programs</i></a>.
</p></li><li><p>
For additional information about <code class="literal">InnoDB</code>
backup procedures, see <a href="storage-engines.html#innodb-backup" title="13.6.6. Backing Up and Recovering an InnoDB Database">Section 13.6.6, “Backing Up and Recovering an <code class="literal">InnoDB</code> Database”</a>.
</p></li><li><p>
Replication enables you to maintain identical data on multiple
servers. This has several benefits, such as allowing client load
to be distributed over servers, availability of data even if a
given server is taken offline or fails, and the ability to make
backups using a slave server without affecting the master. See
<a href="replication.html" title="Chapter 16. Replication">Chapter 16, <i>Replication</i></a>.
</p></li><li><p>
MySQL Cluster provides a high-availability, high-redundancy
version of MySQL adapted for the distributed computing
environment. See <a href="mysql-cluster.html" title="Chapter 17. MySQL Cluster NDB 6.X/7.X">Chapter 17, <i>MySQL Cluster NDB 6.X/7.X</i></a>. For
information specifically about MySQL Cluster backup, see
<a href="mysql-cluster.html#mysql-cluster-backup" title="17.5.3. Online Backup of MySQL Cluster">Section 17.5.3, “Online Backup of MySQL Cluster”</a>.
</p></li><li><p>
Distributed Replicated Block Device (DRBD) is another
high-availability solution. It works by replicating a block
device from a primary server to a secondary server at the block
level. See <a href="ha-overview.html" title="Chapter 14. High Availability and Scalability">Chapter 14, <i>High Availability and Scalability</i></a>
</p></li></ul></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="backup-methods"></a>6.1. Database Backup Methods</h2></div></div></div><p>
This section summarizes some general methods for making backups.
</p><p>
<span class="bold"><strong>Making Backups by Copying Table
Files</strong></span>
</p><p>
For storage engines that represent each table using its own files,
tables can be backed up by copying those files. For example,
<code class="literal">MyISAM</code> tables are stored as files, so it is
easy to do a backup by copying files (<code class="filename">*.frm</code>,
<code class="filename">*.MYD</code>, and <code class="filename">*.MYI</code> files).
To get a consistent backup, stop the server or do a
<a href="sql-syntax.html#lock-tables" title="12.4.5. LOCK TABLES and
UNLOCK
TABLES Syntax"><code class="literal">LOCK TABLES</code></a> on the relevant tables
followed by <a href="sql-syntax.html#flush" title="12.5.6.3. FLUSH Syntax"><code class="literal">FLUSH
TABLES</code></a> for the tables. See
<a href="sql-syntax.html#lock-tables" title="12.4.5. LOCK TABLES and
UNLOCK
TABLES Syntax">Section 12.4.5, “<code class="literal">LOCK TABLES</code> and
<code class="literal">UNLOCK
TABLES</code> Syntax”</a>, and <a href="sql-syntax.html#flush" title="12.5.6.3. FLUSH Syntax">Section 12.5.6.3, “<code class="literal">FLUSH</code> Syntax”</a>. You
need only a read lock; this allows other clients to continue to
query the tables while you are making a copy of the files in the
database directory. The
<a href="sql-syntax.html#flush" title="12.5.6.3. FLUSH Syntax"><code class="literal">FLUSH TABLES</code></a>
statement is needed to ensure that the all active index pages are
written to disk before you start the backup.
</p><p>
<span class="bold"><strong>Making Delimited-Text File
Backups</strong></span>
</p><p>
To create a text file containing a table's data, you can use
<code class="literal">SELECT * INTO OUTFILE
'<em class="replaceable"><code>file_name</code></em>' FROM
<em class="replaceable"><code>tbl_name</code></em></code>. The file is created
on the MySQL server host, not the client host. For this statement,
the output file cannot already exist because allowing files to be
overwritten would constitute a security risk. See
<a href="sql-syntax.html#select" title="12.2.8. SELECT Syntax">Section 12.2.8, “<code class="literal">SELECT</code> Syntax”</a>. This method works for any kind of data
file, but saves only table data, not the table structure.
</p><p>
To reload the output file, use
<a href="sql-syntax.html#load-data" title="12.2.6. LOAD DATA INFILE
Syntax"><code class="literal">LOAD DATA
INFILE</code></a> or <a href="programs.html#mysqlimport" title="4.5.5. mysqlimport — A Data Import Program"><span><strong class="command">mysqlimport</strong></span></a>.
</p><p>
<span class="bold"><strong>Making Backups with
<a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump</strong></span></a> or
<a href="programs.html#mysqlhotcopy" title="4.6.9. mysqlhotcopy — A Database Backup Program"><span><strong class="command">mysqlhotcopy</strong></span></a></strong></span>
</p><p>
The <a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump</strong></span></a> program and the
<a href="programs.html#mysqlhotcopy" title="4.6.9. mysqlhotcopy — A Database Backup Program"><span><strong class="command">mysqlhotcopy</strong></span></a> script can make backups.
<a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump</strong></span></a> is more general because it can back
up all kinds of tables. <a href="programs.html#mysqlhotcopy" title="4.6.9. mysqlhotcopy — A Database Backup Program"><span><strong class="command">mysqlhotcopy</strong></span></a> works only
with some storage engines. (See <a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program">Section 4.5.4, “<span><strong class="command">mysqldump</strong></span> — A Database Backup Program”</a>, and
<a href="programs.html#mysqlhotcopy" title="4.6.9. mysqlhotcopy — A Database Backup Program">Section 4.6.9, “<span><strong class="command">mysqlhotcopy</strong></span> — A Database Backup Program”</a>.)
</p><p>
Create a full backup of your database:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqldump --tab=<em class="replaceable"><code>/path/to/some/dir</code></em> --opt <em class="replaceable"><code>db_name</code></em></code></strong>
</pre><p>
Or:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqlhotcopy <em class="replaceable"><code>db_name</code></em> <em class="replaceable"><code>/path/to/some/dir</code></em></code></strong>
</pre><p>
You can also create a binary backup simply by copying all table
files, as long as the server isn't updating anything. The
<a href="programs.html#mysqlhotcopy" title="4.6.9. mysqlhotcopy — A Database Backup Program"><span><strong class="command">mysqlhotcopy</strong></span></a> script uses this method. (But note
that these methods do not work if your database contains
<code class="literal">InnoDB</code> tables. <code class="literal">InnoDB</code> does
not necessarily store table contents in database directories, and
<a href="programs.html#mysqlhotcopy" title="4.6.9. mysqlhotcopy — A Database Backup Program"><span><strong class="command">mysqlhotcopy</strong></span></a> works only for
<code class="literal">MyISAM</code> tables.)
</p><p>
For <code class="literal">InnoDB</code> tables, it is possible to perform an
online backup that takes no locks on tables; see
<a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program">Section 4.5.4, “<span><strong class="command">mysqldump</strong></span> — A Database Backup Program”</a>.
</p><p>
<span class="bold"><strong>Using the Binary Log to Enable Incremental
Backups</strong></span>
</p><p>
MySQL supports incremental backups: You must start the server with
the <a href="replication.html#option_mysqld_log-bin"><code class="option">--log-bin</code></a> option to enable
binary logging; see <a href="server-administration.html#binary-log" title="5.2.4. The Binary Log">Section 5.2.4, “The Binary Log”</a>. 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. 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 href="sql-syntax.html#flush" title="12.5.6.3. FLUSH Syntax"><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 href="backup-and-recovery.html#point-in-time-recovery" title="6.3. Point-in-Time Recovery">Section 6.3, “Point-in-Time Recovery”</a>. The next
time you do a full backup, you should also rotate the binary log
using <a href="sql-syntax.html#flush" title="12.5.6.3. FLUSH Syntax"><code class="literal">FLUSH LOGS</code></a>,
<a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump --flush-logs</strong></span></a>, or
<a href="programs.html#mysqlhotcopy" title="4.6.9. mysqlhotcopy — A Database Backup Program"><span><strong class="command">mysqlhotcopy --flushlog</strong></span></a>. See
<a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program">Section 4.5.4, “<span><strong class="command">mysqldump</strong></span> — A Database Backup Program”</a>, and <a href="programs.html#mysqlhotcopy" title="4.6.9. mysqlhotcopy — A Database Backup Program">Section 4.6.9, “<span><strong class="command">mysqlhotcopy</strong></span> — A Database Backup Program”</a>.
</p><p>
<span class="bold"><strong>Making Backups Using Replication
Slaves</strong></span>
</p><p>
If your MySQL server is a slave replication server, you should
back up the <code class="filename">master.info</code> and
<code class="filename">relay-log.info</code> files when you back up your
slave's data, regardless of the backup method you choose. These
files are always needed to resume replication after you restore
the slave's data. If your slave is replicating
<a href="sql-syntax.html#load-data" title="12.2.6. LOAD DATA INFILE
Syntax"><code class="literal">LOAD DATA
INFILE</code></a> commands, you should also back up any
<code class="filename">SQL_LOAD-*</code> files that may exist in the
directory specified by the
<a href="replication.html#option_mysqld_slave-load-tmpdir"><code class="option">--slave-load-tmpdir</code></a> option. (This
location defaults to the value of the <code class="literal">tmpdir</code>
system variable if not specified.) The slave needs these files to
resume replication of any interrupted
<a href="sql-syntax.html#load-data" title="12.2.6. LOAD DATA INFILE
Syntax"><code class="literal">LOAD DATA
INFILE</code></a> operations.
</p><p class="mnmas"><b>MySQL Enterprise</b>
The MySQL Enterprise Monitor provides numerous advisors that
issue immediate warnings should replication issues arise. For
more information, see
<a href="http://www.mysql.com/products/enterprise/advisors.html" target="_top">http://www.mysql.com/products/enterprise/advisors.html</a>.
</p><p>
If you have performance problems with your master server while
making backups, one strategy that can help is to set up
replication and perform backups on the slave rather than on the
master. See <a href="replication.html" title="Chapter 16. Replication">Chapter 16, <i>Replication</i></a>.
</p><p>
<span class="bold"><strong>Recovering Corrupt Tables</strong></span>
</p><p>
If you have to restore <code class="literal">MyISAM</code> tables that have
become corrupt, try to recover them using
<a href="sql-syntax.html#repair-table" title="12.5.2.6. REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a> or <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk
-r</strong></span></a> first. That should work in 99.9% of all cases. If
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> fails, try the following procedure.
It is assumed that you have enabled binary logging by starting
MySQL with the <a href="replication.html#option_mysqld_log-bin"><code class="option">--log-bin</code></a> option.
</p><div class="orderedlist"><ol type="1"><li><p>
Restore the table from a <a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump</strong></span></a> backup
or binary backup.
</p></li><li><p>
Execute the following command to re-run the updates in the
binary logs:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqlbinlog binlog.[0-9]* | mysql</code></strong>
</pre><p>
In some cases, you may want to re-run only certain binary
logs, from certain positions (usually you want to re-run all
binary logs from the date of the restored backup, excepting
possibly some incorrect statements). See
<a href="backup-and-recovery.html#point-in-time-recovery" title="6.3. Point-in-Time Recovery">Section 6.3, “Point-in-Time Recovery”</a>.
</p></li></ol></div><p>
<span class="bold"><strong>Making Backups Using a File System
Snapshot</strong></span>
</p><p>
If you are using a Veritas file system, you can make a backup like
this:
</p><div class="orderedlist"><ol type="1"><li><p>
From a client program, execute
<a href="sql-syntax.html#flush" title="12.5.6.3. FLUSH Syntax"><code class="literal">FLUSH TABLES WITH READ
LOCK</code></a>.
</p></li><li><p>
From another shell, execute <code class="literal">mount vxfs
snapshot</code>.
</p></li><li><p>
From the first client, execute
<a href="sql-syntax.html#lock-tables" title="12.4.5. LOCK TABLES and
UNLOCK
TABLES Syntax"><code class="literal">UNLOCK
TABLES</code></a>.
</p></li><li><p>
Copy files from the snapshot.
</p></li><li><p>
Unmount the snapshot.
</p></li></ol></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="backup-strategy-example"></a>6.2. Example Backup and Recovery Strategy</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="backup-and-recovery.html#backup-policy">6.2.1. Backup Policy</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#recovery-from-backups">6.2.2. Using Backups for Recovery</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#backup-strategy-summary">6.2.3. Backup Strategy Summary</a></span></dt></dl></div><p>
This section discusses a procedure for performing backups that
allows you to recover data after several types of crashes:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Operating system crash
</p></li><li><p>
Power failure
</p></li><li><p>
File system crash
</p></li><li><p>
Hardware problem (hard drive, motherboard, and so forth)
</p></li></ul></div><p>
The example commands do not include options such as
<a href="programs.html#option_general_user"><code class="option">--user</code></a> and
<a href="programs.html#option_general_password"><code class="option">--password</code></a> for the
<a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump</strong></span></a> and <a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql</strong></span></a> client
programs. You should include such options as necessary to enable
client programs to connect to the MySQL server.
</p><p>
Assume that data is stored in the <code class="literal">InnoDB</code>
storage engine, which has support for transactions and automatic
crash recovery. Assume also that the MySQL server is under load at
the time of the crash. If it were not, no recovery would ever be
needed.
</p><p>
For cases of operating system crashes or power failures, we can
assume that MySQL's disk data is available after a restart. The
<code class="literal">InnoDB</code> data files might not contain consistent
data due to the crash, but <code class="literal">InnoDB</code> reads its
logs and finds in them the list of pending committed and
noncommitted transactions that have not been flushed to the data
files. <code class="literal">InnoDB</code> automatically rolls back those
transactions that were not committed, and flushes to its data
files those that were committed. Information about this recovery
process is conveyed to the user through the MySQL error log. The
following is an example log excerpt:
</p><pre class="programlisting">InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections
</pre><p>
For the cases of file system crashes or hardware problems, we can
assume that the MySQL disk data is <span class="emphasis"><em>not</em></span>
available after a restart. This means that MySQL fails to start
successfully because some blocks of disk data are no longer
readable. In this case, it is necessary to reformat the disk,
install a new one, or otherwise correct the underlying problem.
Then it is necessary to recover our MySQL data from backups, which
means that backups must already have been made. To make sure that
is the case, design and implement a backup policy.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="backup-policy"></a>6.2.1. 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, <code class="literal">InnoDB Hot
Backup</code> provides online nonblocking physical backup of
the <code class="literal">InnoDB</code> data files, and
<a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump</strong></span></a> provides online logical backup.
This discussion uses <a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump</strong></span></a>.
</p><p class="mnmas"><b>MySQL Enterprise</b>
For expert advice on backups and replication, subscribe to the
MySQL Enterprise Monitor. For more information, see
<a href="http://www.mysql.com/products/enterprise/advisors.html" target="_top">http://www.mysql.com/products/enterprise/advisors.html</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><pre class="programlisting">shell> <strong class="userinput"><code>mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql</code></strong>
</pre><p>
The resulting <code class="filename">.sql</code> file produced by
<a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump</strong></span></a> contains a set of SQL
<a href="sql-syntax.html#insert" title="12.2.5. INSERT Syntax"><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 href="sql-syntax.html#flush" title="12.5.6.3. FLUSH Syntax"><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 href="sql-syntax.html#flush" title="12.5.6.3. FLUSH Syntax"><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 href="programs.html#option_mysqldump_single-transaction"><code class="option">--single-transaction</code></a> uses a
consistent read and guarantees that data seen by
<a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">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 href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">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 href="replication.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 was started with the
<a href="replication.html#option_mysqld_log-bin"><code class="option">--log-bin</code></a> option and that has
been running for some days, we find these MySQL binary log
files:
</p><pre class="programlisting">-rw-rw---- 1 guilhem guilhem 1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem guilhem 4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem guilhem 79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem guilhem 508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem guilhem 998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem guilhem 361 Nov 14 10:07 gbichot2-bin.index
</pre><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 href="sql-syntax.html#flush" title="12.5.6.3. FLUSH Syntax"><code class="literal">FLUSH LOGS</code></a> SQL
statement or with a <a href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">mysqladmin flush-logs</strong></span></a>
command. <a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">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 href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">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><pre class="programlisting">shell> <strong class="userinput"><code>mysqldump --single-transaction --flush-logs --master-data=2 \</code></strong>
<strong class="userinput"><code>--all-databases > backup_sunday_1_PM.sql</code></strong>
</pre><p>
After executing this command, the data directory contains a new
binary log file, <code class="filename">gbichot2-bin.000007</code>,
because the <a href="programs.html#option_mysqldump_flush-logs"><code class="option">--flush-logs </code></a>
option causes the server to flush its logs. The
<a href="programs.html#option_mysqldump_master-data"><code class="option">--master-data</code></a> option causes
<a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">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><pre class="programlisting">-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;
</pre><p>
Because the <a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump</strong></span></a> command made a full
backup, those lines mean two things:
</p><div class="itemizedlist"><ul type="disc"><li><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 newer.
</p></li><li><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
newer.
</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 href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">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. will be
in the <code class="filename">gbichot2-bin.000007</code> file. 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 href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">mysqladmin flush-logs</strong></span></a> command. All
changes between Monday 1 p.m. and Tuesday 1 p.m. will be in the
<code class="filename">gbichot2-bin.000008</code> file (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><pre class="programlisting">shell> <strong class="userinput"><code>mysqldump --single-transaction --flush-logs --master-data=2 \</code></strong>
<strong class="userinput"><code>--all-databases --delete-master-logs > backup_sunday_1_PM.sql</code></strong>
</pre><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
Deleting the MySQL binary logs with <a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump
--delete-master-logs</strong></span></a> can be dangerous if your server
is a replication master server, because slave servers might
not yet fully have processed the contents of the binary log.
The description for the <a href="sql-syntax.html#purge-binary-logs" title="12.6.1.1. PURGE BINARY LOGS Syntax"><code class="literal">PURGE BINARY
LOGS</code></a> statement explains what should be verified
before deleting the MySQL binary logs. See
<a href="sql-syntax.html#purge-binary-logs" title="12.6.1.1. PURGE BINARY LOGS Syntax">Section 12.6.1.1, “<code class="literal">PURGE BINARY LOGS</code> Syntax”</a>.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="recovery-from-backups"></a>6.2.2. Using Backups for Recovery</h3></div></div></div><p>
Now, suppose that we have a catastrophic crash on Wednesday at 8
a.m. that requires recovery from backups. To recover, first we
restore the last full backup we have (the one from Sunday 1
p.m.). The full backup file is just a set of SQL statements, so
restoring it is very easy:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysql < backup_sunday_1_PM.sql</code></strong>
</pre><p>
At this point, the data is restored to its state as of Sunday 1
p.m.. To restore the changes made since then, we must use the
incremental backups; that is, the
<code class="filename">gbichot2-bin.000007</code> and
<code class="filename">gbichot2-bin.000008</code> binary log files. Fetch
the files if necessary from where they were backed up, and then
process their contents like this:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql</code></strong>
</pre><p>
We now have recovered the data to its state as of Tuesday 1
p.m., but still are missing the changes from that date to the
date of the crash. To not lose them, we would have needed to
have the MySQL server store its MySQL binary logs into a safe
location (RAID disks, SAN, ...) different from the place where
it stores its data files, so that these logs were not on the
destroyed disk. (That is, we can start the server with a
<a href="replication.html#option_mysqld_log-bin"><code class="option">--log-bin</code></a> option that specifies a
location on a different physical device from the one on which
the data directory resides. That way, the logs are safe even if
the device containing the directory is lost.) If we had done
this, we would have the <code class="filename">gbichot2-bin.000009</code>
file (and any subsequent files) at hand, and we could apply them
using <a href="programs.html#mysqlbinlog" title="4.6.7. mysqlbinlog — Utility for Processing Binary Log Files"><span><strong class="command">mysqlbinlog</strong></span></a> and
<a href="programs.html#mysql" title="4.5.1. mysql — The MySQL Command-Line Tool"><span><strong class="command">mysql</strong></span></a> to restore the most recent data changes
with no loss up to the moment of the crash:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqlbinlog gbichot2-bin.000009 ... | mysql</code></strong>
</pre><p>
For more information about using <a href="programs.html#mysqlbinlog" title="4.6.7. mysqlbinlog — Utility for Processing Binary Log Files"><span><strong class="command">mysqlbinlog</strong></span></a>
to process binary log files, see
<a href="backup-and-recovery.html#point-in-time-recovery" title="6.3. Point-in-Time Recovery">Section 6.3, “Point-in-Time Recovery”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="backup-strategy-summary"></a>6.2.3. Backup Strategy Summary</h3></div></div></div><p>
In case of an operating system crash or power failure,
<code class="literal">InnoDB</code> itself does all the job of recovering
data. But to make sure that you can sleep well, observe the
following guidelines:
</p><div class="itemizedlist"><ul type="disc"><li><p>
Always run the MySQL server with the
<a href="replication.html#option_mysqld_log-bin"><code class="option">--log-bin</code></a> option, or even
<a href="replication.html#option_mysqld_log-bin"><code class="option">--log-bin=<em class="replaceable"><code>log_name</code></em></code></a>,
where the log file name is located on some safe media
different from the drive on which the data directory is
located. If you have such safe media, this technique can
also be good for disk load balancing (which results in a
performance improvement).
</p></li><li><p>
Make periodic full backups, using the
<a href="programs.html#mysqldump" title="4.5.4. mysqldump — A Database Backup Program"><span><strong class="command">mysqldump</strong></span></a> command shown earlier in
<a href="backup-and-recovery.html#backup-policy" title="6.2.1. Backup Policy">Section 6.2.1, “Backup Policy”</a>, that makes an online,
nonblocking backup.
</p></li><li><p>
Make periodic incremental backups by flushing the logs with
<a href="sql-syntax.html#flush" title="12.5.6.3. FLUSH Syntax"><code class="literal">FLUSH LOGS</code></a>
or <a href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">mysqladmin flush-logs</strong></span></a>.
</p></li></ul></div></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="point-in-time-recovery"></a>6.3. Point-in-Time Recovery</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="backup-and-recovery.html#point-in-time-recovery-times">6.3.1. Point-in-Time Recovery from a Specific Time</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#point-in-time-recovery-positions">6.3.2. Point-in-Time Recovery from a Specific Log Position</a></span></dt></dl></div><a class="indexterm" name="id4259641"></a><a class="indexterm" name="id4259650"></a><p>
Point-in-time recovery refers to recovery of data changes made
since a given point in time. Typically, this type of recovery is
performed after restoring a server from a full backup. The full
backup restores the server to its state as of the time the backup
was made. Point-in-time recovery then brings the server up to date
from that point forward to a more recent time. The full backup can
be made in several ways, such as those listed in
<a href="backup-and-recovery.html#backup-methods" title="6.1. Database Backup Methods">Section 6.1, “Database Backup Methods”</a>. The source of information for
point-in-time recovery is the set of incremental backups
represented by the binary log files generated subsequent to the
full backup operation.
</p><p>
Point-in-time recovery requires that the MySQL server was started
with the <a href="replication.html#option_mysqld_log-bin"><code class="option">--log-bin</code></a> option to enable
binary logging (see <a href="server-administration.html#binary-log" title="5.2.4. The Binary Log">Section 5.2.4, “The Binary Log”</a>). You can use the
<a href="programs.html#mysqlbinlog" title="4.6.7. mysqlbinlog — Utility for Processing Binary Log Files"><span><strong class="command">mysqlbinlog</strong></span></a> utility to view the contents of or
recover data from the binary log files, starting from a specified
point in time (for example, since your last full backup) until the
present or another specified point in time. See
<a href="programs.html#mysqlbinlog" title="4.6.7. mysqlbinlog — Utility for Processing Binary Log Files">Section 4.6.7, “<span><strong class="command">mysqlbinlog</strong></span> — Utility for Processing Binary Log Files”</a>.
</p><p class="mnmas"><b>MySQL Enterprise</b>
For maximum data recovery, the MySQL Enterprise Monitor advises
subscribers to synchronize to disk at each write. For more
information, see
<a href="http://www.mysql.com/products/enterprise/advisors.html" target="_top">http://www.mysql.com/products/enterprise/advisors.html</a>.
</p><p>
To restore data from a binary log, you must know the location and
name of the current binary log file. By default, the server
creates binary log files in the data directory, but a path name
can be specified with the <a href="replication.html#option_mysqld_log-bin"><code class="option">--log-bin</code></a>
option to place the files in a different location. To determine
the name of the current binary log file, issue the following
statement:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW MASTER STATUS;</code></strong>
</pre><p>
To see a listing of all binary log files, use this statement:
</p><pre class="programlisting">mysql> <strong class="userinput"><code>SHOW MASTER LOGS;</code></strong>
</pre><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="point-in-time-recovery-times"></a>6.3.1. Point-in-Time Recovery from a Specific Time</h3></div></div></div><p>
To indicate the start and end times for recovery, specify the
<a href="programs.html#option_mysqlbinlog_start-datetime"><code class="option">--start-datetime</code></a> and
<a href="programs.html#option_mysqlbinlog_stop-datetime"><code class="option">--stop-datetime</code></a> options for
<a href="programs.html#mysqlbinlog" title="4.6.7. mysqlbinlog — Utility for Processing Binary Log Files"><span><strong class="command">mysqlbinlog</strong></span></a>, in
<a href="data-types.html#datetime" title="10.3.1. The DATETIME,
DATE, and
TIMESTAMP Types"><code class="literal">DATETIME</code></a> format. As an example,
suppose that exactly at 10:00 a.m. on April 20, 2005 an SQL
statement was executed that deleted a large table. To restore
the table and data, you could restore the previous night's
backup, and then execute the following command:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqlbinlog --stop-datetime="2005-04-20 9:59:59" \</code></strong>
<strong class="userinput"><code>/var/log/mysql/bin.123456 | mysql -u root -p</code></strong>
</pre><p>
This command recovers all of the data up until the date and time
given by the <a href="programs.html#option_mysqlbinlog_stop-datetime"><code class="option">--stop-datetime</code></a>
option. If you did not detect the erroneous SQL statement that
was entered until hours later, you will probably also want to
recover the activity that occurred afterward. Based on this, you
could run <a href="programs.html#mysqlbinlog" title="4.6.7. mysqlbinlog — Utility for Processing Binary Log Files"><span><strong class="command">mysqlbinlog</strong></span></a> again with a start date
and time, like so:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqlbinlog --start-datetime="2005-04-20 10:01:00" \</code></strong>
<strong class="userinput"><code>/var/log/mysql/bin.123456 | mysql -u root -p</code></strong>
</pre><p>
In this command, the SQL statements logged from 10:01 a.m. on
will be re-executed. The combination of restoring of the
previous night's dump file and the two
<a href="programs.html#mysqlbinlog" title="4.6.7. mysqlbinlog — Utility for Processing Binary Log Files"><span><strong class="command">mysqlbinlog</strong></span></a> commands restores everything up
until one second before 10:00 a.m. and everything from 10:01
a.m. on.
</p><p>
To use this method of point-in-time recovery, you should examine
the log to be sure of the exact times to specify for the
commands. To display the log file contents without executing
them, use this command:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqlbinlog /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql</code></strong>
</pre><p>
Then open the <code class="filename">/tmp/mysql_restore.sql</code> file
with a text editor to examine it.
</p><p>
Excluding specific changes by specifying times for
<a href="programs.html#mysqlbinlog" title="4.6.7. mysqlbinlog — Utility for Processing Binary Log Files"><span><strong class="command">mysqlbinlog</strong></span></a> does not work well if multiple
statements executed at the same time as the one to be excluded.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="point-in-time-recovery-positions"></a>6.3.2. Point-in-Time Recovery from a Specific Log Position</h3></div></div></div><p>
Instead of specifying dates and times, the
<a href="programs.html#option_mysqlbinlog_start-position"><code class="option">--start-position</code></a> and
<a href="programs.html#option_mysqlbinlog_stop-position"><code class="option">--stop-position</code></a> options for
<a href="programs.html#mysqlbinlog" title="4.6.7. mysqlbinlog — Utility for Processing Binary Log Files"><span><strong class="command">mysqlbinlog</strong></span></a> can be used for specifying log
positions. They work the same as the start and stop date
options, except that you specify log position numbers rather
than dates. Using positions may enable you to be more precise
about which part of the log to recover, especially if many
transactions occurred around the same time as a damaging SQL
statement. To determine the position numbers, run
<a href="programs.html#mysqlbinlog" title="4.6.7. mysqlbinlog — Utility for Processing Binary Log Files"><span><strong class="command">mysqlbinlog</strong></span></a> for a range of times near the
time when the unwanted transaction was executed, but redirect
the results to a text file for examination. This can be done
like so:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqlbinlog --start-datetime="2005-04-20 9:55:00" \</code></strong>
<strong class="userinput"><code>--stop-datetime="2005-04-20 10:05:00" \</code></strong>
<strong class="userinput"><code>/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql</code></strong>
</pre><p>
This command creates a small text file in the
<code class="filename">/tmp</code> directory that contains the SQL
statements around the time that the deleterious SQL statement
was executed. Open this file with a text editor and look for the
statement that you do not want to repeat. Determine the
positions in the binary log for stopping and resuming the
recovery and make note of them. Positions are labeled as
<code class="literal">log_pos</code> followed by a number. After restoring
the previous backup file, use the position numbers to process
the binary log file. For example, you would use commands
something like these:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysqlbinlog --stop-position=368312 /var/log/mysql/bin.123456 \</code></strong>
<strong class="userinput"><code>| mysql -u root -p</code></strong>
shell> <strong class="userinput"><code>mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 \</code></strong>
<strong class="userinput"><code>| mysql -u root -p</code></strong>
</pre><p>
The first command recovers all the transactions up until the
stop position given. The second command recovers all
transactions from the starting position given until the end of
the binary log. Because the output of
<a href="programs.html#mysqlbinlog" title="4.6.7. mysqlbinlog — Utility for Processing Binary Log Files"><span><strong class="command">mysqlbinlog</strong></span></a> includes <code class="literal">SET
TIMESTAMP</code> statements before each SQL statement
recorded, the recovered data and related MySQL logs will reflect
the original times at which the transactions were executed.
</p></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="table-maintenance"></a>6.4. Table Maintenance and Crash Recovery</h2></div></div></div><div class="toc"><dl><dt><span class="section"><a href="backup-and-recovery.html#crash-recovery">6.4.1. Using <span><strong class="command">myisamchk</strong></span> for Crash Recovery</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#check">6.4.2. How to Check <code class="literal">MyISAM</code> Tables for Errors</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#repair">6.4.3. How to Repair Tables</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#table-optimization">6.4.4. Table Optimization</a></span></dt><dt><span class="section"><a href="backup-and-recovery.html#maintenance-schedule">6.4.5. Setting Up a Table Maintenance Schedule</a></span></dt></dl></div><p>
This section discusses how to use <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> to
check or repair <code class="literal">MyISAM</code> tables (tables that have
<code class="filename">.MYD</code> and <code class="filename">.MYI</code> files for
storing data and indexes). For general
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> background, see
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility">Section 4.6.3, “<span><strong class="command">myisamchk</strong></span> — MyISAM Table-Maintenance Utility”</a>.
</p><p>
You can use <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> to check, repair, or
optimize database tables. The following sections describe how to
perform these operations and how to set up a table maintenance
schedule. For information about using <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a>
to get information about your tables, see
<a href="programs.html#myisamchk-table-info" title="4.6.3.5. myisamchk Table Information">Section 4.6.3.5, “<span><strong class="command">myisamchk</strong></span> Table Information”</a>.
</p><p>
Even though table repair with <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> is
quite secure, it is always a good idea to make a backup
<span class="emphasis"><em>before</em></span> doing a repair or any maintenance
operation that could make a lot of changes to a table.
</p><p>
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> operations that affect indexes can
cause <code class="literal">FULLTEXT</code> indexes to be rebuilt with
full-text parameters that are incompatible with the values used by
the MySQL server. To avoid this problem, follow the guidelines in
<a href="programs.html#myisamchk-general-options" title="4.6.3.1. myisamchk General Options">Section 4.6.3.1, “<span><strong class="command">myisamchk</strong></span> General Options”</a>.
</p><p>
In many cases, you may find it simpler to do
<code class="literal">MyISAM</code> table maintenance using the SQL
statements that perform operations that
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> can do:
</p><div class="itemizedlist"><ul type="disc"><li><p>
To check or repair <code class="literal">MyISAM</code> tables, use
<a href="sql-syntax.html#check-table" title="12.5.2.3. CHECK TABLE Syntax"><code class="literal">CHECK TABLE</code></a> or
<a href="sql-syntax.html#repair-table" title="12.5.2.6. REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a>.
</p></li><li><p>
To optimize <code class="literal">MyISAM</code> tables, use
<a href="sql-syntax.html#optimize-table" title="12.5.2.5. OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a>.
</p></li><li><p>
To analyze <code class="literal">MyISAM</code> tables, use
<a href="sql-syntax.html#analyze-table" title="12.5.2.1. ANALYZE TABLE Syntax"><code class="literal">ANALYZE TABLE</code></a>.
</p></li></ul></div><p>
These statements can be used directly or by means of the
<a href="programs.html#mysqlcheck" title="4.5.3. mysqlcheck — A Table Maintenance Program"><span><strong class="command">mysqlcheck</strong></span></a> client program. One advantage of
these statements over <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> is that the
server does all the work. With <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a>, you
must make sure that the server does not use the tables at the same
time so that there is no unwanted interaction between
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> and the server. See
<a href="sql-syntax.html#analyze-table" title="12.5.2.1. ANALYZE TABLE Syntax">Section 12.5.2.1, “<code class="literal">ANALYZE TABLE</code> Syntax”</a>, <a href="sql-syntax.html#check-table" title="12.5.2.3. CHECK TABLE Syntax">Section 12.5.2.3, “<code class="literal">CHECK TABLE</code> Syntax”</a>,
<a href="sql-syntax.html#optimize-table" title="12.5.2.5. OPTIMIZE TABLE Syntax">Section 12.5.2.5, “<code class="literal">OPTIMIZE TABLE</code> Syntax”</a>, and
<a href="sql-syntax.html#repair-table" title="12.5.2.6. REPAIR TABLE Syntax">Section 12.5.2.6, “<code class="literal">REPAIR TABLE</code> Syntax”</a>.
</p><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="crash-recovery"></a>6.4.1. Using <span><strong class="command">myisamchk</strong></span> for Crash Recovery</h3></div></div></div><a class="indexterm" name="id4260326"></a><a class="indexterm" name="id4260339"></a><a class="indexterm" name="id4260351"></a><a class="indexterm" name="id4260360"></a><p>
This section describes how to check for and deal with data
corruption in MySQL databases. If your tables become corrupted
frequently, you should try to find the reason why. See
<a href="error-handling.html#crashing" title="B.1.4.2. What to Do If MySQL Keeps Crashing">Section B.1.4.2, “What to Do If MySQL Keeps Crashing”</a>.
</p><p>
For an explanation of how <code class="literal">MyISAM</code> tables can
become corrupted, see <a href="storage-engines.html#myisam-table-problems" title="13.5.4. MyISAM Table Problems">Section 13.5.4, “<code class="literal">MyISAM</code> Table Problems”</a>.
</p><p>
If you run <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> with external locking
disabled (which is the default as of MySQL 4.0), you cannot
reliably use <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> to check a table when
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> is using the same table. If you can be
certain that no one will access the tables through
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> while you run
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a>, you only have to execute
<a href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">mysqladmin flush-tables</strong></span></a> before you start
checking the tables. If you cannot guarantee this, you must stop
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> while you check the tables. If you run
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> to check tables that
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> is updating at the same time, you may
get a warning that a table is corrupt even when it is not.
</p><p>
If the server is run with external locking enabled, you can use
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> to check tables at any time. In
this case, if the server tries to update a table that
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> is using, the server will wait for
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> to finish before it continues.
</p><p>
If you use <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> to repair or optimize
tables, you <span class="emphasis"><em>must</em></span> always ensure that the
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> server is not using the table (this
also applies if external locking is disabled). If you do not
stop <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a>, you should at least do a
<a href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">mysqladmin flush-tables</strong></span></a> before you run
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a>. Your tables <span class="emphasis"><em>may become
corrupted</em></span> if the server and
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> access the tables simultaneously.
</p><p>
When performing crash recovery, it is important to understand
that each <code class="literal">MyISAM</code> table
<em class="replaceable"><code>tbl_name</code></em> in a database corresponds to
the three files in the database directory shown in the following
table.
</p><div class="informaltable"><table border="1"><colgroup><col><col></colgroup><tbody><tr><td><span class="bold"><strong>File</strong></span></td><td><span class="bold"><strong>Purpose</strong></span></td></tr><tr><td><code class="filename"><em class="replaceable"><code>tbl_name</code></em>.frm</code></td><td>Definition (format) file</td></tr><tr><td><code class="filename"><em class="replaceable"><code>tbl_name</code></em>.MYD</code></td><td>Data file</td></tr><tr><td><code class="filename"><em class="replaceable"><code>tbl_name</code></em>.MYI</code></td><td>Index file</td></tr></tbody></table></div><p>
Each of these three file types is subject to corruption in
various ways, but problems occur most often in data files and
index files.
</p><p>
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> works by creating a copy of the
<code class="filename">.MYD</code> data file row by row. It ends the
repair stage by removing the old <code class="filename">.MYD</code> file
and renaming the new file to the original file name. If you use
<a href="programs.html#option_myisamchk_quick"><code class="option">--quick</code></a>,
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> does not create a temporary
<code class="filename">.MYD</code> file, but instead assumes that the
<code class="filename">.MYD</code> file is correct and generates only a
new index file without touching the <code class="filename">.MYD</code>
file. This is safe, because <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a>
automatically detects whether the <code class="filename">.MYD</code> file
is corrupt and aborts the repair if it is. You can also specify
the <a href="programs.html#option_myisamchk_quick"><code class="option">--quick</code></a> option twice to
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a>. In this case,
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> does not abort on some errors (such
as duplicate-key errors) but instead tries to resolve them by
modifying the <code class="filename">.MYD</code> file. Normally the use
of two <a href="programs.html#option_myisamchk_quick"><code class="option">--quick</code></a> options is
useful only if you have too little free disk space to perform a
normal repair. In this case, you should at least make a backup
of the table before running <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="check"></a>6.4.2. How to Check <code class="literal">MyISAM</code> Tables for Errors</h3></div></div></div><a class="indexterm" name="id4260789"></a><a class="indexterm" name="id4260802"></a><a class="indexterm" name="id4260814"></a><p>
To check a <code class="literal">MyISAM</code> table, use the following
commands:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="literal">myisamchk
<em class="replaceable"><code>tbl_name</code></em></code>
</p><p>
This finds 99.99% of all errors. What it cannot find is
corruption that involves <span class="emphasis"><em>only</em></span> the data
file (which is very unusual). If you want to check a table,
you should normally run <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> without
options or with the <code class="option">-s</code> (silent) option.
</p></li><li><p>
<code class="literal">myisamchk -m
<em class="replaceable"><code>tbl_name</code></em></code>
</p><p>
This finds 99.999% of all errors. It first checks all index
entries for errors and then reads through all rows. It
calculates a checksum for all key values in the rows and
verifies that the checksum matches the checksum for the keys
in the index tree.
</p></li><li><p>
<code class="literal">myisamchk -e
<em class="replaceable"><code>tbl_name</code></em></code>
</p><p>
This does a complete and thorough check of all data
(<code class="option">-e</code> means “<span class="quote">extended check</span>”).
It does a check-read of every key for each row to verify
that they indeed point to the correct row. This may take a
long time for a large table that has many indexes. Normally,
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> stops after the first error it
finds. If you want to obtain more information, you can add
the <code class="option">-v</code> (verbose) option. This causes
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> to keep going, up through a
maximum of 20 errors.
</p></li><li><p>
<code class="literal">myisamchk -e -i
<em class="replaceable"><code>tbl_name</code></em></code>
</p><p>
This is like the previous command, but the
<code class="option">-i</code> option tells
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> to print additional statistical
information.
</p></li></ul></div><p>
In most cases, a simple <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> command
with no arguments other than the table name is sufficient to
check a table.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="repair"></a>6.4.3. How to Repair Tables</h3></div></div></div><a class="indexterm" name="id4260991"></a><a class="indexterm" name="id4261004"></a><p>
The discussion in this section describes how to use
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> on <code class="literal">MyISAM</code> tables
(extensions <code class="filename">.MYI</code> and
<code class="filename">.MYD</code>).
</p><p>
You can also (and should, if possible) use the
<a href="sql-syntax.html#check-table" title="12.5.2.3. CHECK TABLE Syntax"><code class="literal">CHECK TABLE</code></a> and
<a href="sql-syntax.html#repair-table" title="12.5.2.6. REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a> statements to check
and repair <code class="literal">MyISAM</code> tables. See
<a href="sql-syntax.html#check-table" title="12.5.2.3. CHECK TABLE Syntax">Section 12.5.2.3, “<code class="literal">CHECK TABLE</code> Syntax”</a>, and
<a href="sql-syntax.html#repair-table" title="12.5.2.6. REPAIR TABLE Syntax">Section 12.5.2.6, “<code class="literal">REPAIR TABLE</code> Syntax”</a>.
</p><p>
Symptoms of corrupted tables include queries that abort
unexpectedly and observable errors such as these:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<code class="filename"><em class="replaceable"><code>tbl_name</code></em>.frm</code>
is locked against change
</p></li><li><p>
Can't find file
<code class="filename"><em class="replaceable"><code>tbl_name</code></em>.MYI</code>
(Errcode: <em class="replaceable"><code>nnn</code></em>)
</p></li><li><p>
Unexpected end of file
</p></li><li><p>
Record file is crashed
</p></li><li><p>
Got error <em class="replaceable"><code>nnn</code></em> from table handler
</p></li></ul></div><p>
To get more information about the error, run
<a href="programs.html#perror" title="4.8.1. perror — Explain Error Codes"><span><strong class="command">perror</strong></span></a> <em class="replaceable"><code>nnn</code></em>, where
<em class="replaceable"><code>nnn</code></em> is the error number. The
following example shows how to use <a href="programs.html#perror" title="4.8.1. perror — Explain Error Codes"><span><strong class="command">perror</strong></span></a> to
find the meanings for the most common error numbers that
indicate a problem with a table:
</p><pre class="programlisting">shell> <strong class="userinput"><code>perror 126 127 132 134 135 136 141 144 145</code></strong>
MySQL error code 126 = Index file is crashed
MySQL error code 127 = Record-file is crashed
MySQL error code 132 = Old database file
MySQL error code 134 = Record was already deleted (or record file crashed)
MySQL error code 135 = No more room in record file
MySQL error code 136 = No more room in index file
MySQL error code 141 = Duplicate unique key or constraint on write or update
MySQL error code 144 = Table is crashed and last repair failed
MySQL error code 145 = Table was marked as crashed and should be repaired
</pre><p>
Note that error 135 (no more room in record file) and error 136
(no more room in index file) are not errors that can be fixed by
a simple repair. In this case, you must use
<a href="sql-syntax.html#alter-table" title="12.1.7. ALTER TABLE Syntax"><code class="literal">ALTER TABLE</code></a> to increase the
<code class="literal">MAX_ROWS</code> and
<code class="literal">AVG_ROW_LENGTH</code> table option values:
</p><pre class="programlisting">ALTER TABLE <em class="replaceable"><code>tbl_name</code></em> MAX_ROWS=<em class="replaceable"><code>xxx</code></em> AVG_ROW_LENGTH=<em class="replaceable"><code>yyy</code></em>;
</pre><p>
If you do not know the current table option values, use
<a href="sql-syntax.html#show-create-table" title="12.5.5.12. SHOW CREATE TABLE Syntax"><code class="literal">SHOW CREATE TABLE</code></a>.
</p><p>
For the other errors, you must repair your tables.
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> can usually detect and fix most
problems that occur.
</p><p>
The repair process involves up to four stages, described here.
Before you begin, you should change location to the database
directory and check the permissions of the table files. On Unix,
make sure that they are readable by the user that
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> runs as (and to you, because you need
to access the files you are checking). If it turns out you need
to modify files, they must also be writable by you.
</p><p>
This section is for the cases where a table check fails (such as
those described in <a href="backup-and-recovery.html#check" title="6.4.2. How to Check MyISAM Tables for Errors">Section 6.4.2, “How to Check <code class="literal">MyISAM</code> Tables for Errors”</a>), or you want to use
the extended features that <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a>
provides.
</p><p>
The options that you can use for table maintenance with
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> are described in
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility">Section 4.6.3, “<span><strong class="command">myisamchk</strong></span> — MyISAM Table-Maintenance Utility”</a>.
</p><p>
If you are going to repair a table from the command line, you
must first stop the <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> server. Note that
when you do <a href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">mysqladmin shutdown</strong></span></a> on a remote
server, the <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> server is still alive for
a while after <a href="programs.html#mysqladmin" title="4.5.2. mysqladmin — Client for Administering a MySQL Server"><span><strong class="command">mysqladmin</strong></span></a> returns, until all
statement-processing has stopped and all index changes have been
flushed to disk.
</p><p>
<span class="bold"><strong>Stage 1: Checking your tables</strong></span>
</p><p>
Run <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk *.MYI</strong></span></a> or <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk -e
*.MYI</strong></span></a> if you have more time. Use the
<code class="option">-s</code> (silent) option to suppress unnecessary
information.
</p><p>
If the <a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> server is stopped, you should
use the <a href="programs.html#option_myisamchk_update-state"><code class="option">--update-state</code></a> option
to tell <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> to mark the table as
“<span class="quote">checked.</span>”
</p><p>
You have to repair only those tables for which
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> announces an error. For such
tables, proceed to Stage 2.
</p><p>
If you get unexpected errors when checking (such as <code class="literal">out
of memory</code> errors), or if <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a>
crashes, go to Stage 3.
</p><p>
<span class="bold"><strong>Stage 2: Easy safe repair</strong></span>
</p><p>
First, try <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk -r -q
<em class="replaceable"><code>tbl_name</code></em></strong></span></a> (<code class="option">-r
-q</code> means “<span class="quote">quick recovery mode</span>”). This
attempts to repair the index file without touching the data
file. If the data file contains everything that it should and
the delete links point at the correct locations within the data
file, this should work, and the table is fixed. Start repairing
the next table. Otherwise, use the following procedure:
</p><div class="orderedlist"><ol type="1"><li><p>
Make a backup of the data file before continuing.
</p></li><li><p>
Use <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk -r
<em class="replaceable"><code>tbl_name</code></em></strong></span></a>
(<code class="option">-r</code> means “<span class="quote">recovery mode</span>”).
This removes incorrect rows and deleted rows from the data
file and reconstructs the index file.
</p></li><li><p>
If the preceding step fails, use <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk
--safe-recover
<em class="replaceable"><code>tbl_name</code></em></strong></span></a>. Safe recovery
mode uses an old recovery method that handles a few cases
that regular recovery mode does not (but is slower).
</p></li></ol></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Note</h3><p>
If you want a repair operation to go much faster, you should
set the values of the
<a href="server-administration.html#sysvar_sort_buffer_size"><code class="literal">sort_buffer_size</code></a> and
<a href="server-administration.html#sysvar_key_buffer_size"><code class="literal">key_buffer_size</code></a> variables
each to about 25% of your available memory when running
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a>.
</p></div><p>
If you get unexpected errors when repairing (such as
<code class="literal">out of memory</code> errors), or if
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> crashes, go to Stage 3.
</p><p>
<span class="bold"><strong>Stage 3: Difficult repair</strong></span>
</p><p>
You should reach this stage only if the first 16KB block in the
index file is destroyed or contains incorrect information, or if
the index file is missing. In this case, it is necessary to
create a new index file. Do so as follows:
</p><div class="orderedlist"><ol type="1"><li><p>
Move the data file to a safe place.
</p></li><li><p>
Use the table description file to create new (empty) data
and index files:
</p><pre class="programlisting">shell> <strong class="userinput"><code>mysql <em class="replaceable"><code>db_name</code></em></code></strong>
mysql> <strong class="userinput"><code>SET autocommit=1;</code></strong>
mysql> <strong class="userinput"><code>TRUNCATE TABLE <em class="replaceable"><code>tbl_name</code></em>;</code></strong>
mysql> <strong class="userinput"><code>quit</code></strong>
</pre></li><li><p>
Copy the old data file back onto the newly created data
file. (Do not just move the old file back onto the new file.
You want to retain a copy in case something goes wrong.)
</p></li></ol></div><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><h3 class="title">Important</h3><p>
If you are using replication, you should stop it prior to
performing the above procedure, since it involves file system
operations, and these are not logged by MySQL.
</p></div><p>
Go back to Stage 2. <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk -r -q</strong></span></a> should
work. (This should not be an endless loop.)
</p><p>
You can also use the <code class="literal">REPAIR TABLE
<em class="replaceable"><code>tbl_name</code></em> USE_FRM</code> SQL
statement, which performs the whole procedure automatically.
There is also no possibility of unwanted interaction between a
utility and the server, because the server does all the work
when you use <a href="sql-syntax.html#repair-table" title="12.5.2.6. REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a>. See
<a href="sql-syntax.html#repair-table" title="12.5.2.6. REPAIR TABLE Syntax">Section 12.5.2.6, “<code class="literal">REPAIR TABLE</code> Syntax”</a>.
</p><p>
<span class="bold"><strong>Stage 4: Very difficult repair</strong></span>
</p><p>
You should reach this stage only if the
<code class="filename">.frm</code> description file has also crashed.
That should never happen, because the description file is not
changed after the table is created:
</p><div class="orderedlist"><ol type="1"><li><p>
Restore the description file from a backup and go back to
Stage 3. You can also restore the index file and go back to
Stage 2. In the latter case, you should start with
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk -r</strong></span></a>.
</p></li><li><p>
If you do not have a backup but know exactly how the table
was created, create a copy of the table in another database.
Remove the new data file, and then move the
<code class="filename">.frm</code> description and
<code class="filename">.MYI</code> index files from the other
database to your crashed database. This gives you new
description and index files, but leaves the
<code class="filename">.MYD</code> data file alone. Go back to Stage
2 and attempt to reconstruct the index file.
</p></li></ol></div></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="table-optimization"></a>6.4.4. Table Optimization</h3></div></div></div><a class="indexterm" name="id4261762"></a><a class="indexterm" name="id4261774"></a><p>
To coalesce fragmented rows and eliminate wasted space that
results from deleting or updating rows, run
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> in recovery mode:
</p><pre class="programlisting">shell> <strong class="userinput"><code>myisamchk -r <em class="replaceable"><code>tbl_name</code></em></code></strong>
</pre><p>
You can optimize a table in the same way by using the
<a href="sql-syntax.html#optimize-table" title="12.5.2.5. OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a> SQL statement.
<a href="sql-syntax.html#optimize-table" title="12.5.2.5. OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a> does a table
repair and a key analysis, and also sorts the index tree so that
key lookups are faster. There is also no possibility of unwanted
interaction between a utility and the server, because the server
does all the work when you use <a href="sql-syntax.html#optimize-table" title="12.5.2.5. OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE
TABLE</code></a>. See <a href="sql-syntax.html#optimize-table" title="12.5.2.5. OPTIMIZE TABLE Syntax">Section 12.5.2.5, “<code class="literal">OPTIMIZE TABLE</code> Syntax”</a>.
</p><p>
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> has a number of other options that
you can use to improve the performance of a table:
</p><div class="itemizedlist"><ul type="disc"><li><p>
<a href="programs.html#option_myisamchk_analyze"><code class="option">--analyze</code></a>,
<code class="option">-a</code>
</p></li><li><p>
<a href="programs.html#option_myisamchk_sort-index"><code class="option">--sort-index</code></a>,
<code class="option">-S</code>
</p></li><li><p>
<a href="programs.html#option_myisamchk_sort-records"><code class="option">--sort-records=<em class="replaceable"><code>index_num</code></em></code></a>,
<code class="option">-R <em class="replaceable"><code>index_num</code></em></code>
</p></li></ul></div><p>
For a full description of all available options, see
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility">Section 4.6.3, “<span><strong class="command">myisamchk</strong></span> — MyISAM Table-Maintenance Utility”</a>.
</p></div><div class="section" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="maintenance-schedule"></a>6.4.5. Setting Up a Table Maintenance Schedule</h3></div></div></div><a class="indexterm" name="id4261931"></a><a class="indexterm" name="id4261943"></a><p>
It is a good idea to perform table checks on a regular basis
rather than waiting for problems to occur. One way to check and
repair <code class="literal">MyISAM</code> tables is with the
<a href="sql-syntax.html#check-table" title="12.5.2.3. CHECK TABLE Syntax"><code class="literal">CHECK TABLE</code></a> and
<a href="sql-syntax.html#repair-table" title="12.5.2.6. REPAIR TABLE Syntax"><code class="literal">REPAIR TABLE</code></a> statements. See
<a href="sql-syntax.html#check-table" title="12.5.2.3. CHECK TABLE Syntax">Section 12.5.2.3, “<code class="literal">CHECK TABLE</code> Syntax”</a>, and
<a href="sql-syntax.html#repair-table" title="12.5.2.6. REPAIR TABLE Syntax">Section 12.5.2.6, “<code class="literal">REPAIR TABLE</code> Syntax”</a>.
</p><p>
Another way to check tables is to use
<a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a>. For maintenance purposes, you can
use <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk -s</strong></span></a>. The <code class="option">-s</code>
option (short for <a href="programs.html#option_myisamchk_silent"><code class="option">--silent</code></a>)
causes <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk</strong></span></a> to run in silent mode,
printing messages only when errors occur.
</p><a class="indexterm" name="id4262026"></a><p>
It is also a good idea to enable automatic
<code class="literal">MyISAM</code> table checking. For example, whenever
the machine has done a restart in the middle of an update, you
usually need to check each table that could have been affected
before it is used further. (These are “<span class="quote">expected crashed
tables.</span>”) To check <code class="literal">MyISAM</code> tables
automatically, start the server with the
<a href="server-administration.html#option_mysqld_myisam-recover"><code class="option">--myisam-recover</code></a> option. See
<a href="server-administration.html#server-options" title="5.1.2. Server Command Options">Section 5.1.2, “Server Command Options”</a>.
</p><p>
You should also check your tables regularly during normal system
operation. For example, you can run a <span><strong class="command">cron</strong></span>
job to check important tables once a week, using a line like
this in a <code class="filename">crontab</code> file:
</p><pre class="programlisting">35 0 * * 0 <em class="replaceable"><code>/path/to/myisamchk</code></em> --fast --silent <em class="replaceable"><code>/path/to/datadir</code></em>/*/*.MYI
</pre><p>
This prints out information about crashed tables so that you can
examine and repair them as necessary.
</p><p>
To start with, execute <a href="programs.html#myisamchk" title="4.6.3. myisamchk — MyISAM Table-Maintenance Utility"><span><strong class="command">myisamchk -s</strong></span></a> each
night on all tables that have been updated during the last 24
hours. As you see that problems occur infrequently, you can back
off the checking frequency to once a week or so.
</p><a class="indexterm" name="id4262116"></a><p>
Normally, MySQL tables need little maintenance. If you are
performing many updates to <code class="literal">MyISAM</code> tables with
dynamic-sized rows (tables with
<a href="data-types.html#char" title="10.4.1. The CHAR and
VARCHAR Types"><code class="literal">VARCHAR</code></a>,
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">BLOB</code></a>, or
<a href="data-types.html#blob" title="10.4.3. The BLOB and
TEXT Types"><code class="literal">TEXT</code></a> columns) or have tables with
many deleted rows you may want to defragment/reclaim space from
the tables from time to time. You can do this by using
<a href="sql-syntax.html#optimize-table" title="12.5.2.5. OPTIMIZE TABLE Syntax"><code class="literal">OPTIMIZE TABLE</code></a> on the tables in
question. Alternatively, if you can stop the
<a href="programs.html#mysqld" title="4.3.1. mysqld — The MySQL Server"><span><strong class="command">mysqld</strong></span></a> server for a while, change location
into the data directory and use this command while the server is
stopped:
</p><pre class="programlisting">shell> <strong class="userinput"><code>myisamchk -r -s --sort-index --sort_buffer_size=16M */*.MYI</code></strong>
</pre></div></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="server-administration.html">Prev</a> </td><td width="20%" align="center"> </td><td width="40%" align="right"> <a accesskey="n" href="optimization.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">Chapter 5. MySQL Server Administration </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 7. Optimization</td></tr></table></div></body></html>
