Author: shuixian
Do MySQL 5.1 stored procedures and functions work on replication?
Yes, standard behavior is performed in stored procedures and functions that are replicated from the master MySQL server to the slave server.
Can stored procedures and functions created on the master server be copied to the slave server?
Yes, stored procedures and functions executed through general DDL statements, whose creation on the master server are 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 within replicated stored procedures and functions?
MySQL records every DML event that occurs within stored procedures and functions, and replicates these individual actions to slave servers. 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 are enabled in general (for point-in-time recovery purposes), then the MySQL DBA has two security options available:
Any user who wants to create stored procedures must be granted SUPER privileges.
Alternatively, a DBA can set the log_bin_trust_routine_creators system variable to 1, which will allow anyone 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 do not copy properly. Randomly generated results, by their very nature, are predictable and cannot be reliably cloned. Therefore, random behavior replicated to the slave will not mirror that occurring on the master. 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.
Additionally, time-based behavior is not reproducible 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 logs DML events and does not include timing constraint.
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.