Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
#!/bin/bash
# Converts all InnoDB tables in all databases to MyISAM
#DATABASES="db1 db2" # Convert databases db1 and db2 only
DATABASES="ALL" # Convert all databases
MYSQL_USER=root
# Uncomment if you're not using ~/.my.cnf file (will receive "Warning: Using a password on the command line interface can be insecure" warnings)
#MYSQL_PASS='some-secret-password'
MYSQL_HOST=localhost
### no need to change anything below
# bail out on undefined variables
set -u
# mysql command we will use
MYSQL_COMMAND="mysql -s -u "$MYSQL_USER" -h $MYSQL_HOST"
# Uncomment if you're not using ~/.my.cnf file (will receive "Warning: Using a password on the command line interface can be insecure" warnings)
#MYSQL_COMMAND="mysql -s -u "$MYSQL_USER" --password="$MYSQL_PASS" -h $MYSQL_HOST"
# get a list of databases if we want to convert all databases
if [ "$DATABASES" == "ALL" ] ; then
DATABASES=$(echo "SHOW DATABASES" | $MYSQL_COMMAND | egrep -v '(performance_schema|information_schema|mysql)')
fi
for DATABASE in $DATABASES ; do
echo Converting $DATABASE
# Check if the table is InnoDB (we don't want to convert MyISAM tables over and over again)
TABLES=$(echo "SELECT TABLE_NAME FROM information_schema.TABLES where TABLE_SCHEMA = '$DATABASE' and ENGINE = 'InnoDB'" | $MYSQL_COMMAND)
for TABLE in $TABLES ; do
echo Converting InnoDB $TABLE to MyISAM
echo "ALTER TABLE $TABLE ENGINE = MyISAM" | $MYSQL_COMMAND $DATABASE
done
if [ "x$TABLES" = "x" ] ; then
echo No InnoDB tables found in $DATABASE database
fi
echo
done
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.