MyISAM to InnoDB in OpenCart

Old OpenCart databases are mostly MyISAM. What that does to backups and migrations, and how to convert tables safely.

Older OpenCart installs keep almost everything in MyISAM tables. Three consequences matter in practice:

No transactions, so a migration cannot be rolled back.
mysqldump --single-transaction does not give a consistent snapshot. Use --lock-tables.
With --lock-tables the shop cannot take orders while the dump runs, so plan when.

See what you have

SELECT engine, COUNT(*) FROM information_schema.tables
WHERE table_schema = DATABASE() GROUP BY engine;

Convert

Generate the statements, smallest tables first and the order tables last:

SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;')
FROM information_schema.tables
WHERE table_schema = DATABASE() AND engine = 'MyISAM'
ORDER BY data_length ASC;

Run them table by table, not all at once. Measure the time of the large tables on a copy first.

Check afterwards

FULLTEXT indexes. InnoDB did not support them before MySQL 5.6. MariaDB 10.x supports them, but search behaviour changes, so test product search after the conversion.
Take a backup after conversion and test that it restores. With InnoDB, --single-transaction is now the right dump mode.