|
|
| ||||||||||||||
 |
MySQL Table Maintenance Automation
Mike Peters, 09-16-2007Whether 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'.
Now all that's left is setting up the cronjob to run twice a day -
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
$username = "your_mysql_server_username";
$password = "your_mysql_server_password";
$host = "localhost"; // your mysql server ip address
$adminemail = "you@domain.com";
// No time limit
set_time_limit(0);
// Init
$error_str = "";
// Prepare command
$command = "mysqlcheck -h$host -u$username -p$password --all-databases";
// Run command
if (!exec($command, &$return_val)) $error_str = "Failed to execute command: $command";
// Iterate through return_val, line by line, looking for any errors
for ($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 administrator
if (!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
0 0,10 * * * php dbcheck.php
Subscribe Now to receive new posts via Email as soon as they come out.
Post your comments
About us | Contact us | Privacy | Terms & Conditions | Affiliates
Thursday, July 24th, 2008 Page generated in 0.293 seconds | |