Do MySQL 5.1 stored procedures and functions work
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 Section 19.4, “Binary Logging of Stored Routines and Triggers”.
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
statements for stored procedures and functions are also
How are actions that take place inside stored procedures and
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
Stored functions that change data are logged as function
invocations, not as the DML events that occur inside each
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
Alternatively, a DBA can set the
variable to 1, which enables anyone with the standard
CREATE ROUTINE privilege to create
What limitations exist for replicating stored procedure and
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
variable to 0 will not allow random-valued operations to be
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
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.
Do the preceding limitations affect MySQL's ability to do
The same limitations that affect replication do affect
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 implement is based on statement-based binary
logging. Row-based binary logging resolves the limitations
mentioned earlier. For additional information, see
Section 6.3, “Row-Based Replication”.
Do triggers work with replication?
Triggers and replication in MySQL 5.1 work the
same 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.
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
insert trigger, which exists on a master MySQL server. The
EMP table and
insert trigger exist on the slave server as well. The
replication flow would be: