对MySQL单个表和批量表转换引擎脚本convert_engine.sh

公司最近的MySQL总是大量的锁表，分析了一下，基本上都是用的MYISAM表引擎，MYISAM在一张表里大量的读写会造成MySQL整张表都锁死

公司最近的MySQL总是大量的锁表，分析了一下，基本上都是用的MYISAM表引擎，MYISAM在一张表里大量的读写会造成MySQL整张表都锁死，而造成动态内容不能及时读数据，，给用户体验带来巨大的影响。INNODB的工作原理只是锁表的单行记录（行锁），不会影响同一张表内的其他行记录。与是写下了以下SHELL脚本，可单个表和整数据库的引擎转换...

#!/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 fi