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.
- 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)
#!/bin/bash # Converts all MyISAM tables in all databases to InnoDB 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 MyISAM (we don't want to convert InnoDB tables over and over again) TABLES=$(echo "SELECT TABLE_NAME FROM information_schema.TABLES where TABLE_SCHEMA = '$DATABASE' and ENGINE = 'MyISAM'" | $MYSQL_COMMAND) for TABLE in $TABLES ; do echo Converting MyISAM $TABLE to InnoDB echo "ALTER TABLE $TABLE ENGINE = INNODB" | $MYSQL_COMMAND $DATABASE done if [ "x$TABLES" = "x" ] ; then echo No MyISAM tables found in $DATABASE database fi echo done