Check & Repair Corrupt MySQL Database using myisamchk

MYISAMCHK is a table maintenance utility developed by MySQL. This utility works with MyISAM table that uses .MDI for index and .MYD for data. Database administrator can also use REPAIR Table & CHECK Table statements to repair and check corrupt MySQL database. It offers several options to check, repair and optimize corrupt MySQL database. The options are given below: 

What are the Importance & Use of MySQL database Backup?

Performing backup of MySQL database is the most important activity of any database administrator but most of database administrators just neglect this activity. It is like a nightmare situation for the administrators, if database gets corrupt and there is no any backup available to recover MySQL database. At this scenario, they will realize that they have done a blunder mistake. Backup helps to handle any type of corruption strike by virus, hardware failure and other. 

How to Restore MySQL database from Program files?

In this article, we will discuss about a scenario wherein a database administrator has tape backup of C drive only and production server had compromised by virus attack which was hosted in a third party hosting company. It is very frustrating for every database administrator if they have not schedule MySQL database export to a SQL backup file.

Perform Backup & Restore On MySQL Database Table

Database administrator can use Backup Table syntax for backup and Restore Table syntax for restore MySQL database table. First we will know about how to take a backup of MySQL database table then after how to restore MySQL table from backup created using Backup Table syntax.

Repercussions of InnoDB table corruption and MySQL database recovery

MySQL incorporates a blend of advanced features to offer users a performance-driven and a fully-integrated transaction-safe database. It has excellent capabilities to enable you manage any large database environment. You can use the Query Analyzer to keep a check on query performance and identify the faulty SQL code that is causing a slowdown. Some of its most distinguished features include, crash recovery, roll back, full commit, and row-level locking capabilities. Despite being so much reliable and consistent, this database is prone to corruption. MySQL corruption generally occurs due to improper server shutdown, operating system malfunction, metadata structure damage, etc. As a consequence, all vital database objects become inaccessible. To overcome this problem, you need to go for MySQL database recovery through a proficient third-party utility.

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.