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

公司最近的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
        fiif [ "$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 "33[31m Current Table $TBName is already of type $EngineName;Ignored！ 33[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 "33[31m Current Table $Table is already of type $EngineName;Ignored！ 33[0m"
    else
        $MYSQLbin "use $DBName;alter table $Table engine=$EngineName;"
        echo -e "33[32m $DBName Table $Table Convert $EngineName is Sucessfull！ 33[0m"
# continue
           fi
done < $TmpFile
fiMySQL 5.0存储过程学习笔记MySQL的主从复制Replication之MyIsam和InnoDB数据复制发布相关资讯 mysql

数据库服务器 MySQL （08/15/2013 06:50:23）
MySQL 5.6 GA 及逃亡潮（02/08/2013 14:36:35）
MySQL 5.5.22、5.1.62、5.0.96全线（03/22/2012 19:03:49）

MySQL Administrator连接VMWare下（05/24/2013 09:20:58）
MySQL 5.1.68 发布（02/05/2013 08:37:47）
CentOS 5.2+MySQL+Heartbeat双机互（01/29/2012 11:16:55）

本文评论查看全部评论（0）