SALogs - 专注于Linux技术、Web架构

#!/bin/bash
 
# File          : convert_storage_engine.sh
# Info          : 批量转换表存储引擎,如果忽略-t参数
#                 则转换指定数据库中的所有表。
# Arg1          : -d dbname
# Arg2          : -t [tables]
# Arg3          : -e engine type (myisam | innodb)
# Author        : Cooper
# Site          : http://salogs.com 
# Version       : 2010.05.10 First Release
 
User=root
Pwd=dbpwd
TmpFile="/dev/shm/table.tmp"
MySQLBin="mysql -u$User -p$Pwd -e "
 
function Usage()
{
 echo "Useage:$0 -d dbname [-t table] -e engine_type"
}
 
# 检查用户输入的参数
if [ $# -eq 0 ] ;then
        Usage
        exit 1
fi
 
# 获得命令行参数值，并作相应处理
while getopts t:d:e:h OPTION
do
        case $OPTION in
        d)
           {    # 检查数据库是否存在
                DBName=$OPTARG
                DBExists=`$MySQLBin "show databases;" | grep $DBName`
                if [ "$DBExists" == "" ];then
                   echo "$DBName database not exists!"
                   exit 1
                fi
           }
           ;;
        t)
           {     # 检查表是否存在
                 TableName=$OPTARG
                 TableExists=`$MySQLBin "use $DBName;show tables" | grep $TableName`
                  if [ "$TableExists" == "" ];then
                      echo "$TableName table not exists!"
                      exit 1
                  fi
             }
             ;;
        e)
             EngineName=`echo $OPTARG | tr A-Z a-z `
             if [ $EngineName != "innodb" ] && [ $EngineName != "myisam" ];then
                Usage
                 exit 1
              fi
              ;;
        \?|h)
              Usage
               exit 0
               ;;
        esac
done
 
# 转换表
if [ "$TableName" != "" ];then
{
    CurrentEngine=`$MySQLBin "use $DBName;show table status like \"$TableName\"\G"\
| grep Engine | awk '{print $2}'|tr A-Z a-z`
    if [ $CurrentEngine == $EngineName ];then
        echo "Current Table $Table is already of type $EngineName;Ignored"
        exit 0
    fi
    $MySQLBin "use $DBName;alter table $TableName engine=$EngineName;"
}
else
{
    $MySQLBin "use $DBName;show tables;" | sed 1d >$TmpFile
    while read Table
    do
        CurrentEngine=`$MySQLBin "use $DBName;show table status like '$Table'\G"\
| grep Engine | awk '{print $2}'| tr A-Z a-z`
        if [ $CurrentEngine == $EngineName ];then
           echo "Current Table $Table is already of type $EngineName;Ignored"
           continue
        fi
        $MySQLBin "use $DBName;alter table $Table engine=$EngineName;"
    done<$TmpFile
}
fi
 
rm -rf $TmpFile