Saturday, October 8, 2016
Convert MyISAM to InnoDB Drupal
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."
Credits:
- http://stackoverflow.com/a/23393131
- http://stackoverflow.com/a/16820166
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)
Go to link download
