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
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
is enabled, you must also copy the
files as well. For the proper procedure to do this, see
Section 14.2.6, “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
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
Enterprise Backup tool.
You cannot set up replication for
LOAD TABLE FROM MASTER
statement, which works only for
tables. There are two possible workarounds:
Dump the table on the master and import the dump file into the slave.
ALTER TABLEon the master before setting up replication with
LOAD TABLE, and then use
ALTER TABLEto convert the master table back to
InnoDBafterward. 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 rolled back) is not written to the binary log, so it is not sent to slaves. See Section 13.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;
Suppose that the slave does not have
support enabled. If this is the case, then the tables on the
slave are created, but they use the
storage engine, and the
FOREIGN KEY option is
ignored. Now we insert some rows into the tables on the master:
INSERT INTO fc1 VALUES (1, 1), (2, 2);Query OK, 2 rows affected (0.09 sec) Records: 2 Duplicates: 0 Warnings: 0 master>
INSERT INTO fc2 VALUES (1, 1), (2, 2), (3, 1);Query OK, 3 rows affected (0.19 sec) Records: 3 Duplicates: 0 Warnings: 0
At this point, on both the master and the slave, table
fc1 contains 2 rows, and table
fc2 contains 3 rows, as shown here:
SELECT * FROM fc1;+---+------+ | i | j | +---+------+ | 1 | 1 | | 2 | 2 | +---+------+ 2 rows in set (0.00 sec) master>
SELECT * FROM fc2;+---+------+ | m | n | +---+------+ | 1 | 1 | | 2 | 2 | | 3 | 1 | +---+------+ 3 rows in set (0.00 sec) slave>
SELECT * FROM fc1;+---+------+ | i | j | +---+------+ | 1 | 1 | | 2 | 2 | +---+------+ 2 rows in set (0.00 sec) slave>
SELECT * FROM fc2;+---+------+ | m | n | +---+------+ | 1 | 1 | | 2 | 2 | | 3 | 1 | +---+------+ 3 rows in set (0.00 sec)
Now suppose that you perform the following
DELETE statement on the master:
DELETE FROM fc1 WHERE i=1;Query OK, 1 row affected (0.09 sec)
Due to the cascade, table
fc2 on the master
now contains only 1 row:
SELECT * FROM fc2;+---+---+ | m | n | +---+---+ | 2 | 2 | +---+---+ 1 row in set (0.00 sec)
However, the cascade does not propagate on the slave because on
the slave the
fc1 deletes no rows from
fc2. The slave's copy of
fc2 still contains all of the rows that were
SELECT * FROM fc2;+---+---+ | m | n | +---+---+ | 1 | 1 | | 3 | 1 | | 2 | 2 | +---+---+ 3 rows in set (0.00 sec)
This difference is due to the fact that the cascading deletes
are handled internally by the
engine, which means that none of the changes are logged.