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.