Problems In Doing MySQL Row-Based Replication By Myself

Question

I am trying using MySQL Replication Listener Library to do data replication between HostA and HostB.

The replication is based on row-based other than statements. I know there is such built-in feature in MySQL itself, but I want to do it myself --- Reading all row changes from HostA's bin log and applying all these changes to HostB.

The issue I am encountering is, I am not able to disable triggers temporarily, which will cause the data to be out of sync.

Take an example, suppose the database to be synchronized is TestSyncDB, and there are two tables in it which are Data and DataOp. The Data table has a trigger with it and once a record is inserted into this table, the trigger will generate a new record to the DataOp.

Now a new record is inserted into HostA.TestSyncDB.Data, since there is a trigger, so there will actually be two records added to the database, and the Replication Listener Library will return to me two queries, something like:

1. insert into HostA.TestSyncDB.Data values (...)
2. insert into HostA.TestSyncDB.DataOp values (...) // by trigger

The problem is if I replay these two queries to HostB, there will be three new records added to tables in HostA.TestSyncDB, because of the triggers. Then data is never right ever after.

So how to solve this? Is is possible to disable triggers temporarily? If not, how to do it correctly like what MySQL Replication does?

Answer 1

In mysql replication when you created trigger on master, it will be created on slave. So if you wants different trigger on slave, you can edit that trigger or drop if you don't want. For the relication format if your master's binlog binlog_format = ROW, it will copy as master done, its not fit for triggers if slave wants different trigger action then master as your situation. In this situation binlog required statement based binlog_format = STATEMENT. So is it possible in mysql to switch b/w these two as need.

So key point here is You need binlog_format = MIXED. It is smart enough to decide when to use STATEMENT based and when to used ROW based. It will treate as STATEMENT BASED for this situation. Other wise it will work as ROW BASED.

You can read here more- replication format and Advantages and Disadvantages