Home arrow MySQL arrow Page 6 - Disaster Prevention and Recovery with the MySQL Database

4.6.3 Setting Up a Table Maintenance Schedule - MySQL

If you need to administer MySQL, this article gets you off to a good start. In this section, we discuss the issues of disaster prevention and recovery. The fifth of a multi-part series, it is excerpted from chapter four of the book MySQL Administrator's Guide, written by Paul Dubois (Sams; ISBN: 0672326345).

  1. Disaster Prevention and Recovery with the MySQL Database
  2. General Options for myisamchk
  3. Repair Options for myisamchk
  4. Using myisamchk for Crash Recovery
  5. How to Repair Tables
  6. 4.6.3 Setting Up a Table Maintenance Schedule
  7. 4.6.4 Getting Information About a Table
By: Sams Publishing
Rating: starstarstarstarstar / 7
June 22, 2006

print this article



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. These are available starting with MySQL 3.23.16.

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's also a good idea to check tables when the server starts. For example, whenever the machine has done a restart in the middle of an update, you usually need to check all the tables that could have been affected. (These are "expected crashed tables.") To check MyISAM tables automatically, start the server with the --myisam-recover option, available as of MySQL 3.23.25. If your server is too old to support this option, you could add a test to mysqld_safe that runs myisamchk to check all tables that have been modified during the last 24 hours if there is an old .pid (process ID) file left after a restart. (The .pid file is created by mysqld when it starts and removed when it terminates normally. The presence of a .pid file at system startup time indicates that mysqld terminated abnormally.)

An even better test would be to check any table whose last-modified time is more recent than that of the .pid file.

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 haven't had any unexpectedly crashed tables (tables that become corrupted for reasons other than hardware trouble) for a couple of years now (this is really true), once a week is more than enough 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 changing MyISAM tables with dynamic size 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 (once a month?).

You can do this by using OPTIMIZE TABLE on the tables in question. Or, 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 -O
sort_buffer_size=16M */*.MYI

For ISAM tables, the command is similar:

shell> isamchk -r -s --sort-index -O
sort_buffer_size=16M */*.ISM

>>> More MySQL Articles          >>> More By Sams Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: