MySQL Replication - Triggers do not replicate multiple databases

0
votes

I have the following MySQL 5.5.16 setup, properly running: Server A w/ db.a and db.b Server B configured as slave for db.a (not db.b), by means of --replicate-wild-do-table Tables on Srv.A-db.b have triggers on insert that insert/update db.a

db.a is properly replicated in Server.B, ie, if an update via an SQL client on db.a on server.A happens, is properly replicated to db.a on Server.B

Now comes the problem: triggers on Server.A/db.b that update/insert db.a are not replicated... have lost many hours exhausting all my knowledge on this...

On Srv.A/db.b I finally created a federated table pointing to Srv.A/db.a and the triggers working through the federated table are properly replicating but is VERY slow and some things cannot be used (ON DUPLICATE UPDATE for example)... so is really a stop-goat solution.

If I try to replicate both db.a and db.b the triggers work ok, but db.b is HUGE and not supposed to be replicated the server.B.

Any suggestions on how to make triggers work on db.b-->db.a when only db.a is replicated ??

Thanks for the suggestion.

1
mysqltriggersreplication

1 Answers

0
votes

See MySQL Trigger FAQ B.5.12: http://dev.mysql.com/doc/refman/5.0/en/faqs-triggers.html#qandaitem-B-5-1-12

'Triggers and replication in MySQL 5.0 work in the same way as in most other database systems: Actions carried out through triggers on a master are not replicated to a slave server.'

Maybe you can change all events that cause the trigger to run to instead call a stored procedure which will do the same work the trigger does now.