How to repair corrupted MySQL tables

Step zero: copy the data directory

Every repair tool can lose data. MySQL's manual says to back up a table before REPAIR TABLE, and warns that innodb_force_recovery at 4 or above can corrupt data files for good. With a copy, a step that makes things worse costs nothing: put the files back and try another. Stop the server first, because files copied while mysqld writes them don't match each other.

sudo systemctl stop mysql

sudo du -sh /var/lib/mysql

sudo cp -a /var/lib/mysql /root/mysql-before-repair

On MariaDB the service is mariadb. Check free space with df -h before copying; cp -a keeps owners and permissions. Copy /etc/mysql too, and the binary logs if log_bin points outside the data directory. If the disk may be failing (see the causes below), rsync -a the directory to another machine before anything else.

Find out what broke

The error log names the table and usually the reason. Ubuntu's MySQL writes /var/log/mysql/error.log; MariaDB's Debian and Ubuntu packages log to the systemd journal.

sudo tail -n 100 /var/log/mysql/error.log

sudo journalctl -u mariadb -n 100 --no-pager

The fix depends on the storage engine. Commands here use the MySQL root account, which sudo mysql reaches through the socket on Ubuntu; elsewhere add -u root -p.

SELECT table_name, engine FROM information_schema.tables WHERE table_schema = 'mydb';

If the server won't start, the files in /var/lib/mysql/mydb/ tell you: .ibd is InnoDB, .MYD and .MYI MyISAM, .MAD and .MAI Aria (MariaDB).

Check tables with CHECK TABLE and mysqlcheck

CHECK TABLE mydb.orders;

The last row's Msg_text is OK for a healthy table and Corrupt for a damaged one. On InnoDB, two warnings from the manual: if CHECK TABLE hits a corrupt page, the server exits on purpose so the damage can't spread, and on large tables it can block other sessions (MySQL suggests CHECK TABLE mydb.orders QUICK to avoid semaphore wait timeouts). mysqlcheck runs CHECK TABLE on every table; it needs a running server and read-locks each table while it checks it:

sudo mysqlcheck --all-databases --silent

--silent prints only problems; mysqlcheck mydb orders checks one table. On MariaDB the tool is mariadb-check (mysqlcheck remains as a symlink). To test an InnoDB file page by page, run innochecksum on your copy; it refuses files the server has open and stops at the first page whose checksum doesn't match:

sudo innochecksum /root/mysql-before-repair/mydb/orders.ibd

Repair MyISAM, Aria, ARCHIVE and CSV tables

REPAIR TABLE mydb.orders;

Or check every table and repair whatever fails:

sudo mysqlcheck --all-databases --auto-repair

• --auto-repair repairs corrupted tables after everything has been checked; --repair repairs named tables at once (mysqlcheck --repair mydb orders). Adding --quick rebuilds only the index; --extended is slow and can produce garbage rows.
• On InnoDB both reply The storage engine for the table doesn't support repair and change nothing.
• If the disk fills during REPAIR TABLE, MySQL deletes its temporary files and marks the table as crashed. Check df -h first.

If that fails, or the server won't start, use myisamchk with the server stopped; if mysqld has the table open at the same time, the table can end up corrupted. Run it as the mysql user so new files keep the right owner, and work down the list, checking after each step:

sudo systemctl stop mysql

sudo -u mysql myisamchk --check /var/lib/mysql/mydb/orders.MYI

sudo -u mysql myisamchk --recover --quick /var/lib/mysql/mydb/orders.MYI

sudo -u mysql myisamchk --recover /var/lib/mysql/mydb/orders.MYI

sudo -u mysql myisamchk --safe-recover /var/lib/mysql/mydb/orders.MYI

--recover --quick rebuilds the index without touching the data file; --recover also removes broken and deleted rows; --safe-recover is an older, much slower method for the few cases --recover can't fix. Aria's aria_chk takes the same options, also only with the server stopped; --datadir tells it where Aria's control file is:

sudo -u mysql aria_chk --datadir=/var/lib/mysql --recover /var/lib/mysql/mydb/orders.MAI

Start the server and run CHECK TABLE again to confirm.

InnoDB: rebuild the table

InnoDB can't be repaired in place; you rebuild. If the rows read fine but CHECK TABLE reports index problems, such as a wrong number of entries in a secondary index, a null ALTER TABLE writes a new copy of every row and builds each index again:

ALTER TABLE mydb.orders ENGINE=InnoDB;

OPTIMIZE TABLE mydb.orders does the same on InnoDB (Table does not support optimize, doing recreate + analyze instead). Both run as online DDL: reads and writes continue apart from short locks at the start and end, except on tables with FULLTEXT indexes, which are copied. Allow free space for a second copy of the table, plus sort files in tmpdir. If the rebuild reaches a corrupt data page, the server exits: go to force recovery.

InnoDB: force recovery, dump and reload

When InnoDB crashes on a bad page or won't start, innodb_force_recovery starts it with checks and background work switched off, so you can read the data out. It repairs nothing. Each level includes the ones below it:

Above 0, InnoDB refuses every INSERT, UPDATE and DELETE. If you can dump at 3 or lower, MySQL says you have probably lost only the data on the corrupt pages; try 4 and above on a separate copy first. MariaDB's levels differ slightly (4 changed in 10.6.5); the method is the same. Start at 1:

[mysqld]
innodb_force_recovery = 1

sudo systemctl start mysql

If it crashes again, stop it, raise the number by one and retry. Once it stays up, dump everything to a disk with room:

sudo mysqldump --all-databases --single-transaction --routines --events --triggers --flush-privileges --result-file=/root/rescue.sql

If one table crashes the dump, skip it with --ignore-table=mydb.orders and try it alone; for a bad page mid-table, the manual suggests SELECT * FROM mydb.orders ORDER BY id DESC to read the rows after the damage. With one damaged table, you can dump it, DROP TABLE it (allowed up to level 4), remove the setting, restart and load it back. Otherwise rebuild the instance: move the damaged directory aside and start a fresh one.

sudo systemctl stop mysql

sudo rm /etc/mysql/conf.d/force-recovery.cnf

sudo mv /var/lib/mysql /var/lib/mysql.damaged

sudo install -d -o mysql -g mysql -m 700 /var/lib/mysql

sudo mysqld --initialize-insecure --user=mysql

sudo systemctl start mysql

sudo sh -c 'mysql < /root/rescue.sql'

On MariaDB, initialize with sudo mariadb-install-db --user=mysql. The fresh root account has no password until the dump loads your original accounts, which --flush-privileges puts into effect. Check row counts before the app goes back on.

Find the cause

Fix the table but not the cause, and it breaks again:

• Disk full. Check df -h /var/lib/mysql /tmp. Signs: Got error 28 - 'No space left on device' from storage engine, or Incorrect key file for table '/tmp/#sql...'; try to repair it, which MyISAM temporary tables report when tmpdir fills (MDEV-17307). Free space; a #sql temporary table needs no repair.
• Killed for memory. sudo dmesg -T | grep -i 'killed process' shows Out of memory: Killed process 1234 (mysqld); for an earlier boot use journalctl -k -b -1. InnoDB recovers from a kill on restart; MyISAM tables get marked as crashed. Lower innodb_buffer_pool_size or add memory.
• Power loss or a hard reset. If journalctl -b -1 -n 50 ends with no shutdown messages, the machine went down hard.
• A failing disk. sudo dmesg -T | grep -i 'i/o error'. On physical servers, install smartmontools and run sudo smartctl -H /dev/sda for the drive's own verdict (-a for everything). Cloud virtual disks often report no SMART data; there, kernel I/O errors are the evidence, and the fix is a new volume.

When to stop repairing and restore

• InnoDB only starts at level 4 or higher, or the errors don't name a table (system tablespace, undo or redo damage).
• The kernel logs I/O errors. Whatever you repair on that disk can break again.
• The rescue dump is missing rows that matter.
• Repair is taking longer than a restore would. You only know that if you have timed a restore.

Restore your last mysqldump backup or XtraBackup copy onto a healthy server. If binary logging was on and the logs survived, in your step-zero copy or a binlog backup, replay them up to the crash with point-in-time recovery. Newer rows from the rescue dump can fill gaps once you have checked them.

Common errors

Frequently asked questions

Can REPAIR TABLE fix an InnoDB table?

No. It works on MyISAM, ARCHIVE, CSV and (on MariaDB) Aria tables. For InnoDB, rebuild with ALTER TABLE ... ENGINE=InnoDB, or dump and reload.

What does "Table is marked as crashed and should be repaired" mean?

A MyISAM or Aria table wasn't closed properly, usually because mysqld was killed or the machine lost power. Run REPAIR TABLE, or myisamchk --recover with the server stopped.

Is innodb_force_recovery safe?

Levels 1 to 3 are the safer ones. MySQL warns that 4 and above can corrupt data files permanently. Use it only to dump your data, and remove it afterwards.