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.6.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 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.
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
on the master before setting up
replication with tbl_name
ENGINE=MyISAMLOAD TABLE
,
and then use tbl_name
FROM MASTERALTER 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 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. This
is true whether you are using statement-based or row-based
replication. 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 InnoDB
support enabled. If this is the case, then the tables on the
slave are created, but they use the MyISAM
storage engine, and the FOREIGN KEY
option
is ignored. Now we insert some rows into the tables on the
master:
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:
master>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:
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:
master> 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 DELETE
for
fc1
deletes no rows from
fc2
. The slave's copy of
fc2
still contains all of the rows that
were originally inserted:
slave> 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 InnoDB
storage engine, which means that none of the changes are
logged.
User Comments
Add your own comment.