Mysql storage program, function, trigger program and replication: FAQ _ MySQL

Mysql storage program, function, trigger program and replication: FAQs: bitsCN.com

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. There are a few limitations, which are detailed in Section 20.4, "storing subprograms and triggering program binary log functions.
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.
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.
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.

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.

Will the above restrictions affect MySQL's point-in-time recovery capabilities?
The same restriction that affects replication affects point-in-time recovery.
What should MySQL do to correct the preceding restrictions?
In the future, MySQL is expected to have a function to choose how to copy the data:
Statement-based replication (current implementation ).
Row-level replication (which resolves all limitations described earlier ).
Does the trigger program work for replication?
The triggering program and replication in MySQL 5.1 work as in most other database engines. in those engines, the actions performed by triggering programs on the master server are not copied to the slave server. Instead, the trigger program in the table on the master MySQL server needs to be created in tables that exist on any MySQL slave server, so that the trigger program can be activated on the slave server.

How can a behavior be executed by copying the trigger program from the master server to the slave server?
First, the trigger program on the master server must be rebuilt on the slave server. Once re-built, the replication process will work like other standard DML statements involved in replication. For example, consider an EMP table that has been inserted with the trigger program AFTER, which is located on the master MySQL server. The same EMP table and the AFTER insert trigger program also exist on the slave server. The replication process may be:
1. execute an INSERT statement on EMP.
2. activate the AFTER trigger program on EMP.
3. the INSERT statement is written into the binary log.
4. pick up the INSERT statement from the replication on the server to the EMP table and execute it on the slave server.
5. activate the AFTER trigger program on the slave server EMP.

BitsCN.com