Other Services :-

What and how to use MYISAMCHECK and MYSQLCHECK

MYISAMCHECK

The default storage engine of the MySQL database is MyISAM. The myisam is vulnerable to corruption of the table. During the creation of table in MySQL it creates *.frm files to store table format, *.MYD (My Data) file to store the data and *.MYI (My Index) to store the index.

1) Checking all corrupted tables.

myisamchk /var/lib/mysql/eximstats/*.MYI

myisamchk: error: Wrong bytesec: 0-0-0 at linkstart: 24569875

MyISAM-table ‘/var/lib/mysql/eximstats/error1.MYI’ is corrupted

Fix it using switch “-r” or “-o”

myisamchk: warning: 1 client is using or hasn’t closed the table properly

MyISAM-table ‘groups.MYI’ is usable but should be fixed

myisamchk: warning: 1 client is using or hasn’t closed the table properly

-n, –sort-recover Forces recovering with sorting even if the temporary file would be very big.

-p, –parallel-recover Uses the same technique as ‘-r’ and ‘-n’, but creates all the keys in parallel, in different threads.

-o, –safe-recover Uses old recovery method; Slower than ‘-r’ but can handle a couple of cases where ‘-r’ reports that it can’t fix the data file.

-q, –quick Faster repair by not modifying the data file. One can give a second ‘-q’ to force myisamchk to modify the original datafile in case of duplicate keys. NOTE: Tables where the data file is currupted can’t be fixed with this option.

-u, –unpack Unpack file packed with myisampack.

Other actions:

-a, –analyze Analyze distribution of keys. Will make some joins in MySQL faster. You can check the calculated distribution by using ‘–description –verbose table_name’.

-d, –description Prints some information about table.

MYSQLCHECK

Another MySQL table diagnosing and repairing MySQL tables tool is mysqlcheck. Unlike myisamchk this command repairs the MySQL even while the database is running.