Convert MyISAM Tables to InnoDB

The InnoDB engine is superior. There is no reason to run MyISAM anymore. Here is a Bash script that will convert all tables, that can be converted, to InnoDB. If you do not have access to a script shell, you can do the same using phpMyAdmin or other MySQL management tools. Be sure to take a backup of the database before running this script.

 

#!/bin/bash
MYSQLCMD=mysql

die() {
echo “$(basename $0): $1” 2>&1
exit 1
}
usage_die() {
echo “Usage: $(basename $0):” \
“[-a <DB NAME>] [-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 in `mysql –batch –column-names=false -e “show databases”| grep -v Database|grep -v information_schema|grep -v mysql`; do
for table in `mysql –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


Convert MyISAM Tables to InnoDB

Leave a Reply

Your email address will not be published. Required fields are marked *

Fork me on GitHub