Linux 下将MySQL的所有数据库由MyiSAM存储类型转换为InnoDB存储类型（自动转换脚本）

使用该个脚本 可以将MySQL的所有数据库由MyiSAM存储类型转换为InnoDB存储类型，注意，数据转移是一个较危险的操作，操作前请先做好备份工作。

#!/bin/bash

MYSQLCMD=mysql

die() {
echo “$(basename $0): $1″ 2>&1
exit 1
}
usage_die() {
echo “Usage: $(basename $0):” \
“[-a ] [-d for all databse”] 2>&1
exit 1
}

while getopts a:d OPT; do
case “$OPT” in
d) ALL_DB=$OPTARG ;;
a) ONE_DB=$OPTARG ;;
*) usage_die ;;
esac
done
if [ $# -eq 0 ]; then
usage_die
fi

one_db() {
check_db=$(mysql –batch –column-names=false -e “show databases”|grep “$ONE_DB”)
if [ “$check_db” == “” ]; then
echo “can’t find the databse “$ONE_DB” in mysql”
exit 1
else
for t in $(mysql –batch –column-names=false -e “show tables” “$ONE_DB”);
do
echo “Converting table $t”;
mysql -e “alter table ${t} type=InnoDB” ${ONE_DB};
done
fi
}

all_db() {
for db inmysql –batch –column-names=false -e “show databases”| grep -v Database|grep -v information_schema|grep -v mysql;
do
for table inmysql –batch –column-names=false -e “show tables” ${db} | grep -v information_schema|grep -v Tables_in;
do

echo “Converting table $table”
mysql -e “alter table ${t} type=InnoDB” ${db};
done
done
}
echo ${ONE_DB}
exit 0

if [ “$ONE_DB” == “” ]; then
exit 1
else
one_db
fi

if [ “$ALL_DB” == “” ]; then
exit 1
else
all_db
fi