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 13.2.7, “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
commercial
InnoDB
Hot Backup tool.
There are minor limitations in InnoDB
replication:
LOAD TABLE FROM MASTER does
not work for InnoDB type tables. There
are workarounds: 1) dump the table on the master and import
the dump file into the slave, or 2) use ALTER TABLE
on
the master before setting up replication with tbl_name TYPE=MyISAMLOAD
TABLE , and then use tbl_name FROM
MASTERALTER
TABLE to alter the master table back to the
InnoDB type afterward. However, this
should not be done for tables that have foreign key
definitions because the definitions will be lost.
Before MySQL 4.0.6, SLAVE STOP did not
respect the boundary of a multiple-statement transaction. An
incomplete transaction would be rolled back, and the next
SLAVE START would only execute the
remaining part of the half transaction. That would cause
replication to fail.
Before MySQL 4.0.6, a slave crash in the middle of a
multiple-statement transaction would cause the same problem
as SLAVE STOP.
Before MySQL 4.0.11, replication of the SET
foreign_key_checks = 0 statement does not work
properly.
Most of these limitations can be eliminated by using more recent server versions for which the limitations do not apply.
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 rolled back) is not written to the binary log, so it is not
sent to slaves. See Section 12.3.1, “START TRANSACTION,
COMMIT, and
ROLLBACK Syntax”.
Replication and CASCADE.
Cascading actions for InnoDB tables on the
master are replicated on the slave only
if the tables sharing the foreign key relation use
InnoDB on both the master and slave.
Suppose that you have started replication, and then create two
tables on the master using the following
CREATE TABLE statements:
CREATE TABLE fc1 (
i INT PRIMARY KEY,
j INT
) ENGINE = InnoDB;
CREATE TABLE fc2 (
m INT PRIMARY KEY,
n INT,
FOREIGN KEY ni (n) REFERENCES fc1 (i)
ON DELETE CASCADE
) ENGINE = InnoDB;

User Comments
Add your own comment.