MySQL: replicating to a different table engine type

innodbmyisamMySQLmysql-5.6replication

According to the replication documentation from MySQL, it is possible to set up replication from InnoDB source tables to MyISAM destination tables. Unfortunately, the documentation has little to say about drawbacks, data consistency implications (apart from the CASCADE corner case) and recommended settings for enabling such a replication configuration. So just a number of questions come to my mind regarding this setup:

  1. is statement-based replication preferred over the row-based variant or vice-versa?
  2. does either the InnoDB or the MyISAM side need some specific settings so data consistency would not break all by itself as the result of "normal" replication activity?
  3. are DDL executions on the master handled on the slave in a sane manner?
  4. how would I prevent an ALTER TABLE blah ENGINE=InnoDB statement run at the master from propagating to the slave?
  5. Is there a recommended way of setting up such a "split" setup? I could think of a direct ALTER TABLE blah ENGINE=MyISAM on the slave, is this viable?
  6. any other caveats one should know about?

Best Answer

I will answer each question as follows:

1: is statement-based replication preferred over the row-based variant or vice-versa?

If you are replicating to a Slave with all MyISAM tables, statement-based is more sensible because the SQL statement would be more compact to log and ship.

If the Slave has all InnoDB, row-based is better because of the granular changes. Just be willing to live with bloated binary logs.

2: does either the InnoDB or the MyISAM side need some specific settings so data consistency would not break all by itself as the result of "normal" replication activity?

Data consistency falls out of the picture with MyISAM. Measures for crash-safe replication always falls in favor of InnoDB. MySQL 5.6 supports storing log file and position in an InnoDB table.

3: are DDL executions on the master handled on the slave in a sane manner?

Yes

4: how would I prevent an ALTER TABLE blah ENGINE=InnoDB statement run at the master from propagating to the slave?

Run it like this:

SET SQL_LOG_BIN=0;
ALTER TABLE blah ENGINE=InnoDB;
SET SQL_LOG_BIN=1;

5: Is there a recommended way of setting up such a "split" setup? I could think of a direct ALTER TABLE blah ENGINE=MyISAM on the slave, is this viable?

Yes, I have recommended this before : Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?

6: any other caveats one should know about?

All the caveats you needs are in this post : Using MyISAM for reading and InnoDB for writing data