Restore corrupted MyISAM/InnoDB tables on MySQL 5.0

[jean-paul martell]

hi,
InnoDB tables are my favorite. Transactional, reliable and unlike MyISAM, InnoDB supports concurrent writes into the same table.Many of our InnoDB and MyISAM tables were corrupted because of this “little” mistake.MySQL database allows to define a different MySQL storage engine for different tables.so is there any simple procedure that can restore all my data (or at least most of it).

[Reegan]

Execute the myisamchk software inside your MySQL data directory. If you don’t know where this directory is, check the parameter datadir on your MySQL configuration file (my.ini on Windows, my.cfg on Linux/Unix).

You must use two parameters: –force and –verbose (which is optional) like bellow:

myisamchk --force --verbose */*.MYI

The –force parameter will instruct myisamchk to repair automatically every problem it may encounter on your MyISAM tables, while the –verbose (which is optional) will only show the status of execution of the myisamchk program on your screen.Start the MySQL server with the parameter –innodb_force_recovery Try to start your MySQL server with this parameter. The value must be a number between 1 and 6. As higher it’s value, worst are the problems on your MySQL tablespace.If you only got to start your MySQL server with a value bigger than four, your databases will be in read only mode. If not, your users may use the server normally.

If you wish, you may also add the command innodb_force_recovery to your MySQL configuration file as in the example bellow:

[mysqld]
innodb_force_recovery=4

After your MySQL server starts, backup your data and then restore them on a new MySQL installation.

[Katty]

You can check the health of a MyISAM table using the CHECK TABLE statement, and repair a corrupted MyISAM table with REPAIR TABLE. When mysqld is not running, you can also check or repair a table with the myisamchk command.

[MindSpace]

Execute the myisamchk as shown below, with -r option to repair the corrupted tables.

# myisamchk -r profiles.MYI

- recovering (with sort) MyISAM-table ‘profiles.MYI’
Data records: 80
- Fixing index 1
- Fixing index 2

You may get error message: clients are using or haven’t closed the table properly, if the tables are still getting used by your application and other tables. To avoid this error message, shutdown mysqld before performing the repair, if you can afford to shutdown the DB for a while. If not, use FLUSH TABLES to force mysqld to flush any table modification that are still in memory.

[Praetor]

Bad memory or MySQL Bugs on Primary can corrupt storage… which will be replicated by DRBD - Long switch time even for Innodb tables - on large system tuned for … may take infinity to check and repair - Waste of hardware as Secondary node …

[Zecho]

Stellar Phoenix Database Recovery for MySQL is an advanced and easy to use repair application, which repairs and restores corrupted MySQL database. It supports in depth recovery from both MySQL database storage engines - MyISAM and InnoDB. As the most dynamic MySQL recovery utility, it supports recovery of lost or corrupted MySQL databases created on both Windows and Linux platforms.

Download Stellar Phoenix Database Recovery for MySQL