by BehindJava

Convert MySQL database from MyISAM to InnoDB

Home » java » 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. Not anytime you change the innodblogfile_size parameter you will need to shutdown MySQL, move these files, and start up MySQL again.

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).

With some more time spent fine tuning and benchmarking, and taking into account the specifics of your application, these setting could be improved upon I’m sure, but they’re a decent starting point at least. These parameters go inside your my.cnf file. Anytime you edit your my.cnf file remember to restart my.sql in order to have the new parameters take affect.

Finally, in addition to adding the settings below, I also lowered values for things that are more MyISAM specific like keybuffersize. 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
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

4. Time for the actual conversion of the tables

Borrowing heavily from some commandline magic from this post we’ll get the database tables converted. It’s worth noting that I’ve changed the sequence of the other steps here around because the order of them did not work for me. I’ve also disregarded the part about leaving certain tables MyIASM (*see notes at the end of this post for more).

My database did not seem to want to be converted (via the queries found inside altertable.sql file you’ll generate) until after the my.cnf file was all setup and the steps with iblogfile0 and ib_logfile1 were completed and MySQL had been stop and restarted. 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 you’ve ran the queries check your database to make sure that it shows InnoDB as the storage engine and not MyISAM.

5. Restart MySQL and take your site for a spin

6. Done