DB-유

Migrating MyISAM Tables to InnoDB

· mysql, innodb, migration

Every few months I inherit a database that still has MyISAM tables: created back when 5.1 defaulted to it, or copied from an old tutorial. No transactions, no crash recovery, no foreign keys, and table-level locking that makes every write wait in line. I have been bitten by "marked as crashed" after a power cut often enough that I convert on sight. InnoDB has been the default since 5.5, and MySQL 8.0 doesn't even use MyISAM for its own system tables anymore.

Conversion is mostly one statement per table, but check full-text indexes, disk space, AUTO_INCREMENT behaviour and buffer pool size first. This is for MySQL 5.7/8.0 and MariaDB 10.x. Take a backup, have free disk equal to the largest table plus indexes (InnoDB is usually bigger), and plan a window or use pt-online-schema-change for tables that must stay writable.

Inventory the MyISAM tables

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS,
       ROUND((DATA_LENGTH + INDEX_LENGTH)/1024/1024) AS size_mb
FROM information_schema.TABLES
WHERE ENGINE = 'MyISAM'
  AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY size_mb DESC;

Leave the mysql schema alone on 5.7 and MariaDB; those system tables are meant to be MyISAM there.

Features InnoDB handles differently

Full-text indexes exist in InnoDB since 5.6, but the ranking and the minimum word length differ (innodb_ft_min_token_size = 3 versus ft_min_word_len = 4), and InnoDB requires an FTS_DOC_ID column that it adds itself. Find them:

SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME FROM information_schema.STATISTICS
WHERE INDEX_TYPE = 'FULLTEXT' AND TABLE_SCHEMA = 'shop';

Test full-text queries on a converted copy before converting the live table. Two more differences to know:

  • Multi-column AUTO_INCREMENT keys of the form PRIMARY KEY (group_id, seq) where seq restarts per group are MyISAM-only. InnoDB requires the AUTO_INCREMENT column to be the first column of some index.
  • SELECT COUNT(*) FROM t is instant on MyISAM (stored in the header) but scans the smallest index on InnoDB. Applications that do this on large tables will notice; information_schema.TABLES.TABLE_ROWS gives an estimate if an exact count is not required.

Give InnoDB some memory

MyISAM servers often have a tiny buffer pool because the data lived in key_buffer_size and the OS page cache. Before converting, adjust /etc/my.cnf (CentOS) or /etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu):

[mysqld]
innodb_buffer_pool_size        = 4G        # 50-70% of RAM on a dedicated server
innodb_log_file_size           = 512M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
key_buffer_size                = 32M       # shrink; only needed for temp MyISAM tables now

Restart the server after changing innodb_log_file_size on 5.7 or earlier; on 8.0 it is still static, so the same applies. With SET PERSIST_ONLY on 8.0 you can queue it for the next restart.

Convert

ALTER TABLE shop.products ENGINE=InnoDB;

Generate the statements for everything in a schema:

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

Feed the output back with mysql -N -e "..." | mysql. Each ALTER copies the table and holds a write lock for the duration, so do the big ones with pt-online-schema-change --alter "ENGINE=InnoDB" if the application cannot pause.

For tables without a primary key, add one before or during the conversion; InnoDB creates a hidden 6-byte row id otherwise, which works but makes replication in ROW format slow and prevents some online operations:

ALTER TABLE shop.logs ADD COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST, ENGINE=InnoDB;

Finally, make sure new tables are InnoDB by default and that nothing silently falls back:

default_storage_engine = InnoDB
disabled_storage_engines = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE"   # MySQL 5.7.8+ / 8.0 only

disabled_storage_engines makes CREATE TABLE ... ENGINE=MyISAM fail instead of quietly succeeding.

Did everything convert?

SELECT ENGINE, COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'shop' GROUP BY ENGINE;
CHECK TABLE shop.products;

The first query should list only InnoDB. Run the application's test suite with attention to full-text search and anything relying on COUNT(*). Then watch the buffer pool hit rate for a day:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Innodb_buffer_pool_reads (disk reads) should be a small fraction of Innodb_buffer_pool_read_requests. If not, the pool is too small for the converted data.

Errors along the way

  • ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes: you are on MySQL 5.5 or older. Upgrade first; InnoDB full-text needs 5.6+.
  • ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key: the MyISAM-style composite auto-increment described earlier. Redesign the key, typically with a single AUTO_INCREMENT primary key and a unique index on the old columns.
  • ERROR 1118 (42000): Row size too large (> 8126): a table with many VARCHAR or TEXT columns exceeds InnoDB's in-page row limit. Use ROW_FORMAT=DYNAMIC (default on 5.7+) so long columns are stored off-page, or split the table.
  • Disk fills during the ALTER: the copy is written to the same filesystem as the data directory (or tmpdir for some steps). Free space, or set innodb_tmpdir to a larger volume for the duration.
  • Application errors Table 'shop.products' is marked as crashed and should be repaired on tables still in MyISAM: that is exactly why you are converting. REPAIR TABLE fixes it once; InnoDB's crash recovery makes it a non-issue.

The conversion is routine. The real work is testing full-text behaviour and sizing the buffer pool before the switch.