MySql Mass Convertion of Database Table’s Storage Engines from Other Types to InnoDB

| One Comment

A Few months back I had to convert moodle LMS database storage engine from MyISAM to InnoDB. Moodle has some 200+ databases and to manually convert each table takes an awful lot of time. My friend who was responsible for the conversion decided to write a php script that lists all the tables in the database and loop through each table and convert its storage engine to InnoDB. I couldn’t find any tutorial where it showed how to mass convert database storage engines by SQL alone. I finally devised a SQL only method to accomplish this.

My method is divided into two steps. First, you need to list all tables in the database. When the table names are listed parts of the SQL statement to alter the table is added to each table name. So the output of the first query is a list of SQL statements. The query of the first step is provided below. This query will take about 5 to 10 seconds to execute. Replace ‘testdb’ with the name of your database.

SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') as FLDSQL FROM information_schema.tables WHERE table_schema = 'testdb' ORDER BY table_name DESC;

Generate InnoDB conversion SQL and export the output

Now the output of the first query need to be exported into a text format. For that I export the out put into CSV format as shown below. This results in having one SQL statement per line that converts one table.

Convert Tables into InnoDB

For the second step, copy the output of step one and run it as SQL query. The output of step one will contain as many lines of SQL queries as there are tables in the database. The output of step one would look like this.

ALTER TABLE t2 ENGINE=InnoDB;
ALTER TABLE t1 ENGINE=InnoDB;

Now you know how to achieve mass conversion of database storage engine with just SQL without any knowledge of programming languages.

Download a free trial of active x today!

Referense
http://dev.mysql.com/
MySQL INFORMATION SCHEMA

One Response to MySql Mass Convertion of Database Table’s Storage Engines from Other Types to InnoDB

  1. Much appreciated for the information and share!

Leave a Reply

Your email address will not be published. Required fields are marked *