一、基础环境
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
}
redirectlog
checkengine
generatedb
generatetable
alterengine
exit 0
