How to fix Corrupt InnoDB Database

Sometimes, power outage or software & hardware malfunction corrupt the MySQL database. MySQL database mostly used two database engines to store data: MyISAM & InnoDB. MySQL database mostly get corrupted either by MyISAM corruption or by InnoDB corruption. MyISAM corruption is easy to fix & I have already described it in this post. This article will provide all the steps to recover corrupted InnoDB database.

InnoDB database engine provides transaction safe database within MySQL database. You can review the MySQL log file and confirm that database is not starting due to InnoDB corruption or some other reasons as well as find the solution to fix it.

Log File always guide for recovery:

First check log file by using UNIX command tail & check what’s written in the log file. There are two possibilities:

• Either it shows the error message like “Ending processing because of a corrupt database page”

                                            Or

• Suggest solution for checking actually where corruption exists.

Possibilities Suggestions:

1) In first case, you may check that the database get corrupt and it can be recover by proper recovery steps.

2) In second case the log file provide links of online resource containing more information about steps to recovery.

But the second case occurs very rare & it helps only in some corruption cases. Check following steps to resolve severe corruption:

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

[mysqld]
innodb_force_recovery = 4 

Step 2: Restart MySQL.

Step 3: Dump all tables

Step 4: Stop mysqld 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.

Step 6: Restore everything from the dump.

If neither of the two options is able to solve your problem & you also forgot to create backup. In this case you should use third party MySQL Database Recovery Software to recover corrupt InnoDB tables. Surely, the software recover corrupt InnoDB database without losing your important data.

»»  Read More...

How to rectify MySQL error code 145

MySQL logo (Photo credit: tmolini)

If MySQL Server is unable to open MyISAM tables and reporting an error message i.e. “MySQL error code 145”. It reflects that MyISAM tables got damaged. 

Full description of the error:

“MySQL error code 145 = Table was marked as crashed and should be repaired”

What is MyISAM database engine: 

It is a default storage engine for MySQL database. MyISAM supports three types of file .frm, .myd & .myi and these files stores in tables. These file types such as:
The .MYD: called data file  [USE images]
The MYI: called index file.
The .FRM: used to store the definition of the table. 

How should I know if MyISAM files get corrupted or not?: 

You can identify corruption by considering following points:

• Once you get an error message during selecting data from the table. For example: “MySQL error code 145”
• If Queries don't find rows in the table.
• If Queries returns incomplete results.

You can check MyISAM table corruption by using the CHECK TABLE statement.

How to repair corrupted MyISAM files:

 
You can easily repair corrupted MyISAM files either by using REPAIR TABLE command or by using myisamchk command. But first you should read the following points before using these commands:

• First make sure you have created the backup before using any repair command.
• myisamchk does not support partitioned tables but REPAIR TABLE command supports partitioned tables.
• Befor using myisamchk, first stop MySQL Server.
• Also ensure that no other application is using tables while you are running myisamchk command.

What if MySQL Inbuilt utilities doesn't repair corrupt MyISAM files:

 
If above Inbuilt utilities are giving you an error message while you are trying to recover your corrupt MyISAM tables or if you are still unable to open MyISAM tables then you need to try an appropriate third party MySQL Recovery tool to fixed table corruption. These software repairs corrupted tables as well as other corrupted MySQL database objects. It also repair corrupt MySQL database when MySQL inbuilt utilities are unable to do that.

»»  Read More...

3 Tweaks that Helped Me TO Recover MySQL Database In Few Hours

When I starts MySQL Server, I am not able to perform any action on it. Like other users, I am also stuck with MySQL Recovery process. But I am not able to understand “what is the reason behind it?” Recently, I made several changes to MySQL database that increased the speed of the database. 

Sometimes MySQL database can become inaccessible because of internal database corruption errors, hardware failure, software failure, power interruptions etc. Each possible database failure error requires a different recovery action. Protect your data against the possibility of data loss by having a well known recovery strategy in place. 

The tips I’m about to give you, it won’t cost you thousands of dollars—in fact, implementing these tips won’t cost you a cent. And they can easily be used by any user. My Guide will help you to recover your MySQL database in few hours. When you will apply any recovery strategy on your database then first check:

• The database can be in recoverable stage?
• Mark the time that will be spent to recover your database?
• Mark the time that spent to performing backup operations?
• How much storage space can be allocated for backup copies and another files.
• Will Single table backup be sufficient, or will full MySQL database backups be necessary?

So these are some general terms you should know before performing MySQL Database Recovery 

3 useful tweaks:

Regular schedule for taking database backup:  The concept of a MySQL database is the same as any other database backup. Before taking the backup first shutting down the database to ensure that no further transactions occur, and then simply backing it up. Storing it on a different medium in case of failure or damage to the original. You can then rebuild the database if it becomes damaged or corrupted in some way. Take backup on regular basis to avoid the data loss.

Use Inbuilt Utilities to recover database: The inbuilt utility selection is depend on three terms:

1.Only want to repir MyISAM tables;

2.repair corruption of the other table types;

3.Restore from backup;

If you only want to repair MyISAM tables, use this command:

REPAIR TABLE tablename[,tablename1...] [options];

myisamchk [options] tablename.MYI;

You can use inbuilt MySQL database utilities to repair all corrupt MySQL database tables: 

mysqlcheck [options] dbname tablename [tablename2... ];

or in last you can use restore option to repair MySQL database from the backup. These are some inbuilt utilities examples which you can use to repair MySQL database. 

Commercial Solutions: Third-party recovery software is one another solution to repair corrupt MySQL database & repair all corrupt MySQL database files. The software is build with very advanced technology & work for all MySQL database tables. 

So these are three tweaks to recover MySQL database. If the article helps you to recover your MySQL database, please give your valuable comments..

»»  Read More...