What to do when MySQL database is corrupted

Usually once the database is corrupted, the best practice is to restore it from the last backup or from the DR. Or take down the affected node, if Galera clustering is enabled so that the traffic can be taken from the other nodes.

Before going to the recovery steps, check out the database engine, whether it is InnoDB or MyISAM. Both have different recovery steps to be followed.

After getting in to the database, type the below query to get the engine type:

show table status;

Or to be specific
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES where TABLE_SCHEMA = 'database' AND ENGINE IS NOT NULL;

Innodb is more fault-tolerant compared to MyISAM. InnoDB has auto_recovery features and is much safer as compared to the older MyISAM engine.

Backup existing Databases
Before doing any recovery step, stop the service and take the backup
service mysqld stop
cp -r /var/lib/mysql /var/lib/mysql_bkp

After we have a backup copy of the data directory, we are ready to start troubleshooting.

Identify the corrupted data
Error log always helps to find the the corrupted data. We can check the variable log_error in MySQL configuration file my.cnf for the exact log file.

The main tools/commands to diagnose issues with data corruption are CHECK TABLE, REPAIR TABLE, and myisamchk. The mysqlcheck client performs table maintenance: It checks, repairs (MyISAM), optimizes or analyzes tables while MySQL is running.
mysqlcheck -uroot -p <DATABASE> <TABLE>

Mysqlcheck checks the specified database and tables. If a table passes the check, mysqlcheck displays OK for the table.

Recovering InnoDB table
To enable auto recovery MySQL needs innodb_force_recovery option to be enabled. Innodb_force_recovery forces InnoDB to start up while preventing background operations from running, so that you can dump your tables.
To do this open my.cnf and add the following line to the [mysqld] section:
[mysqld]
innodb_force_recovery=1

service mysql restartYou should start from innodb_force_recovery=1 save the changes to my.cnf file, and then restart the MySQL server using the appropriate command for your operating system. If you are able to dump your tables with an innodb_force_recovery value of 3 or less, then you are relatively safe. In many cases you will have to go up to 4 and as you already know that can corrupt data.

If needed change to the higher value, six is the maximum and most dangerous.

Once you are able to start your database, type the following command to export all of the databases to the dump.sql file:
mysqldump –all-databases –add-drop-database –add-drop-table > dump.sql

Start mysql, and then try to drop the affected database or databases using the DROP DATABASE command. If MySQL is unable to drop a database, you can delete it manually using the steps below after you stop the MySQL server.
service mysqld stop

If you were unable to drop a database, type the following commands to delete it manually.
cd /var/lib/mysql
rm -rf <DATABASE>

Make sure you do not delete the internal database directories.
After you are done, comment out the following line in the [mysqld] to disable InnoDB recovery mode.
#innodb_force_recovery=…

Save the changes to the my.cnf file, and then start the MySQL server
service mysqld start

Type the following command to restore the databases from the backup file you created in step 5:
mysql> tee import_database.log
mysql> source dump.sql

Repairing MyISAM

If mysqlcheck reports an error for a table, type the mysqlcheck command with -repair flag to fix it. 
The mysqlcheck repair option works while the server is up and running.
mysqlcheck -uroot -p -r <DATABASE> <TABLE>

If the server is down and for any reason mysqlcheck cannot repair your table, you still have an option to perform recovery directly on files using myisamchk. With myisamchk, you need to make sure that the server doesn’t have the tables open.

Stop the MySQL
service mysqld stop
cd /var/lib/mysql

Change to the directory where the database is located.
cd /var/lib/mysql/employees
myisamchk <TABLE>

To check all of the tables in a database, type the following command:
myisamchk *.MYI

If the previous command does not work, you can try deleting temporary files that may be preventing myisamchk from running correctly. To do this, change back to the data dir directory, and then run the following command:
ls */*.TMD

If there are any .TMD files listed, delete them:
rm */*.TMD

Then re-run myisamchk.

To attempt repair a table, execute the following command, replacing TABLE with the name of the table that you want to repair:
myisamchk –recover <TABLE>

Restart the MySQL server
service mysqld start