Convert MySQL database from MyISAM to InnoDB

In this tutorial we are going to learn about connecting MySQL database from MyISAM to InnoDB.

Before you go any further backup your database before doing any steps below. If you ‘splode your database for any reason, you’ll need it. Here are the steps:

1. Shutdown MySQL

2. Move/copy/change the name of iblogfile0 and iblogfile1 files.

(find where MySQL exists on your system - locations can vary greatly). MySQL will recreate these files when you start it up again and and you need to shutdown MySQL and startup MySQL when you change the innodblogfile_size parameter when there is a movement in these files.

3. Tune it up a bit

Based on a lot of searching around and benchmarking with JMeter I arrived at the setting below for running on my Macbook Pro. The production server for this particular site ended up with 5000M setting for innodbbufferpool_size. So settings will, and should, vary greatly just depending).

In order to have the new parameters come into play we can edit the my.cnf file with these parameters and restart the my.sql. These setting can improve the application time.

Finally, In addition of settings, we can also lower the values for things that are more MyISAM specific like keybuffersize etc. Here are the relevant InnoDB related paremeters, which you should merge into your existing my.cnf. IMPORTANT - be sure to comment out skip-innodb parementer if it exists.

# skip-innodb needs to commented out if it already exists in your my.cnf - shown here as a reminder
#skip-innodb
default-storage-engine = innodb
innodb_buffer_pool_size = 200M
innodb_log_file_size = 100M
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_additional_mem_pool_size = 20M

# num cpu's/cores *2 is a good base line for innodb_thread_concurrency
innodb_thread_concurrency = 4

#Tried the following parameters to no good effect, actually (very) small decrease in performance, thus they are commented out but document here, just because.
#innodb_data_home_dir = /path/to/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /path/to/mysql
#transaction-isolation=READ-COMMITTED

4. Conversion into tables

Database didnt seem to be converted (via the queries found inside altertable.sql file) until after the my.cnf file was all setup and the steps with iblogfile0 and ib_logfile1 were completed. Now stop and restart the MySQL.

To generate the queries you need to run the following commands:

mysql -u [USER_NAME] -p -e "SHOW TABLES IN [DATABASE_NAME];" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql
perl -p -i -e 's/(search_[a-z_]+ ENGINE=)INNODB/\1MYISAM/g' alter_table.sql

To run these queries against your database run the following command, or else take the contents of the alter_table.sql file you generate with the instructions and just many paste it in as a query in favorite MySQL interface (Sequel Pro for me, perhaps PHPMyadmin for others)

mysql -u [USER_NAME] -p [DATABASE_NAME] < alter_table.sql

After running all the queries, now check the database to see the change i.e., InnoDB as the storage engine instead of MyISAM.

5. Restart MySQL and take your site for a spin

6. Done