SQL Repairs

• Step 1: Backing up the databases

service mysql stop
cp -rfv /var/lib /mysql /var/lib /mysql$(date +%s)
service mysql start

• Step 2: mysqlcheck Repair Table

cd /var/lib /mysql
mysqlcheck DATABASE TABLE
mysqlcheck -r DATABASE TABLE #repair any broken databases

• Step 3: Running engine-specific diagnostics

• Repairing MyISAM tables with myisamchk

service mysql stop
cd /var/lib /mysql/$DATABASE
myisamchk TABLE
myisamchk *.MYI
rm /var/lib /mysql/*.TMD #only if the previous commands don't work
myisamchk --recover TABLE #repair any broken table
service mysql start

• Running the InnoDB recovery process

Add the following line to the [mysqld] section of my.cnf:

innodb_force_recovery=4

Run the following:

mysql restart
mysqldump --all-databases --add-drop-database --add-drop-table > ~/databases.sql

Comment out the following line in the [mysqld] section as shown:

#innodb_force_recovery=4

Attempt to start mysql

service mysql restart

If the service still will not start, perform the following:

service mysql stop
cd /var/lib /mysql
rm -rf $DATABASE #!!!Make sure you do not delete the mysql or performance_schema directories!!!
service mysql start
mysql < ~/databases.sql