14.2.6.5. InnoDB and MySQL Replication
MySQL replication works for InnoDB tables as
it does for MyISAM tables. It is also
possible to use replication in a way where the storage engine on
the slave is not the same as the original storage engine on the
master. For example, you can replicate modifications to an
InnoDB table on the master to a
MyISAM table on the slave.
To set up a new slave for a master, you have to make a copy of
the InnoDB tablespace and the log files, as
well as the .frm files of the
InnoDB tables, and move the copies to the
slave. If the innodb_file_per_table variable
is enabled, you must also copy the .ibd
files as well. For the proper procedure to do this, see
Section 14.2.8, “Backing Up and Recovering an InnoDB Database”.
If you can shut down the master or an existing slave, you can
take a cold backup of the InnoDB tablespace
and log files and use that to set up a slave. To make a new
slave without taking down any server you can also use the
non-free (commercial)
InnoDB
Hot Backup tool.
You cannot set up replication for InnoDB
using the LOAD TABLE FROM MASTER statement,
which works only for MyISAM tables. There are
two possible workarounds:
Dump the table on the master and import the dump file into
the slave.
Use ALTER TABLE tbl_name
ENGINE=MyISAM on the master before setting up
replication with LOAD TABLE
tbl_name FROM MASTER,
and then use ALTER TABLE to convert the
master table back to InnoDB afterward.
However, this should not be done for tables that have
foreign key definitions because the definitions will be
lost.
Transactions that fail on the master do not affect replication
at all. MySQL replication is based on the binary log where MySQL
writes SQL statements that modify data. A transaction that fails
(for example, because of a foreign key violation, or because it
is is rolled back) is not written to the binary log, so it is
not sent to slaves. See Section 13.4.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.
