Sometimes mysql database tables get corrupted for various reasons like server reboot, multiple processes trying to write to same table etc.. If your databses is using MyISAM storage engine ( which is the default one ), you can recover these tables the following way.
MyISAM provides a built-in tool for repairing corrupted tables. The command is myisamchk, which can be used for varied functions like scanning database for errors, tuning tables for performance, repair corrupted tables etc.
There is also an SQL variant for some functionalities of myisamchk, like CHECK TABLE,REPAIR TABLE etc.. While myisamchk is ran from command line, these queries should be ran from sql console.
For me, the issue started when I tried to backup a database with mysqldump
safeer@db2 ~:$sudo /usr/bin/mysqldump oncall > oncall.sql
mysqldump: Got error: 1194: Table 'log' is marked as crashed and should be repaired when using LOCK TABLES
Now this is a sign of problem and to start with troubleshooting, we should first check the table for errors.
Two things before we proceed: as a best practice if possible, we should shut down the mysql server or make sure no one is accessing the tables ( obtaining a lock on the tables ). Another thing to do is, backup the database before repairing its table. In some cases, there is a possibility that while repairing the tables some data might be lost.
For my case the mysql server was not a production server, rather an internal service. So I was able to shut it down. But backing up the databaes using mhysqldump was not an option as you saw above, so I copied out the database's data directory. In my system the database directory is at /var/lib/mysql/oncall.
Now let us start checking the table with myisamchk. myisamchk requires the actual table filename with full path under mysql data directory to work with.
safeer@db2 ~:$sudo myisamchk /var/lib/mysql/oncall/log.MYI
Checking MyISAM file: /var/lib/mysql/oncall/log.MYI
Data records: 116889 Deleted blocks: 0
myisamchk: warning: Table is marked as crashed
myisamchk: warning: 3 clients are using or haven't closed the table properly
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
myisamchk: error: Found key at page 1439744 that points to record outside datafile
- check record links
myisamchk: error: Checksum for key: 1 doesn't match checksum for records
myisamchk: warning: Found 116889 parts Should be: 116890 parts
MyISAM-table '/var/lib/mysql/oncall/log.MYI' is corrupted
Fix it using switch "-r" or "-o"
Now that we know the table is corrupted, let us try fixing it.
safeer@db2 ~:$sudo myisamchk -r /var/lib/mysql/oncall/log.MYI
- recovering (with sort) MyISAM-table '/var/lib/mysql/oncall/log.MYI'
Data records: 116889
- Fixing index 1
Check again to confirm the table is fixed
safeer@db2 ~:$sudo myisamchk /var/lib/mysql/oncall/log.MYI
Checking MyISAM file: /var/lib/mysql/oncall/log.MYI
Data records: 116889 Deleted blocks: 0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links
The table is fixed now.
Now let us see how we can do the same task from mysql console using sql commands. To do this, I am copying the backed up corrupted mysql files to the /var/lib/mysql/oncall location so that the db will be in the corrupted state again (This is the advantage of doing file system level db backup, you can play around it by copying data files back and forth :) ).
Select Database
mysql oncall>use oncall;
Check Table
mysql oncall>check table log;
+------------+-------+----------+-----------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------+-------+----------+-----------------------------------------------------------+
| oncall.log | check | warning | Table is marked as crashed |
| oncall.log | check | warning | 3 clients are using or haven't closed the table properly |
| oncall.log | check | error | Size of datafile is: 22364116 Should be: 22364308 |
| oncall.log | check | error | Corrupt |
+------------+-------+----------+-----------------------------------------------------------+
Repair Table
mysql> repair table log;
+------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+--------+----------+----------+
| oncall.log | repair | status | OK |
+------------+--------+----------+----------+
1 row in set (11.29 sec)
Check Again
mysql> check table log;
+------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+-------+----------+----------+
| oncall.log | check | status | OK |
+------------+-------+----------+----------+
1 row in set (7.44 sec)
If possible always use myisamchk, it has more options that can be used if you run into problems while repairing tables. A few options worth remembering are:
For checking:
-F, --fast Check only tables that haven't been closed properly.
-m, --medium-check Faster than extend-check, but only finds 99.99% of
all errors. Should be good enough for most cases.
-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.
For repairing:
-r, --recover Can fix almost anything except unique keys that aren't
unique.
-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.
For more options, checkout the man page and following mysql website links
MYISAMCHK
CHECK TABLES
No comments:
Post a Comment