Friday, March 22, 2013

Repairing corrupted mysql database


Sometimes it happens that your database get corrupted due to many reason like it does nto get restored properly or server get rebooted while updating database etc…. and you have tried to repait it using phpmyadmin and it does shows that it has repaired but it is not.
Solution :1
Login to server with root access
Stop mysql using following command
/etc/init.d/mysql stop
Run following command to check all the tables in the database
Run following command to check all the tables in teh database
/usr/bin/myisamchk /var/lib/mysql/databasename/*.MYI
Run following command to repair all the tables in the database
/usr/bin/myisamchk -r /var/lib/mysql/databasename/*.MYI
Then recheck again using following command
/usr/bin/myisamchk /var/lib/mysql/databasename/*.MYI
Restrart MySql service using following command
/etc/init.d/mysql restart
Solution : 2
If you want repair DB using myisamchk, you need to shutdown MySQL service before proceeding otherwise it will corrupt some other databases.

Alternatively, if you do not want to shut down MySQL, you can use mysqlcheck
mysqlcheck [DBNAME]
To repair the database tables: 
mysqlcheck -r [DBNAME]
also we can use the commands
mysqlcheck -u{username} -p{password}  –check –optimize –auto-repair –all-databases

No comments:

Post a Comment