更改mysql表引擎(shell)

lfjgh

一、基础环境
1、版本
cat /etc/redhat-release
CentOS release 6.6 (Final)

2、内核
uname -r
2.6.32-504.el6.x86_64

3、ip(eth0)
10.1.10.23

二、shell脚本函数说明
redirectlog           #记录日志用的
checkengine           #检查innodb是否支持及查看默认引擎
generatedb            #导出需要的DB库
generatetable         #导出需要更改引擎表到某个文件
alterengine           #将某个文件中的表进行引擎更改

三、cat myisamtoinnodb.sh
#!/bin/bash
#--------------------------------------------------
#Author:jimmygong
#Email:jimmygong@taomee.com
#FileName:myisamtoinnodb.sh
#Function:
#Version:1.0
#Created:2015-08-07
#--------------------------------------------------
currdate=$(date +%Y%m%d)
dates=`date "+%s"`
sleeptime=2
dbuser="test"
dbpass="123456"
dbport="3306"
dbip=$(ifconfig eth0|awk '/inet addr:/'|awk -F: '{print $2}'|awk -F" " '{print $1}')
logdir="/opt/myisamtoinnodb"
exclude="mysql information_schema"
function mysqlcmd ()
{
    comm=$1
    mysql -u"$dbuser" -p"$dbpass" -h"$dbip" -P"$dbport" -sNe "$comm"
}
function redirectlog ()
{
    logfile=$logdir/${currdate}_${dbip}_log
    mkdir -p $logdir
    exec 1>$logfile
    exec 2>$logfile
}
function checkengine ()
{
    defaultengine=`mysqlcmd 'show engines'|grep -i default|awk '{print $1,$2}'`
    echo "$defaultengine"
    innodb=`mysqlcmd 'show engines'|grep -i innodb|grep -i yes`
    if [[ ! -z "$innodb" ]]
    then
        echo "innodb enabled"
    fi
}
function generatedb ()
{
    i=0
    dblist=`mysqlcmd 'show databases'`
    for db in $dblist
    do
        rv=`echo $exclude|grep -w -i $db`
        if [[ -n "$rv" ]]
        then
            continue;
        fi
        dbname[i]=$db
        let i++
    done
}
function generatetable ()
{
    i=0
    for db in ${dbname[@]}
do
    alltable=`mysqlcmd "use $db;show tables"|wc -l`
    for ((j=1;j<=$alltable;j++))
    do
        table=`mysqlcmd "use $db;show tables;"|sed -n $j"p"`
        for tablename in $table
        do
            engine=`mysqlcmd "use $db;show create table $tablename;"|grep -w -i "engine=myisam"`
            if [[ $? = 0 ]]
            then
                echo $db $table >> $logdir/table.$dates
            fi
        done
    done
done
}
function alterengine ()
{
    [[ ! -e $logdir/table.$dates ]] && exit 1
    while read db table
    do
        mysqlcmd "alter table $db.$table engine=innodb;"
        sleep $sleeptime
        echo $db $table $(date +%F:%T)
    done < $logdir/table.$dates
}
redirectlog
checkengine
generatedb
generatetable
alterengine
exit 0