Pages

Tuesday, April 12, 2011

MySQL : Recovering CORRUPT TABLES



How to fix corrupt MySQL Tables 
Before you start the process  take MySQL Server is Safe Start - ./mysqld_safe & 
i.e..
mysqladmin -uxxx -pxxx shutdown
mysqld_safe --user=mysql &


1 . For MyISAM tables


To fix a single table, connect to your MySQL database and issue a:
REPAIR TABLE TABLENAME;


To fix everything, go with all databases :
../mysqlcheck --all-databases -uUSERNAME -pPASSWORD -r 
-r indicates ( repair )
I suggest you add this line to your my.cnf config file. It will automatically fix MyISAM tables as soon as they become corrupt  under  : 
[mysqld] 
myisam-recover=backup,force 


2. For InnoDB Tables
InnoDB does not support  repair -
Once you have corrupt InnoDB tables that are preventing your database from starting, you should follow this five step process:


Step 1: Add this line to your /etc/my.cnf configuration file:


[mysqld] 
innodb_force_recovery = 4 


Step 2: Restart MySQL. Your database will now start, but with innodb_force_recovery, all INSERTs and UPDATEs will be ignored. 


Step 3: Dump all tables


Step 4: Shutdown database and delete the data directory. Run mysql_install_db to create MySQL default tables


Step 5: Remove the innodb_force_recovery line from your /etc/my.cnf file and restart the database. (It should start normally now)


Step 6: Restore everything from your backup


There were other 6 recovery modes availables which can be used as per requirements for corrupted innodb tables






Cheers!!!

No comments: