Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a corrupt MyISAM table in our production MySQL database. Is there a method for repairing this table without locking it?

We were considering using a method similar to Facebook's Online Schema Change or Percona's pt-online-schema-change. The idea would be to: (1) copy the table, (2) monitor changes to the original using triggers, (3) repair the copy, and (4) finally, swap the repaired table and the original by renaming them.

Will something like this work or is there another recommended technique for repairing a corrupt table in a production database without downtime?

share|improve this question

migrated from stackoverflow.com Dec 1 '12 at 8:13

This question came from our site for professional and enthusiast programmers.

    
Uh I'll better let a real DBA answer this, good luck! ( maybe add some info why you think it us corrupt and how you can be sure it is not the underlying hard disk ) –  Michel Feldheim Nov 30 '12 at 22:11
add comment

2 Answers 2

Realistically, there is no online method for table repait.

There are two techniques to repair mydb.mytable

TECHNIQUE #1 : Repair Online

REPAIR TABLE mydb.mytable;

This will perform the table repair with mysql runnng. This will perform a full table lock so no one can access the table.

TECHNIQUE #2 : Repair Offline

To repair a table offline, move the files making up the table to another folder and perform the repair there. For example, to repair mydb.mytable using the folder /var/lib/mysql

REPAIR_OPTION="-r"
DB_NAME=mydb
TABLE_NAME=mytable
FRM=${TABLE_NAME}.frm
MYD=${TABLE_NAME}.MYD
MYI=${TABLE_NAME}.MYI
cd /var/lib/mysql/${DB_NAME}
mv ${FRM} ..
mv ${MYD} ..
mv ${MYI} ..
cd ..
myisamchk -${REPAIR_OPTION} ${MYD}
myisamchk -${REPAIR_OPTION} ${MYI}
mv ${FRM} /var/lib/mysql/${DB_NAME}
mv ${MYD} /var/lib/mysql/${DB_NAME}
mv ${MYI} /var/lib/mysql/${DB_NAME}

If -r does not work, rerun these lines using REPAIR_OPTION="-o"

EPILOGUE

Neither of these techniques will allow REPAIR TABLE operations while the file is live.

share|improve this answer
add comment

It might work - it depends on the nature of the corrption - but you might need to take another tack. However you've not said what the nature of the corruption is, nor what OS/filesystem this is running on.

IIRC, either of the tools you mention support 'REPAIR TABLE...' syntax. Personally I'd look at hacking OSC since I program in PHP, but if your skills are in Perl, percona might be a better starting point.

You'll need to change the operation on the clone to 'REPAIR TABLE...' then exit before the cut over to check whether the resulting dataset is adequately complete so you can verify how successful the operation will be when you try it again for real.

If you lose data in the process, then you may have more luck taking a filesystem snapshot of the datafile, copying it into a seperate DB installation and running myisamchk against the file image - which means you'll need to roll forard the changes yourself - and in this case it may be simpler to just restore the backup somewhere else (possibly rolling forward the binlogs to bring to closer to the live system) rename the table, export it back into the original DBMS and roll forward the changes thereafter before switching the tables.

share|improve this answer
add comment

protected by Mark Storey-Smith Sep 6 '13 at 23:20

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?