Keywords: MySQL recovery | physical files | MyISAM | InnoDB | database backup
Abstract: This article provides a comprehensive exploration of MySQL database restoration from physical files, with detailed analysis of file structures and recovery procedures for both MyISAM and InnoDB storage engines. Through in-depth examination of .frm, .MYD, .MYI files and core InnoDB components like ibdata1 and ib_logfile files, it offers complete recovery steps and permission configuration guidelines. The article combines practical examples to illustrate operational differences between Linux and Windows environments, emphasizing the importance of server state management and file permission settings.
Understanding MySQL Physical File Structures
Physical restoration of MySQL databases requires deep understanding of storage engine file organization. In MySQL, different storage engines employ distinct file structures, with MyISAM and InnoDB being the most commonly used engines.
MyISAM Storage Engine File Composition
MyISAM tables consist of three core files: .frm files store table structure definitions, containing metadata such as field information and data types; .MYD files serve as actual data storage, recording all table content; .MYI files are dedicated to index storage, optimizing query performance. This file separation design makes MyISAM excel in read-only or read-intensive scenarios.
MyISAM Database Recovery Procedure
The primary step in recovering MyISAM databases is stopping the MySQL service to ensure data files are not occupied. In Linux systems, the default data directory is located at /var/lib/mysql/. After completely copying the backup database folder to the target location, proper file permissions must be set: use sudo chown -R mysql:mysql /var/lib/mysql/database_name to transfer ownership to mysql user and group, set file permissions with sudo chmod -R 660, while ensuring the database directory itself has 700 permissions.
InnoDB Storage Engine Recovery Challenges
InnoDB storage engine recovery is relatively complex, involving multiple system-level files. Core files include ibdata1 (storing tablespace data), ib_logfile0 and ib_logfile1 (transaction log files). The coordinated operation of these files ensures InnoDB's ACID properties.
InnoDB Configuration File Adjustments
When recovering InnoDB databases, modification of MySQL configuration file my.cnf may be necessary. Key parameters include: uncommenting skip-innodb to enable InnoDB engine; setting innodb_data_home_dir to point to the data directory; configuring innodb_log_file_size to match actual log file size; adjusting innodb_buffer_pool_size to optimize memory usage.
Force Recovery Mechanism Application
When encountering severe InnoDB corruption, the --innodb_force_recovery=6 startup parameter can be used for forced recovery. This parameter allows MySQL to start in a corrupted state, with level 6 indicating maximum data recovery possible, though some transaction consistency may be lost. After successful recovery, data should be immediately exported and reimported into a normal environment.
Cross-Platform Recovery Considerations
In Windows environments, MySQL data directory is typically located at C:\Program Files\MySQL\MySQL Server X.X\data\. Similar to Linux systems, service must be stopped before file operations. For integrated environments like XAMPP, data paths may vary and require adjustment based on specific installation configurations.
Permission and Ownership Management
Correct file permission settings are crucial for successful recovery. Beyond basic chown and chmod commands, attention must be paid to: InnoDB system files (like ibdata1, ib_logfile*) must have mysql user ownership; database directories require execute permissions; table files need read-write permissions. In complex permission environments, sudo chown -R mysql:mysql /var/lib/mysql can be used to uniformly set permissions for the entire data directory.
Recovery Verification and Subsequent Processing
After recovery completion, start MySQL service and verify database accessibility. Immediately execute CHECK TABLE commands to inspect table integrity, and use mysqldump to export SQL backups as additional protection. For production environments, thoroughly test recovery effects in testing environments before actual deployment.
Best Practices Summary
While physical file recovery is direct, it carries higher risks. Combining logical backups (like mysqldump) to form complete backup strategies is recommended. Regularly test recovery procedures to ensure quick and effective service restoration during emergencies. For critical business data, consider using MySQL Enterprise Edition's advanced backup features or third-party professional backup tools.