Convert DB type

db_type_convert

#!/bin/bash

# MySQL info
DB_USER='root'
DB_PSWD=''
DB_HOST='localhost'

# Backup path, no trailing slash!
BACKUP_PATH='.'

DATE_BAK="$(date +"%Y-%m-%d")"

TYPE_FROM='MyISAM'
TYPE_TO='InnoDB'

echo "Converting $TYPE_FROM to $TYPE_TO"
echo "==========================================================="

# Get DATABASES
DATABASES="$(echo show databases | MYSQL_PWD=$DB_PSWD mysql -u $DB_USER -h $DB_HOST | grep -v Database | grep -v mysql | grep -v .*_schema | grep -v test)"

for DB in $DATABASES
do 
	echo "Found database: $DB"
	
	#Backup DB
	echo "Backup DB.........................."
	MYSQL_PWD=$DB_PSWD mysqldump -u $DB_USER -h $DB_HOST $DB > $BACKUP_PATH/bak-$DB-$DATE_BAK.sql
	echo "Backup done. Saved to "$BACKUP_PATH/bak-$DB-$DATE_BAK.sql
	
	# Get TABLES
	TABLES="$(echo show tables | MYSQL_PWD=$DB_PSWD mysql -u $DB_USER -h $DB_HOST $DB | grep -v Tables_in_)"
	
	
	for TABLE in $TABLES
	do
		# Get Table Type
		TABLE_TYPE="$(echo show create table $TABLE | MYSQL_PWD=$DB_PSWD mysql -u $DB_USER -h $DB_HOST $DB | grep -v 'Create Table' | sed -e 's/.*ENGINE=\([[:alnum:]]*\).*/\1/')"
		
		# Change Table Type to Target Type if Source Type found.
		if [ $TABLE_TYPE = $TYPE_FROM ]
		then
			echo "> $TABLE"
			MYSQL_PWD=$DB_PSWD mysqldump -u $DB_USER -h $DB_HOST $DB $TABLE | gzip -9 > $BACKUP_PATH/bak-$DB-$TABLE-$DATE_BAK.sql.gz
			echo ">> Converting to $TYPE_TO!"
			echo "ALTER TABLE $TABLE ENGINE=$TYPE_TO" | MYSQL_PWD=$DB_PSWD mysql -u $DB_USER -h $DB_HOST $DB
		fi
	done
done