MySQL 5.x Repair Cheat Sheet
Last updated on April 14, 2024 am
If you upgrade MySQL or move the database around, you are most likely need to do those operations:
Backup
mysqldump -u root -p --default-character-set=utf8 databasename > data.sql
Restore
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
Reference:
http://www.felipecruz.com/repair-mysql-database.php