8.1  Database recovery

Database recovery is the action the database system or user takes to recuperate 

the database in case of system, hardware, software, or application failure. This 

includes precautionary measures and curing measures. Different and multiple 

recovery methods are followed by all the database systems for warding off the 

data lose condition.

8.1.1  MySQL recovery 

MySQL provide two recovery options: the first one is using table maintenance 

feature and the second is using backup and restore utilities. MySQL also 

provides automatic recovery of crashed tables, which can be enabled by running 

mysqld

 with the 

  myisam recover=#

 option. This works only for MyISAM tables 

and internally uses the 

myisamchk

 utility. MySQL recovery options can be done 

by:

Checking and repairing tables for disorder

MySQL provides

myisamchk

 (or 

isamchk

) for checking and repairing MyISAM 

(or ISAM) tables:

bash>myisamchk   silent   force   fast   update state 

/home/itso/mysql/data/*.MYI

bash>isamchk   silent   force /home/itso/mysql/data/*.ISM

If you are using 

mysqld

 with the 

  skip external locking

 option, you cannot 

use 

mysqladmin 

to check the table if it is locked by 

mysqld

. In this case you 

should do 

mysqladmin flush tables

 before checking the table.

Also, you may use the 

check table

 or

 REPAIR TABLE

command for doing this; 

using 

REPAIR TABLE 

is same as using

myisamchk  r

CHECK TABLE

 works only with MyISAM and InnoDB table and the 

REPAIR TABLE

option works with only MyISAM tables.

Using backup and restore 

Another technique the MySQL user can use for crash recovery is by doing 

precautionary database backup frequently. In case of a crash, obtaining a 

stable state of database by

restoring the data from backup and binary logs. 

The steps for doing this are:

  Precautionary frequent database backup by using one of these:

bash> mysqldump   tab=/home/itso/mysql/backup   opt itz`sodb

bash> mysqlhotcopy itsodb /home/itso/mysql/backup

  Restart

 mysqld

 with the 

  log bin

 option; this will write the information 

required to replicate all the changes to the database after backup.

236 

MySQL to DB2 UDB Conversion Guide