Mysql检查表进行repair、optimize
来源:互联网 发布:usb端口检测软件 编辑:程序博客网 时间:2024/04/25 12:12
有时候服务器突然断电之类的,然后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);
- }
0 0
- Mysql检查表进行repair、optimize
- 【MySQL】批量检查表并进行repair,optimize
- mysql optimize
- MYSQL 检查表是否存在
- OPTIMIZE/CHECK/REPAIR/ANALYZE TABLE InnoDB Edition
- optimize 优化mysql数据表
- MySQL的optimize
- MySQL OPTIMIZE all tables
- mysql myisam optimize任务
- MySQL Optimize Analysis
- mysql optimize table
- mysql optimize table
- 检查表
- MySql index analysis and optimize
- MySQL中的optimize table命令
- mysql table:analyze、check、optimize
- optimize在mysql中的用法
- optimize在mysql中的用法
- 移动前端开发之viewport的深入理解
- Mac OS X挂载ntfs文件系统
- 如何搭建一个独立博客——简明Github Pages与Hexo教程
- CocoaPods安装和使用教程
- File类
- Mysql检查表进行repair、optimize
- HP实训软件测试第一阶段发现的问题
- 代码
- Can not find the tag library descriptor for "http://java.sun.com/jsp/jstl/core"
- C#学习之多线程开发技术(十一)
- MYISAM表批量压缩
- Activity之间返回结果
- php date 处理
- Log4j1X迁移至Log4j2(基于SLF4j)