MySQL MyISAM to InnoDB Conversion
Back in November 2020, I managed to convert all legacy MyISAM tables to InnoDB on all Onlime GmbH database servers and customer webservers. MyISAM as legacy storage engine was quite okay-ish on MySQL 5.7 but started to perform really bad on MySQL 8.0. There was simply no reason to keep on using it and honestly, for the last 10 years I did never understand why people still held onto it. I had to find out that a lot of my customers just never heard of any storage engine types, and they didn't even know of any differences between MyISAM and InnoDB.
List all MyISAM tables
Before proceeding to the actual task of converting all those shitty MyISAM tables, I would like to present you some queries to find out about MyISAM table existence on your MySQL server.
List all MyISAM tables (+ table size) of all user databases:
SELECT table_schema db, table_name tbl, CAST((IFNULL(data_length, 0) + IFNULL(index_length, 0)) AS SIGNED) size
FROM information_schema.TABLES
WHERE engine = 'MyISAM' AND table_type = 'BASE TABLE'
AND table_schema NOT IN ('information_schema', 'sys', 'performance_schema', 'mysql')
ORDER BY table_schema, table_name;
Only list databases with at least one MyISAM table:
SELECT table_schema db FROM information_schema.TABLES
WHERE engine = 'MyISAM' AND table_type = 'BASE TABLE'
AND table_schema NOT IN ('information_schema', 'sys', 'performance_schema', 'mysql')
GROUP BY table_schema ORDER BY table_schema;
Or do it all with a nice one-liner for plain output (batch mode), one db name per line:
# full list of all tables
$ mysql -sN -r -e "SELECT CONCAT(table_schema, '.', table_name) FROM information_schema.TABLES
WHERE engine = 'MyISAM' AND table_type = 'BASE TABLE'
AND table_schema NOT IN ('information_schema', 'sys', 'performance_schema', 'mysql')
ORDER BY table_schema, table_name"
# only list databases
$ mysql -sN -r -e "SELECT table_schema db FROM information_schema.TABLES
WHERE engine = 'MyISAM' AND table_type = 'BASE TABLE'
AND table_schema NOT IN ('information_schema', 'sys', 'performance_schema', 'mysql')
GROUP BY table_schema ORDER BY table_schema"
Convert single table to InnoDB
To convert a single table to InnoDB, run:
ALTER TABLE `db`.`tablename` ENGINE=InnoDB;
Run this manually if you just have a bunch of tables to convert. If you have hundreds or thousands of tables to convert, read on.
Requirements
In my case, I had to convert over 10'000 MyISAM tables (some of which had several GB of data) on multiple MySQL servers. So I sat down and wrote a script that did exactly what I needed with those requirements:
- Loop over all MyISAM tables (only user dbs, no system table schemas!)
- Limit number of databases (no matter how many tables they contain) to convert, so that we could split up conversion and spread the migration over several days to limit risk
- Could be directly written in MySQL Shell but preferably written in Python
- Logging: timestamps (start/end of conversion of each table), db name, table name
- Dumps whole database as backup prior to doing conversion.
- Conversion should be done directly with
ALTER TABLE ... ENGINE=InnoDBand not by dumping / loading full DB. (uptime!) - Should break on any failure (not continuing with next table conversion!) and report to STDOUT
- In case of a failure, it should restore data from DB backup dump
MyISAM-to-InnoDB Converter
I have published this project as it helped me to migrate all customer tables from MyISAM to InnoDB with zero downtime and with near to zero risk:
Install this project using Python 3 venv:
$ git clone git@gitlab.com:onlime/myisam-to-innodb.git
$ cd myisam-to-innodbd
$ python3 -m venv venv
$ . venv/bin/activate
(venv)$ pip install -r requirements.txt
MyISAM-to-InnoDB consists of a single script convert.py which is pretty powerful, check convert.py --help for help:
usage: convert.py [-h] [--dbpattern DBPATTERN] [--skip-dbs SKIP_DBS] [--limit LIMIT] [--not-before NOT_BEFORE]
[--login-path LOGIN_PATH] [--backup-dir BACKUP_DIR] [--dryrun] [--logdir LOGDIR] [--verbose] [--debug]
optional arguments:
-h, --help show this help message and exit
--dbpattern DBPATTERN
database pattern to limit search
--skip-dbs SKIP_DBS databases to ignore (comma separated)
--limit LIMIT Limit number of databases (Default: 0 / unlimited)
--not-before NOT_BEFORE
Limit tables by age (update_time)
--login-path LOGIN_PATH
login_path for authentication (Default: client)
--backup-dir BACKUP_DIR
Path for mysqldump backups (Default: backups)
--dryrun, --dry-run Don't convert anything, just output MyISAM tables
--logdir LOGDIR Logdir path (Default: logs)
--verbose Also print all log entries
--debug Enable debug mode
Usage examples:
WARNING: Do not try this at home! I have added
--dryrunto every command, so that it won't touch your existing data. Once you are sure what the command does, run it without the--dryrunflag.
$ cd <PROJECT_DIR>
$ . venv/bin/activate
# Convert all user tables
(venv)$ ./convert.py --dryrun
# Convert all tables of a single database
(venv)$ ./convert.py --dbpattern=my_db1 --dryrun
# Convert all tables of a single database
(venv)$ ./convert.py --dbpattern=dbname --dryrun
# Convert all tables of all databases by pattern
(venv)$ ./convert.py --dbpattern='my_db%' --dryrun
# Convert all tables of all databases by pattern and write backups to some external dir
(venv)$ ./convert.py --dbpattern='my_db%' --backup-dir=/backups/myisam-dumps --dryrun
# Convert all tables of all databases but exclude some single dbs (comma separated list)
(venv)$ ./convert.py --skip-dbs='my_db1,my_db2' --dryrun
# or even combine such excludes with a db pattern
(venv)$ ./convert.py --dbpattern='my_db%' --skip-dbs='my_db1,my_db2' --dryrun
# Convert all tables and output loglines (in addition to application.log)
(venv)$ ./convert.py --dryrun --verbose
# Convert all tables, limited by the next 10 databases with MyISAM tables
(venv)$ ./convert.py --limit=10 --dryrun --verbose
# Convert all tables, limited by the next 10 databases with MyISAM tables,
# also reporting debug messages (e.g. creation of single table backup dumps) on STDOUT
(venv)$ ./convert.py --limit=10 --dryrun --verbose --debug
# Finally, you can limit MyISAM table lookup by age. As we cannot count on create_time, we
# only limit by update_time from information_schema.TABLES
# This is only used for a very special use case if you want to exclude really old MyISAM
# tables which might have been created/used prior a MySQL 5.7 upgrade, avoiding problems like:
# https://bugs.mysql.com/bug.php?id=99791
(venv)$ ./convert.py --not-before='2018-01-01' --dryrun --verbose
Or run it in a one-liner (without entering the venv):
$ cd <PROJECT_DIR> && venv/bin/python convert.py --dryrun
If you did not specify --verbose, you might want to monitor the log:
$ tail -f logs/application.log
Run it in a cronjob
If running this in a cronjob, ensure the following:
- The user needs to have access to the right
--login-pathwhich is stored in his~/.mylogin.cnf. Usually this will beroot, as we don't want to store MySQL root credentials in any regular system user environment. - The
convert.pyscript needs to run inside your project's venv
example of a nightly cronjob at 04:15AM, only converting MyISAM tables of 10 databases:
15 4 * * * cd PROJECT_DIR && venv/bin/python convert.py --limit=10 --backup-dir=/backups/myisam-dumps
Disable MyISAM
After having successfully converted all MyISAM tables to InnoDB (check with above queries), it's time to disable MyISAM for good!
Put those storage engines into disabled_storage_engines in your MySQL configuration (/etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf on a Debian Linux / Percona Server for MySQL):
[mysqld]
disabled_storage_engines = "MyISAM,MERGE"
Restart MySQL and say goodbye to MyISAM forever!
