How to Repair a Crashed MySQL Table
Today, I had a server crash when the /var partition filled up while reindexing. I attempted to clear out extra log files and such but df was still showing greater than 100% utilization. Checking /var/db/mysql showed I had one table that was taking up too much space. du -d 1 -h is a very useful command when tracking down the largest folder usage on partition.
Jul 21 07:39:06 plab2catsg01 kernel: pid 36500 (mysqld), uid 88 inumber 329992 o n /var: filesystem full Jul 21 07:39:22 plab2catsg01 kernel: pid 36500 (mysqld), uid 88 inumber 353413 o n /var: filesystem full Jul 21 07:39:23 plab2catsg01 kernel: pid 36500 (mysqld), uid 88 inumber 353288 o n /var: filesystem full
I attempted to stop MySQL by executing /usr/local/etc/rc.d/mysql-server stop and unfortunately, it kept looping while trying to stop the process. I was eventually forced to reboot the server.
On reboot, I thought all was well but then I found the Eventum application I use wasn't showing support emails. I checked the MySQL log files:
plab2catsg01# tail -f plab2catsg01.ten-net.net.err ... 090721 9:15:54 [ERROR] /usr/local/libexec/mysqld: Table './eventum/eventum_mail _queue' is marked as crashed and last (automatic?) repair failed 090721 9:15:54 [ERROR] /usr/local/libexec/mysqld: Table './eventum/eventum_mail _queue' is marked as crashed and last (automatic?) repair failed 090721 9:15:55 [ERROR] /usr/local/libexec/mysqld: Table './eventum/eventum_mail _queue' is marked as crashed and last (automatic?) repair failed 090721 9:15:55 [ERROR] /usr/local/libexec/mysqld: Table './eventum/eventum_mail _queue' is marked as crashed and last (automatic?) repair failed ...
That's not good. Fortunately, if I lose the table it's not critical but this is how you can try to repair a crashed table:
plab2catsg01# myisamchk eventum_mail_queue Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Checking MyISAM file: eventum_mail_queue Data records: 0 Deleted blocks: 0 myisamchk: warning: Table is marked as crashed and last repair failed - check file-size myisamchk: warning: Size of indexfile is: 14201856 Should be: 1024 - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 - check data record references index: 3 - check data record references index: 4 - check record links myisamchk: error: Record-count is not ok; is 489063 Should be: 0 myisamchk: warning: Found 489063 parts Should be: 0 parts MyISAM-table 'eventum_mail_queue' is corrupted Fix it using switch "-r" or "-o"
The myisamchk command wasn't able to repair the table using its default flags so it advised I try the -r or -o options. The -r option is to repair the table which is what I did below:
plab2catsg01# myisamchk -r eventum_mail_queue Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 - recovering (with sort) MyISAM-table 'eventum_mail_queue' Data records: 0 - Fixing index 1 - Fixing index 2 - Fixing index 3 - Fixing index 4 Data records: 489063 You have new mail. plab2catsg01#
If you have a lot of corruption, you can check and repair an entire MySQL database as such:
# myisamchk --silent --force --fast --update-state /var/lib/mysql/eventum/*.MYI myisamchk: MyISAM file /var/lib/mysql/eventum/groups.MYI myisamchk: warning: 1 client is using or hasn't closed the table properly myisamchk: MyISAM file /var/lib/mysql/eventum/profiles.MYI myisamchk: warning: 1 client is using or hasn't closed the table properly
The options are:
- -s, --silent option: Prints only errors. You can use two -s to make myisamchk very silent.
- -f, --force option: Restart myisamchk automatically with repair option -r, if there are any errors in the table.
- -F, --fast option: Check only tables that haven?t been closed properly.
- -U --update-state option: Marks tables as crashed, when it finds any error.
Additional things you can do with myisamcheck include identifying all corrupted ables:
# myisamchk /var/lib/mysql/bugs/*.MYI >> /tmp/myisamchk_log.txt myisamchk: error: Wrong bytesec: 0-0-0 at linkstart: 18361936 MyISAM-table 'attach_data.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
When you redirect the output of myisamchk to a temporary file, it will display only the corrupted table names on the screen. The /tmp/myisamchk_log.txt file will contain information about all the tables including the good ones, as shown below:
Checking MyISAM file: user_group_map.MYI Data records: 182 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1
For large database, it may take several hours to recover the tables. Depending on RAM available on your system, increase the memory parameters as shown below while executing the myisamchk:
# myisamchk --silent --force --fast --update-state \ --key_buffer_size=512M --sort_buffer_size=512M \ --read_buffer_size=4M --write_buffer_size=4M \ /var/lib/mysql/bugs/*.MYI
You can also use myisamchk to get basic information about a table such as:
# myisamchk -dvv albums.MYI MyISAM file: albums.MYI Record format: Packed Character set: latin1_swedish_ci (8) File-version: 1 Creation time: 2012-01-16 13:07:26 Status: open,changed Auto increment key: 1 Last value: 155 Data records: 97 Deleted blocks: 0 Datafile parts: 180 Deleted data: 0 Datafile pointer (bytes): 6 Keyfile pointer (bytes): 6 Datafile length: 10708 Keyfile length: 9216 Max datafile length: 281474976710654 Max keyfile length: 288230376151710719 Recordlength: 1893 table description: Key Start Len Index Type Rec/key Root Blocksize 1 5 4 unique unsigned long 1 2048 1024 2 13 255 multip. varchar prefix 0 7168 2048 Field Start Length Nullpos Nullbit Type 1 1 4 2 5 4 no zeros 3 9 4 1 1 no zeros 4 13 256 varchar 5 269 10 1 2 blob 6 279 10 1 4 blob 7 289 8 1 8 no zeros 8 297 10 1 16 blob 9 307 4 no zeros 10 311 4 no zeros 11 315 4 no zeros 12 319 256 1 32 varchar 13 575 4 1 64 no zeros 14 579 21 1 128 varchar 15 600 21 2 1 varchar 16 621 4 2 2 no zeros 17 625 4 2 4 no zeros 18 629 4 2 8 no zeros 19 633 4 2 16 no zeros 20 637 256 varchar 21 893 10 2 32 blob 22 903 4 2 64 no zeros 23 907 4 2 128 no zeros 24 911 12 3 1 blob 25 923 10 3 2 blob 26 933 4 3 4 no zeros 27 937 10 3 8 blob 28 947 128 3 16 varchar 29 1075 193 3 32 varchar 30 1268 4 no zeros 31 1272 4 3 64 no zeros 32 1276 258 3 128 varchar 33 1534 10 4 1 blob 34 1544 256 4 2 varchar 35 1800 65 4 4 varchar 36 1865 8 4 8 no zeros 37 1873 8 4 16 no zeros 38 1881 8 4 32 no zeros