How to Disable InnoDB in MySQL

Disabling InnoDB in MySQL is possible but generally not recommended for modern applications, as InnoDB is the default storage engine due to its robustness, support for ACID transactions, and foreign key constraints.

However, if you want to disable InnoDB and use other storage engines like MyISAM, here’s how you can do it.

For MariaDB, please see this guide

Steps to Disable InnoDB in MySQL #

1. Backup Your Databases #

• Before making any changes, it’s crucial to back up all your databases to avoid data loss in case of any issues. Use these two commands, t crate a backup folder then to do the actual backing up.

mkdir -p /home/databases/

mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > /home/databases/"$dbname".sql; done

2. Edit MySQL Configuration File #

• You need to modify the MySQL configuration file (my.cnf) to disable InnoDB.
• Open the configuration file in your preferred text editor.
• For CentOS/AlmaLinux/RHEL:

sudo vi /etc/my.cnf

For Ubuntu/Debian:

sudo vi /etc/mysql/my.cnf

3. Add InnoDB Disabling Options #

• Add the following lines under the [mysqld] section to disable InnoDB:

[mysqld]
skip-innodb
default-storage-engine=MyISAM

• In the above;
  • skip-innodb tells MySQL not to load the InnoDB engine.
  • default-storage-engine=MyISAM sets the default storage engine to MyISAM instead of InnoDB.

4. Convert InnoDB Tables to MyISAM (Optional) #

If you already have tables using the InnoDB engine, you should convert them to MyISAM before disabling InnoDB.

• Log into your MySQL database:

mysql -u root -p

• You can find all the tables that are using InnoDB by running this query:

SELECT table_name FROM information_schema.tables WHERE engine = 'InnoDB';

• For each InnoDB table, run the following command to convert it to MyISAM:

ALTER TABLE table_name ENGINE=MyISAM;

5. Restart MySQL #

• After making changes to the configuration file, restart the MySQL service to apply the changes using one of these commands

sudo systemctl restart mysql OR sudo service mysql restart

6. Verify InnoDB is Disabled

• Once the MySQL service has restarted, log into MySQL to confirm that InnoDB is disabled.

mysql -u root -p

• Run the following query to check the available storage engines:

SHOW ENGINES;

• You should see that InnoDB is listed as “DISABLED” or with a Status og NO under support column.
• You will also see MyISAM engine set to DEFAULT, as in the image below.