Convert Tables to a Different Storage Engine

Older databases, especially ones migrated from legacy WordPress or PHP applications, often still have MyISAM or Aria tables. Converting them to InnoDB gives you transactions, row-level locking, and crash recovery. The same method works for any target engine, such as MyRocks.

1. Back Up the Database

Converting rewrites every table, so start with a full dump:

mariadb-dump --single-transaction --routines --triggers --databases <DB_NAME> > <DB_NAME>-before-convert.sql

2. Find Tables That Need Converting

List the tables that aren't already on the target engine:

SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '<DB_NAME>'
  AND TABLE_TYPE = 'BASE TABLE'
  AND ENGINE <> 'InnoDB';

3. Generate the ALTER Statements

Have MariaDB write the conversion statements for you, and review them before running:

SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ENGINE=InnoDB;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '<DB_NAME>'
  AND TABLE_TYPE = 'BASE TABLE'
  AND ENGINE <> 'InnoDB';

ALTER TABLE `<DB_NAME>`.`wp_options` ENGINE=InnoDB;
ALTER TABLE `<DB_NAME>`.`wp_posts` ENGINE=InnoDB;

The backticks handle table names that contain spaces or reserved words.

4. Run the Conversion

Once you're happy with the list, pipe the generated statements straight back into the server from the shell:

mariadb -N -e "SELECT CONCAT('ALTER TABLE \`', TABLE_SCHEMA, '\`.\`', TABLE_NAME, '\` ENGINE=InnoDB;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = '<DB_NAME>' AND TABLE_TYPE = 'BASE TABLE' AND ENGINE <> 'InnoDB'" | mariadb

-N drops the column header, so only the statements reach the second mariadb process.

5. Verify

Confirm every table now reports the new engine:

SELECT TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '<DB_NAME>'
ORDER BY ENGINE, TABLE_NAME;

You don't need a separate OPTIMIZE TABLE afterward. Changing the engine already rebuilds each table from scratch.

Converting to Other Engines

Replace InnoDB in the queries above with the target engine's name. Some engines are plugins that must be installed first. MyRocks, for example, ships as a separate package:

apt install mariadb-plugin-rocksdb

SHOW ENGINES;

Check that the engine is listed as YES before converting. Also check that the target engine supports the features your tables use, such as full-text indexes, foreign keys, or spatial columns.