17.3.2 Using Replication with Different Master and Slave Storage Engines

It does not matter for the replication process whether the source
table on the master and the replicated table on the slave use
different engine types. In fact, the
default_storage_engine and
storage_engine system variables
are not replicated.

This provides a number of benefits in the replication process in
that you can take advantage of different engine types for
different replication scenarios. For example, in a typical
scale-out scenario (see
Section 17.3.3, “Using Replication for Scale-Out”), you want to use
InnoDB tables on the master to take advantage
of the transactional functionality, but use
MyISAM on the slaves where transaction support
is not required because the data is only read. When using
replication in a data-logging environment you may want to use the
Archive storage engine on the slave.

Configuring different engines on the master and slave depends on
how you set up the initial replication process:

If you used mysqldump to create the
database snapshot on your master, you could edit the dump file
text to change the engine type used on each table.

Another alternative for mysqldump is to
disable engine types that you do not want to use on the slave
before using the dump to build the data on the slave. For
example, you can add the
--skip-innodb
option on your slave to disable the InnoDB
engine. If a specific engine does not exist for a table to be
created, MySQL will use the default engine type, usually
MyISAM. (This requires that the
NO_ENGINE_SUBSTITUTION SQL
mode is not enabled.) If you want to disable additional
engines in this way, you may want to consider building a
special binary to be used on the slave that only supports the
engines you want.

If you are using raw data files (a binary backup) to set up
the slave, you will be unable to change the initial table
format. Instead, use ALTER
TABLE to change the table types after the slave has
been started.

For new master/slave replication setups where there are
currently no tables on the master, avoid specifying the engine
type when creating new tables.

If you are already running a replication solution and want to
convert your existing tables to another engine type, follow these
steps:

Stop the slave from running replication updates:

mysql> STOP SLAVE;

This will enable you to change engine types without
interruptions.

Execute an ALTER TABLE ...
ENGINE='engine_type' for
each table to be changed.

Start the slave replication process again:

mysql> START SLAVE;

Although the
default_storage_engine variable
is not replicated, be aware that CREATE
TABLE and ALTER TABLE
statements that include the engine specification will be correctly
replicated to the slave. For example, if you have a CSV table and
you execute:

mysql> ALTER TABLE csvtable Engine='MyISAM';

The above statement will be replicated to the slave and the engine
type on the slave will be converted to MyISAM,
even if you have previously changed the table type on the slave to
an engine other than CSV. If you want to retain engine differences
on the master and slave, you should be careful to use the
default_storage_engine variable
on the master when creating a new table. For example, instead of: