Do MySQL 5.1 stored procedures and functions work on replication?
Yes, standard behavior is implemented in stored procedures and functions that are replicated from the master MySQL server to the slave server. There are a few limitations, which are detailed in Section 20.4, "Store Subroutine and Trigger Binary Log Features".
Can stored procedures and functions created on the master server be copied to the slave server?
Yes, for stored procedures and functions executed through general DDL statements, their creation on the master server is copied to the slave server, so the target will exist on both servers. ALTER and DROP statements for stored procedures and functions are also replicated.
How does behavior occur in copied stored procedures and functions?
MySQL records every DML event that occurs in stored procedures and functions, and replicates these individual actions to the slave server. Actual calls to stored procedures and functions are not copied.
Are there any special security requirements for using stored procedures, functions and replication together?
Yes, because a slave has permission to execute any statement that reads the master's binary log, the specified security constraints exist for stored procedures and functions used with replication. If replication or binary logging is enabled in general (for point-in-time recovery purposes), then the MySQL DBA has two security options available:
Any user who wants to create a stored procedure must be granted SUPER privileges.
Alternatively, a DBA can set the log_bin_trust_routine_creators system variable to 1, which will allow people with standard CREATE ROUTINE permissions to create stored procedures and functions.
What are the restrictions on the behavior of copying stored procedures and functions?
Indeterminate (random) or time-based lines embedded in stored procedures are not copied appropriately. Randomly generated results, by their very nature, are predictable and cannot be reliably cloned. Therefore, random behavior replicated to the slave server will not mirror that occurring on the master server. Note that declaring a stored procedure or function DETERMINISTIC or setting the system variable to 0 in log_bin_trust_routine_creators will allow random value operations to be called.
In addition, time-based behavior cannot be reproduced on the slave server, because such time-based behavior is not reproducible in the stored procedure through the binary log used for replication, because the binary log only records DML events and does not include Timing constraints.
Finally, if an error occurs in a non-interactive table during a large DML action (such as a bulk insert), the non-interactive table may undergo replication, and the master server may be partially updated from the DML action in the replicated version of the non-interactive table. But because of the error that occurred, there was no update to the slave server. For the DML behavior of the function, the workspace will be executed with the IGNORE keyword so that updates that cause errors on the master server are ignored, and updates that do not cause errors are copied to the slave server.
Will the above restrictions affect MySQL’s ability to perform point-in-time recovery?
The same limitations that affect replication affect point-in-time recovery.
What should MySQL do to correct the aforementioned limitations?
Future releases of MySQL are expected to have a feature to choose how replication should be handled:
Statement-based replication (current implementation).
Row level replication (it will solve all the limitations described earlier).
Does triggering work on replication?
Triggers and replication in MySQL 5.1 work like in most other database engines, where actions performed on the master via triggers are not replicated to the slaves. Instead, triggers located in tables on the master MySQL server need to be created within those tables that exist on any MySQL slaves so that the triggers can be activated on the slaves as well.
How can a behavior be executed through a trigger copied from the master server to the slave server?
First, the trigger program on the master server must be rebuilt on the slave server. Once rebuilt, the replication process works like any other standard DML statement involved in replication. For example: Consider an EMP table that has a trigger AFTER inserted into it, located on the main MySQL server. The same EMP table and AFTER insert triggers also exist on the slave server. The copy process may be:
1. Make an INSERT statement for EMP.
2. AFTER trigger activation on EMP.
3. The INSERT statement is written to the binary log.
4. Replication on the slave server picks up the INSERT statement to the EMP table and executes it on the slave server.
5. The AFTER trigger program located on the slave server EMP is activated.