MySQL storage programs, functions, and replication Problems

1. Does MySQL 5.1 storage programs and functions work for replication?

Yes, the standard behavior executed in the storage program and function is copied from the master MySQL server to the slave server.

2. Can the stored programs and functions created on the master server be copied to the slave server?

Yes, the storage programs and functions executed by DDL statements are copied to the slave server after they are created on the master server. Therefore, the target will exist on two servers. The ALTER and DROP statements for stored programs and functions are also copied.

3. How does the behavior occur in the copied stored programs and functions?

MySQL records every DML event that occurs in the storage program and function, and copies these individual actions to the slave server. The Actual invocation of stored programs and functions is not copied.

4. Are there any special security requirements for using storage programs, functions, and replication together?

Yes, because a slave server has the permission to execute any statement to read the binary log of the autonomous server, the specified security constraint exists because of the storage program and function used together with the replication. If the replication or binary log is generally active (for point-in-time recovery), MySQL DBA has two Security Options:

Any user who wants to create a bucket must be granted the SUPER permission.

As an option, a DBA can set the log_bin_trust_routine_creators system variable to 1, which will allow people with the standard create routine permission to CREATE a storage program and function.

5. What are the restrictions on the replication of stored programs and functions?

Uncertain (random) or time-based rows embedded into the storage program cannot be copied as appropriate. The random result is only because of its nature. It is predictable and cannot be cloned. Therefore, the random behavior of copying to the slave server will not mirror those generated on the master server. Note: declaring a stored program or function as DETERMINISTIC or setting the system variable to 0 in log_bin_trust_routine_creators allows immediate value operations to be called.

In addition, the time base behavior cannot be re-generated from the server, because such time base behavior cannot be re-generated in the storage program by timing the binary logs used for replication, because this binary log only records DML events and does not include timing constraints.

Finally, an error occurs in a non-interactive table in a large DML behavior (such as a large number of inserts), and the non-interactive table may experience replication, the master server in the non-interactive table of the replication version can be partially updated from DML behavior. However, the slave server is not updated due to the error. For the DML behavior of the function, the workspace will be executed with the IGNORE keyword, so that error updates can be ignored on the master server and errors will not be copied to the slave server.