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.

The shortcut

If you have connected VeriTeknik MCP to your AI assistant (Claude, ChatGPT, Cursor...), just say it:

"List the tables in the wordpress database that are still on MyISAM, take a backup first, then convert them to InnoDB."

Your assistant lists the tables, takes the backup and converts them. The connection needs the Servers: admin commands group for this. On large tables, writes may wait while the conversion runs (see below), so ask for it at a quiet hour.

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)