Converting to InnoDB from MyISAM tables using the Command Line

by Ryan Thrash

Published on October 11, 2017

InnoDB is a more robust and better database table type for modern applications like MODX Revolution compared to the older MyISAM table type. Your sites should perform better and faster, and they should also be more tolerant of database hiccups that can occur.

This is a slightly more advanced companion to our previous tutorial to convert to InnoDB using phpMyAdmin. It is especially useful for power users and larger databases that may fail conversion when using a GUI web app.

Requirements

You’ll need the following in order to convert your database to use InnoDB tables instead of MyISAM.

  1. Comfort with using the command line
  2. SSH access to your database server and your database credentials
  3. A database version that supports full text indexes in InnoDB table types

Process

First, ALWAYS back up your database before doing anything potentially destructive. For the purposes of this tutorial, we’re assuming you are doing this in MODX Cloud, so you can just click the Backup Cloud menu option and get started right away. Different environments may require slightly different commands as follows:

  1. SSH into your instance
  2. Enter the following into your command line, and use your MySQL password. Make sure to substitute your your username (e.g., c0000) and your database (e.g., instance_c0000_modx) in both of the appropriate spots, below:

    mysql -B --user=c0000 --host=127.0.0.1 --password instance_c0000_modx --disable-column-names --execute "SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM' AND table_schema='instance_c0000_modx';"  

  3. Copy the resulting alter statements this generates, and paste it into a text editor. On a Mac, you can simply press shift-cmd-A to select the result of the last command in the built-in Terminal.app then cmd-c to copy.

  4. At the shell prompt again, execute the following command to get to the MySQL command prompt. Make sure to substitute your your username (e.g., c0000) and database name (e.g., instance_c0000_modx) below:

    mysql --user=c0000 --host=127.0.0.1 --password instance_c0000_modx    

  5. Copy the alter statements from two steps earlier, and paste them into your terminal. Depending on the size of your database, your tables should convert to use InnoDB in anywhere from a few seconds to a minute or so.

We hope you find this useful as you prepare for the future of MODX Revolution.

Millions Rely on MODX

In 2005, MODX could power a fully mobile-responsive website using HTML5 and CSS3, even though those technologies weren’t invented yet. And with MODX today, you’re ready not only for what you need now but also what comes next.

Try MODX Right Now