1

We have to migrate MyISAM tables to InnoDB but we are afread of a long down time. In our database we have 10 MyISAM tables where 2 of them are the biggest: ~26GB data + ~10GB indexes.

My question is: Can I set replication between primary server (with original engine's tables) and a replica where all tables were migrated to InnoDB? Then replica will be our main DB.

I read a few articles about migrating tables from MyISAM to InnoDB (like: http://mysql.rjweb.org/doc.php/myisam2innodb or https://dev.mysql.com/doc/refman/5.7/en/converting-tables-to-innodb.html) and additional steps are required (like removing duplicated indexes and server configuration changes) but Will it work?

1 Answer 1

1

Replication cannot be started without copies of the tables already on the Replica. That is, Replication is not practical for starting the conversion, only for maintaining the conversion.

OTOH, if you already have replication set up and the 26GB tables are on the Replica, you can ALTER TABLE .. ENGINE=InnoDB; for each table on the Replica. Meanwhile, Replication will continue to send updates and inserts to it. (Although, with a significant delay due to locks.) Eventually, the Replica will be all InnoDB. Then you need enough downtime to perform the "failover" swap roles (Primary vs Replica), presumably by switching the client connections.

On The Other Other Hand, if you have no Replica yet, you are probably out of luck when it comes to a fast changeover.

My old tips on conversion: http://mysql.rjweb.org/doc.php/myisam2innodb

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.