Skip to content

MySQL/MariaDB storage engines: moving to InnoDB#

This page does not cover installing MySQL itself - for that, see the MySQL install page in the Linux Administration Book. What's here is a narrow but common question: a table (or an entire old database) is still on MyISAM, and you need to move it to InnoDB.

Why InnoDB#

InnoDB has been MySQL's default storage engine since 5.5 - if you are still seeing MyISAM, that table was probably created on a very old version or restored from an old backup. The difference is not cosmetic:

MyISAM InnoDB
Transactions (COMMIT/ROLLBACK) No Yes
Locking Table-level Row-level
Crash recovery Weak, tables corrupt often Automatic
Foreign keys Not supported Supported
Full-text index Yes (historically the one reason to stay) Since MySQL 5.6+

If full-text search was your only reason to stay on MyISAM, InnoDB has had it for over a decade now - there is usually no remaining reason to keep it.

Check your current state#

SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND engine != 'InnoDB';

This lists every table in your system that is not using InnoDB.

Convert a single table#

ALTER TABLE database.table_name ENGINE=InnoDB;

Instant on small tables. On large tables ALTER TABLE rewrites the table and the time grows with its size - how much locking that involves depends on your MySQL/MariaDB version and which ALGORITHM/LOCK options it supports: ALGORITHM=COPY rebuilds the table from scratch and usually needs stronger locking, while ALGORITHM=INPLACE with LOCK=NONE may allow concurrent writes where it's supported.

Check locking before you assume it

Don't assume writes wait for the entire conversion, or that they can continue uninterrupted either way - verify which ALGORITHM/LOCK options your version supports beforehand (try ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE or check EXPLAIN) and watch metadata locks while it runs. For a table with millions of rows, if the native operation can't meet your locking needs, do it during a low-traffic window or use an online tool like Percona Toolkit's pt-online-schema-change.

Convert an entire database#

Instead of writing each one by hand, generate the ALTER TABLE statements from information_schema and run them:

mysql -N -e "SELECT CONCAT('ALTER TABLE \`', REPLACE(table_schema, '\`', '\`\`'), '\`.\`', REPLACE(table_name, '\`', '\`\`'), '\` ENGINE=InnoDB;')
FROM information_schema.tables
WHERE table_schema='database_name' AND engine != 'InnoDB';" | mysql database_name

The backtick-quoting is not decoration: a table/database name with a reserved word, space, or hyphen breaks an unquoted ALTER TABLE statement, and the generated script stops right there - the remaining tables are silently left unconverted.

Back up before converting

ALTER TABLE ENGINE= is not reversible - if something goes wrong mid-conversion, you are left with neither the old nor the new format intact. Do not start a large conversion without a mysqldump backup first.

mysqldump database_name > /opt/backup/before-$(date +%F).sql

Make new tables default to InnoDB#

Set the server-wide default engine so this does not happen again:

/etc/mysql/mysql.conf.d/mysqld.cnf (Debian/Ubuntu) or /etc/my.cnf.d/*.cnf (RHEL family)
[mysqld]
default_storage_engine = InnoDB

sudo systemctl restart mysql    # or mariadb

With Morpheus

"list tables in database X that are not InnoDB"

"convert table Y to InnoDB, back up first"

Morpheus runs the query, shows you the result, and applies the conversion with your approval - but it does not skip the backup before a large conversion.

Checklist#

  • [ ] Took a mysqldump backup before converting
  • [ ] Converted large tables during a low-traffic window
  • [ ] Set default_storage_engine = InnoDB so this does not recur
  • [ ] Verified the application actually works after conversion (test anything relying on foreign keys or transactions)