MySQL: Repairing broken tables/indices
I part time administrate a football forum that I used to run when I was at university and one problem we had recently was that some of the tables/indices had got corrupted when MySQL crashed due to a lack of disc space.
We weren't seeing any visible sign of a problem in any of the logs but whenever you tried to query one of the topics it wasn't returning any posts.
I eventually came across a useful article which explained how to check whether some of the tables in a MySQL database had been corrupted and how to fix them.
I first shutdown the database using the following command:
And then I ran this command to check on the status of each of the tables:
for path in `ls /var/lib/mysql/forum/*.MYI`; do echo $path; myisamchk $path; done
This gave an output like the following for each table:
Checking MyISAM file: /var/lib/mysql/forum/forum.MYI Data records: 217 Deleted blocks: 4 myisamchk: warning: 1 client is using or hasn'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 - check record links MyISAM-table '/var/lib/mysql/forum/forum.MYI' is usable but should be fixed
If you pass the '--recover' flag to myisamchk it will attempt to fix any problems it finds. I therefore ran the following command:
for path in `ls /var/lib/mysql/forum/*.MYI`; do echo $path; myisamchk --recover $path; done
After I'd run that it seemed to fix most of the problems we'd been experiencing. There are still a couple of edge cases left but at least the majority of the forum is now in a usable state.
I think we could just as easily run myisamchk by passing a wildcard selection of files for it to run against but I didn't realise that until afterwards!
The following would therefore work just as well:~~~
myisamchk --recover /var/lib/mysql/forum/*.MYI