How to Recover Corrupt MySQL Database - MySQL Recovery

MySQL database is an open source database with the best speed and reliability. Most of the branded organizations use it to save their money and time. It offers several syntax to optimize, backup and repair corrupt MySQL database. MySQL database can be corrupt due to several reasons like power failure, malware attack, system failure, hardware failure and many more. Sometimes repair syntax fails to fix the corruption at this situation, you should take help from any third party MySQL database repair software.

In this article, we will discuss a condition wherein a database administrator has tape backup of C drive only and how to recover corrupt MySQL database with the help that tape backup of C drive.

Recover MySQL Database with tape backup: To do this, you will have to follow the below points.

1. Create a new MySQL server environment: Install a same version of MySQL server to restore corrupt MySQL database. For example, if you are at MySQL version 4.1.22, then install the same version.
2. Stop MySQL Service: It is required for adding the data folders.
3. Copy & Paste Database Folder: Copy the database folder from old MySQL database and paste them in new MySQL database folder. Database folder contains information about .frm, .myd and .myi files. The default database folder location is C:\Program Files\MySQL\MySQL Server x.x\Data\database-Folder-Name\.
4. Restart MySQL Service: To restore new MySQL database.
5. Check & Repair MySQL Database Tables: MySQL offers check & repair syntax to check and repair corrupt MySQL database tables as discussed above. Check syntax specifies that there is a corruption in the table and those corrupt tables can be repaired by the Repair syntax. It is very necessary for ensuring that there is no any corruption in the MySQL database tables.
6. Make Backup: If corruption has been fixed by the above mentioned steps then make a backup to avoid data loss in future.

Summary: In the end, if check syntax specifies that corruption in the table and Repair syntax is unable to fix then it is recommended you to try any 3^rd party MySQL database repair software to fix the corruption.

Note: Above mentioned steps will work with MyISAM database storage engine not with the InnoDB database storage engine.

Types & Comparison between MySQL database Storage Engines

MySQL is an open source database and used by the most of the database administrator. It uses database storage engines as a handler to handle different MySQL database tables. There are several storage engines supported by MySQL like MyISAM, InnoDB, Merge, Memory, Archive, CSV, Example, Federated and many more. All of them have their own functionalities and characteristics.

What is the default storage engine? The point comes in your mind. If database administrators do not select the type of storage engine for the database then what is the default storage engine for the database. The answer is InnoDB. It is the default storage engine.

Applied For: After release MySQL 5.5, InnoDB will be the default storage engine.

How to Change Storage engine? A database administrator can change the storage engine for database from the pluggable storage engine. Pluggable storage engine gives users flexibility to select the best fit for their specific case.

InnoDB storage engine is the most used storage engine in comparison of other storage engine. It is used for financial systems, health care applications, telecommunications, retails built on MySQL database. This storage engine is structurally designed to handle transactional related applications that needs crash recovery, high response time, concurrency, and integrity.

Comparison between InnoDB & MyISAM Storage engine: Each storage engine has its own specific characteristic and benefits. Beyond the performance, there are various other factors that suggest for a particular storage engine.

Features	InnoDB	MyISAM
ACID Transactions	Yes	No
B-Tree Indexes	Yes	Yes
Crash Safe	Yes	No
Clustered Index	Yes	No
Storage Limits	64TB	256TB
Foreign Key Support	Yes	No
Full Text Search Indexes	No	Yes
Backup/Point-in-time Recovery	Yes	Yes
Data Cashes	Yes	No
Others

Note: I have tested all above comparison scenarios but there may be many more comparison between these two storage engines.

Summary: InnoDB is default and most used storage engine by MySQL database administrators. It offers efficient ACID transactional capabilities. Now database administrator can create application in the InnoDB storage engine without altering default configuration settings.

About Author: Author is a technical writer and had written several article on MySQL recovery scenarios. You can reach him through comment.

Comments: Share your feedback through comments. I always welcome your comments.

Analyzing the root causes of Index file corruption in MySQL and methods to perform database recovery

MySQL is an open source database that is freely available under GNU General public license. It offers developers a range of storage engines, including Berkeley DB, InnoDB, Heap, and MyISAM. It incorporates adequate security measures and also provides regular security patches to keep the software up-to-date. When it comes to performance, it clearly dominates over the other databases. However, MySQL databases may get corrupt in the event of a virus infection, hardware malfunction, or an unexpected system shutdown. In such circumstances, you lose access to the vital information stored in the database. To overcome this problem, you need to go for MySQL recovery through a reliable third-party utility.

For instance, you might receive the following error message on your screen while trying to access a MySQL table:

“No more room in index file”

Cause: The above error occurs when you try to access a damaged or corrupt MySQL table. This would render all valuable records in the table inaccessible. At this stage, you may use an updated database backup to restore the table. However, if the backup is unavailable, you should follow the below mentioned resolution steps.

Resolution: For resolving the issue, follow the below given steps:

• Firstly, ensure that you have full administrative rights on the database that you are trying to access.
• Use the following commands to verify the integrity of the database table:

CHECK TABLE tablename;

myisamchk *.MYI

mysqlcheck *.MYI

• Use the 'REPAIR' command with appropriate options to repair corruption:

REPAIR TABLE table_name

• Now, use the 'EXIT' command to quit the database. Restart the MySQL server.

If the problem still persists, you should take help of an efficient MySQL repair utility. These software embed a range of advanced and highly robust mechanisms to efficiently scan corrupt MySQL databases and restore valuable objects, such as tables, primary keys, indexes, constraints, etc.

These third-party tools provide preview of all recoverable objects and allow saving log of the recovery process in a separate text file. They support all the latest MySQL versions, including MySQL 3.x, 4.x, 5.x, and 6.x. Furthermore, they are compatible with Windows 7, Vista, Server 2003, XP, and 2000.

How to check & verify InnoDB TableSpace File Corruption

MySQL database offers an offline InnoDB File Checksum Utility (innochecksum) to check the checksums for innodb files. When you run innochecksum utility on the mysql database then it will read and print checksums for innodb tablespace files. After calculating checksum it will compare the calculated checksum with stored checksum, if you found checksum mismatch between calculated checksum and stored checksum that means pages are corrupt.

Process of innochecksum Utility:

Read innoDB tablespace files-->Calculate checksum-->Compare checksum-->Report checksum mismatch

Why developed this tool: There are two main motives to develop this utility that are as:

• To speed up verifying the integrity of tablespace files after power outages.
• To verify the integrity after file copies.

Use as soon as Possible: As we know that checksum mismatch will shutdown the running mysql server so use this utility to as soon as possible rather than waiting production database will encounter the damaged pages.

Note: innochecksum does not work on tablespace files that the server already has open. For those types of files you can use CHECK Table command to check tables within the tablespace.

Recover Tablespace: When innochecksum utility finds checksum mismatch for the innodb tablespace files then it is recommended you to restore the tablespace from backup or start mysql server and use mysqldump to make a backup of table’s within the tablespace.

How to restore without Backup: If you don't have backup of corrupt tablespace then you should try any third party MySQL repair utility that will restore corrupt tablespace of mysql database.

Attention: Always maintain a backup for any database to avoid data loss when disaster will strike to the database.

Summary: innochecksum is an offline innodb file checksum utility that calculates, compares and reports checksum mismatch for innodb database. You can restore corrupt tablespace from backup or using any third party MySQL repair utility.

Incorrect Information in .FRM file of MyISAM storage engine

MySQL database uses tables to store data and all these tables are defined by .FRM file. FRM is an internal file in MySQL database that contains the formating information or structure data for the database. It defines fields and structure of each MySQL's table. Regardless of the storage engine that you choose, every MySQL table is represented by .frm file. File bears the same name as table name with extension .FRM.

Example: suppose you have create a employee table in mysql database then frm file name will be employee.frm that will define the employee table.

Corruption in MySQL database table will resist you to access the table that can be happen due to several reasons like kernel bugs, unexpected power failure, hardware failure, virus attack, and many more. A corrupted table will placed the incorrect information in the frm file and you may got below error message:

“Incorrect information in file: './able-name.frm'”

Resolution: you have three solutions to repair MySQL table after getting above error message:

1. Repair corrupt table by REPAIR TABLE statement
2. Restore from Backup
3. Stop and copy method

Stop and copy method details
Note: if you are using MyISAM storage engine then follow the below steps to repair corrupt table:

• Stop MySQL database
• Take backup of your .frm, .myd, myi files.
• Delete these files from mysql data directory.
• Now start MySQL Database
• Rereate table again by CREATE Table statement.
• Stop MySQL database.
• Copy .myd & .myi files back into the data directory, replace the ones there.
• Start MySQL database again.

If all three methods fail to fix the corruption from table then at last you should try Stellar Phoenix MySQL repair software. MySQL repair software repairs corrupted table created using MyISAM as well as Innodb. This software has around 99 percent success rate. Its very safe and offers easy-to-use interface.

REPAIR TABLE statement to Repair corrupt MySQL Table

MySQL database offers seven syntaxes for proper maintaining of mysql table's records. Those syntaxes are as follows:

1. Repair table syntax 
2. Analyze table syntax
3. Backup table syntax
4. Check  table syntax
5. Checksum  table syntax
6. Optimize  table syntax
7. Restore  table syntax

In this article, we will discuss briefly about first syntax that is Repair Table syntax.

REPAIR Table Syntax: It repairs corrupted mysql database tables. A table gets corrupt due to several reasons like virus attack, system failure, power failure, hardware problem, power failure and so on. Corrupt mysql database tables can be repair by this syntax.

Systax for Repair Table:
“REPAIR [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...
    [QUICK] [EXTENDED] [USE_FRM]”

Above syntax has three options like quick, extended, and use_frm.
When you select “quick” option then repair table syntax will try to repair index file only. This kind of repair is done by myisamchk.

When you select “extended” option then mysql database creates row by row index in place of one row at a time.  This kind of repair is done by myisamchk.

Use_frm option is available only when header file is corrupted or index file missing.  This kind of repair is not done by myisamchk.

Caution: It is the best method to take a backup of corrupt mysql database table before trying “Repair Table” syntax on it.

Sometimes above repair syntax also fails to fix corruption in mysql database at this situation most of the database administrators suggest to try any third party mysql database repair software. It totally depends upon you to choose the best mysql repair tool that meets your requirement and budget.

How to Repair MySQL Table using phpMyAdmin?

MySQL is a relational database and uses table to store the data. Table has column and row to store records. An Individual column must be created to store each type of data like employee name, employee code, employee address, age, sex, and many more. On the other side row contains the actual value that specified with the column of table.

Example: Lets consider an example, we have created an employee details table. Employee details table has five columns employee name, employee code, employee address, employee age, and employee sex. We can insert values into the employee details table through the insert query.

We have inserted John, s0300, New Jersey, 28, and M. Wherein

John= Employee name

s0300=Employee code

New Jersey=Employee adress

28=Employee age

M=Employee Sex.

Corruption in Employee Details Table: Corruption in the above table can happen due to severely reasons like operating system failure, power failure, hardware problem, metadata structure corruption, virus attack, and many more.

How to Repair?: When MySQL database tables get corrupted or damaged, you can repair and restore corrupted MySQL table by carrying out the below mentioned methods.

PhpMyAdmin Method

Third party repair software method

Repair using phpMyAdmin: Follow the below steps to repair corrupt MySQL database table through phpmyadmin.

• Login to the phpmyadmin.
• Select the database.
• Note: if you have only one database then it will be selected by default.
• Goto to the main panel and choose database.
• Select the corrupted MySQL database table.
• Goto to the bottom of the window and click on the drop down menu.
• Select Repair Table from the menu.
• And phpmyadmin automatically fix the corruption in the table.

Third party repair software: If you are unable to repair corrupt MySQL table through above mentioned method then you should try any third party MySQL repair software. MySQL repair software is very helpful in most of the corruption cases and repair table up to ninety nine percent. These software are safe in nature and don't harm your database.

Top three MySQL database repair software: Top three MySQL database repair software are stellar phoenix database recovery for MySQL, recovery for MySQL, recovery toolbox for MySQL.