Questions
26.6.1: Do MySQL 5.1 stored procedures and functions work with replication?
26.6.2: Are stored procedures and functions created on a master server replicated to a slave?
26.6.3: How are actions that take place inside stored procedures and functions replicated?
26.6.4: Are there special security requirements for using stored procedures and functions together with replication?
26.6.5: What limitations exist for replicating stored procedure and function actions?
26.6.6: Do the preceding limitations affect MySQL's ability to do point-in-time recovery?
26.6.7: What will MySQL do to correct the aforementioned limitations?
26.6.8: Do triggers work with replication?
26.6.9: How are actions carried out through triggers on a master replicated to a slave?
Questions and Answers
26.6.1: Do MySQL 5.1 stored procedures and functions work with replication?
Yes, standard actions carried out in stored procedures and functions are replicated from a master MySQL server to a slave server. There are a few limitations that are described in detail in 項17.4. 「ストアドルーチンとトリガのバイナリログ」.
26.6.2: Are stored procedures and functions created on a master server replicated to a slave?
Yes, creation of stored procedures and functions carried out
through normal DDL statements on a master server are
replicated to a slave, so the objects will exist on both
servers. ALTER
and
DROP
statements for stored procedures and
functions are also replicated.
26.6.3: How are actions that take place inside stored procedures and functions replicated?
MySQL records each DML event that occurs in a stored procedure and replicates those individual actions to a slave server. The actual calls made to execute stored procedures are not replicated.
Stored functions that change data are logged as function invocations, not as the DML events that occur inside each function.
26.6.4: Are there special security requirements for using stored procedures and functions together with replication?
Yes. Because a slave server has authority to execute any statement read from a master's binary log, special security constraints exist for using stored functions with replication. If replication or binary logging in general (for the purpose of point-in-time recovery) is active, then MySQL DBAs have two security options open to them:
Any user wishing to create stored functions must be
granted the SUPER
privilege.
Alternatively, a DBA can set the
log_bin_trust_function_creators
system variable to 1, which enables anyone with the
standard CREATE ROUTINE
privilege
to create stored functions.
26.6.5: What limitations exist for replicating stored procedure and function actions?
Non-deterministic (random) or time-based actions embedded in
stored procedures may not replicate properly. By their very
nature, randomly produced results are not predictable and
cannot be exactly reproduced, and therefore, random actions
replicated to a slave will not mirror those performed on a
master. Note that declaring stored functions to be
DETERMINISTIC
or setting the
log_bin_trust_function_creators
system
variable to 0 will not allow random-valued operations to be
invoked.
In addition, time-based actions cannot be reproduced on a slave because the timing of such actions in a stored procedure is not reproducible through the binary log used for replication. It records only DML events and does not factor in timing constraints.
Finally, non-transactional tables for which errors occur
during large DML actions (such as bulk inserts) may
experience replication issues in that a master may be
partially updated from DML activity, but no updates are done
to the slave because of the errors that occurred. A
workaround is for a function's DML actions to be carried out
with the IGNORE
keyword so that updates
on the master that cause errors are ignored and updates that
do not cause errors are replicated to the slave.
26.6.6: Do the preceding limitations affect MySQL's ability to do point-in-time recovery?
The same limitations that affect replication do affect point-in-time recovery.
26.6.7: What will MySQL do to correct the aforementioned limitations?
As of MySQL 5.1.5, you can choose either statement-based replication or row-based replication. The original replication implementation is based on statement-based binary logging. Row-based binary logging resolves the limitations mentioned earlier.
Beginning with MySQL 5.1.8, mixed
replication is also available (by starting the server with
--binlog-format=mixed
). This hybrid,
「smart」 form of replication
「knows」 whether statement-level replication can
safely be used, or row-level replication is required.
For additional information, see 項5.1.2. 「レプリケーション フォーマット」.
26.6.8: Do triggers work with replication?
Triggers and replication in MySQL 5.1 work in the same wasy as in most other database engines: Actions carried out through triggers on a master are not replicated to a slave server. Instead, triggers that exist on tables that reside on a MySQL master server need to be created on the corresponding tables on any MySQL slave servers so that the triggers activate on the slaves as well as the master.
26.6.9: How are actions carried out through triggers on a master replicated to a slave?
First, the triggers that exist on a master must be
re-created on the slave server. Once this is done, the
replication flow works as any other standard DML statement
that participates in replication. For example, consider a
table EMP
that has an
AFTER
insert trigger, which exists on a
master MySQL server. The same EMP
table
and AFTER
insert trigger exist on the
slave server as well. The replication flow would be:
An INSERT
statement is made to
EMP
.
The AFTER
trigger on
EMP
activates.
The INSERT
statement is written to
the binary log.
The replication slave picks up the
INSERT
statement to
EMP
and executes it.
The AFTER
trigger on
EMP
that exists on the slave
activates.
For answers to some general questions about MySQL stored procedures and stored functions, see 項A.4. 「MySQL 5.1 FAQ — Stored Procedures」. Some common questions concerning MySQL triggers are adressed in 項A.5. 「MySQL 5.1 FAQ — Triggers」.