How to use MYISAMCHECK and MYSQLCHECK and what they are used for

MYISAMCHECK

The default storage engine of the MySQL database is MyISAM. MyISAM is susceptible to table corruption. When a table is created in MySQL, it generates *.frm files to store the table format, *.MYD (My Data) file to store the data, and *.MYI (My Index) to store the index.

1) Verifying all corrupted tables.

myisamchk /var/lib/mysql/eximstats/*.MYI
myisamchk: error: Incorrect bytesec: 0-0-0 at linkstart: 24569875
MyISAM-table ‘/var/lib/mysql/eximstats/error1.MYI’ is corrupted
Fix it using the “-r” or “-o” switch
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
MyISAM-table ‘profiles.MYI’ is usable but should be fixed

2) Verifying the corrupted tables.

myisamchk ~ mysql/eximstats/error1.MYI
Verifying MyISAM file: /var/lib/mysql/eximstats/error1.MYI
Data records: 445902 Deleted blocks: 0
myisamchk: warning: Table is marked as crashed and last repair failed
– checking file-size
myisamchk: warning: Size of index file is: 87874560 should be: 2048
– checking record delete-chain
– checking key delete-chain
– checking index reference
– checking data record references index: 1
myisamchk: Unknown error 126
myisamchk: error: Can’t read index page from filepos: -1
MyISAM-table ‘/var/lib/mysql/eximstats/error1.MYI’ is corrupted
Fix it using the “-r” or “-o” switch

3) Perform verification and repair together for the entire MySQL database.

myisamchk –silent –force –fast –update-state /var/lib/mysql/eximstats/*.MYI
myisamchk: MyISAM file /var/lib/mysql/eximstats/groups.MYI
myisamchk: warning: 1 client is using or hasn’t closed the table properly
myisamchk: MyISAM file /var/lib/mysql/eximstats/profiles.MYI
myisamchk: warning: 1 client is using or hasn’t closed the table properly

4) Repairing the table.

myisamchk -r ~mysql/eximstats/error1.MYI
-recovering (with sort) MyISAM-table ‘/var/lib/mysql/eximstats/error1.MYI’
Data records: 568742
– Fixing index 1
– Fixing index 2
– Fixing index 3
– Fixing index 4
– Fixing index 5
– Fixing index 6
Data records: 531670

5) Perform verification and repair together for the entire MySQL database

myisamchk –silent –force –fast –update-state /var/lib/mysql/eximstats/*.MYI
myisamchk: MyISAM file /var/lib/mysql/bugs/groups.MYI
myisamchk: warning: 1 client is using or hasn’t closed the table properly
myisamchk: MyISAM file /var/lib/mysql/bugs/profiles.MYI
myisamchk: warning: 1 client is using or hasn’t closed the table properly

Different switches used in myisamcheck

Global options:

-s, –silent Only display errors. Two -s can be used to make myisamchk very silent.

-v, –verbose Display more information. This can be used with –description and –check. Use multiple -v for increased verbosity.

-V, –version Display version and exit.

-w, –wait Wait if table is locked.

Check options (check is the default action for myisamchk):

-c, –check Check table for errors.

-e, –extend-check Thoroughly check the table. Use this option only in extreme cases as myisamchk should usually be able to detect if the table is fine without it.

-F, –fast Check only tables that haven’t been closed properly.

-C, –check-only-changed Check only tables that have changed since the last check.

-f, –force Restart with ‘-r’ if there are any errors in the table. States will be updated as with ‘–update-state’.

-i, –information Display statistics information about the checked table.

-m, –medium-check Faster than extend-check, but may miss 0.01% of all errors. Typically sufficient for most cases.

-U –update-state Mark tables as crashed if errors are found.

-T, –read-only Don’t mark table as checked.

Repair options (When using ‘-r’ or ‘-o’):

-B, –backup Create a backup of the .MYD file as ‘filename-time.BAK’.

–correct-checksum Correct checksum information for the table.

-e, –extend-check Attempt to recover every possible row from the data file. This may also recover garbage rows; use this option only if absolutely necessary.

-f, –force Overwrite old temporary files.

-r, –recover Can repair almost anything except for non-unique keys that aren’t unique.

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

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

-o, –safe-recover Uses the old recovery method; slower than ‘-r’ but can handle certain cases where ‘-r’ reports inability to fix the data file.

-q, –quick Faster repair by not modifying the data file. Using a second ‘-q’ forces myisamchk to modify the original datafile in case of duplicate keys. NOTE: Tables with corrupted data files cannot be repaired with this option.

-u, –unpack Unpack file packed with myisampack.

Other actions:

-a, –analyze Analyze distribution of keys. This can improve performance for certain joins in MySQL. The calculated distribution can be checked using ‘–description –verbose table_name’.

-d, –description Print information about the table.

MYSQLCHECK

Another tool for diagnosing and repairing MySQL tables is mysqlcheck. Unlike myisamchk, this command repairs MySQL tables while the database is still running.

1) Checking a specific table in a database.

mysqlcheck -c db1 table1 -u root -p
Enter password:
db1.table1 OK
If the login credentials are incorrect, the following message will appear:
mysqlcheck: Got error: 1045: Access denied for user ‘root’@’localhost’ (using password: NO) when trying to connect

2) Checking all tables in a database.

mysqlcheck -c db2 -u root -p
Enter password:
db2.JBPM_ACTION                                  OK
db1.JBPM_BYTEARRAY                          OK
db2.JBPM_BYTEBLOCK                        OK
db2.JBPM_COMMENT                             OK
db2.JBPM_DECISIONCONDITIONS           OK
db2.JBPM_DELEGATION                         OK
db2.JBPM_EVENT                                   OK

3) Check All Tables and All Databases.

To check all tables and databases, use the “–all-databases” option along with the -c option as shown.
mysqlcheck -c -u root -p –all-databases
Enter password:
db2.table1 OK
db2.JBPM_ACTION OK
db2.JBPM_BYTEARRAY OK
db2.JBPM_BYTEBLOCK OK

To check specific tables in several databases, specify the database names using “-databases”. The following example checks all tables in the db1 and db2 databases.

mysqlcheck -c -u root -p –databases db1 db2
Enter password:
db1.table1 OK
db2.JBPM_ACTION OK
db2.JBPM_BYTEARRAY OK
db2.JBPM_BYTEBLOCK OK

4) Analyze Tables using mysqlcheck.

The following analyzes the table1 table located in the db1 database.

mysqlcheck -a db1 table1 -u root -p
Enter password:
db1.table1 Table is already up to date

This mysqlcheck command uses the “Analyze table” command in the background. When mysqlcheck executes, the tables will be locked and will only be available for other processes in read-only mode.

5) Optimize Tables using mysqlcheck.

The following optimizes the table1 table located in the db1 database.

mysqlcheck -o db1 table1 -u root -p
Enter password:
db1.table1 OK

This mysqlcheck command uses the “Optimize table” command in the background. This command optimizes the table by reclaiming unused space, which helps improve performance.

6) Repair Tables using mysqlcheck

The following repairs the table1 table located in the db1 database.