MySQL Table Corruption - Automatic Maintenence

Whether you're running WordPress or any other software powered by MySQL, you are relying on MySQL tables always being available and problem free.

Unfortunately MySQL table corruption is a fact of life. 

Network latency, server shutdown, hard drive running out of space and memory defragmentation are just some causes of MySQL table corruption.

A corrupted MySQL table could render your site useless, lead to loss of revenues and basically make your life a living hell.

MySQL offers two command-line tools to check for table corruption:

* mysqlcheck - uses the SQL statements CHECK TABLE, ANALYZE TABLE, and OPTIMIZE TABLE. To invoke mysqlcheck for checking all of your databases you run: mysqlcheck --all-databases

* myisamchk - runs on the raw MySQL files, bypassing the server, myisamchk is faster but unlike mysqlcheck cannot be executed while the server is running.


--

The worst part about MySQL table corruption is that you're often not going to know about it until your customers tell you or until it's already too late to restore yesterday's backup.

As part of this post I'd like to share how we automate MySQL table maintenance. 

We will be running mysqlcheck in the background, twice a day, check for any problems and email the administrator in case any tables are corrupted.

Since our script will run on a live database we have to use 'mysqlcheck' and not 'myisamchk'.


PHP Code for the script:

$username = "your_mysql_server_username";$password = "your_mysql_server_password";$host = "localhost"; // your mysql server ip address$adminemail = "you@domain.com";// No time limitset_time_limit(0);// Init$error_str = "";// Prepare command$command = "mysqlcheck -h$host -u$username -p$password --all-databases";// Run commandif (!exec($command, &$return_val)) $error_str = "Failed to execute command: $command";// Iterate through return_val, line by line, looking for any errorsfor ($i=0; $i{
  $line = $return_val[$i];

  // Split output to tablename and result code
  $line = str_replace(":", " ", $line)." ";
  $pos = strpos($line, " ");
  if ($pos<1) continue;
  $tablename = trim(substr($line, 0, $pos));
  $resultcode= trim(substr($line, $pos+1));
  while (strlen($resultcode)>0 && $resultcode[0]==' ') $resultcode =substr($resultcode,1);

  // If result code is not 'OK', add details to error_str
  if (Strcasecmp($resultcode,'ok')!=0)
  {
    $error_str .= $line."\\r\\n";
    $i++;
    if ($i
    {
      $line = $return_val[$i];
      $error_str .= $line."\\r\\n";
    }
  }
}// If we have any output, email administratorif (!empty($error_str))
{mail($adminemail, "MySQL Table Corruption", $error_str, "From: support@yourdomain.com");
}

Now all that's left is setting up the cronjob to run twice a day -

# Check MySQL table corruption
0 0,10 * * * php dbcheck.php