August 31, 2007
Recently I was involved with a task to recover a database on MySQL 4.x that crashed. Below are the steps that I did to recover the database. The commands in this post are available on MySQL 4.x, if you are on MySQL version higher than this then check MySQL 5.x
1. The MYI file of the database was corrupted and I could find out that by trying to select rows from one of the tables. The error message returned for the select query – ERROR 1016 (HY000): Can’t open file: ‘TABLE.MYI’ (errno: 144).
2. Next step was to run myisamchk for which I should shut down the database first by issuing command “mysqladmin -u root shutdown” at the command prompt
3. First I tried using the –quick option on the database to rebuild the index only
/pathtomysqlexecutablefiles/myisamchk –quick /pathtodatabase/*.MYI [ to repair all the tables use wildcard (*) ], the command executed successfully, and displayed the repair information.
4. I again started mysql server using command “mysqld &”
5. Logged in to mysql prompt and selected the repaired database. To know the status of the tables after repair I executed “show table status” on the mysql prompt.
6. The results displayed error “Incorrect information in file: ‘./database/TABLE.frm” which means the frm file of the database got corrupted. Searching for the same on the web I found if we run an extended check using myisamchk may solve this problem for which I had to go to step 2.
7. There are few checks one should do if step 3 cannot help you recover the database, and after every check repeat steps 4 – 6 in the same order. You would know your table is recovered when “show table status” shows no errors, and to confirm this further you should be able to select rows from the table.
a) /pathtomysqlexecutablefiles/myisamchk –force –fast -c -i -r /pathtodatabase/*.MYI
b) /pathtomysqlexecutablefiles/myisamchk –force –fast -c -i -o /pathtodatabase/*.MYI
c) /pathtomysqlexecutablefiles/myisamchk –force –fast –update-state -i -r -e /pathtodatabase/*.MYI
if it gives error – myisamchk: error: Not enough memory for blob at 1940 (need 1890777748) (1.7GB thats wierd)
but if you think your table may be holding that much of data and your server has huge memory you may use
d) /pathtomysqlexecutablefiles/myisamchk –fast –update-state -i -o -e -O key_buffer_size=2048M -O read_buffer_size=2048M -O write_buffer_size=2048M /pathtodatabase/*.MYI
Thats giving 2 GB of memory to myisamchk for processing!
If you want to know more about visit : myisamchk Memory Usage
If the error about memory persists, it means there has been serious damage to the database. Same was the case with my database, these are the steps for recovering in such scenario:
a) Recreate a database on local MySQL server.
b) Recreate all the tables with similar schema as it is on the web server
c) Stop local MySQL server
d) Copy frm and MYD to the local computer at the same place where your newly created database exists with its (frm, MYI and, MYD) files.
e) Execute myisamchk –quick /path/to/datadir/*.MYI (One can also use REPAIR TABLE command from mysql prompt)
f) Restart MySQL Server and try selecting rows from the tables.