Skip to content

Running mysqlcheck to repair and optimize databases on cPanel without locking the host

mysqlcheck is the right tool for a single damaged InnoDB table — and the wrong tool for a "weekly optimize all" cron. Knowing when to run it and what it actually does saves an outage.

Some cPanel hosts ship with a cron that runs mysqlcheck --all-databases --optimize every Sunday. Whoever set this up was thinking in MyISAM terms — back when an OPTIMIZE actually reclaimed space and a REPAIR fixed a damaged table. On a modern InnoDB host, the same cron locks every table on the server for the duration of the run, frequently rebuilds tables that did not need rebuilding, and on a busy host produces a Sunday-night outage that nobody connects to the cron three hours later. mysqlcheck is still useful, but for specific situations, not as a scheduled chore.

What’s happening

mysqlcheck is a CLI front-end for CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE. Behaviour depends on storage engine:

  • MyISAM: REPAIR actually repairs damaged indexes. OPTIMIZE reclaims fragmented space. Useful, but you should not be running MyISAM in 2026.
  • InnoDB: REPAIR does nothing useful — InnoDB’s redo log handles crash recovery, and a damaged table needs innodb_force_recovery or a restore, not REPAIR. OPTIMIZE is internally translated to ALTER TABLE ... ENGINE=INNODB, which rebuilds the entire table. For a 50GB table, that’s a multi-hour operation that holds an exclusive lock for the entire duration on older MySQL, or a shared metadata lock with online DDL on newer versions.
  • ANALYZE: updates the optimizer’s row-distribution statistics. Cheap, safe, sometimes worth running after a large bulk delete.

So the right answer is engine-specific, and “run it weekly on everything” was never the right answer.

How to verify

mysql -e "SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, DATA_FREE/1024/1024 AS free_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys') ORDER BY DATA_FREE DESC LIMIT 20"
mysqlcheck --check --all-databases | grep -v OK
crontab -l | grep mysqlcheck
ls /etc/cron.weekly/ /etc/cron.d/ | xargs grep -l mysql 2>/dev/null

The first query identifies tables with significant free space — candidates for an OPTIMIZE if (and only if) you’re freeing real space. The second runs a non-locking check on everything and reports only tables that aren’t OK. The last two find any scheduled cron entry that might be running OPTIMIZE in your sleep.

The fix

  1. Remove any “OPTIMIZE all databases weekly” cron. It’s almost always doing more harm than good on InnoDB hosts. If you genuinely have a fragmented InnoDB table, OPTIMIZE it explicitly during a maintenance window.
  2. Use mysqlcheck --check to identify damaged tables, not to repair them: mysqlcheck --check --all-databases | grep -v OK. A table reporting Table is marked as crashed is a candidate for REPAIR (MyISAM) or restore-from-backup (InnoDB).
  3. For a single damaged InnoDB table, do not run REPAIR. Restore the specific table from the most recent backup, or dump it with mysqldump --single-transaction, drop it, recreate it, reimport. REPAIR on InnoDB is a placebo.
  4. For a fragmented InnoDB table (DATA_FREE in the 20% range and the table is read-heavy), schedule an OPTIMIZE during a maintenance window and run it on the single table: mysql -e "OPTIMIZE TABLE acmecorp_wp.wp_options". With MySQL 5.6+ or MariaDB 10.0+, this uses online DDL and is non-blocking for SELECT but still blocking for write — schedule accordingly.
  5. Use ANALYZE freely. mysqlcheck --analyze --all-databases is cheap and updates optimizer statistics. Worth running after large bulk inserts or deletes. Does not lock anything significant.

For environments where database maintenance is part of the ongoing service, Stack Harbor’s environment management team builds the per-table maintenance schedule into the platform and removes the inherited “weekly optimize all” crons that show up on takeover.