MySQL: replicating to a different table engine type

Question

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?

Answer 1

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

• syneticon-dj: Rolando, thanks for taking the time. A follow-up considering data consistency: I trust statements are only written to the replication binlog after the transaction has indeed been committed, so I would not end up with statements from rolled-back transactions executed on the slave?
• RolandoMySQLDBA: This is true to the extent of what was executed on the Master and written to the Master's binlogs. The exception would be Error 1053 : Query partially completed on the master (error on master: 1053) and was aborted. You can either skip the error or execute the SQL command manually and then skip the error.
• syneticon-dj: So any rollback on the master would result in broken replication? Or just the transactions which were explicitly KILLed?
• RolandoMySQLDBA: Just the tranactions explicitly killed