File format · .mwb

MySQL Database Structure and Corruption Repair

Overview of MySQL database structure, file formats, corruption causes, and methods for repairing MySQL tables and recovering data.


MySQL is a widely used open-source relational database management system (RDBMS) that organizes data into tables, allowing for efficient data retrieval and management. While the software itself is robust, the underlying database files stored on the disk can become corrupt due to various system failures. Understanding the physical structure of these files is essential for diagnosing issues and performing successful repairs.

MySQL Database Structure and Storage Engines

MySQL does not use a single monolithic file format for its data. Instead, it utilizes a pluggable storage engine architecture, meaning the physical structure of the database files depends entirely on the storage engine specified for a table (e.g., InnoDB, MyISAM, Archive). The most common engines are InnoDB, which is the default for modern MySQL versions, and MyISAM, which was the default in older versions.

InnoDB Storage Engine

InnoDB is the default and most transactionally secure storage engine for MySQL. It stores data in two primary areas:

  • The Tablespace: By default, InnoDB stores all table data and indexes in a single file known as the system tablespace, typically named ibdata1. This file can grow to a very large size and contains metadata, undo logs, and the data for tables that are not configured to use "file-per-table."
  • File-Per-Table Tablespace: When the innodb_file_per_table setting is enabled (which is the default in many current configurations), InnoDB creates a separate .ibd file for each newly created table. This file contains the data and indexes for that specific table only. The table definition (schema) is stored in the MySQL data dictionary.

InnoDB also relies heavily on a Redo Log (typically files named ib_logfile0 and ib_logfile1) to ensure ACID compliance and crash recovery.

MyISAM Storage Engine

MyISAM is the older storage engine, known for its speed in read-heavy environments but lacking transactional support. A MyISAM table is stored on disk using three distinct files:

  • .frm File: This file stores the table format (schema) or definition. It contains the structure of the table, such as column names, types, and indexes.
  • .MYD File (MYData): This file contains the actual row data stored in the table.
  • .MYI File (MYIndex): This file stores the indexes for the table. The index file is crucial for fast data retrieval.

Causes of MySQL Corruption

Database corruption refers to a state where the data in the database is no longer consistent or readable by the MySQL server. Corruption can occur at the file system level or within the logical structure of the database files.

Common causes include:

  • Unexpected Shutdowns: Power outages or system crashes while data is being written to the disk can leave files in an incomplete state.
  • Hardware Failures: Failing hard drives, bad sectors, or faulty RAM can write incorrect data to the database files.
  • Software Bugs: Issues within the MySQL server software or the operating system kernel can lead to data corruption.
  • Filesystem Issues: Operating system file system corruption or running out of disk space during a write operation can damage files.

For MyISAM, corruption often manifests as errors indicating a wrong key or incorrect file size. For InnoDB, corruption might result in the server refusing to start or throwing "page corruption" errors in the log.

Data Recovery Potential

The potential for data recovery depends heavily on the storage engine and the nature of the corruption.

  • MyISAM: Because the data and indexes are stored in separate text-based and binary files, recovery is often possible. The .frm file is critical; if it is lost, reconstructing the table structure is difficult. However, if the .MYD file is intact, the raw data can often be extracted even if the indexes (.MYI) are corrupt.
  • InnoDB: Recovery is more complex due to the transactional nature of the engine. If the ibdata1 file or specific .ibd files are corrupt, the database may become inaccessible. However, InnoDB includes a crash recovery mechanism that uses the redo logs (ib_logfile*) to repair committed transactions during startup. If physical damage to the file is too severe for the automatic recovery to handle, the data might still exist within the pages of the file but require specialized tools to extract.

How MySQL Recovery Works

Recovery strategies differ based on the engine involved.

MyISAM Repair

MySQL includes a built-in utility called myisamchk for checking and repairing MyISAM tables. The process generally involves:

  1. Stopping the MySQL Server: To prevent further changes or conflicts, the server should be shut down before running manual repairs.
  2. Checking the Table: myisamchk scans the .MYI and .MYD files to identify errors.
  3. Repairing: The utility attempts to rebuild the index file (.MYI) based on the data file (.MYD). In "Safe Recovery" mode, it scans the rows to remove corrupted links and rebuilds the tree.

Alternatively, the SQL command REPAIR TABLE can be run from within the MySQL client, which performs similar operations while managing server locks.

InnoDB Recovery

InnoDB does not support the myisamchk utility. Recovery methods include:

  1. Force InnoDB Recovery: The MySQL configuration file (my.cnf or my.ini) can be edited to set innodb_force_recovery. This setting allows the server to start even if it detects corruption, preventing certain background operations from running. Values range from 1 to 6, with higher levels being more aggressive (and risky). The goal is to start the server long enough to dump the data using mysqldump.
  2. Restoring from Backup: The most reliable method for InnoDB is restoring a logical backup (created via mysqldump) or a physical backup (from tools like Percona XtraBackup) taken before the corruption occurred.
  3. Specialized Software: If the InnoDB tablespace is physically corrupt and the server will not start, third-party file repair tools may be required to parse the .ibd or ibdata1 files at the binary level to extract records and rebuild the database.

FAQ

What is the difference between the .frm, .MYD, and .MYI files?

The .frm file stores the table structure (schema). The .MYD file contains the actual data rows. The .MYI file contains the indexes that help speed up queries. These three files together constitute a MyISAM table.

Can I repair MySQL tables while the server is running?

For MyISAM tables, you can use the SQL command REPAIR TABLE while the server is running, though it may lock the table. For InnoDB, you should not attempt to modify files directly while the server is running. If using myisamchk on MyISAM, it is safest to stop the server to prevent cached data from causing conflicts.

What should I do if innodb_force_recovery fails to start the server?

If the server fails to start even with force recovery enabled, or if setting it to a high level causes data loss, the corruption is likely physical at the disk level. In this scenario, immediate file system repair or engaging a professional data recovery service to image the drive and reconstruct the database files is necessary.

How can I prevent future database corruption?

Regular backups are the best defense. Additionally, ensuring a reliable power supply (UPS), using ECC RAM to correct memory errors, and monitoring disk health with S.M.A.R.T. tools can significantly reduce the risk of corruption.

If you are facing a MySQL database corruption issue that standard repair utilities cannot fix, we offer a free evaluation to assess the damage and determine the best path for recovering your critical data.

Send us your .mwb file for a free evaluation

Our recovery engineers will tell you exactly what can be recovered — at no cost. There is no charge if we cannot recover your data.

Start a free evaluation