How to Disable InnoDB in MariaDB

Disabling InnoDB in MariaDB 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.

Steps to Disable InnoDB in MariaDB #

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 MariaDB Configuration File #

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

sudo vi /etc/mariadb.cnf

• For Ubuntu/Debian:

sudo vi /etc/mariadb/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 MariaDB 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 MariaDB 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 MariaDB #

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

sudo systemctl restart mariadb OR sudo service mariadb restart

6. Verify InnoDB is Disabled

• Once the MariaDB service has restarted, log into MariaDB 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.