Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 14-04-2009
Member
 
Join Date: Mar 2009
Posts: 92
Restore corrupted MyISAM/InnoDB tables on MySQL 5.0

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).
Reply With Quote
  #2  
Old 14-04-2009
Member
 
Join Date: Oct 2005
Posts: 2,389
Re: Restore corrupted MyISAM/InnoDB tables on MySQL 5.0

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.
Reply With Quote
  #3  
Old 14-04-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: Restore corrupted MyISAM/InnoDB tables on MySQL 5.0

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.
Reply With Quote
  #4  
Old 14-04-2009
Member
 
Join Date: Feb 2008
Posts: 1,848
Re: Restore corrupted MyISAM/InnoDB tables on MySQL 5.0

Execute the myisamchk as shown below, with -r option to repair the corrupted tables.
Code:
# 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.
Reply With Quote
  #5  
Old 14-04-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
Re: Restore corrupted MyISAM/InnoDB tables on MySQL 5.0

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 …
Reply With Quote
  #6  
Old 14-04-2009
Member
 
Join Date: May 2008
Posts: 2,293
Re: Restore corrupted MyISAM/InnoDB tables on MySQL 5.0

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
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Restore corrupted MyISAM/InnoDB tables on MySQL 5.0"
Thread Thread Starter Forum Replies Last Post
How to manage databases and tables in MySQL? Aahlaadith Software Development 5 17-12-2010 07:18 AM
How to create MySQL Database and Tables in PHP? Orton Software Development 4 23-02-2010 06:45 AM
Mysql select from multiple tables for php. afidelino Software Development 4 09-07-2009 06:13 PM
MySQL MyISAM vs InnoDB tombman Software Development 5 13-04-2009 03:42 PM
MyISAM Static Vs MyISAM Dynamic Sylvester.S Education Career and Job Discussions 2 24-10-2008 07:59 PM


All times are GMT +5.5. The time now is 09:56 AM.