Menu
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:
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?
551
Save this answer. Show activity on this post. To give a simple answer to your question, "No, replication does not kill the performance of your master."
Advantages of replication in MySQL include: Scale-out solutions - spreading the load among multiple replicas to improve performance. In this environment, all writes and updates must take place on the source server. Reads, however, may take place on one or more replicas.
First, we make sure that MySQL never synchronizes the binary log to disk. Instead, we let the operating system do it from time to time. Note that sync_binlog default value is 0, but we used a higher value to avoid problems instead of crash.
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
53
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
