Table of Contents
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 that you can choose from to select whatever methods best suit the requirements for your installation.
Briefly summarized, backup concepts with which you should be familiar include the following:
Logical versus physical backups
Online versus offline backups
Local versus remote backups
Snapshot backups
Full versus incremental backups
Point-in-time recovery
Backup scheduling, compression, and encryption
Table maintenance
More generally, the following discussion amplifies on the properties of different backup methods.
Logical versus physical (raw)
backups. Logical backups save information represented
as logical database structure (CREATE
DATABASE
, CREATE TABLE
statements)
and content (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 going through the MySQL server to obtain database structure and content information.
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.
Output is larger than for physical backup, paticularly 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 highly portable.
Logical backups are performed with the MySQL server running (the server is not taken offline).
Logical backup tools include the
mysqldump program and the SELECT
... INTO OUTFILE
statement. These work for any
storage engine, even MEMORY
.
For restore, SQL-format dump files can be processed using
the mysql client. To load delimited-text
files, use the LOAD DATA INFILE
statement
or the mysqlimport client.
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 MEMORY
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 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 MyISAM
table corresponds uniquely to a set of files, but an
InnoDB
table shares file storage with
other InnoDB
tables.)
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 filesystem-level commands
(such as cp, scp,
tar, rsync),
mysqlhotcopy for
MyISAM
tables,
ibbackup for InnoDB
tables, or START BACKUP
for
NDB
tables.
For restore, files copied at the filesystem level or with
mysqlhotcopy can be copied back to their
original locations with filesystem commands;
ibback restores InnoDB
tables, and ndb_restore restores
NDB
tables.
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:
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.
Care must be taken to impose appropriate locking so that data modifications do not take place that compromise backup integrity.
Offline backup methods have these characteristics:
Affects clients adversely because the server is unavailable during backup.
Simpler backup procedure because there is no possibility of interference from client activity.
Local versus remote backups. A local backup is performed on the same host where the MySQL server runs, whereas a remote backup is initiated from a different host.
mysqldump can connect to local or remote
servers. For SQL output (CREATE
and
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 remote client host, but the output file is created on
the server host.
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.
Snapshot backups. Some filesystem implementations enable “snapshots” to be taken. These provide logical copies of the filesystem at a given point in time, without having to physically copy the entire filesystem. (For example, the implementation may use copy-on-write techniques so that only parts of the filesystem modified after the snapshot time need be copied.) MySQL itself does not provide the capability for taking filesystem snapshots. It is available through third-party solutions such as Veritas or LVM.
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 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.
Point-in-time recovery. 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.
Backup scheduling, compression, and
encryption. 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 compress
InnoDB
backups, and compression or encryption
of backup output can be achieved using filesystem utilities.
Other third-party solutions may be available.
Table maintenance. 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
MyISAM
tables. See
Section 6.4, “Table Maintenance and Crash Recovery”.
Additional resources
Resources related to backup or to maintaining data availability include the following:
A forum dedicated to backup issues is available at http://forums.mysql.com/list.php?93.
The syntax of the SQL statements described here is given in Chapter 12, SQL Statement Syntax.
Details for mysqldump, mysqlhotcopy, and other MySQL backup programs can be found in Chapter 4, MySQL Programs.
For additional information about InnoDB
backup procedures, see Section 13.2.8, “Backing Up and Recovering an InnoDB
Database”.
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 Chapter 15, Replication.
MySQL Cluster provides a high-availability, high-redundancy version of MySQL adapted for the distributed computing environment. See Chapter 16, MySQL Cluster. For information specifically about MySQL Cluster backup, see Section 16.9, “On-line Backup of MySQL Cluster”.
This section summarizes some general methods for making backups.
Making Backups by Copying Files
MyISAM
tables are stored as files, so it is
easy to do a backup by copying files. To get a consistent backup,
do a LOCK TABLES
on the relevant tables,
followed by FLUSH TABLES
for the tables. See
Section 12.4.5, “LOCK TABLES
and UNLOCK TABLES
Syntax”, and Section 12.5.5.2, “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 FLUSH TABLES
statement
is needed to ensure that the all active index pages are written to
disk before you start the backup.
Making Delimited-Text File Backups
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 would constitute a security risk. See
Section 12.2.7, “file_name
' FROM
tbl_name
SELECT
Syntax”. This method works for any kind of data
file, but saves only table data, not the table structure.
To reload the output file, use LOAD DATA INFILE
or mysqlimport.
Making Backups with mysqldump or mysqlhotcopy
Another technique for backing up a database is to use the mysqldump program or the mysqlhotcopy script. 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.8, “mysqlhotcopy — A Database Backup Program”.)
Create a full backup of your database:
shell> mysqldump --tab=/path/to/some/dir
--opt db_name
Or:
shell> mysqlhotcopy db_name
/path/to/some/dir
You can also create a binary backup simply by copying all table
files (*.frm
, *.MYD
, and
*.MYI
files), as long as the server isn't
updating anything. The mysqlhotcopy script uses
this method. (But note that these methods do not work if your
database contains InnoDB
tables.
InnoDB
does not necessarily store table
contents in database directories, and
mysqlhotcopy works only for
MyISAM
and ISAM
tables.)
For InnoDB
tables, it is possible to perform an
online backup that takes no locks on tables; see
Section 4.5.4, “mysqldump — A Database Backup Program”.
Using the Binary Log to Enable Incremental Backups
MySQL supports incremental backups: You must start the server with
the --log-bin
option to enable binary logging;
see Section 5.2.3, “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 by using
FLUSH 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.3, “Point-in-Time Recovery”. The next time you do a
full backup, you should also rotate the binary log using
FLUSH LOGS
, mysqldump
--flush-logs, or mysqlhotcopy
--flushlog. See Section 4.5.4, “mysqldump — A Database Backup Program”, and
Section 4.6.8, “mysqlhotcopy — A Database Backup Program”.
Backing Up Replication Slaves
If your MySQL server is a slave replication server, then
regardless of the backup method you choose, you should also back
up the master.info
and
relay-log.info
files when you back up your
slave's data. These files are always needed to resume replication
after you restore the slave's data. If your slave is subject to
replicating LOAD DATA 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 tmpdir
system
variable if not specified.) The slave needs these files to resume
replication of any interrupted LOAD DATA INFILE
operations.
MySQL Enterprise The MySQL Enterprise Monitor provides numerous advisors that issue immediate warnings should replication issues arise. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
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 Chapter 15, Replication.
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
--log-bin
option.
Restore the original mysqldump backup, or binary backup.
Execute the following command to re-run the updates in the binary logs:
shell> 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.3, “Point-in-Time Recovery”.
Making Backups Using a Filesystem Snapshot
If you are using a Veritas filesystem, you can make a backup like this:
From a client program, execute FLUSH TABLES WITH READ
LOCK
.
From another shell, execute mount vxfs
snapshot
.
From the first client, execute UNLOCK
TABLES
.
Copy files from the snapshot.
Unmount the snapshot.
This section discusses a procedure for performing backups that allows you to recover data after several types of crashes:
Operating system crash
Power failure
Filesystem crash
Hardware problem (hard drive, motherboard, and so forth)
The example commands do not include options such as
--user
and --password
for the
mysqldump and mysql
programs. You should include such options as necessary so that the
MySQL server allows you to connect to it.
We assume that data is stored in the InnoDB
storage engine, which has support for transactions and automatic
crash recovery. We also assume that the MySQL server is under load
at the time of the crash. If it were not, no recovery would ever
be needed.
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
non-committed transactions that have not been flushed to the data
files. 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 InnoDB: Started mysqld: ready for connections
For the cases of filesystem 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 we must already have made backups. To make sure that is the case, we should design a backup policy.
We all know that backups must be scheduled periodically. A full
backup (a snapshot of the data at a point in time) can be done
in MySQL with several tools. For example, InnoDB Hot
Backup
provides online non-blocking physical backup of
the InnoDB
data files, and
mysqldump provides online logical backup.
This discussion uses mysqldump.
MySQL Enterprise For expert advice on backups and replication, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
Assume that we make a backup on Sunday at 1 p.m., when load is
low. The following command makes a full backup of all our
InnoDB
tables in all databases:
shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql
This is an online, non-blocking backup that does not disturb the
reads and writes on the tables. We assumed earlier that our
tables 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 we do also have other
types of tables, we must assume that they are not changed during
the backup. For example, for the MyISAM
tables in the mysql
database, we must assume
that no administrative changes are being made to MySQL accounts
during the backup.
The resulting .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.
Full backups are necessary, but they are not always convenient. 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. After we have made the initial full backup, it is more efficient to make incremental backups. They 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.
To make incremental backups, we need to save the incremental
changes. The MySQL server should always be started with the
--log-bin
option so that it stores these
changes in a file while it updates data. This option enables
binary logging, so that the server writes each SQL statement
that updates data into a file called a MySQL binary log. 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 files:
-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 directory. This file is used for replication.
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, then any 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:
shell>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, gbichot2-bin.000007
. The
resulting .sql
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 .sql
file contains all changes made
before any changes written to the
gbichot2-bin.000007
binary log file or
newer.
All data changes logged after the backup are not present in
the .sql
, but are present in the
gbichot2-bin.000007
binary log file or
newer.
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
creates gbichot2-bin.000008
. All changes
between the Sunday 1 p.m. full backup and Monday 1 p.m. will be
in the 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:
shell>mysqldump --single-transaction --flush-logs --master-data=2 \
--all-databases --delete-master-logs > backup_sunday_1_PM.sql
Deleting the MySQL binary logs with mysqldump
--delete-master-logs 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 PURGE MASTER LOGS
statement explains what should be verified before deleting the
MySQL binary logs. See Section 12.6.1.1, “PURGE MASTER LOGS
Syntax”.
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:
shell> 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.000007
and
gbichot2-bin.000008
binary log files. Fetch
the files if necessary from where they were backed up, and then
process their contents like this:
shell> 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 gbichot2-bin.000009
file
at hand, and we could apply it using
mysqlbinlog and mysql to
restore the most recent data changes with no loss up to the
moment of the crash.
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
following guidelines:
Always run the MySQL server with the
--log-bin
option, or even
--log-bin=
,
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).
log_name
Make periodic full backups, using the mysqldump command shown earlier in Section 6.2.1, “Backup Policy”, that makes an online, non-blocking backup.
Make periodic incremental backups by flushing the logs with
FLUSH LOGS
or mysqladmin
flush-logs.
If a MySQL server was started with the --log-bin
option to enable binary logging, you can use the
mysqlbinlog utility to recover data from the
binary log files, starting from a specified point in time (for
example, since your last backup) until the present or another
specified point in time. For information on enabling the binary
log and using mysqlbinlog, see
Section 5.2.3, “The Binary Log”, and Section 4.6.7, “mysqlbinlog — Utility for Processing Binary Log Files”.
MySQL Enterprise For maximum data recovery, the MySQL Enterprise Monitor advises subscribers to synchronize to disk at each write. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
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 pathname can
be specified with the --log-bin
option to place
the files in a different location. Typically the option is given
in an option file (that is, my.cnf
or
my.ini
, depending on your system). It can
also be given on the command line when the server is started. To
determine the name of the current binary log file, issue the
following statement:
mysql> SHOW MASTER STATUS
If you prefer, you can execute the following command from the command line instead:
shell> mysql -u root -p -E -e "SHOW MASTER STATUS"
Enter the root
password for your server when
mysql prompts you for it.
To view the contents of a binary log, use
mysqlbinlog
. See Section 4.6.7, “mysqlbinlog — Utility for Processing Binary Log Files”.
To indicate the start and end times for recovery, specify the
--start-date
and --stop-date
options for mysqlbinlog, in
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:
shell>mysqlbinlog --stop-date="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 --stop-date
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:
shell>mysqlbinlog --start-date="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 a.m. on. 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:
shell> mysqlbinlog /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql
Then open the file with a text editor to examine it.
Instead of specifying dates and times, the
--start-position
and
--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
like so:
shell>mysqlbinlog --start-date="2005-04-20 9:55:00" \
--stop-date="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 don't 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:
shell>mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \
| mysql -u root -p
shell>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
mysqlbinlog includes SET
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.
This section discusses how to use myisamchk to
check or repair MyISAM
tables (tables that have
.MYD
and .MYI
files for
storing data and indexes). For general
myisamchk background, see
Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.
You can use myisamchk to get information about your database tables or to check, repair, or optimize them. The following sections describe how to perform these operations and how to set up a table maintenance schedule.
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
cause 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 4.6.3.1, “myisamchk General Options”.
In many cases, you may find it simpler to do
MyISAM
table maintenance using the SQL
statements that perform operations that
myisamchk can do:
To check or repair MyISAM
tables, use
CHECK TABLE
or REPAIR
TABLE
.
To optimize MyISAM
tables, use
OPTIMIZE TABLE
.
To analyze MyISAM
tables, use
ANALYZE TABLE
.
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. See
Section 12.5.2.1, “ANALYZE TABLE
Syntax”, Section 12.5.2.3, “CHECK TABLE
Syntax”,
Section 12.5.2.5, “OPTIMIZE TABLE
Syntax”, and
Section 12.5.2.6, “REPAIR TABLE
Syntax”.
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.1.4.2, “What to Do If MySQL Keeps Crashing”.
For an explanation of how MyISAM
tables can
become corrupted, see Section 13.1.4, “MyISAM
Table Problems”.
If you run mysqld with external locking disabled (which is the default as of MySQL 4.0), 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 don't 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
that each MyISAM
table
tbl_name
in a database corresponds to
three files in the database directory:
File | Purpose |
| Definition (format) file |
| Data file |
| Index file |
Each of these three file types is subject to corruption in various ways, but problems occur most often in data files and index files.
myisamchk works by creating a copy of the
.MYD
data file row by row. It ends the
repair stage by removing the old .MYD
file
and renaming the new file to the original file name. If you use
--quick
, 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
.MYD
file. This is safe, because
myisamchk automatically detects whether the
.MYD
file is corrupt and aborts the repair
if it is. You can also specify the --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
modifying the .MYD
file. Normally the use
of two --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.
To check a MyISAM
table, use the following
commands:
myisamchk
tbl_name
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.
myisamchk -m
tbl_name
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.
myisamchk -e
tbl_name
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
the -v
(verbose) option. This causes
myisamchk to keep going, up through a
maximum of 20 errors.
myisamchk -e -i
tbl_name
This is like the previous command, but the
-i
option tells
myisamchk to print additional statistical
information.
In most cases, a simple myisamchk command with no arguments other than the table name is sufficient to check a table.
The discussion in this section describes how to use
myisamchk on MyISAM
tables
(extensions .MYI
and
.MYD
).
You can also (and should, if possible) use the CHECK
TABLE
and REPAIR TABLE
statements
to check and repair MyISAM
tables. See
Section 12.5.2.3, “CHECK TABLE
Syntax”, and
Section 12.5.2.6, “REPAIR TABLE
Syntax”.
Symptoms of corrupted tables include queries that abort unexpectedly and observable errors such as these:
is locked against change
tbl_name
.frm
Can't find file
(Errcode: tbl_name
.MYInnn
)
Unexpected end of file
Record file is crashed
Got error nnn
from table handler
To get more information about the error, run
perror nnn
, where
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:
shell> 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 MAX_ROWS
and
AVG_ROW_LENGTH
table option values:
ALTER TABLEtbl_name
MAX_ROWS=xxx
AVG_ROW_LENGTH=yyy
;
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.4.2, “How to Check MyISAM
Tables for Errors”), or you want to use
the extended features that myisamchk
provides.
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
information.
If the mysqld server is stopped, you should
use the --update-state
option to tell
myisamchk to mark the table as
“checked.”
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 out
of memory
errors), or if myisamchk
crashes, go to Stage 3.
Stage 2: Easy safe repair
First, try myisamchk -r -q
tbl_name
(-r
-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
tbl_name
(-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
--safe-recover
tbl_name
. Safe recovery
mode uses an old recovery method that handles a few cases
that regular recovery mode does not (but is slower).
Note: If you want a repair operation to go much faster, you
should set the values of the sort_buffer_size
and key_buffer_size
variables each to about
25% of your available memory when running
myisamchk.
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:
shell>mysql
mysql>db_name
SET AUTOCOMMIT=1;
mysql>TRUNCATE TABLE
mysql>tbl_name
;quit
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.)
Go back to Stage 2. myisamchk -r -q should work. (This should not be an endless loop.)
You can also use the REPAIR TABLE
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 tbl_name
USE_FRMREPAIR TABLE
. See
Section 12.5.2.6, “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 myisamchk -r.
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.
To coalesce fragmented rows and eliminate wasted space that results from deleting or updating rows, run myisamchk in recovery mode:
shell> myisamchk -r tbl_name
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 OPTIMIZE TABLE
. See
Section 12.5.2.5, “OPTIMIZE TABLE
Syntax”.
myisamchk has a number of other options that you can use to improve the performance of a table:
--analyze
, -a
--sort-index
, -S
--sort-records=
,
index_num
-R
index_num
For a full description of all available options, see Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.
To obtain a description of a table or statistics about it, use the commands shown here. We explain some of the information in more detail later.
myisamchk -d
tbl_name
Runs myisamchk in “describe mode” to produce a description of your table. If you start the MySQL server with external locking disabled, myisamchk may report an error for a table that is updated while it runs. However, because myisamchk does not change the table in describe mode, there is no risk of destroying data.
myisamchk -d -v
tbl_name
Adding -v
runs myisamchk
in verbose mode so that it produces more information about
what it is doing.
myisamchk -eis
tbl_name
Shows only the most important information from a table. This operation is slow because it must read the entire table.
myisamchk -eiv
tbl_name
This is like -eis
, but tells you what is
being done.
The tbl_name
argument can be either
the name of a MyISAM
table or the name of its
index file, as described in Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.
Multiple tbl_name
arguments can be
given.
Sample output for some of these commands follows. They are based on a table with these data and index file sizes:
-rw-rw-r-- 1 monty tcx 317235748 Jan 12 17:30 company.MYD -rw-rw-r-- 1 davida tcx 96482304 Jan 12 18:35 company.MYI
Example of myisamchk -d output:
MyISAM file: company.MYI Record format: Fixed length Data records: 1403698 Deleted blocks: 0 Recordlength: 226 table description: Key Start Len Index Type 1 2 8 unique double 2 15 10 multip. text packed stripped 3 219 8 multip. double 4 63 10 multip. text packed stripped 5 167 2 multip. unsigned short 6 177 4 multip. unsigned long 7 155 4 multip. text 8 138 4 multip. unsigned long 9 177 4 multip. unsigned long 193 1 text
Example of myisamchk -d -v output:
MyISAM file: company Record format: Fixed length File-version: 1 Creation time: 1999-10-30 12:12:51 Recover time: 1999-10-31 19:13:01 Status: checked Data records: 1403698 Deleted blocks: 0 Datafile parts: 1403698 Deleted data: 0 Datafile pointer (bytes): 3 Keyfile pointer (bytes): 3 Max datafile length: 3791650815 Max keyfile length: 4294967294 Recordlength: 226 table description: Key Start Len Index Type Rec/key Root Blocksize 1 2 8 unique double 1 15845376 1024 2 15 10 multip. text packed stripped 2 25062400 1024 3 219 8 multip. double 73 40907776 1024 4 63 10 multip. text packed stripped 5 48097280 1024 5 167 2 multip. unsigned short 4840 55200768 1024 6 177 4 multip. unsigned long 1346 65145856 1024 7 155 4 multip. text 4995 75090944 1024 8 138 4 multip. unsigned long 87 85036032 1024 9 177 4 multip. unsigned long 178 96481280 1024 193 1 text
Example of myisamchk -eis output:
Checking MyISAM file: company Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 4 Key: 2: Keyblocks used: 98% Packed: 50% Max levels: 4 Key: 3: Keyblocks used: 97% Packed: 0% Max levels: 4 Key: 4: Keyblocks used: 99% Packed: 60% Max levels: 3 Key: 5: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 6: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 7: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 8: Keyblocks used: 99% Packed: 0% Max levels: 3 Key: 9: Keyblocks used: 98% Packed: 0% Max levels: 4 Total: Keyblocks used: 98% Packed: 17% Records: 1403698 M.recordlength: 226 Packed: 0% Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 Record blocks: 1403698 Delete blocks: 0 Recorddata: 317235748 Deleted data: 0 Lost space: 0 Linkdata: 0 User time 1626.51, System time 232.36 Maximum resident set size 0, Integral resident set size 0 Non physical pagefaults 0, Physical pagefaults 627, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 639, Involuntary context switches 28966
Example of myisamchk -eiv output:
Checking MyISAM file: company
Data records: 1403698 Deleted blocks: 0
- check file-size
- check delete-chain
block_size 1024:
index 1:
index 2:
index 3:
index 4:
index 5:
index 6:
index 7:
index 8:
index 9:
No recordlinks
- check index reference
- check data record references index: 1
Key: 1: Keyblocks used: 97% Packed: 0% Max levels: 4
- check data record references index: 2
Key: 2: Keyblocks used: 98% Packed: 50% Max levels: 4
- check data record references index: 3
Key: 3: Keyblocks used: 97% Packed: 0% Max levels: 4
- check data record references index: 4
Key: 4: Keyblocks used: 99% Packed: 60% Max levels: 3
- check data record references index: 5
Key: 5: Keyblocks used: 99% Packed: 0% Max levels: 3
- check data record references index: 6
Key: 6: Keyblocks used: 99% Packed: 0% Max levels: 3
- check data record references index: 7
Key: 7: Keyblocks used: 99% Packed: 0% Max levels: 3
- check data record references index: 8
Key: 8: Keyblocks used: 99% Packed: 0% Max levels: 3
- check data record references index: 9
Key: 9: Keyblocks used: 98% Packed: 0% Max levels: 4
Total: Keyblocks used: 9% Packed: 17%
- check records and index references
*** LOTS OF ROW NUMBERS DELETED ***
Records: 1403698 M.recordlength: 226 Packed: 0%
Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00
Record blocks: 1403698 Delete blocks: 0
Recorddata: 317235748 Deleted data: 0
Lost space: 0 Linkdata: 0
User time 1639.63, System time 251.61
Maximum resident set size 0, Integral resident set size 0
Non physical pagefaults 0, Physical pagefaults 10580, Swaps 0
Blocks in 4 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 10604, Involuntary context switches 122798
Explanations for the types of information myisamchk produces are given here. “Keyfile” refers to the index file. “Record” and “row” are synonymous.
MyISAM file
Name of the MyISAM
(index) file.
File-version
Version of MyISAM
format. Currently
always 2.
Creation time
When the data file was created.
Recover time
When the index/data file was last reconstructed.
Data records
How many rows are in the table.
Deleted blocks
How many deleted blocks still have reserved space. You can optimize your table to minimize this space. See Section 6.4.4, “Table Optimization”.
Datafile parts
For dynamic-row format, this indicates how many data blocks
there are. For an optimized table without fragmented rows,
this is the same as Data records
.
Deleted data
How many bytes of unreclaimed deleted data there are. You can optimize your table to minimize this space. See Section 6.4.4, “Table Optimization”.
Datafile pointer
The size of the data file pointer, in bytes. It is usually 2, 3, 4, or 5 bytes. Most tables manage with 2 bytes, but this cannot be controlled from MySQL yet. For fixed tables, this is a row address. For dynamic tables, this is a byte address.
Keyfile pointer
The size of the index file pointer, in bytes. It is usually 1, 2, or 3 bytes. Most tables manage with 2 bytes, but this is calculated automatically by MySQL. It is always a block address.
Max datafile length
How long the table data file can become, in bytes.
Max keyfile length
How long the table index file can become, in bytes.
Recordlength
How much space each row takes, in bytes.
Record format
The format used to store table rows. The preceding examples
use Fixed length
. Other possible values
are Compressed
and
Packed
.
table description
A list of all keys in the table. For each key, myisamchk displays some low-level information:
Key
This key's number.
Start
Where in the row this portion of the index starts.
Len
How long this portion of the index is. For packed numbers, this should always be the full length of the column. For strings, it may be shorter than the full length of the indexed column, because you can index a prefix of a string column.
Index
Whether a key value can exist multiple times in the
index. Possible values are unique
or
multip.
(multiple).
Type
What data type this portion of the index has. This is a
MyISAM
data type with the possible
values packed
,
stripped
, or
empty
.
Root
Address of the root index block.
Blocksize
The size of each index block. By default this is 1024, but the value may be changed at compile time when MySQL is built from source.
Rec/key
This is a statistical value used by the optimizer. It tells how many rows there are per value for this index. A unique index always has a value of 1. This may be updated after a table is loaded (or greatly changed) with myisamchk -a. If this is not updated at all, a default value of 30 is given.
For the table shown in the examples, there are two
table description
lines for the ninth
index. This indicates that it is a multiple-part index with
two parts.
Keyblocks used
What percentage of the keyblocks are used. When a table has just been reorganized with myisamchk, as for the table in the examples, the values are very high (very near the theoretical maximum).
Packed
MySQL tries to pack key values that have a common suffix.
This can only be used for indexes on CHAR
and VARCHAR
columns. For long indexed
strings that have similar leftmost parts, this can
significantly reduce the space used. In the third of the
preceding examples, the fourth key is 10 characters long and
a 60% reduction in space is achieved.
Max levels
How deep the B-tree for this key is. Large tables with long key values get high values.
Records
How many rows are in the table.
M.recordlength
The average row length. This is the exact row length for tables with fixed-length rows, because all rows have the same length.
Packed
MySQL strips spaces from the end of strings. The
Packed
value indicates the percentage of
savings achieved by doing this.
Recordspace used
What percentage of the data file is used.
Empty space
What percentage of the data file is unused.
Blocks/Record
Average number of blocks per row (that is, how many links a fragmented row is composed of). This is always 1.0 for fixed-format tables. This value should stay as close to 1.0 as possible. If it gets too large, you can reorganize the table. See Section 6.4.4, “Table Optimization”.
Recordblocks
How many blocks (links) are used. For fixed-format tables, this is the same as the number of rows.
Deleteblocks
How many blocks (links) are deleted.
Recorddata
How many bytes in the data file are used.
Deleted data
How many bytes in the data file are deleted (unused).
Lost space
If a row is updated to a shorter length, some space is lost. This is the sum of all such losses, in bytes.
Linkdata
When the dynamic table format is used, row fragments are
linked with pointers (4 to 7 bytes each).
Linkdata
is the sum of the amount of
storage used by all such pointers.
If a table has been compressed with myisampack, myisamchk -d prints additional information about each table column. See Section 4.6.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”, for an example of this information and a description of what it means.
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 MyISAM
tables is with the
CHECK TABLE
and REPAIR
TABLE
statements. See Section 12.5.2.3, “CHECK TABLE
Syntax”,
and Section 12.5.2.6, “REPAIR TABLE
Syntax”.
Another way to check tables is to use
myisamchk. For maintenance purposes, you can
use myisamchk -s. The -s
option (short for --silent
) 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 check MyISAM
tables
automatically, start the server with the
--myisam-recover
option. See
Section 5.1.2, “Command Options”.
You should also check your tables regularly during normal system
operation. At MySQL AB, we run a cron job to
check all our important tables once a week, using a line like
this in a crontab
file:
35 0 * * 0/path/to/myisamchk
--fast --silent/path/to/datadir
/*/*.MYI
This prints out information about crashed tables so that we can examine and repair them when needed.
Because we have not had any unexpectedly crashed tables (tables that become corrupted for reasons other than hardware trouble) for several years, once a week is more than sufficient for us.
We recommend that to start with, you execute myisamchk -s each night on all tables that have been updated during the last 24 hours, until you come to trust MySQL as much as we do.
Normally, MySQL tables need little maintenance. If you are
performing many updates to MyISAM
tables with
dynamic-sized rows (tables with VARCHAR
,
BLOB
, or 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
stopped:
shell> myisamchk -r -s --sort-index --sort_buffer_size=16M */*.MYI