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