Keywords: MySQL | table repair | myisamchk | database crash | data recovery
Abstract: This article provides a comprehensive analysis of the common issue where MySQL tables are marked as crashed with failed automatic repairs. Based on Q&A data and reference cases, it systematically explains the causes, diagnostic methods, and multiple repair strategies. The focus is on detailed steps for offline repair using the myisamchk tool, including stopping MySQL services, locating data files, and executing repair commands. Additional online repair methods and precautions are also covered to help database administrators effectively resolve such failures. The article discusses potential errors during repair and corresponding countermeasures to ensure data security and system stability.
Problem Background and Cause Analysis
MySQL database tables can become corrupted due to various reasons during operation, common scenarios include sudden power outages, hardware failures, system crashes, or storage media issues. When a table is marked as "crashed" status, MySQL automatically attempts repair, but in some cases automatic repair may fail, requiring manual intervention.
From a technical perspective, table corruption typically manifests as structural integrity damage to index files (.MYI) or data files (.MYD). The MyISAM storage engine uses separate files to store table data and indexes, making file-level repairs possible. Cases from reference articles show that even with standard repair commands, issues like insufficient buffer size or file read errors may occur.
Diagnostic Methods and Preliminary Checks
When encountering table crash errors, first confirm the specific manifestations. Common error messages include:
ERROR 144 - Table './database_name/table_name' is marked as crashed and last (automatic?) repair failed
At this point, table status can be checked with:
myisamchk table_name.MYI
This command outputs detailed table status information, including data record counts, deleted blocks, and specific error descriptions. Output from reference articles shows corrupted tables may display anomalies like "Size of indexfile is: 4729856 Should be: 10240".
Detailed Offline Repair Methods
When MySQL service is running, table files may be locked, preventing repair operations. Offline repair strategy is required:
Step 1: Stop MySQL Service
On Debian/Ubuntu systems:
sudo service mysql stop
On CentOS/RHEL systems:
sudo service mysqld stop
Step 2: Locate Data Directory
Navigate to the specific database folder in MySQL data directory:
cd /var/lib/mysql/database_name
Step 3: Execute Repair Operations
Basic repair command:
myisamchk -r table_name
If basic repair fails, try more forceful repair options:
myisamchk -r -v -f table_name
Where -r means repair, -v means verbose output, -f means force repair.
Advanced Repair Techniques and Error Handling
In complex situations, more specialized repair options may be needed:
Safe Recovery Mode
When standard repair fails, try safe recovery:
myisamchk --safe-recover table_name
This mode uses more conservative algorithms, slower but with higher success rates.
Handling Buffer Size Errors
If encountering "myisam_sort_buffer_size is too small" error, temporarily increase buffer size:
myisamchk --sort_buffer_size=64M -r table_name
Force Repair Options
For severely corrupted tables, multiple force options may be needed:
myisamchk --silent --force table_name.MYI
Avoid using --fast option as it may skip necessary repair steps.
Online Repair Methods
For minor corruption or when service cannot be stopped, try online repair:
Use MySQL built-in REPAIR TABLE command:
REPAIR TABLE table_name;
Or use mysqlcheck tool:
mysqlcheck --repair database_name table_name
Reference articles mention mysqlcheck can repair entire databases:
mysqlcheck --repair --all-databases
But note that online repair may fail due to table locking, especially when tables are in use.
Preventive Measures and Best Practices
To avoid table corruption issues, implement these preventive measures:
Regular Maintenance
Regularly run table checks and optimization:
mysqlcheck --check --all-databases
mysqlcheck --optimize --all-databases
Backup Strategy
Establish comprehensive backup mechanisms including:
- Regular full backups
- Real-time binary log backups
- Testing backup recovery procedures
Monitoring and Alerts
Set up database health monitoring to detect potential issues early:
- Monitor table status changes
- Set error log alerts
- Regularly check disk space and IO performance
Conclusion
MySQL table crash repair is a systematic process requiring appropriate strategies based on specific situations. Offline repair, while requiring service stoppage, typically yields better results; online repair suits minor corruption or scenarios where service cannot be stopped. Regardless of method, data backup before repair is recommended to prevent accidental data loss during the process.
Using methods described in this article, database administrators can effectively address table crash issues, ensuring stable database system operation. Meanwhile, establishing comprehensive prevention and maintenance mechanisms can reduce the probability of such problems occurring from the source.