Friday, March 5, 2010

Heavy Load Might Corrupt InnoDB Database

Storing MySQL database with InnoDB storage engine gives enhancements of ACID-compliant transaction support and foreign key support, as compared to other storage engines. But database thus stored can also become corrupt and inaccessible due to several issues. Using MySQL Server in heavy load environment might corrupt the database. In such cases, it is recommended to dump the database tables if a valid backup is available. But if no clean backup is present, use powerful MySQL Recovery software to repair and restore the database.

Following is the example of a MySQL Server database stored using InnoDB storage engine that processes large amount of data and performs multiple tasks at a time:

A large database with thousands GB of data Multiple threads are running that write data to the server Several complex are running Multiple threads are running that query data from the server Different operations are performing to manipulate data from several tables

If you use a MySQL Server database in such an environment, the Server might crash and result into a corrupt database. You might observe that the InnoDB engine hangs when you to try to access the database.

Solution

If there are one or more database tables that are preventing the MySQL Server to start, you can dump those tables provided you have a valid backup available. To do so, follow these steps:

Before trying to start MySQL Server, add the following line to /etc/my.cnf configuration file:

[mysqld] innodb_force_recovery=4

This allows starting the server while preventing background operations.

Restart MySQL Server Dump all the database tables and shutdown the database You next need to delete the data dictionary and execute mysql_install_db in order to create the default tables that MySQL requires You should then remove the innodb_force_recovery line from the file Restore from backup

In case, no valid backup is available, you are suggested to use third-party MySQL Database Recovery tools. MySQL Recovery utilities are powerful, comprehensive and safe database repair applications that repair damaged MySQL database while maintaining the intactness of data.

Database Recovery For MySQL is an advanced and powerful MySQL Database Recovery utility that supports both MySQL database storage engines - MyISAM and InnoDB. It can completely repair and restore MySQL objects such as tables, primary key(s) and relations. It is compatible with Windows Vista, XP, 2003 and 2000. It supports MySQL 5.x. It also supports Windows based recovery from MySQL database installed on Linux platform.

Article Source :- http://www.goarticles.com/cgi-bin/showa.cgi?C=1931742

1 comment:

  1. Repair mysql database due to following software MySQL Fix Toolbox utility starts under all Windows OS, starting with Win 98

    ReplyDelete