MySQL: Converting all tables in all databases from MyISAM to InnoDB

With MySQL, for most use cases, InnoDB is a much better database engine than MyISAM.

If you have plenty of databases with tables in MyISAM format which you'd like to convert to InnoDB, here is a bash script which will do it.

The script only converts MyISAM tables, and will not re-convert already existing InnoDB tables.

Please note that it may take long to convert very large tables, so plan your maintenance time accordingly.

Usage:

  • if you would like to convert all database, please uncomment DATABASES="ALL" line; otherwise, adjust DATABASES variable to contain all databases in which you would like to convert the tables to InnoDB.
  • the script assumes your password is stored in ~./my.cnf - if it's not the case, please uncomment and set MYSQL_PASS variable and uncomment the other MYSQL_COMMAND (the one which uses --password="$MYSQL_PASS"); please note that mysql will output "Warning: Using a password on the command line interface can be insecure" in such case)