Mysql – Altering a large table to INNODB

alter-tableinnodbmyisammysql-5.5mysql-5.6

I have a table of 4GB(18 Million Rows) in MYISAM Engine on one of my slave server, and I want to convert it to INNODB Engine, the server is replicating from a master.
I tried to use the pt-online-schema-change tool which says that it will take more than 1 day, and server lag is increasing because there a lot of insertions in this table,so every query has to be executed twice because of the trigger that the tool creates so that no-downtime is faced.

But I can afford a downtime since it is a slave, is it advisable to just simply run the Alter Engine command ,and how much approximate time will it take to execute?

Also I fear that the replication may break,once the alteration takes place.
I am replicating from a 5.5 Master and My Slave is on 5.6.

What am I advised to do in this case, Any help would be appreciated since I am very new to this field.

Thank You

Best Answer

Hmmm... puzzling that a 4GB operation of any sort should take a day.

I recently answered a question on binary compatibility between versions here. The relevant IMHO bit from that post is quoted below (from the documentation):

 - Export and Import (mysqldump)

You can use mysqldump to dump your tables on one machine and then
import the dump files on the other machine. Using this method, 
It does not matter whether the formats differ or if your tables 
contain floating-point data.

I would always advise that users, if at all possible, use plain text solutions (scripts and the like). This is in the vein of Unix philosophy and therefore, what I would strongly advise you to do is to:

  • Stop replication

  • Backup your single table as per the community wiki here, i.e. using mysqldump.

  • Manually edit the mysqldump script to change the engine from MyIsam to InnoDB. Check out Rolando's more detailled answer above mine - although he did say it was "short and sweet" :-). Use this command sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' your_schema_dump.sql as per here.

  • Reload table as per wiki.

  • Recommence replication (same reference as for stopping).

Et voilà!

I don't know your disk setup, but I'm willing to bet that it will take nothing like 24 hours (minutes maybe? :-) ).

Related Question