89584

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 [489] and storage_engine [561] 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 16.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 My ISAM 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 mysąldump to create the database snapshot on your master, you could editthe dump

file text to change the engine type used on each table.

Another alternative for mysąldump 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 —skipinnodb

[1735] 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 MylSAM. (This requires that

the NO_ENGINE_SUBSTITUTION [619] SQL modę 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.