It does not matter for the replication process whether the
original table on the source and the replicated table on the
replica use different storage engine types. In fact, the
variable is 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 3.5, “Using Replication for Scale-Out”), you want to use
InnoDB tables on the source to take advantage
of the transactional functionality, but use
MyISAM on the replicas 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 replica.
Configuring different engines on the source and replica depends on how you set up the initial replication process:
If you used mysqldump to create the database snapshot on your source, 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 replica before using the dump to build the data on the replica. For example, you can add the
--skip-federatedoption on your replica to disable the
FEDERATEDengine. If a specific engine does not exist for a table to be created, MySQL uses the default engine type, usually
InnoDB. (This requires that the
NO_ENGINE_SUBSTITUTIONSQL 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 replica that supports only the engines you want.
If you use raw data files (a binary backup) to set up the replica, it is not possible to change the initial table format. Instead, use
ALTER TABLEto change the table types after the replica has been started.
For new source/replica replication setups where there are currently no tables on the source, 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 replica from running replication updates:
mysql> STOP SLAVE; Or from MySQL 8.0.22: mysql> STOP REPLICA;
This makes it possible to change engine types without interruption.
ALTER TABLE ... ENGINE='for each table to be changed.
Start the replication process again:
mysql> START SLAVE;
Or, beginning with MySQL 8.0.22:
mysql> START REPLICA;
is not replicated, be aware that
statements that include the engine specification are replicated to
the replica correctly. If, in the case of a
CSV table, you execute this
mysql> ALTER TABLE csvtable ENGINE='MyISAM';
This statement is replicated; the table's engine type on the
replica is converted to
InnoDB, even if you
have previously changed the table type on the replica to an engine
CSV. If you want to retain engine
differences on the source and replica, you should be careful to
variable on the source when creating a new table. For example,
mysql> CREATE TABLE tablea (columna int) Engine=MyISAM;
Use this format:
mysql> SET default_storage_engine=MyISAM; mysql> CREATE TABLE tablea (columna int);