Mysql – Force replication to statement for INSERT to a table to fire trigger on slave

MySQLmysql-5.6replicationtrigger

We have a PROD DB which replicates into a slave DB using mixed replication. We want to add a trigger so that a row is added to our DW when a row is INSERTed into table_a (on master). The issue is that this INSERT is coming through using Row-based replication and the trigger (which is on table_a on slave) is not firing. We need to have the trigger on the slave table as that is where our DW is.

Looking around online it looks like this should work if statement-based replication is used. Is it possible to force the INSERT to table A to be processed as statement-based replication? Or is there any other way we can achieve this?

The INSERT itself is deterministic as is the trigger. We are using MySQL 5.6.

If you need any other information please let me know.

Best Answer

As documentation say

With statement-based replication, triggers executed on the master also execute on the slave. With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.

This behavior is by design.

So in anycase statement/row, you don't need to be worrying about the data on slave and it should match the master.

but still if you want and can fit-the-logic on an event, this might be a possibility on slave at the risk of inconsistency!! As you said this is dw slave, would you consider a separate process to do the task you're looking to do, say a procedure?

Related Solutions

Sql-server – Any reasons trigger AFTER INSERT would not fire

It is possible to disable a trigger. (The user could have disabled it) Also, it is possible that there are other triggers that return an error and rolls back the transaction, wich means the after trigger is not called.

Mysql – Replicating from MySQL 5.1 master to 5.6 slave failing because ‘INSERT … VALUES (NOW())’ results in ‘Error_code: 1062’

Can I assume that 'INSERT INTO "Member" ("Created") VALUES (NOW())' is triggering the error here?

Yes that's exactly what the error telling

Error 'Duplicate entry '8072' for key 'PRIMARY'' on query. 
Default database: 'DATABASE'. Query: 'INSERT INTO "Member" ("Created") VALUES (NOW())', 
Error_code: 1062

Can I get replication to work without skipping rows with SET GLOBAL sql_slave_skip_counter = 1;?

Yes, You can go and delete the row with the ID 8072 in the table.. you can add the option of slave-skip-errors = 1062 in my.cnf and restart mysql

But you have to be extremely wary about doing this, as the referential integrity is there for a reason, and without it, your slave could not be satisfactorily promoted to take the place of the master in the case of something going wrong with your master hardware...

The only clean way is:

In the master

You FLUSH TABLES WITH READ LOCK; in the database you want to replicate
SHOW MASTER STATUS and copy the file name and the position
Excute a full backup to the database you want to replicate
Then release the tables with UNLOCK TABLES;

In slave

Restore the databse
Excute this CHANGE MASTER TO MASTER_HOST = 'masterhost', MASTER_USER = 'masteruser', MASTER_PASSWORD = 'masterpass', MASTER_LOG_FILE = 'filename', MASTER_LOG_POS = 'position';
Then start slave;

Best Answer

Related Solutions

Sql-server – Any reasons trigger AFTER INSERT would not fire

Mysql – Replicating from MySQL 5.1 master to 5.6 slave failing because ‘INSERT … VALUES (NOW())’ results in ‘Error_code: 1062’

Related Question