Chapter 6. Backup and Recovery
It is important to back up your databases so that you can recover
your data and be up and running again in case problems occur. MySQL
offers a variety of backup strategies from which you can choose the
methods that best suit the requirements for your installation. This
chapter discusses several backup and recovery topics with which you
should be familiar:
Types of backups: Logical versus physical, full versus
incremental, and so forth
Methods for creating backups
Recovery methods, including point-in-time recovery
Backup scheduling, compression, and encryption
Table maintenance, to enable recovery of corrupt tables
Resources related to backup or to maintaining data availability
include the following:
A forum dedicated to backup issues is available at
Details for mysqldump,
mysqlhotcopy, and other MySQL backup programs
can be found in Chapter 4, MySQL Programs.
The syntax of the SQL statements described here is given in
Chapter 12, SQL Statement Syntax.
For additional information about
backup procedures, see Section 13.6.6, “Backing Up and Recovering an
Replication enables you to maintain identical data on multiple
servers. This has several benefits, such as allowing client
query 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 with no impact on the master by using a
slave server. See Chapter 16, Replication.
MySQL Cluster provides a high-availability, high-redundancy
version of MySQL adapted for the distributed computing
environment. See Chapter 17, MySQL Cluster NDB 6.X/7.X. For
information specifically about MySQL Cluster backup, see
Section 17.5.3, “Online Backup of MySQL Cluster”.
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 Chapter 14, High Availability and Scalability
6.1. Backup and Recovery Types
This section describes the characteristics of different types of
Logical Versus Physical (Raw)
Logical backups save information represented as logical database
CREATE TABLE statements) and
INSERT statements or
delimited-text files). Physical backups consist of raw copies of
the directories and files that store database contents.
Logical backup methods have these characteristics:
The backup is done by querying the MySQL server to obtain
database structure and content information.
Backup is slower than physical methods because the server must
access database information and convert it to logical format.
If the output is written on the client side, the server must
also send it to the backup program.
Output is larger than for physical backup, particularly when
saved in text format.
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.
The backup does not include log or configuration files, or
other database-related files that are not part of databases.
Backups stored in logical format are machine independent and
Logical backups are performed with the MySQL server running.
The server is not taken offline.
Logical backup tools include the mysqldump
program and the
... INTO OUTFILE statement. These work for any
storage engine, even
To restore logical backups, SQL-format dump files can be
processed using the mysql client. To load
delimited-text files, use the
INFILE statement or the
Physical backup methods have these characteristics:
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
tables cannot be backed up this way because their contents are
not stored on disk.
Physical backup methods are faster than logical because they
involve only file copying without conversion.
Output is more compact than for logical backup.
Backup and restore granularity ranges from the level of the
entire data directory down to the level of individual files.
This may or may not provide for table-level granularity,
depending on storage engine. (Each
table corresponds uniquely to a set of files, but an
InnoDB table shares file storage with other
In addition to databases, the backup can include any related
files such as log or configuration files.
Backups are portable only to other machines that have
identical or similar hardware characteristics.
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.
Physical backup tools include file system-level commands (such
as cp, scp,
tables, ibbackup for
InnoDB tables, or
For restore, files copied at the file system level or with
mysqlhotcopy can be copied back to their
original locations with file system commands;
tables, and ndb_restore restores
Online Versus Offline Backups
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 “hot” versus
“cold” backups; a “warm” backup is one
where the server remains running but locked against modifying data
while you access database files externally.
Online backup methods have these characteristics:
The backup is less intrusive to other clients, which can
connect to the MySQL server during the backup and may be able
to access data depending on what operations they need to
Care must be taken to impose appropriate locking so that data
modifications do not take place that would compromise backup
Offline backup methods have these characteristics:
A similar distinction between online and offline applies for
recovery operations, and similar characteristics apply. However,
it is more likely that clients will be affected for online
recovery than for online backup because recovery requires stronger
locking. During backup, clients might be able to read data while
it is being backed up. Recovery modifies data and does not just
read it, so clients must be prevented from accessing data while it
is being restored.
Local Versus Remote Backups
A local backup is performed on the same host where the MySQL
server runs, whereas a remote backup is done from a different
host. For some types of backups, the backup can be initiated from
a remote host even if the output is written locally on the server.
mysqldump can connect to local or remote
servers. For SQL output (
INSERT statements), local or
remote dumps can be done and generate output on the client.
For delimited-text output (with the
--tab option), data files
are created on the server host.
mysqlhotcopy performs only local backups:
It connects to the server to lock it against data
modifications and then copies local table files.
SELECT ... INTO
OUTFILE can be initiated from a local or remote
client host, but the output file is created on the server
Physical backup methods typically are initiated locally on the
MySQL server host so that the server can be taken offline,
although the destination for copied files might be remote.
Some file system implementations enable “snapshots”
to be taken. These provide logical copies of the file system at a
given point in time, without requiring a physical copy of the
entire file system. (For example, the implementation may use
copy-on-write techniques so that only parts of the file system
modified after the snapshot time need be copied.) MySQL itself
does not provide the capability for taking file system snapshots.
It is available through third-party solutions such as Veritas,
LVM, or ZFS.
Full Versus Incremental Backups
A full backup includes all data managed by a MySQL server at a
given point in time. An incremental backup consists of the changes
made to the data during a given time span (from one point in time
to another). MySQL has different ways to perform full backups,
such as those described earlier in this section. Incremental
backups are made possible by enabling the server's binary log,
which the server uses to record data changes.
Full Versus Point-in-Time (Incremental)
A full recovery restores all data from a full backup. This
restores the server instance to the state that it had when the
backup was made. If that state is not sufficiently current, a full
recovery can be followed by recovery of incremental backups made
since the full backup, to bring the server to a more up-to-date
Incremental recovery is recovery of changes made during a given
time span. This is also called point-in-time recovery because it
makes a server's state current up to a given time. Point-in-time
recovery is based on the binary log and typically follows a full
recovery from the backup files that restores the server to its
state when the backup was made. Then the data changes written in
the binary log files are applied as incremental recovery to redo
data modifications and bring the server up to the desired point in
Data integrity can be compromised if tables become corrupt. MySQL
provides programs for checking
tables and repairing them should problems be found. See
Section 6.5, “
MyISAM Table Maintenance and Crash Recovery”.
Backup Scheduling, Compression, and
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. ibbackup can
InnoDB backups, and compression or
encryption of backup output can be achieved using file system
utilities. Other third-party solutions may be available.
6.2. Database Backup Methods
This section summarizes some general methods for making backups.
Making Backups by Copying Table
For storage engines that represent each table using its own files,
tables can be backed up by copying those files. For example,
MyISAM tables are stored as files, so it is
easy to do a backup by copying files (
To get a consistent backup, stop the server or do a
LOCK TABLES on the relevant tables
TABLES for the tables. See
Section 12.4.5, “
LOCK TABLES and
TABLES Syntax”, and Section 22.214.171.124, “
FLUSH Syntax”. 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
statement is needed to ensure that the all active index pages are
written to disk before you start the backup.
Making Delimited-Text File
To create a text file containing a table's data, you can use
SELECT * INTO OUTFILE
'. 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 constitutes a security risk. See
Section 12.2.8, “
SELECT Syntax”. This method works for any kind of data
file, but saves only table data, not the table structure.
Another way to create text data files (along with files containing
CREATE TABLE statements for the
backed up tables) is to use mysqldump with the
To reload the output data file, use
INFILE or mysqlimport.
Making Backups with
The mysqldump program and the
mysqlhotcopy script can make backups.
mysqldump is more general because it can back
up all kinds of tables. mysqlhotcopy works only
with some storage engines. (See Section 4.5.4, “mysqldump — A Database Backup Program”, and
Section 4.6.9, “mysqlhotcopy — A Database Backup Program”.)
Create a full backup of your database using
The first command dumps the database to the named file as
CREATE TABLE and
INSERT statements. The second
command creates two files per table in the named output directory.
One file contains the table contents as tab-delimited text. Other
other contains a
statement for the table.
Create a full backup of your database using
You can also create a binary backup simply by copying all table
files, as long as the server isn't updating anything. The
mysqlhotcopy script uses this method. (But note
that table file copying methods do not work if your database
mysqlhotcopy does not work for
InnoDB tables because
does not necessarily store table contents in database directories.
Also, even if the server is not actively updating data,
InnoDB may still have modified data cached in
memory and not flushed to disk.
InnoDB tables, it is possible to perform an
online backup that takes no locks on tables using the
--single-transaction option to
mysqldump. See Section 4.5.4, “mysqldump — A Database Backup Program”.
Making Incremental Backups by Enabling the
MySQL supports incremental backups: You must start the server with
--log-bin option to enable
binary logging; see Section 5.2.4, “The Binary Log”. The binary log
files provide you with the information you need to replicate
changes to the database that are made subsequent to the point at
which you performed a backup. 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
LOGS. 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 Section 6.4, “Point-in-Time (Incremental) Recovery Using the Binary Log”. The next
time you do a full backup, you should also rotate the binary log
mysqldump --flush-logs, or
mysqlhotcopy --flushlog. See
Section 4.5.4, “mysqldump — A Database Backup Program”, and Section 4.6.9, “mysqlhotcopy — A Database Backup Program”.
Making Backups Using Replication
If you are backing up a slave replication server, you should back
relay-log.info files when you back up the
slave's databases, regardless of the backup method you choose.
These information files are always needed to resume replication
after you restore the slave's data. If your slave is replicating
INFILE commands, you should also back up any
SQL_LOAD-* files that may exist in the
directory specified by the
--slave-load-tmpdir option. (This
location defaults to the value of the
system variable if not specified.) The slave needs these files to
resume replication of any interrupted
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 Section 16.2.1, “Using Replication for Backups”.
The MySQL Enterprise Monitor provides numerous advisors that
issue immediate warnings should replication issues arise. For
more information, see
Recovering Corrupt Tables
If you have to restore
MyISAM tables that have
become corrupt, try to recover them using
REPAIR TABLE or myisamchk
-r first. That should work in 99.9% of all cases. If
myisamchk fails, try the following procedure.
It is assumed that you have enabled binary logging by starting
MySQL with the
Restore the table from a mysqldump backup
or binary backup.
Execute the following command to re-run the updates in the
mysqlbinlog binlog.[0-9]* | mysql
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
Section 6.4, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
Making Backups Using a File System
If you are using a Veritas file system, you can make a backup like
From a client program, execute
FLUSH TABLES WITH READ
From another shell, execute
From the first client, execute
Copy files from the snapshot.
Unmount the snapshot.
Similar snapshot capabilities may be available in other file
systems, such as LVM or ZFS.
6.3. Example Backup and Recovery Strategy
This section discusses a procedure for performing backups that
enables you to recover data after several types of crashes:
The example commands do not include options such as
--password for the
mysqldump and mysql client
programs. You should include such options as necessary to enable
client programs to connect to the MySQL server.
Assume that data is stored in the
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
For cases of operating system crashes or power failures, we can
assume that MySQL's disk data is available after a restart. The
InnoDB data files might not contain consistent
data due to the crash, but
InnoDB reads its
logs and finds in them the list of pending committed and
noncommitted transactions that have not been flushed to the data
InnoDB 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:
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
mysqld: ready for connections
For the cases of file system crashes or hardware problems, we can
assume that the MySQL disk data is not
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.
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,
Backup provides online nonblocking physical backup of
InnoDB data files, and
mysqldump provides online logical backup.
This discussion uses mysqldump.
For expert advice on backups and replication, subscribe to the
MySQL Enterprise Monitor. For more information, see
Assume that we make a full backup of all our
InnoDB tables in all databases using the
following command on Sunday at 1 p.m., when load is low:
mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql
.sql file produced by
mysqldump contains a set of SQL
INSERT statements that can be
used to reload the dumped tables at a later time.
This backup operation acquires a global read lock on all tables
at the beginning of the dump (using
FLUSH TABLES WITH READ
LOCK). 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
FLUSH 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.
It was assumed earlier that the tables to back up are
InnoDB tables, so
--single-transaction uses a
consistent read and guarantees that data seen by
mysqldump does not change. (Changes made by
other clients to
InnoDB tables are not seen
by the mysqldump process.) If the backup
operation includes nontransactional tables, consistency requires
that they do not change during the backup. For example, for the
MyISAM tables in the
database, there must be no administrative changes to MySQL
accounts during the backup.
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
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
--log-bin 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
--log-bin option and that has
been running for some days, we find these MySQL binary log
-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
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
FLUSH LOGS SQL
statement or with a mysqladmin flush-logs
command. mysqldump also has an option to
flush the logs. The
.index file in the data
directory contains the list of all MySQL binary logs in the
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 mysqldump
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:
mysqldump --single-transaction --flush-logs --master-data=2 \
--all-databases > backup_sunday_1_PM.sql
After executing this command, the data directory contains a new
binary log file,
option causes the server to flush its logs. The
--master-data option causes
mysqldump to write binary log information to
its output, so the resulting
.sql dump file
includes these lines:
-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;
Because the mysqldump command made a full
backup, those lines mean two things:
The dump file contains all changes made before any changes
written to the
binary log file or newer.
All data changes logged after the backup are not present in
the dump file, but are present in the
gbichot2-bin.000007 binary log file or
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 mysqladmin flush-logs command
gbichot2-bin.000008. All changes
between the Sunday 1 p.m. full backup and Monday 1 p.m. will be
gbichot2-bin.000007 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 mysqladmin flush-logs command. All
changes between Monday 1 p.m. and Tuesday 1 p.m. will be in the
gbichot2-bin.000008 file (which also should
be copied somewhere safe).
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:
mysqldump --single-transaction --flush-logs --master-data=2 \
--all-databases --delete-master-logs > backup_sunday_1_PM.sql
6.3.2. Using Backups for Recovery
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:
mysql < backup_sunday_1_PM.sql
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
gbichot2-bin.000008 binary log files. Fetch
the files if necessary from where they were backed up, and then
process their contents like this:
mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql
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
--log-bin 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
file (and any subsequent files) at hand, and we could apply them
using mysqlbinlog and
mysql to restore the most recent data changes
with no loss up to the moment of the crash:
mysqlbinlog gbichot2-bin.000009 ... | mysql
For more information about using mysqlbinlog
to process binary log files, see
Section 6.4, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
6.3.3. Backup Strategy Summary
In case of an operating system crash or power failure,
InnoDB itself does all the job of recovering
data. But to make sure that you can sleep well, observe the
Always run the MySQL server with the
--log-bin option, or even
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
Make periodic full backups, using the
mysqldump command shown earlier in
Section 6.3.1, “Backup Policy”, that makes an online,
Make periodic incremental backups by flushing the logs with
or mysqladmin flush-logs.
6.4. Point-in-Time (Incremental) Recovery Using the Binary Log
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 full backup that brings the server to
its state as of the time the backup was made. (The full backup can
be made in several ways, such as those listed in
Section 6.2, “Database Backup Methods”.) Point-in-time recovery then
brings the server up to date incrementally from the time of the
full backup to a more recent time.
Point-in-time recovery is based on these principles:
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. Therefore,
the server must be started with the
--log-bin option to enable
binary logging (see Section 5.2.4, “The Binary Log”).
To restore data from the binary log, you must know the name
and location of the current binary log files. By default, the
server creates binary log files in the data directory, but a
path name can be specified with the
--log-bin option to place the
files in a different location. Section 5.2.4, “The Binary Log”.
To see a listing of all binary log files, use this statement:
SHOW BINARY LOGS;
To determine the name of the current binary log file, issue
the following statement:
SHOW MASTER STATUS;
The mysqlbinlog utility converts the events
in the binary log files from binary format to text so that
they can be executed or viewed. mysqlbinlog
has options for selecting sections of the binary log based on
event times or position of events within the log. See
Section 4.6.7, “mysqlbinlog — Utility for Processing Binary Log Files”.
Executing events from the binary log causes the data
modifications they represent to be redone. This enables
recovery of data changes for a given span of time. To execute
events from the binary log, process
mysqlbinlog output using the
binlog_files | mysql -u root -p
Viewing log contents can be useful when you need to determine
event times or positions to select partial log contents prior
to executing events. To view events from the log, send
mysqlbinlog output into a paging program:
binlog_files | more
Alternatively, save the output in a file and view the file in
a text editor:
binlog_files > tmpfile
edit tmpfile ...
Saving the output in a file is useful as a preliminary to
executing the log contents with certain events removed, such
as an accidental
You can delete from the file any statements not to be executed
before executing its contents. After editing the file, execute
the contents as follows:
mysql -u root -p < tmpfile
If you have more than one binary log to execute on the MySQL
server, the safe method is to process them all using a single
connection to the server. Here is an example that demonstrates
what may be unsafe:
mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!
Processing binary logs this way using different connections to the
server causes problems if the first log file contains a
TABLE statement and the second log contains a statement
that uses the temporary table. When the first
mysql process terminates, the server drops the
temporary table. When the second mysql process
attempts to use the table, the server reports “unknown
To avoid problems like this, use a single
connection to execute the contents of all binary logs that you
want to process. Here is one way to do so:
mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
Another approach is to write all the logs to a single file and
then process the file:
mysqlbinlog binlog.000001 > /tmp/statements.sql
mysqlbinlog binlog.000002 >> /tmp/statements.sql
mysql -u root -p -e "source /tmp/statements.sql"
For maximum data recovery, the MySQL Enterprise Monitor advises
subscribers to synchronize to disk at each write. For more
6.4.1. Point-in-Time Recovery Using Event Times
To indicate the start and end times for recovery, specify the
--stop-datetime options for
DATETIME 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:
mysqlbinlog --stop-datetime="2005-04-20 9:59:59" \
/var/log/mysql/bin.123456 | mysql -u root -p
This command recovers all of the data up until the date and time
given by the
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 mysqlbinlog again with a start date
and time, like so:
mysqlbinlog --start-datetime="2005-04-20 10:01:00" \
/var/log/mysql/bin.123456 | mysql -u root -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
mysqlbinlog commands restores everything up
until one second before 10:00 a.m. and everything from 10:01
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:
mysqlbinlog /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
Then open the
with a text editor to examine it.
Excluding specific changes by specifying times for
mysqlbinlog does not work well if multiple
statements executed at the same time as the one to be excluded.
6.4.2. Point-in-Time Recovery Using Event Positions
Instead of specifying dates and times, the
--stop-position options for
mysqlbinlog 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
mysqlbinlog 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
mysqlbinlog --start-datetime="2005-04-20 9:55:00" \
--stop-datetime="2005-04-20 10:05:00" \
/var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
This command creates a small text file in the
/tmp 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
log_pos 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:
mysqlbinlog --stop-position=368312 /var/log/mysql/bin.123456 \
| mysql -u root -p
mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 \
| mysql -u root -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
TIMESTAMP statements before each SQL statement
recorded, the recovered data and related MySQL logs will reflect
the original times at which the transactions were executed.
MyISAM Table Maintenance and Crash Recovery
This section discusses how to use myisamchk to
check or repair
MyISAM tables (tables that have
.MYI files for
storing data and indexes). For general
myisamchk background, see
Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”. Other table-repair information can be
found at Section 2.4.4, “Rebuilding or Repairing Tables or Indexes”.
You can use myisamchk 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 myisamchk
to get information about your tables, see
Section 126.96.36.199, “myisamchk Table Information”.
Even though table repair with myisamchk is
quite secure, it is always a good idea to make a backup
before doing a repair or any maintenance
operation that could make a lot of changes to a table.
myisamchk operations that affect indexes can
FULLTEXT 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
Section 188.8.131.52, “myisamchk General Options”.
MyISAM table maintenance can also be done using
the SQL statements that perform operations similar to what
myisamchk can do:
For additional information about these statements, see
Section 12.5.2, “Table Maintenance Statements”.
These statements can be used directly or by means of the
mysqlcheck client program. One advantage of
these statements over myisamchk is that the
server does all the work. With myisamchk, you
must make sure that the server does not use the tables at the same
time so that there is no unwanted interaction between
myisamchk and the server.
6.5.1. Using myisamchk for Crash Recovery
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
Section B.5.4.2, “What to Do If MySQL Keeps Crashing”.
For an explanation of how
MyISAM tables can
become corrupted, see Section 13.5.4, “
MyISAM Table Problems”.
If you run mysqld with external locking
disabled (which is the default), you cannot reliably use
myisamchk to check a table when
mysqld is using the same table. If you can be
certain that no one will access the tables through
mysqld while you run
myisamchk, you only have to execute
mysqladmin flush-tables before you start
checking the tables. If you cannot guarantee this, you must stop
mysqld while you check the tables. If you run
myisamchk to check tables that
mysqld is updating at the same time, you may
get a warning that a table is corrupt even when it is not.
If the server is run with external locking enabled, you can use
myisamchk to check tables at any time. In
this case, if the server tries to update a table that
myisamchk is using, the server will wait for
myisamchk to finish before it continues.
If you use myisamchk to repair or optimize
tables, you must always ensure that the
mysqld server is not using the table (this
also applies if external locking is disabled). If you do not
stop mysqld, you should at least do a
mysqladmin flush-tables before you run
myisamchk. Your tables may become
corrupted if the server and
myisamchk access the tables simultaneously.
When performing crash recovery, it is important to understand
tbl_name in a database corresponds to
the three files in the database directory shown in the following
|Definition (format) file|
Each of these three file types is subject to corruption in
various ways, but problems occur most often in data files and
myisamchk works by creating a copy of the
.MYD data file row by row. It ends the
repair stage by removing the old
and renaming the new file to the original file name. If you use
myisamchk does not create a temporary
.MYD file, but instead assumes that the
.MYD file is correct and generates only a
new index file without touching the
file. This is safe, because myisamchk
automatically detects whether the
is corrupt and aborts the repair if it is. You can also specify
--quick option twice to
myisamchk. In this case,
myisamchk does not abort on some errors (such
as duplicate-key errors) but instead tries to resolve them by
.MYD file. Normally the use
--quick 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 myisamchk.
6.5.2. How to Check
MyISAM Tables for Errors
To check a
MyISAM table, use the following
This finds 99.99% of all errors. What it cannot find is
corruption that involves only the data
file (which is very unusual). If you want to check a table,
you should normally run myisamchk without
options or with the
-s (silent) option.
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.
This does a complete and thorough check of all data
-e means “extended check”).
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,
myisamchk stops after the first error it
finds. If you want to obtain more information, you can add
-v (verbose) option. This causes
myisamchk to keep going, up through a
maximum of 20 errors.
myisamchk -e -i
This is like the previous command, but the
-i option tells
myisamchk to print additional statistical
In most cases, a simple myisamchk command
with no arguments other than the table name is sufficient to
check a table.
6.5.3. How to Repair
The discussion in this section describes how to use
You can also (and should, if possible) use the
CHECK TABLE and
REPAIR TABLE statements to check
MyISAM tables. See
Section 184.108.40.206, “
CHECK TABLE Syntax”, and
Section 220.127.116.11, “
REPAIR TABLE Syntax”.
Symptoms of corrupted tables include queries that abort
unexpectedly and observable errors such as these:
is locked against change
Can't find file
Unexpected end of file
Record file is crashed
nnn from table handler
To get more information about the error, run
nnn is the error number. The
following example shows how to use perror to
find the meanings for the most common error numbers that
indicate a problem with a table:
perror 126 127 132 134 135 136 141 144 145
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
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
ALTER TABLE to increase the
AVG_ROW_LENGTH table option values:
If you do not know the current table option values, use
SHOW CREATE TABLE.
For the other errors, you must repair your tables.
myisamchk can usually detect and fix most
problems that occur.
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
mysqld 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.
This section is for the cases where a table check fails (such as
those described in Section 6.5.2, “How to Check
MyISAM Tables for Errors”), or you want
to use the extended features that myisamchk
The options that you can use for table maintenance with
myisamchk are described in
Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.
If you are going to repair a table from the command line, you
must first stop the mysqld server. Note that
when you do mysqladmin shutdown on a remote
server, the mysqld server is still alive for
a while after mysqladmin returns, until all
statement-processing has stopped and all index changes have been
flushed to disk.
Stage 1: Checking your tables
Run myisamchk *.MYI or myisamchk -e
*.MYI if you have more time. Use the
-s (silent) option to suppress unnecessary
If the mysqld server is stopped, you should
to tell myisamchk to mark the table as
You have to repair only those tables for which
myisamchk announces an error. For such
tables, proceed to Stage 2.
If you get unexpected errors when checking (such as
of memory errors), or if myisamchk
crashes, go to Stage 3.
Stage 2: Easy safe repair
First, try myisamchk -r -q
-q means “quick recovery mode”). 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:
Make a backup of the data file before continuing.
Use myisamchk -r
-r means “recovery mode”).
This removes incorrect rows and deleted rows from the data
file and reconstructs the index file.
If the preceding step fails, use myisamchk
tbl_name. Safe recovery
mode uses an old recovery method that handles a few cases
that regular recovery mode does not (but is slower).
If you want a repair operation to go much faster, you should
set the values of the
each to about 25% of your available memory when running
If you get unexpected errors when repairing (such as
out of memory errors), or if
myisamchk crashes, go to Stage 3.
Stage 3: Difficult repair
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:
Move the data file to a safe place.
Use the table description file to create new (empty) data
and index files:
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.)
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.
Go back to Stage 2. myisamchk -r -q should
work. (This should not be an endless loop.)
You can also use the
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
REPAIR TABLE. See
Section 18.104.22.168, “
REPAIR TABLE Syntax”.
Stage 4: Very difficult repair
You should reach this stage only if the
.frm description file has also crashed.
That should never happen, because the description file is not
changed after the table is created:
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
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
.frm description and
.MYI index files from the other
database to your crashed database. This gives you new
description and index files, but leaves the
.MYD data file alone. Go back to Stage
2 and attempt to reconstruct the index file.
MyISAM Table Optimization
To coalesce fragmented rows and eliminate wasted space that
results from deleting or updating rows, run
myisamchk in recovery mode:
You can optimize a table in the same way by using the
OPTIMIZE TABLE SQL statement.
OPTIMIZE TABLE 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
TABLE. See Section 22.214.171.124, “
OPTIMIZE TABLE Syntax”.
myisamchk has a number of other options that
you can use to improve the performance of a table:
-a: Perform key distribution analysis. This
improves join performance by enabling the join optimizer to
better choose the order in which to join the tables and
which indexes it should use.
-S: Sort the index blocks. This optimizes
seeks and makes table scans that use indexes faster.
Sort data rows according to a given index. This makes your
data much more localized and may speed up range-based
BY operations that use this index.
For a full description of all available options, see
Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.
6.5.5. Setting Up a
MyISAM Table Maintenance Schedule
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
MyISAM tables is with the
CHECK TABLE and
REPAIR TABLE statements. See
Section 12.5.2, “Table Maintenance Statements”.
Another way to check tables is to use
myisamchk. For maintenance purposes, you can
use myisamchk -s. The
option (short for
causes myisamchk to run in silent mode,
printing messages only when errors occur.
It is also a good idea to enable automatic
MyISAM 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 “expected crashed
tables.”) To cause the server to check
MyISAM tables automatically, start it with
--myisam-recover option. See
Section 5.1.2, “Server Command Options”.
You should also check your tables regularly during normal system
operation. For example, you can run a cron
job to check important tables once a week, using a line like
this in a
35 0 * * 0
/path/to/myisamchk --fast --silent
This prints out information about crashed tables so that you can
examine and repair them as necessary.
To start with, execute myisamchk -s 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.
Normally, MySQL tables need little maintenance. If you are
performing many updates to
MyISAM tables with
dynamic-sized rows (tables with
TEXT 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
OPTIMIZE TABLE on the tables in
question. Alternatively, if you can stop the
mysqld server for a while, change location
into the data directory and use this command while the server is
myisamchk -r -s --sort-index --sort_buffer_size=16M */*.MYI