Convert MyISAM to InnoDB (Drupal)

Convert Drupal MySQL engine from MyISAM to InnoDB using Drush:
drush updatedb -y && drush sql-query "SELECT ENGINE AS 'Engine Before:' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='node';" && ($(drush sql-connect | sed -e 's/^mysql/mysqldump --no-data/' | sed -e 's/--database=/--databases /') | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/' | sed 's/varchar(256)/varchar(255)/' > ~/schema.sql) && drush sql-dump -q --data-only --result-file=~/data.sql && drush sql-dump -y --result-file=~/backup.sql && drush sql-drop -y && ($(drush sql-connect) < ~/schema.sql) && ($(drush sql-connect) < ~/data.sql) && rm ~/schema.sql && rm ~/data.sql && drush sql-query "SELECT ENGINE AS 'Engine After:' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='node';" && drush cc all && echo "Remember to delete ~/backup.sql after verifying the site still works."


P.S. This is safer and faster and causes much less strain on your database server than a lot of ALTER statements on the live database (especially for cluster solutions, like Percona)

Comments

Popular Posts