How to repair MySQL tables from the Command Line

Sometimes your tables crash and your website is unresponsive. You don’t have access to phpMyAdmin to do a repair, because you can’t access Plesk. That sucks!

When you’re out in the field and all that’s at hand is a trusty SSH connection from your mobile, here’s an alternative way to bring your WordPress Sites back to life.

You need the following ingredients:

• Database Name (I’m using “databasename” here)
• User Name (I’m using “username” here)
• Password for the above

All three will be in your wp-admin.php file, use Vi to check if you’ve forgotten them.

Next it’s time to get busy with some MySQL commands I keep forgetting (hence this article):

mysql -u username -p

This will ask you for your password, which you can type but won’t see written out onscreen. Upon success, you’ll be deep in MySQL territory, not the Linux prompt anymore. Next, we’ll select our actual database in use for the site:

use databasename;

Note the importance of the semicolon at the end of the line. Now let’s see which tables are in that database and what they’re called:

show tables;

Excellent! We’re getting there! You’ll see everything that’s in your database, almost like in phpMyAdmin (just less convenient). By default, you should be seeing entries like “wp_options”, “wp_links”, wp_posts” and so forth. The “wp_” is the default prefix for WordPress tables, but depending on your installation this may have changed to something else.

Depending on what else you’re using your database for, there may well be other entries from projects such as PHPList, Drupal, phpBB and so forth. The principles are applicable for every application that’s using MySQL.

You’ve got a whole host of commands at your fingertips now, but the most important ones for a rescue mission like this are probably

check table yourtablename;

repair table yourtablename;

The “check table” command seems to repair minor errors, like hosts not having closed connections. It also shows you what’s wrong with a table, or if it’s OK. The “repair” command checks, optimizes and repairs a table – so it’s probably the best one to use.

Unlike phpMyAdmin, you have to issue one command per table; you can’t select all tables and say “repair all” (and if you can, I don’t know about it – in which case, please leave a comment and enlighten us).

For WordPress, that’s 11 tables you need to check – so it’s not a major inconvenience.

Once you’re ready to leave the heady heights of MySQL, type “quit” and you’re back to the command prompt.

Good Luck!