Chapter 6. Backup and Recovery

Table of Contents

6.1. Database Backups
6.2. Example Backup and Recovery Strategy
6.2.1. Backup Policy
6.2.2. Using Backups for Recovery
6.2.3. Backup Strategy Summary
6.3. Using MySQL Backup
6.3.1. Quick Guide to MySQL Backup
6.3.2. How MySQL Backup Works
6.3.3. MySQL Backup Status Reporting and Monitoring
6.4. Point-in-Time Recovery
6.4.1. Specifying Times for Recovery
6.4.2. Specifying Positions for Recovery
6.5. Table Maintenance and Crash Recovery
6.5.1. Using myisamchk for Crash Recovery
6.5.2. How to Check MyISAM Tables for Errors
6.5.3. How to Repair Tables
6.5.4. Table Optimization
6.5.5. Getting Information About a Table
6.5.6. Setting Up a Table Maintenance Schedule

It is important to back up your databases in case problems occur so that you can recover your data and be up and running again. MySQL offers a variety of backup strategies from which you can choose to select whatever methods best suit the requirements for your installation.

Briefly summarized, backup concepts with which you should be familiar include the following:

More generally, the following discussion amplifies on the properties of different backup methods.

Additional resources

Resources related to backup or to maintaining data availability include the following:

6.1. Database Backups

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.7.3, “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 'file_name' FROM tbl_name. 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.9, “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.10, “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.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 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.4, “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.10, “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 16, 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.

  1. Restore the original mysqldump backup, or binary backup.

  2. 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.4, “Point-in-Time Recovery”.

Making Backups Using a File System Snapshot

If you are using a Veritas file system, you can make a backup like this:

  1. From a client program, execute FLUSH TABLES WITH READ LOCK.

  2. From another shell, execute mount vxfs snapshot.

  3. From the first client, execute UNLOCK TABLES.

  4. Copy files from the snapshot.

  5. Unmount the snapshot.

6.2. Example Backup and Recovery Strategy

This section discusses a procedure for performing backups that allows you to recover data after several types of crashes:

  • Operating system crash

  • Power failure

  • File system 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 noncommitted 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 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 we must already have made backups. To make sure that is the case, we should design a backup policy.

6.2.1. 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 nonblocking 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 backup acquires a global read lock on all tables (using FLUSH TABLES WITH READ LOCK) at the beginning of the dump. 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 MySQL server may get stalled until those statements finish. After that, the dump becomes lock-free and does not disturb 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

Note

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 BINARY LOGS statement explains what should be verified before deleting the MySQL binary logs. See Section 12.6.1.1, “PURGE BINARY LOGS Syntax”.

6.2.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:

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.

6.2.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 following guidelines:

  • Always run the MySQL server with the --log-bin option, or even --log-bin=log_name, 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).

  • Make periodic full backups, using the mysqldump command shown earlier in Section 6.2.1, “Backup Policy”, that makes an online, nonblocking backup.

  • Make periodic incremental backups by flushing the logs with FLUSH LOGS or mysqladmin flush-logs.

6.3. Using MySQL Backup

MySQL Backup is available as of MySQL 6.0.5. This feature comprises the BACKUP DATABASE and RESTORE statements. They provide a way to make a copy of a database or set of databases at a given point in time, and a way to restore each database to its state as of that time.

A backup operation can include tables for different storage engines and the backup image will still be consistent. That is, you need not care which storage engines you're using. BACKUP DATABASE saves the data in a consistent backup image with respect to its “validity point.

The validity point ties the backup to the binary log. Restoring a backup can be combined with use of the binary log to accomplish point-in-time recovery: If the restore operation is done because data loss has occurred after the backup was made (that is, after the validity point), restored databases can be brought up to the time of data loss by executing the data changes in the binary log between the times when the backup was made and when the data loss occurred.

A goal of the BACKUP DATABASE and RESTORE statements is to enable other database operations to proceed concurrently, to make it unnecessary to take databases offline or prevent clients from accessing them. BACKUP DATABASE must block some operations from occurring (such as dropping tables from a database while it is being backed up), but the attempt is made to keep blocking to a minimum. Generally, blocked operations are those involving Data Definition Language (DDL) statements. RESTORE must do more blocking because it writes database contents rather than just reading them.

The following discussion covers these aspects of BACKUP DATABASE and RESTORE:

  • Quick guide to making backups and restoring them

  • How BACKUP DATABASE and RESTORE work

  • Status reporting and monitoring for backup and restore operations

For additional information about the BACKUP DATABASE and RESTORE statements, see these sections of the manual:

6.3.1. Quick Guide to MySQL Backup

Use the BACKUP DATABASE and RESTORE statements like this:

  • BACKUP DATABASE backs up one or more databases to a named file:

    BACKUP DATABASE world TO '/tmp/mybackupfile';
    

    To back up more than one database, separate the names by commas:

    BACKUP DATABASE world, sakila TO '/tmp/mybackupfile';
    

    To select all databases for backup, use the * selector as a shortcut:

    BACKUP DATABASE * TO '/tmp/mybackupfile';
    
  • RESTORE restores databases using the contents of the backup file:

    RESTORE FROM '/tmp/mybackupfile';
    

BACKUP DATABASE backs up database and table definitions, table data, stored routines, triggers, events, and views. TEMPORARY tables are not included. Tablespace backup support is limited to the Falcon storage engine.

Prior to MySQL 6.0.7, BACKUP DATABASE did not save any privileges in the backup image file and RESTORE did not restore privileges. As of MySQL 6.0.7, privileges are saved and restored according to these rules:

  • BACKUP DATABASE saves privileges for the backed-up databases in the backup image file. The privileges are stored in the form of GRANT statements.

  • Only privileges are the database level or below (table, column, routine) are saved. Global privileges are not saved because they are not specific to the databases included in the backup.

  • Privileges that specify the database name using a pattern (containing the '%' or '_' wildcard character) are not saved because they might apply to databases not included in the backup.

  • For restore operations, only those privileges are restore that pertain to accounts that exist on the MySQL server performing the restore. Other privileges are ignored with a warning. (These warnings can be displayed with SHOW WARNINGS.) Suppose that a backup contains this GRANT statement:

    GRANT SELECT, INSERT ON db1.* to 'someuser'@'localhost'
    

    The privileges specified by this statement will be restored if the 'someuser'@'localhost' account exists, and ignored with a warning otherwise.

    Restoration of privileges for accounts that do not exist is not done because that would implicitly create accounts that have no password, which is a security risk.

Storage of GRANT statement in backup image files has a security implication: Backup images should be stored in a secure location so that unauthorized users cannot modify the GRANT statements contained therein to change the privileges granted by restore operations.

For anything else not explicitly listed, assume that it is not backed up. This includes but is not limited to items such as UDF definitions and files, logs, and option files.

BACKUP DATABASE currently does not back up the contents of the mysql database. This database contains the grant tables that define user accounts and their privileges, as well as other system information. To make a full server instance backup that includes account information in addition to data, use the BACKUP DATABASE statement together with the mysqldump program. In the following instructions, path represents the full path name to the directory where you store your backup files.

  1. Use mysqldump to back up the mysql database. This is a blocking operation that prevents changes to the database during the dump, but the mysql database normally is relatively small and can be dumped quickly:

    shell> mysqldump --databases mysql > path/mysql-db.sql
    
  2. Use BACKUP DATABASE to back up the data from other databases. This is a nonblocking operation:

    mysql> BACKUP DATABASE * TO 'path/other-dbs.bak';
    

Restore the server instance later like this:

  1. To restore the user accounts, reload the mysql database dump file using the mysql client:

    shell> mysql -u root -p < path/mysql-db.sql
    
  2. To restore the data for other databases, use RESTORE with the image file produced by BACKUP DATABASE:

    mysql> RESTORE FROM 'path/other-dbs.bak';
    

For more information about the operation of the BACKUP DATABASE and RESTORE statements, see Section 12.5.3.1, “BACKUP DATABASE Syntax”, and Section 12.5.3.3, “RESTORE Syntax”.

6.3.2. How MySQL Backup Works

A backup operation creates a backup of one or more databases at a given point in time and saves it as a backup image, a file that contains the backup data (table contents) and metadata (definitions for databases, tables, and other objects, and server information).

The backup is intended to provide a consistent snapshot of the backed-up data as of the point at which the operation began, and it is intended to provide online operation as much as possible that allows other server activity to proceed without blocking.

A backup operation begins at time t1 and ends at time t2, producing a backup image that contains the backup state (database state) at time t, where t1 < t < t2. The time t is called the validity point of the backup image. It represents the time when all storage engines are synchronized for the backup. Restoring this image restores the state to be the same as it was at time t.

Consistency of the backup means that these constraints must be true:

  • Data from transactional tables is included only for committed transactions.

  • Data from nontransactional tables is included only for completed statements.

  • Referential integrity is maintained between all backed-up tables within a given backup image.

The referential-integrity constraint does not necessarily hold if two tables are related but only one of them is included in a backup. Restoring the backup then would restore only the backed-up table, which can produce tables for which referential integrity no longer holds.

For a backup to proceed properly, certain types of server activity must be blocked, so the backup system incorporates a commit blocker and a Backup Metadata Lock.

The commit blocker has these properties:

  • Changes for nontransactional tables must be blocked.

  • Changes for transactional tables are not blocked, but only changes that have been committed when the backup occurs appear in the backup. Changes that occur during the backup operation are not included in the backup image.

When a backup or restore operation is in progress, it is not allowable to modify the structure of database objects. Consequently, during the operation, the Backup Metadata Lock blocks statements that change database metadata from executing. A backup image stores metadata for the following types of objects:

  • Databases

  • Tablespaces

  • Privileges

  • Tables

  • Views

  • Stored programs (functions, procedures, events, triggers)

This requires that the following metadata changes be frozen during backup operation:

  • Databases being backed up should not disappear or be changed.

  • BACKUP DATABASE * ..., new databases should not appear.

  • The list of objects inside each database should not change.

  • Metadata for objects in the databases should not change.

  • The set of privileges for each database should not change.

  • Users for which privileges are stored should not disappear or change.

  • Tablespaces used by tables being backed up should not disappear or change.

To achieve these requirements, the Backup Metadata Lock blocks the following statements:

DROP   DATABASE/TABLE/VIEW/FUNCTION/PROCEDURE/EVENT/TRIGGER/INDEX/
       USER/TABLESPACE
CREATE DATABASE/TABLE/VIEW/FUNCTION/PROCEDURE/EVENT/TRIGGER/INDEX
ALTER  DATABASE/TABLE/VIEW/FUNCTION/PROCEDURE/EVENT/TABLESPACE
RENAME TABLE/USER
GRANT/REVOKE
TRUNCATE/OPTIMIZE/REPAIR TABLE

Currently, all instances of statements that change metadata are blocked, even for database or table objects that are not included in the backup. Eventually, the goal is to block only metadata-changing statements for objects in the backup.

Blocking works in both directions. A backup or restore blocks DDL statements, but if a backup or restore operation is initiated while DDL statements are in progress, the operation waits until the statements have finished.

Implementation of BACKUP DATABASE and RESTORE uses an architecture with the following design:

  • The MySQL server communicates with the backup kernel.

  • The backup kernel is responsible for communicating with backup engines and for handling metadata (definitions for databases, tables, and other objects, as well as server information).

  • Each backup engine provides backup and restore drivers for the backup kernel to use.

  • An engine's backup and restore drivers perform actual transfer of data (table contents).

The backup system chooses from among the backup engines available to it:

  • There is a default backup engine to be used if a better one is not found. This engine provides default backup and restore drivers that use a blocking algorithm. For example, the backup driver locks all tables at the start of the backup and unlocks them after the last one is processed (which may occur before the operation is complete).

  • A consistent-snapshot engine implements the same kind of backup as that made by mysqldump --single-transaction.

    The backup driver for the snapshot engine works with only those storage engines that support consistent read via the handler interface, which currently includes only InnoDB and Falcon. The backup driver creates a logical backup because it reads rows one at a time and returns them to the backup kernel to be stored in the backup image.

A backup image must have contents that are consistent with the binary log coordinates taken from the time of the backup. Otherwise, point-in-time recovery using the backup image plus the binary log contents will not work correctly. BACKUP DATABASE synchronizes with binary logging to make sure that the backup image and binary log are consistent with each other. This way, if data loss occurs later, use of the backup image combined with the binary log makes point-in-time recovery possible:

  1. Restore the backup image

  2. Re-execute binary log contents beginning from the coordinates of the backup's validity point up to the desired point of recovery

6.3.3. MySQL Backup Status Reporting and Monitoring

MySQL provides information about the status or progress of BACKUP DATABASE or RESTORE operations in the following ways:

  • SHOW PROCESSLIST displays information while a thread performing a backup or restore is executing.

  • Upon successful completion, the BACKUP DATABASE and RESTORE statements return a result set with the backup number. (This number is the ID for the corresponding row or rows in the metadata tables described later.) Warnings produced during the operation can be displayed with SHOW WARNINGS.

    If errors occur during a backup or restore operation, they are written to the error log, recorded in the progress tables, and are available via the SHOW ERRORS and SHOW WARNINGS statements.

    If a fatal error occurs, the BACKUP DATABASE or RESTORE statement reports it to the user.

  • The server maintains backup_history and backup_progress tables in the mysql database that contain metadata indicating backup status and progress. It is also possible to write log information to files. For information about selecting log destinations, see Section 6.3.3.1, “MySQL Backup Log Control”. For a description of what is logged, see Section 6.3.3.2, “MySQL Backup Log Contents”.

    If you upgrade to MySQL 6.0.5 or later from an older version, be sure to run mysql_upgrade to ensure that the backup log tables exist. From MySQL 6.0.5 through 6.0.7, these tables were named online_backup and online_backup_progress.

    Currently, there are no INFORMATION_SCHEMA tables corresponding to the backup_history and backup_progress tables.

6.3.3.1. MySQL Backup Log Control

MySQL Backup provides status and progress logging. This capability can be enabled or disabled. If logging is enabled, tables in the mysql database or log files can be used as the destinations for log output. These features are similar to those provided for the general query log and slow query log (see Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”), although the options and variables are different.

This section describes how to control MySQL Backup logging. For a description of what is logged, see Section 6.3.3.2, “MySQL Backup Log Contents”.

Note

The features described here are available as of MySQL 6.0.8. Before 6.0.8, MySQL Backup logs to the online_backup and online_backup_progress tables in the mysql database. Logging to files is not supported and logging cannot be disabled.

Log control at server startup. The --log-backup-output option specifies the destination for log output, if logging is enabled, but the option does not in itself enable the logs. The syntax for this option is --log-backup-output[=value,...]:

  • If --log-backup-output is given with a value, the value can be a comma-separated list of one or more of the words TABLE (log to tables), FILE (log to files), or NONE (do not log to tables or files). NONE, if present, takes precedence over any other specifiers.

  • If --log-backup-output is omitted or given without a value, the default is TABLE.

The --backup_history_log and --backup_progress_log options, if given, enable logging to the history and progress logs for the selected log destinations. (By default, both logs are enabled.) These options take an optional argument of 1 or 0 to enable or disable the log. If either log is enabled, the server opens the corresponding log file and writes startup messages to it. However, logging to the file does not occur unless the FILE log destination is selected.

Examples:

  • With no logging arguments, MySQL Backup logs to the log tables by default.

  • To write log entries to the log tables and log files, use --log-backup-output=TABLE,FILE to select both log destinations.

Log control at runtime. Several system variables are associated with log tables and files and enable runtime control over logging:

  • The global log_backup_output system variable indicates the current logging destination. It can be modified at runtime to change the destination.

  • The global backup_history_log and backup_progress_log variables indicate whether the history and progress logs are enabled (ON) or disabled (OFF). You can set these variables at runtime to control whether the logs are enabled.

  • The global backup_history_log_file and backup_progress_log_file variables indicate the names of the history and progress log files. You can set these variables at server startup or at runtime to change the names of the log files.

6.3.3.2. MySQL Backup Log Contents

If you enable backup logging to tables, MySQL Backup uses the backup_history and backup_progress tables in the mysql database. For logging to files, MySQL Backup uses the backup_history.log and backup_progress.log files in the MySQL data directory by default. The log file names can be changed by setting the global backup_history_log_file and backup_progress_log_file system variables. For information about selecting log destinations, see Section 6.3.3.1, “MySQL Backup Log Control”.

The contents of the log tables are discussed following. For logging to files, the server writes lines with a field for each column in the corresponding log table. The server also writes an initial line to the file at startup to indicate the names of the fields. Backup log contents can be culled with the PURGE BACKUP LOGS statement. See Section 12.5.3.2, “PURGE BACKUP LOGS Syntax”.

If the table destination is selected for backup logging, the server uses these tables:

  • The backup_history table contains a row for each backup and restore operation. A row is created when an operation completes. The rows in this table serve as a history of all backup and restore operations performed on the server. The table can be queried to obtained detailed information about the operations or as a means to create a summary of the operations. The rows are not removed from the table by the server. Any table maintenance, such as removing old rows, is intended to be performed by the database administrator.

  • The backup_progress table contains progress data describing the steps in the most recent backup or restore operation. There may be multiple rows for the operation. Rows are added to this table over the course of the operation and are not updated. This enables the table to be used to track the current progress of the operation. Each row in the table represents a step in the operation and may contain informational statements, errors, and other pertinent information. The data in this table has a limited lifetime. At the start of each operation, the table is truncated and new data is added. The database administrator should not need to perform maintenance for this data.

The backup_history table has this structure:

CREATE TABLE backup_history (
    backup_id           BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    process_id          INT UNSIGNED NOT NULL,
    binlog_pos          INT UNSIGNED DEFAULT 0,
    binlog_file         CHAR(64),
    backup_state        ENUM('complete', 'starting', 'validity point',
                             'running', 'error', 'cancel') NOT NULL,
    operation           ENUM('backup', 'restore') NOT NULL,
    error_num           INT NOT NULL DEFAULT 0,
    num_objects         INT UNSIGNED NOT NULL DEFAULT 0,
    total_bytes         BIGINT UNSIGNED,
    validity_point_time DATETIME,
    start_time          DATETIME,
    stop_time           DATETIME,
    host_or_server_name CHAR (30),
    username            CHAR (30),
    backup_file         CHAR (100),
    backup_file_path    VARCHAR (512),
    user_comment        VARCHAR (200),
    command             VARCHAR (512),
    engines             VARCHAR (100),
) ENGINE=CSV CHARSET=utf8;

The backup_history columns are used as follows:

  • backup_id

    The ID for the table row. BACKUP DATABASE and RESTORE return a result set containing a backup ID, which is the value that tells you which row in the backup_history table corresponds to the backup or restore operation.

  • process_id

    The process ID that the operation ran as.

  • binlog_pos, binlog_file

    For a backup, the binary log position and file name at the time the validity point is generated (the time when all storage engines are synchronized). Before that time, the values are 0 and NULL.

  • backup_state

    The status of the operation.

  • operation

    The type of operation.

  • error_num

    The error from this operation (0 = no error).

  • num_objects

    The number of objects in the backup.

  • total_bytes

    The size of the backup image file in bytes.

  • validity_point_time

    For a backup, this is the time that the validity point was generated. Before that time, the value is NULL. For a restore, the value currently is always NULL.

  • start_time, stop_time

    The date and time when the operation started and stopped.

  • host_or_server_name

    The server name where the operation ran.

  • username

    The name of the user who ran the operation.

  • backup_file

    The name of the backup image file. As of MySQL 6.0.8, this column contains the file basename.

  • backup_file_path

    The directory containing the image file. This column was added in MySQL 6.0.8.

  • user_comment

    The comment from the user entered at the command line.

  • command

    The statement used to perform the operation.

  • drivers

    The names of the drivers used in the operation. Before MySQL 6.0.7, this column was named engines.

The backup_progress table has this structure:

CREATE TABLE backup_progress (
    backup_id   BIGINT UNSIGNED NOT NULL
    object      CHAR (30) NOT NULL
    start_time  DATATIME
    stop_time   DATATIME
    total_bytes BIGINT
    progress    BIGINT UNSIGNED
    error_num   INT NOT NULL DEFAULT 0
    notes       CHAR(100)
) ENGINE=CSV CHARSET=utf8;

The backup_progress columns are used as follows:

  • backup_id

    The backup_id value of the backup_history table row with which the rows in the backup_progress table are associated.

  • object

    The object being operated on.

  • start_time, stop_time

    The date and time when the operation started and stopped.

  • total_bytes

    The size of the object in bytes.

  • progress

    The number of bytes processed.

  • error_num

    The error from this operation (0 = no error).

  • notes

    Commentary from the backup engine.

6.4. Point-in-Time Recovery

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.4, “The Binary Log”, and Section 4.6.8, “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 path name 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.8, “mysqlbinlog — Utility for Processing Binary Log Files”.

6.4.1. Specifying Times for Recovery

To indicate the start and end times for recovery, specify the --start-datetime and --stop-datetime 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-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 --stop-datetime 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-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 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.

6.4.2. Specifying Positions for Recovery

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-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 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.

6.5. Table Maintenance and Crash Recovery

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:

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.2, “CHECK TABLE Syntax”, Section 12.5.2.4, “OPTIMIZE TABLE Syntax”, and Section 12.5.2.5, “REPAIR TABLE Syntax”.

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.1.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 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 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 that each MyISAM table tbl_name in a database corresponds to the three files in the database directory shown in the following table.

FilePurpose
tbl_name.frmDefinition (format) file
tbl_name.MYDData file
tbl_name.MYIIndex 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.

6.5.2. How to Check MyISAM Tables for Errors

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.

6.5.3. How to Repair Tables

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.2, “CHECK TABLE Syntax”, and Section 12.5.2.5, “REPAIR TABLE Syntax”.

Symptoms of corrupted tables include queries that abort unexpectedly and observable errors such as these:

  • tbl_name.frm is locked against change

  • Can't find file tbl_name.MYI (Errcode: nnn)

  • 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 TABLE tbl_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.5.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:

  1. Make a backup of the data file before continuing.

  2. 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.

  3. 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:

  1. Move the data file to a safe place.

  2. Use the table description file to create new (empty) data and index files:

    shell> mysql db_name
    mysql> SET autocommit=1;
    mysql> TRUNCATE TABLE tbl_name;
    mysql> quit
    
  3. 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.)

Important

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 REPAIR TABLE tbl_name USE_FRM 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 REPAIR TABLE. See Section 12.5.2.5, “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:

  1. 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.

  2. 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.

6.5.4. Table Optimization

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.4, “OPTIMIZE TABLE Syntax”.

myisamchk has a number of other options that you can use to improve the performance of a table:

For a full description of all available options, see Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.

6.5.5. Getting Information About a Table

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.5.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.5.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 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.5.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.

6.5.6. Setting Up a 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 repair MyISAM tables is with the CHECK TABLE and REPAIR TABLE statements. See Section 12.5.2.2, “CHECK TABLE Syntax”, and Section 12.5.2.5, “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, “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 crontab file:

35 0 * * 0 /path/to/myisamchk --fast --silent /path/to/datadir/*/*.MYI

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 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