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):

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:

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

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!

About Jay Versluis

Jay is a medical miracle known as a Super Survivor. He runs two YouTube channels, five websites and several podcast feeds. To see what else he's up to, and to support him on his mission to make the world a better place, check out his Patreon Campaign.

14 thoughts on “How to repair MySQL tables from the Command Line

  1. Some of my tables in my chat room db crashed. I was given the following command to repair all tables at once. The developer of my chat software gave it to me and said it worked.

    mysqlcheck -u root -p –auto-repair –check –optimize –all-databases

    It didn’t work. It may work with phpadmin but using the mysql command prompt it doesn’t work.

    The good news for me is that your commands worked great and fixed my chat room db problems.

    Thanks a bunch.

    1. I’m glad to hear it!

      The command you’ve mentioned probably doesn’t work because there may be a password associated with the root user. Say if your password is “jeronimo” then the command needs to look like this – notice the absence of a space in between “-p” and “jeronimo”:

      mysqlcheck -u root -pjeronimo –auto-repair –check –optimize –all-databases

      Thanks for sharing 😉

      1. The Issues with the command is it needs to be linke this

        mysqlcheck -u root -p –auto-repair –check –optimize –all-databases

        Note the Double dash

  2. Excellent post. Nope, this isn’t spamm-o-rama, but a sincere thanks. I did have to ensure that I put a — in front of all the mysqlcheck options command. I was running over 100% cpu (didn’t know that that was possible, lol), but it was. Seems to have help!

    Thanks, Frank

Leave a Reply to Sakvith Cancel reply