Repair and Optimize a MySQL Database

Repair or optimize: which one the table needs

Run CHECK TABLE first. OPTIMIZE TABLE rebuilds a table and reclaims space, REPAIR TABLE only works on MyISAM and ARCHIVE, and ANALYZE TABLE just refreshes index statistics. In phpMyAdmin, select the database, tick the tables, then pick Check or Optimize from the With selected menu.

A slow dashboard is not corruption. If pages load slowly but the data reads correctly, optimize or add an index; repair changes nothing on a table that already passes the check.

Back up the database before either command

Both commands rewrite files on disk. Take a dump first, then read the size before you trust it.

mysqldump -u user -p --single-transaction --routines dbname | gzip > dbname-2026-10-29.sql.gz

--single-transaction gives InnoDB a consistent snapshot without holding a lock for the whole dump. In cPanel, Backup, Download a Full Account Backup does the same job from the panel, and the Export tab in phpMyAdmin works for a single database.

Confirm the file before running anything: gzip -l dbname-2026-10-29.sql.gz should show a size close to the database size, and zcat dbname-2026-10-29.sql.gz | head -20 should show CREATE TABLE lines. Copy it off the server, because a dump stored in the same account leaves with the account.

Repair tables in phpMyAdmin

phpMyAdmin is the shortest route when the database is small and SSH is not open.

1. Log in to cPanel, then phpMyAdmin. The client area where you pay invoices is a separate login.
2. Click the database name in the left column, then tick the tables. Use Select all when the whole database is suspect.
3. From the With selected menu at the bottom, choose Check table and read the Msg_text column.
4. If a MyISAM table reports an error, repeat the selection with Repair table.
5. When the repair returns OK, run Optimize table on the same selection to reclaim the space.
6. Reload the site. It is fixed when the page loads and a second Check table returns OK for every row.

phpMyAdmin clears the selection after each action, so re-tick the tables every time. A repair on a 1 GB MyISAM table can run for minutes; leave the tab open until the result table appears.

Run mysqlcheck from the command line

mysqlcheck takes one flag per job and runs over SSH or the terminal in the hosting panel.

• mysqlcheck -c dbname -u user -p checks every table in one database.
• mysqlcheck -r dbname -u user -p repairs the tables that failed the check.
• mysqlcheck -o dbname -u user -p optimizes, which is the rebuild.
• mysqlcheck -a dbname -u user -p refreshes statistics after a large import.
• mysqlcheck -o --all-databases -u root -p runs the pass over every database on the server.

Add --auto-repair to a check run to fix what it finds in the same pass. Each table is locked while it is processed, so a server-wide run belongs in a maintenance window, not at noon.

On WordPress, the wrapper reads the credentials from wp-config.php: wp db check and wp db optimize call the same tools without the flags.

InnoDB versus MyISAM after a crash

InnoDB has been the default engine since MySQL 5.5. It keeps one .ibd file per table, recovers from its redo log after a crash, and refuses REPAIR TABLE because it either recovers itself or needs a restore. MyISAM has no crash recovery, so a power cut can leave a table marked as crashed, and REPAIR TABLE can rebuild it by dropping rows it cannot read.

Converting to InnoDB is the durable fix for a table that keeps crashing. Run the ALTER in a quiet hour: it rebuilds the table and needs free disk space roughly the size of the table.

How to tell a table is really corrupted

These messages mean damage. A slow admin page, a timeout or a failed plugin does not.

• "Table is marked as crashed and should be repaired" — a MyISAM table; run repair once.
• Error 145 or 1194 — table marked as crashed, or corrupt.
• "Incorrect key file for table" — index damage, usually MyISAM.
• "Database page corruption on disk or a failed file read" — InnoDB, restore.
• CHECK TABLE returns Error or Corrupt in the Msg_text column.

If CHECK returns OK and the site still fails, the fault may be a full disk, a dropped connection or a missing table. Compare SHOW TABLES; with the table prefix in wp-config.php before you repair anything.

When to restore instead of repair

Restore when a second repair fails, when the message names an InnoDB page or checksum, or when the table opens but rows are missing. A restore replaces the table with the last dump, so orders and posts written after that dump disappear.

1. Copy the damaged table out: mysqldump -u user -p dbname wp_posts > wp_posts-broken.sql.
2. Rename the live table so the site fails cleanly instead of half-reading it: RENAME TABLE wp_posts TO wp_posts_old;.
3. Import the dump with mysql -u user -p dbname < dbname-2026-10-29.sql, or wp db import dbname-2026-10-29.sql.
4. Compare row counts with the old table, then drop the copy once the site has run a full day.

A single-table restore is possible: pull that table's CREATE and INSERT lines from the dump, import only those, and leave the rest of the database untouched.

Keeping tables small on WebJi hosting

Optimize returns space after deletes but does not stop the growth. On WordPress, revisions, expired transients and autoloaded options are the usual weight in wp_posts and wp_options; the wp_options cleanup guide covers that cleanup, which is a different job from repair.

Our Linux hosting plans include phpMyAdmin, SSH and the MySQL users these commands need. If the check output is not clear, email support@webji.services with the database name, the error text and the time it started; support runs 24/7 in Hindi and English and the first reply usually lands within the hour. The office phone is not a support line.