How to change MyISAM tables to InnoDB in MySQL/MariaDB

MySQL and its fork MariaDB are two widely used relational database management systems in the development of web and enterprise applications. Both support different storage engines, with two of the most common being MyISAM and InnoDB. While MyISAM has been popular for its simplicity, InnoDB has become the preferred choice due to its advanced transaction features and fault recovery. If you want to enhance the integrity and reliability of your data, switching tables from MyISAM to InnoDB is a good decision. In the following article, it will guide you through the steps to perform this migration effectively.

Backup (Recommended)

Before making any changes to the database structure, it's important to perform a complete backup. This ensures that you can restore the data in case of any issues during the modification process. Use your preferred backup tool, either through the command line or a graphical interface. For example, using mysqldump or mariadbdump:

# mysqldump -u root -p database_name > backup.sql

Create SQL Change Statements

Once the database backup is created, connect to the database server with a client, for example, using the root user and the mysql client:

# mysql -u root

Once inside MySQL or MariaDB, execute the following (modifying the name for the database):

SET @DATABASE_NAME = 'database_name';

Then, execute the following (does not make any changes):

SELECT CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') 
AS sql_statements FROM information_schema.tables 
AS tb WHERE table_schema = @DATABASE_NAME 
AND `ENGINE` = 'MyISAM' AND `TABLE_TYPE` = 'BASE TABLE' 
ORDER BY table_name DESC;

It will search for all MyISAM tables and provide a list of statements to modify the tables. You can copy the statements individually and execute them manually, or you can automate it by extracting the result and executing the complete statement directly.

Execute Statements

Use the database to execute the obtained statements:

use 'database_name';

Finally, execute the ALTER TABLE for the desired tables you want to change to InnoDB (you can also execute the entire SQL to apply the change to all):

ALTER TABLE `example_table` ENGINE=InnoDB;

Changing tables from MyISAM to InnoDB in MySQL or MariaDB can significantly improve the reliability and integrity of your data. However, it's always advisable to make these changes in a test environment before applying them to a production environment. With the proper backup and following these steps, you'll be on your way to leveraging the advantages that InnoDB offers in terms of transactions and performance.

We hope this tutorial has been helpful. If you have any questions about this or any other issue related to your servers at Clouding, feel free to contact support@clouding.io We're here to help!