Convert all MyISAM table to InnoDB in a given database

myisam2innodb.sh

#!/bin/bash

# MySQL info
DB_HOST='localhost'
DB_USER='root'
DB_PSWD='p@ssword'
DB='dbname'


echo "Converting MyISAM to InnoDB"
echo "==========================================================="


CMDTB="$(echo "show tables" | MYSQL_PWD=$DB_PSWD mysql -u $DB_USER -h $DB_HOST $DB | grep -v Tables_in_)"


for TABLE in $CMDTB
do
	Q="show create table $DB.$TABLE"
	TABLE_TYPE="$(echo $Q | MYSQL_PWD=$DB_PSWD mysql -u $DB_USER -h $DB_HOST $DB | grep -v 'Create Table' | sed -e 's/.*ENGINE=\([[:alnum:]]*\).*/\1/')"

	if [ $TABLE_TYPE = "MyISAM" ]
	then
		echo ">> Converting $TABLE to InnoDB!"
		echo "ALTER TABLE $DB.${TABLE} ENGINE=InnoDB" | MYSQL_PWD=$DB_PSWD mysql -u $DB_USER -h $DB_HOST $DB
	else
		echo "## $TABLE already in InnoDB!"
	fi
done