mysql repair tables

With a recent OS upgrade, some of the mysql database tables got corrupted. Below is how I was able to get it repaired.

  1. Stop mysql server.
  2. Once mysql server is stopped, run a repair on all of *.MYI files via myisamchk:
    # myisamchk -r /var/lib/mysql/*/*.MYI
  3. Bring up the mysql server.
  4. Run a mysqlcheck of all databases via:
    # mysqlcheck -c --all-databases | tee /tmp/dbcheck.log
  5. Grep for "error" on the log and proceed to create a sql file to be run to repair the tables.
    # grep error -B1 /tmp/dbcheck.log | grep -v "error\|--" | sed 's/\(.*\)/REPAIR TABLE \1;/' >/tmp/dbrepair.sql
  6. The file output should be something like:
    REPAIR TABLE database1.table1;
    REPAIR TABLE database1.table2;
    REPAIR TABLE database2.table1;
  7. Log into mysql and source the repair script:
    # mysql> source /tmp/dbrepair.sql
  8. That should run and repair all of the corrupted tables. Verify by running another check and maybe an extended one.
    # mysqlcheck -c -e --all-databases
Comment