鸿 网 互 联 www.68idc.cn

当前位置 : 服务器租用 > 服务器相关 > 批处理 > >

[转]对MySQL单个表和批量表转换引擎脚本

来源:互联网 作者:佚名 时间:2016-03-03 12:15
公司最近的MySQL总是大量的锁表,分析了一下,基本上都是用的MYISAM表引擎,MYISAM在一张表里大量的读写会造成MySQL整张表都锁死, 而造成动态内容不能及时读数据,给用户体验带来巨大的影响。INNODB的工作原理只是锁表的单行记录(行锁),不会影响同一张表
公司最近的MySQL总是大量的锁表,分析了一下,基本上都是用的MYISAM表引擎,MYISAM在一张表里大量的读写会造成MySQL整张表都锁死, 而造成动态内容不能及时读数据,给用户体验带来巨大的影响。INNODB的工作原理只是锁表的单行记录(行锁),不会影响同一张表内的其他行记录。于是写下了以下SHELL脚本,可单个表和整数据库的引擎转换... 存储引擎 MySQL
#!/bin/sh

# Arg1          : -d dbname
# Arg2          : -t [tables]
# Arg3          : -e engine type (myisam | innodb)

User="root"
Pwd="666666"
MYSQLbin="/usr/local/mysql/bin/mysql -u$User -p$Pwd -e"
TmpFile='/tmp/table.tmp'
Usage()
{
    echo "Usage():$0 -d dbname [-t tbname] -e engine( myisam | innodb )"
}
if [ $# -eq 0 ];then
   Usage
   exit 1
fi
while getopts d:t: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)
 {
    TBName=$OPTARG
    TBExists=`$MYSQLbin "use $DBName;show tables"|grep $TBName`
    if [ "$TBExists" == "" ];then
        echo "$TBName table not exists!"
        exit 1
    fi
 };;
        e)
         {
   EngineName=`echo $OPTARG|tr A-Z a-z`
   if [ "$EngineName" != "myisam" ] && [ "$EngineName" != "innodb" ];then
          Usage
  echo "Engine $EngineName is no exists!"
  exit 1
          fi
   };;
 ?|h)
     Usage
     exit 0
   ;;
 esac
done

        if [ "$EngineName" == "" ];then
                Usage
                echo "Lose '-e (innodb | myisam)'!"
                exit 1
        fi

if [ "$TBName" != "" ];then
 CurrentEngine=`$MYSQLbin "use $DBName;show table status like '$TBName'\G"|grep Engine|awk '{print $2}'|tr A-Z a-z`
 if [ "$CurrentEngine" == "$EngineName" ];then
     echo -e "\033[31m Current Table $TBName is already of type $EngineName;Ignored! \033[0m"
     exit 0
 fi
        $MYSQLbin "use $DBName;alter table $TBName 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 -e "\033[31m Current Table $Table is already of type $EngineName;Ignored! \033[0m"
    else
        $MYSQLbin "use $DBName;alter table $Table engine=$EngineName;"
        echo -e "\033[32m $DBName Table $Table Convert $EngineName is  Sucessfull! \033[0m"
#  continue
           fi
 done  < $TmpFile
fi
网友评论
<