Repair Existing Crashed Databases and Tables
From the command line, run a general check and repair of all MyISAM and InnoDB tables:
mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
The MySQL Services has to be running for “mysqlcheck” to work.
If MySQL will not start (and only in this case), run myisamchk with the path to MySQL’s data directory:
myisamchk --recover C:/WampDeveloper/Database/mysql-data-56/*/*.MYI
Auto Repair Future Crashed Databases and Tables
Edit MySQL’s my.ini file and tell MySQL to auto recover MyISAM tables as they are opened:
[mysqld] # For MySQL 5.1 #myisam-recover=BACKUP,FORCE # For MySQL 5.5.3+ myisam-recover-options=BACKUP,FORCE
Remove the myisam-recover option “BACKUP” if this is happening often, as the backup files will accumulate and grow in number (and the overall size becomes a problem).
InnoDB tables automatically recover after every crash, and no additional configuration is needed… Unless the InnoDB data is so corrupt that MySQL will not start.
Trace Crashes to Specific Query
If you log all the queries made, the last one before the crash will likely be the one that is causing crashes and corruption. And from it, you can trace the issue back to its origin…
[mysqld] general_log = 1 general_log_file = "C:/mysql-query.log.txt"