MySQL 5.x Repair Cheat Sheet


If you upgrade MySQL or move the database around, you are most likely need to do those operations:


mysqldump -u root -p --default-character-set=utf8 databasename > data.sql


mysqldump -u root -p --default-character-set=utf8 databasename < data.sql
if database is larger then 100MB, this works better:
mysql -u root -p
#mysql> use the_database
#mysql> source data.sql

Check, Repair & Optimize All Tables in All Databases

If you need database server up, use mysqlcheck:
mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

Or you can make it offline and do a better check:
service mysqld stop
myisamchk /var/lib/mysql/the_database/*.MYI
service mysqld start

Repair single table

mysql -u root -p
#mysql> use the_database
#mysql> repair table the_table