MySQL statement-based replication and stored procedure

MySQLreplication

When you have a MySQL statement-based replication and you create a stored procedure that modify some rows (INSERT, UPDATE, DELETE commands…), What is really replicated on the slave when you call this procedure on the master ? The CALL himself or the commands (INSERT, UPDATE, DELETE…) inside the procedure ?

For example, create such a procedure:

delimiter |

CREATE PROCEDURE myproc ()
      BEGIN

        DELETE FROM mytable LIMIT 1;

      END |

delimiter ;

Then call on the master:

master> CALL myproc();

=> does it replicates "CALL myproc()" or "DELETE FROM mytable LIMIT 1" ?

Best Answer

In short, no, your CALL is not replicated

You can take a look into the FAQ starting from B.4.22 and the Binary Logging of Stored Programs

B.4.23: 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 servers. ALTER and DROP statements for stored procedures and functions are also replicated.

B.4.24: How are actions that take place inside stored procedures and functions replicated?

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 replicated.

Stored functions that change data are logged as function invocations, not as the DML events that occur inside each function.

Related Question