How to synchronize trigger, procedure, and event

I recently encountered a requirement involving the stored procedure, and was suddenly asked how to synchronize the problem. I have to make up a lesson to learn more.

 

First, let's take a look at the definitions of trigger, procedure, and event?

Trigger: A trigger is a data object that is specified to be associated with a table. When a special event occurs to a table, the trigger is activated.

Procedure: a set of SQL statements for specific functions.

Event: scheduled task, similar to Linux crontab.

 

In mysql synchronization, you can set different binlog_formats, including statment, row, and mix.

Statment: Statement-level, record the original SQL statement.

Row: row-level, which records the actual modification of affected rows.

Mix: the preceding two types of mixture use statement-level data inconsistency.

 

Finally, let's look at how trigger, procedure, and event record binlog in statement and row formats.

1. statement + trigger

Binlog records the create trigger and pushes it to slave. Then, it records normal SQL statements and does not record any SQL statements in the trigger.

2. row + trigger

Binlog records the create trigger, records all execution results of normal SQL and trigger SQL, and pushes them to slave.

3. statement + procedure

In binlog, create procedure is recorded, and the call statement is not recorded. Instead, SQL statements in procedu are recorded at the statement level and pushed to slave.

4. row + procedure

Binlog will record create procedure, and will not record call procedure. Instead, it will record all executed records in binlog and push them to slave.

5. statement + event

Binlog records the create event and pushes the operation records in the event to slave.

6. row + event

Binlog records the create event, records all operations in the event in the row format, and pushes them to slave.

 

Problems during this period:

1. Since create is statement-level, the slave database automatically changes binlog_format.

2. If binlog_format in the slave database is statement but the master database is row, an error will be reported during synchronization. You need to change the slave database to row to restore the database.

 

The above two points can easily cause synchronization interruption, so we do not recommend using the above three settings in mysql. If you want to use the statement format, it is best to use.