/ script

Convert MySQL database from MyISAM to InnoDB

In MySQL, you have a command to convert a table from MyISAM engine to InnoDB engine.

ALTER TABLE table_name ENGINE = InnoDB;

However, this command needs to be done for EVERY table, so here’s a way to do it by scripting if you want to do the entire database :)

  1. Backup your database

  2. Create the script (change DATABASE_NAME by yours):

     mysql -p -e "show tables in <DATABASE_NAME>;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql
    
  3. Check the file alter_table.sql to see if it looks correct

  4. Run the script:

    mysql --database=<DATABASE_NAME> -p < alter_table.sql

  5. Verify:

    mysql> show table status;