What and how to use MYISAMCHECK and MYSQLCHECK

Posted on August 4th, 2016

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

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

 

2) Checking the corrupted tables.

myisamchk ~ mysql/eximstats/error1.MYI

Checking 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

– check file-size

myisamchk: warning: Size of index file is: 87874560 should be: 2048

– check record delete-chain

– check key delete-chain

– check index reference

– check 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 switch “-r” or “-o”

 

3) Perform check and repair together for 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 check and repair together for 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

 

Various switches used in myisamcheck

Global options:

-s, –silent Only print errors. One can use two -s to make myisamchk very silent.

-v, –verbose Print more information. This can be used with –description and –check. Use many -v for more verbosity.

-V, –version Print 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 Check the table VERY throughly. Only use this in extreme cases as myisamchk should normally be able to find out if the table is ok even without this switch.

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

-C, –check-only-changed Check only tables that have changed since 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 Print statistics information about table that is checked.

-m, –medium-check Faster than extend-check, but only finds 99.99% of all errors. Should be good enough for most cases.

-U –update-state Mark tables as crashed if you find any errors.

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

 

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

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

–correct-checksum Correct checksum information for table.

-e, –extend-check Try to recover every possible row from the data file. Normally this will also find a lot of garbage rows; Don’t use this option if you are not totally desperate.

-f, –force Overwrite old temporary files.

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

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

 

1) Checking specific table in a Database.

 mysqlcheck -c db1 table1 -u root -p

Enter password:

db1.table1                   OK

If the login credentials are false, then the following message occurs

mysqlcheck: Got error: 1045: Access denied for user ‘root’@’localhost’ (using password: NO) when trying to connect

 

2) Checking all tables in a Databases.

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 the tables and all the databases use “–all-databases” along with -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 of few databases, we need to specify the database names using “-databases”. The following example checks all the tables in db1 and db2 database.

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 table1 table that is located in db1 database.

 

mysqlcheck -a db1 table1 -u root -p

Enter password:

db1.table1   Table is already up to date

 

In background mysqlcheck command uses “Analyze table” command. When mysqlcheck executes the tables will be locked and will be available for other process in readable mode only.

 

5) Optimize Tables using mysqlcheck.

The following optimizes table1 table that is located in db1 database.

mysqlcheck -o db1 table1 -u root -p

Enter password:

db1.table1         OK

In background mysqlcheck command uses “Optimize table” command. This command is used for optimizing the table to use the unused space helping to improve the performance.

 

6) Repair Tables using mysqlcheck

The following repairs table1 table that is located in db1 database.

mysqlcheck -r db1 table1 -u root -p

Enter password:

db1.table1        OK

 

While executing the mysqlcheck the command uses “REPAIR TABLE” command, which fixes and repair myisam and archive tables.

 

If you need any further assistance please reach our support department.

 

 

Leave a Reply