Convert a MyISAM database to InnoDB by changing the storage engine

An application may require converting MyISAM tables to InnoDB. Do not do a global text replace inside the dump or empty the production database: the change must be tested on an independent copy, keeping the original so you can revert.

1. Create and verify a protected copy

Work from a private folder outside public_html, with a new dump name. Temporarily stop writes and generate a dump. The -p option asks for the password without including it in the command:

umask 077
mysqldump --routines --triggers -u USUARIO_MYSQL -p BASE_DE_DATOS > base-antes-innodb.sql &&
test -s base-antes-innodb.sql &&
sha256sum base-antes-innodb.sql > base-antes-innodb.sql.sha256 &&
chmod 400 base-antes-innodb.sql base-antes-innodb.sql.sha256 &&
sha256sum -c base-antes-innodb.sql.sha256

If mysqldump returns an error, do not continue or treat the partial file as valid. Exporting routines requires permissions; if it fails, check which objects the application needs before leaving them out. Events, if there are any, must also be inventoried and exported with the right options and permissions. The checksum detects later changes to the file, but it does not prove the copy is complete. Read-only permissions do not make it immutable either.

Download the dump and its checksum to other storage. Do not modify these files.

2. Restore and review a clone

Create a new database and user, restore the copy and set up a clone of the application there. Turn off its emails, payments, webhooks and automatic tasks so you do not duplicate production actions:

mysql -u USUARIO_NUEVO -p BASE_NUEVA < base-antes-innodb.sql

Preview the MyISAM tables and their size:

SELECT TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'BASE_NUEVA'
  AND ENGINE = 'MyISAM';

Check the primary keys, the indexes (including FULLTEXT), the row formats and the application's compatibility with the engine change. Confirm there is free space both for the clone and for rebuilding its tables. The conversion can lock tables and need extra space.

3. Convert only the reviewed tables

Generate a list of exact statements, check it and run each one only on the clone:

SELECT CONCAT('ALTER TABLE `', REPLACE(TABLE_SCHEMA, '`', '``'),
              '`.`', REPLACE(TABLE_NAME, '`', '``'), '` ENGINE=InnoDB;') AS sentencia_a_revisar
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'BASE_NUEVA'
  AND ENGINE = 'MyISAM';

Do not use a global replacement in the .sql: it could alter user data, comments or settings that contain the word MyISAM.

4. Validate and switch over

Use COUNT(*) for exact counts: TABLE_ROWS may be an estimate, especially after converting to InnoDB. Compare the schema, indexes and row count of every table between the original database and the clone. Test logging in, searches, writes, scheduled tasks and the application's critical operations.

Only after validating the clone, stop writes again, sync the final changes if needed and change the application's credentials to use BASE_NUEVA. Keep the original database intact and with no writes during the observation period.

Reverting

If any later check fails, stop writes. Before restoring the previous credentials, identify and keep the operations received in the new database; going back without reconciling them would lose those changes. Once that is resolved, go back to the original database. Keep both that database and the verified dump until you have confirmed the conversion is stable.

If you cannot have a clone or work out the space needed, contact support before converting the tables.