# MySQL Table Corrupted: Repair Tables Guide

You see an error like this in your MySQL error log or application:

bash
ERROR 145 (HY000): Table './mydb/users' is marked as crashed and should be repaired

Or:

bash
Incorrect key file for table 'orders'; try to repair it

Or InnoDB errors:

bash
InnoDB: Database page corruption on disk or a failed file read
InnoDB: of table mydb.products

Table corruption can result from hardware failures, power outages, or software bugs. Let me show you how to diagnose and repair corrupted tables.

Understanding Storage Engines

Repair methods differ between storage engines:

  • MyISAM: Uses .MYD (data) and .MYI (index) files. Easier to repair.
  • InnoDB: Uses tablespace files. Has built-in crash recovery. Harder to manually repair.

Check your table's engine:

sql
SHOW TABLE STATUS LIKE 'your_table'\G

Step 1: Check for Corruption

Use CHECK TABLE:

sql
CHECK TABLE mydb.users;
CHECK TABLE mydb.users QUICK;      -- Fast check
CHECK TABLE mydb.users FAST;      -- Check tables not closed properly
CHECK TABLE mydb.users CHANGED;   -- Check tables changed since last check
CHECK TABLE mydb.users MEDIUM;    -- Scan rows for consistency
CHECK TABLE mydb.users EXTENDED;  -- Full row-by-row check

Output will show:

bash
+-------------+-------+----------+---------------------------------------------+
| Table       | Op    | Msg_type | Msg_text                                    |
+-------------+-------+----------+---------------------------------------------+
| mydb.users  | check | error    | Table './mydb/users' is marked as crashed   |
+-------------+-------+----------+---------------------------------------------+

Check all tables in a database:

sql
-- Generate check commands for all tables
SELECT CONCAT('CHECK TABLE ', table_schema, '.', table_name, ';') 
FROM information_schema.tables 
WHERE table_schema = 'mydb';

Or use mysqlcheck from command line:

```bash # Check all tables in a database mysqlcheck -u root -p --check mydb

# Check all databases mysqlcheck -u root -p --check --all-databases

# Check specific table mysqlcheck -u root -p --check mydb users ```

Step 2: Repair MyISAM Tables

Using REPAIR TABLE

```sql -- Standard repair REPAIR TABLE mydb.users;

-- Quick repair (faster, may miss some issues) REPAIR TABLE mydb.users QUICK;

-- Extended repair (slower, more thorough) REPAIR TABLE mydb.users EXTENDED;

-- Use temporary file (for serious corruption) REPAIR TABLE mydb.users USE_FRM; ```

Using myisamchk

Stop MySQL first for safety:

bash
sudo systemctl stop mysql

Run myisamchk on the table files:

```bash # Check table myisamchk /var/lib/mysql/mydb/users

# Check with extended options myisamchk -e /var/lib/mysql/mydb/users.MYI

# Repair table myisamchk -r /var/lib/mysql/mydb/users.MYI

# Safe recovery (for serious corruption) myisamchk -o /var/lib/mysql/mydb/users.MYI

# Force recovery with sort myisamchk -r -q /var/lib/mysql/mydb/users.MYI ```

Restart MySQL:

bash
sudo systemctl start mysql

Using mysqlcheck

```bash # Repair all tables in database mysqlcheck -u root -p --repair mydb

# Repair all databases mysqlcheck -u root -p --repair --all-databases

# Optimize after repair mysqlcheck -u root -p --optimize mydb ```

Step 3: Repair InnoDB Tables

InnoDB has built-in crash recovery and is more resistant to corruption. However, when corruption occurs, it's more complex to repair.

Force InnoDB Recovery

Edit /etc/mysql/my.cnf:

ini
[mysqld]
innodb_force_recovery = 1

Recovery levels: - 1-2: Safe, minimal impact - 3: More aggressive, may have some data loss - 4-5: Aggressive, can cause data corruption - 6 (SRV_FORCE_NO_LOG_REDO): Most aggressive, significant data loss possible

Start with 1 and increase if needed:

bash
sudo systemctl restart mysql

At recovery level 1, try to dump data:

```sql -- Check tables CHECK TABLE mydb.users;

-- Try to select data SELECT * FROM mydb.users LIMIT 100;

-- Create backup CREATE TABLE mydb.users_backup LIKE mydb.users; INSERT INTO mydb.users_backup SELECT * FROM mydb.users; ```

Dump the database:

bash
mysqldump -u root -p mydb > mydb_backup.sql

If dump succeeds, recreate tables:

```sql DROP TABLE mydb.users;

-- Then restore from backup SOURCE mydb_backup.sql; ```

Disable recovery mode:

ini
[mysqld]
# Remove or comment out
# innodb_force_recovery = 1
bash
sudo systemctl restart mysql

Check InnoDB Table Status

```sql -- Check InnoDB status SHOW ENGINE INNODB STATUS\G

-- Look for corruption indicators -- Check the "LATEST DETECTED DEADLOCK" and "LATEST FOREIGN KEY ERROR" sections ```

Rebuild InnoDB Tables

If a specific table is corrupted:

```sql -- Method 1: ALTER TABLE rebuild ALTER TABLE mydb.users ENGINE=InnoDB;

-- Method 2: Dump and restore -- In bash: mysqldump -u root -p mydb users > users.sql

-- In MySQL: DROP TABLE mydb.users; SOURCE users.sql;

-- Method 3: Create new table and copy CREATE TABLE mydb.users_new LIKE mydb.users; INSERT INTO mydb.users_new SELECT * FROM mydb.users; DROP TABLE mydb.users; RENAME TABLE mydb.users_new TO mydb.users; ```

Step 4: Handle Specific Corruption Types

Index Corruption

```sql -- Check indexes ANALYZE TABLE mydb.users;

-- Rebuild indexes ALTER TABLE mydb.users ENGINE=InnoDB;

-- For MyISAM REPAIR TABLE mydb.users; ```

Table Marked as Crashed

```sql -- For MyISAM REPAIR TABLE mydb.users;

-- For InnoDB -- Try restarting MySQL first -- Then check table CHECK TABLE mydb.users; ```

Incorrect Key File

```sql -- MyISAM REPAIR TABLE mydb.users USE_FRM;

-- If that fails, use myisamchk -- Stop MySQL -- myisamchk -r -o /var/lib/mysql/mydb/users.MYI ```

Auto-Increment Corruption

```sql -- Check current auto-increment value SHOW TABLE STATUS LIKE 'users'\G

-- Reset auto-increment ALTER TABLE mydb.users AUTO_INCREMENT = 1000;

-- Find max value and set SELECT MAX(id) + 1 FROM mydb.users; ALTER TABLE mydb.users AUTO_INCREMENT = [max_value + 1]; ```

Step 5: Recover from Backups

If corruption is severe and repair fails:

Restore from mysqldump:

```bash # Restore specific database mysql -u root -p mydb < mydb_backup.sql

# Restore specific table mysql -u root -p mydb < users_backup.sql ```

Restore from binary logs (point-in-time recovery):

```bash # Find last backup position grep "CHANGE MASTER" mydb_backup.sql

# Apply binlog from that position mysqlbinlog --start-position=107 mysql-bin.000123 | mysql -u root -p

# Or for time-based recovery mysqlbinlog --start-datetime="2026-04-04 10:00:00" \ --stop-datetime="2026-04-04 12:00:00" \ mysql-bin.000123 | mysql -u root -p ```

Restore from physical backup (Percona XtraBackup):

```bash # Prepare backup xtrabackup --prepare --target-dir=/backup/mysql

# Restore xtrabackup --copy-back --target-dir=/backup/mysql

# Fix permissions chown -R mysql:mysql /var/lib/mysql ```

Step 6: Prevent Corruption

Regular Backups

```bash # Daily full backup mysqldump -u root -p --single-transaction --all-databases > /backup/mysql_$(date +%Y%m%d).sql

# Binary logging for point-in-time recovery # In my.cnf: [mysqld] log_bin = mysql-bin binlog_format = ROW expire_logs_days = 7 ```

Regular Checks

```bash # Weekly table check mysqlcheck -u root -p --check --all-databases

# Add to crontab # 0 3 * * 0 /usr/bin/mysqlcheck -u root -pPASSWORD --check --all-databases ```

Hardware Considerations

```bash # Check disk for errors sudo badblocks -v /dev/sda

# Check filesystem sudo fsck -f /dev/sda1

# Monitor disk health sudo smartctl -a /dev/sda ```

Configuration for InnoDB Reliability

```ini [mysqld] # Data integrity innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_commit = 1 innodb_doublewrite = 1

# Recovery settings innodb_purge_threads = 1 innodb_max_dirty_pages_pct = 75 ```

Step 7: Verify Repair

After repair, verify the table:

```sql -- Check table CHECK TABLE mydb.users EXTENDED;

-- Verify row count SELECT COUNT(*) FROM mydb.users;

-- Test operations INSERT INTO mydb.users (name, email) VALUES ('test', 'test@example.com'); SELECT * FROM mydb.users WHERE name = 'test'; DELETE FROM mydb.users WHERE name = 'test';

-- Run analyze ANALYZE TABLE mydb.users;

-- Optimize OPTIMIZE TABLE mydb.users; ```

Emergency Recovery Workflow

When corruption strikes:

  1. 1.Don't panic - Assess the damage first
  2. 2.Stop writes - Set database read-only if possible
  3. 3.Check tables - Identify which tables are corrupted
  4. 4.Try repair - Use REPAIR TABLE or appropriate repair method
  5. 5.Backup data - Dump what you can recover
  6. 6.Force recovery - Use innodb_force_recovery if needed
  7. 7.Restore - From backup if repair fails
  8. 8.Verify - Check data integrity after recovery

Quick Reference Commands

```sql -- Check table CHECK TABLE tablename EXTENDED;

-- Repair MyISAM REPAIR TABLE tablename;

-- Repair InnoDB (rebuild) ALTER TABLE tablename ENGINE=InnoDB;

-- Force recovery -- Add to my.cnf: innodb_force_recovery = 1

-- Dump table -- mysqldump -u root -p db table > table.sql ```

```bash # Check all tables mysqlcheck -u root -p --check --all-databases

# Repair all MyISAM tables mysqlcheck -u root -p --repair --all-databases

# myisamchk repair myisamchk -r /var/lib/mysql/db/table.MYI ```

Table corruption is stressful, but with proper backups and these repair techniques, you can recover. The key is to have regular backups and test your recovery procedures before you need them.