Mysql检查表进行repair、optimize

最新推荐文章于 2024-04-18 07:01:44 发布

zhs2014150551

最新推荐文章于 2024-04-18 07:01:44 发布

阅读量539

点赞数

分类专栏：数据库文章标签： mysql

数据库专栏收录该内容

5 篇文章

订阅专栏

本文提供了两种自动修复MySQL损坏表的方法：一种是Shell脚本，用于检查并修复指定数据库中的所有表；另一种是PHP脚本，通过监控从属服务器状态来发现并修复问题表。

有时候服务器突然断电之类的，然后mysql表坏掉了，可能还不止一个，再然后repair了一个又一个，然后手动去repair是肯定很不理想的，还是用脚本解决吧：

Shell脚本如下：

#!/bin/bash
 
host_name=192.168.10.3
user_name=lucky
user_pwd=123.com 
database=my_db_name
need_optmize_table=true
tables=$(mysql -h$host_name -u$user_name -p$user_pwd $database -A -Bse "show tables")
 
for table_name in $tables
do
  check_result=$(mysql -h$host_name -u$user_name -p$user_pwd $database -A -Bse "check table $table_name" | awk '{ print $4 }')
  if [ "$check_result" = "OK" ]
  then
    echo "It's no need to repair table $table_name"
  else
    echo $(mysql -h$host_name -u$user_name -p$user_pwd $database -A -Bse "repair table $table_name")
  fi
 
  # 优化表,可提高性能
  if [ $need_optmize_table = true ]
  then
    echo $(mysql -h$host_name -u$user_name -p$user_pwd $database -A -Bse "optimize table $table_name")
  fi
done

Php脚本如下：

<?php
while(1){
    $link = mysql_connect('192.168.10.3:3306','user','password');
    $sql = "show slave status";
    $result = mysql_query($sql,$link);
    $row = mysql_fetch_assoc($result);
    $error_no = $row['Last_SQL_Errno'];
    if($error_no == 0)
    {
        sleep(10);
         continue;
    }
    $string = $row['Last_SQL_Error'];
    $Table = preg_match('/Table/',$string);
    $Duplicate = preg_match('/*.Duplicate.*/i',$string);
    $Incorrect = preg_match('/Incorrect key file for table/i',$string);
    if($Incorrect){
        $pattern_db = '/.*Incorrect.*.\/(\w+)\/(\w+).*/';
        preg_match($pattern_db,$string,$match);
        $db=$match[1];
        $table=$match[2];
    } 
    if($Table){
        $pattern = '/.*database:\s\'(\w+).*Query:\s\'\w+\s(\w+).*/';
        preg_match($pattern,$string,$match);
        $db = $match[1];
        $pattern = '/.*Table\s\'(\w+).*Query:\s\'\w+\s\w+\s(\w+).*/';
        preg_match($pattern,$string,$match);
        $table = $match[1];
    } else{
        $update = preg_match('/*.update.*/i',$string);
        if($update){
            $pattern = '/.*database:\s\'(\w+).*Query:\s\'\w+\s(\w+).*/';
        }else{
            $pattern = '/.*database:\s\'(\w+).*Query:\s\'\w+\s\w+\s(\w+).*/';
        }
        preg_match($pattern,$string,$match);
        $db = $match[1];
        $table = $match[2];
    }
    $repair_sql = "repair table $db.".$table;
 var_dump($repair_sql);
    mysql_query($repair_sql,$link);
    mysql_query("start slave",$link);
}