Knowledgebase

If your site shows database errors, a WordPress page says a table "is marked as crashed", or the database has grown big and sluggish, cPanel and phpMyAdmin have tools to check, repair and optimize a MySQL database. This guide explains what each one actually does, how to run them safely, and when they won't help.

Check, repair and optimize: what's the difference?

One thing to know up front: most modern websites store their tables with the InnoDB engine, which protects itself from damage much better than the older MyISAM engine. Classic "repair" only really applies to MyISAM (and Aria) tables. For InnoDB tables, a repair request just reports that the engine doesn't support it -- that's not a failure. You can see which engine each table uses in phpMyAdmin's Structure tab, in the Type column.

Before you start: make a copy

Checking is harmless, but repairing a badly damaged table can lose the rows that couldn't be saved, and optimizing locks each table while it's rebuilt. Take a copy first:

1. In cPanel, go to Databases > phpMyAdmin.
2. Click the database in the left panel, then the Export tab, and click Export.

If the export itself fails because of a damaged table, don't worry -- your account is backed up automatically by JetBackup 5, and you can restore the database from there if a repair goes wrong.

Not sure which database your site uses? For WordPress, look at the DB_NAME line in wp-config.php (Files > File Manager, right-click the file, View).

How to check a database in cPanel

1. Log in to cPanel (from the client area: Services > My Services, choose your plan, then Log in to cPanel).
2. Go to Databases > Manage My Databases.
3. Scroll to Modify Databases.
4. Choose your database from the Check Database menu and click Check Database.
5. Read the result. Check Complete means no problems were found. If a table is damaged, its name is listed.
6. Click Go Back.

How to repair a database in cPanel

1. In Manage My Databases, scroll to Modify Databases.
2. Choose the database from the Repair Database menu and click Repair Database.
3. Wait for the results. Repair Complete means cPanel fixed what it could. Otherwise it shows which table is still a problem.
4. Click Go Back, then run Check Database again to confirm.
5. Load your website and test it.

cPanel's repair works on the whole database at once. To repair just one table, use phpMyAdmin instead.

How to check, repair or optimize individual tables in phpMyAdmin

cPanel doesn't have an optimize button, so this is also where you optimize.

1. In cPanel, go to Databases > phpMyAdmin and click your database in the left panel.
2. On the Structure tab, you'll see every table with its size and Overhead (wasted space, shown for MyISAM tables).
3. Tick the tables you want, or tick Check all at the bottom. A handy shortcut: Check tables having overhead selects only those with wasted space.
4. From the With selected menu, choose Check table, Repair table or Optimize table.
5. phpMyAdmin runs it immediately and shows a results table with one row per table.

Reading the results

• status | OK -- the table is fine.
• Table is already up to date -- nothing needed doing.
• The storage engine for the table doesn't support repair -- the table is InnoDB. This is normal; see below.
• Table does not support optimize, doing recreate + analyze instead -- also normal for InnoDB. The table was rebuilt, which has the same effect as optimizing.
• error | Table ... is marked as crashed or Incorrect key file -- a MyISAM table is damaged. Run Repair table on it.

Using SSH instead (big databases)

For large databases, the command line is faster and won't time out in the browser. Open Advanced > Terminal in cPanel (or connect with SSH) and use mysqlcheck with a database user that's been added to the database:

# Check every table (read-only)
mysqlcheck -u username_dbuser -p --check username_dbname

# Repair (MyISAM/Aria tables)
mysqlcheck -u username_dbuser -p --repair username_dbname

# Optimize every table
mysqlcheck -u username_dbuser -p --optimize username_dbname

Enter the database user's password when asked. For WordPress, WP-CLI does the same using the details in wp-config.php:

cd ~/public_html
wp db check
wp db repair
wp db optimize

Should you optimize regularly?

Not really. On InnoDB tables, optimizing mostly shrinks tables after big deletions. It won't make a healthy site noticeably faster, and on large tables it locks each table while it's rebuilt, so visitors may see slow pages or errors for a moment. Good times to optimize:

• After deleting thousands of post revisions, spam comments or expired transients.
• After a plugin that logged heavily (security, redirection or email log plugins) has been cleaned out.
• When phpMyAdmin shows large overhead on MyISAM tables.

Do it at a quiet time of day. For real speed gains, turning on LiteSpeed Cache does far more than optimizing tables (see the related guides).

WordPress: clean up first, then optimize

Optimizing only helps once the junk is gone. With WP-CLI (from Advanced > Terminal, in your WordPress folder) you can see and clear the usual suspects. Export the database first.

cd ~/public_html
wp db export ~/before-cleanup.sql

# How many post revisions are stored?
wp post list --post_type=revision --format=count

# Remove expired transients (temporary cached data)
wp transient delete --expired

# Empty the spam and trash comment folders
wp comment delete $(wp comment list --status=spam --format=ids) --force
wp comment delete $(wp comment list --status=trash --format=ids) --force

# Then rebuild the tables
wp db optimize

If a comment delete line says there's nothing to delete, that folder was already empty. To limit how many revisions WordPress keeps in future, add define( 'WP_POST_REVISIONS', 10 ); to wp-config.php above the "That's all, stop editing" line. Deleting existing revisions is best done with a well-known cleanup plugin, or LiteSpeed Cache's Database > Manage page, which can clean revisions, transients and spam and then optimize in a couple of clicks.

Troubleshooting

WordPress says "One or more database tables are unavailable"

WordPress has spotted a damaged table. Run Repair Database in cPanel first. WordPress also has its own repair page, but you'd need to add define( 'WP_ALLOW_REPAIR', true ); to wp-config.php, visit https://example.com/wp-admin/maint/repair.php, and then remove the line straight away -- that page works without logging in, so anyone could use it while it's switched on.

Repair ran but the error is still there

The table may be too badly damaged to rebuild, or the problem isn't damage at all. Restore the database from JetBackup 5, choosing a date before the problem started. If the error mentions an InnoDB table, open a ticket with the exact message -- InnoDB damage needs to be looked at on the server side.

"Error establishing a database connection" -- will repair fix it?

Rarely. That error is almost always a wrong database name, user or password in wp-config.php, not a damaged table. See the database connection guide in the related links.

Optimize didn't make the database smaller

Optimize only reclaims space from deleted rows. If the data is still there, the size stays the same. Find the biggest tables on phpMyAdmin's Structure tab (click the Size heading to sort) and clean up the data inside them -- often a plugin's log or statistics table.

The page timed out while optimizing

Optimize a few tables at a time in phpMyAdmin, or use the mysqlcheck command over SSH, which isn't limited by a web timeout.

Common questions

Is it safe to run Check Database on a live site?

Yes. It only reads the tables. On a very large database it may briefly slow the site, so pick a quiet time.

Why did a table get damaged in the first place?

For MyISAM tables, a crash or interrupted write is the usual cause. Converting old MyISAM tables to InnoDB makes this far less likely. Ask your developer, or open a ticket if you'd like advice for your site.

Can I restore just one table from a backup?

Not directly in JetBackup 5. Download the database backup, pull out the table you need and import it with phpMyAdmin -- or open a ticket and we'll help.

Related guides

• How to Restore a MySQL Database with JetBackup 5
• How to Fix "Error Establishing a Database Connection" in WordPress
• How to Import and Export a Database with phpMyAdmin
• How to Speed Up WordPress with LiteSpeed Cache
• How to Use SSH, SSH Keys and Terminal in cPanel

Still stuck? Open a support ticket and the Instant Access Internet Services team will help.