Mysql – Repair MySQL table without downtime

myisamMySQL

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?

Best Answer

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.