Repair mysql database

After an unexpected shutdown, your mysql dastabase files might need to be recovered.

https://www.a2hosting.com/kb/developer-corner/mysql/repairing-mysql-data…

Database datafiles for mysql are typically in /var/lib/mysql, one folder for each schema so long as you have innodb_file_per_table set.

[mysqld]
innodb_file_per_table=1

The mysql log file can be found at /var/log/mysql/error.log. 

Check each database with

mysqlcheck my_database

Check a specific table with

mysqlcheck my_database my_table

Note you’ll likely need to specify a user and password to mysqlcheck, like so

mysqlcheck -u root -p my_database

If mysqlcheck is disconnected from mysql, then the error.log will show the issue. Typically it’s with the next table in the folder after the one that was shown as OK.

To attempt to fix the errors try

mysqlcheck -r my_database my_table

Likely that will not work.

To recover innodb tables, set innodb_force_recovery=4 in the my.cnf file, usually located at /etc/mysql/my.cnf.

[mysqld]
innodb_force_recovery=4

Export just structure and then export data skipping tables that are problematic.

mysqldump -u user -p database --no-data > db-structure.sql
mysqldump -u user -p database --no-create-info --ignore-table=my_database.my_table > db-data.sql

These could take a long time.

Drop the database you just exported

mysql -u root -p

drop database my_database

quit

Comment out the innodb_force_recovery option and restart mysql.

[mysqld]
#innodb_force_recovery=4

If you forget to comment this out, you will get errors when you try to remove the database you want to rebuild.

Recreate the database. PHPMyadmin works nicely.

Import 

mysql -u root -p my_database < db-structure.sql

mysql -u root -p my_database < db-data.sql

 

Repair a table

mysql -u [username] -p

use [databasename];

show tables;

check table [yourtablename];

repair table [yourtablename];